Select from View with function doesn't work on mariaDB 10.4

[carlomarcocci]

I'm trying to get data from a view with a function in hits fields, i noticed that api.php works correctly on mysql 8 and it doesn't on Maria 10.4. This is a simple sql script to repeat the problem

1. Create table ppippo, view pippo_guarda and function fn_date
2. execute the get statement to a server with mysql on table and view
3. execute the get statement to a server with mariadb on table and view

   
   DROP TABLE IF EXISTS pippo;
   CREATE TABLE pippo(
   dt datetime,
   num int,
   primary key (dt)
   );
   INSERT INTO pippo(dt,num) VALUES(NOW(),1);

DELIMITER $$ DROP FUNCTION IF EXISTS fn_date $$ CREATE FUNCTION fn_date (in_date DATETIME) RETURNS char(10) CHARSET latin1 DETERMINISTIC BEGIN RETURN DATE(in_date); END $$ DELIMITER ;

DROP VIEW IF EXISTS pippo_guarda; CREATE VIEW pippo_guarda AS SELECT dt, fn_date(dt) FROM pippo;

execute the script in mysql 8.0 it works

TABLE

carlo@jigen:~$ wget http://host:8080/api.php/records/pippo/ -O /tmp/pippo ... HTTP request sent, awaiting response... 200 OK Length: 50 [application/json] Saving to: ‘/tmp/pippo’

/tmp/pippo 100%[=================================================================>] 50 --.-KB/s in 0s

2019-11-15 13:51:55 (6,17 MB/s) - ‘/tmp/pippo’ saved [50/50]

carlo@jigen:~$ cat /tmp/pippo {"records":[{"dt":"2019-11-15 11:33:30","num":1}]}

VIEW with function

carlo@jigen:~$ wget http://host:8080/api.php/records/pippo_guarda/ -O /tmp/pippo_guarda ... HTTP request sent, awaiting response... 200 OK Length: 69 [application/json] Saving to: ‘/tmp/pippo_guarda’

/tmp/pippo_guarda 100%[=================================================================>] 69 --.-KB/s in 0s

2019-11-15 13:55:19 (11,8 MB/s) - ‘/tmp/pippo_guarda’ saved [69/69]

carlo@jigen:~$ cat /tmp/pippo_guarda {"records":[{"dt":"2019-11-15 11:33:30","fn_date(dt)":"2019-11-15"}]}

on mariaDB 10.4 it works on
TABLE

carlo@jigen:~$ wget http:/api.php/records/pippo/ -O /tmp/pippo ... HTTP request sent, awaiting response... 200 OK Length: 50 [application/json] Saving to: ‘/tmp/pippo’

/tmp/pippo 100%[=================================================================>] 50 --.-KB/s in 0s

2019-11-1/host5 14:00:03 (9,76 MB/s) - ‘/tmp/pippo’ saved [50/50]

carlo@jigen:~$ cat /tmp/pippo {"records":[{"dt":"2019-11-15 11:32:53","num":1}]}

it returns and error code on
VIEW with funcion

carlo@jigen:~$ wget http://host/api.php/records/pippo_guarda/ -O /tmp/pippo_guarda ... HTTP request sent, awaiting response... 500 Internal Server Error 2019-11-15 14:02:54 ERROR 500: Internal Server Error.

[lcuis]

Hello Carlo,

Sorry, I cannot test your issue right now. One thing I can say is that with php-crud-api on MariaDB with view, I had to cast the output of concat function for example for the filters to work.

[carlomarcocci]

thanks for quick answer. in my test i don't use filters, i retrieve the entire table Do you think is rhe issue easy to solve? i like php-crud-api and i'd like to use it but these kind of views are mandatory in my ws thanks in advance

[lcuis]

I understand your concern. I cannot say whether it is easy to fix or not. Another thing I can say however is that I always alias the column output. Maybe this makes a difference? I should be able to try to reproduce your issue in a few minutes.

[lcuis]

I was not able to reproduce your issue yet. However, I am able with php-crud-api to GET (list) a view with a column defined as fn_date(testdatetime)with no cast nor any alias on MariaDB 10.3.17 on Debian. Where the fn_date is defined as you specified. Now I will try to create the table and the view exactly as you specified.

[lcuis]

I was able to GET (list) /records/pippo_guarda using a Rest client:

{
    "records": [
        {
            "dt": "2019-11-15 15:45:17",
            "fn_date(dt)": "2019-11-15"
        }
    ]
}

