Post by OpenCode » Wed Jun 20, 2018 5:23 am

Hi Guys!
maybe is just me but when we upgrade our OC from 1.5.6.4 to the new 3.0.2.0 everything was upgraded almost perfect,
Products, categories, information, and all data was updated the only exception is the products (formerly know as url_alias (SEO Keyword),
this table was not converted to the new seo_ url, all the information still in the old table the new table `seo_url` is empty.
do anyone knows on how to do it manually by running a SQL query directly via phpadmin.
or other option besides doing it manually - any ideas?

Thank you in advance guys!

New member

Posts

Joined
Fri Jun 07, 2013 9:31 am

Post by IP_CAM » Thu Jun 21, 2018 2:56 am

everything was upgraded almost perfect
Well, you possibly feel this way :D
But since you did not mention, in detail, on how you 'upgraded', and what you
upgraded, nobody probably really believes, that your thing will ever work again as
planned. But so, it's of no use, to bother about, just to start another discussion on
this, this place is already full of it.

But please, don't take it personal, it's just an information, since most Upgraders
forget, that 99.99 percent of all OC Users don't use a strictly default Version OC,
but one, in some ways Extension-enhanced and/or modified. With the consequence,
that NO upgrade can work, as planned, in such setups. But that's not OpenCart's
fault, it's, at best, just poor or non-existing Communication, adding to a lot of
problemes, by a lot of OC Users, eager, to mock up their existing System,
with incompatible Parts ... :crazy: like uploading Win10 Drivers and DLL's onto an XP!

Good Luck! ;)
Ernie

My Github OC Site: https://github.com/IP-CAM
5'200 + FREE OC Extensions, on the World's largest private Github OC Repository Archive Site.


User avatar
Legendary Member

Posts

Joined
Tue Mar 04, 2014 1:37 am
Location - Switzerland

Post by OpenCode » Fri Jun 22, 2018 4:47 am

IP_CAM wrote:
Thu Jun 21, 2018 2:56 am
everything was upgraded almost perfect
Well, you possibly feel this way :D
But since you did not mention, in detail, on how you 'upgraded', and what you
upgraded, nobody probably really believes, that your thing will ever work again as
planned. But so, it's of no use, to bother about, just to start another discussion on
this, this place is already full of it.

But please, don't take it personal, it's just an information, since most Upgraders
forget, that 99.99 percent of all OC Users don't use a strictly default Version OC,
but one, in some ways Extension-enhanced and/or modified. With the consequence,
that NO upgrade can work, as planned, in such setups. But that's not OpenCart's
fault, it's, at best, just poor or non-existing Communication, adding to a lot of
problemes, by a lot of OC Users, eager, to mock up their existing System,
with incompatible Parts ... :crazy: like uploading Win10 Drivers and DLL's onto an XP!

Good Luck! ;)
Ernie
Upgraded by following the instructions on the file name upgrade.txt that can be found here https://github.com/opencart/opencart/bl ... pgrade.txt

upgraded from OpenCart V1.5.6.4 x TO 3.0.2.0, follow the instructions in the upgrade.txt
but I think they forgot transforming the url_alias table to the new seo_url table, I think DUMPING the old table AND CREATE the new TABLE should
happen in the upgrade don't you think?

the old table

Code: Select all

CREATE TABLE IF NOT EXISTS `url_alias` (
  `url_alias_id` int(11) NOT NULL,
  `query` varchar(255) NOT NULL,
  `keyword` varchar(255) NOT NULL
) ENGINE=MyISAM AUTO_INCREMENT=DEFAULT CHARSET=utf8;
to the new table

Code: Select all

CREATE TABLE IF NOT EXISTS `seo_url` (
  `seo_url_id` int(11) NOT NULL,
  `store_id` int(11) NOT NULL,
  `language_id` int(11) NOT NULL,
  `query` varchar(255) NOT NULL,
  `keyword` varchar(255) NOT NULL
) ENGINE=MyISAM AUTO_INCREMENT=DEFAULT CHARSET=utf8;
and since the old URL table still in the DB my question is how can I get the information out and move to the new SEO_URL table, adding the new names fields.
I when and check the upgrade script made by Q to upgrade the v2 and this table is not reformated for the upgrade either, so my guessing is either nobody has noticed or is just left out, I think products URL are important as well the rest of the data upgraded, don't you think?

