[SOLVED] Cannot import db into 3.0.8 Maintenance Release that was created by a 3.0.8 store

Quote

Post by mediatechnology2 » Fri Sep 22, 2023 8:13 am

Cannot import database into 3.0.8 Maintenance Release that was created by a 3.0.8 live store.

I've given up on OC 4.0, and in order to update jQuery to pass PCI scans, have installed a test instance of 3.0.8 maintenance release. Calling the test install "OC3M" to differentiate it from the live store.

I have a non-maint-rel 3.0.8 store running live and when I export/backup its dbf and attempt to import it into the 3.0.8 "OC3M" store it fails. No extensions installed, PHP 8, default theme. The live store is running with storage in a private folder above /web. I have not yet moved the storage location for OC3M.

The test store runs in demo, connects to it's own separate db and can export and import its own dbf. But OC3M won't import the live store's db from a local backup copy. I initially found that the live OC3 install had table entries not recognized by OC3-Maint. These may have been left over from its days as OC2. Not exporting those tables allowed OC3M to attempt an import but fails. Error is:

Code: Select all

Internal Server Error
<b>Warning</b>: mysqli::query(): (42S22/1054): Unknown column 'firstname' in 'field list' in <b>/var/www/clients/client14/web50/web/shop309/system/library/db/mysqli.php</b> on line <b>25</b>
Any suggestions?
Last edited by mediatechnology2 on Sun Sep 24, 2023 9:28 am, edited 1 time in total.
mediatechnology2
New member
Posts
29
Joined
Wed Oct 26, 2022 11:24 pm
Top

Re: Cannot import db into 3.0.8 Maintenance Release that was created by a 3.0.8 store

Quote

Post by KrewNoah » Fri Sep 22, 2023 2:55 pm

It appears there’s a mismatch in database schema between your live and test stores, resulting in the “Unknown column 'firstname'” error. To resolve this, compare the database structure of both stores and update the test store's database schema to match the live store's. Ensure all columns, especially 'firstname', are consistent across both databases before importing again. Additionally, double-check if any extensions or modifications in the live store have added extra columns or tables. Lastly, check the compatibility of PHP 8 with OpenCart 3.0.8, as it might cause issues.

Krew Noah

KrewNoah
Newbie
Posts
10
Joined
Sun Sep 03, 2023 3:39 pm
Location - US
Top

Re: Cannot import db into 3.0.8 Maintenance Release that was created by a 3.0.8 store

Quote

Post by mediatechnology2 » Fri Sep 22, 2023 7:38 pm

Thank you! What I had suspected.

I'm having to go through table-by-table exporting working ones from the live store, importing into the test store and repairing those that won't import.

I now have product populated. Prefer not to mark this solved because I may have questions WRT repairing tables with the wrong schema.
mediatechnology2
New member
Posts
29
Joined
Wed Oct 26, 2022 11:24 pm
Top

Re: Cannot import db into 3.0.8 Maintenance Release that was created by a 3.0.8 store

Quote

Post by ADD Creative » Fri Sep 22, 2023 7:50 pm

The database structure hasn't change between 3.0.3.8 and the maintenance version. If you are not bothered about removing the changes made by old versions, modifications or extensions, you could just import the whole database. You would need to do this directly and not using the built in OpenCart backup.

I wouldn't use the built in OpenCart backup anyway, if possible use something like phpMyAdmin.

www.add-creative.co.uk

ADD Creative
Expert Member
Posts
4336
Joined
Sat Jan 14, 2012 1:02 am
Location - United Kingdom
Top

Re: Cannot import db into 3.0.8 Maintenance Release that was created by a 3.0.8 store

Quote

Post by mediatechnology2 » Fri Sep 22, 2023 8:53 pm

Unfortunately the obvious solution didn't work. Importing the entire live database directly, outside of OC and into "OC3M" (test 3.0.8 maint rel), is what I originally tried and could not login as admin. Most other live store elements were also missing on the front end. It looked pretty much like the demo despite it having imported the working live store db.

The bare metal install of 3.08 maint rel (OC3M) has a very different schema than the live store which was originally OC2>OC3X>OC 3.08. Some of the tables include extra columns which prevents import. The error messages tell me which fields and/or table. Attached is an example error message of the live store's db being imported into OC3M. The live store contains a table that OC3M does not recognize and the rest of the db update process then aborts. I've been going table-by-table and importing them one at a time or in small groups and doing this have imported products where I otherwise couldn't.

/system/library/db/mysqli.php are different in 3.0.8 and 3.0.8M. I don't know php well but it looks like they trap errors differently. The commented out line was present in OC3.08 and "mysqli_report(MYSQLI_REPORT_ERROR);" was added at the top in OC3.0.8M.

