Bug report

rf1234

Initially I thought the bug I found today is similar to this one: https://datatables.net/forums/discussion/42850/postsubmit-event-not-always-raised-in-editor-1-6-3#latest

But it is actually not the same. It is pretty serious too and I haven't found a work around yet. I have a unique index on two columns in a very simple table consisting of only five columns: id, currency, date, rate, update_time. The unique index is on currency and date. A duplicate key situation could be created through both an insert and an update.

The application crashes on a simple SQL dup key when I use "postSubmit"
The event is being triggered but the json object is a null value!

An SQL error occurred: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'SEK-2010-02-11 00:00:00' for key 'UniqueIndexForex'

Fatal error: Call to a member function insertId() on boolean in E:\xampp\htdocs\lgf\DataTables\php\Editor\Editor.php on line 1624

This is my post submit statement which doesn't work any more:

forexEditor
        .on( 'postSubmit', function ( e, json, data, action ) {
            if (json.error) {
                var sqlDupKey = json.error;
                sqlDupKey = sqlDupKey.substring(0, 15 )
                if (sqlDupKey == 'SQLSTATE[23000]') {
                    json.error = 'Sorry, this rate already exists!';
                }
            }        
        });

For the sake of completeness: If there is no SQL error the event is also triggered and the JSON object is not null. So something is wrong with Editor's error handling I guess.

If I remove the 'postSubmit' event I get the usual system error message with a link to this
https://datatables.net/manual/tech-notes/12

The crash in Editor is therefore caused by the 'postSubmit' event.

rf1234

@ Allan: Would you have a fix or a work around for me, please?

In my own coding I do this to avoid a dup key but I don't know how to do this with data tables.

