How are you meant to do an upgrade with existing quiz data in your db? I get errors when trying to update:

Cannot add unique key result_answer to table quiz_node_results_answers: unique key already exists.                                                                                                                                                                   [error]
Performed update: quiz_update_7501                                                                                                                                                                                                                                   [ok]
SQLSTATE[42000]: Syntax error or access violation: 1171 All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead                                                                                                                   [error]
Performed update: quiz_question_update_7500                                                                                                                                                                                                                          [ok]
Cannot add field quiz_multichoice_user_answers.result_answer_id: field already exists.                                                                                                                                                                               [error]
Performed update: multichoice_update_7502                                                                                                                                                                                                                            [ok]
SQLSTATE[42000]: Syntax error or access violation: 1171 All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead

It's pretty clear the addition of new pks etc.. breaks existing data - is there something I'm missing to do the upgrade?

Comments

benstjohn created an issue. See original summary.

benstjohn’s picture

benstjohn commented
Issue summary: View changes
benstjohn’s picture

benstjohn commented
Issue summary: View changes
djdevin’s picture

djdevin
he/him
Primary language English
Location Philadelphia
commented

`drush updb` should do it, but it looks like it already failed. Is this the second time you ran drush updb? It's saying the keys in 5.x are already there.

benstjohn’s picture

benstjohn commented

Thanks for the response, the previous errors may have been after attempting a couple things. Here is a what I get after running updb on a fresh db with existing quiz data:

Do you wish to run all pending updates? (y/n): y
4 byte UTF-8 for mysql is not activated, but it is supported on your system. It is recommended that you enable this to allow 4-byte UTF-8 input such as emojis, Asian symbols and mathematical symbols to be stored correctly. See the documentation on adding 4 byte[warning]
UTF-8 support for more information. (Currently using Database 4 byte UTF-8 support Not enabled)
Added new layout field to the quiz_node_results table                                                                                                                                                                                                                [ok]
Performed update: quiz_update_7500                                                                                                                                                                                                                                   [ok]
SQLSTATE[42000]: Syntax error or access violation: 1171 All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead                                                                                                                   [error]
Performed update: quiz_update_7501                                                                                                                                                                                                                                   [ok]
SQLSTATE[42000]: Syntax error or access violation: 1171 All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead                                                                                                                   [error]
Performed update: quiz_question_update_7500                                                                                                                                                                                                                          [ok]
Performed update: multichoice_update_7500                                                                                                                                                                                                                            [ok]
Added weights for multichoice alternatives.                                                                                                                                                                                                                          [ok]
Performed update: multichoice_update_7501                                                                                                                                                                                                                            [ok]
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'qnra.result_answer_id' in 'field list'                                                                                                                                                                       [error]
Performed update: multichoice_update_7502                                                                                                                                                                                                                            [ok]
SQLSTATE[42000]: Syntax error or access violation: 1171 All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead                                                                                                                   [error]
Performed update: truefalse_update_7500                                                                                                                                                                                                                              [ok]
exception 'PDOException' with message 'SQLSTATE[42S02]: Base table or view not found: 1146 Table 'ibi.quiz_result_type' doesn't exist' in /Users/benstjohn/Sites/test-site/includes/database/database.inc:2204                                                  [error]

The main error that is giving me grief is Primary Key errors. I'm not entirely sure I understand what they're saying? Is it saying existing data is resulting in NULL primary keys? Also I'm finding it isn't creating the quiz_result_type table. All I've done is replace my existing quiz module with this version and tried to run updb is there steps I'm missing?

benstjohn’s picture

benstjohn commented

A re-install works, naturally, but it blows all my data away.

djdevin’s picture

djdevin
he/him
Primary language English
Location Philadelphia
commented

Hi,

Thanks for the info. Running drush updb is exactly what you are supposed to do and 4.x to 5.x is supported. But somehow it looks like your 4.x install has a table format that the 5.x upgrade is not expecting.

Can you provide me the output of "SHOW CREATE TABLE quiz_node_results_answers" on your old 4.x install?

benstjohn’s picture

benstjohn commented

CREATE TABLE `quiz_node_results_answers` (
`result_id` int(10) unsigned NOT NULL,
`question_nid` int(10) unsigned NOT NULL,
`question_vid` int(10) unsigned NOT NULL,
`tid` int(10) unsigned DEFAULT NULL,
`is_correct` tinyint(3) unsigned NOT NULL DEFAULT '0',
`is_skipped` tinyint(3) unsigned NOT NULL DEFAULT '0',
`points_awarded` tinyint(4) NOT NULL DEFAULT '0',
`answer_timestamp` int(10) unsigned NOT NULL,
`number` smallint(6) NOT NULL DEFAULT '1',
`is_doubtful` tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`result_id`,`question_nid`,`question_vid`),
KEY `result_id` (`result_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Table storing information about the results for the...';

benstjohn’s picture

benstjohn commented

I did see the UTF8 4 byte warning but didn't really think that was my issue ..

djdevin’s picture

djdevin
he/him
Primary language English
Location Philadelphia
commented

Are you running MySQL 5.7 by any chance?

  • djdevin committed eefc5ae on 7.x-5.x 
    Issue #2812685: add missing "not null" to fix half of a mysql 5.7 issue
djdevin’s picture

djdevin
he/him
Primary language English
Location Philadelphia
commented
Version: 7.x-4.0-rc2 » 7.x-5.x-dev
Category: Task » Bug report
Related issues: +#2615496: A serial/primary key field can not be added to an existing table for some databases, +#2665362: D7 upgrades fail with mysql 5.7:

I confirmed this is an issue with MySQL 5.7. Unfortunately it's an issue with Drupal 7 core that affects core and other modules so you'll have to patch core to get it to upgrade correctly or downgrade to MySQL 5.6.

Here are the core issues that describe it:

#2665362: D7 upgrades fail with mysql 5.7:
and
#2615496: A serial/primary key field can not be added to an existing table for some databases

I made a change to Quiz core to fix #2665362: D7 upgrades fail with mysql 5.7: . But you'll need to patch core to fix #2615496: A serial/primary key field can not be added to an existing table for some databases which we can't fix in Quiz.

I was able to upgrade Quiz doing the following:

1) Get the latest Quiz 7.x-5.x
2) Patch core with #2615496: A serial/primary key field can not be added to an existing table for some databases
3) drush updb

After Quiz successfully upgrades you could unpatch core.

benstjohn’s picture

benstjohn commented

Thanks so much I'll give that a go.

djdevin’s picture

djdevin
he/him
Primary language English
Location Philadelphia
commented
Status: Active » Fixed

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.

ron collins’s picture

ron collins commented

This specific patch worked for me
https://www.drupal.org/files/issues/d7-2615496-mysql_schema_add_field_se...

I had to first roll the db back to before the attempt to run drush updb, of course.