Upgrade issue - 3.2.2 -> 3.3.0

[wtconseil]

Hello

trying to upgrade from 3.2.2 to 3.3.0

I got this exception
Thanks !

, InputData: {"id":"557749c483e32"} - Error while rebuilding database. See log file for details. [] []
[2015-06-09 20:17:11] Espo.ERROR: Display Error: Error while rebuilding database. See log file for details., Code: 500 URL: /api/v1/Admin/action/runUpgrade [] []
[2015-06-09 20:17:16] Espo.ERROR: Uncaught exception {"exception":"[object] (PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'user.avatar' in 'field list' at /usr/share/nginx/html/espo/application/Espo/ORM/DB/Mapper.php:138)"} []

[wtconseil]

Here is the table user
mysql> desc user;
+-----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| id | varchar(24) | NO | PRI | NULL | |
| is_admin | tinyint(1) | YES | | 0 | |
| user_name | varchar(50) | YES | | NULL | |
| salutation_name | varchar(255) | YES | | NULL | |
| first_name | varchar(100) | YES | | | |
| last_name | varchar(100) | YES | | | |
| password | varchar(150) | YES | | NULL | |
| is_active | tinyint(1) | YES | | 1 | |
| title | varchar(100) | YES | | NULL | |
| deleted | tinyint(1) | YES | | 0 | |
| default_team_id | varchar(24) | YES | MUL | NULL | |
| avatar_id | varchar(24) | YES | MUL | NULL | |
+-----------------+--------------+------+-----+---------+-------+

[wtconseil]

Here is my first exception

[2015-06-09 21:02:19] Espo.ALERT: Rebuild database fault: exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1075 Incorrect table definition; there can be only one auto column and it must be defined as a key' in /usr/share/nginx/html/espo/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php:699
Stack trace:
#0 /usr/share/nginx/html/espo/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(699): PDO->query('DROP INDEX UNIQ...')
#1 /usr/share/nginx/html/espo/application/Espo/Core/Utils/Database/Schema/Schema.php(203): Doctrine\DBAL\Connection->executeQuery('DROP INDEX UNIQ...')
#2 /usr/share/nginx/html/espo/application/Espo/Core/DataManager.php(84): Espo\Core\Utils\Database\Schema\Schema->rebuild(NULL)
#3 /usr/share/nginx/html/espo/application/Espo/Core/DataManager.php(53): Espo\Core\DataManager->rebuildDatabase(NULL)
#4 /usr/share/nginx/html/espo/application/Espo/Core/Upgrades/Actions/Base.php(499): Espo\Core\DataManager->rebuild()
#5 /usr/share/nginx/html/espo/application/Espo/Core/Upgrades/Actions/Base/Install.php(75): Espo\Core\Upgrades\Actions\Base->systemRebuild()
#6 /usr/share/nginx/html/espo/application/Espo/Core/Upgrades/ActionManager.php(77): Espo\Core\Upgrades\Actions\Base\Install->run(Array)
#7 /usr/share/nginx/html/espo/application/Espo/Core/Upgrades/Base.php(78): Espo\Core\Upgrades\ActionManager->run(Array)
#8 /usr/share/nginx/html/espo/application/Espo/Controllers/Admin.php(75): Espo\Core\Upgrades\Base->install(Array)
#9 /usr/share/nginx/html/espo/application/Espo/Core/ControllerManager.php(99): Espo\Controllers\Admin->actionRunUpgrade(Array, Array, Object(Slim\Http\Request))
#10 /usr/share/nginx/html/espo/application/Espo/Core/Application.php(213): Espo\Core\ControllerManager->process('Admin', 'runUpgrade', Array, '{"id":"55775455...', Object(Slim\Http\Request))
#11 [internal function]: Espo\Core\Application->Espo\Core\{closure}(NULL)
#12 /usr/share/nginx/html/espo/vendor/slim/slim/Slim/Slim.php(1175): call_user_func(Object(Closure), NULL)
#13 /usr/share/nginx/html/espo/vendor/slim/slim/Slim/Slim.php(1315): Slim\Slim->applyHook('slim.before.dis...')
#14 /usr/share/nginx/html/espo/vendor/slim/slim/Slim/Middleware/Flash.php(85): Slim\Slim->call()
#15 /usr/share/nginx/html/espo/vendor/slim/slim/Slim/Middleware/MethodOverride.php(92): Slim\Middleware\Flash->call()
#16 /usr/share/nginx/html/espo/application/Espo/Core/Utils/Api/Auth.php(95): Slim\Middleware\MethodOverride->call()
#17 /usr/share/nginx/html/espo/application/Espo/Core/Utils/Api/Slim.php(45): Espo\Core\Utils\Api\Auth->call()
#18 /usr/share/nginx/html/espo/application/Espo/Core/Application.php(83): Espo\Core\Utils\Api\Slim->run()
#19 /usr/share/nginx/html/espo/api/v1/index.php(26): Espo\Core\Application->run()
#20 {main}

Next exception 'Doctrine\DBAL\DBALException' with message 'An exception occurred while executing 'DROP INDEX UNIQ_4C62E638E34CE745 ON `contact`':

