upgrade from 3.0.11 to 3.1.2 error on: scripts/DBUpdate-to-3.1.mysql.sql

newer
Re: [otrs] OTRS on linux vs windows

Luca Domenella

7 Mar 2012 7 Mar '12

11:22 a.m.

Hi, Im testing the upgrade procedure from out ours 2.0.4 to 3.1.2. I passed thought all the steps: 2.0.4 -> 2.1.x 2.1.x -> 2.2.x 2.2.x -> 2.3.x 2.3.x -> 2.4.x 2.4.x –> 3.0.11 And from 3.0.11 to 3.1.2 when i do the following: cat scripts/DBUpdate-to-3.1.mysql.sql | mysql -p -f -u root otrs I get this error: ERROR 1050 (42S01) at line 169: Table 'smime_signer_cert_relations' already exists Our DBA looked at the sql script and also seen the the instructions go ahead but they stopped when they are trying to create a table with foreign keys. The problem looks to be that the engine is MyISAM and that myisam do not support foreign keys. We are using mysql 5.5 (stable) Now the question is: how can i go ahead ? I change the engine from myisam to innodb for this table or did i miss something ? Please let me know. Best regards, Luca Domenella Storage Team Leader P: +39 06 54070440 ext: 16 E: luca.domenella@bwinparty.com bwin Italia Via Adolfo Ravà, 124 00142 Roma (rm) Italy www.bwinparty.com This email and any attachments are confidential, and may be legally privileged and protected by copyright. If you are not the intended recipient dissemination or copying of this email is prohibited. If you have received this in error, please notify the sender by replying by email and then delete the email completely from your system. Any views or opinions are solely those of the sender. This communication is not intended to form a binding contract unless expressly indicated to the contrary and properly authorised. Any actions taken on the basis of this email are at the recipient's own risk.

Attachments:

attachment.html (text/html — 8.5 KB)

Show replies by date

Luca Domenella

7 Mar 7 Mar

11:43 a.m.

Hi all, I also seen that the script create a table gi_webservice_config and try to add a FK. Awfully our DB is MyISAM… So its not supported In the otrs3 db schema for mysql, do i need to change the default DB ENGINE from MyISAM to INNODB ? Please let me know how to fix that Br, Luca Domenella Storage Team Leader P: +39 06 54070440 ext: 16 E: luca.domenella@bwinparty.com bwin Italia Via Adolfo Ravà, 124 00142 Roma (rm) Italy www.bwinparty.com This email and any attachments are confidential, and may be legally privileged and protected by copyright. If you are not the intended recipient dissemination or copying of this email is prohibited. If you have received this in error, please notify the sender by replying by email and then delete the email completely from your system. Any views or opinions are solely those of the sender. This communication is not intended to form a binding contract unless expressly indicated to the contrary and properly authorised. Any actions taken on the basis of this email are at the recipient's own risk. Da: Luca Domenella mailto:Luca.Domenella@bwinparty.com> Risposta: "otrs@otrs.orgmailto:otrs@otrs.org" mailto:otrs@otrs.org> Data: Wed, 7 Mar 2012 11:22:22 +0000 A: "otrs@otrs.orgmailto:otrs@otrs.org" mailto:otrs@otrs.org> Oggetto: [otrs] upgrade from 3.0.11 to 3.1.2 error on: scripts/DBUpdate-to-3.1.mysql.sql Hi, Im testing the upgrade procedure from out ours 2.0.4 to 3.1.2. I passed thought all the steps: 2.0.4 -> 2.1.x 2.1.x -> 2.2.x 2.2.x -> 2.3.x 2.3.x -> 2.4.x 2.4.x –> 3.0.11 And from 3.0.11 to 3.1.2 when i do the following: cat scripts/DBUpdate-to-3.1.mysql.sql | mysql -p -f -u root otrs I get this error: ERROR 1050 (42S01) at line 169: Table 'smime_signer_cert_relations' already exists Our DBA looked at the sql script and also seen the the instructions go ahead but they stopped when they are trying to create a table with foreign keys. The problem looks to be that the engine is MyISAM and that myisam do not support foreign keys. We are using mysql 5.5 (stable) Now the question is: how can i go ahead ? I change the engine from myisam to innodb for this table or did i miss something ? Please let me know. Best regards, Luca Domenella Storage Team Leader P: +39 06 54070440 ext: 16 E: luca.domenella@bwinparty.com bwin Italia Via Adolfo Ravà, 124 00142 Roma (rm) Italy www.bwinparty.com This email and any attachments are confidential, and may be legally privileged and protected by copyright. If you are not the intended recipient dissemination or copying of this email is prohibited. If you have received this in error, please notify the sender by replying by email and then delete the email completely from your system. Any views or opinions are solely those of the sender. This communication is not intended to form a binding contract unless expressly indicated to the contrary and properly authorised. Any actions taken on the basis of this email are at the recipient's own risk. --------------------------------------------------------------------- OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs

