On Sat, May 10, 2008 at 10:20 PM, <nad(a)svn.wikimedia.org> wrote:
> In MySQL the standard way of inserting data into rows exhibiting AUTOINCREMENT columns is simply to use a ''NULL'' value which will be ignored. In MSSQL however assigning a ''NULL'' to an ''IDENTITY'' column is not allowed, instead the best way is not to include those items in the list of columns to be updated at all.
>
> To get round this in the MediaWiki MSSQL layer, I've modified the insert wrapper in the ''DatabaseMssql'' class to check if the primary key is used in the insert and remove it if so. It checks this by assuming that the primary key will be of the same name as the table but with ''_id'' on the end, and that it will the first item in the list of columns to update.
It would be best to raise some kind of warning when this condition
occurs, preferably in the MySQL code path too, so that developers
using MySQL with notices enabled can notice and fix it. Just not
specifying the column to begin with works fine with MySQL too, so it
should be used if it's more compatible.
> MySQL implicitly casts NULL assignments to NOT NULL columns to an empty string or zero value accordingly, but MSSQL raises an error instead. This is a big problem within the MediaWiki environment because the code relies heavily on this implicit NULL casting. I've tried to get round the problem by replacing NULL's with empty strings from update and insert queries, and MSSQL is happy to cast the empty string to a numeric zero if necessary.
How does PostgreSQL handle this? If you know what type the column is,
you can cast it in the application logic. Again, it would seem better
to implement this checking in the cross-database code and raise
warnings if it comes up so it can be fixed on a case-by-case basis.
This doesn't work with MySQL strict mode either, which is the default
under some circumstances last I heard.