Post by Kalena » Fri Aug 14, 2020 11:44 pm

Upgrading to oc-3.0.3.6 and get this message:

Error Code(0): Error: Duplicate column name 'username'
Error No: 1060
ALTER TABLE `oc_api` CHANGE `name` `username` VARCHAR(64) NOT NULL in /home/rnpgkvks/public_html/system/library/db/mysqli.php on line 40

Don´t know what to do or what database it refers to

New member

Posts

Joined
Thu Jan 29, 2015 5:44 pm

Post by khnaz35 » Sat Aug 15, 2020 12:12 am

Kalena wrote:
Fri Aug 14, 2020 11:44 pm
Upgrading to oc-3.0.3.6 and get this message:

Error Code(0): Error: Duplicate column name 'username'
Error No: 1060
ALTER TABLE `oc_api` CHANGE `name` `username` VARCHAR(64) NOT NULL in /home/rnpgkvks/public_html/system/library/db/mysqli.php on line 40

Don´t know what to do or what database it refers to
The Error said itself, you have a duplicate column name 'username' in your db.
double confirm with your database that why you have it twice, which extension is creating it.
try to disable all the install extension one by one and see if it is been resolved.
it is always advisable to backup your db first and the website before making any changes.

Urgent Questions shoot here: khnaz35@gmail.com
Enjoy nature ;) :) :-*


User avatar
Active Member

Posts

Joined
Mon Aug 27, 2018 11:30 pm
Location - Malaysia

Post by weborder » Fri Sep 25, 2020 11:51 am

Hello,

I disabled all extensions and Journal theme and I am getting

Code: Select all

Error Code(0): Error: Duplicate column name 'username'
Error No: 1060
ALTER TABLE `oc_api` CHANGE `name` `username` VARCHAR(64) NOT NULL in /home/XXXXX/public_html/v3/system/library/db/mysqli.php on line 40
what can be the problem ?

thank you in advance for your help.

Newbie

Posts

Joined
Wed Apr 22, 2020 12:54 pm

Post by xxvirusxx » Fri Sep 25, 2020 4:07 pm

You made upgrade from 2.x version right?
You have the answer already...
weborder wrote:
Fri Sep 25, 2020 11:51 am

Code: Select all

Duplicate column name 'username' in oc_api`
Upgrading process is not made 100% correctly.

For 2.x
Beefore upgrade, delete from database oc_api table.

To fix you can delete oc_api table and recreate from opencart sql database or you can delete one of username field from oc_api.

OC 2.3.0.2 | OC 3.0.3.6 | Extensions converted by me | Buy me a beer


User avatar
Expert Member

Posts

Joined
Tue Jul 17, 2012 10:35 pm
Location - România

Post by letxobnav » Fri Sep 25, 2020 4:16 pm

looks like you are running the wrong upgrade or are running it twice.

Crystal Light Centrum Taiwan
Extensions: MailQueue | SUKHR | VBoces

“Data security is paramount at [...], and we are committed to protecting the privacy of anyone who is associated with our [...]. We’ve made a lot of improvements and will continue to make them.”
When you know your life savings are gone.


User avatar
Expert Member

Posts

Joined
Fri Aug 18, 2017 4:35 pm
Location - Taiwan

Post by xxvirusxx » Fri Sep 25, 2020 7:32 pm

Is not running twice...it is a bug.

Example:
When you try to upgrade from 2.3.0.2 to 3.0.3.6.. username is added in database because is in opencart.sql table.
Then SQL command not work because is already an username.

Code: Select all

		$query = $this->db->query("SELECT * FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = '" . DB_DATABASE . "' AND TABLE_NAME = '" . DB_PREFIX . "api' AND COLUMN_NAME = 'name'");
		
		if ($query->num_rows) {
			$this->db->query("ALTER TABLE `" . DB_PREFIX . "api` CHANGE `name` `username` VARCHAR(64) NOT NULL");
		}
Fix1 : Changed alter table with delete command, because username is taken already from opencart.sql.
Fix2: Added delete command before ALTER to delete username then name will be renamed in username and data will be kept (api id, key...)

So in the end upgrade process (upgrade controller) should ignore entire opencart.sql database and use only commands from upgrade models.

Example

Code: Select all

 
 if $clean_install
 	$database_file = opencart.sql
 else
 	$dabase_file = execute all commands from upgrade models and ignore opencart.sql
endif

OC 2.3.0.2 | OC 3.0.3.6 | Extensions converted by me | Buy me a beer


User avatar
Expert Member

Posts

Joined
Tue Jul 17, 2012 10:35 pm
Location - România
Who is online

Users browsing this forum: No registered users and 9 guests