Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Erin Card
Starting Member
6 Posts |
 Posted - 2012-01-18 : 12:40:37
|
I am working on developing a ZenCart for my work, but am having some pretty serious issues when it comes to SQL INSERT queries and blank, aka '', values. I am running MAMP locally and can install a configuration just fine, such as this one:INSERT INTO configuration_group VALUES ('', 'Manufacturers All Config', 'Manufacturers All Config', '1', '1'); But when I try to install it on my server, i get an error "1366 Incorrect integer value: '' for column 'configuration_group_id' at row 1." The '' as the first value SHOULD default to the incremental value in the table, correct? This happens whenever there is a '' trying to go into a numerical column. If it is for a text-based column, it enters '', whether there's a default value of '' or something else and I am not given the error.I have talked with my server administrator, and he is convinced there is nothing wrong and has told me to just fix my code. ugh. the code works fine in MAMP and i'm guessing in just about every other installation on every other server as i shouldn't have the same problem with ever ZenModule I work with, as well as the custom queries I've written myself. Has anyone else run into this before? I'm convinced it's a server (or possibly database) configuration problem but I can't for the life of me figure out what.  I have the phpinfo(); printouts for both the working server and my local MAMP if that will clarify anything. Let me know if I need to attach/send/etc them. |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2012-01-18 : 12:46:25
|
Hello Erin Card,It is difficult to say as one cannot determine what is the structure of configuration_group table from the information provided. Does the table contain an identity column? Perhaps your insert is trying to assign an explicit value to an identity column? I can only guess. Perhaps you can modify your insert statement with explicit columns and it will help reveal the issue. Something like:INSERT INTO configuration_group (col1, col2, col3, etc...) VALUES (val1, val2, val3, etc...) HTH.EDIT: sniped... :) |
 |
|
|
Erin Card
Starting Member
6 Posts |
Posted - 2012-01-18 : 13:15:14
|
This particular INSERT query is not something i've written, but there is a value for each of the columns (there are only 5 in this table). If I rewrite it (which I'm annoyed at having to do all the time, now), it will go as thus:INSERT INTO configuration_group (configuration_group_id, configuration_group_title, configuration_group_description, sort_order, visible) VALUES ('', 'Manufacturers All Config', 'Manufacturers All Config', '1', '1');and that doesn't work on the server, but does in MAMP, just checked. Rewriting to remove the configuration_group_id works on both servers. INSERT INTO configuration_group (configuration_group_title, configuration_group_description, sort_order, visible) VALUES ('Manufacturers All Config', 'Manufacturers All Config', '1', '1');configuration_group DDL (i think i understand you correctly when you say DDL)column, type, collation, attributes, null, default, extraconfiguration_group_id, int(11), '', '', no, none, auto_incrementconfiguration_group_title, varchar(64), latin1_general_ci, '', no, '', ''configuration_group_description, varchar(255), latin1_general_ci, '', no, '', ''sort_order, int(5), '', '', yes, NULL, ''visible, int(1), '', '', yes, 1this isn't the only table i'm having issues with - it's my entire database. whether it's a coupon table, product information, etc., '' values throw errors whenever there is an int type. I'm REALLY hoping there is a fix for this and don't have to resign to recoding every time a field or value has the potential to be left blank, especially when i don't have to on my local setup. |
|
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2012-01-18 : 13:19:36
|
| Unless I am mistaken, it appears that configuration_group_id is an identity (auto increment) field and as such cannot be assigned a value in an insert statement.HTH. |
|
|
|
Erin Card
Starting Member
6 Posts |
Posted - 2012-01-18 : 13:25:22
|
quote:
Originally posted by ehorn
Unless I am mistaken, it appears that configuration_group_id is an identity (auto increment) field and as such cannot be assigned a value in an insert statement.HTH.
Makes sense. Until you consider the statement works on my MAMP server with the empty ( '' ) value. When '' is entered in lieu of 1 or 'yes', it's not actually assigning a value, am I correct? |
|
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2012-01-18 : 13:28:20
|
quote:
Originally posted by Erin Card
quote:
Originally posted by ehorn
Unless I am mistaken, it appears that configuration_group_id is an identity (auto increment) field and as such cannot be assigned a value in an insert statement.HTH.
Makes sense. Until you consider the statement works on my MAMP server with the empty ( '' ) value. When '' is entered in lieu of 1 or 'yes', it's not actually assigning a value, am I correct?
I cannot say for certain. But I would have to assume that MAMP is not setup as auto increment or has some different settings than the other DB's (i.e. IDENTITY_INSERT is ON in dev environment).HTH. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Erin Card
Starting Member
6 Posts |
Posted - 2012-01-18 : 13:49:57
|
quote:
Originally posted by X002548
ummmmSince what you are doing is NOT a best practice, you SHOULD rewrite your INSERTs to have the column list match your input valuesWhat happens if they alter the table and add a column?
That's fine. there are only 5 lines of code to copy/insert to fix THAT particular file (I did not write the INSERT codes for that, i'm more picky and would have put each column in, initially). I can fix this instance, as I've had to do with 20 other files that should work as-is. quote:
I cannot say for certain. But I would have to assume that MAMP is not setup as auto increment or has some different settings than the other DB's (i.e. IDENTITY_INSERT is ON in dev environment).
The database was exported from MAMP to the server. The two table structures are exactly the same and executing INSERT INTO configuration_group VALUES ('', 'Manufacturers All Config', 'Manufacturers All Config', '1', '1'); in MAMP correctly auto-increments. :/I mentioned the coupon table earlier, which is set up by ZenCart. Installed fine on my local server, but both an upload of the files to the server AND a fresh install gave me the same 1366 Incorrect integer value. I have not been able to find a resolution of this in the ZenCart forums. Results of "1366 Incorrect integer value" pop up in threads every so often haven't found a solved one, yet. :/ |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Erin Card
Starting Member
6 Posts |
Posted - 2012-01-18 : 14:04:16
|
| Yes? Would SQL queries work on it if it is not?Client API version: mysqlnd 5.0.8-dev - 20102224 - $Revision: 310735 $ |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Erin Card
Starting Member
6 Posts |
Posted - 2012-01-18 : 14:14:09
|
| Ahh, I misread. I added a word to read "an SQL server." I manage the databases using phpmyadmin. Am I in an incorrect forum area? |
|
|
|
|
|
|
|
|
Default Value Errors?