Series Import: DataIntegrityViolationException for a long URL

[php-coder]

We have mismatched length for the following fields:

• series_import_requests.url varchar(767)
• series_sales.transaction_url varchar(255)
  And when we import a series with a URL longer than 255 characters, we get DataIntegrityViolationException.

---

Steps to reproduce:

1. run wiremock server: mvn wiremock:run -DkeepRunning=true
2. login as admin
3. go to import series page (http://localhost:8080/series/import/request)
4. import a page with a URL longer than 255 characters:
   http://127.0.0.1:8888/series/import/request-logic/new-seller.html?test=0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789
5. on the next page, submit a form with provided data

Actual result:

• "Internal server error" page is shown
• the server outputs the error:

Caused by: org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO series_sales ( series_id , date , seller_id , transaction_url , first_price , first_currency , second_price , second_currency , buyer_id , created_at , created_by ) VALUES ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? )]; Value too long for column "TRANSACTION_URL VARCHAR(255)": "'http://127.0.0.1:8888/series/import/request-logic/new-seller.html?test=01234567890123456789012345678901234567890123456789012345... (261)"; SQL statement:
INSERT INTO series_sales ( series_id , date , seller_id , transaction_url , first_price , first_currency , second_price , second_currency , buyer_id , created_at , created_by ) VALUES ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) [22001-197]; nested exception is org.h2.jdbc.JdbcSQLException: Value too long for column "TRANSACTION_URL VARCHAR(255)": "'http://127.0.0.1:8888/series/import/request-logic/new-seller.html?test=01234567890123456789012345678901234567890123456789012345... (261)"; SQL statement:
INSERT INTO series_sales ( series_id , date , seller_id , transaction_url , first_price , first_currency , second_price , second_currency , buyer_id , created_at , created_by ) VALUES ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? ) [22001-197]

Expected result:

• Page should be imported successfully

[php-coder]

@mihnayan Hi, how about this one? The only thing that is required here is to provide a database migration (liquibase). Assign the issue to self if you ready to work on it.

Ask me about steps to reproduce the issue if you need.

[mihnayan]

@php-coder Do I understand correctly that this bug is reproduced on the "Image URL" field of the "/series/add" page? Have you got example of the long url?

[php-coder]

@mihnayan Thank you for the question! This is a little different case. I've updated the issue description and included the steps to reproduce the bug locally.

If you need a full picture, here is the overview:

• we have a feature where it's possible to import information about series from external site. User submits a URL (that is saved to series_import_requests.url), the site downloads it and parses its content. The parsed values are persisted to the database and later user can review them, fill required values and create a series.
• at some point, this feature was enhanced: it became possible to also import information about a seller. So, now it also possible to extract information like "Slava is selling this series by that URL for 200 RUB". The URL in that case is the page from where we import the series from. This URL is saved into series_sales.transaction_url field.

The bug appears because series_import_requests.url field has greater length than series_sales.transaction_url and hence we can't save a long URL into that field.

This issue is about setting the length of the series_sales.transaction_url field to the length of the series_import_requests.url.

[mukeshk]

There was a pull which is not complete.
#1013
Need to set SeriesSalesDb.SeriesSales.TRANSACTION_URL_LENGTH = 767

append to > version/0.4.xml
<include file="0.4/2019-07-20--series_sales_transaction_url_length.xml" relativeToChangelogFile="true"/>

This should work.
<modifyDataType tableName="series_sales" columnName="transaction_url" newDataType="VARCHAR(767)" />
<renameColumn columnDataType="VARCHAR(767)" newColumnName="transaction_url" oldColumnName="transaction_url" remarks="Must match to the series_import_requests.url field" tableName="series_sales"/>

the "columnDataType" is required in case of MySQL
https://www.liquibase.org/documentation/changes/rename_column.html

Is it all required ? or am I missing something?

[php-coder]

@mukeshk Yes, you're right. I can't merge this PR because it's incomplete and has conflicts, so we need to re-write it.

Keep in mind, that a migration now should be placed into src/main/resources/liquibase/version/0.4.1 directory. Also, we need to create src/main/resources/liquibase/version/0.4.1.xml and include it from within src/main/resources/liquibase/changelog.xml)