Page 1 of 1

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

PostPosted: Mon Nov 19, 2018 11:34 pm
by mehorter
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);

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 (10.69 KiB) Viewed 1202 times

However if I run a SELECT from Tools>SQL.. I get the expected result:

Queries also behave unexpectedly. This code, run directly, returns nothing.
Code: Select all   Expand viewCollapse view

Yet, this code returns "1", as expected.
Code: Select all   Expand viewCollapse view

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?

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

PostPosted: Thu Nov 22, 2018 11:44 am
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., and the LibreOffice web page indicates that current stable version is 6.1.3.

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

PostPosted: Thu Nov 22, 2018 12:17 pm
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.

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

PostPosted: Fri Nov 23, 2018 7:03 pm
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   Expand viewCollapse view
returns nothing I do find that
Code: Select all   Expand viewCollapse view
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 (6.84 KiB) Viewed 1048 times

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.

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

PostPosted: Fri Nov 23, 2018 9:54 pm
by keme
The time may also be an "unmanageable integer", requiring the use of a translation function to output a human readable form. ... 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.

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

PostPosted: Sun Nov 25, 2018 3:41 am
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   Expand viewCollapse view

DROP view IF EXISTS view_table1;

                   id INTEGER NOT NULL auto_increment PRIMARY KEY
     , original_stamp INT(11)

ON table1
  SET new.original_stamp=unix_timestamp();

CREATE view view_table1
         , table1.original_stamp
         , Concat(From_unixtime(original_stamp))
         , Date(From_unixtime(original_stamp))
         , Concat(Time(From_unixtime(original_stamp)))
    FROM table1;

             , NULL);

  FROM table1;

  FROM view_table1;

Viewing the Table and View tables works and is manageable: