Announcement

#1 2014-08-13 20:47:48

Nixblicker
Member
2014-08-13
7

[resolved] Multiple mysql errors alike "Warning: [mysql error 1054]..."

Dear forum members,

I spent the whole day to figure out what causes the mysql errors of the form

Code:

 
Warning:  [mysql error 1054] Unknown column 'nb_categories' in 'field list'

INSERT IGNORE INTO piwigo_user_cache_categories
  (user_id,cat_id,date_last,max_date_last,nb_images,count_images,nb_categories,count_categories)
  VALUES('1','2',NULL,NULL,'0','0','0','0') in /var/www/piwigo/include/dblayer/functions_mysql.inc.php on line 759

Warning:  [mysql error 1054] Unknown column 'last_photo_date' in 'field list'

INSERT IGNORE INTO piwigo_user_cache
  (user_id, need_update, cache_update_time, forbidden_categories, nb_total_images,
    last_photo_date,
    image_access_type, image_access_list)
  VALUES
  (1,'false',1407949863,'0',0,NULL,'NOT IN','0') in /var/www/piwigo/include/dblayer/functions_mysql.inc.php on line 759

Warning:  [mysql error 1054] Unknown column 'nb_available_tags' in 'field list'

UPDATE piwigo_user_cache
  SET nb_available_tags = '0'
  WHERE user_id = '1' in /var/www/piwigo/include/dblayer/functions_mysql.inc.php on line 759

...

on the new installation of piwigo on my debian wheezy home server. I have succesfully(?) applied the netinstall method after I'd fiddled around with my nginx webserver to start the process as adviced on your wiki pages. It completed without errors and let me enter and tweak the administration pages of my piwigo server without problems.

However, whenever I want to take a look at the gallery or direct my browser to the Piwigo base URL, the upper part of the screen is flushed with the above error messages. The rest of the page seems normal, including the display of the selected theme.

If it where only an (unpleasant) graphical issues, I most likely wouldn't describe it here in such length and wouldn't dare to bother you with this.
But I am unable to add any picture files to my database, and I suppose this is connected to the issue.

