Dot column qualifier syntax error

Hi , our hosting provider upgraded sql server from 5.7 to 8.0.25-15, and some queries in several sites stopped working.

For example:
ALTER TABLE table1 CHANGE table1.column1 table1.column1 varchar(45) null

On 5.7 it was working properly, and now we got 1064 sytax error pointing to the very first dot in query. If I remove table name with a dot it works fine, but that’s not the solution.

I can “select” using table.column in query but not “alter table” - is it a bug? Can’t find anything about it in manual.

1 Like

Hello, this is not a documented or supported syntax but seems it was previously accepted as per MySQL :: MySQL 8.0 Release Notes :: Changes in MySQL 8.0.2 (2017-07-17, Development Milestone)
You will beed to rewrite alters with the proper supported syntax.

1 Like

From the notes:

Parser Notes
The parser rules for ALTER TABLE were refactored to be context independent and improve maintainability and extensibility. A resulting effect is that some previously accepted undocumented syntax variants are no longer accepted. For example, CREATE TABLE statements were permitted with column names qualified by the table name, or by the current database and table name, as were certain ALTER TABLE statements for which only column names are permitted. Such statements now produce an error.

What that says it that your example ALTER should have never worked in the first place and code was added to MySQL to prevent this non-standard. You will need to modify your SQL to follow SQL standards.

1 Like