Home > Uncategorized > A taste of MySQL wat…

A taste of MySQL wat…

25 June 2013 Comments Off on 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

Comments are closed.