Column names with trailing spaces in tables and views

Just found out a bizarre feature of MySQL: you simply cannot create a table with a trailing space in a column name. Something like this, doesn’t work:

create table a (`a ` int);
ERROR 1166 (42000): Incorrect column name 'a '


There’s a thread in MySQL’s lists explaining it

Ok, it seems there’s a technical limitation but this solution seems like a lazy workaround to me.
The problem is with CREATE VIEW. In recent MySQL builds you cannot either create a view with a trailing space in a column name. But recent means, at least, greater than 5.0.51a, cause this version DOES let you create a view with a trailing space.

# mysql --version
mysql Ver 14.12 Distrib 5.0.51a, for debian-linux-gnu (x86_64) using readline 5.2

mysql> create view b as select a `a ` from a;
Query OK, 0 rows affected (0.00 sec)


There’s a big problem here: 5.0.51a is the default and supported version in Debian Lenny! So, if you have Lenny as a master of a more recent MySQL installation, be careful cause a CREATE VIEW with a trailing space in a column name will break your replication. Grrrr. MySQL, I hate you, really.

MySQL slaves and the “corrupted” relay log/binlog problem

When trying to pull up to date a new slave, maybe using a binlog server (a MySQL server with no true tablespace, just acting as master for slaves, feeding them with replication data), you may encounter an error like this in your mysql.err file, after noticing the replication has just broken

091028 11:32:49 [ERROR] Slave SQL: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Error_code: 1594
091028 11:32:49 [Warning] Slave: Field 'owner' doesn't have a default value Error_code: 1364
091028 11:32:49 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysqld-bin.000780' position 786513508


Well, everything on the web points to a “OMG, my file is corrupt!! My disk is freaking out!”. Probably, instead, it’s just simpler: you have forgotten a FLUSH LOGS; before backing up your binary logs from the original master. So, when you are recreating the binlog server from the backup, the final slave arrives to the last statement wich obviously is not complete/correct and booom, it stops.

The solution is to copy the correct and complete binlog file (in my case mysqld-bin.000780) again in the binlog server, then issue a
STOP SLAVE;
CHANGE MASTER TO master_log_file="mysqld-bin.000780", master_log_pos=786513508;
SLAVE START;


and… problem solved!
(remember to adjust file name and position to suite your needs)

Lesson learned (once again): always test your backups and do not hesitate to flush with MySQL :)