anyways I'm with Opencart since V.0 ( when qphoria chromecart I think that's the name close his Idea and move it over here to OC) all throughout the changes, I also see that Daniel and other moderator has lost control of the forum, as well deleting old good tips for older versions, so they can sell their module base on the free ideas of others.

anyways all I need is the query to runed directly in phpadmin to get the data updated or drop the new table and re-created with the data from url_alias that's old.

Thank you for taking the time of reading my post-Ernie.

PS: I'm Apple user but I got your point

New member

Posts

Joined
Fri Jun 07, 2013 9:31 am

Post by kestas » Fri Jun 22, 2018 7:23 am

You can try do it in your phpMyAdmin.

IMPORTANT!!! Before you try, please make backup all of your tables.
If you have both oc_seo_url and oc_url_alias (old and new) tables and new table is empty DROP new table.
Open your DB in phpMyAdmin click tab SQL

Make sure what prefix of your tables if not "oc" change it in code below.
first add:

Code: Select all

ALTER TABLE `oc_url_alias` RENAME TO `oc_seo_url`
click GO

next click tab SQL add:

Code: Select all

ALTER TABLE `oc_seo_url`
CHANGE COLUMN  `url_alias_id`  seo_url_id int(11) NOT NULL
click GO

next click tab SQL add:

Code: Select all

ALTER TABLE `oc_seo_url`
ADD COLUMN  `store_id` int(11) NOT NULL
AFTER       `seo_url_id`
click GO

next click tab SQL add:

Code: Select all

ALTER TABLE `oc_seo_url`
ADD COLUMN  `language_id` int(11) NOT NULL
AFTER       `store_id`
click GO
IMPORTANT!!! Before you try, please make backup all of your tables.

Custom OpenCart modules and solutions. You can write PM with additional questions... Extensions you can find here


Active Member

Posts

Joined
Tue Oct 12, 2010 2:23 am

Post by OpenCode » Sat Jun 23, 2018 1:16 am

kestas wrote:
Fri Jun 22, 2018 7:23 am
You can try do it in your phpMyAdmin.

IMPORTANT!!! Before you try, please make backup all of your tables.
If you have both oc_seo_url and oc_url_alias (old and new) tables and new table is empty DROP new table.
Open your DB in phpMyAdmin click tab SQL

Make sure what prefix of your tables if not "oc" change it in code below.
first add:

Code: Select all

ALTER TABLE `oc_url_alias` RENAME TO `oc_seo_url`
click GO

next click tab SQL add:

Code: Select all

ALTER TABLE `oc_seo_url`
CHANGE COLUMN  `url_alias_id`  seo_url_id int(11) NOT NULL
click GO

next click tab SQL add:

Code: Select all

ALTER TABLE `oc_seo_url`
ADD COLUMN  `store_id` int(11) NOT NULL
AFTER       `seo_url_id`
click GO

next click tab SQL add:

Code: Select all

ALTER TABLE `oc_seo_url`
ADD COLUMN  `language_id` int(11) NOT NULL
AFTER       `store_id`
click GO
IMPORTANT!!! Before you try, please make backup all of your tables.
@ kestas, Thank you very Much! that work like a charm saves us a lot of time...

the only thing we did after running all the SQL queries, was run the following since the language_id has a value of 1.

Code: Select all

UPDATE `oc_seo_url` SET `language_id` = '1';
or can also be done (I guess) as follow if you only have one language in your store.

Code: Select all

ALTER TABLE `oc_seo_url`
ADD COLUMN  `language_id` int(11) NOT NULL DEFAULT '1'
AFTER       `store_id`
if anyone has the same problem we were having, kestas solution works like charm just make sure, "IMPORTANT!!! Before you try, please make backup all of your tables." as KESTAS suggested.

The help and solution kestas has post here it remaimgs me old times OpenCart's when the v1.4 was out everbody pitch in for the "bugs" and solutions.

I think help still available here if everyone using the forum to find the solution for their issues with their OC store, pitch in with donations for the developer, that offer the solution to the problem.

kestas, I like to buy you a cup of coffee. please let me know how to do that.

Thank You for your help.

New member

Posts

Joined
Fri Jun 07, 2013 9:31 am

Post by kestas » Sat Jun 23, 2018 9:59 pm

