(at least since MySQL 5.1.42)
alter table t1 force;
Pretty neat huh? In fact, in Drizzle this will end up doing a copying alter table. Not useful.
There’s an over four year old bug report in MySQL (Bug#24091).
I’m just going to remove that bit from the parser in Drizzle – it makes no sense.
So, the MySQL (and Drizzle) ALTER TABLE syntax allows you to easily change the default value of a column. For example:
CREATE TABLE t1 (answer int); ALTER TABLE t1 ALTER answer SET DEFAULT 42;
So, you create a TIMESTAMP column and forgot to set the default value to CURRENT_TIMESTAMP. Easy, just ALTER TABLE:
create table t1 (a timestamp); alter table t1 alter a set default CURRENT_TIMESTAMP;
(This is left as another exercise for the reader as to what this will do – again, maybe not what you expect)
Here’s a nice challenge for you. What does the following do (or error out on?):
CREATE TABLE t1 (a int); CREATE TABLE t2 (b int); ALTER TABLE t1 RENAME t3, RENAME t2, RENAME t4;
I’d be interested to know what a) you think it does and then b) if you were surprised when you went and typed it into your RDBMS of choice.
A long time ago, in a time that can only serve to make some feel old and others older, MySQL didn’t support transactions. Each statement was executed as it went, there was no ROLLBACK (or COMMIT or crash recovery etc). Then there were transactions. Other RDBMSs implement auto_commit functionality, but for MySQL users, we think of it as the magic compatibility mode that (mostly) makes applications written for MyISAM magically work on InnoDB (okay, and making “you should use transactions” a really easy consulting gig :)
I’m currently working on finishing up a patch that removes the implicit COMMIT from DDL operations in Drizzle. Instead, you get an error message saying that Transactional DDL is not currently supported. I see a future where we have one of two situations (possibly depending on the storage engine): support DDL within normal transactions, DDL only transactions (cannot mix with DML). The latter (DDL only transactions) I see as the option for InnoDB/HailDB.
As anybody who has ever implemented a Storage Engine for MySQL will know, a bunch of the DDL calls got passed a parameter named “path”. This was a filesystem path. Depending on what platform you were running, it may contain / or \ (and no, it’s not consistent on each platform). Add to that the difference if you were creating temporary tables (table name of #sql_somethingsomething) and the difference if you were one of the two (built in) engines that were able to be used for creating internal temporary tables (temp tables that are created during query execution that do not belong in a schema). Well… you had a bit of a mess.
My earlier attempts involved splitting everything up into two strings: schema name and table name. This ended badly. The final architecture we decided on was to have an object passed around that would deal with various transformations (from what the user entered to what we can store on file systems, or to what temporary table maps to what unique name). This is TableIdentifier.
Brian has been introducing it around the code for a while now, and we just got it to now most of the places where table names are passed to Storage Engines. This means that if you’re writing a Storage Engine that doesn’t just blindly store things in files, you can sensibly use the getSchemaName() and getTableName() methods to call your API.