Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 8 of 8
  1. #1
    Senior Coder angst's Avatar
    Join Date
    Apr 2004
    Location
    Toronto, Ontario
    Posts
    2,114
    Thanks
    15
    Thanked 122 Times in 122 Posts

    Question moving column with invalid character

    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?

  • #2
    Senior Coder alykins's Avatar
    Join Date
    Apr 2011
    Posts
    1,806
    Thanks
    41
    Thanked 199 Times in 198 Posts
    is there data in it? can you do something like Script-table and then drop table, then create (via the script)?

    I code C hash-tag .Net
    Reference: W3C W3CWiki .Net Lib
    Validate: html CSS
    Debug: Chrome FireFox IE

  • #3
    Senior Coder angst's Avatar
    Join Date
    Apr 2004
    Location
    Toronto, Ontario
    Posts
    2,114
    Thanks
    15
    Thanked 122 Times in 122 Posts
    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.

  • #4
    New Coder
    Join Date
    Mar 2009
    Posts
    90
    Thanks
    5
    Thanked 14 Times in 14 Posts
    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)

  • #5
    Senior Coder angst's Avatar
    Join Date
    Apr 2004
    Location
    Toronto, Ontario
    Posts
    2,114
    Thanks
    15
    Thanked 122 Times in 122 Posts
    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 `TransactionsDROP COLUMN `IDEBIT_VERSION\`; 

  • #6
    Senior Coder alykins's Avatar
    Join Date
    Apr 2011
    Posts
    1,806
    Thanks
    41
    Thanked 199 Times in 198 Posts
    I don't know about MySQL syntax, but in a lot of languages \\ is the escape sequence for a single \

    I code C hash-tag .Net
    Reference: W3C W3CWiki .Net Lib
    Validate: html CSS
    Debug: Chrome FireFox IE

  • #7
    Senior Coder angst's Avatar
    Join Date
    Apr 2004
    Location
    Toronto, Ontario
    Posts
    2,114
    Thanks
    15
    Thanked 122 Times in 122 Posts
    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.

  • #8
    Senior Coder angst's Avatar
    Join Date
    Apr 2004
    Location
    Toronto, Ontario
    Posts
    2,114
    Thanks
    15
    Thanked 122 Times in 122 Posts
    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!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •