Page 1 of 2

[SOLVED] url_alias table not updated when upgrade to the new seo_url table OC 3.0.2.0 anyone know how to doing manually.

Posted: Wed Jun 20, 2018 5:23 am
by OpenCode
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!

Re: url_alias table not updated when upgrade to the new seo_url table OC 3.0.2.0 anyone know how to doing manually.

Posted: Thu Jun 21, 2018 2:56 am
by IP_CAM
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

Re: url_alias table not updated when upgrade to the new seo_url table OC 3.0.2.0 anyone know how to doing manually.

Posted: Fri Jun 22, 2018 4:47 am
by OpenCode
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

Re: url_alias table not updated when upgrade to the new seo_url table OC 3.0.2.0 anyone know how to doing manually.

Posted: Fri Jun 22, 2018 7:23 am
by kestas
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.

Re: url_alias table not updated when upgrade to the new seo_url table OC 3.0.2.0 anyone know how to doing manually.

Posted: Sat Jun 23, 2018 1:16 am
by OpenCode
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.

Re: url_alias table not updated when upgrade to the new seo_url table OC 3.0.2.0 anyone know how to doing manually.

Posted: Sat Jun 23, 2018 9:59 pm
by kestas
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

Re: url_alias table not updated when upgrade to the new seo_url table OC 3.0.2.0 anyone know how to doing manually.

Posted: Mon Jun 25, 2018 1:25 am
by OpenCode
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

Re: url_alias table not updated when upgrade to the new seo_url table OC 3.0.2.0 anyone know how to doing manually.

Posted: Sun Dec 30, 2018 4:28 am
by mRC
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.

Re: url_alias table not updated when upgrade to the new seo_url table OC 3.0.2.0 anyone know how to doing manually.

Posted: Tue Jan 15, 2019 1:02 am
by como
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.

Re: url_alias table not updated when upgrade to the new seo_url table OC 3.0.2.0 anyone know how to doing manually.

Posted: Wed Jan 01, 2020 3:18 pm
by dracoteam
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 :)

Re: url_alias table not updated when upgrade to the new seo_url table OC 3.0.2.0 anyone know how to doing manually.

Posted: Thu Jan 02, 2020 1:37 pm
by kestas
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... ;)

Re: url_alias table not updated when upgrade to the new seo_url table OC 3.0.2.0 anyone know how to doing manually.

Posted: Fri Feb 21, 2020 3:31 pm
by TG1313
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!!

Re: url_alias table not updated when upgrade to the new seo_url table OC 3.0.2.0 anyone know how to doing manually.

Posted: Fri Feb 21, 2020 8:05 pm
by xxvirusxx
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..

Re: url_alias table not updated when upgrade to the new seo_url table OC 3.0.2.0 anyone know how to doing manually.

Posted: Fri Feb 21, 2020 8:07 pm
by straightlight
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).

Re: url_alias table not updated when upgrade to the new seo_url table OC 3.0.2.0 anyone know how to doing manually.

Posted: Fri Feb 21, 2020 8:54 pm
by xxvirusxx
Yes, possible he added that seo after...and url_alias has all data...

Re: url_alias table not updated when upgrade to the new seo_url table OC 3.0.2.0 anyone know how to doing manually.

Posted: Sat Feb 22, 2020 1:09 am
by TG1313
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...

Re: url_alias table not updated when upgrade to the new seo_url table OC 3.0.2.0 anyone know how to doing manually.

Posted: Sat Feb 22, 2020 1:15 am
by straightlight
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.

Re: url_alias table not updated when upgrade to the new seo_url table OC 3.0.2.0 anyone know how to doing manually.

Posted: Sat Feb 22, 2020 1:24 am
by TG1313
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?

Re: url_alias table not updated when upgrade to the new seo_url table OC 3.0.2.0 anyone know how to doing manually.

Posted: Sat Feb 22, 2020 3:31 am
by straightlight
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.

Re: url_alias table not updated when upgrade to the new seo_url table OC 3.0.2.0 anyone know how to doing manually.

Posted: Sat Feb 22, 2020 4:45 am
by TG1313
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!