[Eestlane]

Aside from other weird problems I have with Apache 2.2.11 + PHP 5.3.0 + MySQL 5.1.36 (or 5.1.40) I have following issue.

If making insert with $db->query, i.e Dragonfly admin message:

PHP:

$db->sql_query("INSERT INTO ".$prefix."_message (mid, title, content, date, expire, active, view, mlanguage) VALUES (DEFAULT, '$title', '$content', ".time().", $expire, $active, $view, '$language')"); 

The mid will be (tried to be) valued as 0, doesn't matter if 0 exists or doesn't exist (auto_increment starts by default with 1 anyway). The table is correct however, because not giving the default values at all, works:

PHP:

$db->sql_query("INSERT INTO ".$prefix."_message (title, content, date, expire, active, view, mlanguage) VALUES ('$title', '$content', ".time().", $expire, $active, $view, '$language')");

I actually think the last one is even better, as giving default values to undefined field names is automatic anyway, however the issue is, why doesn't it work with my setup?

Anyone else using same or close-to version of apache, php and mysql? I'm using wampserver, so they're on Windows XP SP2.

[layingback]

I have Apache 2.2.12 PHP 5.3.0 MySQL 5.1.37 - XAMPP on Ubuntu. Close enough?

If so, what test do you want me to run?

[Eestlane]

For example just adding a message from administration->Messages->Messages. To be sure add two messages (without deleting the first one first) and see if both will be added.

[layingback]

Seemed Ok. There was 1 message (mid = 1) already. Both mid 2 and mid 3 added without issue.

Guess you need someone on M$ OS?

[Eestlane]

Thanks for testing.

I assume the problem is something on my end, maybe I've messed up something somehow.

[Eestlane]

Found the issue. If CPG_DEBUG has to be set to false in config.php.
I've not trailed down yet what exactly caused the real issue, however this was also what caused ALL my other issues (for example dragonflycms.org/Proje.../id=1103/)

[Eestlane]

Strip the "If" from my second sentence, I reconstructed the sentence while I wrote it.

Anyone can test if the issue will occur when setting CPG_DEBUG to true.

[Eestlane]

Seems it was introduced with the latest includes/db/mysql.php

dragonflycms.org/cvs/h...=9.28-9.27

[Eestlane]

You can further discuss it here:
dragonflycms.org/Proje...s/id=1112/

[InspectorClueNo]

http://dragonflycms.org/Projects/bugs/id=1112/ wrote:

I'm not familiar with MySQL ISO standards, so why does debug mode need to be compliant with SQL-92 strict mode exactly?

- The only way to support multiple RDBMS is to stick with a standard.
- CPG_DEBUG is normally enabled in development stage or for general debugging porpoises.

So why so strict when CPG_DEBUG is enabled?
I think it should give the opportunity to get the code compliant while still keep the code working on production sites where CPG_DEBUG is disabled?

PostgreSQL will yell the error even when disabling CPG_DEBUG so this is a MySQL thing only since we all dev for MySQL.
Something that I want to point out is that PostgreSQL is just a tiny bit slower but way more scalable then MySQL.

[DJ Maze]

DEFAULT is the problem

Code:

VALUES (DEFAULT

DEFAULT takes the default value for the column as defined in the table field.
When a column is AUTO_INCREMENT de default should be NULL not 0.

In ISO SQL; 0, -1, -2 etc. are valid entries (MySQL bug here) So it tries to insert 0.

Either set default to NULL or set the value to NULL

PHP:

$db->sql_query("INSERT INTO ".$prefix."_message (mid, title, content, date, expire, active, view, mlanguage) VALUES (NULL, '$title', '$content', ".time().", $expire, $active, $view, '$language')");

According to the installer:

Code:

$tables['message'] = array(
	'mid' => array('Type' => 'SERIAL4', 'Null' => 0),

According to the mysqli_mngr.php:

Code:

'SERIAL4' => 'INT NOT NULL AUTO_INCREMENT'

The only real solution:
Database table should be fixed

Code:

ALTER TABLE cms_message MODIFY COLUMN mid INTEGER  NOT NULL DEFAULT NULL AUTO_INCREMENT;

So... InspectorClueNo is right CPG_DEBUG should stay on

[Eestlane]

This actually means editing lots of Dragonfly code.

[DJ Maze]

no, this means editing the database

[layingback]

As in updating via installer & cpg_inst.php's? As it affects several, if not all, modules. Eg. Pro_News seems to have copied News with auto_increment fields set to Null: No, Default: None.

Still a lot of code changes, even if neatly compartmentalised in install routines.

I guess my memory must be wrong on this, but I thought STRICT_TRANS_TABLES required '0' for Integer fields not NULL? Or you got cpg_inst/installer SQL errors?