Database error 1366 while doing query Incorrect integer

Once you've installed phpList... ask questions here!
Forum rules
Please help the volunteers to help you by supplying the version of phpList you are using, browser & version and if possible, a link to your phpList installation.

Database error 1366 while doing query Incorrect integer

Postby storein » 6:11am, Sun 01 Sep, 2013

I have upgraded phplist from 2.10.17 to 3.0.2.

While i login the admin page, the phplist displays the following error notice:

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

The server is IIS8

How do I fix the issue to remove the error notice?

1.jpg
1.jpg (161.15 KiB) Viewed 16826 times



Thanks
storein
PL Nut
 
Posts: 43
Joined: 7:27am, Wed 17 Nov, 2010

Re: Database error 1366 while doing query Incorrect integer

Postby Sven2157 » 9:15am, Sun 01 Sep, 2013

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

There is no integer( number ) in that query. Since your last install is much older, the database has changed a bit and has a couple of times since. So the v2.10.17 data didn't get placed in the correct spot.

I know it is a pain, but try restoring your database and site to the previous version. Then do a progressive upgrade:
  • v2.10.17 to v2.10.19
  • v2.10.19 to v2.11.10
  • v2.11.10 to v3.0.0
  • v3.0.0 to 3.0.2.
Other than that, you're left with 2 choices: start a new database/message system : hand edit a SQL Dump to place the data back into the new tables.

Sven2157
Sven2157
PL Master
 
Posts: 170
Joined: 12:18pm, Fri 07 Jun, 2013

Re: Database error 1366 while doing query Incorrect integer

Postby Dragonrider » 9:23am, Sun 01 Sep, 2013

Try this page which may help, through it is for IIS7, not IIS8.
My sites:- http://wharfedalefestival.co.uk, http://ilkleygardeners.org.uk, http://emergencyaid.net, http://dragonrider.co.uk
Latest phpList version is now 3.0.12 (3 February 2015) and requires a minimum of MySQL 5.0 and PHP 5.3.x
Dragonrider
Moderator
 
Posts: 3460
Joined: 6:58am, Sun 02 Jul, 2006
Location: Ilkley, West Yorkshire, United Kingdom

Re: Database error 1366 while doing query Incorrect integer

Postby storein » 2:17pm, Sun 01 Sep, 2013

Sven2157 wrote:Other than that, you're left with 2 choices: start a new database/message system : hand edit a SQL Dump to place the data back into the new tables.

Sven2157


I installed a new phplist in XP+iis5.1 environment for test.
I found that the notice is displayed while i login the admin page.

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

Since it is a new database for phplist 3.0.2, i think there should not display the error notice.
storein
PL Nut
 
Posts: 43
Joined: 7:27am, Wed 17 Nov, 2010

Re: Database error 1366 while doing query Incorrect integer

Postby storein » 2:52pm, Sun 01 Sep, 2013

The error is from admin/mysql.inc file

return '<div id="dberror">Database error '. $errno.' while doing query '.$GLOBALS['lastquery']. ' ' .$msg.'</div>';

from the error notice, the information "$GLOBALS['lastquery']" was not displayed.

I didn't know the reason.
storein
PL Nut
 
Posts: 43
Joined: 7:27am, Wed 17 Nov, 2010

Re: Database error 1366 while doing query Incorrect integer

Postby Sven2157 » 5:02pm, Sun 01 Sep, 2013

storein wrote:The error is from admin/mysql.inc file

return '<div id="dberror">Database error '. $errno.' while doing query '.$GLOBALS['lastquery']. ' ' .$msg.'</div>';

from the error notice, the information "$GLOBALS['lastquery']" was not displayed.

I didn't know the reason.

Actually to read that properly, it is saying:
Answer back to the user -
admin/mysql.inc wrote:return

With a nice html coded, division -
admin/mysql.inc wrote: '<div id="dberror">

In human words, explain why this message is here -
admin/mysql.inc wrote:Database error

retrieve and display the actual ERROR, in a code -
admin/mysql.inc wrote:'. $errno.'

More human words, to explain what action threw this error -
admin/mysql.inc wrote:while doing query

Show the user, what was last queried( asked ) of the MySQL database, by calling from memory a global variable -
admin/mysql.inc wrote:'.$GLOBALS['lastquery']. '

Display a predefined message from error code -
admin/mysql.inc wrote:' .$msg.'

