Category Archives: mysql

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.

Interrupted WordPress Update Leads to Crash

I’ve been busy lately with various things, and haven’t paid much attention to my blog.  I keep planning to make a post, but never get around to it for some reason.  I finally decided that I had something to share, and noted that there was an update for WordPress that I could install.  I clicked the “install” button, and waited as things began to happen.

 

Once they started to happen, they never stopped.  My site never finished updating, and I was now left with a blog that didn’t display anything at all-just a blank, white screen.

After waiting a long time (hours), I decided that I’d probably broken things completely.  I searched for an answer, thinking this must be fairly common, but didn’t find anything that was useful.  I finally contact my hosting expert, who also publishes this great food-critic blog.  I was able to get FTP access to my site after a couple of password resets, and I was able to get to the files for my WordPress blog.  The first thing I did was look at the error messages that php had recorded.  Here’s what I found:

[14-Nov-2013 01:35:31 UTC] PHP Fatal error: Call to a member function reset_postdata() on a non-object in /myhostingrootpath/www.corneroftherooftop.com/directorytofiles/query.php on line 118

(Note that I did change the path information slightly so as to not give all the internal details of my hosting server.)

This didn’t really help me that much, but a internet search on this error wasn’t really all that promising.  I then downloaded and installed the latest version of WordPress using FTP, but still I got nothing but a blank screen upon entering the web address.  I then decided to look at my wp-config.php file.  I had previously noted from the timestamp associated with the files that this one hadn’t changed when I had attempted the update from within WordPress.  I also logged in to my host user management site and discovered what my problem was.

The address for the mysql server that my WordPress site was using was different in the wp-config.php file and what the host noted was to be used on its website.  This probably resulted from an upgrade to a newer version of mysql on the hosts server farm, and when I attempted to update WordPress the old mysql address became obsolete.   I changed the address in the wp-config.php file to match the new mysql server url, and off we went. The line specifically that I had to change was similar to this one:

define('DB_HOST', 'theactaullwebaddressfortheMysqlserver');

I had to undo a few other things that I had tried in the interim, such as moving all of my content via FTP to my computer to see if content specifically was the problem, which it wasn’t.  I also turned debugging mode on which resulted in some notifications but didn’t really help.

To summarize, the mysql host changed for some reason, most likely being the new version of WordPress and an updated version of Mysql from my hosting platform.  After changing to the new mysql server address, my problems went away.

I hope this will be of help if anyone stumbles into the same problem that I did.