ThumbsPlus

All forums => ThumbsPlus v10 Questions => Topic started by: feraltek on 2016-02-27 21:20:28

Title: Thumbs 10 Pro - MySQL
Post by: feraltek on 2016-02-27 21:20:28
Where is the SQL script to set up a database in MySQL for Thumbs Plus 10 Pro?
Title: Thumbs 10 Pro - MySQL
Post by: Sjoinca 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
Title: Thumbs 10 Pro - MySQL
Post by: feraltek on 2016-03-03 15:22:01
The sql script on that page generates errors. Is there another?
Title: Thumbs 10 Pro - MySQL
Post by: Sjoinca 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.
Title: Re: Thumbs 10 Pro - MySQL
Post by: rocky 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 (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.

Title: Re: Thumbs 10 Pro - MySQL
Post by: Daan van Rooijen 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 :)
Title: Re: Thumbs 10 Pro - MySQL
Post by: Daan van Rooijen 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
Title: Re: Thumbs 10 Pro - MySQL
Post by: rocky 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.


Title: Re: Thumbs 10 Pro - MySQL
Post by: Daan van Rooijen on 2019-05-23 12:17:14
Thanks for the feedback, good to hear that you got it working!