Author Topic: Thumbs 10 Pro - MySQL  (Read 56165 times)

0 Members and 1 Guest are viewing this topic.

feraltek

  • Member
  • **
  • Posts: 2
    • View Profile
Thumbs 10 Pro - MySQL
« on: 2016-02-27 21:20:28 »
Where is the SQL script to set up a database in MySQL for Thumbs Plus 10 Pro?

Sjoinca

  • Member
  • **
  • Posts: 8
    • View Profile
Thumbs 10 Pro - MySQL
« Reply #1 on: 2016-03-03 08:49:57 »
ferraltek please go to the support page and click on database support or you can use the direct link: http://www.cerious.com/databases.shtml

feraltek

  • Member
  • **
  • Posts: 2
    • View Profile
Thumbs 10 Pro - MySQL
« Reply #2 on: 2016-03-03 15:22:01 »
The sql script on that page generates errors. Is there another?

Sjoinca

  • Member
  • **
  • Posts: 8
    • View Profile
Thumbs 10 Pro - MySQL
« Reply #3 on: 2016-03-03 23:59:33 »
First you should post your questions to the right category.  Announcements is for Cerious to announce new software versions.

Second, please follow the instructions per the thread entitled "TP7 w/ MySQL: Full instructions by PeteW1959".  This is the most complete information on the forums.

Third, if you still have problems after reading the above information, you will need to provide information, on what exactly the error message states.

Only the pro and network versions of ThumbsPlus are able to use the external MySQL/Maria/PostgreSQL/etc databases.

rocky

  • Member
  • **
  • Posts: 2
    • View Profile
Re: Thumbs 10 Pro - MySQL
« Reply #4 on: 2019-05-22 16:50:48 »
Hi I have the same problem. I have MariaDB running on a linux box.

I try loading the sql schema using this command found on your website http://www.thumbsplus.com/databases.html.
Code: [Select]
[rocky@server~]$ mysql -u root -p  < Thumbs9_mysql.sql
Results in this error.
Code: [Select]
ERROR 1064 (42000) at line 14: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"ThumbsPlus"' at line 1

Seems like it hates the double quotes, so I stripped them out of the entire script and run it again but then get this error.
Code: [Select]
ERROR 1064 (42000) at line 93: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'varchar(255) NOT NULL            ,
    value             longtext       NOT NULL' at line 4

I tried all kinds of syntax variations in that area of the script and can't figure out why it's throwing that error.

I can't find the thread entitled "TP7 w/ MySQL: Full instructions by PeteW1959" anywhere on your forum.

I can successfully connect to my MariaDB box through a 32 bit ODBC connection so I think I'm good as long as I can get the schema working.  Any help would be greatly appreciated. Thank you.


Daan van Rooijen

  • Administrator
  • Sr. Member
  • *****
  • Posts: 933
    • View Profile
Re: Thumbs 10 Pro - MySQL
« Reply #5 on: 2019-05-22 19:12:17 »
Quote
I can't find the thread entitled "TP7 w/ MySQL: Full instructions by PeteW1959" anywhere on your forum.

That thread must have been lost in the conversion to the current forum software, but it wouldn't have been of much help anyway because it concerned ThumbsPlus 7, which had a very different database format.

Its author, PeteW1959, later published a MySQL database creation script for ThumbsPlus 9 which many found useful. If I'm not mistaken, ThumbsPlus 10 uses the same format. If interested (it also contains some rudimentary instructions), you can download it here:

https://www.dropbox.com/s/bb4s0v31u0xpm13/thumbs9-MySQL-by-PeteW1959.zip?dl=0

Quote
I can successfully connect to my MariaDB box through a 32 bit ODBC connection so I think I'm good as long as I can get the schema working.

For MySQL, two solutions to common problems are:

- Use the Unicode driver, not the ANSI version.
- Put this in your my.ini configuration file:
sql-mode = "STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ANSI_QUOTES"

I've never used MariaDB, so I don't know if any of this is actually relevant to your problem :)
I'm volunteering as a moderator - I do not work for Cerious Software, Inc.

Daan van Rooijen

  • Administrator
  • Sr. Member
  • *****
  • Posts: 933
    • View Profile
Re: Thumbs 10 Pro - MySQL
« Reply #6 on: 2019-05-22 19:24:19 »
Fellow user Pie.photo also once took the trouble of explaining how he got things to work with MySQL and PostgreSQL, and that message seems to have disappeared as well. I will quote it here from my backup of it, in case it may contain any useful clues for you:

Quote
Experiences with MySQL and PostgreSQL Client/Server Database

by pie.photo, Saturday, May 09, 2015, 16:59

Since 2007 I am using TP7 with a MySQL database. It still runs on a NAS-Box.
Now I decided to migrate to TP9sp2. As I have another application running a lot faster with PostgreSQL than with MySQL, I wanted to run all with PostgreSQL.
But after several unsuccessful days with PostgreSQL I switched to MySQL.
By the way: The database conversion TP7 -> TP9 did not work.

