php-crud-api: Select from View with function doesn't work on mariaDB 10.4

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

Create table ppippo, view pippo_guarda and function fn_date
execute the get statement to a server with mysql on table and view
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.

About this issue

Original URL
State: closed
Created 5 years ago
Reactions: 1
Comments: 19 (15 by maintainers)

Most upvoted comments

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 on Nov 15, 2019

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

carlomarcocci on Nov 15, 2019

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 on Apr 13, 2020

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

akuehnis on Mar 17, 2020

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?

mevdschee on Nov 16, 2019

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 .

lcuis on Nov 16, 2019

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.

carlomarcocci on Nov 15, 2019

Still, there are 2 things I would do if I were you if you didn’t do already:

check the server logs
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.

lcuis on Nov 15, 2019

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:

configuration differences
version differences
software differences
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 on Nov 15, 2019

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 on Nov 15, 2019

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 on Nov 15, 2019

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 on Nov 15, 2019

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

carlomarcocci on Nov 15, 2019

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.

lcuis on Nov 15, 2019