successfully added message, but no data inserted in databse

[Blesta Addons]

today we have found that one of our custom plugin have a strange behavior in v4 . after adding a item, the successfully message appear but no data inserted in database !  , when we left some empty fields it return the error validation for that fields; so the validation pass without any issue, after a search in the forum the same behavior was reported in support manager pro in v4 also, no change made in database.

i was thinking what will be the cause for this behavior ?as no error returned from the Record components or the Input components .

i have added the begin() commit() rollback() fucntion to see what happen but no result, also the error_reporting and the debugger is not catching any error !!!

any idea how to diagnostics the problem ?

[Blesta Addons]

5 hours ago, Adam said:

Sounds like you found your problem...

 

What does 

<? echo date('c'); ?>

produce? When you insert that manually via a SQL query from command line or something like phpMyAdmin... does it work? 

Can you also verify that table structure you are using is setup correctly. Are date_added or date_updated DATES, or TIMESTAMPS or DATETIMES, etc.?

 

-Adam

 

no is not the issue, as other plugins didn't has the date field and has the same effect. take a look at this plugin in github , is working fine in php 5.5, but in php 5.6 is not working. not sure if the table shema has something to do with the issue .

[Blesta Addons]

what i have made as test :

1 - i have inserted a sample data via phpmyadmin and it was added perfectly .

                INSERT INTO nh_announcement_news (title, body, active, company_id, public, date_added)
                VALUES ('dfdsfdsf', '4654646464', 1, '1', '1', '23/04/2017');

from the plugin page i can edit the already added data .

2 - try to add the same sample data from the plugin page with insert Record., it show a success message but is not added to database.

$this->Record->insert('nh_announcement_news', $vars, $fields);

3 - try to add the same sample data from the plugin page with sql Record, it show a success message but is not added to database.

			$this->Record->query(
				"INSERT INTO nh_announcement_news (title, body, active, company_id, public, date_added)
				VALUES ('dfdsfdsf', '4654646464', 1, '1', '1', '23/04/2017');"
			);

so why from sql command (phpmyadmin) is inserted correctly, and not with the Record components. i will be crazy as i have achieved all the test i can do without identifying the issue .

 

[Adam]

Can you make a single PHP file without Blesta or minPHP that 

1. Creates the schema
2. Inserts into sql
3. Delete everything

Does the problem still exist? This will help narrow if the issue is with minPHP or something else. Please also share the file and I can try and recreate it on my environment. I run CloudLinux and have access to multiple versions of PHP I can help test with.

-Adam

[Michael]

51 minutes ago, Blesta Addons said:

what i have made as test :

1 - i have inserted a sample data via phpmyadmin and it was added perfectly .

                INSERT INTO nh_announcement_news (title, body, active, company_id, public, date_added)
                VALUES ('dfdsfdsf', '4654646464', 1, '1', '1', '23/04/2017');

from the plugin page i can edit the already added data .

2 - try to add the same sample data from the plugin page with insert Record., it show a success message but is not added to database.

$this->Record->insert('nh_announcement_news', $vars, $fields);

3 - try to add the same sample data from the plugin page with sql Record, it show a success message but is not added to database.

			$this->Record->query(
				"INSERT INTO nh_announcement_news (title, body, active, company_id, public, date_added)
				VALUES ('dfdsfdsf', '4654646464', 1, '1', '1', '23/04/2017');"
			);

so why from sql command (phpmyadmin) is inserted correctly, and not with the Record components. i will be crazy as i have achieved all the test i can do without identifying the issue .

 

Does this work mate:

$this->Record->insert('nh_announcement_news', [
                'title'         => $vars['uri'],
                'body'          => $company_id,
                'active'        => serialize($vars['title']),
                'company_id'    => $company_id,
                'public'        => serialize($vars['meta_tags']),
                'date_added'    => serialize($vars['description'])
            ]);

Something like that but edited as that's what Abdy has done in our cms.

[Adam]

17 minutes ago, Licensecart said:

Does this work mate:

$this->Record->insert('nh_announcement_news', [
                'title'         => $vars['uri'],
                'body'          => $company_id,
                'active'        => serialize($vars['title']),
                'company_id'    => $company_id,
                'public'        => serialize($vars['meta_tags']),
                'date_added'    => serialize($vars['description'])
            ]);

Something like that but edited as that's what Abdy has done in our cms.

Ugh.. That does not make sense. You have a field called "dated_added" but its a serialization of a field called description? If you run a query to find all announcements by date_added or sorted by date_added how does that work? Are you doing it application side -- not having the database do all the work for you?

