Hi,
While testing the upgrade to 4.10.0 I ran to a DB upgrade issue, logs
below...
Basically,
the storeproc fail to be execute event manually with following error:
mysql> CALL
`cloud`.`IDEMPOTENT_INSERT_GUESTOS_HYPERVISOR_MAPPING`('Xenserver',
'7.0.0', 'CentOS 4.5 (32-bit)', 1, 0);
ERROR 1267 (HY000): Illegal mix of collations (utf8_general_ci,IMPLICIT)
and (utf8_unicode_ci,IMPLICIT) for operation '='
but If I ran this command:
mysql> ALTER TABLE guest_os_hypervisor CONVERT TO CHARACTER SET utf8
COLLATE utf8_unicode_ci;
Query OK, 2378 rows affected (0.17 sec)
Then the storeproc succeed and the database upgrade up to 4.10 will succeed.
So does anyone got that behavior too ? if so I think it would worth adding
this to our release notes?
Thanks,
management-server.log:
2017-07-05 12:59:32,687 DEBUG [c.c.u.s.Script] (localhost-startStop-1:null)
(logid:) Absolute path =
/usr/share/cloudstack-management/setup/db/schema-4910to4920.sql
2017-07-05 12:59:32,687 DEBUG [c.c.u.d.ScriptRunner]
(localhost-startStop-1:null) (logid:) -- Licensed to the Apache Software
Foundation (ASF) under one
2017-07-05 12:59:32,687 DEBUG [c.c.u.d.ScriptRunner]
(localhost-startStop-1:null) (logid:) -- or more contributor license
agreements. See the NOTICE file
2017-07-05 12:59:32,687 DEBUG [c.c.u.d.ScriptRunner]
(localhost-startStop-1:null) (logid:) -- distributed with this work for
additional information
2017-07-05 12:59:32,687 DEBUG [c.c.u.d.ScriptRunner]
(localhost-startStop-1:null) (logid:) -- regarding copyright ownership.
The ASF licenses this file
2017-07-05 12:59:32,687 DEBUG [c.c.u.d.ScriptRunner]
(localhost-startStop-1:null) (logid:) -- to you under the Apache License,
Version 2.0 (the
2017-07-05 12:59:32,687 DEBUG [c.c.u.d.ScriptRunner]
(localhost-startStop-1:null) (logid:) -- "License"); you may not use this
file except in compliance
2017-07-05 12:59:32,687 DEBUG [c.c.u.d.ScriptRunner]
(localhost-startStop-1:null) (logid:) -- with the License. You may obtain
a copy of the License at
2017-07-05 12:59:32,687 DEBUG [c.c.u.d.ScriptRunner]
(localhost-startStop-1:null) (logid:) --
2017-07-05 12:59:32,687 DEBUG [c.c.u.d.ScriptRunner]
(localhost-startStop-1:null) (logid:) --
http://www.apache.org/licenses/LICENSE-2.0
2017-07-05 12:59:32,687 DEBUG [c.c.u.d.ScriptRunner]
(localhost-startStop-1:null) (logid:) --
2017-07-05 12:59:32,687 DEBUG [c.c.u.d.ScriptRunner]
(localhost-startStop-1:null) (logid:) -- Unless required by applicable law
or agreed to in writing,
2017-07-05 12:59:32,687 DEBUG [c.c.u.d.ScriptRunner]
(localhost-startStop-1:null) (logid:) -- software distributed under the
License is distributed on an
2017-07-05 12:59:32,687 DEBUG [c.c.u.d.ScriptRunner]
(localhost-startStop-1:null) (logid:) -- "AS IS" BASIS, WITHOUT WARRANTIES
OR CONDITIONS OF ANY
2017-07-05 12:59:32,687 DEBUG [c.c.u.d.ScriptRunner]
(localhost-startStop-1:null) (logid:) -- KIND, either express or implied.
See the License for the
2017-07-05 12:59:32,687 DEBUG [c.c.u.d.ScriptRunner]
(localhost-startStop-1:null) (logid:) -- specific language governing
permissions and limitations
2017-07-05 12:59:32,687 DEBUG [c.c.u.d.ScriptRunner]
(localhost-startStop-1:null) (logid:) -- under the License.
2017-07-05 12:59:32,687 DEBUG [c.c.u.d.ScriptRunner]
(localhost-startStop-1:null) (logid:) --;
2017-07-05 12:59:32,687 DEBUG [c.c.u.d.ScriptRunner]
(localhost-startStop-1:null) (logid:) -- Schema upgrade from 4.9.1.0 to
4.9.2.0;
2017-07-05 12:59:32,687 DEBUG [c.c.u.d.ScriptRunner]
(localhost-startStop-1:null) (logid:) --;
2017-07-05 12:59:32,687 DEBUG [c.c.u.d.ScriptRunner]
(localhost-startStop-1:null) (logid:) --;
2017-07-05 12:59:32,687 DEBUG [c.c.u.d.ScriptRunner]
(localhost-startStop-1:null) (logid:) -- Stored procedure to do idempotent
insert;
2017-07-05 12:59:32,687 DEBUG [c.c.u.d.ScriptRunner]
(localhost-startStop-1:null) (logid:) --;
2017-07-05 12:59:32,687 DEBUG [c.c.u.d.ScriptRunner]
(localhost-startStop-1:null) (logid:) DROP PROCEDURE IF EXISTS
`cloud`.`IDEMPOTENT_INSERT_GUESTOS_HYPERVISOR_MAPPING`
2017-07-05 12:59:32,691 DEBUG [c.c.u.d.ScriptRunner]
(localhost-startStop-1:null) (logid:) CREATE PROCEDURE
`cloud`.`IDEMPOTENT_INSERT_GUESTOS_HYPERVISOR_MAPPING`(
IN in_hypervisor_type VARCHAR(32), IN
in_hypervisor_version VARCHAR(32), IN
in_guest_os_name VARCHAR(255), IN
in_guest_os_id BIGINT(20) UNSIGNED, IN
is_user_defined int(1) UNSIGNED) BEGIN IF NOT EXISTS ((SELECT *
FROM `cloud`.`guest_os_hypervisor` WHERE
hypervisor_type=in_hypervisor_type AND
hypervisor_version=in_hypervisor_version AND guest_os_id =
in_guest_os_id)) THEN INSERT INTO
`cloud`.`guest_os_hypervisor` ( uuid,
hypervisor_type, hypervisor_version,
guest_os_name, guest_os_id,
created, is_user_defined)
VALUES ( UUID(),
in_hypervisor_type,
in_hypervisor_version, in_guest_os_name,
in_guest_os_id,
utc_timestamp(), is_user_defined
); END IF; END;
2017-07-05 12:59:32,696 DEBUG [c.c.u.d.ScriptRunner]
(localhost-startStop-1:null) (logid:) CALL
`cloud`.`IDEMPOTENT_INSERT_GUESTOS_HYPERVISOR_MAPPING`('Xenserver',
'7.0.0', 'CentOS 4.5 (32-bit)', 1, 0)
2017-07-05 12:59:32,697 ERROR [c.c.u.d.ScriptRunner]
(localhost-startStop-1:null) (logid:) Error executing: CALL
`cloud`.`IDEMPOTENT_INSERT_GUESTOS_HYPERVISOR_MAPPING`('Xenserver',
'7.0.0', 'CentOS 4.5 (32-bit)', 1, 0)
2017-07-05 12:59:32,697 ERROR [c.c.u.d.ScriptRunner]
(localhost-startStop-1:null) (logid:) java.sql.SQLException: Illegal mix of
collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for
operation '='
2017-07-05 12:59:32,698 ERROR [c.c.u.DatabaseUpgradeChecker]
(localhost-startStop-1:null) (logid:) Unable to execute upgrade script:
/usr/share/cloudstack-management/setup/db/schema-4910to4920.sql
java.sql.SQLException: Illegal mix of collations (utf8_general_ci,IMPLICIT)
and (utf8_unicode_ci,IMPLICIT) for operation '='
at com.cloud.utils.db.ScriptRunner.runScript(ScriptRunner.java:185)
at com.cloud.utils.db.ScriptRunner.runScript(ScriptRunner.java:87)
at
com.cloud.upgrade.DatabaseUpgradeChecker.runScript(DatabaseUpgradeChecker.java:322)
at
com.cloud.upgrade.DatabaseUpgradeChecker.upgrade(DatabaseUpgradeChecker.java:425)
at
com.cloud.upgrade.DatabaseUpgradeChecker.check(DatabaseUpgradeChecker.java:509)
at
org.apache.cloudstack.spring.lifecycle.CloudStackExtendedLifeCycle.checkIntegrity(CloudStackExtendedLifeCycle.java:65)
at
org.apache.cloudstack.spring.lifecycle.CloudStackExtendedLifeCycle.start(CloudStackExtendedLifeCycle.java:55)
at
org.springframework.context.support.DefaultLifecycleProcessor.doStart(DefaultLifecycleProcessor.java:173)
at
org.springframework.context.support.DefaultLifecycleProcessor.access$200(DefaultLifecycleProcessor.java:51)
at
org.springframework.context.support.DefaultLifecycleProcessor$LifecycleGroup.start(DefaultLifecycleProcessor.java:346)
at
org.springframework.context.support.DefaultLifecycleProcessor.startBeans(DefaultLifecycleProcessor.java:149)
at
org.springframework.context.support.DefaultLifecycleProcessor.onRefresh(DefaultLifecycleProcessor.java:112)
at
org.springframework.context.support.AbstractApplicationContext.finishRefresh(AbstractApplicationContext.java:879)
at
org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:545)
at
org.apache.cloudstack.spring.module.model.impl.DefaultModuleDefinitionSet.loadContext(DefaultModuleDefinitionSet.java:145)
at
org.apache.cloudstack.spring.module.model.impl.DefaultModuleDefinitionSet$2.with(DefaultModuleDefinitionSet.java:122)
at
org.apache.cloudstack.spring.module.model.impl.DefaultModuleDefinitionSet.withModule(DefaultModuleDefinitionSet.java:245)
at
org.apache.cloudstack.spring.module.model.impl.DefaultModuleDefinitionSet.withModule(DefaultModuleDefinitionSet.java:250)
at
org.apache.cloudstack.spring.module.model.impl.DefaultModuleDefinitionSet.withModule(DefaultModuleDefinitionSet.java:233)
at
org.apache.cloudstack.spring.module.model.impl.DefaultModuleDefinitionSet.loadContexts(DefaultModuleDefinitionSet.java:117)
at
org.apache.cloudstack.spring.module.model.impl.DefaultModuleDefinitionSet.load(DefaultModuleDefinitionSet.java:79)
at
org.apache.cloudstack.spring.module.factory.ModuleBasedContextFactory.loadModules(ModuleBasedContextFactory.java:37)
at
org.apache.cloudstack.spring.module.factory.CloudStackSpringContext.init(CloudStackSpringContext.java:71)
at
org.apache.cloudstack.spring.module.factory.CloudStackSpringContext.<init>(CloudStackSpringContext.java:58)
at
org.apache.cloudstack.spring.module.factory.CloudStackSpringContext.<init>(CloudStackSpringContext.java:62)
at
org.apache.cloudstack.spring.module.web.CloudStackContextLoaderListener.contextInitialized(CloudStackContextLoaderListener.java:52)
at
org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:5068)
at
org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5584)
at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:147)
at
org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:899)
at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:875)
at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:652)
at
org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java:1260)
at
org.apache.catalina.startup.HostConfig$DeployDirectory.run(HostConfig.java:2002)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:748)
2017-07-05 12:59:32,700 ERROR [c.c.u.DatabaseUpgradeChecker]
(localhost-startStop-1:null) (logid:) Unable to upgrade the database
com.cloud.utils.exception.CloudRuntimeException: Unable to execute upgrade
script: /usr/share/cloudstack-management/setup/db/schema-4910to4920.sql
at
com.cloud.upgrade.DatabaseUpgradeChecker.runScript(DatabaseUpgradeChecker.java:331)
at
com.cloud.upgrade.DatabaseUpgradeChecker.upgrade(DatabaseUpgradeChecker.java:425)
at
com.cloud.upgrade.DatabaseUpgradeChecker.check(DatabaseUpgradeChecker.java:509)
at
org.apache.cloudstack.spring.lifecycle.CloudStackExtendedLifeCycle.checkIntegrity(CloudStackExtendedLifeCycle.java:65)
at
org.apache.cloudstack.spring.lifecycle.CloudStackExtendedLifeCycle.start(CloudStackExtendedLifeCycle.java:55)
at
org.springframework.context.support.DefaultLifecycleProcessor.doStart(DefaultLifecycleProcessor.java:173)
at
org.springframework.context.support.DefaultLifecycleProcessor.access$200(DefaultLifecycleProcessor.java:51)
at
org.springframework.context.support.DefaultLifecycleProcessor$LifecycleGroup.start(DefaultLifecycleProcessor.java:346)
at
org.springframework.context.support.DefaultLifecycleProcessor.startBeans(DefaultLifecycleProcessor.java:149)
at
org.springframework.context.support.DefaultLifecycleProcessor.onRefresh(DefaultLifecycleProcessor.java:112)
at
org.springframework.context.support.AbstractApplicationContext.finishRefresh(AbstractApplicationContext.java:879)
at
org.springframework.context.support.AbstractApplicationContext.refresh(AbstractApplicationContext.java:545)
at
org.apache.cloudstack.spring.module.model.impl.DefaultModuleDefinitionSet.loadContext(DefaultModuleDefinitionSet.java:145)
at
org.apache.cloudstack.spring.module.model.impl.DefaultModuleDefinitionSet$2.with(DefaultModuleDefinitionSet.java:122)
at
org.apache.cloudstack.spring.module.model.impl.DefaultModuleDefinitionSet.withModule(DefaultModuleDefinitionSet.java:245)
at
org.apache.cloudstack.spring.module.model.impl.DefaultModuleDefinitionSet.withModule(DefaultModuleDefinitionSet.java:250)
at
org.apache.cloudstack.spring.module.model.impl.DefaultModuleDefinitionSet.withModule(DefaultModuleDefinitionSet.java:233)
at
org.apache.cloudstack.spring.module.model.impl.DefaultModuleDefinitionSet.loadContexts(DefaultModuleDefinitionSet.java:117)
at
org.apache.cloudstack.spring.module.model.impl.DefaultModuleDefinitionSet.load(DefaultModuleDefinitionSet.java:79)
at
org.apache.cloudstack.spring.module.factory.ModuleBasedContextFactory.loadModules(ModuleBasedContextFactory.java:37)
at
org.apache.cloudstack.spring.module.factory.CloudStackSpringContext.init(CloudStackSpringContext.java:71)
at
org.apache.cloudstack.spring.module.factory.CloudStackSpringContext.<init>(CloudStackSpringContext.java:58)
at
org.apache.cloudstack.spring.module.factory.CloudStackSpringContext.<init>(CloudStackSpringContext.java:62)
at
org.apache.cloudstack.spring.module.web.CloudStackContextLoaderListener.contextInitialized(CloudStackContextLoaderListener.java:52)
at
org.apache.catalina.core.StandardContext.listenerStart(StandardContext.java:5068)
at
org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5584)
at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:147)
at
org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:899)
at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:875)
at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:652)
at
org.apache.catalina.startup.HostConfig.deployDirectory(HostConfig.java:1260)
at
org.apache.catalina.startup.HostConfig$DeployDirectory.run(HostConfig.java:2002)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLException: Illegal mix of collations
(utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '='
at com.cloud.utils.db.ScriptRunner.runScript(ScriptRunner.java:185)
at com.cloud.utils.db.ScriptRunner.runScript(ScriptRunner.java:87)
at
com.cloud.upgrade.DatabaseUpgradeChecker.runScript(DatabaseUpgradeChecker.java:322)
... 36 more
2017-07-05 12:59:32,701 DEBUG [c.c.u.d.T.Transaction]
(localhost-startStop-1:null) (logid:) Rolling back the transaction: Time =
15 Name = Upgrade; called by
-TransactionLegacy.rollback:889-TransactionLegacy.removeUpTo:832-Transaction
Pierre-Luc
