[Solved] Table not visible w/ timestamp data type w/MySQL

Creating tables and queries
Post Reply
mehorter
Posts: 8
Joined: Sat Jul 15, 2017 7:51 pm

[Solved] Table not visible w/ timestamp data type w/MySQL

Post by mehorter »

I have created a TABLE in MySql server (mariaDB) database named "pmat" on my local desktop running SuSe

Code: Select all

MariaDB [pmat]> CREATE TABLE vis (id INT, ts TIMESTAMP);
INSERT INTO vis VALUES(1,NOW());
DESCRIBE vis; 
SELECT * FROM vis;

Query OK, 0 rows affected (0.03 sec)

Query OK, 1 row affected (0.01 sec)

+-------+-----------+------+-----+---------------------+-------------------------------+
| Field | Type      | Null | Key | Default             | Extra                         |
+-------+-----------+------+-----+---------------------+-------------------------------+
| id    | int(11)   | YES  |     | NULL                |                               |
| ts    | timestamp | NO   |     | current_timestamp() | on update current_timestamp() |
+-------+-----------+------+-----+---------------------+-------------------------------+
2 rows in set (0.00 sec)

+------+---------------------+
| id   | ts                  |
+------+---------------------+
|    1 | 2018-11-19 15:34:12 |
+------+---------------------+
1 row in set (0.00 sec)

But when I try to open the table in base the table returns no records. Notice no title in title bar:
Screenshot_20181119_160033.png
Screenshot_20181119_160033.png (10.69 KiB) Viewed 2665 times
However if I run a SELECT from Tools>SQL.. I get the expected result:
Screenshot_20181119_155350.png
Queries also behave unexpectedly. This code, run directly, returns nothing.

Code: Select all

SELECT * FROM vis
Yet, this code returns "1", as expected.

Code: Select all

SELECT id FROM vis
I connect to MySql with a MySQL(Native) type connection.
I have also found that changing the file type from (TIMESTAMP or DATETIME) to either (DATE or TIME) types everything works as expected.
Base is connected and other tables behave normally, only a table with either TIMESTAMP or DATETIME type does this problem apppear.
Any ideas?
Last edited by mehorter on Sun Nov 25, 2018 3:42 am, edited 1 time in total.
LibreOffice Version: 6.1.3.1 on Linux4.18.6-1-default x86_64 GNU/Linux Open Suse Tumbleweed
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Table not visible with timestamp data type w/MySQL backe

Post by keme »

Your description indicates that the DB client does not support MariaDB/MySQL "full timespec" (date + time of day) data format. The support for such data as "atomic" may be entirely missing, or it may be that the data type is stored in a manner not properly interpreted by Base. A few things to try:

Does the Base application support a datetime/timestamp data type at all? See whether you can create a new table with a datetime/timestamp field.

Have you tried to update LibreOffice? Your sig. indicates v.5.3.2.2, and the LibreOffice web page indicates that current stable version is 6.1.3.
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Table not visible with timestamp data type w/MySQL backe

Post by eremmel »

Another aspect needed for Base is that each table needs a primary key (PK), else inserting records will fail. As far as I understand your table definition, a PK is missing.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
mehorter
Posts: 8
Joined: Sat Jul 15, 2017 7:51 pm

Re: Table not visible with timestamp data type w/MySQL backe

Post by mehorter »

Thanks for your responses.

I have updated my sig to indicate I am using the current LO version and Kernel.

I have been trying every iteration of the columns to find or isolate the issue. I have indeed added a primary key (PK) to the table:
+-------+--------------+------+-----+---------------------+-------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------------------+-------------------------------+
| id | int(10) | NO | PRI | NULL | auto_increment |
| d | date | NO | | current_timestamp() | |
| t | time | NO | | current_timestamp() | |
| tx | varchar(100) | YES | | NULL | |
| dt | datetime | YES | | current_timestamp() | on update current_timestamp() |
+-------+--------------+------+-----+---------------------+-------------------------------+
I have found that the DATE column behaves as expected when created/altered. However, once I ADD/ALTER a column to DATETIME or TIMESTAMP with any combination of NULL, DEFAULT, ect.. Base fails to display anything. This is true whether I ADD/ALTER the column with Base, the MySQL command line, or the MySQL Workbench.

