[FIXED] Liquibase and MySql - how to auto generate id by UUID

Issue

I want to create liquibase's scripts. I would like to auto generate id by UUID when I will do insert operations in next scripts. I tried with something what I found there: EDIT: I used an answer:

<property name="u_id" value="uuid()" dbms="mysql"/>
<changeSet id="1" author="xyz">
    <createTable tableName="persons">
        <column name="id" type="varchar(36)" defaultValueComputed="${u_id}">
            <constraints primaryKey="true" nullable="false"/>
        </column>
        <column name="name" type="varchar(50)">
            <constraints nullable="false"/>
        </column>
        <column name="surname" type="varchar(50)">
            <constraints nullable="false"/>
        </column>
        <column name="email" type="varchar(50)">
            <constraints nullable="false" unique="true"/>
        </column>
    </createTable>
</changeSet>
</databaseChangeLog>

First script is OK, table has been created but when I add second script:

<changeSet  id="2" author="xyz">
    <insert tableName="persons">
        <column  name="name"  value="name value"/>
        <column  name="surname"  value="surname value"/>
        <column  name="email"  value="email value"/>
    </insert>
</changeSet>

And now error is:

org.springframework.beans.factory.BeanCreationException: Error creating bean with 
name 'liquibase' defined in class path resource [liquibase/LiquibaseAutoConfiguration$LiquibaseConfiguration.class]: 
Invocation of init method failed; nested exception is liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: 
  Migration failed for change set db/version/V1__create_persons_table.xml::1::xyz:
 Reason: liquibase.exception.DatabaseException: 
 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 'uuid() NOT NULL, 
 name VARCHAR(50) NOT NULL, surname VARCHAR(50) NOT NULL' at line 1 [Failed SQL: (1064) CREATE TABLE ticketapidatabase.persons (id VARCHAR(36) DEFAULT uuid() NOT NULL, name VARCHAR(50) NOT NULL, surname VARCHAR(50) NOT NULL, email VARCHAR(50) NOT NULL, CONSTRAINT PK_PERSONS PRIMARY KEY (id), UNIQUE (email))]

Solution

Your id column definition should look something like this:

<column name="id" type="varchar(36)" defaultValueComputed="${u_id}">
    <constraints primaryKey="true" nullable="false"/>
</column>

Answered By - dekkard
Answer Checked By - Candace Johnson (JavaFixing Volunteer)