MySQL on Linux

Hi @ all,

I have a WD App using MySQL as database.
The WD App runs on windows platform.

I moved a existing MySQL database from a Windows Server to a Linux Server as the custumer got new hardware.
I run into trouble doing this:
I would like to share this experience, might be this will be helpfull for someone else in this forum:

First I got a native SQL error claiming that the SSL Settings are not properly set .... but my Software does not use SSL.
After some google research I learned that the libmysql.dll version 6.1.11 which I use and which was shipped with the windows MySQL Installer is outdated.
I found an more recent Version from a hint in a german Delphi Forum.
After changing the DLL the error message disappeared and I was able to start my program which generated blank tables using Hcreationifnotfound() and I was able to play around a bit.

Then I did a dump from the old database on the windows Server and imported this dump into the Linux MySQL Server.
Worked fine, I started the program and got an error message, telling me that a query was not initialized.

After isolating the piece of code producing the error I found out that a simple query crashes ....and this query was the first query executed when the program starts.

The query was:

 Select * from Verkehrsarten

Typing this query in the MySQL Workbench connected to the Windows Server the query worked.
Typing this query in the MySQL Workbench connected to the Linux Server I got the error: "Table Verkehrsarten does not exist".

After some investigation I found out:

1.MySQL generates one ore more physical files on the disk for each table, and this files are named as the name of the table, so the table "Verkehrsarten" generates a File "Verkehrsarten".

2. Linux file system is case sensitive, Windows not, so on Windows Verkehrsarten is the same as verkehrsarten, in Linux they are not the same.

3. MySQL has the setting lower_case_table_names which could have following settings:
1 => All tablenames are stored in lower case independenant of the CreateTable statements and due to this independant of the writing in WD Analysys. When an query is executed the query is also handled as lower case.
0 => All tablenames are stored in the way the CreateTable statement is typed and due to this in the way the table was defined in WD analysis.
Also the queries are executed in the way they have been coded.
2 => All tablenames are stored in the way the CreateTable statement was written, but when the query is executed the behaviour is like 1.

1 is default on Windows, 0 is default on Linux, 2 is available on windows only.

What happened:
My table Verkehrsarten was defenied with a capital V in the analysis and the queries also where written with capital V.
MySQL on windows stored Verkehrsarten as verkehrsarten and translated "select * from Verkehsrarten" to "select * from verkehrsarten".
After importing the dump to Linux the table is still stored as verkehrsarten but the query expected Verkehrsarten.

The solution is to change the setting lower_case_table_name to 1 in the config of the Linux MySQL Server.

But:
You can not change this setting after the Mysqld service started the first time. But If you install my SQL using APT-GET Install the installers starts the service. If you edit the setting for lower_case_table_names afterwards the MYSql server will refuse to start on linux.

Workaround (Debian Linux, should also work on Ubuntu)
Attention: This will delete all existing databases, so best do this with a fresh installes MySQL Server.
Or do a dump of all databases first and pray that everything will work after:

Stop the MySQL Service:

sudo service mysql stop

Delete the database directory:

sudo rm -rf /var/lib/mysql

Create an new database directory:

sudo mkdir /var/lib/mysql

Change the owner of the new Database Directory to the MySQL user

sudo chown mysql:mysql /var/lib/mysql

Give the MySQL user read/write/execute rights:

sudo chmod 700 /var/lib/mysql

Alter the config file (should be: /etc/mysql/mysql.conf.d/mysqld.cnf)
Add following entry below the [mysqld] section:

lower_case_table_names = 1

Attention: Do not forget to start the editor (I am using VIM) as root or using SUDO
Then: Force MySQL to re-create the system tables with the setting lower_case_table_names:

sudo mysqld --defaults-file=/etc/mysql/my.cnf --initialize --lower_case_table_names=1 --user=mysql --console

Then: Start the service again.

sudo service mysql start

If the services starts without error everything should be ok .... but: Deleting and re-creating the System tables deleted the root password for the MySQL Server.
mySQL Server created a new temorary root password, which you can extract using:

sudo grep 'temporary password' /var/log/mysql/error.log

Now we need to change the password to the new one:
Don't try with MySQL Workbench or such tools, it quitted with an error when I tried.

Use

sudo mysql -u root -p

and enter the temp password:

Change the password using:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword;

Last step:
Check wheather you have been sucessfull:

SHOW VARIABLES LIKE 'lower_case_%'

And you should get something like:
lower_case_table_names = 1

Then I was able re-import the dump and start teh programm.
I tested it and ... another crash in a query:
It was a complex query using GROUP BY statements, HAVING statements and UNION statements.
Typing the query in MySQL Workbench connected to Windows MySQL it worked, connected to Linux MySQL it crashed.

MySQL has several settings determining how strict SQL codes are handled.
It seems, that the default setting in a Windows installation is different to the defaults of a Linux installation:

You can ask MySQL for the settings using:

SELECT @@GLOBAL.sql_mode

What I did:
I took the settings I retrieved from my Windows installation and added these in the config file of the Linux installtion:
( /etc/mysql/mysql.conf.d/mysqld.cnf)
Put the results of the above mentioned select in the config file below the [mysqld] section in that way:
(This is only a example:

sql-mode="STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION"

Result:
If you transfer an existing MySQL database from Windows to Linux make sure:
1.) lower_case_table_names is set to 1 on the Linux machine

2.) the sql mode settings on the linux machine should be the same as on the windows machine (or vice versa if you go from Linux to Windows)


Hope this will help someone else.

Regards


Stefan.

I hope that I will never need it, but...

WOW
and THANK YOU

Hi Stefan,

thank you very much for the detail analysis and solution.
And to think that I avoided PostgreSQL because of lowercase vs uppercase issues.

* Of course these issues are a lot more complicated in PostgreSQL

Regards
Steven Sitas

Thank you!

what linux distro are you using ?

Hi ccc,

this was ubuntu.

Hello,

I have been using MySQL/MariaDb in Ununtu for a year now but have never had to wrestle setting it up to the extent you have had to. I salute you for your efforts.

When it comes to non-Windows platforms we need to understand one very fundamental difference and that is that every file created is linked to the user/owner.

I generally user the following command(s) to install MySQL/MariaDB:

sudo apt install mariadb-server

After installation is complete Secure MySQL/MariaDB

sudo mysql_secure_installation

To secure MySQL/MariaDB do the following

Enter current password for root (enter for none):   (safely press Enter)
Switch to unix_socket authentication [Y/n]          (Press "Y")
Change the root password? [Y/n]                     (Press "Y")
New password:                                       ("Enter new Password")
Re-enter new password:                              ("Re-enter new Password")
Remove anonymous users? [Y/n]                       (Press "Y")
Disallow root login remotely? [Y/n]                 (Press "Y") //If Press "N" if You want to access the database from a remote server

Remove test database and access to it? [Y/n]        (Press "Y")
Reload privilege tables now? [Y/n]                  (Press "Y")

Edit Configuration

sudo nano /etc/mysql/mariadb.conf.d/50-server.cnf

Once the file opens you need to locate the line where

collation-server = utf8mb4_general_ci

replace this line with following

collation-server = utf8mb4_unicode_ci

Now press Ctrl-S to Save and then Ctrl-X to exit.

And also locate my.cnf and edit the below configuration.

sudo nano /etc/mysql/my.cnf

Make sure your configuration has the below lines in the file.

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[mysql]
default-character-set = utf8mb4

Press Ctrl-S to Save and then Ctrl-X to exit.

Now MySQL/MariaDB setup is ready, restart this service.

sudo service mysql restart

That is it!

You are done!

--
Yogi Yang