Author Topic: move mysql db to new computer  (Read 12117 times)

0 Members and 2 Guests are viewing this topic.

wingfield

  • Full Member
  • ***
  • Posts: 59
    • View Profile
move mysql db to new computer
« on: 2017-11-07 21:41:45 »
folks,

is there a recommended procedure to move my mysql database to a new computer.  simplistically, I figured I'd use TP menu / database / advanced / export.  Unfortunately, this gives an error message.  The error states "a fatal exception has occurred in ThumbsPlus or a Windows component.  We apologize for the problem.

I'm open to exporting using some other means.  not sure if it matters, but technically - I believe I'm using mariadb (which is a mysql drop in).

I look forward to hearing from folks.

thank you,

david

Daan van Rooijen

  • Administrator
  • Sr. Member
  • *****
  • Posts: 933
    • View Profile
move mysql db to new computer
« Reply #1 on: 2017-11-07 23:53:33 »
I'm not sure if I understand the problem. The location of the database isn't very important. Can't you just install your db software on the new computer, copy your database files to it, and then tell TP to connect to the database at the new location?

(Are you moving the items cataloged in the database (image and/or movie files) to the new PC as well? If so, that need not be a problem either if you leave them in the same folder structure and give the new drive (that they reside on) the same volume label as the one on the old PC. If the images/movies are on a network server, the new server name should be made identical to the old one too.)
I'm volunteering as a moderator - I do not work for Cerious Software, Inc.

wingfield

  • Full Member
  • ***
  • Posts: 59
    • View Profile
move mysql db to new computer
« Reply #2 on: 2017-11-08 19:36:37 »
Daan

>> Can't you just install your db software on the new computer, copy your database files to it, and then tell TP to connect to the database at the new location?

Little doubt I had that you would come to the rescue.  yeah, this sounds reasonable.  couple comments.  one : the version of mysql on my new machine is looking like one version newer that on my old machine.  will this matter?

second : yep, guess I can look this up; but I have no idea where mysql stores it's database files.  

>> (Are you moving the items cataloged in the database (image and/or movie files) to the new PC as well?
no, they sit on a NAS (Synology).  as such, no action here, I take it??

thank you!

Daan van Rooijen

  • Administrator
  • Sr. Member
  • *****
  • Posts: 933
    • View Profile
move mysql db to new computer
« Reply #3 on: 2017-11-08 21:29:58 »
> one : the version of mysql on my new machine is looking like one version newer that on my old machine.  will this matter?

Hopefully not :-)  It probably won't. Maybe you can check their "What's new" but if there should be any incompatibilities with older versions, there's usually a setting to work around that.

> second : yep, guess I can look this up; but I have no idea where mysql stores it's database files.

I can't look on your system either so yes, you'll have to look those up.
 
> no, they sit on a NAS (Synology).  as such, no action here, I take it??

Correct!
I'm volunteering as a moderator - I do not work for Cerious Software, Inc.

wingfield

  • Full Member
  • ***
  • Posts: 59
    • View Profile
move mysql db to new computer
« Reply #4 on: 2017-11-08 21:32:09 »
Daan,

so I found my data by looking at the my.ini file.   that pointed me to C:\ProgramData\MySQL\MySQL Server 5.7\Data.  under this directory was a folder called "thumbs".  so I dragged this folder to my NAS, then dragged it from my NAS to my new computer C drive.  I was thinking I was good to go, so I opened thumbsplus and opened my odbc data source.  sadly, I get the following error.

"database error : the selected database is not a thmbsplus pro database.  and so on and so forth.......

the error number is 5015 : 8038".

thoughts?

David

Daan van Rooijen

  • Administrator
  • Sr. Member
  • *****
  • Posts: 933
    • View Profile
move mysql db to new computer
« Reply #5 on: 2017-11-08 22:42:56 »
I'd compare the new MySQL configuration (my.ini, etc) to the one that worked on the old PC. It's probably something like the sql_mode="ansi" setting that TP requires.

Or, go through PeteW's multi-part instructions to see if you may have missed any steps, they start here: http://forums.cerious.com/forum/index.php?id=4283
I'm volunteering as a moderator - I do not work for Cerious Software, Inc.

wingfield

  • Full Member
  • ***
  • Posts: 59
    • View Profile
move mysql db to new computer
« Reply #6 on: 2017-11-11 17:58:20 »
Daan,

>> If so, that need not be a problem either if you leave them in the same folder structure and give the new drive (that they reside on) the same volume label as the one on the old PC.

can you elaborate on this statement please.  is there something I need to change using MYSQL workbench in the actual database??  or something I need to change on my new laptop?

thank you,

David

wingfield

  • Full Member
  • ***
  • Posts: 59
    • View Profile
move mysql db to new computer
« Reply #7 on: 2017-11-11 19:25:17 »
so I found out that my volume label for both machines is "OS", but the serial number is different.

