Can someone please take a look at this SQL?

Hi,

This SQL is based on code I've seen used in many modules here. It is not working for someone using MySQL client library 4.1.7. I've no idea why, but it would appear the @t4 variable or last_insert_id() aspect isn't working.

Since this code is so widely used by Zen Cart modules I hope someone can see what's going wrong..

Thanks in advance for any replies.

SET @t4=0;
SELECT (@t4:=configuration_group_id) as t4 
FROM configuration_group
WHERE configuration_group_title= 'Back In Stock Notifications';
DELETE FROM configuration WHERE configuration_group_id = @t4;
DELETE FROM configuration_group WHERE configuration_group_id = @t4;

INSERT INTO configuration_group VALUES ('', 'Back In Stock Notifications', 'Set Back In Stock Notifications Options', '1', '1');
UPDATE configuration_group SET sort_order = last_insert_id() WHERE configuration_group_id = last_insert_id();

SET @t4=0;
SELECT (@t4:=configuration_group_id) as t4 
FROM configuration_group
WHERE configuration_group_title= 'Back In Stock Notifications';

INSERT INTO `configuration` ( `configuration_id` , `configuration_title` , `configuration_key` , `configuration_value` , `configuration_description` , `configuration_group_id` , `sort_order` , `last_modified` ,  `use_function` , `set_function`, `date_added`) 
VALUES ('', 'Enable/Disable Back In Stock Notification', 'BACK_IN_STOCK_NOTIFICATION_ENABLED', '1', 'If enabled, when a customer comes across a product that is out of stock, the customer will be offered the chance to be notified when it is back in stock<br /><br />0 = off <br />1 = on', @t4, '1', NOW(), NULL, 'zen_cfg_select_option(array(''0'', ''1''), ', NOW());

INSERT INTO `configuration` ( `configuration_id` , `configuration_title` , `configuration_key` , `configuration_value` , `configuration_description` , `configuration_group_id` , `sort_order` , `last_modified` , `date_added`) 
VALUES ('', 'Send Copy of Back In Stock Notification Subscription E-mails To', 'SEND_EXTRA_BACK_IN_STOCK_NOTIFICATION_SUBSCRIPTION_EMAILS_TO', '', 'Send copy of Back In Stock Notification Subscription e-mails to the following email addresses, in this format: Name 1 <email@address1>, Name 2 <email@address2>', @t4, '2', NOW(), NOW()
);

All the best...

Conor

hi,

are you getting any particular error message, or is the sql just failing silently

Hi wilt,

How are you?

Thanks for replying!

wilt:

are you getting any particular error message, or is the sql just failing silently

The value of t4 doesn't seem to get set. Here's the post concerned:

http://www.zen-cart.com/forum/showpost.php?p=605016&postcount=22

I sent details of how to put a manual ID into the SQL for the configuration group ID and that didn't work either so the SQL could be fine then, it could be some other issue.

I definitely saw the same error myself before but it was years ago and I can't remember what the reason was (MySQL version, or something else on the server possibly?).

Since that code comes pretty much from another module I've seen here (and I've seen a few others use the same method of inserting new configuration groups and their associated options), I was hoping maybe someone here had seen the problem frequently enough that they might remember what can cause it (unlike me ;) ).