OpenCode wrote:
Sat Jun 23, 2018 1:16 am
kestas, I like to buy you a cup of coffee. please let me know how to do that.
Nice. I'm very happy if it helped for you.
Regarding the coffee I can't.... refuse your suggestion to drink a cup of coffee.
If you still wish to make me more happy you can make it via paypal: kestas@vigintos.com :crazy: ::)

Cheers

Custom OpenCart modules and solutions. You can write PM with additional questions... Extensions you can find here


Active Member

Posts

Joined
Tue Oct 12, 2010 2:23 am

Post by OpenCode » Mon Jun 25, 2018 1:25 am

I just did
To kestas,
For your cup of coffee.
For helping me on the DB solution
From OpenCode.
Thank you
I hope OC Forum moderator don't delete it can help others.

Thank You

New member

Posts

Joined
Fri Jun 07, 2013 9:31 am

Post by mRC » Sun Dec 30, 2018 4:28 am

Just to say thanks for this, I have managed to upgrade from Version 2.3.0.2 to Version 3.0.2.0 but I did come across one problem with the above.

I had to make a slight change to the column "seo_url_id" and enable AUTO_INCREMENT.

mRC
Active Member

Posts

Joined
Wed Nov 12, 2014 12:43 am

Post by como » Tue Jan 15, 2019 1:02 am

Here I like to publish my more general and simple solutin.
You should run just one MySQL line:

Code: Select all

INSERT INTO `oc_seo_url` (`language_id`, `query`, `keyword`) SELECT 1 AS `language_id`, `query`, `keyword` FROM `oc_url_alias`
Above
1 AS `language_id`
suppose you have language with Id - 1.
If you have more languages, run this query several times, replasing '1' with respective language Ids.

Hope this helps all who upgrades to Opencart 3.
Last edited by straightlight on Fri Feb 21, 2020 7:23 pm, edited 1 time in total.
Reason: Added code tags.

Dynamic Downloads - make downloadable files alive - pdf, zip etc. Electronic tickets, serial numbers, personalised to given order and product
Shipping Labels Advanced - fully customizable approach to easy print labels
Ads provider to other sites - provide ad boxes with source from the e-shop (ad server) in other yours or partner sites (ad client)


User avatar
Newbie

Posts

Joined
Mon Dec 19, 2011 4:53 pm

Post by dracoteam » Wed Jan 01, 2020 3:18 pm

I had the same problem, and the solution of Kestas worked for me, thanks to share. I payed to your paypal account 3 € for a real coffe :-)

I didn´t try the last solution of user como, but thanks for helping :)

Newbie

Posts

Joined
Sat Oct 20, 2012 4:37 pm

Post by kestas » Thu Jan 02, 2020 1:37 pm

dracoteam wrote:
Wed Jan 01, 2020 3:18 pm
I had the same problem, and the solution of Kestas worked for me, thanks to share. I payed to your paypal account 3 € for a real coffe :-)

I didn´t try the last solution of user como, but thanks for helping :)
Thank you... ;)

Custom OpenCart modules and solutions. You can write PM with additional questions... Extensions you can find here


Active Member

Posts

Joined
Tue Oct 12, 2010 2:23 am

Post by TG1313 » Fri Feb 21, 2020 3:31 pm

I made the changes suggested but it did NOT work for me. Please help - what did i do wrong:

when i export table opu_seo_url it seems correct:
. . . . .

Code: Select all

