[LPS-89456] Keyword "system" should be escaped for MySQL 8.0 - Liferay Issues

Details

Type: Bug
Status: Closed
Resolution: Fixed
Affects Version/s: Master
Fix Version/s: 7.0.0 DXP FP73, 7.0.10.11 DXP SP11, 7.1.10 DXP FP8, 7.1.10.2 SP2, 7.1.3 CE GA4, Master
Component/s: Core Infrastructure > Service Builder, Environments > Databases
Labels:

Epic/Theme:
- Core-Technical-Debt
Fix Priority:
5
Git Pull Request:
https://github.com/brianchandotcom/liferay-portal/pull/68074

Description

Steps to reproduce issue:

Install MySQL 8.0 locally or use docker instance
Set jdbc.default.driverClassName to com.mysql.cj.jdbc.Driver in portal-ext.properties
Replace existing mysql.jar with the new 8.0 connector: https://dev.mysql.com/downloads/connector/j/8.0.html
Start up portal

Expected result: Portal starts up successfully
Actual result: Portal fails to start due to Company and Layout tables not created, as the sql script(s) used to create them do not have the "system" keyword escaped

2019-01-22 20:33:00.384 INFO  [main][StartupHelper:72] There are no patches installed
2019-01-22 20:33:00.539 WARN  [main][ReleaseLocalServiceImpl:239] Table 'lportalmaster.Release_' doesn't exist
2019-01-22 20:33:00.543 INFO  [main][ReleaseLocalServiceImpl:130] Create tables and populate with default data
2019-01-22 20:33:01.197 WARN  [main][BaseDB:486] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'system tinyint,_        maxUsers integer,_      active_ tinyint_) engine InnoDB' at line 10: create table Company (_    mvccVersion bigint default 0 not null,_ companyId bigint not null primary key,_accountId bigint,_       webId varchar(75) null,_        key_ longtext null,_    mx varchar(200) null,_  homeURL longtext null,_logoId bigint,_  system tinyint,_        maxUsers integer,_      active_ tinyint_) engine InnoDB;_ [Sanitized]
2019-01-22 20:33:02.327 WARN  [main][BaseDB:486] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'system tinyint,_        friendlyURL varchar(255) null,_ iconImageId bigint,_    themeId va' at line 25: create table Layout (_  mvccVersion bigint default 0 not null,_ uuid_ varchar(75) null,_       plid bigint not null primary key,_       groupId bigint,_        companyId bigint,_      userId bigint,_ userName varchar(75) null,_     createDate datetime(6) null,_   modifiedDate datetime(6) null,_ parentPlid bigint,_     leftPlid bigint,_       rightPlid bigint,_      privateLayout tinyint,_ layoutId bigint,_       parentLayoutId bigint,_ name longtext null,_    title longtext null,_   description longtext null,_     keywords longtext null,_        robots longtext null,_  type_ varchar(75) null,_       typeSettings longtext null,_     hidden_ tinyint,_       system tinyint,_        friendlyURL varchar(255) null,_ iconImageId bigint,_    themeId varchar(75) null,_      colorSchemeId varchar(75) null,_        css longtext null,_     priority integer,_     layoutPrototypeUuid varchar(75) null,_   layoutPrototypeLinkEnabled tinyint,_    sourcePrototypeLayoutUuid varchar(75) null,_   lastPublishDate datetime(6) null_) engine InnoDB;_ [Sanitized]
2019-01-22 20:33:12.899 WARN  [main][BaseDB:486] Table 'lportalmaster.Company' doesn't exist: create index IX_38EFE3FD on Company (logoId);_ [Sanitized]
2019-01-22 20:33:12.903 WARN  [main][BaseDB:486] Table 'lportalmaster.Company' doesn't exist: create index IX_12566EC2 on Company (mx);_ [Sanitized]
2019-01-22 20:33:12.905 WARN  [main][BaseDB:486] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'system)' at line 1: create index IX_35E3E7C6 on Company (system);_ [Sanitized]
2019-01-22 20:33:12.908 WARN  [main][BaseDB:486] Table 'lportalmaster.Company' doesn't exist: create unique index IX_EC00543C on Company (webId);_ [Sanitized]
2019-01-22 20:33:16.428 WARN  [main][BaseDB:486] Table 'lportalmaster.Layout' doesn't exist: create index IX_881EABCB on Layout (companyId, layoutPrototypeUuid);_ [Sanitized]
2019-01-22 20:33:16.431 WARN  [main][BaseDB:486] Table 'lportalmaster.Layout' doesn't exist: create index IX_D4B8A526 on Layout (groupId, leftPlid, rightPlid, privateLayout);_ [Sanitized]
2019-01-22 20:33:16.433 WARN  [main][BaseDB:486] Table 'lportalmaster.Layout' doesn't exist: create unique index IX_BC2C4231 on Layout (groupId, privateLayout, friendlyURL);_ [Sanitized]
2019-01-22 20:33:16.435 WARN  [main][BaseDB:486] Table 'lportalmaster.Layout' doesn't exist: create unique index IX_7162C27C on Layout (groupId, privateLayout, layoutId);_ [Sanitized]
2019-01-22 20:33:16.438 WARN  [main][BaseDB:486] Table 'lportalmaster.Layout' doesn't exist: create index IX_2C89110E on Layout (groupId, privateLayout, leftPlid, rightPlid);_ [Sanitized]
2019-01-22 20:33:16.442 WARN  [main][BaseDB:486] Table 'lportalmaster.Layout' doesn't exist: create index IX_7399B71E on Layout (groupId, privateLayout, parentLayoutId, priority);_ [Sanitized]
2019-01-22 20:33:16.445 WARN  [main][BaseDB:486] Table 'lportalmaster.Layout' doesn't exist: create index IX_8CE8C0D9 on Layout (groupId, privateLayout, sourcePrototypeLayoutUuid);_ [Sanitized]
2019-01-22 20:33:16.448 WARN  [main][BaseDB:486] Table 'lportalmaster.Layout' doesn't exist: create index IX_1A1B61D2 on Layout (groupId, privateLayout, type_);_ [Sanitized]
2019-01-22 20:33:16.451 WARN  [main][BaseDB:486] Table 'lportalmaster.Layout' doesn't exist: create index IX_6EDC627B on Layout (groupId, type_);_ [Sanitized]
2019-01-22 20:33:16.453 WARN  [main][BaseDB:486] Table 'lportalmaster.Layout' doesn't exist: create index IX_23922F7D on Layout (iconImageId);_ [Sanitized]
2019-01-22 20:33:16.456 WARN  [main][BaseDB:486] Table 'lportalmaster.Layout' doesn't exist: create index IX_B529BFD3 on Layout (layoutPrototypeUuid);_ [Sanitized]
2019-01-22 20:33:16.460 WARN  [main][BaseDB:486] Table 'lportalmaster.Layout' doesn't exist: create index IX_1D4DCAA5 on Layout (parentPlid);_ [Sanitized]
2019-01-22 20:33:16.463 WARN  [main][BaseDB:486] Table 'lportalmaster.Layout' doesn't exist: create index IX_3BC009C0 on Layout (privateLayout, iconImageId);_ [Sanitized]
2019-01-22 20:33:16.465 WARN  [main][BaseDB:486] Table 'lportalmaster.Layout' doesn't exist: create index IX_39A18ECC on Layout (sourcePrototypeLayoutUuid);_ [Sanitized]
2019-01-22 20:33:16.468 WARN  [main][BaseDB:486] Table 'lportalmaster.Layout' doesn't exist: create index IX_2CE4BE84 on Layout (uuid_, companyId);_ [Sanitized]
2019-01-22 20:33:16.471 WARN  [main][BaseDB:486] Table 'lportalmaster.Layout' doesn't exist: create unique index IX_E118C537 on Layout (uuid_, groupId, privateLayout);_ [Sanitized]
2019-01-22 20:33:25.215 INFO  [main][UpgradeProcess:93] Upgrading com.liferay.portal.security.audit.storage.internal.activator.AuditStorageServiceBundleActivator$1$1
2019-01-22 20:33:25.226 INFO  [main][UpgradeProcess:107] Completed upgrade process com.liferay.portal.security.audit.storage.internal.activator.AuditStorageServiceBundleActivator$1$1 in 11 ms
2019-01-22 20:33:25.229 INFO  [main][UpgradeProcess:93] Upgrading com.liferay.knowledge.base.internal.activator.KnowledgeBaseServiceBundleActivator$1$1
2019-01-22 20:33:25.233 INFO  [main][UpgradeProcess:107] Completed upgrade process com.liferay.knowledge.base.internal.activator.KnowledgeBaseServiceBundleActivator$1$1 in 4 ms
2019-01-22 20:33:27.278 INFO  [main][VerifyProcess:65] Verifying com.liferay.portal.verify.VerifyProcessSuite
2019-01-22 20:33:27.281 INFO  [main][VerifyProcess:65] Verifying com.liferay.portal.verify.VerifyProperties
2019-01-22 20:33:27.293 INFO  [main][LoggingTimer:83] Starting com.liferay.portal.verify.VerifyProperties#verifySystemProperties
2019-01-22 20:33:27.293 INFO  [main][LoggingTimer:43] Completed com.liferay.portal.verify.VerifyProperties#verifySystemProperties in 0 ms
2019-01-22 20:33:27.294 INFO  [main][LoggingTimer:83] Starting com.liferay.portal.verify.VerifyProperties#verifyPortalProperties
2019-01-22 20:33:27.314 INFO  [main][LoggingTimer:43] Completed com.liferay.portal.verify.VerifyProperties#verifyPortalProperties in 20 ms
2019-01-22 20:33:27.316 INFO  [main][LoggingTimer:83] Starting com.liferay.portal.verify.VerifyProperties#verifyDocumentLibrary
2019-01-22 20:33:27.316 INFO  [main][LoggingTimer:43] Completed com.liferay.portal.verify.VerifyProperties#verifyDocumentLibrary in 0 ms
2019-01-22 20:33:27.317 INFO  [main][VerifyProcess:80] Completed verification process com.liferay.portal.verify.VerifyProperties in 36 ms
2019-01-22 20:33:27.319 INFO  [main][VerifyProcess:65] Verifying com.liferay.portal.verify.VerifyPermission
2019-01-22 20:33:27.319 INFO  [main][LoggingTimer:83] Starting com.liferay.portal.verify.VerifyPermission#checkPermissions
2019-01-22 20:33:31.390 INFO  [main][LoggingTimer:43] Completed com.liferay.portal.verify.VerifyPermission#checkPermissions in 4071 ms
2019-01-22 20:33:31.390 INFO  [main][LoggingTimer:83] Starting com.liferay.portal.verify.VerifyPermission#fixOrganizationRolePermissions
2019-01-22 20:33:31.470 INFO  [main][LoggingTimer:43] Completed com.liferay.portal.verify.VerifyPermission#fixOrganizationRolePermissions in 80 ms
2019-01-22 20:33:31.471 INFO  [main][LoggingTimer:83] Starting com.liferay.portal.verify.VerifyPermission#fixUserDefaultRolePermissions
2019-01-22 20:33:31.477 INFO  [main][LoggingTimer:43] Completed com.liferay.portal.verify.VerifyPermission#fixUserDefaultRolePermissions in 6 ms
2019-01-22 20:33:31.479 INFO  [main][VerifyProcess:80] Completed verification process com.liferay.portal.verify.VerifyPermission in 4160 ms
2019-01-22 20:33:31.479 INFO  [main][VerifyProcess:80] Completed verification process com.liferay.portal.verify.VerifyProcessSuite in 4201 ms
2019-01-22 20:33:31.497 ERROR [main][DBUpgrader:259] Unable to execute verify process: com.liferay.portal.verify.VerifyException: java.sql.SQLSyntaxErrorException: Table 'lportalmaster.Company' doesn't exist
com.liferay.portal.verify.VerifyException: com.liferay.portal.verify.VerifyException: java.sql.SQLSyntaxErrorException: Table 'lportalmaster.Company' doesn't exist
        at com.liferay.portal.verify.VerifyProcess.verify(VerifyProcess.java:74)
        at com.liferay.portal.verify.VerifyProcessUtil._verifyProcess(VerifyProcessUtil.java:113)
        at com.liferay.portal.verify.VerifyProcessUtil._verifyProcess(VerifyProcessUtil.java:78)
        at com.liferay.portal.verify.VerifyProcessUtil.verifyProcess(VerifyProcessUtil.java:47)
        at com.liferay.portal.events.StartupHelper.verifyProcess(StartupHelper.java:162)
        at com.liferay.portal.events.StartupHelperUtil.verifyProcess(StartupHelperUtil.java:91)
        at com.liferay.portal.tools.DBUpgrader.verify(DBUpgrader.java:253)
        at com.liferay.portal.events.StartupAction.doRun(StartupAction.java:218)
        at com.liferay.portal.events.StartupAction.run(StartupAction.java:78)
        at com.liferay.portal.internal.servlet.MainServlet.init(MainServlet.java:289)
        at javax.servlet.GenericServlet.init(GenericServlet.java:158)
        at org.apache.catalina.core.StandardWrapper.initServlet(StandardWrapper.java:1124)
        at org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java:1079)
        at org.apache.catalina.core.StandardWrapper.load(StandardWrapper.java:971)
        at org.apache.catalina.core.StandardContext.loadOnStartup(StandardContext.java:4829)
        at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5143)
        at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
        at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:742)
        at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:718)
        at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:703)
        at org.apache.catalina.startup.HostConfig.deployDescriptor(HostConfig.java:630)
        at org.apache.catalina.startup.HostConfig$DeployDescriptor.run(HostConfig.java:1840)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
        at java.util.concurrent.FutureTask.run(FutureTask.java:266)
        at org.apache.tomcat.util.threads.InlineExecutorService.execute(InlineExecutorService.java:75)
        at java.util.concurrent.AbstractExecutorService.submit(AbstractExecutorService.java:112)
        at org.apache.catalina.startup.HostConfig.deployDescriptors(HostConfig.java:525)
        at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:424)
        at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1585)
        at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:308)
        at org.apache.catalina.util.LifecycleBase.fireLifecycleEvent(LifecycleBase.java:123)
        at org.apache.catalina.util.LifecycleBase.setStateInternal(LifecycleBase.java:424)
        at org.apache.catalina.util.LifecycleBase.setState(LifecycleBase.java:367)
        at org.apache.catalina.core.ContainerBase.startInternal(ContainerBase.java:966)
        at org.apache.catalina.core.StandardHost.startInternal(StandardHost.java:839)
        at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
        at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1427)
        at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1417)
        at java.util.concurrent.FutureTask.run(FutureTask.java:266)
        at org.apache.tomcat.util.threads.InlineExecutorService.execute(InlineExecutorService.java:75)
        at java.util.concurrent.AbstractExecutorService.submit(AbstractExecutorService.java:134)
        at org.apache.catalina.core.ContainerBase.startInternal(ContainerBase.java:943)
        at org.apache.catalina.core.StandardEngine.startInternal(StandardEngine.java:258)
        at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
        at org.apache.catalina.core.StandardService.startInternal(StandardService.java:422)
        at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
        at org.apache.catalina.core.StandardServer.startInternal(StandardServer.java:770)
        at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
        at org.apache.catalina.startup.Catalina.start(Catalina.java:682)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:350)
        at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:492)
