Changing Database in an existing project

mortoza_khan · November 1, 2019, 7:42pm

I have been using PostgreSQL database in one of my projects and now want to use MariaDB instead. I have installed maria DB in my windows computer and connected from CUBA Studio.

Since I want to use a new database, I recreated database to have a fresh start but when I am trying to create database after generating the database scripts I am getting the exception below.

    Executing SQL script: D:\projects\inteaccerp\modules\core\build\db\20-bpm\init\mysql\activiti.identity.create-db.sql
Executing SQL script: D:\projects\inteaccerp\modules\core\build\db\20-reports\init\mysql\create-db.sql
Failed to execute: 
insert into REPORT_GROUP (ID, CREATE_TS, CREATED_BY, VERSION, TITLE, CODE, LOCALE_NAMES)
values ('4e0835300b9c11e19b416bdaa41bff94', current_timestamp, 'admin', 0, 'General', 'ReportGroup.default',
concat('en=General','\n','ru=?????')) because: (conn=124) Incorrect string value: '\xD0\x9E\xD0\xB1\xD1\x89...' for column `inteaccerp`.`report_group`.`LOCALE_NAMES` at row 1

Since Report is an add-on, I do not see any option in my project deleting those scripts what is reported to be already existing. Thanks for your help to overcome this.

gorbunkov · November 6, 2019, 1:38pm

Probably your database has wrong character encoding and collation. Try to use the character encoding utf8mb4 and collation utf8mb4_unicode_ci. This combination worked for me.

The following links may be useful:

mortoza_khan · November 7, 2019, 6:58am

Thank you Max for your help, I applied those commands and that error is resolved.
Now I am having another error message, seems like the DDL code generated in the studio is not competible with MariaDB.

Here is the DDL Code:

create table ERP_ACCOUNT (
ID varchar(32),
VERSION integer not null,
CREATE_TS datetime(3),
CREATED_BY varchar(50),
UPDATE_TS datetime(3),
UPDATED_BY varchar(50),
DELETE_TS datetime(3),
DELETED_BY varchar(50),
TENANT_ID varchar(255),
--
ACCOUNT_CODE varchar(10) not null,
ACCOUNT_HEADER boolean,
NAME varchar(255),
INACTIVE boolean,
HAVING_SUB_ACCOUNT boolean,
ACCOUNT_TYPE integer,
COST_ELEMENT_CATEGORY integer,
ACCOUNT_GROUP_ID varchar(32),
COST_ELEMENT_GROUP_ID varchar(32),
ACCOUNT_PARENT_ID varchar(32),
DEFAULT_BALANCE integer,
BANK_ACCOUNT boolean,
CASH_ACCOUNT boolean,
INTERFACE_ACCOUNT boolean,
--
primary key (ID)
)^

-- constraints

alter table ERP_ACCOUNT add constraint FK_ERP_ACCOUNT_ACCOUNT_GROUP foreign key (ACCOUNT_GROUP_ID) references ERP_ACCOUNT_GROUP(ID)^
alter table ERP_ACCOUNT add constraint FK_ERP_ACCOUNT_COST_ELEMENT_GROUP foreign key (COST_ELEMENT_GROUP_ID) references ERP_COST_ELEMENT_GROUP(ID)^
alter table ERP_ACCOUNT add constraint FK_ERP_ACCOUNT_ACCOUNT_PARENT foreign key (ACCOUNT_PARENT_ID) references ERP_ACCOUNT(ID)^


-- indexes

create index IDX_ERP_ACCOUNT_ACCOUNT_GROUP on ERP_ACCOUNT (ACCOUNT_GROUP_ID)^
create index IDX_ERP_ACCOUNT_COST_ELEMENT_GROUP on ERP_ACCOUNT (COST_ELEMENT_GROUP_ID)^
create index IDX_ERP_ACCOUNT_ACCOUNT_PARENT on ERP_ACCOUNT (ACCOUNT_PARENT_ID)^


-- unique indexes

create unique index IDX_ERP_ACCOUNT_UNQ_NAME on ERP_ACCOUNT (NAME, TENANT_ID) ^
create unique index IDX_ERP_ACCOUNT_UNQ_CODE on ERP_ACCOUNT (ACCOUNT_CODE, TENANT_ID) ^

