Base will not process one table in MySQL DB

[cscj01]

I have a MySQL DB defined by the following SQL statements:

CREATE TABLE author (
author_id int NOT NULL AUTO_INCREMENT,
first_name varchar(45) DEFAULT NULL,
middle_name varchar(45) DEFAULT NULL,
last_name varchar(45) DEFAULT NULL,
suffix varchar(45) DEFAULT NULL,
preferred_name varchar(45) DEFAULT NULL,
birth date DEFAULT NULL,
death date DEFAULT NULL,
PRIMARY KEY (author_id)

CREATE TABLE books (
book_id int NOT NULL AUTO_INCREMENT,
bk_title varchar(100) DEFAULT NULL,
bk_edition varchar(45) DEFAULT NULL,
bk_year year DEFAULT NULL,
bk_isbn varchar(45) DEFAULT NULL,
bk_type enum(‘H’,‘T’,‘F’,‘P’,’’) DEFAULT NULL,
author_id int NOT NULL,
publisher_id int NOT NULL,
PRIMARY KEY (book_id),
KEY author_id-indx (author_id),
KEY pub_id-index (publisher_id),
CONSTRAINT fk_books_1 FOREIGN KEY (author_id) REFERENCES author (author_id),
CONSTRAINT fk_books_2 FOREIGN KEY (publisher_id) REFERENCES publisher (publisher_id)

CREATE TABLE publisher (
publisher_id int NOT NULL AUTO_INCREMENT,
pub_name varchar(45) DEFAULT NULL,
pub_address varchar(45) DEFAULT NULL,
pub_city varchar(45) DEFAULT NULL,
pub_state char(2) DEFAULT NULL,
pub_zip char(10) DEFAULT NULL,
imprint_id int DEFAULT NULL,
PRIMARY KEY (publisher_id)

CREATE TABLE imprint (
imprint_id int NOT NULL AUTO_INCREMENT,
imprint_name varchar(45) NOT NULL,
publisher_id int DEFAULT NULL,
PRIMARY KEY (imprint_id),
KEY idx_imprint_publisher_id (publisher_id),
CONSTRAINT fk_imprint_1 FOREIGN KEY (publisher_id) REFERENCES publisher (publisher_id)

When I connect the DB to LO Base, I can show every table except the Books table. Whenever I try to access the Books, I get the following message:

Due to an error, LibreOffice crashed. All the files you were working on will be saved. The next time LibreOffice is launched, your files will be recovered automatically.

I have checked the books table for some error in the definition. Can anyone shed any light on this? BTW, I am on Ubuntu 22.04 LTS.

[Wanderer]

@cscj01

bk_type enum(‘H’,‘T’,‘F’,‘P’,’’)

This type is something not used in other tables. So maybe try, if replacing this with a CHAR helps.
.
I assume you checked, the tables are generated correctly in MySQL? Or did you create the tables from LibreOffice through Tools->SQL ?

[RobertG]

Have changed the code, because the posted code won’t work:

CREATE TABLE author (
author_id int NOT NULL AUTO_INCREMENT,
first_name varchar(45) DEFAULT NULL,
middle_name varchar(45) DEFAULT NULL,
last_name varchar(45) DEFAULT NULL,
suffix varchar(45) DEFAULT NULL,
preferred_name varchar(45) DEFAULT NULL,
birth date DEFAULT NULL,
death date DEFAULT NULL,
PRIMARY KEY (author_id));

CREATE TABLE publisher (
publisher_id int NOT NULL AUTO_INCREMENT,
pub_name varchar(45) DEFAULT NULL,
pub_address varchar(45) DEFAULT NULL,
pub_city varchar(45) DEFAULT NULL,
pub_state char(2) DEFAULT NULL,
pub_zip char(10) DEFAULT NULL,
imprint_id int DEFAULT NULL,
PRIMARY KEY (publisher_id));

CREATE TABLE books (
book_id int NOT NULL AUTO_INCREMENT,
bk_title varchar(100) DEFAULT NULL,
bk_edition varchar(45) DEFAULT NULL,
bk_year year DEFAULT NULL,
bk_isbn varchar(45) DEFAULT NULL,
bk_type enum('H','T','F','P','') DEFAULT NULL,
author_id int NOT NULL,
publisher_id int NOT NULL,
PRIMARY KEY (book_id),
KEY author_id_index (author_id),
KEY pub_id_index (publisher_id),
CONSTRAINT fk_books_1 FOREIGN KEY (author_id) REFERENCES author (author_id),
CONSTRAINT fk_books_2 FOREIGN KEY (publisher_id) REFERENCES publisher (publisher_id));

CREATE TABLE imprint (
imprint_id int NOT NULL AUTO_INCREMENT,
imprint_name varchar(45) NOT NULL,
publisher_id int DEFAULT NULL,
PRIMARY KEY (imprint_id),
KEY idx_imprint_publisher_id (publisher_id),
CONSTRAINT fk_imprint_1 FOREIGN KEY (publisher_id) REFERENCES publisher (publisher_id));

Missed close bracket on end of every CREATE statement.
Wrong names for keys: “pub_id-index” isn’t allowed → set to “pub_id_index”.
Wrong order of statements: There couldn’t be created a reference to table publisher before table publisher exists.

After sending this code I tried to open table “books”. No Problem to open this table in
Version: 24.2.3.2 (X86_64) / LibreOffice Community
Build ID: 433d9c2ded56988e8a90e6b2e771ee4e6a5ab2ba
CPU threads: 6; OS: Linux 6.4; UI render: default; VCL: kf5 (cairo+xcb)
Locale: de-DE (de_DE.UTF-8); UI: de-DE
Calc: threaded

Done all this with a MariaDB on OpenSUSE 15.6 Linux with direct connection from LO Base.

1. Which version of LO do you use?
2. How do you connect to MySQL database (direct, JDBC, ODBC)?

[cscj01]

Everything works well in MySQL. I have a number of queries that work as they should. I will try your idea of using CHAR.

[cscj01]

Version: 7.6.7.2 (X86_64) / LibreOffice Community
Build ID: 60(Build:2)
CPU threads: 16; OS: Linux 6.5; UI render: default; VCL: gtk3
Locale: en-US (en_US.UTF-8); UI: en-US
Ubuntu package version: 4:7.6.7-0ubuntu0.23.10.1~bpo22.04.1
Calc: threaded

Direct connect with Mariadb

I think I’m going to dump the database and recreate it with the changes you have or have suggested. I’ll let you know if things go well. Thanks for your help

[RobertG]

@cscj01 : You are using a special LO package from Ubuntu. Might be the direct driver isn’t the same as supported directly from LO. Could you try to connect by JDBC to your MariaDB database?

[cscj01]

I am unsure of what you are saying, but the indexes are required for the foreign keys.

[Wanderer]

Meanwhile you should learn to use comments (the little speech-bubble) instead of “answering” with the bigger button “Suggest a solution” if you don’t suggest a solution here.

[nicholas59]

Shall use it from now on. Thanks

[robleyd]

@nicholas59 You should also read This is the Guide….

[nicholas59]

Sorry robleyd, I believed I could give a point of view under the form of a comment… does it comply with rules?

[robleyd]

In addition to Wanderer’s advice on the use of comments vs solutions, you might want to read the link I gave and familiarise yourself with how to work with this site and the guidelines for using it.

[nicholas59]

Thank you robleyd, I will refrain from accessing for the time being. Bye

[Villeroy]

The database created with @RobertG’s SQL looks like this in the Base relations window:

I connect with admin privileges via SDBC (native LibreOffice driver) to database “dummy” on localhost.