Code: Select all

		if (!$mysqli->connect_errno) {
			mysqli_report(MYSQLI_REPORT_ERROR);
			$this->connection = $mysqli;
//			$this->connection->report_mode = MYSQLI_REPORT_ERROR;
			$this->connection->set_charset('utf8');
			$this->connection->query("SET SESSION sql_mode = 'NO_ZERO_IN_DATE,NO_ENGINE_SUBSTITUTION'");
		} else {
			throw new \Exception('Error: Could not make a database link using ' . $username . '@' . $hostname . '!');

Attachments

Server_error-OC_Address_Format.jpg

Server_error-OC_Address_Format.jpg (44.44 KiB) Viewed 747 times

mediatechnology2
New member
Posts
29
Joined
Wed Oct 26, 2022 11:24 pm
Top

Re: Cannot import db into 3.0.8 Maintenance Release that was created by a 3.0.8 store

Quote

Post by ADD Creative » Sat Sep 23, 2023 1:01 am

That looks like a bug fix to the error reporting. Depending on your PHP version you could get different results.

The oc_address_format table was added in 4.0.x or https://github.com/opencart/opencart-3 (which isn't 3.0.x at all). There will have lots of changes to the database. Have you previously updated your database to one of these versions?

www.add-creative.co.uk

ADD Creative
Expert Member
Posts
4336
Joined
Sat Jan 14, 2012 1:02 am
Location - United Kingdom
Top

Re: Cannot import db into 3.0.8 Maintenance Release that was created by a 3.0.8 store

Quote

Post by mediatechnology2 » Sat Sep 23, 2023 2:20 am

Background motivation: I originally attempted to run 4.0 in a test environment but never could login as admin. So I resorted to installing "3.0.3.8M" in a test environment. The goal is to update jQuery to pass a PCI scan.

I see oc_address_format in my live "3.0.3.8" store whose db was untouched by the OC4 install. I have the original zip archive but honestly can't tell you the source. Could have been here: https://github.com/opencart/opencart-3 as you suggest. The internal file dates of the zip are 8/27/21 and I installed it around August '22.

So it could be my 3.0.3.8 archive came from github and the answer to your question is likely "yes." Thanks for the info and advice.
mediatechnology2
New member
Posts
29
Joined
Wed Oct 26, 2022 11:24 pm
Top

Re: Cannot import db into 3.0.8 Maintenance Release that was created by a 3.0.8 store

Quote

Post by mediatechnology2 » Sat Sep 23, 2023 2:46 am

Update: I just downloaded the 3.0.3.8 archive here https://www.opencart.com/index.php?rout ... ad/history and compared it to the already-extracted zip I had used from the August '22 installation. A quick comparison using Total Commander shows all 4286 files in /upload to be equal. I suppose I could do a compare content but it appears my archive was from the "official"(?) page and in seeing it again I have a faint memory of visiting it for the original DL. I recall seeing 4.0-something at the time and chose to pass. Prior to that it appears from the files I saved I was running 3.0.2.0.

I count about 170 db tables in 3.0.3.8 and about 135 in 3.0.3.8M. That 170 includes about a half-dozen from an extension but the rest appear to be defaults or left-overs from prior versions. The extension, cleantalk, was uninstalled before I cloned the db for import into 3.0.3.8. It's the only extension I've ever installed.

Update: I have attached pdf files of the list of tables in both versions.
FWIW the 3.0.3.8 live store runs under PHP 8.0.x but blows up running 8.2.
mediatechnology2
New member
Posts
29
Joined
Wed Oct 26, 2022 11:24 pm
Top

Re: Cannot import db into 3.0.8 Maintenance Release that was created by a 3.0.8 store

Quote

Post by mediatechnology2 » Sat Sep 23, 2023 9:31 am

By editing the live store's cloned dbf to make it match the schema of 3.0.3.8M I can populate the store with product, customers etc.

When importing the edited dbf login ability is destroyed.

When I restore the edited sql near the end of the progress bar I receive the following error:

Code: Select all

SyntaxError: Unexpected token '<', "<!DOCTYPE "... is not valid JSON
OK
<!DOCTYPE html>
<html dir="ltr" lang="en">
<head>
<meta charset="UTF-8" />
<title>Administration</title>
<base href="https://REDACTED/shop309/admin/" />
<meta name="viewport" content="width=device-width, initial-scale=1.0, user-scalable=no, minimum-scale=1.0, maximum-scale=1.0" />
<script type="text/javascript" src="view/javascript/jquery/jquery-3.7.0.min.js"></script>
<script type="text/javascript" src="view/javascript/bootstrap/js/bootstrap.min.js"></script>
<link href="view/stylesheet/bootstrap.css" type="text/css" rel="stylesheet" />
<link href="view/javascript/font-awesome/css/font-awesome.min.css" type="text/css" rel="stylesheet" />
<script src="view/javascript/jquery/datetimepicker/moment/moment.min.js" type="text/javascript"></script>
<script src="view/javascript/jquery/datetimepicker/moment/moment-with-locales.min.js" type="text/javascript"></script>
<script src="view/javascript/jquery/datetimepicker/bootstrap-datetimepicker.min.js" type="text/javascript"></script>
<link href="view/javascript/jquery/datetimepicker/bootstrap-datetimepicker.min.css" type="text/css" rel="stylesheet" media="screen" />
<link type="text/css" href="view/stylesheet/stylesheet.css" rel="stylesheet" media="screen" />
<link href="https://REDACTED/shop309/image/catalog/favicon.png" rel="icon" />
<script src="view/javascript/common.js" type="text/javascript"></script>
</head>
mediatechnology2
New member
Posts
29
Joined
Wed Oct 26, 2022 11:24 pm
Top

Re: Cannot import db into 3.0.8 Maintenance Release that was created by a 3.0.8 store

Quote

Post by mediatechnology2 » Sun Sep 24, 2023 5:24 am

Well, good news though I wouldn't mark this as [SOLVED] yet. The doctype error posted previously is not resolved and I have further testing to do including payments - the most important of all. It's been a long week and I need a day off so may not be able to report the final outcome for a few days.

However I can now login as admin, have product and customers in the store and have what appears (so far) to be a configured fully-functional store ready for testing. The problem was the timezone setting to America/Chicago.
I recalled the time zone issue biting me previously and had to set it to UTC to permit admin login. viewtopic.php?t=217805&start=20#p817273

In addition to a bunch of extra tables one of the issues that required editing the dbf was removing an extra column, "approved" and editing the values to prevent a column count error in oc_customer. 3.0.3.8 liked it 3.0.3.8 maint didn't. And I did verify that the original 3.0.3.8 wasn't an imposter - it matched the release on OC's site.

The lingering issue is although I have a db that OC 3.0.3.8 maint likes will it upgrade less painlessly when moving to OC4.0.X.

Stay tuned...
mediatechnology2
New member
Posts
29
Joined
Wed Oct 26, 2022 11:24 pm
Top

Re: Cannot import db into 3.0.8 Maintenance Release that was created by a 3.0.8 store

Quote

Post by mediatechnology2 » Sun Sep 24, 2023 9:28 am

Also found oc_zone missing lots of (over 4000) rows and was able to fix that with phpMyadmin.

I haven't sandboxed and tried checkout yet and now feel compelled to compare both db side-by-side but otherwise I'm ready to put this one to bed and mark it [SOLVED].
mediatechnology2
New member
Posts
29
Joined
Wed Oct 26, 2022 11:24 pm
Top

Re: Cannot import db into 3.0.8 Maintenance Release that was created by a 3.0.8 store

Quote

Post by mediatechnology2 » Sun Sep 24, 2023 9:30 am

KrewNoah wrote: ↑
Fri Sep 22, 2023 2:55 pm
 It appears there’s a mismatch in database schema between your live and test stores, resulting in the “Unknown column 'firstname'” error. To resolve this, compare the database structure of both stores and update the test store's database schema to match the live store's. Ensure all columns, especially 'firstname', are consistent across both databases before importing again. Additionally, double-check if any extensions or modifications in the live store have added extra columns or tables. Lastly, check the compatibility of PHP 8 with OpenCart 3.0.8, as it might cause issues.
Nailed it! Thanks!
mediatechnology2
New member
Posts
29
Joined
Wed Oct 26, 2022 11:24 pm
Top

Re: [SOLVED] Cannot import db into 3.0.8 Maintenance Release that was created by a 3.0.8 store

Quote

Post by mediatechnology2 » Mon Sep 25, 2023 9:06 pm

A couple of other issues to report are

oc_api in the non-maint db 3.0.3.8 live store contained fields "firstname" and "lastname" which required deleting those columns and removing the two extra values

System settings | Options did not have an enabled API user.

I finally have the 3.0.3.8 maintenance release store running live on PHP 8.0 and it passes a PCI compliance scan for jQuery. Marking [SOLVED]
mediatechnology2
New member
Posts
29
Joined
Wed Oct 26, 2022 11:24 pm
Top
Post Reply
Return to “Installation, Upgrade, & Config Support”
Who is online

Users browsing this forum: No registered users and 10 guests