Why it is important to use columns or aliases in MySQL Views

You can define a view in MySQL as follows:

 CREATE VIEW v AS SELECT  'MySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQL';

The above long string might give trouble reading, but ‘MySQLMy..

’ is 70 characters long. It will work fine, but check out how MySQL stores it (output of SHOW CREATE VIEW):

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost`
SQL SECURITY DEFINER VIEW `v` AS select
  'MySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQL'
  AS `MySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQL`

This is where the trouble starts. If you backup your schema using mysqldump, it will get the above definition, but when you restore it, you’ll get the following error:

ERROR 1166 (42000) at line 43: Incorrect column name
'MySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQL'

Why?

Quoting the MySQL Manual:

As of MySQL 5.1.23, aliases for column names in the SELECT statement are checked against the maximum column length of 64 characters (not the maximum alias length of 256 characters).”

Solutions

General good practice:

  • Use columns when defining the view: CREATE VIEW v (MySQLString) AS SELECT 'MySQLMy..
  • or use an alias: CREATE VIEW v AS SELECT 'MySQLMy..' ASMySQLString``

Both will result in the following being stored:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost`
  SQL SECURITY DEFINER VIEW `v` AS select
  'MySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQLMySQL'
  AS `MySQLString`

In my honest opinion, there should be a warning stating that the resulting alias is too long. This was discussed in bug #36287.

Note: If you got the above ERROR 1166 with your views, it means you never tested your backups! HA! Got ya!

Comments

Shlomi N.
Hi,

Good post.
The problem would also emerge if you're using replication; the slave will fail to replicate the query.