Different column types for MySQL and Postgres DDL

Steps to reproduce

(See discussion in for background for this bug)

I'd like to ask if in the MySQL DDL Script, should

CREATE TABLE mh_job (
id BIGINT NOT NULL,
status INTEGER,
payload TEXT(65535),
date_started DATETIME,
run_time BIGINT,
creator TEXT(65535) NOT NULL,
instance_version BIGINT,
date_completed DATETIME,
operation VARCHAR(128),
dispatchable TINYINT(1) DEFAULT 1,
organization VARCHAR(128) NOT NULL,
date_created DATETIME,
queue_time BIGINT,
creator_service BIGINT,
processor_service BIGINT,
parent BIGINT,
root BIGINT,
PRIMARY KEY (id),
CONSTRAINT FK_mh_job_creator_service FOREIGN KEY (creator_service) REFERENCES mh_service_registration (id) ON DELETE CASCADE,
CONSTRAINT FK_mh_job_processor_service FOREIGN KEY (processor_service) REFERENCES mh_service_registration (id) ON DELETE CASCADE,
CONSTRAINT FK_mh_job_parent FOREIGN KEY (parent) REFERENCES mh_job (id) ON DELETE CASCADE,
CONSTRAINT FK_mh_job_root FOREIGN KEY (root) REFERENCES mh_job (id) ON DELETE CASCADE,
CONSTRAINT FK_mh_job_organization FOREIGN KEY (organization) REFERENCES mh_organization (id) ON DELETE CASCADE
) ENGINE=InnoDB;

be changed to

CREATE TABLE mh_job (
id BIGINT NOT NULL,
status INTEGER,
payload TEXT(65535),
date_started DATETIME,
run_time BIGINT,
creator VARCHAR(128) NOT NULL,
instance_version BIGINT,
date_completed DATETIME,
operation VARCHAR(128),
dispatchable TINYINT(1) DEFAULT 1,
organization VARCHAR(128) NOT NULL,
date_created DATETIME,
queue_time BIGINT,
creator_service BIGINT,
processor_service BIGINT,
parent BIGINT,
root BIGINT,
PRIMARY KEY (id),
CONSTRAINT FK_mh_job_creator_service FOREIGN KEY (creator_service) REFERENCES mh_service_registration (id) ON DELETE CASCADE,
CONSTRAINT FK_mh_job_processor_service FOREIGN KEY (processor_service) REFERENCES mh_service_registration (id) ON DELETE CASCADE,
CONSTRAINT FK_mh_job_parent FOREIGN KEY (parent) REFERENCES mh_job (id) ON DELETE CASCADE,
CONSTRAINT FK_mh_job_root FOREIGN KEY (root) REFERENCES mh_job (id) ON DELETE CASCADE,
CONSTRAINT FK_mh_job_organization FOREIGN KEY (organization) REFERENCES mh_organization (id) ON DELETE CASCADE
) ENGINE=InnoDB;

as the "creator" column contains only a username (data type "text" is saved off the table IIRC, so this might be impractical here) and in the Postgres DDL Script, the "creator" column is already a character varying(128) type?

In MH-9910, Greg answered on this question:
"The question about mh_job is an interesting one. The autogenerated tables use the text data type in MySQL, wheras the DDLs create a mediumtext field. This means that changing to that VARCHAR means also changing the codebase, something I'm leery of doing unless we have a driving need to at this point. Maybe file that as a bug against 1.5 and we'll do it then since I'm sure we'll have lots of other things moving about at the same time."

Activity

Show:
Greg Logan
May 20, 2014, 5:11 PM

Looking at JobJpaImpl, we see the following:

@Lob
@Column(name = "creator", nullable = false, length = 65535)

This means it should be a column of at least 65535 length, which matches the MySQL DDL. You're welcome to submit an updated postgres ddl if you want, but the column size needs to match that 65535 (whether it needs to be that long is a good question, but something that should come up on list).

Alexander Bias
May 22, 2014, 11:20 AM

Dear Greg,

thank you for your feedback.

I think it won't be a problem to change in the postgres DDL script
CREATE TABLE "mh_job" (
[...]
"creator" character varying(128) NOT NULL,
[...]
}

to

CREATE TABLE "mh_job" (
[...]
"creator" text NOT NULL,
[...]
}

to make sure that MySQL and Postgres column types match again.

The corresponding Postgres upgrade script would be
ALTER TABLE "mh_job"
ALTER COLUMN "creator" TYPE text;

Could you include this change into 1.5.0?

Alex

Greg Logan
June 20, 2014, 7:25 PM

Resolved in the docs, we're removing the DDLs from the codebase

Fixed and reviewed

Assignee

Greg Logan

Reporter

Alexander Bias

Severity

Cosmetic Issue

Tags (folksonomy)

None

Components

Fix versions

Affects versions

Priority

Minor