Announcement

#1 2012-05-04 03:59:00

NWS
Member
Seattle, WA
2011-04-02
175

Moving MySQL database how to?

I have what I consider a small gallery set up with Piwigo. No issues with Piwigo but my database size limit is 100 megs. Even as small as my gallery is it's using more than 15 megs in the database. So I'm trying to figure out something before it does hit the max size and creates an enormous amount of work for me.

So I have a couple of questions.

Can Piwigo use a 2nd, 3rd, 4th, etc.  database in addition to the the database it used when setting up the gallery?

I have another webhost and the MySQL database size limit is 2 gigs. Obviously better but at some point that could fill up to. I contacted the webhost and was told once the database hits it's max size that I would not be able to add any more albums or photos. So at that point everything becomes semi useless unless I delete a lot of stuff on a regular basis.

How can I move my Piwigo gallery to the other webhost without having to start all over again? Or how can I get it to use a database on another webhost?

Anyone know of a workaround on the database size issue? Suggestions are welcome.

Last edited by NWS (2012-05-04 06:55:58)

Offline

 

#2 2012-05-04 13:41:12

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

Re: Moving MySQL database how to?

hi,
I'm pretty sure the responsible of the huge database is the table "piwigo_session", if you are very limited in space you can truncate it (and not delete) through PHPMyAdmin (note it will be filled again with time)

you can use multiple database but it's a bit tricky,
-first of all, all your databases must be accessible with the same user/password and on the same host (ex: localhost)
-then create your databases and move the tables you want into them
-finally add and modify the needed lines among this in you local config file (local/config/config.inc.php)

Code:

define('CATEGORIES_TABLE', $prefixeTable.'categories');
  define('COMMENTS_TABLE', $prefixeTable.'comments');
  define('CONFIG_TABLE', $prefixeTable.'config');
  define('FAVORITES_TABLE', $prefixeTable.'favorites');
  define('GROUP_ACCESS_TABLE', $prefixeTable.'group_access');
  define('GROUPS_TABLE', $prefixeTable.'groups');
  define('HISTORY_TABLE', $prefixeTable.'history');
  define('HISTORY_SUMMARY_TABLE', $prefixeTable.'history_summary');
  define('IMAGE_CATEGORY_TABLE', $prefixeTable.'image_category');
  define('IMAGES_TABLE', $prefixeTable.'images');
  define('SESSIONS_TABLE', $prefixeTable.'sessions');
  define('SITES_TABLE', $prefixeTable.'sites');
  define('USER_ACCESS_TABLE', $prefixeTable.'user_access');
  define('USER_GROUP_TABLE', $prefixeTable.'user_group');
  define('USER_INFOS_TABLE', $prefixeTable.'user_infos');
  define('USER_FEED_TABLE', $prefixeTable.'user_feed');
  define('RATE_TABLE', $prefixeTable.'rate');
  define('USER_CACHE_TABLE', $prefixeTable.'user_cache');
  define('USER_CACHE_CATEGORIES_TABLE', $prefixeTable.'user_cache_categories');
  define('CADDIE_TABLE', $prefixeTable.'caddie');
  define('UPGRADE_TABLE', $prefixeTable.'upgrade');
  define('SEARCH_TABLE', $prefixeTable.'search');
  define('USER_MAIL_NOTIFICATION_TABLE', $prefixeTable.'user_mail_notification');
  define('TAGS_TABLE', $prefixeTable.'tags');
  define('IMAGE_TAG_TABLE', $prefixeTable.'image_tag');
  define('PLUGINS_TABLE', $prefixeTable.'plugins');
  define('OLD_PERMALINKS_TABLE', $prefixeTable.'old_permalinks');
  define('THEMES_TABLE', $prefixeTable.'themes');
  define('LANGUAGES_TABLE', $prefixeTable.'languages');

PS : don't forget to backup your files and your original database

Offline

 

#3 2012-05-04 14:06:24

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

Re: Moving MySQL database how to?

The sessions table should be automatically purged each time someones logs in.

I think the "history" table may be the problem. You can deactivate history in the configuration.

Offline

 

#4 2012-05-04 17:03:08

NWS
Member
Seattle, WA
2011-04-02
175

Re: Moving MySQL database how to?

plg wrote:

The sessions table should be automatically purged each time someones logs in.

I think the "history" table may be the problem. You can deactivate history in the configuration.

I'm looking in the database (MySQL admin) and I see a section named "portfoliohistory". It has over 178,000 entries. Would this be the area you're referring to?

I also see an "empty" button. Can I use that safely or will that corrupt the database?
You mention deactivate history in the configuration. Is that somewhere in Piwigo?

Sorry to appear so stupid but still learning Piwigo. This is the first program I've used that uses a MySQL database so it's really confusing to me.

Thank you very much for the quick reply.

I'm looking in "tools" in piwigo admin and I see a list of "purge" options. Will one of those clear out the entries in "portfolio history"? Or should I not purge anything? I did disable the options under "history" in piwigo.

