Question :
I installed Squirel SQL on Ubuntu and a downloaded and put a MySQL driver into the lib folder of the Squirel folder. Then I chose this driver in the “extra class” tab when configuring Squirel MySQL driver. Connecting to the database and viewing data works but when I try to rename a column in Squirel it suggests Oracle-specific code to do it instead of a MySQL-specific variant. What did I do wrong? Doesn’t Squirel’s refactoring tools support MySQL or how do I configure it to properly rename columns? PhpMyAdmin automaticaly solves the task just fine.
This is the SQL that is shown when I click “show SQL” in the “modify column” menu:
ALTER TABLE some_column ALTER COLUMN "one_name" RENAME TO "another_name";
This doesn’t work. mySQL answers with incorrect syntax error.
Exact order of clicks to reproduce is:
right-click on a table in the left panel, then in the context menu “refactoring”->”column”->”modify column”. Then you choose the column and edit the name. Button “execute” leads to an incorrect syntax error and “show sql” gives me this sql.
Answer :
Works for me with the latest version of SQuirreL. Make sure that you change the dialect to MySQL in the Alter Column dialog. Its just below the column name.
OK – the syntax for changing a column name in MySQL is
ALTER TABLE table_name CHANGE old_col_name new_col_name COL_DATA_TYPE;
However, it appears that the syntax generated by SQuirreL SQL is
ALTER TABLE table_name ALTER COLUMN "old_col_name" RENAME TO "new_col_name";
I tried this with both the MySQL jdbc driver (from mysql.com) and the MariaDB driver (from mariadb.org) and it fails on both counts.
Indeed using the MySQL contextual menu (just above the Refactoring one you tried to use), it is also impossible to modify the column name.
So, indeed, it appears that you have discovered a bug in SQuirreL SQL. Congratulations!
You can, of course, use the SQL window to perform this operation (tested).
All I can say is that I’m surprised that SQuirreL SQL has such a flaw for a server as popular as MySQL.