Here is the error message obtained when I tried to update the connected mariadb. Note that I have generated the db scripts after connecting the Mariadb, therefore, expecting all database related codes are generated according to the connected database system.

WARNING: Failed to execute: alter table ERP_ACCOUNT add constraint FK_ERP_ACCOUNT_COST_ELEMENT_GROUP foreign key (COST_ELEMENT_GROUP_ID) references ERP_COST_ELEMENT_GROUP(ID);



        org.gradle.api.GradleException: Exception when executing SQL: alter table ERP_ACCOUNT add constraint FK_ERP_ACCOUNT_COST_ELEMENT_GROUP foreign key (COST_ELEMENT_GROUP_ID) references ERP_COST_ELEMENT_GROUP(ID);
create index IDX_ERP_ACCOUNT_COST_ELEMENT_GROUP on ERP_ACCOUNT (COST_ELEMENT_GROUP_ID);

	at com.haulmont.gradle.task.db.CubaDbTask.executeSqlScript(CubaDbTask.java:330)
	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.codehaus.groovy.runtime.callsite.PogoMetaMethodSite$PogoCachedMethodSiteNoUnwrapNoCoerce.invoke(PogoMetaMethodSite.java:210)
	at org.codehaus.groovy.runtime.callsite.PogoMetaMethodSite.callCurrent(PogoMetaMethodSite.java:59)
	at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callCurrent(AbstractCallSite.java:169)
	at CubaDbUpdate.executeScript(CubaDbUpdate.groovy:198)
	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.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:98)
	at groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:325)
	at org.codehaus.groovy.runtime.metaclass.ClosureMetaClass.invokeMethod(ClosureMetaClass.java:352)
	at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:1034)
	at org.codehaus.groovy.runtime.callsite.PogoMetaClassSite.callCurrent(PogoMetaClassSite.java:68)
	at org.codehaus.groovy.runtime.callsite.AbstractCallSite.callCurrent(AbstractCallSite.java:169)
	at CubaDbUpdate$_updateDb_closure3.doCall(CubaDbUpdate.groovy:73)
	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.codehaus.groovy.reflection.CachedMethod.invoke(CachedMethod.java:98)
	at groovy.lang.MetaMethod.doMethodInvoke(MetaMethod.java:325)
	at org.codehaus.groovy.runtime.metaclass.ClosureMetaClass.invokeMethod(ClosureMetaClass.java:264)
	at groovy.lang.MetaClassImpl.invokeMethod(MetaClassImpl.java:1034)
	at groovy.lang.Closure.call(Closure.java:418)
	at groovy.lang.Closure.call(Closure.java:434)
	at org.codehaus.groovy.runtime.DefaultGroovyMethods.each(DefaultGroovyMethods.java:2125)
	at org.codehaus.groovy.runtime.DefaultGroovyMethods.each(DefaultGroovyMethods.java:2110)
	at org.codehaus.groovy.runtime.DefaultGroovyMethods.each(DefaultGroovyMethods.java:2151)
	at org.codehaus.groovy.runtime.dgm$163.invoke(Unknown Source)
	at org.codehaus.groovy.runtime.callsite.PojoMetaMethodSite$PojoMetaMethodSiteNoUnwrapNoCoerce.invoke(PojoMetaMethodSite.java:274)
	at org.codehaus.groovy.runtime.callsite.PojoMetaMethodSite.call(PojoMetaMethodSite.java:56)
	at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:128)
	at CubaDbUpdate.updateDb(CubaDbUpdate.groovy:72)
	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.gradle.internal.reflect.JavaMethod.invoke(JavaMethod.java:73)
	at org.gradle.api.internal.project.taskfactory.StandardTaskAction.doExecute(StandardTaskAction.java:46)
	at org.gradle.api.internal.project.taskfactory.StandardTaskAction.execute(StandardTaskAction.java:39)
	at org.gradle.api.internal.project.taskfactory.StandardTaskAction.execute(StandardTaskAction.java:26)
	at org.gradle.api.internal.AbstractTask$TaskActionWrapper.execute(AbstractTask.java:801)
	at org.gradle.api.internal.AbstractTask$TaskActionWrapper.execute(AbstractTask.java:768)
	at org.gradle.api.internal.tasks.execution.ExecuteActionsTaskExecuter$1.run(ExecuteActionsTaskExecuter.java:131)
	at org.gradle.internal.operations.DefaultBuildOperationExecutor$RunnableBuildOperationWorker.execute(DefaultBuildOperationExecutor.java:301)
	at org.gradle.internal.operations.DefaultBuildOperationExecutor$RunnableBuildOperationWorker.execute(DefaultBuildOperationExecutor.java:293)
	at org.gradle.internal.operations.DefaultBuildOperationExecutor.execute(DefaultBuildOperationExecutor.java:175)
	at org.gradle.internal.operations.DefaultBuildOperationExecutor.run(DefaultBuildOperationExecutor.java:91)
	at org.gradle.internal.operations.DelegatingBuildOperationExecutor.run(DelegatingBuildOperationExecutor.java:31)
	at org.gradle.api.internal.tasks.execution.ExecuteActionsTaskExecuter.executeAction(ExecuteActionsTaskExecuter.java:120)
	at org.gradle.api.internal.tasks.execution.ExecuteActionsTaskExecuter.executeActions(ExecuteActionsTaskExecuter.java:99)
	at org.gradle.api.internal.tasks.execution.ExecuteActionsTaskExecuter.execute(ExecuteActionsTaskExecuter.java:77)
	at org.gradle.api.internal.tasks.execution.OutputDirectoryCreatingTaskExecuter.execute(OutputDirectoryCreatingTaskExecuter.java:51)
	at org.gradle.api.internal.tasks.execution.SkipUpToDateTaskExecuter.execute(SkipUpToDateTaskExecuter.java:59)
	at org.gradle.api.internal.tasks.execution.ResolveTaskOutputCachingStateExecuter.execute(ResolveTaskOutputCachingStateExecuter.java:54)
	at org.gradle.api.internal.tasks.execution.ValidatingTaskExecuter.execute(ValidatingTaskExecuter.java:59)
	at org.gradle.api.internal.tasks.execution.SkipEmptySourceFilesTaskExecuter.execute(SkipEmptySourceFilesTaskExecuter.java:101)
	at org.gradle.api.internal.tasks.execution.FinalizeInputFilePropertiesTaskExecuter.execute(FinalizeInputFilePropertiesTaskExecuter.java:44)
	at org.gradle.api.internal.tasks.execution.CleanupStaleOutputsExecuter.execute(CleanupStaleOutputsExecuter.java:91)
	at org.gradle.api.internal.tasks.execution.ResolveTaskArtifactStateTaskExecuter.execute(ResolveTaskArtifactStateTaskExecuter.java:62)
	at org.gradle.api.internal.tasks.execution.SkipTaskWithNoActionsExecuter.execute(SkipTaskWithNoActionsExecuter.java:59)
	at org.gradle.api.internal.tasks.execution.SkipOnlyIfTaskExecuter.execute(SkipOnlyIfTaskExecuter.java:54)
	at org.gradle.api.internal.tasks.execution.ExecuteAtMostOnceTaskExecuter.execute(ExecuteAtMostOnceTaskExecuter.java:43)
	at org.gradle.api.internal.tasks.execution.CatchExceptionTaskExecuter.execute(CatchExceptionTaskExecuter.java:34)
	at org.gradle.api.internal.tasks.execution.EventFiringTaskExecuter$1.run(EventFiringTaskExecuter.java:51)
	at org.gradle.internal.operations.DefaultBuildOperationExecutor$RunnableBuildOperationWorker.execute(DefaultBuildOperationExecutor.java:301)
	at org.gradle.internal.operations.DefaultBuildOperationExecutor$RunnableBuildOperationWorker.execute(DefaultBuildOperationExecutor.java:293)
	at org.gradle.internal.operations.DefaultBuildOperationExecutor.execute(DefaultBuildOperationExecutor.java:175)
	at org.gradle.internal.operations.DefaultBuildOperationExecutor.run(DefaultBuildOperationExecutor.java:91)
	at org.gradle.internal.operations.DelegatingBuildOperationExecutor.run(DelegatingBuildOperationExecutor.java:31)
	at org.gradle.api.internal.tasks.execution.EventFiringTaskExecuter.execute(EventFiringTaskExecuter.java:46)
	at org.gradle.execution.taskgraph.LocalTaskInfoExecutor.execute(LocalTaskInfoExecutor.java:42)
	at org.gradle.execution.taskgraph.DefaultTaskExecutionGraph$BuildOperationAwareWorkItemExecutor.execute(DefaultTaskExecutionGraph.java:277)
	at org.gradle.execution.taskgraph.DefaultTaskExecutionGraph$BuildOperationAwareWorkItemExecutor.execute(DefaultTaskExecutionGraph.java:262)
	at org.gradle.execution.taskgraph.DefaultTaskPlanExecutor$ExecutorWorker$1.execute(DefaultTaskPlanExecutor.java:135)
	at org.gradle.execution.taskgraph.DefaultTaskPlanExecutor$ExecutorWorker$1.execute(DefaultTaskPlanExecutor.java:130)
	at org.gradle.execution.taskgraph.DefaultTaskPlanExecutor$ExecutorWorker.execute(DefaultTaskPlanExecutor.java:200)
	at org.gradle.execution.taskgraph.DefaultTaskPlanExecutor$ExecutorWorker.executeWithWork(DefaultTaskPlanExecutor.java:191)
	at org.gradle.execution.taskgraph.DefaultTaskPlanExecutor$ExecutorWorker.run(DefaultTaskPlanExecutor.java:130)
	at org.gradle.internal.concurrent.ExecutorPolicy$CatchAndRecordFailures.onExecute(ExecutorPolicy.java:63)
	at org.gradle.internal.concurrent.ManagedExecutorImpl$1.run(ManagedExecutorImpl.java:46)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
	at org.gradle.internal.concurrent.ThreadFactoryImpl$ManagedThreadRunnable.run(ThreadFactoryImpl.java:55)
	at java.lang.Thread.run(Thread.java:748)