-Adam

[Blesta Addons]

19 minutes ago, Adam said:

Ugh.. That does not make sense. You have a field called "dated_added" but its a serialization of a field called description? If you run a query to find all announcements by date_added or sorted by date_added how does that work? Are you doing it application side -- not having the database do all the work for you?

-Adam

mike has just post a sample of insertion , we need to change it to a propre our query.

 

25 minutes ago, Licensecart said:

Does this work mate:

$this->Record->insert('nh_announcement_news', [
                'title'         => $vars['uri'],
                'body'          => $company_id,
                'active'        => serialize($vars['title']),
                'company_id'    => $company_id,
                'public'        => serialize($vars['meta_tags']),
                'date_added'    => serialize($vars['description'])
            ]);

Something like that but edited as that's what Abdy has done in our cms.

from your response i feel that blesta CMS plugin was also infected with this behavior ?

[Adam]

16 minutes ago, Blesta Addons said:

mike has just post a sample of insertion , we need to change it to a propre our query.

I think what he posted is wrong. It does not make sense. Read my message.

16 minutes ago, Blesta Addons said:

from your response i feel that blesta CMS plugin was also infected with this behavior ?

Doubt it, seeing how no one else is having issues.. just you...

-Adam

[Blesta Addons]

20 minutes ago, Adam said:

I think what he posted is wrong. It does not make sense. Read my message.

Doubt it, seeing how no one else is having issues.. just you...

-Adam

is not only me, first some users has posted in support manager pro thread about this issue, then we have found a issue with a custom plugin, then some people contacted us about the same issue in other plugins, lately other posted in the announcements thread the same issue, @Licensecart also has the same issue with the announcement plugin. the problem only with blesta +php5.6 and above . for php5.5 no issue .

you can test yourself, the announcements plugin is hosted in GitHub and you can give it a try .

[Michael]

38 minutes ago, Blesta Addons said:

mike has just post a sample of insertion , we need to change it to a propre our query.

 

from your response i feel that blesta CMS plugin was also infected with this behavior ?

Sure was.

[Blesta Addons]

17 minutes ago, Licensecart said:

Sure was.

you have php 5.6 or php 7 ? because i'm also sure php 5.5 is not affected by this .

[Michael]

35 minutes ago, Adam said:

I think what he posted is wrong. It does not make sense. Read my message.

Doubt it, seeing how no one else is having issues.. just you...

-Adam

A lot of plugins have been effected... Support Manager Pro by PauloV, my BlestaCMS but Abdy fixed it and Anouar has issues with his plugins.

[Michael]

15 minutes ago, Blesta Addons said:

you have php 5.6 or php 7 ? because i'm also sure php 5.5 is not affected by this .

I use Php 7.0 but was having issues I believe on php 5.6 can't remember.

I'm sure I asked Abdy about it not submitting when it should have @cyandark

[Blesta Addons]

43 minutes ago, Blesta Addons said:

mike has just post a sample of insertion , we need to change it to a propre our query.

 

from your response i feel that blesta CMS plugin was also infected with this behavior ?

addy has changed the format fo date posted in database from

$vars['date_added'] = date('c');

to

$vars['date_added'] = date('Y-m-d H:i:s');

when we use the same date value, it work. the insertion is working .....

this is something totally strange ....

[Abdy]

I fixed the problem with the CMS only adding "is_null" to the date_updated field, and works. I detected this problem only in PHP 7.0+ with MariaDB (With Percona or MySQL works fine) I guess that my solution probably will not work for you. But in fact IDK what is causing this problem.  I found this problem in another plugins too.

->setField('date_updated', ['type' => 'datetime', 'is_null' => true, 'default' => null])

 

[Adam]

What MySQL driver are you using with PHP?

-Adam

[Adam]

7 hours ago, Blesta Addons said:

no is not the issue, as other plugins didn't has the date field and has the same effect. take a look at this plugin in github , is working fine in php 5.5, but in php 5.6 is not working. not sure if the table shema has something to do with the issue .

Thank you for providing more detailed information like someone else can reproduce the issue....

 

Please apply the following patch file and let me know the results:

