TYPO3 Core

typo: "It affects 4.2 as well 4.2 branches" -> "It affects 4.2 as well 4.3 branches. "

Start-of-line comments are handled correctly ("#"). Other comments are not really supported, also not after-a-line-comments.

1. no problem with this comment #
   CREATE TABLE user_test (
   uid int(11) NOT NULL auto_increment,
   pid int(11) DEFAULT '0' NOT NULL, # no problem here too
   tstamp int(11) DEFAULT '0' NOT NULL, # this is a problem
   )

So extensions having such non-supported stuff in their ext_tables.sql should be corrected, as this has never worked before.

If you think it is worth to make a new feature allowing more flexible comments in the sql file, you could provide a patch?

Hmm, so far I isolated bug triggering to this sql definition

CREATE TABLE tx_extkey_table1 (
uid int(11) unsigned NOT NULL auto_increment,
pid int(11) unsigned DEFAULT '0' NOT NULL, # comment;
PRIMARY KEY (uid),
);
CREATE TABLE tx_extkey_table2 (
uid int(11) unsigned NOT NULL auto_increment,
pid int(11) unsigned DEFAULT '0' NOT NULL,
PRIMARY KEY (uid),
);

Note semicolon in comment - it is important for trigering bug,
also tables should be from real extension - does not work with user_ key, probably it depends on TCA being in place for those tables.

For that definition in Install tool I'm getting this suggestion:
ALTER TABLE tx_extkey_table1 ADD CREATE TABLE tx_extkey_table2 (;

that is obviously incorrect

Ok, I unterstand now.

The parser from the install tool is pretty "dumb" when it comes to comments. It will get handed over an array of "SQL statements", which have been just split by semicolon in the original file: so if you have a semicolon in a comment you indeed have this problem.

So the bug report is justified and should be fixed. But someone needs to provide a patch (scratch the itch...).

Actually, when coming from SQL the only comment allowed starts with two dashes, so

-- this is a comment in SQL

Just as a note :)

from MySQL manual:

1.7.5.6. '--' as the Start of a Comment
Standard SQL uses the C syntax /* this is a comment */ for comments, and MySQL Server supports this syntax as well. MySQL also support extensions to this syntax that allow MySQL-specific SQL to be embedded in the comment, as described in Section 8.5, “Comment Syntax”.

Standard SQL uses “--” as a start-comment sequence. MySQL Server uses “#” as the start comment character. MySQL Server 3.23.3 and up also supports a variant of the “--” comment style. That is, the “--” start-comment sequence must be followed by a space (or by a control character such as a newline). The space is required to prevent problems with automatically generated SQL queries that use constructs such as the following, where we automatically insert the value of the payment for payment:

UPDATE account SET credit=credit-payment

Consider about what happens if payment has a negative value such as -1:

UPDATE account SET credit=credit--1

credit--1 is a legal expression in SQL, but “--” is interpreted as the start of a comment, part of the expression is discarded. The result is a statement that has a completely different meaning than intended:

UPDATE account SET credit=credit

The statement produces no change in value at all. This illustrates that allowing comments to start with “--” can have serious consequences.

Using our implementation requires a space following the “--” in order for it to be recognized as a start-comment sequence in MySQL Server 3.23.3 and newer. Therefore, credit--1 is safe to use.

Another safe feature is that the mysql command-line client ignores lines that start with “--”.