MySQL - SQL Mode + Timezone + Character Set

[pcr910303]

Use utf8mb4

Article URL: https://adamhooper.medium.com/in-mysql-never-use-utf8-use-utf8mb4-11761243e434

Comments URL: https://news.ycombinator.com/item?id=29907551

 

[Costas]

String data, right truncated: 1406 Data too long for column

SQL_MODE is used for getting MariaDB to emulate behavior from other SQL servers.

You can check the local and global value of it with:

SELECT @@SQL_MODE, @@GLOBAL.SQL_MODE;

you can set per session the SQL MODE as :

SET sql_mode = 'NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION';
SET GLOBAL sql_mode = 'NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION';

--

To set the (proper) SQL MODE globally, open the my.ini and add to mysqld tag the SQL MODE needed, most probably the following :

[client]
;password = your_password
port = 3306
socket = /tmp/mariadb.sock

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
; Remove the next comment character if you are not familiar with SQL
;safe-updates


[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[mysqld]
port=3306
sql-mode="NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION"

the use :
the my.ini must be one folder back by BIN folder
the data folder must no containing any my.ini

restart the server

ref - https://mariadb.com/kb/en/sql-mode/
ref - https://www.educba.com/mysql-super-privilege/

 

[Costas]

Set MySQL timezone

[mysqld]
port=3306
sql-mode="NO_ZERO_IN_DATE,NO_ZERO_DATE,NO_ENGINE_SUBSTITUTION"
default-time-zone='+00:00'

--then you can see the changes
SELECT @@SQL_MODE, @@GLOBAL.SQL_MODE;
SELECT @@time_zone, @@GLOBAL.time_zone;

if this doesn't work, through mysql.exe, run :

SET GLOBAL time_zone = '+8:00';

ref [2]