diff --git a/announcements/models/announcements.php b/announcements/models/announcements.php                        
index a61adc6..91c7856 100644                                                                                                                                                        
--- a/announcements/models/announcements.php                                                                                                                                         
+++ b/announcements/models/announcements.php                                                                                                                                         
@@ -201,7 +201,13 @@ class Announcements extends AnnouncementsModel {                                               
                        $this->Record->begin();                                                                                         
                                                                                                                    
                        $fields = array("title", "body", "date_added", "active", "company_id", "public", "permit_client_groups", "permit_packages");
-                       $this->Record->insert("nh_announcement_news", $vars, $fields);                              
+                       $pdoStatement = $this->Record->insert("nh_announcement_news", $vars, $fields);              
+            if($pdoStatement instanceof PDOStatement && $pdoStatement->errorCode() !== '00000') {                  
+                echo "<pre>";                                                                                      
+                print_r($pdoStatement->errorInfo());                                                               
+                echo "</pre>";                                                                                     
+                die();                                                                                                                                                                   
+            }                                                                                                                                                                            
                        $announcement_id = $this->Record->lastInsertId();                                                                                                                 
                                                                                                                    
                        // Add client groups

 

[Adam]

Sorry for the spam, but I am pretty sure I know what is going on. The issue was right in front of us -- the datetime is invalid  . You should really be using a timestamp (UNIX/epoch as an example).

 

Blesta uses PDO statements to insert into the database. When you create a PDO connection, you get to set the 'sql_mode'. Blesta's minPHP framework uses the system's default settings. It appears your system does NOT have the following enabled (system wide):

SET SQL_MODE='ALLOW_INVALID_DATES';

As mentioned earlier, when Blesta creates a PDO connection, it uses system defaults and not override anything for that session.

Why is this important, because PHP's date('c') produces invalid datetime for MySQL. Its explained fairly well in their documentation:

https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sqlmode_allow_invalid_dates

 

You can see it live from a system I created to reproduce the issue:

MariaDB [datetest]> select @@sql_mode;
+----------------------------------------------------------------+
| @@sql_mode                                                     |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [datetest]> INSERT INTO test (id, col1) VALUES (NULL, '2017-04-25T05:04:57+00:00');
ERROR 1292 (22007): Incorrect datetime value: '2017-04-25T05:04:57+00:00' for column 'col1' at row 1
MariaDB [datetest]> set @@sql_mode='ALLOW_INVALID_DATES';
Query OK, 0 rows affected (0.00 sec)

MariaDB [datetest]> INSERT INTO test (id, col1) VALUES (NULL, '2017-04-25T05:04:57+00:00');
Query OK, 1 row affected, 1 warning (0.01 sec)

MariaDB [datetest]> show warnings;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'col1' at row 1 |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)

MariaDB [datetest]> select * from test;
+----+---------------------+
| id | col1                |
+----+---------------------+
| 10 | 2017-04-25 05:04:57 |
+----+---------------------+
1 row in set (0.01 sec)

MariaDB [datetest]> 

 

You will notice that datetime format is different then what we inserted because date('c') from PHP is not accepted by MySQL. 

 

Again, issue is with your environment. Tricky to find if you do not have experience.

-Adam

 

[Michael]

10 hours ago, Adam said:

Ugh.. That does not make sense. You have a field called "dated_added" but its a serialization of a field called description? If you run a query to find all announcements by date_added or sorted by date_added how does that work? Are you doing it application side -- not having the database do all the work for you?

-Adam

I did say as an example I edited the left hand side to his table structure and didn't know what he'd put on the right.

[Michael]

3 hours ago, Adam said:

Why is this important, because PHP's date('c') produces invalid datetime for MySQL. Its explained fairly well in their documentation:

 

That's so weird one of the best frameworks (PHP) isn't supported fully by MySQL. Why can't PHP just remove the c if it fails to work?

[Blesta Addons]

7 hours ago, Adam said:

Sorry for the spam, but I am pretty sure I know what is going on. The issue was right in front of us -- the datetime is invalid  . You should really be using a timestamp (UNIX/epoch as an example).

 

Blesta uses PDO statements to insert into the database. When you create a PDO connection, you get to set the 'sql_mode'. Blesta's minPHP framework uses the system's default settings. It appears your system does NOT have the following enabled (system wide):

SET SQL_MODE='ALLOW_INVALID_DATES';

As mentioned earlier, when Blesta creates a PDO connection, it uses system defaults and not override anything for that session.

Why is this important, because PHP's date('c') produces invalid datetime for MySQL. Its explained fairly well in their documentation:

https://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sqlmode_allow_invalid_dates

 

You can see it live from a system I created to reproduce the issue:

