Hi
Doing some pre upgrade checks on a non production environment, and I came aware
off these new default settings in mysql 8.
I guess Cloudstack still use mysql_native_password?
#Caching_sha2_password as the default
Please make sure that you double-check if your applications and proxies will
work properly with caching_sha2_password authentication plugin as it becomes
the default one in MySQL 8.0. Older applications might be impacted and not able
to connect to the database. Of course, you can change this to whatever
authentication plugin you want (like mysql_native_password for example, as it
was the default in previous MySQL versions) so it is not a blocker by any
means. It’s just something to remember to test before the upgrade so you won’t
end up with MySQL 8.0 and apps that cannot connect to it unless you reconfigure
your database to use an older authentication mechanism.
#UTF8mb4 as the default charset
This shouldn’t come as a surprise given how widely changed to UTF8 was
discussed in the community, but that’s the fact – MySQL 8.0 comes with UTF8mb4
charset as the default one
Here are some output from mysqlsh util.checkForServerUpgrade()
4) Usage of utf8mb3 charset
Warning: The following objects use the utf8mb3 character set. It is
recommended to convert them to use utf8mb4 instead, for improved Unicode
support.
More information:
https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-utf8mb3.html
cloud.account.account_name - column's default character set: utf8
cloud.account.uuid - column's default character set: utf8
cloud.account.state - column's default character set: utf8
cloud.account.network_domain - column's default character set: utf8
cloud.account_details.name - column's default character set: utf8
cloud.account_details.value - column's default character set: utf8
cloud.account_view.uuid - column's default character set: utf8
cloud.account_view.account_name - column's default character set: utf8
............................
.............................
all...
9) Usage of obsolete sql_mode flags
Notice: The following DB objects have obsolete options persisted for
sql_mode, which will be cleared during upgrade to 8.0.
More information:
https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html#mysql-nutshell-removals
cloud.ADD_GUEST_OS_AND_HYPERVISOR_MAPPING - PROCEDURE uses obsolete
NO_AUTO_CREATE_USER sql_mode
cloud.IDEMPOTENT_ADD_COLUMN - PROCEDURE uses obsolete NO_AUTO_CREATE_USER
sql_mode
cloud_usage.IDEMPOTENT_ADD_COLUMN - PROCEDURE uses obsolete
NO_AUTO_CREATE_USER sql_mode
cloud_usage.IDEMPOTENT_ADD_UNIQUE_INDEX - PROCEDURE uses obsolete
NO_AUTO_CREATE_USER sql_mode
cloud_usage.IDEMPOTENT_CHANGE_COLUMN - PROCEDURE uses obsolete
NO_AUTO_CREATE_USER sql_mode
cloud_usage.IDEMPOTENT_DROP_INDEX - PROCEDURE uses obsolete
NO_AUTO_CREATE_USER sql_mode
global system variable sql_mode - defined using obsolete NO_AUTO_CREATE_USER
option
22) New default authentication plugin considerations
Warning: The new default authentication plugin 'caching_sha2_password' offers
more secure password hashing than previously used 'mysql_native_password'
(and consequent improved client connection authentication). However, it also
has compatibility implications that may affect existing MySQL
installations.
If your MySQL installation must serve pre-8.0 clients and you encounter
compatibility issues after upgrading, the simplest way to address those
issues is to reconfigure the server to revert to the previous default
authentication plugin (mysql_native_password). For example, use these lines
in the server option file:
[mysqld]
default_authentication_plugin=mysql_native_password
However, the setting should be viewed as temporary, not as a long term or
permanent solution, because it causes new accounts created with the setting
in effect to forego the improved authentication security.
If you are using replication please take time to understand how the
authentication plugin changes may impact you.
Errors: 0
Warnings: 1503
Notices: 7
NOTE: No fatal errors were found that would prevent an upgrade, but some
potential issues were detected. Please ensure that the reported issues are not
significant before upgrading.
MySQL localhost:3306 ssl JS >
-David
-----Opprinnelig melding-----
Fra: Wei ZHOU <ustcweiz...@gmail.com>
Sendt: mandag 27. mars 2023 10:35
Til: users@cloudstack.apache.org
Emne: Re: SQL error and "can't upgrade database" when I try to upgrade CS
4.17.1.0 to 4.18.0.0
Hi David,
There is no special setting for mysql upgrade.
You'd better back up the database and then upgrade mysql. It would be good to
test the upgrade on a test server before changes on production.
-Wei
On Mon, 27 Mar 2023 at 09:58, David Larsen <david.lar...@adcom.no> wrote:
> Hi Wei
>
> Thanks for the link. I will keep an eye on it.
>
> Strange problem... I'm still stuck with mysql 5.7.41 and CS 4.17.2
> since the mysql 8 upgrade was not successful. I had to revert...
> I didn't have so much time on friday to investigate further, so I will
> look more into it today.
>
> By the way... Any obvious traps to fell into regarding Cloudstack and
> upgrade to Mysql 8?
> Any changes in db.properties files or any connectors?
>
> Thanks
>
> -David
>
>
>
> -----Opprinnelig melding-----
> Fra: Wei ZHOU <ustcweiz...@gmail.com>
> Sendt: fredag 24. mars 2023 22:20
> Til: users@cloudstack.apache.org
> Emne: Re: SQL error and "can't upgrade database" when I try to upgrade
> CS
> 4.17.1.0 to 4.18.0.0
>
> Hi,
> Please keep an eye on
>
> https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgith
> ub.com%2Fapache%2Fcloudstack%2Fissues%2F7358&data=05%7C01%7CDavid.Lars
> en%40adcom.no%7C8588bbcc61884159898008db2e9e6f2b%7C1dd023eed2894f20892
> 6463c9b991b5f%7C0%7C0%7C638155030250148968%7CUnknown%7CTWFpbGZsb3d8eyJ
> WIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000
> %7C%7C%7C&sdata=4BycWmSJs1PZWSvuxyno8WlZF0tpYry2lklnUB6csRc%3D&reserve
> d=0
>
> -Wei
>
>
> On Friday, 24 March 2023, David Larsen <david.lar...@adcom.no> wrote:
>
> > Hi
> >
> > I’m running CS 4.17.1.0 on ubuntu 18.04 and MySQL 5.7.41, and tried
> > to upgrade to 4.18.0.0
> >
> > Please see log bellow for details regarding sql error.
> >
> >
> > 2023-03-24 15:07:42,669 DEBUG [c.c.u.d.ScriptRunner] (main:null)
> > (logid:) CALL `cloud`.`IDEMPOTENT_ADD_COLUMN`('cloud.vm_template',
> > 'user_data_id', 'bigint unsigned DEFAULT NULL COMMENT "id of the us
> > er data"')
> > 2023-03-24 15:07:43,075 DEBUG [c.c.u.d.ScriptRunner] (main:null)
> > (logid:) CALL
> > `cloud`.`IDEMPOTENT_ADD_FOREIGN_KEY`('cloud.vm_template',
> > 'user_data', 'id')
> > 2023-03-24 15:07:43,213 ERROR [c.c.u.d.ScriptRunner] (main:null)
> > (logid:) Error executing: CALL
> > `cloud`.`IDEMPOTENT_ADD_FOREIGN_KEY`('cloud.vm_template',
> > 'user_data', 'id')
> > 2023-03-24 15:07:43,214 ERROR [c.c.u.d.ScriptRunner] (main:null)
> > (logid:)
> > java.sql.SQLIntegrityConstraintViolationException: Can't write;
> > duplicate key in table '#sql-500_158'
> > 2023-03-24 15:07:43,216 ERROR [c.c.u.DatabaseUpgradeChecker]
> > (main:null)
> > (logid:) Unable to execute upgrade script
> > java.sql.SQLIntegrityConstraintViolationException: Can't write;
> > duplicate key in table '#sql-500_158'
> > 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:226)
> > at com.cloud.upgrade.DatabaseUpgradeChecker.upgrade(
> > DatabaseUpgradeChecker.java:310)
> > at com.cloud.upgrade.DatabaseUpgradeChecker.check(
> > DatabaseUpgradeChecker.java:401)
> > at org.apache.cloudstack.spring.lifecycle.
> > CloudStackExtendedLifeCycle.checkIntegrity(CloudStackExtendedLifeCycle.
> > java:64)
> > at org.apache.cloudstack.spring.lifecycle.
> > CloudStackExtendedLifeCycle.start(CloudStackExtendedLifeCycle.java:54)
> > at org.springframework.context.support.DefaultLifecycleProcessor.
> > doStart(DefaultLifecycleProcessor.java:178)
> > at org.springframework.context.support.DefaultLifecycleProcessor.
> > access$200(DefaultLifecycleProcessor.java:54)
> > at
> > org.springframework.context.support.DefaultLifecycleProcessor$
> > LifecycleGroup.start(DefaultLifecycleProcessor.java:356)
> > at java.base/java.lang.Iterable.forEach(Iterable.java:75)
> > at org.springframework.context.support.DefaultLifecycleProcessor.
> > startBeans(DefaultLifecycleProcessor.java:155)
> > at org.springframework.context.support.DefaultLifecycleProcessor.
> > onRefresh(DefaultLifecycleProcessor.java:123)
> > at
> org.springframework.context.support.AbstractApplicationContext.
> > finishRefresh(AbstractApplicationContext.java:935)
> > at
> org.springframework.context.support.AbstractApplicationContext.
> > refresh(AbstractApplicationContext.java:586)
> > at org.apache.cloudstack.spring.module.model.impl.
> > DefaultModuleDefinitionSet.loadContext(DefaultModuleDefinitionSet.
> > java:144)
> > at org.apache.cloudstack.spring.module.model.impl.
> > DefaultModuleDefinitionSet$2.with(DefaultModuleDefinitionSet.java:121)
> > at org.apache.cloudstack.spring.module.model.impl.
> >
> DefaultModuleDefinitionSet.withModule(DefaultModuleDefinitionSet.java:
> 244)
> > at org.apache.cloudstack.spring.module.model.impl.
> >
> DefaultModuleDefinitionSet.withModule(DefaultModuleDefinitionSet.java:
> 249)
> > at org.apache.cloudstack.spring.module.model.impl.
> >
> DefaultModuleDefinitionSet.withModule(DefaultModuleDefinitionSet.java:
> 232)
> > at org.apache.cloudstack.spring.module.model.impl.
> > DefaultModuleDefinitionSet.loadContexts(DefaultModuleDefinitionSet.
> > java:116)
> > at org.apache.cloudstack.spring.module.model.impl.
> > DefaultModuleDefinitionSet.load(DefaultModuleDefinitionSet.java:78)
> > at org.apache.cloudstack.spring.module.factory.
> > ModuleBasedContextFactory.loadModules(ModuleBasedContextFactory.java:37)
> > at org.apache.cloudstack.spring.module.factory.
> > CloudStackSpringContext.init(CloudStackSpringContext.java:70)
> > at org.apache.cloudstack.spring.module.factory.
> > CloudStackSpringContext.<init>(CloudStackSpringContext.java:57)
> > at org.apache.cloudstack.spring.module.factory.
> > CloudStackSpringContext.<init>(CloudStackSpringContext.java:61)
> > at org.apache.cloudstack.spring.module.web.
> > CloudStackContextLoaderListener.contextInitialized(
> > CloudStackContextLoaderListener.java:52)
> > at org.eclipse.jetty.server.handler.ContextHandler.
> > callContextInitialized(ContextHandler.java:1073)
> > at org.eclipse.jetty.servlet.ServletContextHandler.
> > callContextInitialized(ServletContextHandler.java:572)
> > at org.eclipse.jetty.server.handler.ContextHandler.
> > contextInitialized(ContextHandler.java:1002)
> > at org.eclipse.jetty.servlet.ServletHandler.initialize(
> > ServletHandler.java:765)
> > at
> > org.eclipse.jetty.servlet.ServletContextHandler.startContext(
> > ServletContextHandler.java:379)
> > at org.eclipse.jetty.webapp.WebAppContext.startWebapp(
> > WebAppContext.java:1449)
> > at org.eclipse.jetty.webapp.WebAppContext.startContext(
> > WebAppContext.java:1414)
> > at org.eclipse.jetty.server.handler.ContextHandler.
> > doStart(ContextHandler.java:916)
> > at org.eclipse.jetty.servlet.ServletContextHandler.doStart(
> > ServletContextHandler.java:288)
> > at org.eclipse.jetty.webapp.WebAppContext.doStart(
> > WebAppContext.java:524)
> > at org.eclipse.jetty.util.component.AbstractLifeCycle.
> > start(AbstractLifeCycle.java:73)
> > at org.eclipse.jetty.util.component.ContainerLifeCycle.
> > start(ContainerLifeCycle.java:169)
> > at org.eclipse.jetty.util.component.ContainerLifeCycle.
> > doStart(ContainerLifeCycle.java:110)
> > at org.eclipse.jetty.server.handler.AbstractHandler.
> > doStart(AbstractHandler.java:97)
> > at org.eclipse.jetty.server.handler.gzip.GzipHandler.
> > doStart(GzipHandler.java:426)
> > at org.eclipse.jetty.util.component.AbstractLifeCycle.
> > start(AbstractLifeCycle.java:73)
> > at org.eclipse.jetty.util.component.ContainerLifeCycle.
> > start(ContainerLifeCycle.java:169)
> > at org.eclipse.jetty.util.component.ContainerLifeCycle.
> > doStart(ContainerLifeCycle.java:117)
> > at org.eclipse.jetty.server.handler.AbstractHandler.
> > doStart(AbstractHandler.java:97)
> > at org.eclipse.jetty.util.component.AbstractLifeCycle.
> > start(AbstractLifeCycle.java:73)
> > at org.eclipse.jetty.util.component.ContainerLifeCycle.
> > start(ContainerLifeCycle.java:169)
> > at org.eclipse.jetty.server.Server.start(Server.java:423)
> > at org.eclipse.jetty.util.component.ContainerLifeCycle.
> > doStart(ContainerLifeCycle.java:110)
> > at org.eclipse.jetty.server.handler.AbstractHandler.
> > doStart(AbstractHandler.java:97)
> > at org.eclipse.jetty.server.Server.doStart(Server.java:387)
> > at org.eclipse.jetty.util.component.AbstractLifeCycle.
> > start(AbstractLifeCycle.java:73)
> > at
> org.apache.cloudstack.ServerDaemon.start(ServerDaemon.java:192)
> > at
> > org.apache.cloudstack.ServerDaemon.main(ServerDaemon.java:107)
> > 2023-03-24 15:07:43,224 ERROR [c.c.u.DatabaseUpgradeChecker]
> > (main:null)
> > (logid:) Unable to upgrade the database
> > com.cloud.utils.exception.CloudRuntimeException: Unable to execute
> > upgrade script
> > at com.cloud.upgrade.DatabaseUpgradeChecker.runScript(
> > DatabaseUpgradeChecker.java:232)
> > at com.cloud.upgrade.DatabaseUpgradeChecker.upgrade(
> > DatabaseUpgradeChecker.java:310)
> > at com.cloud.upgrade.DatabaseUpgradeChecker.check(
> > DatabaseUpgradeChecker.java:401)
> > at org.apache.cloudstack.spring.lifecycle.
> > CloudStackExtendedLifeCycle.checkIntegrity(CloudStackExtendedLifeCycle.
> > java:64)
> > at org.apache.cloudstack.spring.lifecycle.
> > CloudStackExtendedLifeCycle.start(CloudStackExtendedLifeCycle.java:54)
> > at org.springframework.context.support.DefaultLifecycleProcessor.
> > doStart(DefaultLifecycleProcessor.java:178)
> > at org.springframework.context.support.DefaultLifecycleProcessor.
> > access$200(DefaultLifecycleProcessor.java:54)
> > at
> > org.springframework.context.support.DefaultLifecycleProcessor$
> > LifecycleGroup.start(DefaultLifecycleProcessor.java:356)
> > at java.base/java.lang.Iterable.forEach(Iterable.java:75)
> > at org.springframework.context.support.DefaultLifecycleProcessor.
> > startBeans(DefaultLifecycleProcessor.java:155)
> > at org.springframework.context.support.DefaultLifecycleProcessor.
> > onRefresh(DefaultLifecycleProcessor.java:123)
> > at
> org.springframework.context.support.AbstractApplicationContext.
> > finishRefresh(AbstractApplicationContext.java:935)
> > at
> org.springframework.context.support.AbstractApplicationContext.
> > refresh(AbstractApplicationContext.java:586)
> > at org.apache.cloudstack.spring.module.model.impl.
> > DefaultModuleDefinitionSet.loadContext(DefaultModuleDefinitionSet.
> > java:144)
> > at org.apache.cloudstack.spring.module.model.impl.
> > DefaultModuleDefinitionSet$2.with(DefaultModuleDefinitionSet.java:121)
> > at org.apache.cloudstack.spring.module.model.impl.
> >
> DefaultModuleDefinitionSet.withModule(DefaultModuleDefinitionSet.java:
> 244)
> > at org.apache.cloudstack.spring.module.model.impl.
> >
> DefaultModuleDefinitionSet.withModule(DefaultModuleDefinitionSet.java:
> 249)
> > at org.apache.cloudstack.spring.module.model.impl.
> >
> DefaultModuleDefinitionSet.withModule(DefaultModuleDefinitionSet.java:
> 232)
> > at org.apache.cloudstack.spring.module.model.impl.
> > DefaultModuleDefinitionSet.loadContexts(DefaultModuleDefinitionSet.
> > java:116)
> > at org.apache.cloudstack.spring.module.model.impl.
> > DefaultModuleDefinitionSet.load(DefaultModuleDefinitionSet.java:78)
> > at org.apache.cloudstack.spring.module.factory.
> > ModuleBasedContextFactory.loadModules(ModuleBasedContextFactory.java:37)
> > at org.apache.cloudstack.spring.module.factory.
> > CloudStackSpringContext.init(CloudStackSpringContext.java:70)
> > at org.apache.cloudstack.spring.module.factory.
> > CloudStackSpringContext.<init>(CloudStackSpringContext.java:57)
> > at org.apache.cloudstack.spring.module.factory.
> > CloudStackSpringContext.<init>(CloudStackSpringContext.java:61)
> > at org.apache.cloudstack.spring.module.web.
> > CloudStackContextLoaderListener.contextInitialized(
> > CloudStackContextLoaderListener.java:52)
> > at org.eclipse.jetty.server.handler.ContextHandler.
> > callContextInitialized(ContextHandler.java:1073)
> > at org.eclipse.jetty.servlet.ServletContextHandler.
> > callContextInitialized(ServletContextHandler.java:572)
> > at org.eclipse.jetty.server.handler.ContextHandler.
> > contextInitialized(ContextHandler.java:1002)
> > at org.eclipse.jetty.servlet.ServletHandler.initialize(
> > ServletHandler.java:765)
> > at
> > org.eclipse.jetty.servlet.ServletContextHandler.startContext(
> > ServletContextHandler.java:379)
> > at org.eclipse.jetty.webapp.WebAppContext.startWebapp(
> > WebAppContext.java:1449)
> > at org.eclipse.jetty.webapp.WebAppContext.startContext(
> > WebAppContext.java:1414)
> > at org.eclipse.jetty.server.handler.ContextHandler.
> > doStart(ContextHandler.java:916)
> > at org.eclipse.jetty.servlet.ServletContextHandler.doStart(
> > ServletContextHandler.java:288)
> > at org.eclipse.jetty.webapp.WebAppContext.doStart(
> > WebAppContext.java:524)
> > at org.eclipse.jetty.util.component.AbstractLifeCycle.
> > start(AbstractLifeCycle.java:73)
> > at org.eclipse.jetty.util.component.ContainerLifeCycle.
> > start(ContainerLifeCycle.java:169)
> > at org.eclipse.jetty.util.component.ContainerLifeCycle.
> > doStart(ContainerLifeCycle.java:110)
> > at org.eclipse.jetty.server.handler.AbstractHandler.
> > doStart(AbstractHandler.java:97)
> > at org.eclipse.jetty.server.handler.gzip.GzipHandler.
> > doStart(GzipHandler.java:426)
> > at org.eclipse.jetty.util.component.AbstractLifeCycle.
> > start(AbstractLifeCycle.java:73)
> > at org.eclipse.jetty.util.component.ContainerLifeCycle.
> > start(ContainerLifeCycle.java:169)
> > at org.eclipse.jetty.util.component.ContainerLifeCycle.
> > doStart(ContainerLifeCycle.java:117)
> > at org.eclipse.jetty.server.handler.AbstractHandler.
> > doStart(AbstractHandler.java:97)
> > at org.eclipse.jetty.util.component.AbstractLifeCycle.
> > start(AbstractLifeCycle.java:73)
> > at org.eclipse.jetty.util.component.ContainerLifeCycle.
> > start(ContainerLifeCycle.java:169)
> > at org.eclipse.jetty.server.Server.start(Server.java:423)
> > at org.eclipse.jetty.util.component.ContainerLifeCycle.
> > doStart(ContainerLifeCycle.java:110)
> > at org.eclipse.jetty.server.handler.AbstractHandler.
> > doStart(AbstractHandler.java:97)
> > at org.eclipse.jetty.server.Server.doStart(Server.java:387)
> > at org.eclipse.jetty.util.component.AbstractLifeCycle.
> > start(AbstractLifeCycle.java:73)
> > at
> org.apache.cloudstack.ServerDaemon.start(ServerDaemon.java:192)
> > at
> > org.apache.cloudstack.ServerDaemon.main(ServerDaemon.java:107)
> > Caused by: java.sql.SQLIntegrityConstraintViolationException: Can't
> > write; duplicate key in table '#sql-500_158'
> > 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:226)
> > ... 52 more
> > 2023-03-24 15:07:43,253 DEBUG [c.c.u.d.T.Transaction] (main:null)
> > (logid:) Rolling back the transaction:
> >
> >
> > Thanks 😊
> >
> > -David
> >
> >
> >
> > -
> >
> >
>
