Hi,
I have a site running with VirtueMart 3.0.6.2. on Joomla 3.4.1. It was upgraded from the previous latest Joomla and VirtueMart (version 2 of both).
OS: Debian 3.2.65-1+deb7u2 x86_64
PHP version: 5.4.36-0+deb7u3
Apache: 2.2.22-13+deb7u4
The problem I have is that when a user is logged into the shop, he can't add nothing to the cart, simply can't press the add button.
If I'm not logged in, I can add items to the cart but once I'll try to log in, I'll get the following error:
1054 - Unknown column 'virtuemart_cart_id' in 'field list' SQL=SELECT `virtuemart_cart_id` FROM `koeu0_virtuemart_carts` WHERE `virtuemart_user_id` = "167"
That's the site address (it's in Hebrew so not sure it will help):
http://www.yonishobby.co.il
Thanks,
Izi
And one more error I found, when pressing the "Sort By", I get a 404 with the following error:
1054 - Unknown column 'c.category_name' in 'order clause' SQL=SELECT SQL_CALC_FOUND_ROWS p.`virtuemart_product_id` FROM `koeu0_virtuemart_products` as p LEFT JOIN `koeu0_virtuemart_product_shoppergroups` as ps ON p.`virtuemart_product_id` = `ps`.`virtuemart_product_id` LEFT JOIN `koeu0_virtuemart_product_categories` as pc ON p.`virtuemart_product_id` = `pc`.`virtuemart_product_id` WHERE ( `pc`.`virtuemart_category_id` = 22 AND ( `ps`.`virtuemart_shoppergroup_id`= "1" OR `ps`.`virtuemart_shoppergroup_id` IS NULL ) AND p.`published`="1" ) group by p.`virtuemart_product_id` ORDER BY c.category_name ASC LIMIT 0, 12
After that, I can't access any category until I clean the browser cache.
I can get the main page and the products, but any category will return the above error.
Thanks
You should be using VM3.0.4 at least for Joomla 3.4.1 http://dev.virtuemart.net/projects/virtuemart/files
Upgraded to 3.0.6.4, during the process of installing "com_virtuemart.3.0.6.4.zip", I got the following messages:
Warning
Incorrect table definition; there can be only one auto column and it must be defined as a key SQL=ALTER TABLE `koeu0_virtuemart_carts` ADD virtuemart_cart_id INT(1) UNSIGNED NOT NULL AUTO_INCREMENT FIRST
Error
Error installing component
In the admin panel it shows 3.0.6.4 but I still have the same issue as described above.
Also updated the PHP version to 5.4.39-0+deb7u2
Examining the database, looks like there is no column named "virtuemart_cart_id" in the "virtuemart_carts" table.
Looks like the installer couldn't add it.
Edit:
The "virtuemart_user_id" column is marked as AUTO_INCREMENT, that's probably the reason the installation script couldn't add the "virtuemart_cart_id" with AUTO_INCREMENT to the table.
Waiting for your advise whether to manually remove the AUTO_INCREMENT from the "virtuemart_user_id" and run the installer again.
Thanks
So with the help of phpMyAdmin, was able to run the installation with some progress and got the following:
Notice
alterTable CHANGE koeu0_virtuemart_carts.virtuemart_cart_id : ALTER TABLE `koeu0_virtuemart_carts` CHANGE COLUMN `virtuemart_cart_id` `virtuemart_cart_id` INT(1) UNSIGNED NOT NULL AUTO_INCREMENT FIRST
alterTable CHANGE koeu0_virtuemart_carts.virtuemart_user_id : ALTER TABLE `koeu0_virtuemart_carts` CHANGE COLUMN `virtuemart_user_id` `virtuemart_user_id` INT(1) UNSIGNED NOT NULL AFTER `virtuemart_cart_id`
alterTable CHANGE koeu0_virtuemart_order_userinfos.agreed : ALTER TABLE `koeu0_virtuemart_order_userinfos` CHANGE COLUMN `agreed` `agreed` TINYINT(1) NOT NULL DEFAULT '0' AFTER `email`
alterTable CHANGE koeu0_virtuemart_order_userinfos.tos : ALTER TABLE `koeu0_virtuemart_order_userinfos` CHANGE COLUMN `tos` `tos` TINYINT(1) NOT NULL DEFAULT '0' AFTER `agreed`
alterTable CHANGE koeu0_virtuemart_order_userinfos.customer_note : ALTER TABLE `koeu0_virtuemart_order_userinfos` CHANGE COLUMN `customer_note` `customer_note` VARCHAR(2500) NOT NULL DEFAULT '' AFTER `tos`
Message
Table updated: Tablename koeu0_virtuemart_carts dropped: 0 altered: 2 added: 0
Table updated: Tablename koeu0_virtuemart_order_userinfos dropped: 0 altered: 3 added: 0
Warning
Incorrect table definition; there can be only one auto column and it must be defined as a key SQL=ALTER TABLE `koeu0_virtuemart_ratings` DROP INDEX `virtuemart_product_id`
Played a little more, run again the installer:
Warning
Incorrect table definition; there can be only one auto column and it must be defined as a key SQL=ALTER TABLE `koeu0_virtuemart_ratings` CHANGE COLUMN `virtuemart_rating_id` `virtuemart_rating_id` INT(1) UNSIGNED NOT NULL AUTO_INCREMENT FIRST
And now I'm stocked in this stage:
Notice
alterTable CHANGE koeu0_virtuemart_userinfos.agreed : ALTER TABLE `koeu0_virtuemart_userinfos` CHANGE COLUMN `agreed` `agreed` TINYINT(1) NOT NULL DEFAULT '0' AFTER `zip`
alterTable CHANGE koeu0_virtuemart_userinfos.tos : ALTER TABLE `koeu0_virtuemart_userinfos` CHANGE COLUMN `tos` `tos` TINYINT(1) NOT NULL DEFAULT '0' AFTER `agreed`
alterTable CHANGE koeu0_virtuemart_userinfos.customer_note : ALTER TABLE `koeu0_virtuemart_userinfos` CHANGE COLUMN `customer_note` `customer_note` VARCHAR(2500) NOT NULL DEFAULT '' AFTER `tos`
Message
Table updated: Tablename koeu0_virtuemart_userinfos dropped: 0 altered: 3 added: 0
Warning
Incorrect table definition; there can be only one auto column and it must be defined as a key SQL=ALTER TABLE `koeu0_virtuemart_vmusers` DROP INDEX `u_virtuemart_user_id`
Couldn't find yet a way to fix this one. Will appreciate any assistance.
As this is an upgrade from a store with information, I can't just drop the table and let the script re create it.
Thanks
If you can :
remove the PREFIX_virtuemart_carts table that had some problems.
run in mysql (change PREFIX by the name of your table prefix):
CREATE TABLE IF NOT EXISTS `PREFIX_virtuemart_carts` (
`virtuemart_cart_id` INT(1) UNSIGNED NOT NULL AUTO_INCREMENT,
`virtuemart_user_id` INT(1) UNSIGNED NOT NULL,
`virtuemart_vendor_id` INT(1) UNSIGNED NOT NULL,
`cartData` VARBINARY(50000),
`created_on` datetime NOT NULL default '0000-00-00 00:00:00',
`created_by` int(1) NOT NULL DEFAULT '0',
`modified_on` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified_by` int(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`virtuemart_cart_id`),
KEY `virtuemart_vendor_id` (`virtuemart_vendor_id`),
KEY `virtuemart_user_id` (`virtuemart_user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 COMMENT='Used to store the cart' AUTO_INCREMENT=1 ;
For the other tables
alter mean change type of data
simply go in the table having failure and change it manually
for eg:
alterTable CHANGE koeu0_virtuemart_carts.virtuemart_cart_id : ALTER TABLE `koeu0_virtuemart_carts` CHANGE COLUMN `virtuemart_cart_id` `virtuemart_cart_id` INT(1)
mean the column have to be an int as type
...
Thanks. I was able to fix the virtuemart_carts table.
The problem I'm facing at the moment is the virtuemart_vmusers table. This is the relevant code from the installer:
CREATE TABLE IF NOT EXISTS `#__virtuemart_vmusers` (
`virtuemart_user_id` INT(1) UNSIGNED NOT NULL AUTO_INCREMENT,
`virtuemart_vendor_id` smallint(1) UNSIGNED NOT NULL DEFAULT '0',
`user_is_vendor` tinyint(1) NOT NULL DEFAULT '0',
`customer_number` char(32),
`virtuemart_paymentmethod_id` mediumint(1) UNSIGNED,
`virtuemart_shipmentmethod_id` mediumint(1) UNSIGNED,
`agreed` tinyint(1) NOT NULL DEFAULT '0',
`created_on` datetime NOT NULL default '0000-00-00 00:00:00',
`created_by` int(1) NOT NULL DEFAULT '0',
`modified_on` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified_by` int(1) NOT NULL DEFAULT '0',
`locked_on` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`locked_by` int(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`virtuemart_user_id`),
KEY `virtuemart_vendor_id` (`virtuemart_vendor_id`),
UNIQUE KEY `u_virtuemart_user_id` (`virtuemart_user_id`,`virtuemart_vendor_id`),
KEY `user_is_vendor` (`user_is_vendor`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 COMMENT='Holds the unique user data' ;
That's the error from the VM installation:
Warning
Incorrect table definition; there can be only one auto column and it must be defined as a key SQL=ALTER TABLE `koeu0_virtuemart_vmusers` CHANGE COLUMN `virtuemart_user_id` `virtuemart_user_id` INT(1) UNSIGNED NOT NULL AUTO_INCREMENT FIRST
And when trying to run it manually from the mysql command line:
mysql> ALTER TABLE `kobt0_virtuemart_vmusers` CHANGE `virtuemart_user_id` `virtuemart_user_id` INT(1) UNSIGNED NOT NULL AUTO_INCREMENT;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
I can't delete this table as it contains information. I deleted the Index virtuemart_user_id.
Thanks again.
Thanks for all the assistance.
In the end, I deleted the indexes of each table that got an error during the installation and re-created new indexes according to what I found in the sql installation script.
Ran the VM installation process and done without any error.
I think that the installer should have a way to detect and existing Indexes, remove them if possible and add them again.
Again, thanks for the assistance.
Izi