But now TP9 works with MySQL!
A lot of time I lost because of using the most recent MySQL ODBC-driver version 5.2 that I used without any problems for my TP7-database. Version 5.1 works fine with TP9 - version 5.2 does NOT! You need the 32bit ODBC driver version! The 64bit ODBC driver does not work with ThumbsPlus.
You have to put in
SET SESSION sql_mode='ANSI_QUOTES,STRICT_TRANS_TABLES';
in the field "Initial Statement:" of the MySQL Connector/ODBC Data Source Configuration. All the rest I left as is.
If you have Win7 64bit, you cannot use the default ODBC configuration tool. You have to use C:\windows\SysWOW64\odbcad32.exe . Otherwise you cannot configure
the 32bit ODBC-driver.

I created an empty database with default settings (UTF8 character set):
CREATE DATABASE "thumbs9".
Then I used the SQL-File http://www.cerious.com/binaries/cerious/database/new/Thumbs9_mysql.sql to create all tables and the necessary entries in table "ThumbsPlusDatabase".
TP9 now could connect and I scanned all 120.000 pictures without troubles.
After some queries which where very slow I remembered, that the default engine for MySQL tables is "MyISAM".
Queries are a lot faster with "InnoDB" engine! Therefore I converted the tables to "InnoDB":
ALTER TABLE xyz ENGINE=InnoDB;
Now queries are fast again!

#################

But I was not happy, because I was not able to run TP9 with my PostgreSQL server :-(
Again and again I have read the forum thread of KurtA, heinzelrumpel and Daan van Rooijen (thank you a lot!). Finally TP9 now also runs with PostgreSQL 9.2 on my NAS box.
What I have done:
Created a database with ENCODING=UTF8, collation and character type set to en_US.UTF-8
Downloaded the SQL file from http://www.cerious.com/binaries/cerious/database/new/Thumbs9_postgres.sql
Changed "idFileType" to "idFiletype" in table "Thumbnail" (I have not tested, but I think it is not
anymore necessary with ThumbsPlus 9 SP2; I did not change the table name "ThumbsPlusDatabase" to "thumbsplusdatabase").
I used this SQL-script for creation of the tables and all necessary entries in the table "ThumbsPlusDatabase"
Important!: I have lost a lot of time because by mistake I created the tables as admin-user. So the owner of these tables was not the thumbsplus-user. Therefore PostgreSQL did not allow access when I tried to connect with TP9. I had to change the owner of all tables to thumbsplus-user.

Next I downloaded and installed PostgreSQL ODBC driver psqlodbc_09_03_0400.zip
Again - you have to use C:\windows\SysWOW64\odbcad32.exe on a Win7 64bit system. Otherwise you cannot configure 32bit ODBC drivers!
When selecting the ODBC-driver choose "PostgreSQL Unicode" (NOT ANSI!).
Fill in all connection data (server name, name of database, user and password). Then click the button "Datasource" an go to "Page 2".
Activate "bytea as LO"!
All the rest I left as is.

Now I could connect TP9 with my new PostgreSQL database.
Some hours later I can tell you now, the performance seems to be even better than MySQL with InnoDB engine.
All 120.000 pictures are scanned and I will begin to test and check if all runs well. If so, I will uninstall my good old TP7 and delete the corresponding MySQL database.

################

Hope, this helps someone.

Have a nice day,

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

rocky

  • Member
  • **
  • Posts: 2
    • View Profile
Re: Thumbs 10 Pro - MySQL
« Reply #7 on: 2019-05-23 12:08:32 »
Thank you Daan!  That helped get it working, using the sql script on the TP website:
http://www.thumbsplus.com/binaries/database/new/Thumbs9_mysql.sql

The one from the zip file you posted didn't work. ThumbsPlus gave an error about it not being a ThumbsPlus database.

This is what I did.

Log into MariaDB terminal
Code: [Select]
$ mysql -u root -p
Set the mode
Code: [Select]
MariaDB [(none)]>  SET sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ANSI_QUOTES';
Load the script
Code: [Select]
MariaDB [(none)]>  source /path/to/Thumbs9_mysql.sql
Grant permission to a user to use the database
Code: [Select]
MariaDB [(none)]>  grant all privileges on ThumbsPlus.* to user@'%' identified by 'password';
Then set up an ODBC connection to the ip address of the machine using the above user/pass.

Seems to be working! Thanks for taking the time to post.



Daan van Rooijen

  • Administrator
  • Sr. Member
  • *****
  • Posts: 933
    • View Profile
Re: Thumbs 10 Pro - MySQL
« Reply #8 on: 2019-05-23 12:17:14 »
Thanks for the feedback, good to hear that you got it working!
I'm volunteering as a moderator - I do not work for Cerious Software, Inc.