Ugo Bellavance

12:45 p.m.

New subject: upgrade from 3.0.11 to 3.1.2 error on: scripts/DBUpdate-to-3.1.mysql.sql

On 2012-03-07 06:43, Luca Domenella wrote:

...

Hi all,

I also seen that the script create a table gi_webservice_config and try to add a FK. Awfully our DB is MyISAM… So its not supported

In the otrs3 db schema for mysql, do i need to change the default DB ENGINE from MyISAM to INNODB ?

So that is why my upgrade to 3.1 didn't work :). I haven't seen anything related to INNODB in the install notes or doc. Even a google search didn't show up much, except for a guy that says that he changed to INNODB for its engine in 2008. Ugo

Luca Domenella

1:33 p.m.

Hi Ugo, Have you done the upgrade in some way bypassing this problem or not ? Br, Luca Domenella Storage Team Leader P: +39 06 54070440 ext: 16 E: luca.domenella@bwinparty.com bwin Italia Via Adolfo Ravà, 124 00142 Roma (rm) Italy www.bwinparty.com This email and any attachments are confidential, and may be legally privileged and protected by copyright. If you are not the intended recipient dissemination or copying of this email is prohibited. If you have received this in error, please notify the sender by replying by email and then delete the email completely from your system. Any views or opinions are solely those of the sender. This communication is not intended to form a binding contract unless expressly indicated to the contrary and properly authorised. Any actions taken on the basis of this email are at the recipient's own risk. Il giorno 3/7/12 1:45 PM, "Ugo Bellavance" ha scritto:

...

On 2012-03-07 06:43, Luca Domenella wrote:

...
Hi all,

I also seen that the script create a table gi_webservice_config and try to add a FK. Awfully our DB is MyISAMŠ So its not supported

In the otrs3 db schema for mysql, do i need to change the default DB ENGINE from MyISAM to INNODB ?

So that is why my upgrade to 3.1 didn't work :).

I haven't seen anything related to INNODB in the install notes or doc. Even a google search didn't show up much, except for a guy that says that he changed to INNODB for its engine in 2008.

Ugo

--------------------------------------------------------------------- OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs

Steven Carr

2:11 p.m.

New subject: upgrade from 3.0.11 to 3.1.2 error on: scripts/DBUpdate-to-3.1.mysql.sql

...

Hi Ugo,

Have you done the upgrade in some way bypassing this problem or not ?

Br,

Luca Domenella Storage Team Leader

Michiel Beijen

5:21 p.m.

New subject: upgrade from 3.0.11 to 3.1.2 error on: scripts/DBUpdate-to-3.1.mysql.sql

Hi Steven, We do not use a specific engine. This means that we'll use whatever engine your MySQL uses as default. For instance on Windows the default engine is InnoDB, and I guess more and more InnoDB is becoming the default storage engine for MySQL. So I guess you simply set up MySQL with MyISAM as a storage engine, then installed OTRS, then the MySQL default storage engine switched to InnoDB beause of an OS or MySQL upgrade, then you upgraded OTRS and now you'll have tables with different storage engines in one database. So I'll understand your issue but you should probably complain with your OS vendor, and not with OTRS. As for the foreign keys: MyISAM does not USE foreign keys but you can specify one. It will just not be enforced. This to support people like us who have ONE create table statement for MySQL regardless of the storage engine. Ref: http://dev.mysql.com/doc/refman/5.1/en/ansi-diff-foreign-keys.html The error message you have is that the table "smime_signer_cert_relations" is already there, and the upgrade script tries to create it a second time and then fails. This table is added in OTRS 3.1, and you should really only have that after you ran the update script "DBUpdate-to-3.1.mysql.sql" -- my idea is you simply tried to run this script two times. If you can reproduce this error, obviously I would be interested to know how you got this. - Mike On Wed, Mar 7, 2012 at 15:11, Steven Carr wrote:

