A taste of MySQL wat…
Here are just a few things that I have seen so far that make me …. wat… out loud.
mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | 123e45_table | +----------------+ 3 rows in set (0.00 sec) mysql> select * from 123e45_table; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '123e45_table' at line 1 mysql> wat... mysql> select * from `123e45_table`; Empty set (0.00 sec) mysql> select 123e45; +----------+ | 123e45 | +----------+ | 1.23e+47 | +----------+ 1 row in set (0.00 sec)
It turns out that MySQL interprets [0-9]+e[0-9]+ as scientific notation so make sure that you have your quotes.
Now for round 2….
mysql> create table test (`test `> column` int); Query OK, 0 rows affected (0.02 sec) mysql> show create table test; +-------+---------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+---------------------------------------------------------------------------------------------------+ | test | CREATE TABLE `test` ( `test column` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +-------+---------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
wat…
You saw right, MySQL does indeed allow for newlines in the table schema. Enjoy that one.
Off we go again for some more joy and fun!
mysql> create table test (`id` float); Query OK, 0 rows affected (0.02 sec) mysql> insert into test values(-0),('-0'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from test; +------+ | id | +------+ | 0 | | -0 | +------+ 2 rows in set (0.00 sec)
wat…
For me this one was particularly annoying as mysqldump does not quote numerical values magically turning a negative zero into a positive zero… TADA!!
Next up, replication…
slave1> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 127.0.0.1 Master_User: rsandbox Master_Port: 28376 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 6775 Relay_Log_File: mysql_sandbox28377-relay-bin.000487 Relay_Log_Pos: 6920 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: test Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 6775 Relay_Log_Space: 7131 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: 1 row in set (0.00 sec) slave1> drop database test; Query OK, 4 rows affected (0.04 sec) slave1> Bye master> create table test.new (id int); Query OK, 0 rows affected (0.02 sec) master> Bye slave1> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 127.0.0.1 Master_User: rsandbox Master_Port: 28376 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 6864 Relay_Log_File: mysql_sandbox28377-relay-bin.000489 Relay_Log_Pos: 251 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: test Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1049 Last_Error: Error 'Unknown database 'test'' on query. Default database: ''. Query: 'create table test.new (id int)' Skip_Counter: 0 Exec_Master_Log_Pos: 6775 Relay_Log_Space: 508 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1049 Last_SQL_Error: Error 'Unknown database 'test'' on query. Default database: ''. Query: 'create table test.new (id int)' 1 row in set (0.00 sec)
wat…
So, turns out that MySQL will only actually ignore queries on a database if the default databases has been set… very annoying when you don’t control the queries coming into the server.
Well I think that is enough wat for one day. Do you have more wat to share? Let me know!
Scott