SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value #320

adi2k5 · 2020-08-18T13:10:25Z

Not sure if this is facerecognition issue or something with db configuration, but when tryig to migrate to new model, I'm getting an sql error.

Expected behaviour

Migrate from model 2 to model 4

Actual behaviour

After I've started migration from model 2 to model 4 I got immediate sql error

  [Doctrine\DBAL\Exception\DriverException]                                    
  An exception occurred while executing 'INSERT INTO `oc_facerecog_images` (`  
  user`, `file`, `model`, `is_processed`) VALUES(?, ?, ?, ?)' with params ["a  
  di", "9499824", 4, false]:                                                   
                                                                               
  SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: ''   
  for column `nextcloud`.`oc_facerecog_images`.`is_processed` at row 1

Steps to reproduce

Run php occ face:migrate -m 2 -u adi (model 4 is already set up as default)

Server configuration

Operating system: Fedora
Pdlib version: 1.0.2-1
How is DLib installed: As in previous issue, using copr from matias
How is PDlib installed: Make sure it is working correctly with this tool
PHP version: 7.3.20
Web server: Lighttpd
Database: mariadb
Nextcloud version: 19.0.1

Logs

Background task log with debug.

php occ face:migrate -m 2 -u adi

$ php occ face:migrate -m 2 -u adi -vvv
Will be migrated <adi> from model <2>
      0/142597 [>---------------------------]   0% < 1 sec/< 1 sec 92.0 MiB
In AbstractMySQLDriver.php line 106:
                                                                               
  [Doctrine\DBAL\Exception\DriverException]                                    
  An exception occurred while executing 'INSERT INTO `oc_facerecog_images` (`  
  user`, `file`, `model`, `is_processed`) VALUES(?, ?, ?, ?)' with params ["a  
  di", "9499824", 4, false]:                                                   
                                                                               
  SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: ''   
  for column `nextcloud`.`oc_facerecog_images`.`is_processed` at row 1         
                                                                               

Exception trace:
  at /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:106
 Doctrine\DBAL\Driver\AbstractMySQLDriver->convertException() at /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:169
 Doctrine\DBAL\DBALException::wrapException() at /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:145
 Doctrine\DBAL\DBALException::driverExceptionDuringQuery() at /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:1063
 Doctrine\DBAL\Connection->executeUpdate() at /var/www/nextcloud/lib/private/DB/Connection.php:214
 OC\DB\Connection->executeUpdate() at /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php:203
 Doctrine\DBAL\Query\QueryBuilder->execute() at /var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php:216
 OC\DB\QueryBuilder\QueryBuilder->execute() at /var/www/nextcloud/lib/public/AppFramework/Db/QBMapper.php:129
 OCP\AppFramework\Db\QBMapper->insert() at /var/www/nextcloud/apps/facerecognition/lib/Command/MigrateCommand.php:232
 OCA\FaceRecognition\Command\MigrateCommand->migrateImage() at /var/www/nextcloud/apps/facerecognition/lib/Command/MigrateCommand.php:204
 OCA\FaceRecognition\Command\MigrateCommand->migrateUser() at /var/www/nextcloud/apps/facerecognition/lib/Command/MigrateCommand.php:183
 OCA\FaceRecognition\Command\MigrateCommand->execute() at /var/www/nextcloud/3rdparty/symfony/console/Command/Command.php:255
 Symfony\Component\Console\Command\Command->run() at /var/www/nextcloud/3rdparty/symfony/console/Application.php:1012
 Symfony\Component\Console\Application->doRunCommand() at /var/www/nextcloud/3rdparty/symfony/console/Application.php:272
 Symfony\Component\Console\Application->doRun() at /var/www/nextcloud/3rdparty/symfony/console/Application.php:148
 Symfony\Component\Console\Application->run() at /var/www/nextcloud/lib/private/Console/Application.php:215
 OC\Console\Application->run() at /var/www/nextcloud/console.php:100
 require_once() at /var/www/nextcloud/occ:11

In PDOStatement.php line 119:
                                                                               
  [Doctrine\DBAL\Driver\PDOException (22007)]                                  
  SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: ''   
  for column `nextcloud`.`oc_facerecog_images`.`is_processed` at row 1         
                                                                               

Exception trace:
  at /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:119
 Doctrine\DBAL\Driver\PDOStatement->execute() at /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:1054
 Doctrine\DBAL\Connection->executeUpdate() at /var/www/nextcloud/lib/private/DB/Connection.php:214
 OC\DB\Connection->executeUpdate() at /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php:203
 Doctrine\DBAL\Query\QueryBuilder->execute() at /var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php:216
 OC\DB\QueryBuilder\QueryBuilder->execute() at /var/www/nextcloud/lib/public/AppFramework/Db/QBMapper.php:129
 OCP\AppFramework\Db\QBMapper->insert() at /var/www/nextcloud/apps/facerecognition/lib/Command/MigrateCommand.php:232
 OCA\FaceRecognition\Command\MigrateCommand->migrateImage() at /var/www/nextcloud/apps/facerecognition/lib/Command/MigrateCommand.php:204
 OCA\FaceRecognition\Command\MigrateCommand->migrateUser() at /var/www/nextcloud/apps/facerecognition/lib/Command/MigrateCommand.php:183
 OCA\FaceRecognition\Command\MigrateCommand->execute() at /var/www/nextcloud/3rdparty/symfony/console/Command/Command.php:255
 Symfony\Component\Console\Command\Command->run() at /var/www/nextcloud/3rdparty/symfony/console/Application.php:1012
 Symfony\Component\Console\Application->doRunCommand() at /var/www/nextcloud/3rdparty/symfony/console/Application.php:272
 Symfony\Component\Console\Application->doRun() at /var/www/nextcloud/3rdparty/symfony/console/Application.php:148
 Symfony\Component\Console\Application->run() at /var/www/nextcloud/lib/private/Console/Application.php:215
 OC\Console\Application->run() at /var/www/nextcloud/console.php:100
 require_once() at /var/www/nextcloud/occ:11

In PDOStatement.php line 117:
                                                                               
  [PDOException (22007)]                                                       
  SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value: ''   
  for column `nextcloud`.`oc_facerecog_images`.`is_processed` at row 1         
                                                                               

Exception trace:
  at /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:117
 PDOStatement->execute() at /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:117
 Doctrine\DBAL\Driver\PDOStatement->execute() at /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:1054
 Doctrine\DBAL\Connection->executeUpdate() at /var/www/nextcloud/lib/private/DB/Connection.php:214
 OC\DB\Connection->executeUpdate() at /var/www/nextcloud/3rdparty/doctrine/dbal/lib/Doctrine/DBAL/Query/QueryBuilder.php:203
 Doctrine\DBAL\Query\QueryBuilder->execute() at /var/www/nextcloud/lib/private/DB/QueryBuilder/QueryBuilder.php:216
 OC\DB\QueryBuilder\QueryBuilder->execute() at /var/www/nextcloud/lib/public/AppFramework/Db/QBMapper.php:129
 OCP\AppFramework\Db\QBMapper->insert() at /var/www/nextcloud/apps/facerecognition/lib/Command/MigrateCommand.php:232
 OCA\FaceRecognition\Command\MigrateCommand->migrateImage() at /var/www/nextcloud/apps/facerecognition/lib/Command/MigrateCommand.php:204
 OCA\FaceRecognition\Command\MigrateCommand->migrateUser() at /var/www/nextcloud/apps/facerecognition/lib/Command/MigrateCommand.php:183
 OCA\FaceRecognition\Command\MigrateCommand->execute() at /var/www/nextcloud/3rdparty/symfony/console/Command/Command.php:255
 Symfony\Component\Console\Command\Command->run() at /var/www/nextcloud/3rdparty/symfony/console/Application.php:1012
 Symfony\Component\Console\Application->doRunCommand() at /var/www/nextcloud/3rdparty/symfony/console/Application.php:272
 Symfony\Component\Console\Application->doRun() at /var/www/nextcloud/3rdparty/symfony/console/Application.php:148
 Symfony\Component\Console\Application->run() at /var/www/nextcloud/lib/private/Console/Application.php:215
 OC\Console\Application->run() at /var/www/nextcloud/console.php:100
 require_once() at /var/www/nextcloud/occ:11

face:migrate [-m|--model_id MODEL_ID] [-u|--user_id USER_ID]

$

Nextcloud log (data/nextcloud.log)

No errors in nextcloud logs

Browser log

Not applicable, command run from cli.

The text was updated successfully, but these errors were encountered:

matiasdelellis · 2020-08-18T13:54:13Z

Pretty strange.. 😕

Do you have access to the database? Can you check the data types of the oc_facerecog_images table?

Columna	Tipo	Comentario
id	int(11) Incremento automático
user	varchar(64)
file	int(11)
model	int(11)
is_processed	tinyint(1) [0]
error	varchar(1024) NULL
last_processed_time	datetime NULL
processing_duration	bigint(20) NULL

adi2k5 · 2020-08-18T14:19:25Z

Seems to be correct

MariaDB [***]> desc oc_facerecog_images;
+---------------------+---------------+------+-----+---------+----------------+
| Field               | Type          | Null | Key | Default | Extra          |
+---------------------+---------------+------+-----+---------+----------------+
| id                  | int(11)       | NO   | PRI | NULL    | auto_increment |
| user                | varchar(64)   | NO   | MUL | NULL    |                |
| file                | int(11)       | NO   | MUL | NULL    |                |
| model               | int(11)       | NO   | MUL | NULL    |                |
| is_processed        | tinyint(1)    | NO   |     | 0       |                |
| error               | varchar(1024) | YES  |     | NULL    |                |
| last_processed_time | datetime      | YES  |     | NULL    |                |
| processing_duration | bigint(20)    | YES  |     | NULL    |                |
+---------------------+---------------+------+-----+---------+----------------+

edit. just to confirm, can migration be run before face recognition using model 2 has been completely finished? Model 2 is working since about 2 months now (scheduled to be started each 30 minutes) and finished scanning of about 2/3 of all images,
so for now I've disabled recoginition job from cron (proces is stopped now) to migrate to model 4 what is already scanned, and finish scanning rest using model 4.

matiasdelellis · 2020-08-18T14:36:42Z

Seems to be correct

I'm honestly clueless.
The error is about is_processed, which is a boolean, handled in db as tinyint, which is correct. and the inserted value is false. 😕

edit. just to confirm, can migration be run before face recognition using model 2 has been completely finished?

Yes. Everything will migrate in the current state except the assigned clusters and names.

Model 2 is working since about 2 months now (scheduled to be started each 30 minutes) and finished scanning of about 2/3 of all images,
so for now I've disabled recoginition job from cron (proces is stopped now) to migrate to model 4 what is already scanned, and finish scanning rest using model 4.

Ok .. Please, reset model 4 just and try again just as test.

[matias@nube nextcloud]$ sudo -u apache php occ face:setup
[sudo] password for matias: 
You must indicate the ID of the model to install
+----+---------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id | Enabled | Name        | Description                                                                                                                                              |
+----+---------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1  |         | Default     | Main model, using dlib defaults: mmod_human_face_detector.dat, shape_predictor_5_face_landmarks.dat and dlib_face_recognition_resnet_model_v1.dat        |
| 2  |         | DlibCnn68   | Alternative default model, using dlib: mmod_human_face_detector.dat, shape_predictor_68_face_landmarks.dat and dlib_face_recognition_resnet_model_v1.dat |
| 3  |         | DlibHog     | DDlib HOG Model which needs lower requirements                                                                                                           |
| 4  | *       | DlibCnnHog5 | Extends the main model, doing a face validation with the Hog detector                                                                                    |
+----+---------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------+
[matias@nube nextcloud]$ sudo -u apache php occ face:reset --model -vvv -u user
Warning: This command is not reversible. Do you want to continue? [y/N]y
Reset model successfully done
[matias@nube nextcloud]$ sudo -u apache php occ face:migrate --user_id user -m 2 -vvv
Will be migrated <user> from model <2>
  19/433 [=>--------------------------]   4% 12 secs/4 mins 14.0 MiB
[matias@nube nextcloud]$

adi2k5 · 2020-08-18T15:08:51Z

Tried the reset, still the same. I've tried to put that one manually into the DB, and it worked like a charm:

MariaDB [***]> INSERT INTO `oc_facerecog_images` (`user`, `file`, `model`, `is_processed`) VALUES ("adi", "9499824", 4, false);                                                 
Query OK, 1 row affected (0.001 sec)

MariaDB [***]> Bye

but I'm not 100% sure if this is the exact command face recognition is trying to execute, maybe there's some typo or something else in there? Any idea if and how could I check the command face recognition is trying to execute?

matiasdelellis · 2020-08-18T15:29:16Z

but I'm not 100% sure if this is the exact command face recognition is trying to execute, maybe there's some typo or something else in there? Any idea if and how could I check the command face recognition is trying to execute?

Yes.. Your query seems correct, but it is difficult to know what doctrine does. 😞

The most similar report I found is this, but I still do not relate it to our problem.

Insert/Update methods do not work for boolean fields in QBMapper nextcloud/server#11236

matiasdelellis · 2020-08-18T15:41:10Z

@adi2k5
Do you dare to experiment a little?

Try this patch..

diff --git a/lib/Db/Image.php b/lib/Db/Image.php
index edb009e..ebb6a7e 100644
--- a/lib/Db/Image.php
+++ b/lib/Db/Image.php
@@ -100,6 +100,10 @@ class Image extends Entity implements JsonSerializable {
        */
        protected $processingDuration;
 
+       public function __construct() {
+               $this->addType('isProcessed', 'bool');
+       }
+
        public function jsonSerialize() {
                return [
                        'id' => $this->id,

adi2k5 · 2020-08-18T16:13:16Z

@matiasdelellis Bingo, that was it. Migration is running smooth now :)

$ php occ face:migrate -m 2 -u adi -vvv
Will be migrated <adi> from model <2>
    191/142597 [>---------------------------]   0% 46 secs/9 hrs  142.0 MiB

matiasdelellis · 2020-08-18T16:28:21Z

Wow..
I still don't know why it fails you when we have the same configured system, but I will apply this change tomorrow.. 😅

Note that this command cannot be paused or restarted. You have to finish it completely.

p.s: When migrating, you will not have all the benefits of model 4 (Will result as the model 1), but it will certainly be much better than model 2.

adi2k5 · 2020-08-18T16:34:48Z

I have no idea either, obviously my db instance may not know that 'false' mean bool value, otherwise I don't know what could be the problem. As for result, yes, I read already that it will be somehow limited, but still, if model 2 is running for bit over 2 months now and processed about 2/3 of my photos (well, I have plenty of them), then doing full rescan on model 4 would take... well, let's say that migration will be enough for now I think ;) Strangely, at the beginning it was showing estimated finish time as 14 days, now 4 days left 😄

matiasdelellis · 2020-08-18T16:43:07Z

Strangely, at the beginning it was showing estimated finish time as 14 days, now 4 days left ;)

The estimated time is calculated directly by Synfony's progress bar. It does this by averaging and as it progresses it is more accurate. 😉

Well.. If originally it took 2 months, and you migrate it in 4 days, I am quite satisfied .. 😄

adi2k5 · 2020-08-18T18:27:29Z

I meant that it was showing 4 days remaining to finish full initial scan from original calculation of 14 days (even though the scan was running for 2 months already) 😉 my guess is that after migration will finish in some next 2-3 days, then the rest of initial scan will take next 1-2 months to finish with all photos 😄

Fix Invalid datetime format. Incorrect integer value. Issue #320

matiasdelellis · 2020-08-27T22:18:07Z

Fixed on PR #324

Thank for report it..

matiasdelellis added a commit that referenced this issue Aug 27, 2020

Fix Invalid datetime format. Incorrect integer value. Issue #320

aa1ce52

matiasdelellis added a commit that referenced this issue Aug 27, 2020

Merge pull request #324 from matiasdelellis/invalid-value

de56a6f

Fix Invalid datetime format. Incorrect integer value. Issue #320

matiasdelellis closed this as completed Aug 27, 2020

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value #320

SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value #320

adi2k5 commented Aug 18, 2020 •

edited

matiasdelellis commented Aug 18, 2020

adi2k5 commented Aug 18, 2020 •

edited

matiasdelellis commented Aug 18, 2020 •

edited

adi2k5 commented Aug 18, 2020

matiasdelellis commented Aug 18, 2020

matiasdelellis commented Aug 18, 2020

adi2k5 commented Aug 18, 2020

matiasdelellis commented Aug 18, 2020

adi2k5 commented Aug 18, 2020 •

edited

matiasdelellis commented Aug 18, 2020

adi2k5 commented Aug 18, 2020

matiasdelellis commented Aug 27, 2020

SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value #320

SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect integer value #320

Comments

adi2k5 commented Aug 18, 2020 • edited

Expected behaviour

Actual behaviour

Steps to reproduce

Server configuration

Logs

Background task log with debug.

Nextcloud log (data/nextcloud.log)

Browser log

matiasdelellis commented Aug 18, 2020

adi2k5 commented Aug 18, 2020 • edited

matiasdelellis commented Aug 18, 2020 • edited

adi2k5 commented Aug 18, 2020

matiasdelellis commented Aug 18, 2020

matiasdelellis commented Aug 18, 2020

adi2k5 commented Aug 18, 2020

matiasdelellis commented Aug 18, 2020

adi2k5 commented Aug 18, 2020 • edited

matiasdelellis commented Aug 18, 2020

adi2k5 commented Aug 18, 2020

matiasdelellis commented Aug 27, 2020

adi2k5 commented Aug 18, 2020 •

edited

adi2k5 commented Aug 18, 2020 •

edited

matiasdelellis commented Aug 18, 2020 •

edited

adi2k5 commented Aug 18, 2020 •

edited