Raphaël Raphaël - 5 months ago 28
MySQL Question

How to use ALTER TABLE in MySQL on a column named partition

I have a MySQL table with a column named partition. As it's a reserved keyword, I should be able to use

backticks
to use it in queries. It works with
SELECT
, but not with
ALTER TABLE
.

If I try :

ALTER TABLE `table` DROP `partition`;


or

ALTER TABLE `table` CHANGE `partition` `othername` INT;


MySQL complains with the same error :

Error code 1054: Unknown column 'partition' in 'partition function'


I tried in 'terminal', via MySQL Workbench or through Java
JDBC
, I always get the same error.

Any suggestion to get rid of that column (without losing / re-creating the whole table ...) ?

EDIT:
You can test it with a small table like that :

CREATE TABLE `testpart` (`id` int(10) unsigned NOT NULL AUTO_INCREMENT, `partition` smallint(6) NOT NULL, UNIQUE KEY `id` (`id`,`partition`)) ENGINE=MyISAM DEFAULT CHARSET=latin1 PARTITION BY HASH(partition) PARTITIONS 16;


Then try

ALTER TABLE `testpart` DROP COLUMN `partition`;

Answer

the first try is nearly correct, but you must say what to drop COLUMN. try this to delete them. Backticks are also working.

ALTER TABLE `table` DROP COLUMN `partition`;

here is the Manual page from Mariadb : https://mariadb.com/kb/en/mariadb/alter-table/

sample

i must add some infos to my answer:

  • the table has PARTITION

  • and the COLUMN that you want to change / remove is the KEY therefore

  • you must first remove the PARTITION before you can change them

  • the name of the COLUMN is a KEYWORD, so you must always quote it with backticks

create a table with 16 partitions

MariaDB [yourschema]> CREATE TABLE testpart (
    ->   id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    ->   `PARTITION` SMALLINT(6) NOT NULL,
    ->   UNIQUE KEY id (id,`PARTITION`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
    -> PARTITION BY HASH (`PARTITION`)
    -> PARTITIONS 16 ;
Query OK, 0 rows affected (0.12 sec)

now remove the paritions

MariaDB [yourschema]> ALTER TABLE `testpart` REMOVE PARTITIONING;
Query OK, 0 rows affected (0.35 sec)
Records: 0  Duplicates: 0  Warnings: 0

remove the (or change) the COLUMN

MariaDB [yourschema]> ALTER TABLE `testpart` DROP COLUMN `PARTITION`;
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [yourschema]>