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

Creating tables and queries

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

Postby mehorter » Mon Nov 19, 2018 11:34 pm

I have created a TABLE in MySql server (mariaDB) database named "pmat" on my local desktop running SuSe
Code: Select all   Expand viewCollapse view
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 707 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   Expand viewCollapse view
SELECT * FROM vis

Yet, this code returns "1", as expected.
Code: Select all   Expand viewCollapse view
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
mehorter
 
Posts: 8
Joined: Sat Jul 15, 2017 7:51 pm

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

Postby keme » Thu Nov 22, 2018 11:44 am

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.
User avatar
keme
Volunteer
 
Posts: 3183
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

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

Postby eremmel » Thu Nov 22, 2018 12:17 pm

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.
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8 for work with ORB; AOO4.1.4,LO5.2.5/6.0.5 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1036
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

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

Postby mehorter » Fri Nov 23, 2018 7:03 pm

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   Expand viewCollapse view
SELECT * FROM t1;
returns nothing I do find that
Code: Select all   Expand viewCollapse view
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 553 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
mehorter
 
Posts: 8
Joined: Sat Jul 15, 2017 7:51 pm

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

Postby keme » Fri Nov 23, 2018 9:54 pm

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.
User avatar
keme
Volunteer
 
Posts: 3183
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

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

Postby mehorter » Sun Nov 25, 2018 3:41 am

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   Expand viewCollapse view
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
mehorter
 
Posts: 8
Joined: Sat Jul 15, 2017 7:51 pm


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 4 guests