MySQL Bugs: #6438: Rename tables and columns

Bug #6438	Rename tables and columns
Submitted:	4 Nov 2004 16:59	Modified:	6 Nov 2004 4:27
Reporter:	Philipp Blanke	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Administrator	Severity:	S2 (Serious)
Version:	1.0.13	OS:	Linux (Gentoo Linux)
Assigned to:	Alfredo Kojima	CPU Architecture:	Any

Description:
Using the Gtk Version of MySQL Administrator, when using the "Edit table" function to alter a table, two things happen that are counter-intuitive.

1) When renaming the table from "xxx.old_name" to "xxx.new_name", the following error appears:
"Error retrieving information for table 'xxx.old_name'
The object was not found in the database."

When clicking "OK" and then again "Apply changes", the application crashes.

2) When altering a table column that has a defult value, such as a DATETIME column, the following error appears:
"Error executing SQL commands to update table.
MySQL Error Nr. 1064
You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near '-00-00 00:00:00' at line 1"

This error appears because the SQL syntax (that is shown in the preview window) is not correct because the DEFAULT value is not quoted. Changing the executed SQL command in the preview window does not help.
If I change the default value from 
0000-00-00 00:00:00  to  '0000-00-00 00:00:00'
in the table mask *before* i apply the change, the error does not appear, because the resulting SQL syntax is correct. 

How to repeat:
The error is easily repeatable:

Use the "Edit table" function to 
1) alter a table name or 
2) a column name (the column must have a default value).

Suggested fix:
1) After altering the table name in the database, the internal variable that stores the tablename must also be updated, so the renamed table is loaded.

2) Default values must be quoted in the query.
(Also, it should be possible to alter the query that is shown in the preview window. At the moment, it is just there to see what goes wrong, without the possibility correct it.)

Hi,

Thank you for the report, I tested your example with MySQL Administrator 1.0.14 and wasn't able to repeat the first error and application crash.
But I can reproduce the second error.

The problem with the quoted default values is, that if we add the quote automatically, you are not able to add NULL as the default value.

But we will add a feature that the '' are automatically created when you select the field and we will place the cursor into the ' | ' so you can immediately start to type.

Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html