porter

porter copied to clipboard

I have a PHPBB3 DB with a user_timezone column that's varchar(100) and contains things like

Etc/GMT+8
Australia/ACT
UTC
Etc/GMT-2
Etc/GMT-10
Etc/GMT-1
Pacific/Tongatapu
Europe/London
Etc/GMT-8

The export then contains these in the HourOffset position and the import fails as they are not integers.

Incorrect integer value: '' for column 'HourOffset' at row 1

Confirmed:

Incorrect integer value: 'UTC' for column 'HourOffset' at row 1

Replacing the time zone string with the corresponding offset (i.e. Etc/GMT+8 -> +8) is a workaround, however I'm not sure how's the conversion for decimal offsets.

---

Incorrect datetime value: '' for column 'DateLastActive' at row 1

When DateLastActive's value is \N. I replaced it with 1970-01-01 00:00:00.

I had to do this for other date fields (e.g. DateUpdated) as well.

---

Incorrect string value: '\xF3zk' for column 'Name' at row 27

Looks like converting the file from ISO-8859-15 to UTF-8 is enough:

iconv -f iso-8859-15 -t utf-8 input.txt -o output.txt

I see many ? inside the file though.

---

Incorrect integer value: '' for column 'ParentCategoryID' at row 1

Root categories have \N as ID. I changed it to 0.

---

Data too long for column 'Name' at row 70

Fixed by changing the SQL column's datatype:

ALTER TABLE GDN_Discussion MODIFY COLUMN Name LONGTEXT;