Author Topic: Is dbase STILL limited to 2gb?  (Read 12080 times)

0 Members and 1 Guest are viewing this topic.

DavidRTurner

  • Member
  • **
  • Posts: 2
    • View Profile
Is dbase STILL limited to 2gb?
« on: 2015-02-16 21:01:29 »
When (if ever) will the dbase be allowed to pass 2gb?

I have millions of images, and don't want to create individual dbases for each drive.

I've just spent a couple of hours discovering this was still a limitation; TP was 'working' on converting some images, but no thumbnails appeared.

This was a problem numerous versions ago; why is it still limited?  Is there some base software on which TP is based, with this limit?

Is there any way around this, while maintaining TP functionality?

Thanks for any suggestions...

Gary

  • Sr. Member
  • ****
  • Posts: 217
    • View Profile
Is dbase STILL limited to 2gb?
« Reply #1 on: 2015-02-16 21:25:48 »
David,

Regardless of TP version, if you use the Access DB you are restricted to 2GB.  If you are using TP8 or TP9 you can use the SQLite3 DB to get around the 2GB limit.  I use the Access DB with TP7 and the SQLite3 DB with TP9 (creating/updating thumbnails can be painfully slow).  I have approx 576,000 thumbnails in each DB - TP7 is pushing very close to the 2GB limit with 128x128 Q50 thumbnail; TP9 DB is about 7GB with 240x175 Q90 thumbnail.

Hope this is helpful.

Cheers,
Gary

Daan van Rooijen

  • Administrator
  • Sr. Member
  • *****
  • Posts: 933
    • View Profile
Also..
« Reply #2 on: 2015-02-16 22:47:50 »
..with Access and SQLite3 databases, there's now an option to store thumbnails outside of the database. I haven't tried it myself, but thumbnail data is by far the largest component of any database so in that respect it should help considerably.
I'm volunteering as a moderator - I do not work for Cerious Software, Inc.

Pete Wieland

  • Full Member
  • ***
  • Posts: 87
    • View Profile
...And...
« Reply #3 on: 2015-02-17 06:50:05 »
External thumbnails are also painfully slow again. It had improved a lot until 3946; a huge backwards step. It now takes around 5 minutes to display (NOT create) the thumbnails for a folder of ~1000 images where it used to be virtually instantaneous.

IMHO the best solution is to use TP7 with a MySQL database. Fast, reliable and unlimited database size.

Here's hoping for TP10!

Gary

  • Sr. Member
  • ****
  • Posts: 217
    • View Profile
...And...
« Reply #4 on: 2015-02-17 09:20:14 »
> External thumbnails are also painfully slow again. It had improved a lot until 3946; a huge backwards step. It now takes around 5 minutes to display (NOT create) the thumbnails for a folder of ~1000 images where it used to be virtually instantaneous.
>
> IMHO the best solution is to use TP7 with a MySQL database. Fast, reliable and unlimited database size.
>
> Here's hoping for TP10!

Pete,

Please, is there any way you could provide detailed instructions on how to implement the MySQL database with TP7 (on Windows 7 64bit)?  Assume I know absolutely nothing so can you provide baby steps?

Thanks for your kind consideration.
Gary

Gary

  • Sr. Member
  • ****
  • Posts: 217
    • View Profile
Also..
« Reply #5 on: 2015-02-17 09:26:34 »
Daan,

I tried this and the net result was a mess.  There is no maintenance of the thumbnail files so one ends up with orphaned files when images are manipulated thru TP9.  Also, file system directory fragmentation becomes severe (not so much of an issue on SSD but bad on HDD).  I'd avoid this implementation.

To be fair, I have not tested this since one of the original releases of TP9 so there is the possibility the thumbnail maintenance issue has been addressed.

Cheers,
Gary

Gary

  • Sr. Member
  • ****
  • Posts: 217
    • View Profile
...And...
« Reply #6 on: 2015-02-17 09:52:43 »
Pete,

I swear build 3946 is seat of the pants slower overall.

Cheers,
Gary

Pete Wieland

  • Full Member
  • ***
  • Posts: 87
    • View Profile
...And...
« Reply #7 on: 2015-02-17 11:30:09 »
I am about to re-install my laptop (Vista, but the steps are the same) so I will document the process as I do it and post it when complete, probably after next weekend.

It can be a bit fiddly, especially installing MySQL, but if you stick to default configuration it should be manageable.

