Tag: mysql

MariaDB Case Sensitive Table names on Windows

Posted on by mario

I had an issue while migrating (My)SQL data back and forth from Linux and Windows. When ever I made come changes on my local windows dev system and tried to apply the changes to the production system the SQL statement failed. Later I noticed that the table names on Windows were no matter what I tried in lower case. The MariaDB docs told me that the table names on Windows where always lower case and case insensitive. What a bummer.

Searching I foundlower_case_table_names in the docs. With the values 0,1 and 2.

0 (Unix) = table names and aliases and database names are compared in a case-sensitive manner.
1 (Windows) = names are stored in lowercase and not compared in a case-sensitive manner.
2 (Mac OS X) = names are stored as declared, but compared in lowercase.

I tried 0 and got

[ERROR] The server option 'lower_case_table_names' is configured to use case sensitive table names but the data directory resides on a case-insensitive file system. Please use a case sensitive file system for your data directory or switch to a case-insensitive table name mode.
[ERROR] Aborting

But setting it to 2 works. No longer problems

[mysqld]
datadir=C:/Program Files/MariaDB 10.2/data
port=3306
innodb_buffer_pool_size=1003M
character-set-server=utf8
performance_schema = ON
lower_case_table_names = 2
[client]
port=3306
plugin-dir=C:/Program Files/MariaDB 10.2/lib/plugin

I wonder cause in the old days of using MySQL it never was a problem.

Akismet blows commentmeta wordpress

Posted on by mario

Today I noticed that the commentmeta table in the DB was huge, even the fact that I delete my spam daily. I saw that the commentmeta table had a size of 1,5 MB compared to the other tables with a few kb. I figured out that Akismet plugin caused that. I love that plugin cause it saved me a lot of time filtering the spam comments, but blowing my db is another thing. I have more than this blogs running with much more content. There is might be more trouble with the increase of the table.
However I executed the following query and the size of that table was only 7 kb. Woot!

DELETE FROM `wp_commentmeta` WHERE 
 `meta_key` = 'akismet_as_submitted' 
 OR `meta_key` = 'akismet_history' 
 OR `meta_key` = 'akismet_rechecking'
 OR `meta_key` = 'akismet_result' 
 OR `meta_key` = 'akismet_user' 
 OR `meta_key` = 'akismet_user_result';

MySQL UTF-8 fix Umlaute

Posted on by mario

Geht ziemlich schnell Sonderzeichen zu reparieren in MySQL! Scheiß Encoding! ;)

UPDATE `table` set
    `column`= REPLACE(`column`,"ß", "ß"),
    `column`= REPLACE(`column`, "ä", "ä"),
    `column`= REPLACE(`column`, "ü", "ü"),
    `column`= REPLACE(`column`, "ö", "ö"),
    `column`= REPLACE(`column`, 'Ä', 'Ä'),
    `column`= REPLACE(`column`, "Ãœ", "Ü"),
    `column`= REPLACE(`column`, "Ö", "Ö"),
    `column`= REPLACE(`column`, '€', '€');

Posts Tagged mysql

Archives by Month: