github / github/gh-ost

gh-ost is not properly identifying column types

Open
#660 0 comments 0 reactions 0 assignees View on GitHub
Dominant language
Go
Stars
13.6k
Forks
1.4k
Avg merge
2h 31m
Merged PRs (30d)
4

Description

I'm facing an issue when trying to alter a table that contains a `json` column.

Original table
```sql
CREATE TABLE `xxx_shipping` (
`xxx_id` bigint(20) unsigned NOT NULL,
`zones` json NOT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`xxx_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
```

Final table
```sql
CREATE TABLE `_xxx_shipping_gho` (
`xxx_id` bigint(20) unsigned NOT NULL,
`my_new_column` json DEFAULT NULL,
`zones` json NOT NULL,
`updated_at` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`xxx_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
```

At some point during the migration I get the following error message:

```
2018-10-31 14:59:57 ERROR Error 3144: Cannot create a JSON value from a str
ing with CHARACTER SET 'binary'.; query=
replace /* gh-ost `my_database`.`_xxx_shipping_gho` */ in
to
`my_database`.`_xxx_shipping_gho`
(`xxx_id`, `zones`, `updated_at`)
values
(?, ?, ?)
; args=[15680576 [123 34 97 100 34 58 91 93 44 34 97 101 34 58 91 93 44 3
4 97 102 34 58 91 93 44 34 97 103 34 58 91 93 44 34 97 105 34 58 91 93 44 3
4 97 108 34 58 91 93 44 34 97 109 34 58 91 93 44 34 97 111 34 58 91 93 44 3
4 97 113 34 58 91 93 44 34 97 114 34 58 91 93 44 34 97 115 34 58 91 93 44 3
4 97 116 34 58 91 93 44 34 97 117 34 58 91 93 44 34 97 119 34 58 91 93 44 3
...
```

The `gh-ost` `--alert` parameter is:
```
--alter="add my_new_column json null after xxx_id"
```

Part of the data is properly migrated, usually the migration fails after the first batch. I created a PR already that solves the problem. I will link this issue in the PR.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.