Don't know how to represent mysql type of <table>.<column> (timestamp)

[denji]

Seems to be a problem with the work of the timestamp data type:

DROP TABLE IF EXISTS `ts_bug`;
CREATE TABLE `ts_bug` (
    `ids` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `tm` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`ids`)
);
INSERT INTO `ts_bug` (tm) VALUES (CURRENT_TIMESTAMP);

$ ks --from "mysql://denji@localhost/db1" --to "postgresql://denji@localhost/db1"

> Don't know how to represent mysql type of ts_bug.tm (timestamp)

Types formats Conversion: mysql2pgsql/lib/postgres_writer.py

MySQL	PostgreSQL
date	date
datetime	timestamp without time zone
time	time without time zone
timestamp	timestamp without time zone

• https://github.com/mihailShumilov/mysql2postgresql (PHP)

• https://github.com/dimitri/pgloader ( Tcl (1.х), Python (2.x), Common Lisp (3.x) )

• https://github.com/maxlapshin/mysql2postgres (Ruby)

• https://github.com/lanyrd/mysql-postgresql-converter (Python)

• http://www.postgresonline.com/journal/archives/29-How-to-convert-a-table-column-to-another-data-type.html

• PostgreSQL complain about timestamp '0000-00-00 00:00:00'

  ALTER TABLE table ALTER COLUMN col TYPE integer USING function(...);
  

mysqlcompat (SQL functions, easing migration)

• https://github.com/pornel/mysqlcompat (SQL/Shell)
• http://www.postgresql.org/ftp/projects/pgFoundry/mysqlcompat/mysqlcompat/ (SQL/Shell)

[willbryant]

So we have two ways we could represent this. One option is to add a mysql-specific type and to map it to the existing type on postgresql. The other is to treat this as a datetime column with a special default.

Is the CURRENT_TIMESTAMP default the only reason you are using the timestamp type?

[denji]

Here are some details of the conversion MySQL to PostgreSQL NULL:

• mysql::timestamp (YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYMMDDHHMM, YYYYMMDD, YYMMDD, YYMM, YY)
• mysql::datetime (0000-00-00 00:00:00)

We need to transform the 'allballs'::time or just '00:00:00'::time, '-infinity'::timestamp.
Transformation back to MySQL not yet decided, but there can be problems with strict-mode in MySQL 5.6.6+.

[willbryant]

So we can turn the mysql special cases into postgresql default value functions. I agree we should use -infinity for the 0000-00-00 00:00:00 time.

What about going the other way? Currently we have no default value function conversion (ie. from mysql to postgresql syntax or vice versa), so maybe we would need to add this for some special cases that kitchen sync itself produces.

[xrewndel]

I have this error too. Any plans to fix this? Or workarounds?

[willbryant]

@xrewndel is the CURRENT_TIMESTAMP default the only reason you are using the timestamp type?

[xrewndel]

@willbryant I don't know. I just want to sync mysql to postgres every 5 min. That is my task. I'm looking for simple solutions and don't want to use Talend or smth like this, cause its over-engineering in my particular case.

[willbryant]

The issue is that postgresql doesn't have a TIMESTAMP type, so we're trying to decide what it should be mapped to.

We can use a default value function to make inserts into the postgresql table populate the TIMESTAMP column, simulating the mysql behavior.

But as far as I'm aware there's no column setting that can make postgresql reset the timestamp column every time the row is updated, which we'd need to simulate the mysql update behavior. I think you'd have to write a trigger function.

What behavior are you expecting when it gets converted to a postgresql schema?

[damirm]

Have the same problem.
I think that mysql timestamp should converts to postgres timestamp without time zone

[willbryant]

Cool. What about the defaults stuff in my preceding comment?

[willbryant]

I think version 0.59 has enough that most of you should be able to use it. The special behavior of the column type is mostly supported, except for ON UPDATE CURRENT_TIMESTAMP which has no equivalent in PostgreSQL, and the special values.

Please open new issues for any remaining blockers you find.