Interestingly, While a

Code: Select all

SELECT * FROM t1;
returns nothing I do find that

Code: Select all

SELECT YEAR(dt) FROM t1; 
does work as expected when run from the normal query tab. So base can 'see' the whole record correctly but not display it, i guess.

I have also found two odd behaviours. The first is that the default for the TIME column, current_timestamp(), returns a erroneous time. The second oddity is a DATE column that does not allow NULL with no default returns a nonsensical date. I added rows directly into the table through Base by only adding gibberish to the TXT column or, where id = 3, inputting a MM/DD record. Consider the two screen shots below. The dates and times for the MySQL cli are correct.
Screenshot_20181123_113057.png
Screenshot_20181123_113057.png (6.84 KiB) Viewed 2511 times
Screenshot_20181123_113027.png
I must be doing something wrong, I can't believe Base cannot understand Date/Time datatypes from a native connection to a MySQL server.
I'm unclear about 'ATOMICity' but perhaps I set the MySQL server up wrong regarding GRANTS and so forth.
It's hard to believe this problem exists and I'm the first to discover it. Must be something really simple and stupid.

*The one thing I keep wondering about is whether the MySQL server and my desktop are using the same calendars and/or clock settings. There nothing odd about my setup, afaik.

EDIT: Additional thoughts:
Perhaps the date/time formats MySQL and Base use are different. It seems like a bad transfer of data from the back-end to the front-end.
LibreOffice Version: 6.1.3.1 on Linux4.18.6-1-default x86_64 GNU/Linux Open Suse Tumbleweed
User avatar
keme
Volunteer
Posts: 3699
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Table not visible with timestamp data type w/MySQL backe

Post by keme »

The time may also be an "unmanageable integer", requiring the use of a translation function to output a human readable form.
https://stackoverflow.com/questions/536 ... e-in-mysql

AFAIK the MariaDB/MySQL timestamp is also different from MS SQL server timestamp, so tables containing timestamp data may need special care if you transfer between the two platforms.
mehorter
Posts: 8
Joined: Sat Jul 15, 2017 7:51 pm

Re: Table not visible with timestamp data type w/MySQL backe

Post by mehorter »

Thank you for your insights.
It seems as though my solution is in managing the case where Base cannot reliably return any TIME data types (TIME, DATETIME, & TIMESTAMP) from a MySQL/MariaDB backend.

My Solution:
Create a table where a column to hold time values as VARCHAR(11)
Create a trigger which inserts a unix-style timestamp, e.g. "1543104706", into the VARCHAR(11) column before insert
Create a view that converts unix-style timestamps into human-readable formats, keeping in mind time values are inserted as either DATE data-types or as CHAR data-type.

This provides a human-readable read only view of date/time values as well a table that is accessible. Time calculations, if needed, would need to passed to the table in unix-style timestamp format.

Here is the code I used:

Code: Select all

DROP TABLE IF EXISTS table1;

DROP view IF EXISTS view_table1;

CREATE TABLE table1
  (
                   id INTEGER NOT NULL auto_increment PRIMARY KEY
     , original_stamp INT(11)
  );

CREATE TRIGGER table1_before_insert BEFORE INSERT
ON table1
FOR EACH row
  SET new.original_stamp=unix_timestamp();

CREATE view view_table1
AS
  SELECT table1.id AS ID
         , table1.original_stamp
         , Concat(From_unixtime(original_stamp))
         , Date(From_unixtime(original_stamp))
         , Concat(Time(From_unixtime(original_stamp)))
    FROM table1;

INSERT INTO table1
     VALUES (NULL
             , NULL);

SELECT *
  FROM table1;

SELECT *
  FROM view_table1;
Viewing the Table and View tables works and is manageable:
Attachments
Screenshot_20181124_202921.png
Screenshot_20181124_202103.png
LibreOffice Version: 6.1.3.1 on Linux4.18.6-1-default x86_64 GNU/Linux Open Suse Tumbleweed
Post Reply