zenPhoto Database problems

There is a big problem with zenPhoto and Database.

#1118 - Row size too large (> 8126). Changing some columns to TEXT or BLOB may help. In current row format, BLOB prefix of 0 bytes is stored inline.

... From the beginning:
I have more than 25.000 photos in my ZenPhoto-Installation. For few weeks it is beginning to have problems with the load of pages in the admin-area by editing the photos. It got worse over time. In the end, it ended up in the webpages no longer being completely loaded in the admin area. At this point, data from the database was no longer read completely and this had the effect that zenPhoto created the data of the photos for which no data was loaded in the database because of the on-the-fly. So the database was destroyed.

Since I have edited a backup of the database from the last photo, I have imported it into a test database as a test. I received the error code # 1118. The time can be limited to the database table 'images'. With this the import was canceled.

Error code # 1118 means that the rows are too big. The problem can be read here: "Row Size Too Large"
https://mariadb.com/kb/en/innodb-strict-mode/#row-size-too-large
There is also a solution to the import problem. With this solution it is possible to import a backup into the database, but the data from the database is still not completely displayed on the website.

My website has been completely broken for 2 days and I can't find a solution. The Searchengine Bots are already working hard on the Uprank of my website.

I tried a new installation on a new web space. I have problems with my backup in the new installation. I have a mysqldump * .sql file. Every time I import this into the database, the zenPhoto installation webpage is displayed and when I click on Run the database is emptied again.

So my question would be, how can I use a mysqldump * .sql backup file when reinstalling zenPhoto?

Comments

  • acrylian Administrator, Developer
    edited March 2020

    Sorry for your problems. I have not heard about this problem before. It would be useful to know what columns cause the problem actually. We'll look into the blob/text change.
    Note that we don't and can't test MariaDB specifially which should not be a problem though. MySQL's doc tell the same about this anyway.

    There are rough plans to move the image meta data columns to its own table some time in the future since the images table has quite a lot columns. I assume those may be part of this problem. Although that is not a priority as it affects quite a few other things and time is already a problem.

    So my question would be, how can I use a mysqldump * .sql backup file when reinstalling zenPhoto?

    If with "database is emptied again" you refer to the albums and images tables you don't have your albums folder with all albums and images in place as our documentation tells. Zenphoto is file system based so the database is matched to that folder and not vice versa (this is why you can upload via FTP). If that is not the case I cannot tell.
    Did you try Zenphoto's own database backup tool?

  • wibbi Member

    I have never used the zenPhoto internal database backup tool. I only found out about this today. It does not accept *.sql files. In this situation, creating a zenPhoto internal database backup makes no sense.

    25,000 photos are ~ 8GB. It's not just copied back and forth. It's also traffic. I installed a new zenPhoto on a new webspace. I also installed my database backup there. Now I wanted to combine the two, without photo files. Just for testing. That does not work? How can I disable this check?

    I could install a new zenPhoto in a subfolder in the existing webspace, but I cannot copy the photo files in this webspace because there is too much space. Moving would be an option. Or. Is there a way to adjust the path to the photo folders?

  • wibbi Member

    OK. albums folder are in the config file. or use symlinks.

    The next problem. My installation was an 1.5.0. Yet i have on the same webspace install a 1.5.6 and i become this message:

    PHP graphics image support
    Error!

    Your PHP graphics library does not support JPEG, GIF, or PNG
    The unsupported image types will not be viewable in your albums.

    To correct this you need to install GD with appropriate image support in your PHP

    I have no idea was is corrupt.

  • acrylian Administrator, Developer

    25,000 photos are ~ 8GB. It's not just copied back and forth. It's also traffic. I installed a new zenPhoto on a new webspace. I also installed my database backup there. Now I wanted to combine the two, without photo files. Just for testing. That does not work? How can I disable this check?

    Again, Zenphoot is file system based. If you don't want to copy everything you may not be able to exactly test it. If it is the same server you may adjust a relative album folder path within the config file to share the same folder. But the cache will be created on each install.

    PHP graphics image support Error!

    Sorry, I cannot help with this, this is a server side issue. Either the server has the support or does not. If it is the same server this is something your host has to look into.

  • wibbi Member
    edited May 2020

    I haven't been able to find the mistake yet. Because of the on-the-fly, the problem has become more complex. It was deleted from 25.000 records in the images table in about 15.000 and recreated with the on-the-fly. The deletion took place within 24 hours. Only the images table was affected. I found no changes in the code. The access_log shows no abnormalities. The IPs of the SFTP and SSH logins are all assigned to me. There is nothing to find that could explain the automatic deletion. There is only one thing left, why I ask here:
    zen-photo 1.5.0
    /zp-core/class-gallery.php
    // Check for the existence albums /* delete the dead albums from the DB */
    $sql2 = "DELETE FROM " . prefix('images') . " WHERE `albumid` IN $criteria";
    /* Delete all image entries that don't belong to an album at all. */ /* delete dead images from the DB */
    $sql = "DELETE FROM " . prefix('images') . " WHERE `albumid`='" . $firstrow . "'";
    // Then go into existing albums recursively to clean them... very invasive.
    Does this mean, or is it possible that images are deleted by the on-the-fly whose album, i.e. their directory, is not accessible? So if the directory on the server is unreachable, why should it be?

    At how many codepoints is this garbageCollect() defined and used? I have found this:

    ./zenphoto-fresh/zp-core/admin-refresh-metadata.php:    $imageid = $_zp_gallery->garbageCollect(true, true, $imageid);
    ./zenphoto-fresh/zp-core/admin.php: $_zp_gallery->garbageCollect();
    ./zenphoto-fresh/zp-core/class-album.php:   function garbageCollect($deep = false) {
    ./zenphoto-fresh/zp-core/class-album.php:   function garbageCollect($deep = false) {
    ./zenphoto-fresh/zp-core/class-album.php:                   $subalbum->garbageCollect($deep);
    ./zenphoto-fresh/zp-core/class-gallery.php: function garbageCollect($cascade = true, $complete = false, $restart = '') {
    ./zenphoto-fresh/zp-core/class-gallery.php:                         $album->garbageCollect(true);
    ./zenphoto-fresh/zp-core/setup/setup-option-defaults.php:$_zp_gallery->garbageCollect();
    ./zenphoto-fresh/zp-core/utilities/gallery_statistics.php:$_zp_gallery->garbageCollect();
    ./zenphoto-fresh/zp-core/zp-extensions/seo_cleanup.php: $_zp_gallery->garbageCollect();
    
Sign In or Register to comment.