...

My OTRS install (3.0.11) seems to have a mix of MyISAM/InnoDB - would be nice if developers stuck to one engine, and if they are going to change to use relationship features in InnoDB, then change all tables.

Steve

+------------------------------+--------+-------+-----------+-----------------+ | Table Name | Engine | Rows | Size | Collation | +------------------------------+--------+-------+-----------+-----------------+ | article | MyISAM | 1593 | 129.74 Mb | utf8_general_ci | | article_attachment | MyISAM | 0 | 0.00 Mb | utf8_general_ci | | article_flag | MyISAM | 1467 | 0.13 Mb | utf8_general_ci | | article_plain | MyISAM | 0 | 0.00 Mb | utf8_general_ci | | article_search | MyISAM | 0 | 0.00 Mb | utf8_general_ci | | article_sender_type | MyISAM | 3 | 0.01 Mb | utf8_general_ci | | article_type | MyISAM | 11 | 0.01 Mb | utf8_general_ci | | auto_response | MyISAM | 4 | 0.02 Mb | utf8_general_ci | | auto_response_type | MyISAM | 5 | 0.01 Mb | utf8_general_ci | | customer_company | MyISAM | 3 | 0.01 Mb | utf8_general_ci | | customer_preferences | MyISAM | 30 | 0.01 Mb | utf8_general_ci | | customer_user | MyISAM | 4 | 0.01 Mb | utf8_general_ci | | faq_attachment | MyISAM | 1 | 0.05 Mb | utf8_general_ci | | faq_category | MyISAM | 2 | 0.00 Mb | utf8_general_ci | | faq_category_group | MyISAM | 4 | 0.00 Mb | utf8_general_ci | | faq_history | MyISAM | 19 | 0.00 Mb | utf8_general_ci | | faq_item | MyISAM | 4 | 0.01 Mb | utf8_general_ci | | faq_language | MyISAM | 2 | 0.01 Mb | utf8_general_ci | | faq_log | MyISAM | 94 | 0.02 Mb | utf8_general_ci | | faq_state | MyISAM | 3 | 0.01 Mb | utf8_general_ci | | faq_state_type | MyISAM | 3 | 0.01 Mb | utf8_general_ci | | faq_voting | MyISAM | 0 | 0.00 Mb | utf8_general_ci | | follow_up_possible | MyISAM | 3 | 0.01 Mb | utf8_general_ci | | generic_agent_jobs | MyISAM | 0 | 0.00 Mb | utf8_general_ci | | groups | MyISAM | 9 | 0.01 Mb | utf8_general_ci | | group_customer_user | MyISAM | 0 | 0.00 Mb | utf8_general_ci | | group_role | MyISAM | 18 | 0.01 Mb | utf8_general_ci | | group_user | MyISAM | 1 | 0.00 Mb | utf8_general_ci | | link_object | MyISAM | 2 | 0.00 Mb | utf8_general_ci | | link_relation | MyISAM | 7 | 0.01 Mb | utf8_general_ci | | link_state | MyISAM | 2 | 0.01 Mb | utf8_general_ci | | link_type | MyISAM | 2 | 0.01 Mb | utf8_general_ci | | mail_account | MyISAM | 3 | 0.01 Mb | utf8_general_ci | | notifications | MyISAM | 20 | 0.01 Mb | utf8_general_ci | | notification_event | MyISAM | 2 | 0.01 Mb | utf8_general_ci | | notification_event_item | MyISAM | 12 | 0.01 Mb | utf8_general_ci | | package_repository | MyISAM | 5 | 8.44 Mb | utf8_general_ci | | personal_queues | MyISAM | 4 | 0.00 Mb | utf8_general_ci | | postmaster_filter | MyISAM | 14 | 0.01 Mb | utf8_general_ci | | process_id | MyISAM | 0 | 0.00 Mb | utf8_general_ci | | queue | MyISAM | 8 | 0.02 Mb | utf8_general_ci | | queue_auto_response | MyISAM | 4 | 0.01 Mb | utf8_general_ci | | queue_preferences | MyISAM | 0 | 0.00 Mb | utf8_general_ci | | queue_standard_response | MyISAM | 5 | 0.01 Mb | utf8_general_ci | | roles | MyISAM | 6 | 0.01 Mb | utf8_general_ci | | role_user | MyISAM | 6 | 0.01 Mb | utf8_general_ci | | salutation | MyISAM | 1 | 0.01 Mb | utf8_general_ci | | search_profile | MyISAM | 26 | 0.01 Mb | utf8_general_ci | | service | MyISAM | 0 | 0.00 Mb | utf8_general_ci | | service_customer_user | MyISAM | 0 | 0.00 Mb | utf8_general_ci | | service_preferences | MyISAM | 0 | 0.00 Mb | utf8_general_ci | | service_sla | MyISAM | 0 | 0.00 Mb | utf8_general_ci | | sessions | MyISAM | 0 | 0.01 Mb | utf8_general_ci | | signature | MyISAM | 1 | 0.01 Mb | utf8_general_ci | | sla | MyISAM | 0 | 0.00 Mb | utf8_general_ci | | sla_preferences | MyISAM | 0 | 0.00 Mb | utf8_general_ci | | standard_attachment | MyISAM | 0 | 0.00 Mb | utf8_general_ci | | standard_response | MyISAM | 1 | 0.01 Mb | utf8_general_ci | | standard_response_attachment | MyISAM | 0 | 0.00 Mb | utf8_general_ci | | support_bench_test | MyISAM | 30000 | 0.63 Mb | utf8_general_ci | | survey | MyISAM | 0 | 0.00 Mb | utf8_general_ci | | survey_answer | MyISAM | 0 | 0.00 Mb | utf8_general_ci | | survey_question | MyISAM | 0 | 0.00 Mb | utf8_general_ci | | survey_queue | MyISAM | 0 | 0.00 Mb | utf8_general_ci | | survey_request | MyISAM | 0 | 0.00 Mb | utf8_general_ci | | survey_vote | MyISAM | 0 | 0.00 Mb | utf8_general_ci | | system_address | MyISAM | 3 | 0.01 Mb | utf8_general_ci | | ticket | MyISAM | 1437 | 0.76 Mb | utf8_general_ci | | ticket_flag | InnoDB | 1330 | 0.28 Mb | utf8_general_ci | | ticket_history | MyISAM | 8191 | 1.89 Mb | utf8_general_ci | | ticket_history_type | MyISAM | 40 | 0.01 Mb | utf8_general_ci | | ticket_index | MyISAM | 0 | 0.00 Mb | utf8_general_ci | | ticket_lock_index | MyISAM | 0 | 0.00 Mb | utf8_general_ci | | ticket_lock_type | MyISAM | 3 | 0.01 Mb | utf8_general_ci | | ticket_loop_protection | MyISAM | 1 | 0.01 Mb | utf8_general_ci | | ticket_priority | MyISAM | 5 | 0.01 Mb | utf8_general_ci | | ticket_state | MyISAM | 9 | 0.01 Mb | utf8_general_ci | | ticket_state_type | MyISAM | 7 | 0.01 Mb | utf8_general_ci | | ticket_type | MyISAM | 1 | 0.01 Mb | utf8_general_ci | | ticket_watcher | MyISAM | 0 | 0.00 Mb | utf8_general_ci | | time_accounting | MyISAM | 0 | 0.00 Mb | utf8_general_ci | | users | MyISAM | 2 | 0.01 Mb | utf8_general_ci | | user_preferences | MyISAM | 80 | 0.01 Mb | utf8_general_ci | | valid | MyISAM | 3 | 0.01 Mb | utf8_general_ci | | virtual_fs | InnoDB | 0 | 0.05 Mb | utf8_general_ci | | virtual_fs_db | InnoDB | 0 | 0.03 Mb | utf8_general_ci | | virtual_fs_preferences | InnoDB | 0 | 0.05 Mb | utf8_general_ci | | web_upload_cache | MyISAM | 0 | 0.00 Mb | utf8_general_ci | | xml_storage | MyISAM | 772 | 0.08 Mb | utf8_general_ci | +------------------------------+--------+-------+-----------+-----------------+

