Database error: Differences found between database and vardefs

šŸ“¦ Install & Upgrade Help
1

Hi there,

i have a problem when upgrading suitecrm ( Version 7.10.11 - installed on a debian 9 virtual machine, running on a qnap-nas).

When trying the upgrade wizard i get:

The output from the .log is

Tue Apr 30 09:14:49 2019 [926][1][FATAL] Mysqli_query failed.
Tue Apr 30 09:14:49 2019 [926][1][FATAL] Error creating table: sampl_samples: Query Failed: /* Table : sampl_samples */
/* MISSING TABLE: sampl_samples */
CREATE TABLE sampl_samples (id char(36)  NOT NULL ,name varchar(255)  NULL ,date_entered datetime  NULL ,date_modified datetime  NULL ,modified_user_id char(36)  NULL ,created_by char(36)  NULL ,description text  NULL ,deleted bool  DEFAULT '0' NULL ,assigned_user_id char(36)  NULL ,sampl_samples_number int(11)  DEFAULT '000001' NOT NULL auto_increment,type varchar(100)  NULL ,status varchar(100)  NULL ,priority varchar(100)  NULL ,resolution varchar(100)  NULL ,work_log text  NULL ,account_id_c char(36)  NULL ,aos_products_id_c char(36)  NULL ,project_id_c char(36)  NULL ,trackingid text  NULL ,trackingurl varchar(255)  NULL ,batch_lotid varchar(255)  NULL ,sampleqty varchar(255)  NULL ,account_id1_c char(36)  NULL ,user_id_c char(36)  NULL  , PRIMARY KEY (id),   UNIQUE sampl_samplesnumk (sampl_samples_number)) CHARACTER SET utf8 COLLATE utf8_general_ci
: MySQL error 1067: Invalid default value for 'sampl_samples_number'
Tue Apr 30 09:14:49 2019 [926][1][FATAL] Exception handling in /var/www/html/include/MVC/Controller/SugarController.php:400
Tue Apr 30 09:14:49 2019 [926][1][FATAL] Exception in Controller: Database failure. Please refer to suitecrm.log for details.
Tue Apr 30 09:14:49 2019 [926][1][FATAL] backtrace:
#0 /var/www/html/include/database/DBManager.php(353): sugar_die('Database failur...')
#1 /var/www/html/include/database/DBManager.php(328): DBManager->registerError('Error creating ...', 'Error creating ...', true)
#2 /var/www/html/include/database/MysqliManager.php(179): DBManager->checkError('Error creating ...', true)
#3 /var/www/html/include/database/MysqlManager.php(593): MysqliManager->query('/* Table : samp...', true, 'Error creating ...')
#4 /var/www/html/include/database/DBManager.php(764): MysqlManager->repairTableParams('sampl_samples', Array, Array, true, NULL)
#5 /var/www/html/modules/UpgradeWizard/end.php(86): DBManager->repairTable(Object(sampl_Samples), true)
#6 /var/www/html/modules/UpgradeWizard/index.php(296): require('/var/www/html/m...')
#7 /var/www/html/include/MVC/View/SugarView.php(818): include_once('/var/www/html/m...')
#8 /var/www/html/include/MVC/View/views/view.classic.php(72): SugarView->includeClassicFile('modules/sampl_S...')
#9 /var/www/html/include/MVC/View/SugarView.php(211): ViewClassic->display()
#10 /var/www/html/include/MVC/Controller/SugarController.php(435): SugarView->process()
#11 /var/www/html/include/MVC/Controller/SugarController.php(375): SugarController->processView()
#12 /var/www/html/include/MVC/SugarApplication.php(113): SugarController->execute()
#13 /var/www/html/index.php(52): SugarApplication->execute()
#14 {main}

As a possible solution i found in another forum thread to do a quick repair

But when trying Repair->Quick Repair and Rebuild i get:

[quote]Differences found between database and vardefs

The following script will sync the database structure with the structure defined in the vardefs. You have the option of exporting this script and then running it against your database using external database management tools, or to allow the administration module to run the script.

NOTE: any changes you make to the script in the textbox will be reflected in the exported or executed code.[/quote]

/* Table : sampl_samples */
/* MISSING TABLE: sampl_samples */
CREATE TABLE sampl_samples (id char(36)  NOT NULL ,name varchar(255)  NULL ,date_entered datetime  NULL ,date_modified datetime  NULL ,modified_user_id char(36)  NULL ,created_by char(36)  NULL ,description text  NULL ,deleted bool  DEFAULT '0' NULL ,assigned_user_id char(36)  NULL ,sampl_samples_number int(11)  DEFAULT '000001' NOT NULL auto_increment,type varchar(100)  NULL ,status varchar(100)  NULL ,priority varchar(100)  NULL ,resolution varchar(100)  NULL ,work_log text  NULL ,account_id_c char(36)  NULL ,aos_products_id_c char(36)  NULL ,project_id_c char(36)  NULL ,trackingid text  NULL ,trackingurl varchar(255)  NULL ,batch_lotid varchar(255)  NULL ,sampleqty varchar(255)  NULL ,account_id1_c char(36)  NULL ,user_id_c char(36)  NULL  , PRIMARY KEY (id),   UNIQUE sampl_samplesnumk (sampl_samples_number)) CHARACTER SET utf8 COLLATE utf8_general_ci;