Caused by: com.liferay.portal.verify.VerifyException: java.sql.SQLSyntaxErrorException: Table 'lportalmaster.Company' doesn't exist
        at com.liferay.portal.verify.VerifyProcess.verify(VerifyProcess.java:74)
        at com.liferay.portal.verify.VerifyProcess.verify(VerifyProcess.java:91)
        at com.liferay.portal.verify.VerifyProcessSuite.doVerify(VerifyProcessSuite.java:26)
        at com.liferay.portal.verify.VerifyProcess.verify(VerifyProcess.java:71)
        ... 54 more
Caused by: java.sql.SQLSyntaxErrorException: Table 'lportalmaster.Company' doesn't exist
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:970)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1020)
        at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
        at com.liferay.portal.util.PortalInstances.getCompanyIdsBySQL(PortalInstances.java:185)
        at com.liferay.portal.verify.VerifyPermission.fixUserDefaultRolePermissions(VerifyPermission.java:183)
        at com.liferay.portal.verify.VerifyPermission.doVerify(VerifyPermission.java:86)
        at com.liferay.portal.verify.VerifyProcess.verify(VerifyProcess.java:71)
        ... 57 more
2019-01-22 20:33:31.504 ERROR [main][MainServlet:292] com.liferay.portal.kernel.events.ActionException: com.liferay.portal.verify.VerifyException: com.liferay.portal.verify.VerifyException: java.sql.SQLSyntaxErrorException: Table 'lportalmaster.Company' doesn't exist
com.liferay.portal.kernel.events.ActionException: com.liferay.portal.verify.VerifyException: com.liferay.portal.verify.VerifyException: java.sql.SQLSyntaxErrorException: Table 'lportalmaster.Company' doesn't exist
        at com.liferay.portal.events.StartupAction.run(StartupAction.java:84)
        at com.liferay.portal.internal.servlet.MainServlet.init(MainServlet.java:289)
        at javax.servlet.GenericServlet.init(GenericServlet.java:158)
        at org.apache.catalina.core.StandardWrapper.initServlet(StandardWrapper.java:1124)
        at org.apache.catalina.core.StandardWrapper.loadServlet(StandardWrapper.java:1079)
        at org.apache.catalina.core.StandardWrapper.load(StandardWrapper.java:971)
        at org.apache.catalina.core.StandardContext.loadOnStartup(StandardContext.java:4829)
        at org.apache.catalina.core.StandardContext.startInternal(StandardContext.java:5143)
        at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
        at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase.java:742)
        at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:718)
        at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:703)
        at org.apache.catalina.startup.HostConfig.deployDescriptor(HostConfig.java:630)
        at org.apache.catalina.startup.HostConfig$DeployDescriptor.run(HostConfig.java:1840)
        at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
        at java.util.concurrent.FutureTask.run(FutureTask.java:266)
        at org.apache.tomcat.util.threads.InlineExecutorService.execute(InlineExecutorService.java:75)
        at java.util.concurrent.AbstractExecutorService.submit(AbstractExecutorService.java:112)
        at org.apache.catalina.startup.HostConfig.deployDescriptors(HostConfig.java:525)
        at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:424)
        at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1585)
        at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java:308)
        at org.apache.catalina.util.LifecycleBase.fireLifecycleEvent(LifecycleBase.java:123)
        at org.apache.catalina.util.LifecycleBase.setStateInternal(LifecycleBase.java:424)
        at org.apache.catalina.util.LifecycleBase.setState(LifecycleBase.java:367)
        at org.apache.catalina.core.ContainerBase.startInternal(ContainerBase.java:966)
        at org.apache.catalina.core.StandardHost.startInternal(StandardHost.java:839)
        at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
        at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1427)
        at org.apache.catalina.core.ContainerBase$StartChild.call(ContainerBase.java:1417)
        at java.util.concurrent.FutureTask.run(FutureTask.java:266)
        at org.apache.tomcat.util.threads.InlineExecutorService.execute(InlineExecutorService.java:75)
        at java.util.concurrent.AbstractExecutorService.submit(AbstractExecutorService.java:134)
        at org.apache.catalina.core.ContainerBase.startInternal(ContainerBase.java:943)
        at org.apache.catalina.core.StandardEngine.startInternal(StandardEngine.java:258)
        at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
        at org.apache.catalina.core.StandardService.startInternal(StandardService.java:422)
        at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
        at org.apache.catalina.core.StandardServer.startInternal(StandardServer.java:770)
        at org.apache.catalina.util.LifecycleBase.start(LifecycleBase.java:183)
        at org.apache.catalina.startup.Catalina.start(Catalina.java:682)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:350)
        at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:492)
