MySQL migration failure (and sequence change work around)


#1

I just installed v1.14 on a local Windows machine for development. I ran install.bat, and start.bat. Then, I went to the admin app and performed an xml export all. After that, I switched the data source to localmysql using the standard instructions and method outlined in the documentation. I have successfully done the same in recent releases of vanilla OFBiz. When I did so with Scipio, however, it failed. The initial process worked, and reported success. The database was even successfully seeded with demo data. But, upon browsing to any app (post switch over), runtime errors were encountered.

I then tried changing the order of the procedure. With a fresh clone of the Scipio source, and a cleaned slate on the database, I replaced the enityengine.xml file with the exact one I had used previously (and the exact scripts to prep MySQL). I did this BEFORE running install.bat and start.bat. With this alternate sequence, Scipio worked perfectly running over MySQL.

Note that I also used the default jdbc jar installed by the Scipio ant command initially. Then, I switched to a more recent jar. That did not seem to matter when the problem was present. When I started the process over again, I used to the newer jar from the get go. This was all done with MySQL 5.6 btw.

When I was encountering the runtime errors, the browser was displaying messages regarding “missing templates” (sorry I failed to save the exact text). The server side Java exceptions included sql errors which included the following:

(SQL Exception while executing the following:INSERT INTO SERVER_HIT_BIN (SERVE
HIT_BIN_ID, CONTENT_ID, HIT_TYPE_ID, SERVER_IP_ADDRESS, SERVER_HOST_NAME, BIN
TART_DATE_TIME, BIN_END_DATE_TIME, NUMBER_HITS, TOTAL_TIME_MILLIS, MIN_TIME_MIL
IS, MAX_TIME_MILLIS, LAST_UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP,
REATED_TX_STAMP, INTERNAL_CONTENT_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
, ?, ?, ?, ?) (Cannot add or update a child row: a foreign key constraint fails
(ofbiz.server_hit_bin, CONSTRAINT SERVER_HBIN_TYPE FOREIGN KEY (HIT_TYPE ID) REFERENCES server_hit_type (HIT_TYPE_ID))))