CREATE TABLE `opu_seo_url` (
  `seo_url_id` int(11) NOT NULL,
  `store_id` int(11) NOT NULL,
  `language_id` int(11) NOT NULL,
  `query` varchar(255) NOT NULL,
  `keyword` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

--
-- Dumping data for table `opu_seo_url`
--

INSERT INTO `opu_seo_url` (`seo_url_id`, `store_id`, `language_id`, `query`, `keyword`) VALUES
. . . 
(1690, 0, 0, 'information_id=3', 'linencasa-privacy-policy'),
. . . .

But my Privacy page (and every single other) still has a "generated" URL instead of allias:

https://www.linencasa.com/index.php?rou ... ation_id=3



My Store and language tables are empty. Just FYI.

Thank you!!
Last edited by straightlight on Fri Feb 21, 2020 7:25 pm, edited 1 time in total.
Reason: Added code tags and renamed privicy for privacy.

Newbie

Posts

Joined
Fri Feb 21, 2020 1:12 pm

Post by xxvirusxx » Fri Feb 21, 2020 8:05 pm

Run commands posted by @kestas because old table with content url_alias will be renamed and converted to seo_url.
And right now seo_url is empty..

Upgrade Service | OC 2.3.0.2 PHP 8 | My Custom OC 3.0.3.8 | Buy me a beer


User avatar
Expert Member

Posts

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

Post by straightlight » Fri Feb 21, 2020 8:07 pm

xxvirusxx wrote:
Fri Feb 21, 2020 8:05 pm
Run commands posted by @kestas because old table with content url_alias will be renamed and converted to seo_url.
And right now seo_url is empty..
According to the query above, it's not empty. There's one value in it (it ain't much though).

Dedication and passion goes to those who are able to push and merge a project.

Regards,
Straightlight
Programmer / Opencart Tester


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by xxvirusxx » Fri Feb 21, 2020 8:54 pm

Yes, possible he added that seo after...and url_alias has all data...

Upgrade Service | OC 2.3.0.2 PHP 8 | My Custom OC 3.0.3.8 | Buy me a beer


User avatar
Expert Member

Posts

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

Post by TG1313 » Sat Feb 22, 2020 1:09 am

Sorry, I might have wasn't very clear....
I did run all commands exactly as @kestas suggested.

Initially had opu_seo_url and opu_url_alias (old and new). New was empty. Old had all the data.
I dropped the "new" one.
Renamed the "old" one. Added additional columns.

All statements ran successfully.

But OpenCart still does not pick up the alias URLs...

What else i can do / check? Please help...

Newbie

Posts

Joined
Fri Feb 21, 2020 1:12 pm

Post by straightlight » Sat Feb 22, 2020 1:15 am

But OpenCart still does not pick up the alias URLs...
That is because Opencart v3.x releases now looks for the seo_url table rather than the url_alias which means even though you decide to rename the table itself on the database without renaming the lookups from the OC models, it will still gather the new name versus the old name.

Dedication and passion goes to those who are able to push and merge a project.

Regards,
Straightlight
Programmer / Opencart Tester


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by TG1313 » Sat Feb 22, 2020 1:24 am

I am so sorry, i copied from @kestas post and now i see the 2 tables got reversed - which one is new and which one is old.
But then all his SQL commands are correct.

opu_seo_url - is a NEW one. I have it with additional columns (store and language) and with all the data.
opu_url_alias - is an OLD one. I don't have it anymore because I renamed it to opu_seo_url.

Is there any other information from my side that can help with troubleshooting?

Newbie

Posts

Joined
Fri Feb 21, 2020 1:12 pm

Post by straightlight » Sat Feb 22, 2020 3:31 am

By simply renaming the old table to your new table (or by copying the data via your new table) should not impact any losses unless you had an important mount of data where your browser may have expired the activity due to the data being too large to complete its process. This may vary between servers where settings may need to be adjusted by your host.

However, if you don't have a huge amount of data in your seo_url table, you should be able to retrieve those data accordingly unless the store_id and the language_id fields do not match your languages and store IDs you may have originally from the store. Ensure that all rows do match from each-other in order to see the results as expected in the OC admin. These queries can be launched easily from your PHPMyAdmin.

Dedication and passion goes to those who are able to push and merge a project.

Regards,
Straightlight
Programmer / Opencart Tester


Legendary Member

Posts

Joined
Mon Nov 14, 2011 11:38 pm
Location - Canada, ON

Post by TG1313 » Sat Feb 22, 2020 4:45 am

Thank you so much for being patient with me.
My seo_url data is only 186 records.
I carefully examined all tables again and found that my Language table had 1 record (for english) with id = 1
When I ran the statement:

Code: Select all

UPDATE `opu_seo_url` SET `language_id` = '1';
if fixed my issue and now i see SEO Tabs for products and other pages populated correctly.

One quick question though - should i have any records in Store table?
Because it is empty. see attached.

Thank you!

Attachments

store_table.JPG

store_table.JPG (24.11 KiB) Viewed 4894 times

Last edited by straightlight on Sat Feb 22, 2020 5:08 am, edited 1 time in total.
Reason: Added code tags.

Newbie

Posts

Joined
Fri Feb 21, 2020 1:12 pm
Who is online

Users browsing this forum: No registered users and 115 guests