LO Base questions

users
#1

Hi All

Two questions to throw into the pot today....

1) I'm running OO Base (3.3.0) as I can not get LO to open my Database of several years standing!! I use Java 1.6.21 and this speeded-up the use of the DB very well. I still however have one major problem. When I shut-down at night it takes several mins for the Base module to close. It seems to be writing everything back to the Hard Drive even if I have not made any changes and as I said it takes several mins to do this. (I have approx 2500 records with about 70 fields in each record and a maximum of 3 graphics in each - one plain small graphic and up to two thumbnail photos.
Anyone know the "magic Trick" to get round this??

2) There has been a lot of "chatter" on the Forum about an SQL Connector. I am interested to go this way but am not happy with SQL, (Don't like its owner!!).
What other connectors are available??

Thanks Guys as always - you are a wonderful source of information!!!

Ian Whitfield
Pretoria

//

#2

Hi Ian,

One or maybe two quick things.

The embedded engine does not actually compact the data unless you ask it
to - so over time you can have quite a bit of wasted space, extra
records if you will marked as deleted, but in the database non the less.

Of course if you are already doing this disregard but if not:

Periodically you should perform the following.
When finished using the database, open the SQL window.
Menu [File>SQL]
In the command window enter:
SHUTDOWN COMPACT
You should, after a short while receive in the status field the message:
"1: Command successfully executed."
At that point close the file. [you must do so as the database engine is
indeed shutdown at this point]

This will reduce the size of the file on disk and the memory used at
run-time, and will increase performance on startup and shutdown.

Depending on how actively you edit that table this period can be once a
week or once every two weeks..can't say what is right for your specific
usage.

Secondly - backing up your database, external of the Base file.

You should consider periodically performing the following:
Open the SQL window again.
In the command window enter the command
SCRIPT '<some file name without a path>'
[note the single quotes are required, the brackets are not of course.

This will generate a text file containing both your database scheme
(table/index definitions, etc] and standard SQL insert commands for all
the data in your table(s), located in the same directory as your ODB
file. This will include the graphic (blog) data as encoded text also
BTW.
[NOTE] this does not include Query definitions/Forms/Reports, just the
database. Also it is a common practice to give these backup files the
descriptor .sql i.e. mydata02072012.sql

Doing this you are assured that you could reload the data to either a
new embedded Base file and or have the first step in migrating your data
to an external RDBMS.

So, you can not stop the 'write to disk' you mention on close, but the
first comment above should help reduce the time and the second is just
good precaution.

Anyway - I'll stop here for the moment.

//drew

#3

Hi :slight_smile:

1.  Close Base long before you want to shut-down the machine ;)  Sorry, that's not very helpful but hopefully Drew's answer solves the problem and i couldn't help a little cheekiness again.

2.  If you are not happy with the owners of MySql then why not give MariaDb a try?
http://mariadb.org/
It is made as a drop-in replacement for MySql and you might find the people there are very like-minded (to you).  The MySql connector should work exactly the same for MariaDb as the code is almost identical with perhaps a few new features and plenty of bug-patches.

Regards from
Tom (the predictable) :slight_smile:

#4

Hi :slight_smile:

1. Close Base long before you want to shut-down the machine :wink: Sorry, that's not very helpful but hopefully Drew's answer solves the problem and i couldn't help a little cheekiness again.

2. If you are not happy with the owners of MySql then why not give MariaDb a try?
http://mariadb.org/
It is made as a drop-in replacement for MySql and you might find the people there are very like-minded (to you). The MySql connector should work exactly the same for MariaDb as the code is almost identical with perhaps a few new features and plenty of bug-patches.

The MySQL connector I use for Ubuntu 11.10 works fine with Mariadb

Regards from
Tom (the predictable) :slight_smile:

From: drew<drew@baseanswers.com>
Subject: Re: [libreoffice-users] LO Base questions
To: users@global.libreoffice.org
Date: Tuesday, 7 February, 2012, 18:08

Hi All

Two questions to throw into the pot today....

1) I'm running OO Base (3.3.0) as I can not get LO to open my Database
of several years standing!! I use Java 1.6.21 and this speeded-up the
use of the DB very well. I still however have one major problem. When I
shut-down at night it takes several mins for the Base module to close.
It seems to be writing everything back to the Hard Drive even if I have
not made any changes and as I said it takes several mins to do this. (I
have approx 2500 records with about 70 fields in each record and a
maximum of 3 graphics in each - one plain small graphic and up to two
thumbnail photos.
Anyone know the "magic Trick" to get round this??

A suggestion about your design, converting will be a pain, many recommend that large picture files be stored in separate tables using the same key as the main table. When you want the images, the search query is a little more complex but overall performance should be better.

The idea is maintable (currently with images) and key mainid is now maintabletext with key mainid and picturetable with key mainid (using the same key as maintableindex. If necessary this can be split up into more image tables.

#5

The MySql connector should work exactly the same for MariaDb as the code is almost identical with perhaps a few new features and plenty of bug-patches.

Thanks Tom!!

Where do I get _YOUR_ connector and how do I use it??

Hi Ian,

One or maybe two quick things.......

Also thanks Drew

Great advise - I will give this a go!!

Ian Whitfield
Pretoria

#6

Hi :slight_smile:

http://extensions.libreoffice.org/extension-center/mysql-native-connector-for-mac-osx

it is not mine.  I think Alex did the ones for Mac and Gnu&Linux but we still need someone to do one for Windows and possibly Bsd.  I think the Bsd people have their own wiki-system.  Somewhere in this mailing is a recent link to a Vista-only connector but i am not sure many people are still using Vista.  Didn't people upgrade back to Xp or on to 7 now?

Good luck and regards from
Tom :slight_smile:

#7

Hi Ian,

Where do I get _YOUR_ connector and how do I use it??

Sorry if I might appear patronising in what follows, that is not my
intention, but :

Before you get carried away, we are talking about the MySQL C native
connector extension.

This MySQL connector will not help you in any way access your current
ODB file which uses the HSQLDB engine included by default in OOo Base
(and LO Base for that matter).

For it to be of any use to you, you need to have access to a MySQL (or
MariaDB) SERVER (and preferably the counterpart client code) - the mysql
server can be installed on the same machine as LO, or alternatively can
be accessible via a network connection (LAN or Internet).

There are MySQL client/server packages available for virtually all of
the Linux distributions I know of, various flavours of BSD, Windows, and
Mac.

You also need to setup at least one user on the mysql server having the
right to administer/use the database server, and create a schema or
catalogue, simply put, a database name. You can either do this from a
terminal/console, or use an extra package such as phpmyadmin which also
requires installing and setting up a webserver.

Then, you need to install the MySQL native connector extension in OOo/LO
- make sure you get the right version - the Oracle supplied one should
still work with both OOo 3.3.0 and LO, although there have been problems
in some versions of LO with that connector on some OSes.

If you are not prepared to learn all of this, how to administer your
MySQL server, or read up on the available documentation of data types,
SQL functions and commands supported by MySQL/MariaDB, then you are
quickly going to become frustrated and disillusioned. It is an
investment in time and energy, but one that I personally have never
regretted.

Alex

#8

Hi everybody,
At the risk of getting on people's nerves...
I have been trying on and off to download and install the native mysql-connector for LO-3.5
from the extensions site. I run Linux-Mint 12 (Lisa) as well as Debian-Wheezy.

Whenever I try to activate it, I get the message loading component library failed
~/.config/libreoffice/3/user/uno_packages/cache/uno_packages/lupqfyfc.tmp_/mysql-native-connector-1.0.1-linux-32bit.oxt/mysqlc.uno.so
This file is of course present in exactly that directory and it always is the version
just downloaded a minute before. I have played around with less restricive permissions of
mysqlc.uno.so, but that didn't help. Any idea of what might be wrong?
Heinz

#9

Thanks Drew!!

This worked a treat for me and I can see a marked improvement in my close-down times of the main DB and also closing Queries,

A real Gem!! (Is there any way of automating this do you know??)

Ian Whitfield
Pretoria

#10

Howdy Ian

Glad it helped.

Automating?

As in a custom menu item for 'Close after Compact'
_or_
Every Friday compact the database?

Thanks

//drew

#11

Good question Drew!!!!! (thinking.......???)

I guess either would work but I think the first option would be my first choice. But rather 'Compact and Close'.

On reflection - whichever is the easiest to implement.

Ian Whitfield
Pretoria

#12

Hi :slight_smile:
I think automating it creates problems.  Unexpected things tend to crop-up and it can be very annoying to find that your machine is suddenly outside of your control and running super slow at a moment when you need rapid access to deal with a situation.

Where i 'work' we share the building with an organisation that only has part-time workers.  As soon as they sit down to do some work their machines hunt for updates and install them, run through a mysterious back-up process, defrags, does an anit-virus scan and error-messages pop-up such as "Must install new java now" and "Must reboot machine NOW".  After a couple of hours the machine has completed most of the tasks and is ready to use.  Just in time for the worker to have to go to their next workplace.

The java updats never completes because none of them have an admin level account.  It gets almost all the way through and then drops out.

In our office we also have a lot of part-timers but when people login the computer lets them get on with work rather than forcing them to wait for the machine to service itself badly.

Regards from
Tom :slight_smile: