Error when creating a Date field

[bradaks]

Has anyone seen this error before? Not sure why but I get a Bad Server error when trying to rebuild after creating a Date entity:

[2017-10-11 04:41:12] Espo.ALERT: Rebuild database fault: PDOException: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'date_start' at row 12 in /opt/bitnami/apps/espocrm/htdocs/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:699 Stack trace: #0 /opt/bitnami/apps/espocrm/htdocs/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(699): PDO->query('ALTER TABLE `as...') #1 /opt/bitnami/apps/espocrm/htdocs/application/Espo/Core/Utils/Database/Schema/Schema.php(215): Doctrine\DBAL\Connection->executeQuery('ALTER TABLE `as...') #2 /opt/bitnami/apps/espocrm/htdocs/application/Espo/Core/DataManager.php(93): Espo\Core\Utils\Database\Schema\Schema->rebuild('Assignment') #3 /opt/bitnami/apps/espocrm/htdocs/application/Espo/Core/DataManager.php(60): Espo\Core\DataManager->rebuildDatabase('Assignment') #4 /opt/bitnami/apps/espocrm/htdocs/application/Espo/Controllers/FieldManager.php(71): Espo\Core\DataManager->rebuild('Assignment') #5 /opt/bitnami/apps/espocrm/htdocs/application/Espo/Core/ControllerManager.php(116): Espo\Controllers\FieldManager->postActionCreate(Array, Array, Object(Slim\Http\Request)) #6 /opt/bitnami/apps/espocrm/htdocs/application/Espo/Core/Application.php(226): Espo\Core\ControllerManager->process('FieldManager', 'create', Array, Array, Object(Slim\Http\Request)) #7 [internal function]: Espo\Core\Application->Espo\Core\{closure}() #8 /opt/bitnami/apps/espocrm/htdocs/vendor/slim/slim/Slim/Slim.php(1210): call_user_func_array(Object(Closure), Array) #9 /opt/bitnami/apps/espocrm/htdocs/vendor/slim/slim/Slim/Slim.php(1356): Slim\Slim->applyHook('slim.before.dis...') #10 /opt/bitnami/apps/espocrm/htdocs/vendor/slim/slim/Slim/Middleware/Flash.php(85): Slim\Slim->call() #11 /opt/bitnami/apps/espocrm/htdocs/vendor/slim/slim/Slim/Middleware/MethodOverride.php(92): Slim\Middleware\Flash->call() #12 /opt/bitnami/apps/espocrm/htdocs/application/Espo/Core/Utils/Api/Auth.php(112): Slim\Middleware\MethodOverride->call() #13 /opt/bitnami/apps/espocrm/htdocs/application/Espo/Core/Utils/Api/Slim.php(125): Espo\Core\Utils\Api\Auth->call() #14 /opt/bitnami/apps/espocrm/htdocs/application/Espo/Core/Application.php(88): Espo\Core\Utils\Api\Slim->run() #15 /opt/bitnami/apps/espocrm/htdocs/api/v1/index.php(33): Espo\Core\Application->run() #16 {main} Next Doctrine\DBAL\DBALException: An exception occurred while executing 'ALTER TABLE `assignment` ADD last_day_of_assignment11111 DATE DEFAULT NULL': SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'date_start' at row 12 in /opt/bitnami/apps/espocrm/htdocs/vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:91 Stack trace: #0 /opt/bitnami/apps/espocrm/htdocs/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(702): Doctrine\DBAL\DBALException::driverExceptionDuring Query(Object(PDOException), 'ALTER TABLE `as...', Array) #1 /opt/bitnami/apps/espocrm/htdocs/application/Espo/Core/Utils/Database/Schema/Schema.php(215): Doctrine\DBAL\Connection->executeQuery('ALTER TABLE `as...') #2 /opt/bitnami/apps/espocrm/htdocs/application/Espo/Core/DataManager.php(93): Espo\Core\Utils\Database\Schema\Schema->rebuild('Assignment') #3 /opt/bitnami/apps/espocrm/htdocs/application/Espo/Core/DataManager.php(60): Espo\Core\DataManager->rebuildDatabase('Assignment') #4 /opt/bitnami/apps/espocrm/htdocs/application/Espo/Controllers/FieldManager.php(71): Espo\Core\DataManager->rebuild('Assignment') #5 /opt/bitnami/apps/espocrm/htdocs/application/Espo/Core/ControllerManager.php(116): Espo\Controllers\FieldManager->postActionCreate(Array, Array, Object(Slim\Http\Request)) #6 /opt/bitnami/apps/espocrm/htdocs/application/Espo/Core/Application.php(226): Espo\Core\ControllerManager->process('FieldManager', 'create', Array, Array, Object(Slim\Http\Request)) #7 [internal function]: Espo\Core\Application->Espo\Core\{closure}() #8 /opt/bitnami/apps/espocrm/htdocs/vendor/slim/slim/Slim/Slim.php(1210): call_user_func_array(Object(Closure), Array) #9 /opt/bitnami/apps/espocrm/htdocs/vendor/slim/slim/Slim/Slim.php(1356): Slim\Slim->applyHook('slim.before.dis...') #10 /opt/bitnami/apps/espocrm/htdocs/vendor/slim/slim/Slim/Middleware/Flash.php(85): Slim\Slim->call() #11 /opt/bitnami/apps/espocrm/htdocs/vendor/slim/slim/Slim/Middleware/MethodOverride.php(92): Slim\Middleware\Flash->call() #12 /opt/bitnami/apps/espocrm/htdocs/application/Espo/Core/Utils/Api/Auth.php(112): Slim\Middleware\MethodOverride->call() #13 /opt/bitnami/apps/espocrm/htdocs/application/Espo/Core/Utils/Api/Slim.php(125): Espo\Core\Utils\Api\Auth->call() #14 /opt/bitnami/apps/espocrm/htdocs/application/Espo/Core/Application.php(88): Espo\Core\Utils\Api\Slim->run() #15 /opt/bitnami/apps/espocrm/htdocs/api/v1/index.php(33): Espo\Core\Application->run() #16 {main} [] []
[2017-10-11 04:41:12] Espo.ERROR: API [POST]:/Admin/fieldManager/:scope, Params:Array ( [scope] => Assignment ) , InputData: {"type":"date","required":false,"dynamicLogicVisib le":null,"dynamicLogicRequired":null,"dynamicLogic ReadOnly":null,"name":"lastDayOfAssignment11111"," label":"LastDayOfAssignment11111","tooltipText":"" ,"default":null,"after":"","before":"","audited":f alse,"readOnly":false,"tooltip":false} - Error while rebuilding database. See log file for details. [] []
[2017-10-11 04:41:12] Espo.ERROR: Display Error: Error while rebuilding database. See log file for details., Code: 500 URL: /espocrm/api/v1/Admin/fieldManager/Assignment [] []

[tanya]

Hello
At least one of your Date has no date start (check it in database). How could it happen?

[bradaks]

I See.. I migrated the database over to the Google Cloud (from Hostgator) to improve performance and security. I will get a workflow going to migrate all of the date_start fields. I removed them as a requirement in the old system due to the time not being able to show time zones properly, and the conversions for a lot of people inputting the data wasn't happening so I just made the field a VARCHAR field so they could write in the timezones. I will get those dates in there and let you know how it goes. Thanks so much!!