Caused by: java.sql.SQLSyntaxErrorException: (conn=5173) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'create index IDX_ERP_ACCOUNT_COST_ELEMENT_GROUP on ERP_ACCOUNT (COST_ELEMENT_GRO' at line 2
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:242)
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:171)
	at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:238)
	at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:327)
	at org.mariadb.jdbc.MariaDbStatement.execute(MariaDbStatement.java:378)
	at groovy.sql.Sql.execute(Sql.java:2339)
	at com.haulmont.gradle.task.db.CubaDbTask.executeSqlScript(CubaDbTask.java:328)
	... 89 more
Caused by: java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'create index IDX_ERP_ACCOUNT_COST_ELEMENT_GROUP on ERP_ACCOUNT (COST_ELEMENT_GRO' at line 2
Query is: alter table ERP_ACCOUNT add constraint FK_ERP_ACCOUNT_COST_ELEMENT_GROUP foreign key (COST_ELEMENT_GROUP_ID) references ERP_COST_ELEMENT_GROUP(ID);
create index IDX_ERP_ACCOUNT_COST_ELEMENT_GROUP on ERP_ACCOUNT (COST_ELEMENT_GROUP_ID);

java thread: Task worker for ':'
	at org.mariadb.jdbc.internal.util.LogQueryTool.exceptionWithQuery(LogQueryTool.java:126)
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:233)
	at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:321)
	... 92 more

