MySQL – My Brain BLOG

How to Fix the Collation and Character Set of a MySQL Database

Sources: mostly here and not so much here.

Something about MySQL being swedish and having a default character set of latin1 and a collation of latin1_swedish_ci which is no good if you need to save utf8 data.

Data size troubles 1406

When the scripts start compaining, “[Native code: 1406] [Native message: Data too long for column …]”, check how much data you are actually trying to squeeze into a single field.
source: maximum length of data I can put in a BLOB column in MySQL?

BLOB ≈ 64KB,
MEDIUMBLOB ≈ 16MB and
LONGBLOB ≈ 4GB

Also: “The maximum size of a BLOB or TEXT object is determined by its type, but the largest value you actually can transmit between the client and server is determined by the amount of available memory and the size of the communications buffers.”
https://dev.mysql.com/doc/refman/8.0/en/storage-requirements.html

Row size too large (> 8126)

Error Code: 1118
Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

See the possible solutions here:
https://mariadb.com/kb/en/troubleshooting-row-size-too-large-errors-with-innodb/

Get your ‘create table’ sql code and add:

SET GLOBAL innodb_default_row_format='dynamic';
SET SESSION innodb_strict_mode=OFF;

MySql Query cache on Windows

Slow server? Check if your query cache is set by querying:

mysql> show variables like 'have_query_cache';
 +------------------+-------+
 | Variable_name | Value |
 +------------------+-------+
 | have_query_cache | NO  |
 +------------------+-------+

How to enable this?
Find your my.ini … in my case in: C:\ProgramData\MySQL\MySQL Server 8.0
Add under [mysqld]

query_cache_size = 268435456
query_cache_type=1
query_cache_limit=1048576

More info here: https://dev.mysql.com/doc/refman/5.6/en/query-cache-configuration.html
And as you will notice this is 5.6 documentation. In my case this does not work, because I am using MySQL8.0 …. The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0.

The search to speed up my server continues….

MySQL user not connecting

Problem:
Windows Server 2016, IIS Website, Setup a MySQL server/database, setup a WordPress website, quickly connect with root and root-password. Everything works fine!

Now you want things more secured, so you add a MySQL user with a password and rights to only the wordpress database. Change wp-config.php with the new login and you get a “Error Establishing a Database Connection” when accessing the WordPress website or admin.

Change back to root and the problem is solved. How to fix this?