$dbh->query('SELECT COUNT(*), `id`, `rate`
               FROM forex
              WHERE `date` = :date  
                AND `currency` = :currency');
$dbh->bind(':currency', $currency);
$dbh->bind(':date', $date);
$row = $dbh->singleAssoc(); // a one-dimensional array is returned SINGLE
if ( (bool)$row["COUNT(*)"] ) { //the record was already there
    if ( (float)$row["rate"] !== (float)$rate ) { //the rate has changed                        
        $dbh->query('UPDATE forex   
                        SET `rate` = :rate
                      WHERE `id` = :id');
        $dbh->bind(':id', $row["id"]);
        $dbh->bind(':rate', $rate);
        $row = $dbh->execute();
    }
} else {  //no record there yet; needs to be inserted
    $dbh->query('INSERT INTO forex  
                 (`currency`, `date`, `rate`)  
                   VALUES   
                 (:currency, :date, :rate)'); 
    $dbh->bind(':currency', $currency);
    $dbh->bind(':date', $date);
    $dbh->bind(':rate', $rate);
    $row = $dbh->execute();
}

allan

Hi,

I'm not quite clear on how the postSubmit Javascript event handler is causing that SQL error? Or perhaps I'm misunderstanding what you mean by "crash" - I'm assuming that means the error?

It sounds like there is some validation missing for the edited data. You would need to check that the submitted data is not going to cause a conflict before inserting it - a global validator could be used for that since it needs to consider the values from two different fields.

Apologies if I've got the wrong end of the stick with this one.

Allan

rf1234

postSubmit does not cause the SQL error. That is caused because the record already exists. I defined the unique index in the database so that the database does the work for me and I don't have to check for a duplicate key myself.
I use postSubmit in order to avoid the display of the usual system error message. Instead I want to display 'Sorry, this rate already exists!' This is a very simple way of checking for a duplicate key. Much easiere than the coding I show above. In fact it does not involve any coding. All you need to do is check for SQLSTATE[23000] and display the right message.

This worked perfectly with the previous Editor version. But now Editor crashes upon the SQL error. This is the error message from Editor upon duplicate key (see above as well).
Fatal error: Call to a member function insertId() on boolean in E:\xampp\htdocs\lgf\DataTables\php\Editor\Editor.php on line 1624

So Editor.php crashes on line 1624 which it didn't do in the previous version. Editor should definitely not crash upon an SQL error. It should simply get back with the SQL error as it did before.

If I remove the postSubmit Editor does not crash any longer. It simply display the usual system error message. For that reason I believe that the postSubmit causes Editor to crash.

rf1234

Allan, forget this please: "If I remove the postSubmit Editor does not crash any longer. It simply display the usual system error message. For that reason I believe that the postSubmit causes Editor to crash."

Unfortunately this does not make any sense because the error is in the Editor.php and not in Javascript. I tested once again with the removed postSubmit.

The system error message is displayed as above. But of course Editor.php crashes as well. Same error as with postSubmit.

An SQL error occurred: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'AUD-2017-07-07 00:00:00' for key 'UniqueIndexForex'
( ! ) Fatal error: Call to a member function insertId() on boolean in E:\xampp\htdocs\lgf\DataTables\php\Editor\Editor.php on line 1624
Call Stack
#   Time    Memory  Function    Location
1   0.0038  223688  {main}( )   ...\actions.php:0
2   0.2700  3358944 tblForex( ) ...\actions.php:428
3   0.2828  3717720 DataTables\Editor->process( )   ...\tblForex.php:42
4   0.2828  3718072 DataTables\Editor->_process( )  ...\Editor.php:661
5   0.2848  3719664 DataTables\Editor->_insert( )   ...\Editor.php:908
6   0.2849  3719952 DataTables\Editor->_insert_or_update( ) ...\Editor.php:1055

As you can see the SQL error comes first (which is a "wanted" error if you know what I mean) and then Editor.php crashes. The JavaScript error using postSubmit is only a consequence of the Editor.php bug because of this bug the json object does not get returned to JavaScript.

rf1234

I went back to using the Editor php libraries of version 1.6.2 - and everything works perfectly fine as you can see on the attached screen shot. When I try to enter the Australian $ exchange rate to the Euro for July 7 again I get the message "Sorry, this rate already exists!"

My work around for now is to use Editor 1.6.2

allan

Do you have tryCatch( false ) in your Editor PHP code by any chance?

As you say, Editor should be catching such an error.

Allan

rf1234

No, I don't have it anywhere! Just checked. Editor 1.6.2 catches the error and returns it to Javascript as "json.error".

allan

I'm not quite sure I understand where the PHP error statement about the insertId is coming from in that case I'm afraid. is that in the error log, or the returned JSON?

Allan

rf1234

No json is being returned. Hence the Javascript error on postSubmit.
The error statement is in the error log.

I attached another screen shot which has all errors in it:
1. The "wanted" SQL error (duplicate key)
2. The PHP error in Editor.php
3. The Javascript error because of the missing json response

allan

Ah!! I see. Okay, thanks for the clarification.

No json is being returned

That's a problem. Editor's Javascript is always expecting JSON in the response. Anything else should result in the generic "System error" message.

The libraries should be returning something like:

{
"error": "SQL error..."
}

if an SQL error is being caused.

Let me try setting something up locally to reproduce that and I'll get back to you.

Allan

rf1234

Thanks Allan.

I think we are in sync about this. Since this all works in Editor 1.6.2 it is hopefully not too hard for you to figure this out.

This is what Editor 1.6.2 returns as "json.error":
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'AUD-2017-07-07 00:00:00' for key 'UniqueIndexForex'

{"fieldErrors":[],"error":"SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry 'AUD-2017-07-07 00:00:00' for key 'UniqueIndexForex'","data":[],"ipOpts":[],"cancelled":[]}

I've been using Editor since version 1.5 and this has always worked like this.

allan

Could you try changing the following line in php/Database/Driver/Mysql/Query.php:

echo "An SQL error occurred: ".$e->getMessage();

to be:

            throw new \Exception( "An SQL error occurred: ".$e->getMessage() );

That will resolve it and the error message will be returned in valid JSON.

Thanks,
Allan

rf1234

ok, that works! Many thanks!
You changed the message compared to Editor 1.6.2

Previously you only displayed the error message without displaying "An SQL error occured: ". But that is no problem I will make a small change in my exception handling to make it more flexiblie regarding the position of the SQLSTATE to avoid future issues.

rf1234

This is how I changed the handling right now. Works.

forexEditor
        .on( 'postSubmit', function ( e, json, data, action ) {
            if (json.error) {
                if ( json.error.includes('1062 Duplicate entry') ) {
                    json.error = 'Sorry, this rate already exists!';
                }
            }        
        });