I'll hopefully get a look at the server concerned (it's bugging me that I don't remember how I resolved this error previously) but if anyone has any ideas I'd love to have my mind set at rest (and get this guy's module up and running! ;) )!

Thanks!

All the best...

Conor

Hi,

This appears to be a bug in Zen Cart itself.

The following SQL works fine in PHPMyAdmin:

INSERT INTO `configuration` ( `configuration_id` , `configuration_title` , `configuration_key` , `configuration_value` , `configuration_description` , `configuration_group_id` , `sort_order` , `last_modified` ,  `use_function` , `set_function`, `date_added`) 
VALUES ('', 'Enable/Disable Back In Stock Notification', 'BACK_IN_STOCK_NOTIFICATION_ENABLED', '1', 'If enabled, when a customer comes across a product that is out of stock, the customer will be offered the chance to be notified when it is back in stock<br /><br />0 = off <br />1 = on', '62', '1', NOW(), NULL, 'zen_cfg_select_option(array(''0'', ''1''), ', NOW());
```But in Zen Cart, using the "Install SQL Patches" functionality, it fails with the error:

> ERROR: Cannot insert configuration_key "" because it already existsClearly the configuration key Zen Cart should be identifying is: BACK_IN_STOCK_NOTIFICATION_ENABLED.

(This behaviour happens whether or not the actual configuration key exists, but the fact Zen Cart is reporting a blank configuration key implies a problem with Zen Cart itself!).

I'll look into what's going wrong in the SQL Patch script when I get a chance but thought I may as well let anyone interested that the SQL is fine, it's a bug in Zen Cart that's interfering with things.

All the best...

Conor

I just copy-and-pasted the SQL you posted above into a fresh clean v1.3.8a site, and it processed without any errors.

So, I'm not convinced that it's a Zen Cart bug.

Hi,

Had a hunch, based on previous problems with certain Zen Cart query handling and checked if the statement had a newline in it.

It did and removing it allows the line to successfully insert the configuration option into the database!

I suppose that's more a "feature" of Zen Cart rather than an outright bug.

I've just taken a good look at the SQLPatch script and see that it is hard coded to expect the configuration_title then configuration_key fields in any "INSERT INTO configuration (" statements.

I guess a lot of us have been basing our SQL configuration group and population statements on someone who used @t4 first but got the format for Zen Cart configuration creations statements wrong! lol, that's what we get for copying!

The statements don't fail when they are all on one line because there is no configuration_key which matches the configuration_title that Zen Cart checks against so duplication checks just result in a plain SQL error.

I'll be updating the SQL for any of Ceon's modules to not use the silly:

INSERT INTO configuration ( configuration_id , configuration_title , configuration_keyformat and instead use the format Zen Cart is expecting:

INSERT INTO `configuration` ( `configuration_title` , `configuration_key`
```..and all on one line of course!

I've a feeling there's quite a number of modules I've seen over the past few years with this code so hopefully these notes can be found by anyone who wants to know what to do to fix things when the following occurs:

Cannot insert configuration_key "" because it already exists

(Something for the forum search index there ;) ).

All the best...

Conor

Hi,

DrByte:

I just copy-and-pasted the SQL you posted above into a fresh clean v1.3.8a site, and it processed without any errors.

So, I'm not convinced that it's a Zen Cart bug.

I guess you probably instinctively took out the newline so it wouldn't have failed for you! ;)

My following post is a lot more enlightening but thanks for taking a look anyway, it's greatly appreciated! :)

All the best...

Conor

While the following is a slightly different topic vs the tip you made above, it's one reason for the way the code is written:

It's worth noting that anytime you're doing an insert into an auto-increment field it's best to leave that field out of the INSERT statement ... or if you choose to include it, be sure to specify NULL instead of just an empty set of quotes ('') ... because MySQL 5 and newer doesn't like mixing strings with numbers (enforcing data types).

conor:

I guess you probably instinctively took out the newline so it wouldn't have failed for you! ;)

My following post is a lot more enlightening but thanks for taking a look anyway, it's greatly appreciated! :)
Actually, I literally copy-and-pasted from the forum. No editing whatsoever. I wanted a pure test. And had no problems with it.

Hi,

DrByte:

Actually, I literally copy-and-pasted from the forum. No editing whatsoever. I wanted a pure test. And had no problems with it.

That's very strange. Copying and pasting it, then submitting it, here on Firefox, on Vista fails every time.

Copying and pasting it, then submitting it, in IE, the exact same string works.

Must be a difference in how they handle newlines when submitting and the result splits the statement onto two lines in Firefox, causing Zen Cart to fail, but keeping it on one in IE.

How very strange.

I guess when I was testing that code before and it didn't fail I must have been using IE.

DrByte:

While the following is a slightly different topic

That's very interesting and definitely good practice. I'll be very glad to see as many people as possible move to MySQL 5 and PHP5, they really do improve designs with their stricter default policies.

All the best..

Conor

My copy-and-paste was from FireFox 2 on forum into Safari for ZC admin, on OSX Leopard.

Hi,

DrByte:

My copy-and-paste was from FireFox 2 on forum into Safari for ZC admin, on OSX Leopard.

Obviously it must differ due to platform/browser (My copy and paste tests were from Firefox into Firefox and Firefox to IE).

Certainly it fails here in Firefox on Vista, and on Gary's system, whatever browser/OS combination he uses!

Given that the code works with one line only that behaviour should be expected and in future all SQL statements for Zen Cart from me will be on the requisite one line! :)

Thanks again for your help.. it's good/interesting to know what was going wrong in the first place!

All the best...

Conor