MySQL Insert with Auto-Incrementing Primary Key

[thad_tensentric]

I haven't done MySQL before.  I'm trying and hit a roadblock pretty early.

I'd like to insert new records and have the primary key autogenerated/autoincrementing.

So, I've attached a few screenshots.  The first is a screenshot of the MySQL settings for the table I am trying to write.  The second is the Labview code I threw together that I'm trying to run.

The full error I receive is:

>Database Connection.vi<ERR>ADO Error: 0x80004005
Exception occured in Microsoft OLE DB Provider for ODBC Drivers: [MySQL][ODBC 3.51 Driver][mysqld-5.7.11-log]Field 'techniciansID' doesn't have a default value in NI_Database_API.lvlib:Rec Create - Command.vi->NI_Database_API.lvlib:Cmd Execute.vi->NI_Database_API.lvlib:DB Tools Insert Data.vi->Database Connection.vi

I know this error is stating that the techniciansID does not have a default value and it doesn't want to write because of this...but since it is an auto-incrementing field, it seems like it should work.

Any help would be appreciated.  Thanks, 

Thad

[aputman]

What's your table engine type?  I think this may only be supported in MyISAM without some additional configurations.  This link may help if you are using InnoDB.

http://dev.mysql.com/doc/refman/5.7/en/innodb-auto-increment-handling.html

[mikeporter]

What does the SQL look like that defines the table? Also you are using the DCT so heaven only knows what SQL that toolkit is generating internally - or what is really wrong.

Do you get the error when you drop the all complication that the DCT imposes and use a straight forward INSERT statement?

Mike...

[thad_tensentric]

I've attached a portion of the SQL code that is generated for this table's definition.  I removed the definitions for other tables.

---------------------------------------------------------------------------------------

-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema spinnerDB
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema spinnerDB
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `spinnerDB` DEFAULT CHARACTER SET utf8 ;
USE `spinnerDB` ;

-- -----------------------------------------------------
-- Table `spinnerDB`.`technicians`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `spinnerDB`.`technicians` (
`techniciansID` INT NOT NULL AUTO_INCREMENT,
`technicianName` VARCHAR(45) NOT NULL,
PRIMARY KEY (`techniciansID`),
UNIQUE INDEX `techniciansID_UNIQUE` (`techniciansID` ASC))
ENGINE = InnoDB;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

------------------------------------------------------------------------------------------------------------------------------------

I also tried running the SQL using the 'Execute Query' VI.  Same result.  Here is the full error:

NI_Database_API.lvlib:Conn Execute.vi->Database Connection.vi<ERR>ADO Error: 0x80004005
Exception occured in Microsoft OLE DB Provider for ODBC Drivers: [MySQL][ODBC 3.51 Driver][mysqld-5.7.11-log]Field 'techniciansID' doesn't have a default value in NI_Database_API.lvlib:Conn Execute.vi->Database Connection.vi

[mikeporter]

Why do you have the redundant unique constraint on techniciansID? Primary keys are by definition unique.

Mike...

[thad_tensentric]

I'm new to MySQL, that's why 🙂  I tried it both ways and it doesn't work whether defined as unique or not.

[thad_tensentric]

I actually can't get this to work in MySQL workbench, which I hadn't tried.

[RavensFan]

Okay.  So if you go into MySQL where you define the table, and give the technician's ID a default value, does anything start working?  Give it a default value of 0 and see what happens.

[thad_tensentric · Accepted solution]

Setting to 0 didn't work, but I did figure this out.  For some reason, when I was regenerating my tables in MySQL server and using the "Forward Engineer" feature, the changes weren't propagating.  I had to drop the schema from the database (attached image) and then do a "Forward Engineer".  I was making changes, but the tables and columns still had the original attributes...guess they can't be updated by the script when running.

After doing this and the original settings I had (auto-incrementing primary key), the insert works in MySQL and in Labview. 

Thanks for all the responses.  They got me thinking and checking some things I hadn't before.

- Thad

[mikeporter]

Yes, well this is why I don't use those tools to actually build the database. I want to see the DDL that runs.

Mike...