Caused by: com.liferay.portal.verify.VerifyException: com.liferay.portal.verify.VerifyException: java.sql.SQLSyntaxErrorException: Table 'lportalmaster.Company' doesn't exist
        at com.liferay.portal.verify.VerifyProcess.verify(VerifyProcess.java:74)
        at com.liferay.portal.verify.VerifyProcessUtil._verifyProcess(VerifyProcessUtil.java:113)
        at com.liferay.portal.verify.VerifyProcessUtil._verifyProcess(VerifyProcessUtil.java:78)
        at com.liferay.portal.verify.VerifyProcessUtil.verifyProcess(VerifyProcessUtil.java:47)
        at com.liferay.portal.events.StartupHelper.verifyProcess(StartupHelper.java:162)
        at com.liferay.portal.events.StartupHelperUtil.verifyProcess(StartupHelperUtil.java:91)
        at com.liferay.portal.tools.DBUpgrader.verify(DBUpgrader.java:253)
        at com.liferay.portal.events.StartupAction.doRun(StartupAction.java:218)
        at com.liferay.portal.events.StartupAction.run(StartupAction.java:78)
        ... 46 more
Caused by: com.liferay.portal.verify.VerifyException: java.sql.SQLSyntaxErrorException: Table 'lportalmaster.Company' doesn't exist
        at com.liferay.portal.verify.VerifyProcess.verify(VerifyProcess.java:74)
        at com.liferay.portal.verify.VerifyProcess.verify(VerifyProcess.java:91)
        at com.liferay.portal.verify.VerifyProcessSuite.doVerify(VerifyProcessSuite.java:26)
        at com.liferay.portal.verify.VerifyProcess.verify(VerifyProcess.java:71)
        ... 54 more