Daan van Rooijen

  • Administrator
  • Sr. Member
  • *****
  • Posts: 933
    • View Profile
move mysql db to new computer
« Reply #8 on: 2017-11-11 20:18:03 »
> >> If so, that need not be a problem either if you leave them in the same folder structure and give the new drive (that they reside on) the same volume label as the one on the old PC.
>
> can you elaborate on this statement please.  is there something I need to change using MYSQL workbench in the actual database??  or something I need to change on my new laptop?

You were not changing the location of your image files, so you shouldn't have to change anything in the database.

If you have your database working now, but ThumbsPlus doesn't match its thumbnail records correctly to the image files (*), then you could have a look at the two network-related settings under 'Volume Matching Settings' in Opt | Pref | Thumbnails.

(You can also right-click on a drive in the tree panel and choose Volume Properties to see if there are any differences between the physical drive and the database information that it is matched to).

(*: in that case your old thumbnails would be shown as orphans in grayed-out folders, and TP would show no thumbnails -or it would make new ones- when you visit your image folders on the NAS drive).
I'm volunteering as a moderator - I do not work for Cerious Software, Inc.

Daan van Rooijen

  • Administrator
  • Sr. Member
  • *****
  • Posts: 933
    • View Profile
move mysql db to new computer
« Reply #9 on: 2017-11-11 20:19:11 »
> so I found out that my volume label for both machines is "OS", but the serial number is different.

The volume labels on the machines that TP runs on are not important. Only that of the drive where the images are stored is important.
I'm volunteering as a moderator - I do not work for Cerious Software, Inc.

wingfield

  • Full Member
  • ***
  • Posts: 59
    • View Profile
move mysql db to new computer
« Reply #10 on: 2017-12-04 18:23:41 »
Hey Daan,

still no joy.  nothing's ever easy I guess.

back in the old days, Phillip had provide me a couple of lines to add to the thumbs.ini file to help diagnose the problem.  they don't seem to work anymore.  do you know if they've changed, or perhaps been depracated (which would be odd.).  the lines were:

SQLTrace=1
SQLTraceFile=c:\temp\odbclog.log

I'm about 90% positive this is an ANSI Quotes issue.  I've changed it in the my.ini file; but I'm thinking mysql must be not picking it up for some reason, or picking it up from elsewhere.

David

Daan van Rooijen

  • Administrator
  • Sr. Member
  • *****
  • Posts: 933
    • View Profile
move mysql db to new computer
« Reply #11 on: 2017-12-04 19:15:28 »
> do you know if they've changed, or perhaps been depracated (which would be odd.).

I don't know.. did you put them in the [Startup] section where they belonged?

If they no longer work, you can still turn SQL tracing on in Windows' own ODBC Data Source Administrator (but don't forget to turn it off later as it tends to slow things down considerably).

> I'm about 90% positive this is an ANSI Quotes issue.  I've changed it in the my.ini file; but I'm thinking mysql must be not picking it up for some reason, or picking it up from elsewhere.

Are there any other .ini or .cnf files that MySQL might be using, or a commandline argument or an environment variable? Or, could your my.ini contain another "sql-mode = " line with different contents?

Also, are you using the Unicode (not ANSI) ODBC connector?
I'm volunteering as a moderator - I do not work for Cerious Software, Inc.

pie.photo

  • Member
  • **
  • Posts: 8
    • View Profile
move mysql db to new computer
« Reply #12 on: 2017-12-14 13:59:35 »
Some time ago I have posted my experiences with MySQL and PostgreSQL in this forum:
http://forums.cerious.com/forum/index.php?id=4447
Maybe that you can find some helpful informations there. I remember that I had troubles with newer ODBC drivers. But I am not up to date with MySQL versions because now I use PostgreSQL.
Good luck!

wingfield

  • Full Member
  • ***
  • Posts: 59
    • View Profile
move mysql db to new computer
« Reply #13 on: 2018-02-25 12:55:35 »
Daan,

well, I finally got it working; but have a final question.  thought about a new post; but I'll put it here and see what happens.

I'm able to access my database now in MYSQL; but all my Galleries are empty.  the folders are still there; but the pics / thumbnails that were in the gallery folders were not.

thoughts??

David

Daan van Rooijen

  • Administrator
  • Sr. Member
  • *****
  • Posts: 933
    • View Profile
move mysql db to new computer
« Reply #14 on: 2018-02-25 16:37:32 »
> I'm able to access my database now in MYSQL; but all my Galleries are empty.  the folders are still there; but the pics / thumbnails that were in the gallery folders were not.

So, if I understand correctly, when you open the section Galleries in the tree, you still see your galleries but they're all empty?

Galleries are made up of simple pointers to thumbnails (thumbnail records) elsewhere in the database. Could it be that the original thumbnails to which the gallery entries referred are no longer there, or have been replaced by new thumbnails?
I'm volunteering as a moderator - I do not work for Cerious Software, Inc.