On 7 March 2012 13:33, Luca Domenella wrote:

...
Hi Ugo,

Have you done the upgrade in some way bypassing this problem or not ?

Br,

Luca Domenella Storage Team Leader

--------------------------------------------------------------------- OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs

Ugo Bellavance

8 Mar 8 Mar

2:04 a.m.

New subject: upgrade from 3.0.11 to 3.1.2 error on: scripts/DBUpdate-to-3.1.mysql.sql

On 2012-03-07 12:21, Michiel Beijen wrote:

...

Hi Steven,

We do not use a specific engine. This means that we'll use whatever engine your MySQL uses as default. For instance on Windows the default engine is InnoDB, and I guess more and more InnoDB is becoming the default storage engine for MySQL.

So I guess you simply set up MySQL with MyISAM as a storage engine, then installed OTRS, then the MySQL default storage engine switched to InnoDB beause of an OS or MySQL upgrade, then you upgraded OTRS and now you'll have tables with different storage engines in one database.

So I'll understand your issue but you should probably complain with your OS vendor, and not with OTRS.

As for the foreign keys: MyISAM does not USE foreign keys but you can specify one. It will just not be enforced. This to support people like us who have ONE create table statement for MySQL regardless of the storage engine. Ref: http://dev.mysql.com/doc/refman/5.1/en/ansi-diff-foreign-keys.html

