MySQL Store Failed Error

[JRutterford]

Environment:

Windows 2003 Server
IIS 6
PHP 5.0.5
MYSQL 5.0.15

Background:

I installed Mambo on Windows 2003 server and everything was working well apart from certain parts of the administration section. Certain areas just wouldn't let me update the database. I would get a SQL UPDATE or INSERT error. Keywords (for the aid of searching) of the error were: Store Failed Invalid Value. One such area was the banners. It was impossible to add or update a banner because Mambo was writing an empty integer value to the database. MySQL would just not allow empty values to be used when updating a record.

After lots of searching this forum and google I just couldn't find an answer. I then stumbled across a new inclusion in MySQL 5 that wasn't in earlier versions: STRICT_TRANS_TABLES. This setting does the following according to manual: Enable strict mode for transactional storage engines, and when possible for non-transactional storage engines.

What this setting does is actually tell MySQL how to handle input values that are missing or invalid. Mambo is full of SQL commands that contain missing values. This setting forces MySQL to not allow empty values in fields. Hence all my problems.

Solution:

Open MySQL Administrator and click on Startup Variables. Under the Advanced tab look for the SQL Mode field and remove the

STRICT_TRANS_TABLES,

text from the text box. You can leave any other options there.

[Lucho Paton]

I have the same problem...... thanks! by your solution, I work in same enviroment:

Windows 2003 Server
IIS 6
PHP 5.0.5
MYSQL 5.0.15

[nsemp]

Great,

This solved almost all my problems after the installation!!!

Thanks!!

[opa]

Great job,

I'm working some days on this problem. Your posting solved all my problems with the Mambo installation. After editing the mysql.ini I must restart the mysql-service. Now it works.

My configuration (only for tests and developing):

Win-XP
Apache 2.0.5
php 5.0.5
MySQL 5.0.15

Thanks

[sclarke27]

I ran into the exact same problems with the same setup. After removing STRICT_TRANS_TABLES from the sql-mode option in mysql.ini along with a mysql restart, my problems all went away. Many thanks for your solution and for saving me lots of time!

my mysql.ini entry. Note that lack of STRICT_TRANS_TABLES

# Set the SQL mode to strict
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Originally Posted by opa

Great job,

I'm working some days on this problem. Your posting solved all my problems with the Mambo installation. After editing the mysql.ini I must restart the mysql-service. Now it works.

My configuration (only for tests and developing):

Win-XP
Apache 2.0.5
php 5.0.5
MySQL 5.0.15

Thanks

[spollin]

Just wanted to quickly say thank you for this solution. I recently moved our company Intranet to a new server and upgraded to the latest version of MySQL but came across this issue when attempting to add new items. This solution fixed the problem perfectly. Thank you