Close off the pretty html code -
admin/mysql.inc wrote:</div>';

Since it threw and actual error code, the best place to find the answer is over at mysql.com. They say this:
Error: 1366 SQLSTATE: HY000 (ER_TRUNCATED_WRONG_VALUE_FOR_FIELD)
mysql.com wrote:Message: Incorrect %s value: '%s' for column '%s' at row %ld

Translated:
Your Database wrote:Database error 1366 while doing query Incorrect integer( %s ) value: ''( %s ) for column 'editable'( %s ) at row 1( %d )

Two single quotes( '' ) mean empty, so they are there to show you, literally nothing. You can see those, but you can't really see a space. :wink:
So ... back to what I said before:
Sven2157 wrote: ...
There is no integer( number ) in that query. Since your last install is much older, the database has changed a bit and has a couple of times since. So the v2.10.17 data didn't get placed in the correct spot.
...

The real question is: What is trying to load, that is editable, when you acces your admin section :?: ... Since '?page=home' is the first thing loaded, after logging in, you might want to see which query is running, and what it is looking for, when it fails - 3 times in a row.

Answer: The campaign, subscriber and statistics information. :wink:
admin_home.jpg
admin_home.jpg (54.44 KiB) Viewed 16805 times



Sven2157
Sven2157
PL Master
 
Posts: 170
Joined: 12:18pm, Fri 07 Jun, 2013

Re: Database error 1366 while doing query Incorrect integer

Postby storein » 7:09am, Thu 05 Sep, 2013

Hi Sven2157,
Thank your patient reply.

The issue is related to mysql 5.6 version.
I use mysql 5.6 in windows, and the my.ini setting:
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

The setting "STRICT_TRANS_TABLES" is used.
If i remove the "STRICT_TRANS_TABLES" setting, it will be ok.

But i think it is not a better solution.

Then i find the below code in connect.php
if (!empty($configInfo['hidden'])) {
$editable = false;
}


Sine the editable field needs a integer value, the code should not use "false" value.
So I change the code as below:

if (!empty($configInfo['hidden'])) {
$editable = 0; //David updated it. it shoule be a integer value.
}


Then phplist works fine. two rows are insterted into config table.

I suggest phplist updates the code.

Thanks
storein
PL Nut
 
Posts: 43
Joined: 7:27am, Wed 17 Nov, 2010

Re: Database error 1366 while doing query Incorrect integer

Postby adub » 9:51pm, Thu 19 Sep, 2013

Thanks!
adub
phpList newbie
 
Posts: 3
Joined: 4:19pm, Thu 22 Aug, 2013

Re: Database error 1366 while doing query Incorrect integer

Postby clawrie » 12:17am, Tue 17 Dec, 2013

Hi
I've just installed the latest version of phpList on Windows Server 2008 and all is working apart from quite a few of these 1366 database errors.
I turned on debugging for one of the problem queries and what is being sent to mysql is:

Code: Select all
update phplist_list set name = "Technology Newsletter", description = "Sign up to our newsletter", active = "", listorder = 1, prefix = "", owner = 1, category = "abc" where id = 2


The active and field is specified as 'tinyint(4) in the db so it shouldn't be trying to update with a NULL string.
This comes from: function Sql_Query_Params($query,$params,$ignore = 0)

Any idea if this will be fixed?
The result in this case is we are not able to remove any of the lists from the public interface.

Thanks Craig
clawrie
phpList newbie
 
Posts: 2
Joined: 12:03am, Tue 17 Dec, 2013

Re: Database error 1366 while doing query Incorrect integer

Postby Guo » 7:29am, Wed 18 Dec, 2013

I have met this problem before.
it is because the configure of MYsql,
you need to find the my.ini
change sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
to sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
then restart your mysql server.
it will be ok.
Guo
phpList newbie
 
Posts: 2
Joined: 12:55am, Wed 18 Dec, 2013

Re: Database error 1366 while doing query Incorrect integer

Postby clawrie » 4:31am, Thu 09 Jan, 2014

Hi,
This is what I've had to do to eliminate the 1366 database errors (we can't turn off STRICT_TRANS_TABLE as there are many websites on our server).

Code: Select all
File: admin\connect.php
192c192
<     $editable = false;
---
>     $editable = 0;

