There is a fatal design flaw in the VirtueMart integration plugin for CBSubs.
I was having problems with new subscribers not getting their VirtueMart discounts. The new users were showing up in VM's Shoppers list, but their shopper groups were still set on "-default-" and not "Discount". After looking at the _virtuemart_userinfos table, and the error messages (when turned on in Joomla!), I have found out why.
The values in the "virtuemart_userinfo_id" field are (sample data):
1
2
4294967295
654824
9
6
174369
While their respective "virtuemart_user_id" values are (sample data):
1
2
3
5
6
9
8
From the size of the registered user-base of the site I am rebuilding, any field in any table with "user" and "id" in the name should not exceed four digits, and as you can see from the sample data, above. . . .
Sample error message:
500 - JDatabaseMySQLi::query: 1062 - Duplicate entry '4294967295' for key 'PRIMARY' SQL=INSERT INTO `j25_virtuemart_userinfos`
(`virtuemart_userinfo_id`, `virtuemart_user_id`, `address_type`, `address_type_name`, `name`, `first_name`, `middle_name`, `last_name`, `created_on`)
VALUES ('a25949590afc82aefbcccc528761c513', 1156, 'BT', '-default-', 'Public User', 'Public', '', 'User', '2013-03-12 19:23:09')
Fortunately, I have upgraded the site to CBSubs 3.0.0 (now I can debug!). The source of the error is at lines 240 and 251 of cbsubs.virtuemart.php (and possibly at lines 205 and 218, I can not answer this as I have not used VM 1.x). Simply delete these lines. As you can see in the output from a MySQL describe command, shown below, manually setting a value to virtuemart_userinfo_id is unnecessary, it is an auto_increment field!
I would like to know why you chose to set the value of the virtuemart_userinfo_id field, when it is unnecessary, and to use an MD5 hash.
Please comment.
MySQL> describe j25_virtuemart_userinfos;
+
+
+
+
+
+
+
| Field | Type | Null | Key | Default | Extra |
+
+
+
+
+
+
+
| virtuemart_userinfo_id | int(1) unsigned | NO | PRI | NULL | auto_increment |
| virtuemart_user_id | int(1) unsigned | NO | | 0 | |
| address_type | char(2) | NO | | | |
| address_type_name | char(32) | NO | | | |
| name | char(64) | YES | | NULL | |
| company | char(64) | YES | | NULL | |
| title | char(32) | YES | | NULL | |
| last_name | char(48) | YES | | NULL | |
| first_name | char(48) | YES | | NULL | |
| middle_name | char(48) | YES | | NULL | |
| phone_1 | char(32) | YES | | NULL | |
| phone_2 | char(32) | YES | | NULL | |
| fax | char(32) | YES | | NULL | |
| address_1 | char(64) | NO | | | |
| address_2 | char(64) | YES | | NULL | |
| city | char(64) | NO | | | |
| virtuemart_state_id | smallint(1) unsigned | NO | | 0 | |
| virtuemart_country_id | smallint(1) unsigned | NO | | 0 | |
| zip | char(32) | NO | | | |
| agreed | tinyint(1) | NO | | 0 | |
| created_on | datetime | NO | | 0000-00-00 00:00:00 | |
| created_by | int(11) | NO | | 0 | |
| modified_on | datetime | NO | | 0000-00-00 00:00:00 | |
| modified_by | int(11) | NO | | 0 | |
| locked_on | datetime | NO | | 0000-00-00 00:00:00 | |
| locked_by | int(11) | NO | | 0 | |
+
+
+
+
+
+
+
26 rows in set (0.01 sec)
The offending code:
237 } elseif ( $this->getVirtuemartVersion() == 2 ) {
238 $query = 'INSERT INTO ' . $_CB_database->NameQuote( '#__virtuemart_userinfos' )
239 . "\n ("
240 . $_CB_database->NameQuote( 'virtuemart_userinfo_id' ) /* This line. */
241 . ', ' . $_CB_database->NameQuote( 'virtuemart_user_id' )
242 . ', ' . $_CB_database->NameQuote( 'address_type' )
243 . ', ' . $_CB_database->NameQuote( 'address_type_name' )
244 . ', ' . $_CB_database->NameQuote( 'name' )
245 . ', ' . $_CB_database->NameQuote( 'first_name' )
246 . ', ' . $_CB_database->NameQuote( 'middle_name' )
247 . ', ' . $_CB_database->NameQuote( 'last_name' )
248 . ', ' . $_CB_database->NameQuote( 'created_on' )
249 . ')'
250 . "\n VALUES ("
251 . $_CB_database->Quote( md5( uniqid( $user->id ) ) ) /* This line, too. */
252 . ', ' . (int) $user->id
253 . ', ' . $_CB_database->Quote( $address_type )
254 . ', ' . $_CB_database->Quote( $address_name )
256 . ', ' . $_CB_database->Quote( $user->name )
257 . ', ' . $_CB_database->Quote( $user->firstname )
258 . ', ' . $_CB_database->Quote( $user->middlename )
259 . ', ' . $_CB_database->Quote( $user->lastname )
260 . ', ' . $_CB_database->Quote( date( 'Y-m-d H:i:s' ) )
261 . ')';
Joomla! stack:
Joomla! 2.5.16
VirtueMart 2.0.24c
Community Builder 1.9.1
CB Paid Subscriptions 3.0.0
CBSubs VirtueMart 1.2.3
krileon
