[Koha-bugs] [Bug 17216] Add a new table to store authorized value categories

[bugzilla-daemon at bugs.koha-community.org]

https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=17216

--- Comment #102 from Jacek Ablewicz <abl at biblos.pk.edu.pl> ---
(In reply to Mirko Tietgen from comment #101)

>     [Thu Nov 24 00:06:08 2016] updatedatabase.pl: "] at
> /usr/share/koha/intranet/cgi-bin/installer/data/mysql/updatedatabase.pl line
> 12632.
>     [Thu Nov 24 00:06:15 2016] updatedatabase.pl: DBD::mysql::db do failed:
> Can't create table 'koha_koha.#sql-2221_18d' (errno: 150) [for Statement "
>     [Thu Nov 24 00:06:15 2016] updatedatabase.pl: ALTER TABLE
> marc_subfield_structure
>     [Thu Nov 24 00:06:15 2016] updatedatabase.pl: MODIFY COLUMN
> authorised_value VARCHAR(32) DEFAULT NULL,
>     [Thu Nov 24 00:06:15 2016] updatedatabase.pl: ADD CONSTRAINT
> marc_subfield_structure_ibfk_1 FOREIGN KEY (authorised_value) REFERENCES
> authorised_value_categories (category_name) ON UPDATE CASCADE ON DELETE SET
> NULL;
>     [Thu Nov 24 00:06:15 2016] updatedatabase.pl: "] at
> /usr/share/koha/intranet/cgi-bin/installer/data/mysql/updatedatabase.pl line
> 13175.
> 
> 
> @Jacek: that is what you are talking about, right?

Possibly ;), hard to tell (errno: 150 by itself is not very informative).
What I did while trying to troubleshoot this problem (following the guidelines
from
<http://stackoverflow.com/questions/4061293/mysql-cant-create-table-errno-150>,
especially nr 4 is quite helpful):

- split erroneous statement in two

    1) ALTER TABLE marc_subfield_structure
    MODIFY COLUMN authorised_value VARCHAR(32) DEFAULT NULL;

    2) ALTER TABLE marc_subfield_structure
ADD CONSTRAINT marc_subfield_structure_ibfk_1 FOREIGN KEY (authorised_value)
REFERENCES authorised_value_categories (category_name) ON UPDATE CASCADE ON
DELETE SET NULL;

- part 1) executed OK, but part 2) still resulted in the same error (150)

- noticed that after 1), utf8_unicode_ci collation is no longer there for
authorised_value field (mysqldump --no-data marc_subfield_structure)

- replaced 1) with

    ALTER TABLE marc_subfield_structure
    MODIFY COLUMN authorised_value VARCHAR(32) CHARACTER SET utf8 COLLATE
utf8_unicode_ci DEFAULT NULL;

- re-run 1) and 2) - no error, FK constrain got created successfully and
collation for authorised_value is utf8_unicode_ci.

-- 
You are receiving this mail because:
You are watching all bug changes.