CODEWITHSUNDEEP
mysqlsql
Fabio
Fabio

Reputation: 237

Error message for my JOIN Table. Error 1366

Good evening to everyone,

sorry I am new with SQL.

I do have two tables in my DB. The first one it’s ok, no problem, but when I insert a record into the second table, which is a JOIN to the first one, I receive the following error:

SQL query: Edit Edit

INSERT INTO  `clihelp`.`commandExample` (

`commandExampleId` ,
`_code` ,
`_cliCommandId` ,
`example` ,
`created` ,
`updated`
)
VALUES (
'',  'WI',  '00010',  'The following command is the same command as the Get-Service -Name Schedule

gsv -n Schedule', 
CURRENT_TIMESTAMP , 
CURRENT_TIMESTAMP
)
MySQL said: Documentation

#1366 - Incorrect integer value: '' for column 'commandExampleId' at row 1

This is my first table:

CREATE TABLE `cliCommand` (
  `cliCommandId` mediumint(5) unsigned zerofill NOT NULL AUTO_INCREMENT COMMENT 'Autoincrement for each code group',
  `code` varchar(255) NOT NULL COMMENT 'Command unique code shared with users to enable search using this code.',
  `os` varchar(255) DEFAULT NULL COMMENT 'Operating Systems this command works with',
  `tags` text COMMENT 'tags for the command',
  `title` text NOT NULL COMMENT 'command short description/title to be displayed in search result listing along with command code and os',
  `script` text COMMENT 'Help/Manual for the command or complete script',
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`code`,`cliCommandId`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Keeps all cli commands with meta data & description'

This is my second table, the once I am having issue:

CREATE TABLE `commandExample` (
  `commandExampleId` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Autoincrement key for internal purposes',
  `_code` varchar(255) NOT NULL,
  `_cliCommandId` int(11) DEFAULT NULL,
  `example` text COMMENT 'an example associated with this command, there could be multiple examples associated with a command, each as a new record in this table',
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`commandExampleId`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COMMENT='Keeps examples, if any, for a command to be displayed'

Do you have any idea how to fix it?

Thank you so much in advance for your help.

Upvotes: 0

Views: 59

Answers (2)

Dekel
Dekel

Reputation: 62556

The value of the commandExampleId column should not be empty string. It should be NULL:

INSERT INTO  `clihelp`.`commandExample` (
    `commandExampleId` ,
    `_code` ,
    `_cliCommandId` ,
    `example` ,
    `created` ,
    `updated`
)
VALUES (
    NULL,
    'WI',
    '00010',
    'The following command is the same command as the Get-Service -Name Schedule gsv -n Schedule', 
    CURRENT_TIMESTAMP , 
    CURRENT_TIMESTAMP
)

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521168

cliCommandId was defined an integer column, yet you are trying to insert empty string. This is the source of the error. Since it is an autoincrement column, you probably want MySQL to handle assigning the value. In this case, just leave out the cliCommandId entirely in your INSERT statement. MySQL should then automatically assign a value for you.

INSERT INTO clihelp.commandExample (
    `_code`,
    `_cliCommandId`,
    `example`,
    `created`,
    `updated`
)
VALUES (
    'WI',
    '00010',
    'The following command is the same command as the Get-Service -Name Schedule gsv -n Schedule',
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP
)

Upvotes: 1

Related Questions