SQL Exception while exec
ting the following:INSERT INTO SERVER_HIT_BIN (SERVER_HIT_BIN_ID, CONTENT_ID, H
T_TYPE_ID, SERVER_IP_ADDRESS, SERVER_HOST_NAME, BIN_START_DATE_TIME, BIN_END_DA
E_TIME, NUMBER_HITS, TOTAL_TIME_MILLIS, MIN_TIME_MILLIS, MAX_TIME_MILLIS, LAST_
PDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP, INTERNAL_
ONTENT_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (Cannot add
r update a child row: a foreign key constraint fails (ofbiz.server_hit_bin,
CONSTRAINT SERVER_HBIN_TYPE FOREIGN KEY (HIT_TYPE_ID) REFERENCES server_hi _type (HIT_TYPE_ID)))

java.sql.SQLIntegrityConstraintViolationException: Cannot add or upda
e a child row: a foreign key constraint fails (ofbiz.server_hit_bin, CONSTR
INT SERVER_HBIN_TYPE FOREIGN KEY (HIT_TYPE_ID) REFERENCES server_hit_type
(HIT_TYPE_ID))

Could not save ServerHitBin:
rg.ofbiz.entity.GenericEntityException: org.ofbiz.entity.GenericEntityException
Error while inserting: [GenericEntity:ServerHitBin][binEndDateTime,2018-06-22
9:44:59.999(java.sql.Timestamp)][binStartDateTime,2018-06-22 09:30:00.0(java.sq
.Timestamp)][contentId,GLOBAL(java.lang.String)][createdStamp,2018-06-22 09:51:
4.516(java.sql.Timestamp)][createdTxStamp,2018-06-22 09:51:54.515(java.sql.Time
tamp)][hitTypeId,REQUEST(java.lang.String)][lastUpdatedStamp,2018-06-22 09:51:5
.516(java.sql.Timestamp)][lastUpdatedTxStamp,2018-06-22 09:51:54.515(java.sql.T
mestamp)][maxTimeMillis,5578(java.lang.Long)][minTimeMillis,1903(java.lang.Long
][numberHits,2(java.lang.Long)][serverHitBinId,10010(java.lang.String)][serverH
stName,ASI-Q3(java.lang.String)][serverIpAddress,192.168.100.118(java.lang.Stri
g)][totalTimeMillis,7481(java.lang.Long)] (SQL Exception while executing the fo
lowing:INSERT INTO SERVER_HIT_BIN (SERVER_HIT_BIN_ID, CONTENT_ID, HIT_TYPE_ID,
ERVER_IP_ADDRESS, SERVER_HOST_NAME, BIN_START_DATE_TIME, BIN_END_DATE_TIME, NUM
ER_HITS, TOTAL_TIME_MILLIS, MIN_TIME_MILLIS, MAX_TIME_MILLIS, LAST_UPDATED_STAM
, LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP, INTERNAL_CONTENT_ID)
ALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (Cannot add or update a
hild row: a foreign key constraint fails (ofbiz.server_hit_bin, CONSTRAINT
SERVER_HBIN_TYPEFOREIGN KEY (HIT_TYPE_ID) REFERENCESserver_hit_type(HI
_TYPE_ID)))) (Error while inserting: [GenericEntity:ServerHitBin][binEndDateTi e,2018-06-22 09:44:59.999(java.sql.Timestamp)][binStartDateTime,2018-06-22 09:3 :00.0(java.sql.Timestamp)][contentId,GLOBAL(java.lang.String)][createdStamp,201 -06-22 09:51:54.516(java.sql.Timestamp)][createdTxStamp,2018-06-22 09:51:54.515 java.sql.Timestamp)][hitTypeId,REQUEST(java.lang.String)][lastUpdatedStamp,2018 06-22 09:51:54.516(java.sql.Timestamp)][lastUpdatedTxStamp,2018-06-22 09:51:54. 15(java.sql.Timestamp)][maxTimeMillis,5578(java.lang.Long)][minTimeMillis,1903( ava.lang.Long)][numberHits,2(java.lang.Long)][serverHitBinId,10010(java.lang.St ing)][serverHostName,ASI-Q3(java.lang.String)][serverIpAddress,192.168.100.118( ava.lang.String)][totalTimeMillis,7481(java.lang.Long)] (SQL Exception while ex cuting the following:INSERT INTO SERVER_HIT_BIN (SERVER_HIT_BIN_ID, CONTENT_ID, HIT_TYPE_ID, SERVER_IP_ADDRESS, SERVER_HOST_NAME, BIN_START_DATE_TIME, BIN_END_ ATE_TIME, NUMBER_HITS, TOTAL_TIME_MILLIS, MIN_TIME_MILLIS, MAX_TIME_MILLIS, LAS _UPDATED_STAMP, LAST_UPDATED_TX_STAMP, CREATED_STAMP, CREATED_TX_STAMP, INTERNA _CONTENT_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) (Cannot ad or update a child row: a foreign key constraint fails (ofbiz.server_hit_bin
, CONSTRAINT SERVER_HBIN_TYPE FOREIGN KEY (HIT_TYPE_ID) REFERENCES server_ it_type (HIT_TYPE_ID)))))


#2

Hello.

The second order you used (set entityengine.xml to mysql before running install.bat) is normally the correct one.

I’m not sure I understand the part before that. In that order you would have been exporting from Derby. So I take it you were trying to import that exported data into MySQL? Using the interface? Most likely that would not work because the interface requires a minimum of seed data (load-seed) to work while your MySQL DB would be empty at that moment. So you would have to import the file instead using “ant load-file -Ddata-file=[path-to-data-file.xml]” after switching the entityengine.xml.

Note however I have not tested this myself recently and there could be some data type issues between Derby and MySQL I’m not aware of (e.g. timestamps). I could also have misunderstood what you wrote. Maybe Paul can comment later since he has used MySQL more recently.

The JDBC jar in the 1.14 package/branch is currently very old (a new release is scheduled soon), you would find newer one in master, though I take it is not the issue.


#3

Hey, Buvinj,

and welcome to the community. So yes, Pascal already said the most important aspects of it, so I will only add a few words to it.

First off, you should make sure that you are basing your work off of our github sources. We do offer the 1.14.3 as installers, but we are only a few days away from the 1.14.4 release and what is on github is really close to the release candidate we use internally.

That being said: Pascal is right, that your issues are probably due to the seed data not being imported. If you are trying to export from OFBiz and migrate over to Scipio ERP, you have to remember that our seed data is a lot different from theirs. So I recommend that you install our seed data first and check with the exported ones that your files do not contain Ids starting with “9000” (like us, ofbiz uses Ids lower than 10000 in their seed data so that the id generator doesn’t clash with the imported seed information). Because of it, there is a chance however that by migrating you are actually overriding our seed data with theirs.

The Server Hit Bin constraints may actually derive from something else, entirely, however. You exported data, sure, but you probably didn’t export the “SeqValueItem” entity.
The reason why i mention it is this: Whenever the system has to create Ids automatically, it will actually increase a value in the SeqValueItem table for this matter. Usually the system increases in steps of 100 and then by 10 for each restart, so that it can use those up internally until it hits the value again. Why is that important? Because it means that when you export and import your data into a new system, the generator will try to generate a new id. It will check against that table what the max value for the entity is, but if the value is too low, it will generate a value and then on storing the value it will notice that the value already exists in the database. The error you see is the result of that.

So check with your old derby files, what the values were and make sure you export those, too. The errors should then be gone.


#4

Thanks a lot guys! This isn’t really an issue for me, but more of a heads up for you. I am playing around with this on a local machine first, where I don’t really care about the data. I am planning to use MySQL from the start in production. So, it makes no difference really, in my case, to change the data source before running the install.bat.

I think I threw a red haring into this discussion by mentioning how I ran the xml export all. I was just clarifying my exact steps, one by one that produced the error. I did that as a test, with the premise being that I was seeing how I’d change data sources in the future potentially after I did care about some production data. I ran this exact sequence in OFBiz and it worked fine.

Basically, my point is that you should test the process of switching data sources like this AFTER the installer was run and the system has been in use. Going over to MySQL, after first seeding / using Derby does not appear to be working now.


#5

I was using the 1.14.3 git branch - not the master. So maybe this is already fixed?


#6

Hey Buvinj,

after thinking about it, i really do think that this may be caused by the SeqValueItem issue i described above. Did you test this?


#7

Though the missing templates are more related towards seed data missing, rather than an autoinc issue…


#8

Sorry, I guess that branch is just named 1.14, so I’m not sure if that’s 1.14.3 vs 1.14.4?


#9

The bulk of the MySQL appeared to be seeded automatically when I switched over from Derby, but maybe there was some data that didn’t get installed? I’m a complete nube with OFbiz, so I can’t claim to know how much of this works internally, and I did not closely inspect the data. I just saw the MySQL database had roughly been populated, so the core process was working.


#10

When I tried the switch I was using the mysql 5.1 jdbc per the Scipio ant helper. I then switched over to the most recent Mysql v8 jdbc. I was using v5.6 server. That may or may not have any baring, but I figured I clarify. (I’m a software developer myself, so normally I’m on the other side of QA asking for all these details!)


#11

Hmm… it is good that you are reporting this, regardless of whether or not there is an actual issue. At the moment, everything hints to how the seed data was loaded, however.

Just a quick question: did you “reinstall” everything after switching from derby to mysql, or did you just import the data you had? Because seed data may not have been installed properly then either…


#12

I did not run an import. I performed the export, but after I switched the datasource, I simply ran start.bat. At which point I could no l could no longer access the gui (to even attempt a user style import). I also did not run install.bat again. It seemed that some magic seeded the database that I couldn’t follow. I assumed there was some event triggered by virtue to using a new datasource.


#13

That explains it. The messages you saw were just the system setting up the database, but seens you did not install the data, it should literally be empty. Since the system needs the data to run, it will throw plenty of errors, so I would say it is pretty likely it is that what you are seeing. Just run the install again (while on the new database) and you should be fine.


#14

As a follow up: the 1.14 branch is the 1.14.3 release with a range of bugfixes, but none of the larger changes we are introducing for 1.14.4.

Like with the installers, I would recommend to just use master at this stage, simply because we are really close to the next release and 1.14.3 is months away by comparison…


#15

(I had to wait a day, because a user can’t reply so many times on first day on this forum)

Ahah! I see. Well that makes sense!

So I understand, what if I were in production in a few years and wanted to change data sources? Or is that not possible? I wouldn’t run an install again there would I? Would I start over with a fresh system / clean slate and then run an import all, rather than trying a direct migration?


#16

It is possible to do that, but you’d run into the same problems you’d run into in an other context: If you want to migrate from one relational database to the next, it is a good idea to plan this properly and perhaps check one of the professional tools for it (Flyway for instance is a good one).