View Issue Details

IDProjectCategoryView StatusLast Update
0017148phpList 3 applicationInstallationpublic21-06-18 14:05
ReporterJohnPr Assigned To 
PrioritynormalSeverityminorReproducibilityalways
Status newResolutionopen 
PlatformUnixOSFreeBSDOS Version10
Product Version3.0.5 
Summary0017148: Database error 1366 - STRICT_TRANS_TABLES
DescriptionFirst install of PHPlist, so no upgrade. Latest MySql. Latest FreeBSD.

With this I get 3 (the same errors): Database error 1366 while doing query Incorrect integer value: '' for column 'editable' at row 1

This shouldn't happen with a new install. And have not found a solution for this. And not sure what does and doesn't work.
Tagsneedinfo

Activities

michiel

09-04-14 20:40

administrator   ~0053472


what is the exact Mysql version?

JohnPr

11-04-14 23:11

reporter   ~0053489

I replied by mail, but it looks like this doesn't work as it does with support tickets.

I have MySQL version 5.6.17

duncanc

18-04-14 12:43

updater   ~0053534

A similar problem has been reported in the past in the user forums, see
http://forums.phplist.com/viewtopic.php?f=35&t=39526#p98892 to do with the mysql STRICT_TRANS_TABLES setting.

Possibly phplist should try to detect this setting, but there is possibly a bug in how the function Sql_Query_Params handles booleans. It generates '0' and '1' for boolean false and true, instead of 0 and 1, and that might be triggering the mysql error.

JohnPr

18-04-14 16:15

reporter   ~0053537

The problem can be solved by editing the five files as suggested here. Not sure if it is the right way, but it works.

http://forums.phplist.com/viewtopic.php?f=35&t=39526#p98892

michiel

16-07-14 10:57

administrator   ~0054281


will be good to get sorted but in order to work on that, I'd need to replicate the problem first. That means setting up a system with the Mysql version. At this moment that's not an option, but presumably that may be in the near future.

Even though they seem to work, I'm not in favour of the changes proposed in the forum post. Most of them are simply adding an "IGNORE" to the SQL which effectively makes the query stop doing what it should do and cause the DB to be out of sync

If someone who encounters this issue can find a solution that works without the IGNOREs that would be great. In the meantime anyone who has this problem can try the changes outlined in the forum post.

duncanc

06-07-15 13:03

updater   ~0056305

This problem has been reported again, on the user forum https://discuss.phplist.org/t/database-error-when-add-new-list/553

The underlying cause is that some code generates invalid queries by using a non-numeric value for an integer column (such as "" for the list active field). Without strict trans tables in effect, mysql seems to be tolerant of that.

gingerling

06-07-15 15:24

manager   ~0056310

Yes, if we can add a fix to this for 3.1 that would be cool

michiel

06-07-15 17:08

administrator   ~0056312

I have mysql Ver 14.14 Distrib 5.5.43, for debian-linux-gnu (x86_64) using readline 6.3

I have set STRICT_TRANS_TABLES in my my.cnf

mysql> use information_schema;
Database changed
mysql> select * from global_variables where variable_name = "sql_mode";
+---------------+---------------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+---------------+---------------------+
| SQL_MODE | STRICT_TRANS_TABLES |
+---------------+---------------------+
1 row in set (0.00 sec)

But I cannot replicate the problem. All Queries submit as usual.

Either it's something else causing this or the Mysql version has an influence.

Would be good if someone can replicate this on their system and find a good solution. Without replicating the problem I can't fix it.

duncanc

14-07-15 20:18

updater   ~0056433

I enabled strict trans mode by adding this line to admin/mysqli.php at the end of function sql_connect()

mysqli_query ($db,"SET SESSION sql_mode = 'STRICT_TRANS_TABLES';");

Then tried to add a list and this error was displayed

Database error 1366 while doing query Incorrect integer value: '' for column 'active' at row 1

The column "active" is defined as tinyint(4) but is being set to an empty string.

duncanc

14-07-15 22:29

updater   ~0056437

Looking back at the various reports the problem seems to be with booleans that are defined as tinyint and not null, and which are rendered as check-boxes.

If the check-box is unchecked then the insert/update statement has an empty string for the value, which is not compatible with the column definition.

There are only a few fields that are defined in this way, such as the active column on the list table.

A suggested solution is to validate whether the check-box has been checked, e.g.

            isset($_POST['active']) ? $_POST['active'] : 0,

This line in admin/connect.php is wrong though. The php false is converted to an empty string, which is not what is needed. A value of 0 should be used.

  if (!empty($configInfo['hidden'])) {
    $editable = false;
  }

michiel

15-07-15 08:12

administrator   ~0056438

yes, but if the query has

editable = %d

doesn't that convert the value to 0 or 1 anyway?

I have STRICT_TRANS_TABLES on now on my system, so I will stumble on these things. I think this particular one is resolved in latest GIT

michiel

15-07-15 11:23

administrator   ~0056446

Can someone check that it's resolved in this version:

https://sourceforge.net/projects/phplist/files/phplist-development/3.1.2/