Announcement

#1 2020-10-17 17:49:03

Stephan67
Member
2020-10-17
1

Warning: [mysql error 1364] while adding an album

Hello,

now I have been using Piwigo for our family photo album for several years and hey! It's a cool software.
You almost earn admiration for the quick and beautiful presentation of your own photos.
And without the help I gathered from the forum during the installation/configuration, it wouldn't have been that easy.
Thank you therefore to all, in whose problems I was allowed to participate and thanks to the wise people, who help others here.
But now I can't find a solution by reading along. Until a few weeks ago, my Piwigo was running together with MySQL5.6 and PHP7. But various reasons called for a new server. I managed the move (also with the help of this forum) very well.

Piwigo 2.10.2 now runs under IIS on Windows Server2016 with MySQL8.0.21 and PHP7.4.8.
With the following plugins: Additional Pages, Admin Tools, Advanced Menu Manager, Community Contact Form, File Uploader, Force HTTPS, Grum Plugin Classes, LocalFiles Editor, Media Icon, PWG Stuffs

I thought that everything was great until yesterday when I wanted to add a new album (called 2020). The following error message appears:

Warning:  [mysql error 1364] Field 'id' doesn't have a default value

INSERT  INTO `piwigo_fotocategories`
  (`name`,`rank`,`global_rank`,`commentable`,`visible`,`status`,`id_uppercat`)
  VALUES('2020','0','6.0','true','true','private','15') in C:\inetpub\wwwroot\fotografien\include\dblayer\functions_mysqli.inc.php on line 865