Pete Wieland

  • Full Member
  • ***
  • Posts: 87
    • View Profile
...And...
« Reply #8 on: 2015-02-17 11:35:53 »
Yep, definitely much slower, to the point of being unusable again. Takes me back to the days of my old 386, floppy discs and DOS; set something going and go and make a cup of tea!

The past year or so the speed issue seemed to have been addressed, but we are back to square one again.

Gary

  • Sr. Member
  • ****
  • Posts: 217
    • View Profile
...And...
« Reply #9 on: 2015-02-17 14:36:53 »
Thanks!!

Again, keep in mind I know nothing.  :)

I am up against the 2GB limit with TP7 and I hate the idea of having to split the database.

Cheers!

Gary

  • Sr. Member
  • ****
  • Posts: 217
    • View Profile
...And...
« Reply #10 on: 2015-02-17 14:38:18 »
Actually, you have time to plant the tea, watch it grow and harvest it.

Pete Wieland

  • Full Member
  • ***
  • Posts: 87
    • View Profile
...And...
« Reply #11 on: 2015-02-21 16:01:04 »
OK here goes. It is a VERY long set of instructions, but I hope comprehensive. As the limit is 5000 characters this is going to be in 3 parts.

For anyone else reading this, the database creation and conversion is for TP VERSION 7 ONLY. You can however still use the instructions for installing MySQL.

I am assuming you already have TP7 installed.

Install MySQL Community Server from  http://dev.mysql.com/downloads/mysql/. Scroll down and click on the big image with 'MySQL Community Server 5.6 for Windows' in it.

On the next page scroll down and choose either the web installer (1.6M) or the full download (282.4M). I recommend the full download as it is less likely to fail, although you will be downloading files you won't need for your configuration. Although they both state they are Windows (x86 32-bit), that is just the installer which isn't available in 64 bit.

On the next screen YOU DON'T NEED TO LOGIN OR SIGN UP if you don't want to. click on 'No thanks, just start my download.' Save the file to a suitable location.

When the file has downloaded double click it to start the installer. You will need to give the usual Windows permissions and then arrive at the licence agreement. Accept the terms and click 'Next'.

Click on 'Custom' and then 'Next'.

On the next screen expand the MySQL Servers category until you see the X64 version then click the right arrow to add it to the install.

Expand the Applications and then MySQL Workbench and select the x64 version.

The Notifier didn't install when I selected it and I had to install it separately, so skip this for now.

You won't need Excel, Visual Studio or Utilities unless you want to to get into advanced stuff, and in any case these can be installed later if you do find you need them.

The Connectors didn't install when I did it so we will install these later as well.

If you want the documentation or examples select those. To be honest the documentation isn't great for non-technical users; you would be better off Googling for help.

Click on 'Next'. Confirm you have MySQL Server, MySQL Workbench and Connector/ODBC all 'Ready to Install. If not click on 'Back' and double check your selections.

Finally click on 'Execute' to start the installation.

When the installation is complete click on 'Next'. You should see MySQL Server 'Ready to Configure'. Click on 'Next'.

Leave the Config Type as Development Machine. This is the best option for a general purpose set-up.

Make sure that TCP/IP is ticked and Port Number is 3306. You can untick 'Open Firewall...' if you want for added security if you are only going to connect to the database from the host machine.

Create a Root Account password. Like the Windows Administrator account you should only use 'Root' in MySQL for maintenance and configuration. You will create a user account later once the TP database has been created. Click on 'Next'.

On the Windows Service screen make sure 'Configure MySQL Server as a Windows Service' is ticked. You can leave the Service Name as suggested, or change it if you prefer.

You have a decision to make for 'Start the MySQL Server at System Startup' option. If you run other software that is system intensive, and you won't need TP at the same time, then I would suggest unticking this. You can start the service manually whenever you need it. However if you use TP every time you start up your PC then this is probably best left ticked.

Make sure that Standard System Account is selected. Click on 'Next'.

On the next screen is a checklist of the configuration steps. Click on 'Execute' to configure MySQL. When configuration has completed click on 'Finish', then 'Next', check that 'Start MySQL Workbench' is ticked then click 'Finish' again.

MySQL Workbench should open. This is the control panel for MySQL server and is where you create and maintain databases and users.

Click on the grey box that has 'Local instance MySQL56' in it. In the box that pops up enter the Root password you created earlier.