SQLSTATE[42000]: Syntax error or access violation: 1075 Incorrect table definition; there can be only one auto column and it must be defined as a key' in /usr/share/nginx/html/espo/vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php:91
Stack trace:
#0 /usr/share/nginx/html/espo/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php(702): Doctrine\DBAL\DBALException::driverExceptionDuring Query(Object(PDOException), 'DROP INDEX UNIQ...', Array)
#1 /usr/share/nginx/html/espo/application/Espo/Core/Utils/Database/Schema/Schema.php(203): Doctrine\DBAL\Connection->executeQuery('DROP INDEX UNIQ...')
#2 /usr/share/nginx/html/espo/application/Espo/Core/DataManager.php(84): Espo\Core\Utils\Database\Schema\Schema->rebuild(NULL)
#3 /usr/share/nginx/html/espo/application/Espo/Core/DataManager.php(53): Espo\Core\DataManager->rebuildDatabase(NULL)
#4 /usr/share/nginx/html/espo/application/Espo/Core/Upgrades/Actions/Base.php(499): Espo\Core\DataManager->rebuild()
#5 /usr/share/nginx/html/espo/application/Espo/Core/Upgrades/Actions/Base/Install.php(75): Espo\Core\Upgrades\Actions\Base->systemRebuild()
#6 /usr/share/nginx/html/espo/application/Espo/Core/Upgrades/ActionManager.php(77): Espo\Core\Upgrades\Actions\Base\Install->run(Array)
#7 /usr/share/nginx/html/espo/application/Espo/Core/Upgrades/Base.php(78): Espo\Core\Upgrades\ActionManager->run(Array)
#8 /usr/share/nginx/html/espo/application/Espo/Controllers/Admin.php(75): Espo\Core\Upgrades\Base->install(Array)
#9 /usr/share/nginx/html/espo/application/Espo/Core/ControllerManager.php(99): Espo\Controllers\Admin->actionRunUpgrade(Array, Array, Object(Slim\Http\Request))
#10 /usr/share/nginx/html/espo/application/Espo/Core/Application.php(213): Espo\Core\ControllerManager->process('Admin', 'runUpgrade', Array, '{"id":"55775455...', Object(Slim\Http\Request))
#11 [internal function]: Espo\Core\Application->Espo\Core\{closure}(NULL)
#12 /usr/share/nginx/html/espo/vendor/slim/slim/Slim/Slim.php(1175): call_user_func(Object(Closure), NULL)
#13 /usr/share/nginx/html/espo/vendor/slim/slim/Slim/Slim.php(1315): Slim\Slim->applyHook('slim.b... [] []
[2015-06-09 21:02:19] Espo.ERROR: API [POST]:/:controller/action/:action, Params:Array
(
[controller] => Admin
[action] => runUpgrade
)
, InputData: {"id":"55775455bf15b"} - Error while rebuilding database. See log file for details. [] []
[2015-06-09 21:02:19] Espo.ERROR: Display Error: Error while rebuilding database. See log file for details., Code: 500 URL: /api/v1/Admin/action/runUpgrade [] []

[wtconseil]

Hmmm ... that's strange.. I guess there is one missing check ...

here is the structure of my "contact" table

mysql> desc contact;
+---------------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+--------------+------+-----+---------+----------------+
| id | varchar(24) | NO | PRI | NULL | |
| salutation_name | varchar(255) | YES | | NULL | |
| first_name | varchar(100) | YES | MUL | | |
| last_name | varchar(100) | YES | | | |
| account_id | varchar(24) | YES | MUL | NULL | |
| description | longtext | YES | | NULL | |
| do_not_call | tinyint(1) | YES | | 0 | |
| address_street | varchar(255) | YES | | NULL | |
| address_city | varchar(255) | YES | | NULL | |
| address_state | varchar(255) | YES | | NULL | |
| address_country | varchar(255) | YES | | NULL | |
| address_postal_code | varchar(255) | YES | | NULL | |
| created_at | datetime | YES | | NULL | |
| modified_at | datetime | YES | | NULL | |
| deleted | tinyint(1) | YES | | 0 | |
| campaign_id | varchar(24) | YES | MUL | NULL | |
| created_by_id | varchar(24) | YES | MUL | NULL | |
| modified_by_id | varchar(24) | YES | MUL | NULL | |
| assigned_user_id | varchar(24) | YES | MUL | NULL | |
| champ_seb | varchar(255) | YES | | NULL | |
| test_anne_so | varchar(255) | YES | | NULL | |
| numero_fiche | int(11) | NO | UNI | NULL | auto_increment |
| type_contact1 | longtext | YES | | NULL | |
| test_list | varchar(255) | YES | | NULL | |
| test_multi | longtext | YES | | NULL | |
| statut_fiche | varchar(255) | YES | | NULL | |
+---------------------+--------------+------+-----+---------+----------------+
26 rows in set (0.00 sec)


There is a custom fields ... called numero_fiche (auto_increment).
It seems to have some side-effects on the upgrade process...

It's not critical for me, so i delete it !

mysql> alter table contact drop column numero_fiche;
Query OK, 29 rows affected (0.01 sec)
Records: 29 Duplicates: 0 Warnings: 0

Now, it's ok
Current version: 3.3.0

Could you just let me know if I did something wrong ?

[yuri]

There were duplicates in your auto increment column. You could just remove them before upgrade/rebuild.

[wtconseil]

Hi
yep, that's what i did.

I remove the field through the webUI (entity manager) but physically, the column was not dropped into the DB. is it normal ?
So, i remove it manually

=> alter table contact drop column numero_fiche;

Maybe you should add a warning when adding a new attritute as "auto_increment" to say that ?

Thanks for your work