Migrating Commerce 1 Database. Data truncated for column 'discountType' at row 1

[magnessjo]

Description

Error occurred in one of the migrations. See output:

Database Exception: SQLSTATE[01000]: Warning: 1265 Data truncated for column 'discountType' at row 1
The SQL being executed was: UPDATE craft_commerce_sales SET discountType='byPercent', dateUpdated='2018-03-31 18:09:08' WHERE discountType='percent'
Migration: craft\commerce\migrations\m180217_130000_sale_migration

Steps to reproduce

1. Backup Commerce 1 db via mysqldump
2. Install backup on database and start migration at /admin.

Additional info

• Craft version: Craft Pro 3.0.0-RC17.1
• PHP version: 7.0.19
• Database driver & version: MySQL 5.7.18
• Plugins & versions:

Commerce : dev-develop as 2.0.0-alpha.99

[lukeholder]

Can you please send the original database pre backup to [email protected]

[magnessjo]

@lukeholder,

Database has been sent.

[lukeholder]

@magnessjo did your db upgrade work successfully? The error is a warning.

I was able to upgrade the DB you supplied without error or warning. It may be a mysql version issue.

[magnessjo]

@lukeholder,

Yes, the error is a warning, but the db upgrade did not work. I am stuck on the screen attached. Every option I do leads me back to migration failed screen. I am on MySQL 5.7.18. I would be surprised if it was a mysql issue though. I have already migrated this database once (about a week ago). Didn't have any issues then. Right now I am testing moving the current commerce 1 db to my new commerce 2 site. My workflow is this:

1. Go to production and do a mysqldump of database 'X'.
2. SCP down the db to my local environment.
3. Login to mysql and drop database 'Y'.
4. Create database 'Y'.
5. Refresh data from production (X) and begin migration.

X = Commerce 1 db.
Y = Commerce 2 db.

Note: not an urgent request. I have another backup and have plenty of updates to do.

[lukeholder]

I just pushed a small change. try doing a composer update and try the upgrade again and let me know.

[magnessjo]

@lukeholder,

No, the problem persists.

Note: I did composer clear-cache before doing composer update.

[magnessjo]

@lukeholder,

I doubt it is related based on the specific error, but I since I did the first migration (a week ago) that worked, I have added plugins:

Commerce/Stripe
Commerce/PayPal
Commerce/Omnipay

Solspace/calendar

Mandrill/mandrill.

[lukeholder]

I dont see how those plugin could interfere with this migration.

Did you perhaps change mysql versions between the first time you upgraded and now?

[magnessjo]

@lukeholder,

No I have been on the same mysql version for a few months. I am going to try and do a migration with a different database (staging) and see if something added to my production db is causing the issue.

[magnessjo]

@lukeholder,

I can verify that it is a bug in the migration. I pulled a staging db that is about a week old and didn't have an issue with the migration so I reviewed what changes have occurred in the last week on the production db. The client ran a promotion for a product and have an existing 50% off promotion. I pull the production db to local, removed the promotion and the migration worked fine.

I can list the steps to reproduce if you would like, but it should be as simple (hopefully) as taking the db I sent to craft support and dumping in into a newly created database. Then going to /admin and doing the installer. The database I sent has the promotion in it.

[lukeholder]

it should be as simple (hopefully) as taking the db I sent to craft support and dumping in into a newly created database. Then going to /admin and doing the installer. The database I sent has the promotion in it.

I have done that multiple times on Mysql 5.6 without issue. My guess is its a mysql warning/error level config related to versions.

[magnessjo]

@lukeholder,

I don't plan on going to production anytime soon so it's not a problem for me. Let me know if you want me to test anything for you on 5.7.1 before beta starts on 4/4.

[lukeholder]

Password protected DB dump http://jmp.sh/73VVUrc

[samuelbirch]

I have the same issue :(

but using MySQL 5.5.5-10.2.14-MariaDB

[lukeholder]

@samuelbirch @magnessjo which versions of commerce were you upgrading from?

[lukeholder]

also, is your db version constraint in composer.json for commerce set to ^2.0.0?

[magnessjo]

@lukeholder,

I believe the version was the latest at the time (most likely 1.2.1360). I looked for a db version constraint setting in the vendor composer.json file and the commerce composer.json file and did not see it being set either place.

@samuelbirch,

I was able to work around the migration issue by removing all active promotions before doing the migration. Not an ideal solution but it worked for fine for my client.

[samuelbirch]

@lukeholder

I managed to fix the issue by updating this migration script: m180217_130000_sale_migration.php

Adding this as line before the first update in safeUp:

$this->alterColumn('{{%commerce_sales}}', 'discountType', $this->string());