After importing an Excel table that contained some special characters (like carriage returns or line feeds) in the headers row, it seems that the phpMyAdmin utility handled this situation silently by inserting those chars in the field's name.
The problem arose later when I tried to import the table into other environments/tools like data integrators, etc. For example, the column "Date Start" was imported into the table as "Date\nStart", with a LINE FEED in the middle.
The field rename operation through phpMyAdmin fails with this error:
**\#1054 - Unknown column 'Date Start' in 'mytable'**
The obvious workaround would be to edit the original Excel file by hand (removing LF's) then reimporting the table in MySql as before, but I'm in the position of needing to refresh the schema while preserving the data in the table.
Next I tried this from an SQL panel in phpMyAdmin (note the \n in the field name, VARCHAR(16) is just an example, DATETIME or INT should work as well):
ALTER TABLE mytable CHANGE `Date\nStart` `Date Start` VARCHAR(16)
but again it gives error #1054 - Unknown column 'Date\nStart' in 'mytable'
I also checked the INFORMATION_SCHEMA db, but as @Steve stated below, it's a read-only database.
I'm using MySql 5.5.32 and phpMyAdmin 4.0.4.1 with a Win7 desktop. Any suggestions?
I thought you couldn't write to INFORMATION_SCHEMA because of a permission issue, but after reading the MySQL Manual I realise this is expected behavior as the manual states:
Although you can select INFORMATION_SCHEMA as the default database with a USE statement, you can only read the contents of tables, not perform INSERT, UPDATE, or DELETE operations on them.
To achieve a table rename by using the RENAME TABLE command, first run a select query to find all the tables that need changing and then rename them replacing the carnage return with a space character.
To rename just a column from within a table the ALTER TABLE command can be used with the CHANGE COLUMN parameters, for example:
ALTER TABLE table_name CHANGE COLUMN 'Date\nStart' 'Date Start' DATETIME
I know you've already said that is the command you need, so I've tested this myself by firstly selecting the tables and then running the ALTER TABLE command and it worked fine. I was using the command line interface, so maybe the problem lies with phpMyAdmin - can you confirm it isn't encoding or escaping \n?
Here is what I tested via the command line and worked OK:
SELECT COLUMN_NAME
FROM `INFORMATION_SCHEMA`.`COLUMNS`
WHERE TABLE_SCHEMA = 'test_345'
AND TABLE_NAME LIKE '%\n%';
ALTER TABLE test_table1 CHANGE COLUMN 'Date\nStart' 'Date Start' DATETIME;
Either of these could be wrapped up into a routine should you think this would be useful in the future.