The error message you have is that the table "smime_signer_cert_relations" is already there, and the upgrade script tries to create it a second time and then fails. This table is added in OTRS 3.1, and you should really only have that after you ran the update script "DBUpdate-to-3.1.mysql.sql" -- my idea is you simply tried to run this script two times.

If you can reproduce this error, obviously I would be interested to know how you got this.

I'll definitely try to reproduce the issue I got. Thanks, Ugo

Michiel Beijen

6:35 a.m.

New subject: upgrade from 3.0.11 to 3.1.2 error on: scripts/DBUpdate-to-3.1.mysql.sql

Nils told me how to reproduce: The problem was, one of our devs added the smime_signer_cert tables to 2.4 after we already released 2.4.11, then rolled it back because it should not be added on a patch level release. He failed to roll back the database upgrade files however.. We created 2.4.12 which contained these new database files, and now there is an issue if you do the upgrade from 2.3 > 2.4 > 3.0 > 3.1. I rolled back his changes. If you'll use the database upgrade files from http://source.otrs.org/viewvc.cgi/otrs/scripts/?hideattic=1&sortby=date&path... you would be good. I vcreated a bug report for it: http://bugs.otrs.org/show_bug.cgi?id=8300 - Database upgrade issue when upgrading from 2.3 > 2.4 > 3.0 > 3.1: table smime_signer_cert_relations' already exists. thanks! -- Mike On Thu, Mar 8, 2012 at 03:04, Ugo Bellavance wrote:

...

On 2012-03-07 12:21, Michiel Beijen wrote:

...
Hi Steven,

We do not use a specific engine. This means that we'll use whatever engine your MySQL uses as default. For instance on Windows the default engine is InnoDB, and I guess more and more InnoDB is becoming the default storage engine for MySQL.

So I guess you simply set up MySQL with MyISAM as a storage engine, then installed OTRS, then the MySQL default storage engine switched to InnoDB beause of an OS or MySQL upgrade, then you upgraded OTRS and now you'll have tables with different storage engines in one database.

So I'll understand your issue but you should probably complain with your OS vendor, and not with OTRS.

As for the foreign keys: MyISAM does not USE foreign keys but you can specify one. It will just not be enforced. This to support people like us who have ONE create table statement for MySQL regardless of the storage engine. Ref: http://dev.mysql.com/doc/refman/5.1/en/ansi-diff-foreign-keys.html

The error message you have is that the table "smime_signer_cert_relations" is already there, and the upgrade script tries to create it a second time and then fails. This table is added in OTRS 3.1, and you should really only have that after you ran the update script "DBUpdate-to-3.1.mysql.sql" -- my idea is you simply tried to run this script two times.

If you can reproduce this error, obviously I would be interested to know how you got this.

I'll definitely try to reproduce the issue I got.

Thanks,

Ugo

--------------------------------------------------------------------- OTRS mailing list: otrs - Webpage: http://otrs.org/ Archive: http://lists.otrs.org/pipermail/otrs To unsubscribe: http://lists.otrs.org/cgi-bin/listinfo/otrs

4880

Age (days ago)

4881

Last active (days ago)

List overview

Download

7 comments

5 participants

participants (5)

Luca Domenella
Michiel Beijen
Michiel Beijen
Steven Carr
Ugo Bellavance