Author Topic: ThumbsPlus 7 : MySQL database : Part 2  (Read 2594 times)

0 Members and 1 Guest are viewing this topic.

wingfield

  • Full Member
  • ***
  • Posts: 59
    • View Profile
ThumbsPlus 7 : MySQL database : Part 2
« on: 2014-08-16 20:58:09 »
no question in this post.  simply a follow up that copies the Chris Williams post from 2007 that I mentioned in my original post.  truncated to 5000 characters.

===============================================================

group: cerious.advanced
Subject: T+ and MySQL - How It Works For Me
    From: Chris Williams
    Date: 5/21/2007 12:07PM

I have read several posts here about people struggling with T+ using
MySQL as a database.  I have struggled too, but now have it working very
well and I thought I'd share my experience.

Caveat: I'm using Windows, with only the released versions of things --
no bleeding edge here.  If you're using something else, YMMV (your
mileage may vary).

--- Versions
I found, like others, that the versions of the various pieces is
important. I'm using MySQL version 5.0.33, which I believe to be the
latest release version for Windows.  The only version of the ODBC
connector I could get to work is the MySQL ODBC connector version 3.51.
  All this is on top of T+ build 2234.  This is what works for me.

--- Database
I used the excellent scripts posted in this message
<45e74987@phlipper.cerious.com> on March 1, 2007.  Don't know who posted
them, but they work great.  Whoever this is (the comments say "JPB"),
I'd like to know to thank them.  I've been using the 5.16 version of the
database and it's fine.

As for the database format, I'm using InnoDB which works fine.  To get
this to work in your MySQL config, you probably have to edit the my.cnf
file in the mysql/bin directory.  Unfortunately Microsoft, in their
infinite wisdom has hardcoded the .cnf extension to some "speed dial"
program, and you can't open this file directly.  If you double click on
it, or right click on it to edit it, nothing happens.  Super...  So what
I do is open an empty notepad window and drag this file into it.  Then
you can edit it, save it, etc.

To get InnoDB to work you have to go down into the InnoDB part and
uncomment a bunch of things.  First comment out "skip-innodb" by putting
a # in front of it.  Then uncomment out all of the things with "=" in
them (all the "innodb_* = " and the "set-variable = " lines).  You want
to set these locations and numbers to things that make sense.  The
example my.cnf file has reasonable numbers to get started with.

Save your changes, restart MySQL, run the SQL scripts, and voila you
have a database.  Install the ODBC connector driver, create an ODBC
connection and off you go.

--- Usernames
There was some discussion about whether or not you needed to create a
user for T+.  I had used "root" for a while, but later switched to user
"ThumbsUser" with password "ThumbsUser" and this works for me.  Just be
sure to make your ODBC connector match this.

--- "Not a Thumbs+ Database"
There has been a lot of discussion here about this error, and it bit me
three times.  I rebuilt my entire T+ database (890,000 thumbs, thank
you) three times before I figured it out.  What happens is that
magically you go along and one day T+ insists that the database you have
built, loved, and nurtured is not a T+ database.  Panic ensues.

The problem is that T+ wants/needs the database to be in ANSI mode.  The
script used to create it, sets ANSI mode on, and all seems fine.  Until
you restart MySQL, and the default is for the SQL_MODE to be "".  This
freaks out T+, and the database looks invalid.

The best solution for me was to put this:

sql_mode="ansi"

in the my.cnf file.  I have it right after the InnoDB stuff (but before
the [mysqldump] section).  This sets ANSI mode all the time.  If you
want to see what mode you have, just put "select @@sql_mode;" in a query
window and press "go".  By default you'll get a null return.  With ANSI
mode on, you'll get something like:

REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ANSI

This is good, and will prevent the dreaded "not a Thumbs+ database".

--- "Database has gone away"
Several people have seen this error.  It's particularly ugly because you
can't even close T+ without it getting this error repeatedly.  Very ugly.

This is because the default MySQL wait timeout for a connection is
something like 28800 seconds (8 hours).  If you leave T+ open longer
than that, it will time out, the connection will go away, life sucks.

The solution is to either a) exit and restart T+ more frequently than 8
hours, b) set a longer wait timeout, or c) both of the above.  I have
set the wait timeout to 99999 (about 27.5 hours).  I do this with this line:

wait_timeout=99999

in my my.cnf file (right after the sql_mode line).  This works, and I
just shut down T+ every day.  All is good.


--- Summary
I have been very successful getting T+ to work with MySQL, and am quite
happy.  I offer this up as only my experience, and hope what I've
learned can help others.

Chris

=================================================================