MariaDB [datetest]> select @@sql_mode;
+----------------------------------------------------------------+
| @@sql_mode                                                     |
+----------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB [datetest]> INSERT INTO test (id, col1) VALUES (NULL, '2017-04-25T05:04:57+00:00');
ERROR 1292 (22007): Incorrect datetime value: '2017-04-25T05:04:57+00:00' for column 'col1' at row 1
MariaDB [datetest]> set @@sql_mode='ALLOW_INVALID_DATES';
Query OK, 0 rows affected (0.00 sec)

MariaDB [datetest]> INSERT INTO test (id, col1) VALUES (NULL, '2017-04-25T05:04:57+00:00');
Query OK, 1 row affected, 1 warning (0.01 sec)

MariaDB [datetest]> show warnings;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'col1' at row 1 |
+---------+------+-------------------------------------------+
1 row in set (0.00 sec)

MariaDB [datetest]> select * from test;
+----+---------------------+
| id | col1                |
+----+---------------------+
| 10 | 2017-04-25 05:04:57 |
+----+---------------------+
1 row in set (0.01 sec)

MariaDB [datetest]> 

 

You will notice that datetime format is different then what we inserted because date('c') from PHP is not accepted by MySQL. 

 

Again, issue is with your environment. Tricky to find if you do not have experience.

-Adam

 

very thank to your analyst and help . i'm not a experienced in MYSQL, but i have arrived to the date('c') issue but it was strange for me is not working in some part and is working in other part .

i have one more simple question, support manager (core) , and some other plugins and the core model use the dat('c') in their insertion, but didn't has the issue, and they are in the same installation blesta ? why only just few sql statements ?

where is the blesta Team? 1st may is not yet 

[Michael]

48 minutes ago, Blesta Addons said:

very thank to your analyst and help . i'm not a experienced in MYSQL, but i have arrived to the date('c') issue but it was strange for me is not working in some part and is working in other part .

i have one more simple question, support manager (core) , and some other plugins and the core model use the dat('c') in their insertion, but didn't has the issue, and they are in the same installation blesta ? why only just few sql statements ?

where is the blesta Team? 1st may is not yet 

Not sure i'll get Paul to look I thought c added the timezone so 2017-04-25T05:04:57+00:00 (Normal) 2017-04-25T05:04:57+02:00 = 2017-04-25T07:04:57

[Blesta Addons]

16 minutes ago, Licensecart said:

Not sure i'll get Paul to look I thought c added the timezone so 2017-04-25T05:04:57+00:00 (Normal) 2017-04-25T05:04:57+02:00 = 2017-04-25T07:04:57

the problem is not the timezone but the date format itself , @Adam has identified the issue . my question why the date('c') is accepted in invoices, orders, and so many place in blesta and is not added in some plugins?

[Adam]

2 hours ago, Blesta Addons said:

the problem is not the timezone but the date format itself , @Adam has identified the issue . my question why the date('c') is accepted in invoices, orders, and so many place in blesta and is not added in some plugins?

Thats because all the core code uses the dateToUtc function:

http://source-docs.blesta.com/class-AppModel.html#_dateToUtc
 

Quote

Converts dates and date times to a UTC date time stamp suitable for inserting into the database.

If $date does not contain any timezone information the company timezone setting will attempt to be used to disambiguate the timezone of the given date.

This method accepts many different date time formats including (but not limited to):

2010-12-31 12:00:00 PST 2010-12-31 12:00:00 UTC 2010-12-31T12:00:00 +0200 2010-12-31T12:00:00 +02:00

 

The function basically converts:

2017-04-25T14:43:00+00:00

which as you know is ISO 8601 format (date('c')) to something MySQL understands as explained in their documentation:

2017-04-25 14:43:00

-Adam

[Michael]

21 minutes ago, Adam said:

Thats because all the core code uses the dateToUtc function:

http://source-docs.blesta.com/class-AppModel.html#_dateToUtc
 

 

The function basically converts:

2017-04-25T14:43:00+00:00

which as you know is ISO 8601 format (date('c')) to something MySQL understands as explained in their documentation:

2017-04-25 14:43:00

-Adam

Ah  so we can use that code can't we mate? just using the dateToUtc function?

[Adam]

15 minutes ago, Licensecart said:

Ah  so we can use that code can't we mate? just using the dateToUtc function?

Yup. Its in the core of Blesta: app/app_model.php. Alternatively, do not use date('c') but perhaps a timestamp (UNIX/epoch, etc.).

 

One could make the argument to the Blesta guys that they should be checking the errorCode of a PDOStatement->execute within minPHP either in Records class of their PdoConnection class.

http://php.net/manual/en/pdostatement.errorinfo.php#refsect1-pdostatement.errorinfo-examples

-Adam