MySQL Database scripts - Replace blocks statements fail

unable to reporduce... setting to won't fix..

I get the exact same error as well as missing the left and right column. By navigating directly to the block positioning admin screen I was able to activate the the left and right columns.

Since I get all sorts of other strange behaviour, I can not be sure whether this is related to the initial MySQL error, which makes trouble shooting very cumbersome.

Setting this to CVS as the same statements still exist in database.mysql.

MySQL 5 strict mode issue?

-K

Yes, strict mode issues: Error 1364 upon importing database.mysql with MySQL 5.0+

I always thought it's idiotic to use REPLACE there. I will roll a patch later.

This is painfully simple, indeed. Am I missing something? Is there any reason we used REPLACE?

I have been informed that 'pages' still needs an explicit value in the insert statement, though chx and I are puzzling over this. We provide a default in the table creation script, and the simple INSERT statement seems to work on both my and his mysql setups, even when running in STRICT.

Can anyone apply this and see if they continue to encounter the problem?

no problems on mysql 4.1.5

oh there is a patch here eh?

I am running STRICT_ALL_TABLES and yet i was able to create text and blob fields with default values. And also, even the REPLACE version imported for me. It seems like I need to spend more time with the mysql manual.

Why would pages need anything other than an empty string?

Here is eaton's patch plus a change to the 'pages' field so that it can be NULL. I see no reason to prevent that.

I agree with chx and eaton, why do we need replace at all?

I'm OK with Moshe's patch. Let's mark this RTBC if no one objects within a day or so.

moshe, there are no nulls in drupal if i remember correctly.

I've certainly used NULLs in my modules. Really, this convention we have of NOT NULL DEFAULT '' is wordy and not needed. If someone objects to this patch, please do so quickly :)

Allowing NULL values should be used where useful, but I don't think this is such a case. Just put in an empty string for pages.

i would state it differently - empty strings should only be used when useful - otherwise use NULL. to me that is simplest.

anyways. let's get this in... :)

You should not use both '' and NULL to mean "no value" at the same time, unless you want to write WHERE pages = '' OR pages IS NULL...

So, should it default to NULL or to ''? The patch uses pages text DEFAULT '' NULL which is weird and in fact defaults to NULL (tested on 4.1.11).

If we assume it's NULL, what about existing entries in the {blocks} table? They use ''. Update function should be written in such case to change them to NULL. Also existing code should be reviewed to check if we need some IS NULL or IS NOT NULL.

Maybe it's be better to leave it as it is, i.e. use empty string ('')?

If this have been taken into consideration already, then I'm sorry for being overcautious. Please tell me so and I'll add database.pgsql changes.

None of block.module has special handling for NULL values for pages, to do that would add a lot of code. The default should be what happens if these blocks had been inserted manually, which is an empty string.

Looks ok now. Postgresql does not need any changes in this case.

I agree with Moshe that NULL is semantically more correct. Would proper NULL handling add that much extra code?

I've committed it. Whether we shoudl use NULL or not should be decided at large and is not part of this issue.

We should use NULL, but yes, it can be fixed later (I guess).

Importing a fresh 4.6.6 database.mysql, the blocks REPLACE statements still failed for me. Also, two REPLACE statements on the variable table failed as well.

This is my development box at work which happens to be running Windows XP Pro at the moment. I'm running MySQL Server 4.1.16-nt, MySQL Client 5.0.11 using MySQL Administrator's "Restore" feature to import the database.

Warning: Do not know how to handle this statement at...
line 799: REPLACE variable SET name='update_start', value='s:10:"2005-03-21";';
line 800: REPLACE variable SET name='theme_default', value='s:10:"bluemarine";';
line 802: REPLACE blocks SET module = 'user', delta = '0', status = '1';
line 803: REPLACE blocks SET module = 'user', delta = '1', status = '1';

Re-running the queries as INSERT INTO variable () VALUES() runs without errors.

INSERT INTO variable (name, value) VALUES('update_start', 's:10:"2005-03-21";');
INSERT INTO variable (name, value) VALUES('theme_default', 's:10:"bluemarine";');

INSERT INTO blocks (module, delta, status) VALUES('user', '0', '1');
INSERT INTO blocks (module, delta, status) VALUES('user', '1', '1');

The blocks failing isn't such a big deal because you can just go and re-enable them. The variables failing is rather important because when you go to "update.php", it will not know where to start doing updates and run everything from the beginning of time which corrupts the database.

Why are the values being REPLACE'd when this is a fresh install of the database?

I have my own opinions about the NULL/NOT NULL but that I think is not central to this issue and is something that should be tackled in Drupal as a whole.

the patch was comitted.

Why was this critical? it apparently only affects people running a beta(?) release of mysql in a particular strict mode.

I'm only curious - but why the REPLACE is failing and INSERT is not?

I added the pages column to the insert statement.