Caused by: java.sql.SQLSyntaxErrorException: Table 'lportalmaster.Company' doesn't exist
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:120)
        at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
        at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeInternal(ClientPreparedStatement.java:970)
        at com.mysql.cj.jdbc.ClientPreparedStatement.executeQuery(ClientPreparedStatement.java:1020)
        at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
        at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
        at com.liferay.portal.util.PortalInstances.getCompanyIdsBySQL(PortalInstances.java:185)
        at com.liferay.portal.verify.VerifyPermission.fixUserDefaultRolePermissions(VerifyPermission.java:183)
        at com.liferay.portal.verify.VerifyPermission.doVerify(VerifyPermission.java:86)
        at com.liferay.portal.verify.VerifyProcess.verify(VerifyProcess.java:71)
        ... 57 more
Stopping the server due to unexpected startup errors

Alternative steps:

Install MySQL 8.0 locally or use docker instance
In portal directory, run ant compile to generate sql scripts
Enter into mysql shell for MySQL 8.0 instance
Source the create-mysql.sql script from sql/create

Expected result: create-mysql.sql is executed successfully
Actual result: create-mysql.sql logs syntax errors where the "system" keyword is used; Company and Layout tables are not created, and interactions with these tables fail.

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'system tinyint,
        maxUsers integer,
        active_ tinyint
) engine InnoDB' at line 10
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'system tinyint,
        friendlyURL varchar(255) null,
        iconImageId bigint,
        themeId va' at line 25