The Workbench screen may look complicated, but you don't need to worry about most of it. I will take you through the important bits.


Continued.....

Pete Wieland

  • Full Member
  • ***
  • Posts: 87
    • View Profile
...And...
« Reply #12 on: 2015-02-21 16:04:44 »
...Part 2

The Workbench screen may look complicated, but you don't need to worry about most of it. I will take you through the important bits.

First on the left you have the Navigator pane. Under management you have 'Server Status' which shows you the current state and configuration of MySQL. The important part to note here is 'Server Directories'. You will notice that all the directories are located on the C: drive. This is fine if you have a large C: drive and keep all your data on that. However I know many people, like myself, keep their data on a separate drive and the MySQL database should be kept there. If your existing .td4 is approaching 2Gb you are going to need at least this amount of space to carry out the conversion, and it will grow as your photo collection grows. However I would strongly recommend NOT using a USB drive if possible due to slow access times. We will re-configure this later.

Ignore 'Client Connections' and have a look at 'Users and Privileges'. You will see a couple of 'Root' accounts. You will need to come back here later to set up a specific user for TP to use.

Data Export and Data Import/Restore are for backups. I won't cover this here, but you can find lots of info online.

The next section 'Instance' refers tot he copy of MySQL that you are connected to. It is possible to have more than one 'instance' running at the same time. Startup/Shutdown does what it says, and you will need to return here later.

Options File is where you need to be next. There are a couple of important changes to make here.

CAUTION, HERE BE DRAGONS! Changing the wrong things in the Options File can be disastrous and could prevent ALL access to your database, even by the Root user. YOU HAVE BEEN WARNED!

You only need to worry about the 'General Tab'. Scroll down to 'Directories'. Here you will see the datadir set to C:/ProgramData..... This should be changed if you store your data on a separate drive. First of all go off and create your new MySQL data directory. I use D:\MySQL\data, but you can use anything that suits your configuration. Click on the three dots button to right of datadir path and select your newly created folder.

Scroll down a bit further to the SQL section. under sql-mode you should have the following:-
"STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

You need to add ',ANSI_QUOTES' before the last quotation marks so the line reads:-
"STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ANSI_QUOTES"
This change is very important as TP won't work with MySQL without it.

Click 'Apply'. You will get a preview file come up where you will see your changes and a some SQL code. As long as the path and ANSI_QUOTES changes show up don't worry about the gobbledegook. Click on 'Apply'.

You now need to restart the MySQL Server. Go to Startup/Shutdown and click on 'Stop Server'. Close the Workbench for now.

If you have changed the location of the data folder you will need to copy everything from the old data folder. This is because a lot of the configuration, including user accounts and passwords are actually held in data tables. Copy everything from  C:\ProgramData\MySQL\MySQL Server 5.6\data to the new data folder, overwriting anything that already exists.

Now to install the Notifier, which is the easiest way to re-start MySQL Server.

Go to http://dev.mysql.com/downloads/windows/notifier/ and click on 'Download' next to 1.1.6 1.5M, NOT the one next to MySQL Installer MSI. Click on 'No thanks, just start my download.'

When the file has downloaded double click it to install. Accept the defaults and accept the usual Windows confirmations.

when it has finished you should have a red icon with a white dolphin outline and a red dot in a white circle in your system tray. Right click it and select Actions then Options. Tick 'Run at Windows Startup' then 'Apply'.

Now to re-start MySQL Server. Right click the notifier icon and move the cursor over 'Stopped'. Click on 'Start'. Accept the Windows permission confirmation and you will see a notification Service Status Changed, first from 'Stopped to StartPending', then from 'StartPending to Running'. The icon changes from red to white and the dot from red to green.

If you chose not to start the service on Windows Startup, you will need to manually start MySQL Server here.

Right click the Notifier icon once again, and click on Configure Instance... This will open up MySQL Workbench, enter the Root password and you will be back to the main Workbench screen. The Server Status pane will have opened automatically and you should see the changed data directory.


Continued...

Pete Wieland

  • Full Member
  • ***
  • Posts: 87
    • View Profile
...And...
« Reply #13 on: 2015-02-21 16:06:02 »
... Part 3