Warning:  [mysql error 1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')
    AND status = 'private'' at line 3

SELECT id
  FROM piwigo_fotocategories
  WHERE id IN ()
    AND status = 'private'
; in C:\inetpub\wwwroot\fotografien\include\dblayer\functions_mysqli.inc.php on line 865


Fatal error: Uncaught Error: Call to a member function fetch_assoc() on bool in C:\inetpub\wwwroot\fotografien\include\dblayer\functions_mysqli.inc.php:926 Stack trace: #0 C:\inetpub\wwwroot\fotografien\admin\include\functions.php(2702): query2array() #1 C:\inetpub\wwwroot\fotografien\admin\include\functions.php(1578): add_permission_on_category() #2 C:\inetpub\wwwroot\fotografien\admin\cat_list.php(163): create_virtual_category() #3 C:\inetpub\wwwroot\fotografien\admin.php(309): include('C:\\inetpub\\wwwr...') #4 {main} thrown in C:\inetpub\wwwroot\fotografien\include\dblayer\functions_mysqli.inc.php on line 926

To this error I do not really find solutions, and/or I cannot implement solutions for me, because I'm not a programmer.
So I'm asking for your help. What can it be? What can I do?

Offline

 

#2 2021-06-27 21:29:59

zenon
Member
2021-06-27
4

Re: Warning: [mysql error 1364] while adding an album

This is a bug in piwigo. The column id is int(10) PRIMARY KEY, so it *must* have a unique integer value, but piwigo tries to insert data in the table without providing a value for id. This triggers the default to be used, which is NULL, which is not an integer, which very obviously must fail:

Code:

MariaDB [piwigo]> describe pw_history;
+-------------+-----------------------------------------+------+-----+------------+-------+
| Field       | Type                                    | Null | Key | Default    | Extra |
+-------------+-----------------------------------------+------+-----+------------+-------+
| id          | int(10) unsigned                        | NO   | PRI | NULL       |       |

There is a very simple solution for this:

Code:

MariaDB [piwigo]> ALTER TABLE pw_history  MODIFY id int(10) NOT NULL AUTO_INCREMENT;

Now we have

Code:

MariaDB [piwigo]> describe pw_history;
+-------------+-----------------------------------------+------+-----+------------+----------------+
| Field       | Type                                    | Null | Key | Default    | Extra          |
+-------------+-----------------------------------------+------+-----+------------+----------------+
| id          | int(10)                                 | NO   | PRI | NULL       | auto_increment |

This way the database will assign the first unused id to the new entry and the problem is solved. This needs to be fixed in the piwigo database creation sql scripts, and also on all upgrade sql scripts. It affects not only .HISTORY_TABLE., but also pw_users (making it impossible to add a new user from the admin interface) and perhaps other tables.

Code:

MariaDB [piwigo]> describe pw_users;
+--------------+-----------------------+------+-----+---------+-------+
| Field        | Type                  | Null | Key | Default | Extra |
+--------------+-----------------------+------+-----+---------+-------+
| id           | mediumint(8) unsigned | NO   | PRI | NULL    |       |

What is amazing is that this bug has lingered since 2018 [Github] Piwigo issue #950, although it took me less than an hour to identify its cause and solve it. Also strange is that I only run into the bug after I upgraded mariadb from 5.x to 10.5. Perhaps the database had a workaround to deal with this previously (e.g. "if PRI and NULL, then do AUTO_INCREMENT"), but I wouldn't know. Judging by google search results, the bug doesn't seem to have bitten many people, although it should have bitten every single piwigo installation. Mysterious are the ways of the SQL.


.

Last edited by zenon (2021-06-28 03:15:19)

Offline

 

#3 2021-06-28 02:37:10

Zentalquabula
Member
2014-05-10
217

Re: Warning: [mysql error 1364] while adding an album

Disable strict mode (set global sql_mode='';) or use MariaDB.

Offline

 

#4 2021-06-28 03:05:55

zenon
Member
2021-06-27
4

Re: Warning: [mysql error 1364] while adding an album

Disable strict mode (set global sql_mode='';) or use MariaDB.

I've been using mariadb.all along, both 5.x and 10.5. I run into this problem with mariadb.

Google will drive here people with the same problem. Please do not advise them to disable strict mode. It exists for a reason (in fact for many good reasons). The only correct and proper solution is that piwigo sets AUTO_INCREMENT when it creates the PRI ids. This is a piwigo bug, not a problem of the database, and it is extremely easy to fix. Weakening the database server (globally, also for all other piwigo-unrelated databases on the same server) instead of fixing the bug, is the absolutely and utterly wrong way to go.

Last edited by zenon (2021-06-28 03:07:16)

Offline

 

#5 2021-06-28 09:21:13

plg
Piwigo Team
Nantes, France, Europe
2002-04-05
13786

Re: Warning: [mysql error 1364] while adding an album

What's wrong with this SQL statement from database creation in Piwigo ?

Code:

CREATE TABLE `piwigo_history` (
  `id` int(10) unsigned NOT NULL auto_increment,
[...]
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM;

This table creation query was created by MySQL. So I really wonder where the bug is!

Offline

 

#6 2021-06-28 14:21:55

zenon
Member
2021-06-27
4

Re: Warning: [mysql error 1364] while adding an album

What's wrong with this SQL statement from database creation in Piwigo?

Nothing is wrong. That statement is exactly as it should be. But "ENGINE=MyISAM" gives me a pointer as to what might be wrong.

I installed piwigo on mariadb-5.5 (centos 7). I didn't notice the problem described here, but I can't say for sure whether there was indeed no problem, or whether there was one but I didn't notice it. Later I migrated the database to a mariadb-10.5 + galera cluster. Galera doesn't support myisam, so I converted all piwigo tables to innodb. Additionally, in a multi-master setting, autoincrement is not sequential, but interleaved.

Now then, I can't be sure about anything, this is only a hunch, but given that the creation sql correctly sets autoincrement, the most likely explanation of what I run into would be that something went wrong in the myisam->innodb conversion or that galera somehow reacted to the pre-existing sequential ids by turning off autoincrement. Neither of these two things makes much sense, but then neither does the fact that Stephan67 and I got bitten by this, while thousands of other piwigo users did not. It would take some testing to try to replicate the problem and identify where exactly it came from.

Question to Stephan67: were you by any chance using innodb?

Question to plg: myisam will still be around for many more years, but support for it is steadily diminishing. Wouldn't it make sense for piwigo to switch to innodb for new installations?

Offline

 

#7 2021-06-28 15:50:23

plg
Piwigo Team
Nantes, France, Europe
2002-04-05
13786

Re: Warning: [mysql error 1364] while adding an album

zenon wrote:

Later I migrated the database to a mariadb-10.5 + galera cluster. Galera doesn't support myisam, so I converted all piwigo tables to innodb.

I'm quite confident to declare that Piwigo is not compatible with innodb. So it seems that the bug is not really on Piwigo side :-)

zenon wrote:

Question to plg: myisam will still be around for many more years, but support for it is steadily diminishing. Wouldn't it make sense for piwigo to switch to innodb for new installations?

The reason Piwigo is still on myisam is performance. Last time I checked, InnoDB was much slower. I would be happy to see how they improved that. If not, I prefer to stick to MyIsam.

Offline

 

#8 2021-06-28 16:10:08

zenon
Member
2021-06-27
4

Re: Warning: [mysql error 1364] while adding an album

I walked through all my piwigo tables and compared them to install/piwigo_structure-mysql.sql. All auto_increment were gone except for those I fixed yesterday.

I created a test database, mysql -p pwtest < install/piwigo_structure-mysql.sql and then 'alter table piwigo_activity ENGINE=InnoDB;'. The table was correctly converted and auto_increment did not disappear.

I dumped the database with mysqldump and reimported it. The tables were dropped and recreated correctly and auto_increment did not disappear.

It looks like I'm chasing a ghost. Anyway, at least we can dismiss my previous statement that this is a bug in piwigo. It is not.

Offline

 

#9 2021-06-28 16:28:09

Zentalquabula
Member
2014-05-10
217

Re: Warning: [mysql error 1364] while adding an album

Whatever works with MyISAM works with InnoDB. MyISAM is faster on reading (as in a gallery), InnoDB on updating (as in a forum).

But Piwigo on Sqlite3 would be the real killer.

plg wrote:

I'm quite confident to declare that Piwigo is not compatible with innodb. So it seems that the bug is not really on Piwigo side :-)

The reason Piwigo is still on myisam is performance. Last time I checked, InnoDB was much slower. I would be happy to see how they improved that. If not, I prefer to stick to MyIsam.

Offline

 

Board footer

Powered by FluxBB

github twitter newsletter Donate Piwigo.org © 2002-2024 · Contact