Author Archives: proprietor

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.

MYSQL Changes

The new versions of mysql apparently have changed the default sql_mode. The new options may cause older code to break, and require resetting.

To make changes to the sql_mode variable in MySQL persist between server restarts, you can modify the MySQL configuration file (my.cnf or my.ini, depending on your operating system).

Here are the steps to modify the sql_mode variable in the MySQL configuration file:

  1. Open the MySQL configuration file in a text editor. The location of the file may vary depending on your operating system and MySQL installation. On Linux systems, the file is typically located at /etc/my.cnf or /etc/mysql/my.cnf. On Windows systems, the file is typically located at C:\ProgramData\MySQL\MySQL Server x.x\my.ini.
  2. Locate the [mysqld] section of the configuration file. This section contains server options that apply to the MySQL server daemon.
  3. Add the following line to the [mysqld] section to set the sql_mode variable:
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"

Replace the value of the sql_mode variable with the desired mode. In this example, we’re setting the mode to STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, and NO_ENGINE_SUBSTITUTION. 4. Save the changes to the configuration file and exit the text editor. 5. Restart the MySQL server to apply the changes. On Linux systems, you can use the following command to restart the MySQL server:

sudo systemctl restart mysql

On Windows systems, you can use the Services app to restart the MySQL server.

After restarting the MySQL server, the changes to the sql_mode variable should be persisted and applied to all new connections. You can verify the current value of the sql_mode variable by running the following SQL query:

SELECT @@sql_mode;

This will return the current value of the sql_mode variable. If the value matches the value that you set in the configuration file, the changes have been successfully applied.