I already mentioned that I host piwigo myself - so I had to create a mysql database on my own. I did so by following the advices from here (http://dvpizone.wordpress.com/2014/03/1 … pberry-pi/)

Code:

# sudo mysql -u root -p
mysql> create database piwigo; grant all on piwigo.* to 'gallery'@'localhost' identified by 'PASSWORD'; flush privileges; \q;

, using my root mysql password at the respective entry of course. After the netinstall I was also able to list the tables included in the piwigo database. There were entries that seemed to be related to pwg.

Another post in this forum suggested that a local foto album directory stucture, symlinked to .../galleries and a click on the "Fast Synchronization" button on the administration page would suffice to include the fotos found beneath into the database.
Unfortunately, this doesn't seem to work at all - the button press redirects me to the "Tools" section stating that nothing new could be found.  User privileges of the symlink and the subtree are set to rw.

I assume this problem is related to the mysql issue, or that it must be solved before. But honestly, I have no real clue.

Does one of you have an idea what might be causing the hassle?
Any hint would be highly appreciated!

And sorry again for not being very concise.

Cheers and thanks,
Nix


Piwigo version: Piwigo 2.6.3
PHP version: 5.4.4-14+deb7u12
MySQL version: 5.5.38-0+wheezy
Piwigo URL: https://raspel.diskstation.eu/piwigo/

Offline

 

#2 2014-08-13 20:51:14

mistic100
Former Piwigo Team
Lyon (FR)
2008-09-27
3277

Re: [resolved] Multiple mysql errors alike "Warning: [mysql error 1054]..."

Hi

can you check the tables are compliant with their definition ?
http://piwigo.org/dev/browser/branches/ … l.sql#L347

if not, drop them and manually recreate them

Offline

 

#3 2014-08-13 21:01:22

Nixblicker
Member
2014-08-13
7

Re: [resolved] Multiple mysql errors alike "Warning: [mysql error 1054]..."

Hey mistic100,

thanks for the immediate reply - I fear I do not know how to perform any of your suggestions.
I am not very familiar with mysql or databases in general.

Was only able to list the tables in db piwigo as yet:

Code:

mysql> show tables;
+-------------------------------+
| Tables_in_piwigo              |
+-------------------------------+
| piwigo_caddie                 |
| piwigo_categories             |
| piwigo_collection_images      |
| piwigo_collection_shares      |
| piwigo_collections            |
| piwigo_comments               |
| piwigo_config                 |
| piwigo_download_sets          |
| piwigo_download_sets_images   |
| piwigo_favorites              |
| piwigo_group_access           |
| piwigo_groups                 |
| piwigo_history                |
| piwigo_history_summary        |
| piwigo_image_category         |
| piwigo_image_sizes            |
| piwigo_image_tag              |
| piwigo_images                 |
| piwigo_languages              |
| piwigo_old_permalinks         |
| piwigo_plugins                |
| piwigo_rate                   |
| piwigo_search                 |
| piwigo_sessions               |
| piwigo_sites                  |
| piwigo_tags                   |
| piwigo_themes                 |
| piwigo_upgrade                |
| piwigo_user_access            |
| piwigo_user_cache             |
| piwigo_user_cache_categories  |
| piwigo_user_feed              |
| piwigo_user_group             |
| piwigo_user_infos             |
| piwigo_user_mail_notification |
| piwigo_users                  |
+-------------------------------+
36 rows in set (0.01 sec)

Could you help with the compliance check?

Thanks alot!
Nix

Offline

 

#4 2014-08-13 21:02:53

mistic100
Former Piwigo Team
Lyon (FR)
2008-09-27
3277

Re: [resolved] Multiple mysql errors alike "Warning: [mysql error 1054]..."

I don't know the administrative commands of MySQL neither, I always use phpMyAdmin
Gotta figure it out by yourself or wait for someone else

Offline

 

#5 2014-08-13 21:14:35

Nixblicker
Member
2014-08-13
7

Re: [resolved] Multiple mysql errors alike "Warning: [mysql error 1054]..."

Ok, thanks anyway, mistic!

Have no phpMyAdmin or such tool installed and refrain from doing so as long as I am not in my home network.

Is there any way to recreate the whole database or restart the netinstall procedure without loosing the configuration changes done so far?

And one last question, if you allow, as I think you were the one with the symlink suggestion in the other post i mentioned.
Do you think this is related to my troubles including my symlinked local foto file structure, or do you have another suggestion regarding this issue?

Thanks a ton!
Cheers,
Nix

Offline

 

#6 2014-08-13 22:16:24

Nixblicker
Member
2014-08-13
7

Re: [resolved] Multiple mysql errors alike "Warning: [mysql error 1054]..."

Hey folks, me again -

If I grasped correctly what is the meaning of the database structure (linked by mistic100 above), I suppose the db entries were not fully compliant with their definition, indeed. Dunno what was causing it in the first place, but the requested entries like nb_categories etc. were not were they supposed to be:

For example, here

Code:

mysql> describe piwigo_user_cache_categories;
+--------------------------------+-----------------------+------+-----+---------+-------+
| Field                          | Type                  | Null | Key | Default | Extra |
+--------------------------------+-----------------------+------+-----+---------+-------+
| user_id                        | smallint(5)           | NO   | PRI | 0       |       |
| cat_id                         | smallint(5) unsigned  | NO   | PRI | 0       |       |
| date_last                      | datetime              | YES  |     | NULL    |       |
| max_date_last                  | datetime              | YES  |     | NULL    |       |
| nb_images                      | mediumint(8) unsigned | NO   |     | 0       |       |
| count_images                   | mediumint(8) unsigned | YES  |     | 0       |       |
| count_categories               | mediumint(8) unsigned | YES  |     | 0       |       |
| user_representative_picture_id | mediumint(8) unsigned | YES  |     | NULL    |       |
+--------------------------------+-----------------------+------+-----+---------+-------+

1 entry was absent.

Table piwigo_user_cache was even missing 3 entries.

For those interested, I added them according to the given definition, e.g.

Code:

mysql> alter table piwigo_user_cache_categories add column `nb_categories` mediumint(8) unsigned default '0';

Et Voila (sorry for the missing accents, guys ;) ), the mysql errors disappeared.

However the synchronization of the local foto data structure is still failing on me :(
Any clues/ideas to solve that?

Thanks and good night,
Nix

PS: I'll better mark this thread solved and open a new one for the file sync issue, ok?

Offline

 

Board footer

Powered by FluxBB

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