I tried export/import the database to a new database (2 gig) but it wouldn't import for me. Got strange errors. Sometime last night a tech imported it and it appears to be fine, at least the same entries are there that were in the old database.

Now I just have to figure out which file to edit in Piwigo to use the new database.

Thanks again for your help.

Last edited by NWS (2012-05-04 17:17:20)

Offline

 

#5 2012-05-04 17:05:01

NWS
Member
Seattle, WA
2011-04-02
175

Re: Moving MySQL database how to?

mistic100 wrote:

hi,
I'm pretty sure the responsible of the huge database is the table "piwigo_session", if you are very limited in space you can truncate it (and not delete) through PHPMyAdmin (note it will be filled again with time)

you can use multiple database but it's a bit tricky,
-first of all, all your databases must be accessible with the same user/password and on the same host (ex: localhost)
-then create your databases and move the tables you want into them
-finally add and modify the needed lines among this in you local config file (local/config/config.inc.php)

Code:

define('CATEGORIES_TABLE', $prefixeTable.'categories');
  define('COMMENTS_TABLE', $prefixeTable.'comments');
  define('CONFIG_TABLE', $prefixeTable.'config');
  define('FAVORITES_TABLE', $prefixeTable.'favorites');
  define('GROUP_ACCESS_TABLE', $prefixeTable.'group_access');
  define('GROUPS_TABLE', $prefixeTable.'groups');
  define('HISTORY_TABLE', $prefixeTable.'history');
  define('HISTORY_SUMMARY_TABLE', $prefixeTable.'history_summary');
  define('IMAGE_CATEGORY_TABLE', $prefixeTable.'image_category');
  define('IMAGES_TABLE', $prefixeTable.'images');
  define('SESSIONS_TABLE', $prefixeTable.'sessions');
  define('SITES_TABLE', $prefixeTable.'sites');
  define('USER_ACCESS_TABLE', $prefixeTable.'user_access');
  define('USER_GROUP_TABLE', $prefixeTable.'user_group');
  define('USER_INFOS_TABLE', $prefixeTable.'user_infos');
  define('USER_FEED_TABLE', $prefixeTable.'user_feed');
  define('RATE_TABLE', $prefixeTable.'rate');
  define('USER_CACHE_TABLE', $prefixeTable.'user_cache');
  define('USER_CACHE_CATEGORIES_TABLE', $prefixeTable.'user_cache_categories');
  define('CADDIE_TABLE', $prefixeTable.'caddie');
  define('UPGRADE_TABLE', $prefixeTable.'upgrade');
  define('SEARCH_TABLE', $prefixeTable.'search');
  define('USER_MAIL_NOTIFICATION_TABLE', $prefixeTable.'user_mail_notification');
  define('TAGS_TABLE', $prefixeTable.'tags');
  define('IMAGE_TAG_TABLE', $prefixeTable.'image_tag');
  define('PLUGINS_TABLE', $prefixeTable.'plugins');
  define('OLD_PERMALINKS_TABLE', $prefixeTable.'old_permalinks');
  define('THEMES_TABLE', $prefixeTable.'themes');
  define('LANGUAGES_TABLE', $prefixeTable.'languages');

PS : don't forget to backup your files and your original database

Thanks for your quick reply and help. At least now I have  more database space to deal with so hopefully I won't have to deal with multiple databases. I will print this out for future reference.

Offline

 

#6 2012-05-04 17:54:09

NWS
Member
Seattle, WA
2011-04-02
175

Re: Moving MySQL database how to?

Having a major issue with the database. I edited the file database.inc.php in local/config with the information to the new database.

When I put it on the server and go to the gallery I get this error message:

Piwigo encountered a non recoverable error

[mysql error 2013] Lost connection to MySQL server at 'reading initial communication packet', system error: 111
Can't connect to server

#1    my_error /homepages/23/d275617611/htdocs/northwestscenescapesnet/gallery/include/common.inc.php(140)
#2    include_once /homepages/23/d275617611/htdocs/northwestscenescapesnet/gallery/index.php(26)


Something else I need to edit?

I did backup and rename the file I'm editing so have not managed to trash the gallery yet...lol

Last edited by NWS (2012-05-04 18:17:15)

Offline

 

#7 2012-05-04 18:17:50

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

Re: Moving MySQL database how to?

NWS wrote:

[...] I see a section named "portfoliohistory". It has over 178,000 entries. Would this be the area you're referring to?

Yes, and 178k lines is not that big (I have a "history" table with over 10M lines)

I also see an "empty" button. Can I use that safely or will that corrupt the database?

Use the purge actions in Piwigo instead [Administration > Tools > Maintenance]

You mention deactivate history in the configuration. Is that somewhere in Piwigo?

[Administration > Configuration > Options > History]

Offline

 

#8 2012-05-04 18:24:17

NWS
Member
Seattle, WA
2011-04-02
175

Re: Moving MySQL database how to?

Thank you very much.

Offline

 

Board footer

Powered by FluxBB

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