TYPO3 Core

Here're some findings from Ernesto:

when changing the SQL-definitions of cache_hash and cache_pagesection:

CREATE TABLE cache_hash (
-  hash varchar(32) DEFAULT '' NOT NULL,
-  content mediumblob,
-  tstamp int(11) unsigned DEFAULT '0' NOT NULL,
-  ident varchar(20) DEFAULT '' NOT NULL,
-  PRIMARY KEY (hash)
+  id int(11) unsigned NOT NULL auto_increment,
+  identifier varchar(32) DEFAULT '' NOT NULL,
+  crdate int(11) unsigned DEFAULT '0' NOT NULL,
+  content mediumtext,
+  tags mediumtext,
+  lifetime int(11) unsigned DEFAULT '0' NOT NULL,
+  PRIMARY KEY (id),
+  KEY cache_id (identifier)
 ) ENGINE=InnoDB;

and:

CREATE TABLE cache_pagesection (
- page_id int(11) unsigned DEFAULT '0' NOT NULL,
- mpvar_hash int(11) unsigned DEFAULT '0' NOT NULL,
- content blob,
- tstamp int(11) unsigned DEFAULT '0' NOT NULL,
- PRIMARY KEY (page_id,mpvar_hash)
+ id int(11) unsigned NOT NULL auto_increment,
+ identifier varchar(32) DEFAULT '' NOT NULL,
+ crdate int(11) unsigned DEFAULT '0' NOT NULL,
+ content mediumtext,
+ tags mediumtext,
+ lifetime int(11) unsigned DEFAULT '0' NOT NULL,
+ PRIMARY KEY (id),
+ KEY cache_id (identifier)
) ENGINE=InnoDB;

The might work on a "fresh" TYPO3 install, but on an upgrade, this will
create the following SQL statements (COMPARE in Install Tool):

Add fields:

ALTER TABLE cache_hash ADD id int(11) unsigned NOT NULL auto_increment;
ALTER TABLE cache_hash ADD identifier varchar(32) NOT NULL default '';
ALTER TABLE cache_hash ADD crdate int(11) unsigned NOT NULL default '0';
ALTER TABLE cache_hash ADD tags mediumtext;
ALTER TABLE cache_hash ADD lifetime int(11) unsigned NOT NULL default '0';
ALTER TABLE cache_hash ADD KEY cache_id (identifier);
ALTER TABLE cache_pagesection ADD id int(11) unsigned NOT NULL
auto_increment;
ALTER TABLE cache_pagesection ADD PRIMARY KEY (id);

Changing fields:

ALTER TABLE cache_hash CHANGE content content mediumtext;
Current value: mediumblob
ALTER TABLE cache_hash DROP PRIMARY KEY;
ALTER TABLE cache_hash ADD PRIMARY KEY (id);
ALTER TABLE cache_hash ENGINE=InnoDB;
Current value: ENGINE=MyISAM

Executing that in that order will never add the "id" field, because of:

mysql> ALTER TABLE cache_hash ADD id int(11) unsigned NOT NULL
auto_increment;
ERROR 1075 (42000): Incorrect table definition; there can be only one
auto column and it must be defined as a key

This is clearly a limitation of our SQL-update-statement-generator, as
the correct order would be:

ALTER TABLE cache_hash DROP PRIMARY KEY;
ALTER TABLE cache_hash ADD id int(11) unsigned NOT NULL auto_increment
PRIMARY KEY;

(auto_increment has to be defined together with "PRIMARY KEY" for a field).

(issue imported from #M9639)