File: admin\editlist.php
69c69
<     = ' update %s'
---
>     = ' update IGNORE %s'
79c79
<     = ' insert into %s'
---
>     = ' insert IGNORE into %s'

File: admin\spageedit.php
88c88
<   Sql_Query(sprintf('update %s set active = 1 where id = %d',
---
>   Sql_Query(sprintf('update IGNORE %s set active = 1 where id = %d',

File: admin\list.php
20c20
<     = ' update %s'
---
>     = ' update IGNORE %s'

File: admin\spage.php
9c9
<   Sql_Query(sprintf('update %s set active = 0',$GLOBALS['tables']['subscribepage']));
---
>   Sql_Query(sprintf('update IGNORE %s set active = 0',$GLOBALS['tables']['subscribepage']));
11c11
<     Sql_Query(sprintf('update %s set active = 1 where id = %d',$GLOBALS['tables']['subscribepage'],$sPageId));
---
>     Sql_Query(sprintf('update IGNORE %s set active = 1 where id = %d',$GLOBALS['tables']['subscribepage'],$sPageId));


Not sure if the subscribepage IGNORES are necessary.
Hope this helps
clawrie
phpList newbie
 
Posts: 2
Joined: 12:03am, Tue 17 Dec, 2013

Re: Database error 1366 while doing query Incorrect integer

Postby miks87 » 7:48pm, Wed 21 May, 2014

clawrie wrote:Hi,
This is what I've had to do to eliminate the 1366 database errors (we can't turn off STRICT_TRANS_TABLE as there are many websites on our server).

Code: Select all
File: admin\connect.php
192c192
<     $editable = false;
---
>     $editable = 0;

File: admin\editlist.php
69c69
<     = ' update %s'
---
>     = ' update IGNORE %s'
79c79
<     = ' insert into %s'
---
>     = ' insert IGNORE into %s'

File: admin\spageedit.php
88c88
<   Sql_Query(sprintf('update %s set active = 1 where id = %d',
---
>   Sql_Query(sprintf('update IGNORE %s set active = 1 where id = %d',

File: admin\list.php
20c20
<     = ' update %s'
---
>     = ' update IGNORE %s'

File: admin\spage.php
9c9
<   Sql_Query(sprintf('update %s set active = 0',$GLOBALS['tables']['subscribepage']));
---
>   Sql_Query(sprintf('update IGNORE %s set active = 0',$GLOBALS['tables']['subscribepage']));
11c11
<     Sql_Query(sprintf('update %s set active = 1 where id = %d',$GLOBALS['tables']['subscribepage'],$sPageId));
---
>     Sql_Query(sprintf('update IGNORE %s set active = 1 where id = %d',$GLOBALS['tables']['subscribepage'],$sPageId));


Not sure if the subscribepage IGNORES are necessary.
Hope this helps


Awesome man! Your code solved my problem. I wasn't even able to create new target lists because of this error.
Thanks a lot!
miks87
phpLister
 
Posts: 10
Joined: 10:49am, Tue 27 Nov, 2012

Re: Database error 1366 while doing query Incorrect integer

Postby WIRealtors » 8:14pm, Mon 05 Jan, 2015

Kudos for this fix, works beautifully.
WIRealtors
phpList newbie
 
Posts: 1
Joined: 8:12pm, Mon 05 Jan, 2015

Re: Database error 1366 while doing query Incorrect integer

Postby Dragonrider » 10:28am, Wed 28 Jan, 2015

For those who are not confident making edits to phpList core files, I've made the listed changes and have put them into a ZIP file.

Download the zip, unzip and simply upload to your lists folder, thus over writing your existing files affected. It contains admin folder and the 6 original files (*.org.php) as well as the 6 amended files (*.php)

lists/admin/files.php
Attachments
lists.zip
(75.34 KiB) Downloaded 206 times
My sites:- http://wharfedalefestival.co.uk, http://ilkleygardeners.org.uk, http://emergencyaid.net, http://dragonrider.co.uk
Latest phpList version is now 3.0.12 (3 February 2015) and requires a minimum of MySQL 5.0 and PHP 5.3.x
Dragonrider
Moderator
 
Posts: 3460
Joined: 6:58am, Sun 02 Jul, 2006
Location: Ilkley, West Yorkshire, United Kingdom


Return to Questions and Problems after Installing

Who is online

Users browsing this forum: No registered users and 2 guests