Regardless of whether you are creating a new TP database, or converting an existing .td4, you will need to create a new empty database in MySQL. The TP database creation script should do this automatically, but in my experience very rarely works. This is what the 'Schemas' section in the left-hand pane is for. A Schema is just a database, and you can have as many as you want in a MySQL Instance. For example I have three ThumbsPlus Schemas (Family Photos, Professional Research Photos and Family Tree Photos), a schema for my personal website which is database driven, one for a forum I run and another for an online shop. A new installation of MySQl should only have one schema 'test'.

Right click on the 'test' schema and select 'Create Schema...'. A new pane will open with new_schema highlighted as a suggested name. Give your schema a more meaningful name. The convention is to not use capitals or spaces, so I will call mine 'tp_test'. Click on 'Apply'. A confirmation window will pop up, again click 'Apply', then 'Finish'.

You will see tp_test has appeared in the list of schemas.

You now need the TP SQL creation script. Go to http://www.cerious.com/databases.shtml and scroll down to ThumbsPlus version 7.0SP2. Right click the 'Download' link next to MySQL (make sure you select the right one) and 'Save Link As...' to a folder of your choice.

In MySQL Workbench click on 'File' in the top menu then 'Open SQL Script...'. Navigate to the script you just downloaded.

The file will open up in a query editor pane. You will need to make a couple of changes to make it compatible with newer versions of MySQL server.

Lines 24 and 25 read:-

   create database "thumbs";
   
use "thumbs";

put a # in front of create and change use "thumbs" to the name of the schema you created like so:-

   # create database "thumbs";

   use "tp_test";

Use 'Search and Replace' (Crtl-H) to change every occurrence of 'TYPE' to 'ENGINE'. (i.e change TYPE=InnoDB to ENGINE-InnoDB, There are 11 of them).

Click on File - Save Script in case you want to use it again.

You now need to run this script to create the tables. Move to the top of the script and click somewhere in the first line to make sure that nothing is highlighted. Click on the 'lightning' icon above the script and if all is well the bottom window will show the tables being created.

If you right click tp_test under Schemas and select 'Refresh All', click the arrow next to tp_test and then the arrow next to Tables you should see the table names familiar from the Access type database.

Now to create a user for TP to connect to the database. You could use the Root user but it isn't recommended.

Under Management click on Users and Privileges. At the bottom of the Users and Privileges pane click on 'Add Account'. You will get a new account with the name 'newuser'. Change this to something more meaningful; I use ThumbsUser. Set a password and confirm it then click 'Apply'.

Now click on 'Schema Privileges' above the Login Name box. Click on 'Add Entry', click 'Selected schema', pick tp_test from the list then click OK. Click on 'Select "ALL"' then 'Apply'.

Nearly there! Just the ODBC Connector to set up now.

Go to http://dev.mysql.com/downloads/connector/odbc/ and download the first one, the 32-bit MSI Installer. Although you are using 64 bit MySQL, TP needs the 32 bit connector. Install the connector using the defaults.

Click the Windows 'Start' Button and copy C:\Windows\SysWow64\odbcad32.exe into the search box. Click on the only file that appears. This is the 32-bit ODBC manager (despite the name of the folder it is located in!)

Under the User DSN click on 'Add'. Scroll down the list of drivers and select 'MySQL ODBC 5.3 ANSI Driver' then on 'Finish'.

In the Connector window that pops up give the data source a name; I use ThumbsPlus. Fill in the description if you want to, type 'localhost' in the TCP/IP Server box and make sure that 3306 is in the Port box. Type the name of your TP database user (ThumbsUser in my case) and the password. Drop the arrow down in the Database box and select the TP database, tp_test in my case.

Finally click on 'Test' and if you get 'Connection Successful' click on OK. You should see your new ODBC connector in the list. Click on OK to close the administrator.

Now open TP7, go to File - Database - Connect and select the Machine Data Source tab. Select your newly created connector and you're done!

Continued...

Pete Wieland

  • Full Member
  • ***
  • Posts: 87
    • View Profile
...And...
« Reply #14 on: 2015-02-21 16:06:36 »
... Part 4

To get your existing td4 database into MySQL you will have to export it from td4 then import into MySQL. Open up your old database then go to File - Database - Export. Select Entire Database and make sure all boxes are ticked and Version 7 Compatible is selected. Click on OK, select a destination with plenty of space and be prepared for long wait if you are approaching the 2Gb limit.

When it is finished connect to your new empty MySQL database and then go to File - Database - Import. Select the file you have just saved, tick any options you want and click OK. Be prepared for a VERY long wait, possibly several hours.

THE END!