BUG + SOLUTION: WB Installer error about Field 'value' doesn't have a default va

[DssTrainer]

This error I see was a problem back in 2007 and it still hasn't been addressed unfortunately in 2.8.1

The fact is that there are a few issues, and they are due to database compatibility with the latest mysql 5.x versions

The root causes...
In Mysql 4.x, auto-increment fields could be set to '' and `text` fields could be ignored
In Mysql 5.x, auto-increment fields must be set to NULL and `text` fields must be given a value on insert

The quick fix to fix all the errors, is to simply add:

Code:

mysql_query('set @@session.sql_mode="MYSQL40"');

in the database class in the connect() function right below this line:

Code:

$status = $this->db_handle = mysql_connect(DB_HOST, DB_USERNAME, DB_PASSWORD);

But the proper fix is below:

I've stepped through the installer in a php debugger and spotted many places a mysql_error is triggered, but there was no stop point so it just went until it got to the final error on the setting table for the 'value' field not getting an entry, which apparently is required for TEXT fields in 5.x mysql.

So here are the many fixes:
OPEN: install/save.php

ISSUE 1: home_folder needs a value

FIND (~LINE 559):

Code:

$insert_admin_user = "INSERT INTO `".TABLE_PREFIX."users` (user_id,group_id,groups_id,active,username,password,email,display_name) VALUES ('1','1','1','1','$admin_username','".md5($admin_password)."','$admin_email','Administrator')";

REPLACE WITH:

Code:

$insert_admin_user = "INSERT INTO `".TABLE_PREFIX."users` (user_id,group_id,groups_id,active,username,password,email,display_name,home_folder) VALUES ('1','1','1','1','$admin_username','".md5($admin_password)."','$admin_email','Administrator','')";

ISSUE 2: search_id in search queries should be NULL not ''

GLOBALLY REPLACE:

Code:

VALUES ('',

WITH:

Code:

VALUES (NULL,

The next problem that causes the dreaded Field 'value' doesn't have a default value is this line in the save.php file:

Code:

$database->query("INSERT INTO `".TABLE_PREFIX."search` (name) VALUES ('template')");

You are specifying the 'name' but no 'value'.
Replace it with:

Code:

$database->query("INSERT INTO `".TABLE_PREFIX."search` VALUES (NULL, 'template', '', '')");

CLOSE save.php


The next issue is for the mod_captcha ct_text not having a default value

OPEN: wb/modules/captcha_control/install.php

FIND AT THE END:

Code:

$database->query("
INSERT INTO `$table`
(`enabled_captcha`, `enabled_asp`, `captcha_type`)
VALUES
('1', '1', 'calc_text')
");

REPLACE WITH:

Code:

$database->query("
INSERT INTO `$table`
(`enabled_captcha`, `enabled_asp`, `captcha_type`, `ct_text`)
VALUES
('1', '1', 'calc_text', '')
");

CLOSE the mod_captcha/install.php file

The next one is in modules/code/install.php

OPEN: wb/modules/code/install.php

FIND:

Code:

$database->query("INSERT INTO ".TABLE_PREFIX."mod_code (page_id,section_id) VALUES ('0','0')");

REPLACE WITH:

Code:

$database->query("INSERT INTO ".TABLE_PREFIX."mod_code (page_id,section_id, content) VALUES ('0','0','')");

more to come tomorrow...

[testör]

I have MySQL version 5.5.8 here on my WAMP server and can't reproduce any errors with WebsiteBaker 2.8.2 RC3
http://www.websitebaker2.org/en/download/latest-version.php

Do you have perhaps more information which MySQL versions exactly have errors and where?
Unfortunately I can't really say sure I've got MySQL 5.5.8 - in php.info it says "mysqlnd 5.0.7-dev - 091210 - $Revision: 300533 $" but in several other sources like PHPMyAdmin it's "Server version: 5.5.8-log | MySQL client version: mysqlnd 5.0.7-dev - 091210 - $Revision: 300533 $".

[ruebenwurzel]

Hello,

described error messages comes from MySQL strict mode. Fix this error messages simply set MySQL to not use strict mode. WB 2.8.x will never be rewritten to work also with strict mode, most modules also did'nt work with strict mode. Maybe the WB 2.9.x will support strict mode in a common version.

Matthias

[DssTrainer]

According to mysql.com Both the 5.0 and 4.1 versions of mysql required 0 or NULL
http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html
http://dev.mysql.com/doc/refman/4.1/en/example-auto-increment.html

You can also explicitly assign NULL or 0 to the column to generate sequence numbers.

I know old versions of zen-cart and other scripts also used to use '' for autoinc fields as well, but can't find any reason why people were doing that as the documents don't mention that as a valid entry. At any rate, the documents above should be enough to prove that it should be fixed or at least add this to the core as a workaround:
mysql_query('set @@session.sql_mode="MYSQL40"');