Category Archives: programming

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.

Backtrader Data Frustruation

I’ve been working on a day-trading algorithm for backtrader using stock data (primarily SPY and QQQ for now). Sometimes it appears that the data doesn’t completely come through and the system doesn’t quite initialize. I now think I know what is going on.

First, the fix…I shortened the maximum parameter for an ATR parameter that I was using. When this was shortened (using 60 minute data, to 300 bars or fewer) the code started working as anticipated.

Why did this work? I believe using some levels of granularity the historical data retrieved is insufficient to allow all of the indicators to fully populate. This was tricky to narrow down because 1 minute data worked and 120 minute data worked but 60 minute data didn’t. I don’t know that the Interactive Brokers API gives you the same amount of data each time, so in some cases it appears less data is retrieved resulting in in my case ATR to not fully populate and leaving me in the “prenext” area of backtrader, not making it to the “next” method.