displaying/editing a table through LO-Base

users
#1

Hello,

To start with - I am not certain that this belongs into THIS "forum"...

I run a MySQL-database which is stored at a Linux-Debian-Squeeze-Server.
This DB consists of "dozens" of inter-related tables, views, functions, procedures,
etc., etc.
I have been accessing the DB both through the the command-line client and phpMyAdmin,
adding, updating, deleting tuples with no problems at all with ALL tables.

Except - when accessing the DB through LO-Base, using the JDBC connector from MySQL
(mysql-connector-java-5.1.30), there is just ONE table which I cannot access. it is
defined like this:
CREATE TABLE inventar
   (nummer INTEGER NOT NULL,
    bezeichnung VARCHAR (255) not null default ' ',
    stueckZahl INTEGER not null default 1,
    anschDatum DATE not null DEFAULT '1999-09-01',/* Anschaffungsdatum */
    fvbNr INTEGER not null default 0, /* Firmennummer des Lieferanten */
    typBesch CHAR(1) not null default 'k', /* Miete, Kauf, Leihe, Spende,... */
    anschKosten FIXED (11,2) default 0, /* Anschaffungskosten in EUR */
    repKosten FIXED (11,2) default 0, /* aufgelaufene Reparaturkosten in EUR */
    abschreibung FIXED (11,2) default 0, /* aufgelaufene Abschreibung in EUR */
    datEliminiert DATE NOT NULL DEFAULT '2099-09-01',/* aus Inventar ausgeschieden am... */
    vorhanden SMALLINT NOT NULL default 1, /* Menge laut Inventur */
    invDat DATE not null default '1999-09-01', /* Inventur-Datum */
    bemerkung VARCHAR (255) not null default ' ',
    PRIMARY KEY (nummer),
    UNIQUE INDEX (nummer),
    INDEX (fvbNr),
    INDEX (typBesch),
    FOREIGN KEY (fvbNr) REFERENCES fvbPartner(nummer),
    FOREIGN KEY (typBesch) REFERENCES invBeschTyp(code)
   )
   CHARACTER SET latin1 COLLATE latin1_german1_ci
   ENGINE=InnoDB
;
I am sure that the problem lies with the JDBC-Connector itself or its usage under LO-Base,
since I don't experience any problems when going either through the command-line client,
phpMyAdmin or the native MySQL connector under LO-Base. There is NO entry in any of the
MySQL-logs...

Has anybody experienced a similar situation?
Regards
H.S.

#2

Heinrich,
I can't be sure if this is your problem, but in the past, I have experienced strange behavior in Base when I upgraded MySQL without saving and restoring the database. In other words, using the old MySQL version database with the new MySQL version. At those times, I was getting some complaints in the MySQL log about the database integrity. However, running the MySQL-suggested database fixer made it worse, not better. Since then, I have been using the policy to save the database with mysqldump under the old version, renaming the MySQL database directory to force a fresh install, upgrading MySQL, and then, after the installation, restore the database with the new version's mysql. Since I have been doing that process, I have not seen any database strangeness and the log remains "clean", but verbose. So, if you have upgraded MySQL at some time in the past and not "upgraded" the database at that time, that may be your instability's "root cause".

You may try a save and restore. The conversion process of mysqldump converting to SQL and mysql importing the resulting SQL can act as a sort of filter to discard problem areas in the database. I don't think it will do any harm, but always backup first.

For the record, I am using MySQL 5.5.36 and MySQL-connector-java-5.1.18 and they seem to work well together. (Slackware 14.0 (K3.2.29) Linux)
HTH.
Girvin Herr

#3

Thanks for caring to answer, Girvin!
I use mysqldump to dump all tables and views from the server and then "restore"
the resulting sql-text-file on my local machine. During this process I actually
drop the database and then recreate it from a "tabula rasa". It seems to me that
this should take care of the problem of migrating the server.

By the way, I have got 5.5.37-0+wheezy1-log installed on both the remote server
and my local mysql-server-machine.

In addition - using the native connector EVERYTHING (including accessing/editing etc.
the table mentioned in my first append) works just fine - both through phpmyadmin,
the command-line client AND LO-Base. So I am pretty sure that the problem has to do
with JDBC and its integration within LO-Base. Unfortunately I simply don't know how
to trace down what happens in the JDBC case. I don't know of any logging facility
within either JDBC or LO-Base. The mysql logs don't show ANY entries in the failing
case.

Regards from cold/rainy Salzburg - actually - looking up from my laptop I can
actually see snow (in mid-May!!) half-way up the surrounding mountains.
Heinrich

#4

Hi Heinrich,

   anschKosten FIXED (11,2) default 0, /* Anschaffungskosten
in EUR */
   repKosten FIXED (11,2) default 0, /* aufgelaufene
Reparaturkosten in EUR */
   abschreibung FIXED (11,2) default 0, /* aufgelaufene
Abschreibung in EUR */

Are these FIXED fields interpreted properly by Base ?

Alex

#5

Hello Alex,
As far as I can see - yes. Mostly the numbers are integers, however (as is to be
expected for bought items) there are also numbers such as 335,80 (comma = decimal
point in our locale) and these are shown correctly in LO-Base.
Regards
H

#6

Heinrich,
Yes, I forgot that the backed-up SQL first drops the table to be restored and then recreates it before loading the data. My mistake. That makes the process even simpler.

I do not know much about the JDBC connector either. Have you tried the Oracle website or a search engine (google, etc.)? Sometimes you can get a hit that can point you in the right direction. Maybe someone else on this forum has replied to your design and has an answer.
Good luck.
Girvin Herr

#7

Hi Heinrich,

How about signing your PK ? Are the PKs in your other tables all
unsigned INT ?

Alex