The site is on Joomla 3.3.6
This morning I tried to install VM 3.0.4 This failed with the error message: 1060 Duplicate column name 'product_price_publish_up' SQL=ALTER TABLE `j30_virtuemart_product_prices` CHANGE COLUMN `product_price_vdate` `product_price_publish_up` DATETIME NULL DEFAULT NULL AFTER `product_currency`
The system configuration however shows now 3.0.4
have u tried a "Install or if necessary update tables" in tools?
I just did. It reports the same Error.
you need to look at your j30_virtuemart_product_prices table carefully.. has it already got a column product_price_publish_up?
(I assume so)
because it is trying to change column name product_price_vdate to product_price_publish_up
but very strange as product_price_vdate is a column in a VM1.1. prices table...
it comes from the public function update() in administrator\components\com_virtuemart\install\script.virtuemart.php
$this->alterTable('#__virtuemart_product_prices',
array(
'product_price_vdate' => '`product_price_publish_up` DATETIME NULL DEFAULT NULL AFTER `product_currency`',
'product_price_edate' => '`product_price_publish_down` DATETIME NULL DEFAULT NULL AFTER `product_price_publish_up`'
));
but runs only if you fail $this->checkIfUpdate()
this is a check for
SELECT count(id) AS idCount FROM `#__virtuemart_adminmenuentries
have you got this table?
Thank you GJC,
Furthermore it checks if the product_price_vdate is there. So I would just delete the old product_price_vdate column in the table.
In the database I find the following VM tables:
j25_virtuemart_product_prices, including the columns product_price_publish_up and product_price_vdate, value NULL
j30_virtuemart_product_prices, including the columns product_price_publish_up and product_price_vdate, value NULL
jos30_virtuemart_product_prices, empty
jos_virtuemart_product_prices, empty
Boy - what a mess - should only be one table j30_virtuemart_product_prices with no product_price_vdate field (if j30 is your live prefix your working with)
Cleaned up the mess by deleting the old tables and deleted the product_price_vdate column.
Still the same 1060 error: 1060 Duplicate column name 'product_price_publish_up' SQL=ALTER TABLE `j30_virtuemart_product_prices` CHANGE COLUMN `product_price_vdate` `product_price_publish_up` DATETIME NULL DEFAULT NULL AFTER `product_currency`
because you've already got both columns in j30_virtuemart_product_prices - you shouldn't !
remove the one without data in it - if both have data remove one or other till it works -- backup obviously
There is only 1 column with product_prices_publish_up, see attached printscreen.
then back to this post
http://forum.virtuemart.net/index.php?topic=128001.msg439362#msg439362
No, I have not. See the attachment for what I have.
so that table is empty? I really wonder how your DB got in this mess?
try importing the attached
MySQL reports: #1062 - Duplicate entry '1' for key 'PRIMARY'
would you provide more info or I will loose interest -- if that is so then there are entries already in there!
I feel sorry if I have upset you. I have not lost any interest, in fact I am a real beginner so I fully rely on this Forum.
As indicated, I imported your sql file but received the indicated error (see attachment).
And now I have no idea how to proceed as I just miss the knowledge to draw conclusions from the error message.
Thank you.
final try - are there any entries already in that table??
I am very reluctant to answer your question, again I am not an expert at all.
I have attached to my best understanding the answer (LEEG means EMPTY). So 8 entries are in use, 4 are empty.
not the structure - the verkennung tab.....
Got it.
See the attached 2 screenprints (let me know if you need more detailed images)
so there are entries there .. then out of ideas - you need someone to take a look at this...
Out of ideas....I was that already from the moment I presented the problem on this forum :(
Anyhow, thanks for all the efforts you made. Hopefully someone else still has a good idea ;).
Ok, here are my stupid ideas :-)
In the file j30_virtuemart_adminmenuentries.sql which GJC sent you, line 52 begins with INSERT INTO `j30_virtuemart_adminmenuentries...
I assume that if you change that to REPLACE INTO `j30_virtuemart_adminmenuentries..., you might not get the error message 1062 duplicate enty '1' for key 'primary' when importing it.
If that doesn't work, what happens if you simply install the latestest VM 3.0.4 over your existing one?
Another idea is to select the database in phpmyadmin, scroll down to the bottom and check all tables and click 'with selected' > Repair table (and afterwards perhaps also check table and optimize table)
Last but not least you can try to go to your VirtueMart Tools & Migration view and click 'Install or if necessary update tables'
Your first proposal to change INSERT INTO into REPLACE INTO worked perfectly. No error. So not a stupid idea at all.
Than I tried to install VM 3.0.4 but still the following error: 1060 Duplicate column name 'product_price_publish_up' SQL=ALTER TABLE `j30_virtuemart_product_prices` CHANGE COLUMN `product_price_vdate` `product_price_publish_up` DATETIME NULL DEFAULT NULL AFTER `product_currency` Remember that I deleted the table product_price_vdate as was suggested earlier.
I followed also your suggestions to repair the tables. The Tools & Migration gave the same error and PHPMyadmin did not detect any error.
Your other suggestion to install the latest VM 3.0.4 over the existing one is unclear to me. I thought that I tried to do this. Apparently you have a different approach in mind. Can you be more specific?
Quote from: bestcons on February 03, 2015, 20:01:03 PM
Your other suggestion to install the latest VM 3.0.4 over the existing one is unclear to me. I thought that I tried to do this. Apparently you have a different approach in mind. Can you be more specific?
My thought was that the installer script might skip the table change after you did the small manual clean up. Apparently the idea didn't work.
You don't actually have a duplicate column name product_price_publish_up in your table, do you?
No, I do'nt.
In the attachment you can see what I've got.
Well, I definitely don't have the column product_price_edate in my database table. That's another one from VM1 and early VM2 versions. I would delete that one, too.
Unfortunately still the same error: 1060 Duplicate column name 'product_price_publish_up' SQL=ALTER TABLE `j30_virtuemart_product_prices` CHANGE COLUMN `product_price_vdate` `product_price_publish_up` DATETIME NULL DEFAULT NULL AFTER `product_currency`
Attached the new table.
Hello :)
I may be out on thin ice here, but is this really correct SQL?
QuoteSQL=ALTER TABLE `j30_virtuemart_product_prices` CHANGE COLUMN `product_price_vdate` `product_price_publish_up` DATETIME NULL DEFAULT NULL AFTER `product_currency`
Should it not be?
QuoteSQL=ALTER TABLE `j30_virtuemart_product_prices` MODIFY COLUMN `product_price_vdate` `product_price_publish_up` DATETIME NULL DEFAULT NULL AFTER `product_currency`
or maybe ?
QuoteSQL=ALTER TABLE `j30_virtuemart_product_prices` ALTER COLUMN `product_price_vdate` `product_price_publish_up` DATETIME NULL DEFAULT NULL AFTER `product_currency`
and why is column `product_price_vdate` missing ?
I am absolutely no SQL expert so please forgive me if I am wrong about this, but I can´t find any documentation that describes CHANGE COLUMN.
best regards
Jörgen @ Kreativ Fotografi
Hi Jörgen,
I am not an expert at all, it's just that I copy the error message I get, trying to upgrade VM 3.0.2 to 3.0.4. Both moderators advised me to delete the column `product_price_vdate`
Hello bestcons
I missed that you deleted that column, sorry. But I interpretet the command that both of the two columns should only be changing their type, not replacing each other. Anyway I can´t find any documentation for the command CHANGE COLUMN to make a correct assessment. Just guessing here. :-\
regards
Jörgen @ Kreativ Fotografi
Bestcons, what is the problem, the thing is actually not hard.
There is a table which got broken for whatever reason. Delete all columns of your price table, like vdate and edate. Then use the table updater again and done. The tableupdater should do it for you.
Hi Milbo, with phpmyadmin I could not delete all columns, rather had to drop the table. So I left the first column and deleted the rest.
Then I used the Virtuemart updater (install table or if necessary update) but still get the same error message: 1060 Duplicate column name 'product_price_publish_up' SQL=ALTER TABLE `j30_virtuemart_product_prices` CHANGE COLUMN `product_price_vdate` `product_price_publish_up` DATETIME NULL DEFAULT NULL AFTER `product_currency`
Should I drop the virtuemart_product_prices table?
I wonder if you work on the right db, or on the tables with the correct PREFIX
Hi Milbo,
Your guess was absolutely right. Did not change the story, but after deleting almost completely the product_prices table I now could use the updater to correct this. And... the update was succesful. I'm now on VM 3.0.4
Thanks guys :) :).
Is' nt the # missing?
Quote from: Jörgen on February 04, 2015, 08:03:30 AM
Hello :)
I may be out on thin ice here, but is this really correct SQL?
QuoteSQL=ALTER TABLE `j30_virtuemart_product_prices` CHANGE COLUMN `product_price_vdate` `product_price_publish_up` DATETIME NULL DEFAULT NULL AFTER `product_currency`
Should it not be?
QuoteSQL=ALTER TABLE `j30_virtuemart_product_prices` MODIFY COLUMN `product_price_vdate` `product_price_publish_up` DATETIME NULL DEFAULT NULL AFTER `product_currency`
or maybe ?
QuoteSQL=ALTER TABLE `j30_virtuemart_product_prices` ALTER COLUMN `product_price_vdate` `product_price_publish_up` DATETIME NULL DEFAULT NULL AFTER `product_currency`
and why is column `product_price_vdate` missing ?
I am absolutely no SQL expert so please forgive me if I am wrong about this, but I can´t find any documentation that describes CHANGE COLUMN.
best regards
Jörgen @ Kreativ Fotografi
I got this in the frontend:
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OR `virtuemart_shoppergroup_id` IS NULL OR `virtuemart_shoppergroup_id`="0") AN' at line 1 SQL=SELECT * FROM `u27qw_virtuemart_product_prices` WHERE `virtuemart_product_id` = "44" AND ( OR `virtuemart_shoppergroup_id` IS NULL OR `virtuemart_shoppergroup_id`="0") AND ( (`product_price_publish_up` IS NULL OR `product_price_publish_up` = "0000-00-00 00:00:00" OR `product_price_publish_up` <= "2015-02-17 14:54:48" ) AND (`product_price_publish_down` IS NULL OR `product_price_publish_down` = "0000-00-00 00:00:00" OR product_price_publish_down >= "2015-02-17 14:54:48" ) ) ORDER BY `product_price` DESC
It is possible to have the 3.0.3 version and try that first?
is there a working 3.0.5 to upgrade?
Please HELP!!!!
It seems to me that this is a different problem. My problem has been fully solved.
I suggest that you open a new lead, to ask for support.
gmcfuerte, your problem is solved by updating to vm3.0.6. But you should check your shoppergroups, maybe you miss a default one.