But while in the other thread hitting the execute button unter the code solved it - for me it results again in:

and the log showing:

Tue Apr 30 09:20:24 2019 [928][1][FATAL] Mysqli_query failed.
Tue Apr 30 09:20:24 2019 [928][1][FATAL] Executing repair query:  Query Failed: CREATE TABLE sampl_samples (id char(36)  NOT NULL ,name varchar(255)  NULL ,date_entered datetime  NULL ,date_modified datetime  NULL ,modified_user_id char(36)  NULL ,created_by char(36)  NULL ,description text  NULL ,deleted bool  DEFAULT '0' NULL ,assigned_user_id char(36)  NULL ,sampl_samples_number int(11)  DEFAULT '000001' NOT NULL auto_increment,type varchar(100)  NULL ,status varchar(100)  NULL ,priority varchar(100)  NULL ,resolution varchar(100)  NULL ,work_log text  NULL ,account_id_c char(36)  NULL ,aos_products_id_c char(36)  NULL ,project_id_c char(36)  NULL ,trackingid text  NULL ,trackingurl varchar(255)  NULL ,batch_lotid varchar(255)  NULL ,sampleqty varchar(255)  NULL ,account_id1_c char(36)  NULL ,user_id_c char(36)  NULL  , PRIMARY KEY (id),   UNIQUE sampl_samplesnumk (sampl_samples_number)) CHARACTER SET utf8 COLLATE utf8_general_ci: MySQL error 1067: Invalid default value for 'sampl_samples_number'
Tue Apr 30 09:20:24 2019 [928][1][FATAL] Exception handling in /var/www/html/include/MVC/Controller/SugarController.php:400
Tue Apr 30 09:20:24 2019 [928][1][FATAL] Exception in Controller: Database failure. Please refer to suitecrm.log for details.
Tue Apr 30 09:20:24 2019 [928][1][FATAL] backtrace:
#0 /var/www/html/include/database/DBManager.php(353): sugar_die('Database failur...')
#1 /var/www/html/include/database/DBManager.php(328): DBManager->registerError('Executing repai...', 'Executing repai...', true)
#2 /var/www/html/include/database/MysqliManager.php(179): DBManager->checkError('Executing repai...', true)
#3 /var/www/html/modules/Administration/repairDatabase.php(93): MysqliManager->query('CREATE TABLE sa...', true, 'Executing repai...')
#4 /var/www/html/include/MVC/View/SugarView.php(818): include_once('/var/www/html/m...')
#5 /var/www/html/include/MVC/View/views/view.classic.php(72): SugarView->includeClassicFile('modules/Adminis...')
#6 /var/www/html/include/MVC/View/SugarView.php(211): ViewClassic->display()
#7 /var/www/html/include/MVC/Controller/SugarController.php(435): SugarView->process()
#8 /var/www/html/include/MVC/Controller/SugarController.php(375): SugarController->processView()
#9 /var/www/html/include/MVC/SugarApplication.php(113): SugarController->execute()
#10 /var/www/html/index.php(52): SugarApplication->execute()
#11 {main}

any hint or tip how to solve this would be great.

Also off-topic: is there a rss-feed or similar to get short infos, when a new security or lts release is available? I couldnā€™t find something like that.

2

Subscribe to the ā€œAnnouncementsā€ category here in the Forums. You will receive en email notification whenever a new Announecement is made, and this includes any new releases (and not much more).

About your issue, what is your Database make and version?

3

thx for your reply.
I am not sure what a database make is - so ill flood you with the information i think it is:

MySQL Database Server
MySQL version 10.1.37 (installed via debian package mariadb-server stable 10.1.37-0+deb9u1 all),
mariadb-client is same version. OS is debian 9 / Turnkey Linux Core 15.0

4

Your Database is MariaDB 10.1, that is supported.

I notice that this table that is erroring out is not a default SuiteCRM table. So you should have a look at any add-ons, customization, etc., that might be generating this error.

Try scanning your custom folder for ā€œsampl_samplesā€ to see what is there, and whether you really need itā€¦

5

Hello,

The error in the log is: [quote] error 1067: Invalid default value for 'sampl_samples_number [/quote]

And the query shows:

So, try to change the default values for this column to 1, like so:

 (...) sampl_samples_number int(11)  DEFAULT 1 NOT NULL (...)
6

thx to both of you for your answers.

The problem was indeed caused by an added module. It was a first try to add a module made with module builder. As it was the first try we made the ā€œmistakeā€ and put it on top-level and installed it. After we saw our mistake and changed it to a sub-level as we didnt want it to be on top.

So: now it is shown under the module-loader - and somehow i still couldnā€™t figure out how to delete it from there.
But: i tried the delete strategy with a snapshot - and it can be solved by simply deleting the corresponding folder from /modules. After that the module is - as mentioned - still shown in the module loader. But the quick repair doesnā€™t show any error - and the upgrade also worked.

thx for your help - the main problem is solved with that. And i will keep m42s answer in mind when i play around with the module builder again.