ERROR 1146 (42S02): Table 'lportalmaster.Company' doesn't exist
ERROR 1146 (42S02): Table 'lportalmaster.Company' doesn't exist
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'system)' at line 1
ERROR 1146 (42S02): Table 'lportalmaster.Company' doesn't exist
ERROR 1146 (42S02): Table 'lportalmaster.Layout' doesn't exist
ERROR 1146 (42S02): Table 'lportalmaster.Layout' doesn't exist
ERROR 1146 (42S02): Table 'lportalmaster.Layout' doesn't exist
ERROR 1146 (42S02): Table 'lportalmaster.Layout' doesn't exist
ERROR 1146 (42S02): Table 'lportalmaster.Layout' doesn't exist
ERROR 1146 (42S02): Table 'lportalmaster.Layout' doesn't exist
ERROR 1146 (42S02): Table 'lportalmaster.Layout' doesn't exist
ERROR 1146 (42S02): Table 'lportalmaster.Layout' doesn't exist
ERROR 1146 (42S02): Table 'lportalmaster.Layout' doesn't exist
ERROR 1146 (42S02): Table 'lportalmaster.Layout' doesn't exist
ERROR 1146 (42S02): Table 'lportalmaster.Layout' doesn't exist
ERROR 1146 (42S02): Table 'lportalmaster.Layout' doesn't exist
ERROR 1146 (42S02): Table 'lportalmaster.Layout' doesn't exist
ERROR 1146 (42S02): Table 'lportalmaster.Layout' doesn't exist
ERROR 1146 (42S02): Table 'lportalmaster.Layout' doesn't exist
ERROR 1146 (42S02): Table 'lportalmaster.Layout' doesn't exist

Reproduced on:
Tomcat 9.0.10 + MySQL 8.0
Portal Master GIT ID: e1226e17f999a491a3669f1c5b8577be8fbc7703

Attachments

Issue Links

causes

LPS-90423 Description column changes are affecting Forms creation

Closed

LPS-90422 Deleting inactive user ends in SQLGrammarException

Closed

LPS-90447 MySQLSyntaxErrorException thrown when starting up bundle after upgrade

Closed

LPS-90448 Unknown column 'pollsquest0_.description' in 'field list' when starting up after upgrade

Closed

Keyword "system" should be escaped for MySQL 8.0

Details

Description

Attachments

Issue Links

Activity

People

Dates

Packages

Version	Package
7.0.0 DXP FP73
7.0.10.11 DXP SP11
7.1.10 DXP FP8
7.1.10.2 SP2
7.1.3 CE GA4
Master