mysqli import problem

ctdlg

Hello,

Happy New Year to all Zenphoto developers and users.

Still a problem when importing a Zenphoto database with phpmyadmin.

1 database is empty

2 Error I get when importing a backup :

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.

To get rid of this problem, on my localhost machine, I edit my.ini and add this line :
innodb_strict_mode = 0

Then I can import my database.
I can then remove this line ...

I cannot add this line on my online site : I have to ask my provider to import my database for me
(cannot edit my.ini - I have to pay more to be able to do this ...)

This issue is already documented on this forum.
It occurred with Zenphoto 1.5.5, 1.5.6, 1.5.7, 1.5.8 and 1.5.9
Did you find any solution ?

Thanks

acrylian

The row size is probably related to the many meta data columns in the images table. There is nothing to do except skipping the fields you don't need. We eventually will move the metadata columns to their own table with the next major version. But

Have you tried to use the included database backup tool? That is not a mysql backup itself but using a custom format for the content. See the "Moving installations" info on our user guide.

ctdlg

Thank you acrylian

I tried your suggestion.
The included database backup tool does work, without innodb_strict_mode = 0.

One year ago, using sql file, I did a successful image title rename (all images) - then I erased the whole database content and imported my sql file

Maybe one day - I will need to mass change some data.
How can I edit a backup-year_date.zdb file for this kind of job ?

Of course, I first check on my localhost machine before changing my online website !

acrylian

How can I edit a backup-year_date.zdb file for this kind of job ?

The .zdb files are text files of serialized data. So theoretically you could just do a search+replace via a text editor. However it is not that easy to "find" because everything is in that one file and all content object types have "title".

What kind of renaming do you need to do regarding the titles? If you know the title exactly to change that might work. If you do some more general renaming, oerhaps some custom PHP code and SQL queries would be easier here to do that and then export it for the live site.

ctdlg

Zenphoto zdb files are probably compressed

example searching "image"
images::BZh41AY&SYG�o����CWq���V=������^J|��@�D_��i)�m�ՊjqqX����̧zo�z+ĸj��~��rE8P��x�513

I'm using Atom - I can use regex to search /replace.
What I did successfully: replace all titles starting with (V)xxx by xxx(V)
Same thing with (P) , (S) , (T) (panos, stereo and time-lapse documents) ...

Nowadays, sql queries are difficult for me.

acrylian

Zenphoto zdb files are probably compressed

Yes, but you can disable that. Btw, for some reason they never work for me if compressed.

Nowadays, sql queries are difficult for me.

Sure, use whatever works for you.

ctdlg

Thanks, I can read the uncompressed zdb file.

I feeld dumb, because compression option was easy to find ...
I will try to restore it and let you know.

ctdlg

Same for me :
if uncompressed, I cannot restore a zdb backup.

Note :
restoring a compressed backup is rather long
restoring an uncompressed backup is really fast (and corrupts the database)

acrylian

I am not sure does it work in some way now or not? Above you meant it does and now you saw it corrupts the database?

ctdlg

Restoring a compressed zdb backup : ok

Restoring an uncompressed backup : fast, no error reported on the admin page but website is broken
= I cannot use an uncompressed zdb file.

acrylian

Okay, thanks, now I understood. I did use our tool for a few migrations and for me the compressed ones didn't work. As usual all servers are a bit different…So at least it is working. That it takes a bit is normal because of all the tables and their data-