moving column with invalid character

[angst]

hello, I made a typo while I was added a column to a table in mysql.

the column name is like "MyField\"

because of this typo I can no longer review the table in my editor. and I'm unable to remove the column using my client software or a linux console.


any ideas?

[alykins]

is there data in it? can you do something like Script-table and then drop table, then create (via the script)?

[angst]

there is no data in the column, any every attempt I've made to remove the column fails. is there maybe someway to escape the bad character? I can't really remove the table as it's the transactions table for a very busy web store.

[koko5]

Hi, you can get the exact column name via console and show create table query. Than run alter table in console using backticks around column name and escaping backslashes:

mysql> create table testtable(id int,`MyField\\` varchar(255));
Query OK, 0 rows affected (0.07 sec)

mysql> show create table testtable;
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| testtable | CREATE TABLE `testtable` (
`id` int(11) DEFAULT NULL,
`MyField\\` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table testtable change `MyField\\` MyField varchar(255);
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> show create table testtable;
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| testtable | CREATE TABLE `testtable` (
`id` int(11) DEFAULT NULL,
`MyField` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[angst]

I would do that if my column name has two slashes in it, but it only has one, which is what is causing the issue.

PHP Code:

mysql>  ALTER TABLE `Transactions` DROP COLUMN `IDEBIT_VERSION\`; 


[alykins]

I don't know about MySQL syntax, but in a lot of languages \\ is the escape sequence for a single \

[angst]

Yes, I've tried that as well.

still no luck, and now I also can't do a backup of the table or the database. it's causing the program to hang.

[angst]

Ok, i've finally got it sorted, from the command line I was able to make a backup of the table, I changed the table name in the sql file and uploaded it back into the database, I dropped the original table and changed the name of the new table to that of the old one and I'm back in business!

thanks for all your help!