A Question about MySQL tables and extraction of Registered User information

wolfeman

Joined: 2004-09-02
Posts: 25
Posted: Thu, 2006-07-27 14:50

Greetings I had a rather large and old Gallery 2.0 installation with thousands of registered users. I am creating a new fresh install of the latest Gallery 2.1.1 but I would like to migrate my userbase and the acompanying user info to the new G2 install. [an upgrade was out of the question due to issues which I don't want to get into here]

Which tables should I extract from the old database to re-enstate user, quota, group and permissions information to the new database?

When I tried the obious, extracting the "users" table and importing it to the new database, I see the list of users show up in the new admin window but I get a "Missing Object" error when trying to edit or delete a name. Aditionally, extracting the "groups" table gave me the original groups in the admin window but when trying to browse members or edit any aspect of a group I get the same "Missing Object" error. Obviously there are other tables that need to be extracted from the original database but I am unsure how to go about it or which tables go together.

----
Gallery version = 2.1.1 core 1.1.0.1
PHP version = 5.0.4 cgi-fcgi
Webserver = Apache/1.3.36 (Unix) mod_fastcgi/2.4.2 mod_gzip/1.3.26.1a mod_auth_passthrough/1.8 mod_log_bytes/1.2 mod_bwlimited/1.4 FrontPage/5.0.2.2635.SR1.2 mod_ssl/2.8.27 OpenSSL/0.9.7a PHP-CGI/0.1b
Database = mysql 4.0.27-standard, lock.system=flock
Toolkits = ArchiveUpload, Exif, Ffmpeg, Getid3, NetPBM, SquareThumb, Thumbnail, Gd
Acceleration = none, partial/900
Operating system = Linux srv0c.web-hosting-today.com 2.4.21-37.EL #1 Wed Sep 7 13:35:21 EDT 2005 i686
Default theme = matrix
Locale = en_US
Browser = Mozilla/5.0 (Macintosh; U; PPC Mac OS X; en) AppleWebKit/418 (KHTML, like Gecko) Safari/417.9.3

 
valiant

Joined: 2003-01-04
Posts: 32509
Posted: Fri, 2006-07-28 04:58

G2's database schema doesn't allow for simple Users -> users table copies.

a GalleryUser extends a GalleryEntity and has a GalleryUserGroupMap membership.
what does that mean on the database level?

You not only need to copy the User table rows, but also the Entity table rows for entityType = GalleryUser.
And you need the UserGroupMap rows.

But that won;t work.

There's the problem that G2 uses a single sequence of IDs from the SequenceId table. so you'll need to ensure that no row inserted (copied) to Entity / User has the same entityId as an existing ID.
and once inserted, you need to set the SequenceId table row value to 1 higher than the highest Entity.Id value.

Yes, this is complicated, even more so when dealing with album items, where much more tables are involved.
on the application level this makes things easy and elegant though.

and finally, after all manual changes, clear the G2 application level database caches in site admin -> maintenance.

the g2 database schema wasn't designed for easy manual changes in mind.

 
wolfeman

Joined: 2004-09-02
Posts: 25
Posted: Fri, 2006-07-28 16:07

oh man, that sucks for me. Thank you for the in depth reply, I appreciate it. This all started when I got a "Missing Object" error, which I detailed here: http://gallery.menalto.com/node/52137

I could not find the source of the error or even what to look for to eradicate it and my search of the forums did not reveal any previously answered help requests that matched my problem exactly, as most of those had to do with embedded anvironments.

I do have a complete backup of the database as it stood, error and all, prior to wiping it and starting over. I had assumed, mistakenly, that I could extract user data over to the new install.

Is there no way to find the database error that caused the "Missing Object" in the first place? or to transfer the userbase to the new install?

thanks again for the reply

cheers
-wolfeman