1054 - Unknown column 'virtuemart_cart_id' ... while log in

[izig]

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

[izig]

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

[jenkinhill]

You should be using VM3.0.4 at least for Joomla 3.4.1   http://dev.virtuemart.net/projects/virtuemart/files

[izig]

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

[izig]

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

[Studio 42]

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):

Code Select Expand

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:

Code Select Expand

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
...

[izig]

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:

Code Select Expand

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:

Code Select Expand

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.

[izig]

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