Category Archives: linux

Mysql to Mariadb Export/Import Problems

I exported my Commodities database from a server running mysql to a mariadb server. I believe that I’ve done this before but at some point updates to one or both of them caused me some serious problems due to incompatibility of the character sets and collation in use in mysql and mariadb.

I tried several ways I saw on the internet to fix the import file. So far I haven’t gotten this to work and have gone a different path. I’m trying to convert to a stable character set and collation in mysql and then I’ll try the export/import again.

I used the following code to generate mysql code that I’m currently in the process of running en masse:

SELECT CONCAT("ALTER TABLE ", TABLE_SCHEMA, ".", "`", TABLE_NAME, "`", " CHARACTER SET utf8 COLLATE utf8_general_ci;   ",
              "ALTER TABLE ", TABLE_SCHEMA, ".", "`", TABLE_NAME, "`", " CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;  ") AS alter_sql
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = "Commodities";

This code outputs sql statement that allow for bulk updates of the database similar to the following examples:

ALTER TABLE Commodities.15mdata CHARACTER SET utf8 COLLATE utf8_general_ci;   ALTER TABLE Commodities.15mdata CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;  
ALTER TABLE Commodities.2001data CHARACTER SET utf8 COLLATE utf8_general_ci;   ALTER TABLE Commodities.2001data CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;  
ALTER TABLE Commodities.2001days CHARACTER SET utf8 COLLATE utf8_general_ci;   ALTER TABLE Commodities.2001days CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;  
ALTER TABLE Commodities.30mdata CHARACTER SET utf8 COLLATE utf8_general_ci;   ALTER TABLE Commodities.30mdata CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;

We shall see if this goes through without problems; I’ve made a backup in case I need to restore the whole thing.

18 Jul 24 I was able to complete the import. The recent changes to mysql and mariadb have updated the collations and character sets for many foreign languages, emojis, etc. I don’t need that for my simple commodities trading application that tracks prices and symbols and dates. Also, there might be performance improvements if I chose the collation and character sets wisely. To that end I’m trying the following:

SELECT CONCAT("ALTER TABLE ", TABLE_SCHEMA, ".", "`", TABLE_NAME, "`", " CHARACTER SET latin1 COLLATE latin1_swedish_ci;   ",
              "ALTER TABLE ", TABLE_SCHEMA, ".", "`", TABLE_NAME, "`", " CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;  ") AS alter_sql
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = "Commodities";

And then running the associated output as its own queries (only some is included for brevity:

ALTER TABLE Commodities.15mdata CHARACTER SET latin1 COLLATE latin1_swedish_ci; ALTER TABLE Commodities.15mdata CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE Commodities.2001data CHARACTER SET latin1 COLLATE latin1_swedish_ci; ALTER TABLE Commodities.2001data CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;
ALTER TABLE Commodities.2001days CHARACTER SET latin1 COLLATE latin1_swedish_ci; ALTER TABLE Commodities.2001days CONVERT TO CHARACTER SET latin1 COLLATE latin1_swedish_ci;

This can be run by making the output of the first query full width, and then copying all of the lines, going back into the database, opening an SQL window, and pasting in the window and executing.

crontab and shell script on hosted server

Making things work on a shared hosting server

I recently had to move my hosted server from one provider to another. As part of the move, I had to adapt to differences in the hosting server setup. I chose linux for the operating system of the server, but I wasn’t able to completely control everything as I didn’t have a complete virtual server at my disposal.

First of all, I had to install python. Python 2.7.9 was installed on the server by default, but it didn’t have pip and I wasn’t able to use some libraries that my scripts depended on. In addition, I couldn’t find out how to get it to work correctly. Since my daily update scripts were based on an Anaconda2 installation, I decided to try to install anaconda again and see if that fixed the problem. I downloaded and setup anaconda, and added pymysql using the pip utility provided by anaconda (and not provided by my web hosting provider, necessitating much of the hassle detailed in this post).The anaconda installation did work, however, each time I would ssh into the server I found that the version of python used by default was still 2.7.9, not the anaconda version. Ti get this to work I needed to modify the .bashrc script.  I changed it to


# added by Anaconda2 4.3.0 installer
export PATH="/the/real/path/goeshere/htdocs/anaconda2/bin:$PATH"

After modifying this script, each time I ssh’d into the server I had to execute the following command:

source ~/.bashrc

If I didn’t do this, the server was still using python 2.7.9, the old version which I wasn’t able to get to work correctly.

After this was fixed, I had to copy my files over and fix some sql that referenced the database name in them. Since I was using a hosted server, I didn’t get the choose the name of the server or database. This was causing a mysql 1142 error, stating that I didn’t have permissions to perform a SELECT query. While initially confusing, I eventually figured out that I had referenced the db name in a query and once I fixed this reference, I was back working again.

Once my wayward mysql was corrected, I began trouble shooting my python scripts.  After some work, I was able to get them working but I still had a few things to work out.  Obviously, any references to the user, password, server and database need to be changed to the new server settings.  Once this is done, I was able to use the phpmyadmin provided by my new provider to determine that my python scripts were working.

I then tried to modify the crontab to automate the script.  However, I needed to run the anaconda version of python to use the libraries that I needed for my script, and it appeared that the crontab was using the older version of python.

It is possible to execute multiple commands in one line of the crontab, separating them by either a “;” or “&&” with slightly different results, but I had several problems and couldn’t get this approach to work.  I needed to run the following command first,

source ~/.bashrc

and then change the directory to where my python scripts were located.  i played around with this for a while but wasn’t able to find a solution.  A major problem is that I wasn’t able to access the system log ,where the cron errors are usually stored, so wasn’t able to see why my script was failing and then correct it.  After many different attempts I decided to try something different.

Some searching on the internet led me to try to use a shell script, and then call the script once using the crontab.  I was finally able to get this to work after some experimentation.  Here’s the an example of the script, which I titled daily_update.sh:


#!/bin/bash
source ~/.bashrc
cd /the/real/path/goeshere/htdocs/python/
python some_python_script.py

The I used the crontab editor and made an entry:


5 18 * * * bash /the/real/path/goes/here/daily_update.sh

This script will execute at 1805 every day.

One other note, which may have saved me some hassle–it is wise to make sure that your server is using the time that you think it is for crontab execution.  I found that my server was using the eastern time zone, while I’m located in the central time zone.  This is easy to check in linux using the

date command:

The output will be the server time, something like
Tue Feb 7 22:13:22 EST 2017