I will try to GET now using wget but I need first to deactivate authentication.

[lcuis]

I am sorry to report that using wget leads to the same result for me:

{"records":[{"dt":"2019-11-15 15:45:17","fn_date(dt)":"2019-11-15"}]}

So, the main differences that I can think of, between you and me that could lead to different results can be: 1) configuration differences 2) version differences 3) software differences 4) architecture differences

For 1. , in php-crud-api , temporarily now, I only have username, password, database and debug (true) set.

For 2. I use a php-crud-api file I took the 22nd November 2019. I slightly modified it but the changes I made that are related to the views only affect the writing, not the reading.

For 3. Here is the software I use:

# mariadb --version
mariadb  Ver 15.1 Distrib 10.3.17-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

# apache2 -v
Server version: Apache/2.4.38 (Debian)
Server built:   2019-10-15T19:53:42

# wget -V
GNU Wget 1.20.1 built on linux-gnu.
-cares +digest -gpgme +https +ipv6 +iri +large-file -metalink +nls +ntlm +opie +psl +ssl/gnutls 
...

# php --version
PHP 7.3.11-1~deb10u1 (cli) (built: Oct 26 2019 14:14:18) ( NTS )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.3.11, Copyright (c) 1998-2018 Zend Technologies
    with Zend OPcache v7.3.11-1~deb10u1, Copyright (c) 1999-2018, by Zend Technologies

For 4. , my server is remote with a FQDN, uses HTTPS with valid certificates and requires a .htaccess based authentication.

Please let me know if you think I can help any further. Sorry, I was not able to help so far.

[lcuis]

Still, there are 2 things I would do if I were you if you didn't do already: 1) check the server logs 2) define the view with cast and alias such as:

alter view pippo_guarda as select dt, cast(fn_date(dt) as date) AS fndate from pippo

Maybe this can help and it shouldn't make things worst.

[carlomarcocci]

The mariadb version i'm using is 10.4.9. I notice your test is on 10.3.17, i run the api on mariadb 10.3.18 and it works correctly. The problem is on maiadb 10.4 Thanks a lot

[lcuis]

Good to know, thanks for the information Carlo! Can you keep 10.3.18 until a solution is found if any for 10.4?

[carlomarcocci]

i'm testing your api on production server, i have to downgrade mariadb server to let the script run. It's friday night here and i'll do that monday morning. Thanks a lot for your kind attention.

[lcuis]

My pleasure. One small thing, I am mostly an enthusiastic user of php-crud-api . This is in fact not my solution. I am just happy if I can help this community as this is a very good solution for me.

[lcuis]

Sorry, I had to unassign myself because I tried installing MariaDB 10.4 but this messed things up on my machine. I had to revert to 10.3 .

[mevdschee]

Sorry, I had to unassign myself because I tried installing MariaDB 10.4

Don't worry, thank you for your help.

... installing MariaDB 10.4 but this messed things up on my machine

I'm running the test environments in a docker setup (see docker dir) for the reason you describe.

@carlomarcocci: I need your help: I'm trying to find a distro release with MariaDB 10.4 in it, Ubuntu 19.10 does not have it. Which one does?

[lcuis]

I am not familiar with it but it seems that Manjaro has MariaDB 10.4 : https://distrowatch.com/table-mobile.php?distribution=manjaro

[lcuis]

Solus 4.1 has Mariadb 10.4.8 apparently: https://distrowatch.com/table.php?distribution=solus

[akuehnis]

Having the same problem with 10.1.44-MariaDB-0+deb9u1: {"code":9999,"message":"SQLSTATE[HY000]: General error: 1615 Prepared statement needs to be re-prepared"}

This helped: In this method: GenericDb::getOptions() set the option: \PDO::ATTR_EMULATE_PREPARES => true, // was false before

I got help from this page: https://stackoverflow.com/questions/31957441/laravel-general-error-1615-prepared-statement-needs-to-be-re-prepared

[mevdschee]

For the record: Most solutions suggest increasing the value of table_definition_cache in MySQL config:

SET GLOBAL table_definition_cache = 4096

I think this is set too low (by default) in MariaDB 10.4 (see next comment)

see: https://mariadb.com/kb/en/library/server-system-variables/#table_definition_cache

[mevdschee]

Note that there is now a docker based test on CentOS 8 for PHP 7.4 and MariaDB 10.4 and it succeeds without any modifications to the default settings.

[mevdschee]

Heads up that this is now the default in v2.10.1 onwards, so no need to change anything anymore, see also #759