iskandarov · November 7, 2019, 7:36am

Hi @mortozakhan
I am facing the same error in my environment.
Please inspect your update-scripts.

There should be something like:

alter table ERP_ACCOUNT add constraint FK_ERP_ACCOUNT_COST_ELEMENT_GROUP foreign key (COST_ELEMENT_GROUP_ID) references ERP_COST_ELEMENT_GROUP(ID);
create index IDX_ERP_ACCOUNT_COST_ELEMENT_GROUP on ERP_ACCOUNT (COST_ELEMENT_GROUP_ID);

Look there is “;” at the end of the query string. Change it to “^” and execute “Update Database”.

It seems Studio incorrectly generates the update-scripts for MariaDB. I am going to create the issue a little bit later.

Regards.

mortoza_khan · November 7, 2019, 8:21am

Thank you Max.tried to replace one by one but later by bulk replacement as I noticed that it’s more than 3,000 places to update. I hope this huge update by command is not breaking anything. Done! Now I’m fixing objects max length related violations due to lower limit in Marian than Postgres.

iskandarov · November 7, 2019, 10:16am

Here is the YouTrack issue.

mortoza_khan · November 7, 2019, 6:33pm

Hi
Thanks for opening the case in YouTrack.
I am getting another error message in Mariadb

Duplicate entry '20-reports/update/mysql/17/170131-addFieldsForAvailableReports.s' for key 'PRIMARY'