Problems with using start transactions

[michaelh613]

MySQL server version  5.0.51a

raw MySQL statement in question

 

INSERT INTO profiles(card_id,productCode,customerName,email,externalID,accountNo)
VALUES('0','$productCode','$customerName','$email','$externalID','$accountNo')

 

errors that MySQL returns to the client

Error number is 0

Error description is

 

the table structure & column indexes of the relevant tables

'profiles', 'CREATE TABLE `profiles` (

  `id` int(11) NOT NULL auto_increment,

  `card_id` int(11) NOT NULL,

  `profileID` varchar(12) collate latin1_general_ci NOT NULL,

  `productCode` varchar(5) collate latin1_general_ci NOT NULL,

  `createDate` date NOT NULL,

  `startDate` date NOT NULL,

  `nextPaymentDate` date NOT NULL,

  `frequency` enum('WEEK','MONT','YEAR') collate latin1_general_ci NOT NULL,

  `amount` decimal(6,2) NOT NULL,

  `status` enum('ACTIVE','DEACTIVE') collate latin1_general_ci NOT NULL default 'ACTIVE',

  `customerName` varchar(150) collate latin1_general_ci NOT NULL,

  `email` varchar(150) collate latin1_general_ci NOT NULL,

  `externalID` int(11) NOT NULL,

  `accountNo` varchar(40) collate latin1_general_ci NOT NULL,

  `schedule` time NOT NULL,

  `temp` tinyint(2) NOT NULL,

  PRIMARY KEY  (`id`),

  UNIQUE KEY `prof_id` (`profileID`),

  KEY `card_id` (`card_id`),

  KEY `FK_profiles` (`productCode`),

  KEY `nextPaymentDate` (`nextPaymentDate`),

  CONSTRAINT `profiles_ibfk_1` FOREIGN KEY (`productCode`) REFERENCES `products` (`Code`)

) ENGINE=InnoDB AUTO_INCREMENT=39988 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci'

 

Now when I run the query directly in the PHP MyAdmin I get this error

 

#1364 - Field 'profileID' doesn't have a default value

 

Seems simple as profileID has a not null value.  But it should get created later in my code

 

function addProfile($card,$productCode,$customerName,$email,$externalID=0,$accountNo=0) {

	$number = $card['number']; 
	$cvv = $card['cvv']; 
	$name_on_card = $card['name_on_card']; 
	$exp_date=$card['exp_date']; 
	$type = $card['type'];

	echo "number is $number<br>";
	echo "cvv is $cvv<br>";
	echo "name on care is $name_on_card<br>";
	echo "exp date is $exp_date<br>";
	echo "type is $type<br>";			


	global $db;
	$db->query("START TRANSACTION");
	$sql1 = "INSERT INTO profiles(card_id,productCode,customerName,email,externalID,accountNo)
	VALUES('0','$productCode','$customerName','$email','$externalID','$accountNo')";
	echo "sql query is  $sql1 <br>";

	$r1 = $db->query($sql1);			 
	$prof_id = $db->last_id;



	$sql2 = "INSERT INTO ccards(profile_id,card_type,card_number,card_cvv,name_on_card,exp_date)
	VALUES ($prof_id','$type','$number','$cvv','$name_on_card','$exp_date')";
	echo "2nd query is $sql2 <br>";
	$r2 = $db->query($sql2);
	$card_id = $db->last_id;
	$sql3 = "UPDATE profiles SET card_id='$card_id' WHERE id='$prof_id'";
	echo "third query is $sql3";
	$r3 = $db->query($sql3);

	$ret = new Message();
	//die("R1: $r1 $sql1 R2: $r2 $sql2 R3: $r3");
	if ($r1 && $r2 && $r3 && $db->affected) {
		$db->query("COMMIT");
		$sql = "SELECT profileID FROM profiles WHERE id=$prof_id";
		$r = $db->results($sql);
		$profileID = $r[0]['profileID'];
		$ret->result = 0;
		$ret->text = "Success.";
		$ret->profileID = $profileID;
	} else {
		$db->query("ROLLBACK");
		$ret->result = 10;
		$ret->text = "Error. Couldn't create the profile";
		$ret->profileID = 0;
	}
	return $ret;
}	

 

and the code for function query is

function query($sql) {
	$start = $this->getmicrotime();
	$q = @mysql_query($sql,$this->mysql_link) or die("Error number is ".mysql_errno()."<br> Error description is ".mysql_error()."<br>Query is  " .$sql."<br>"); ;
	$end = $this->getmicrotime();
	$this->qlist[] = $sql;
	if ($q) {
		$this->nrquery += 1;
		$this->last_query_time = $end - $start;
		$this->total_time += $this->last_query_time;
		$this->affected = mysql_affected_rows($this->mysql_link);
		$this->last_id = mysql_insert_id($this->mysql_link);
		return true;
	}
	else return  false;
}

 

 

This trigger exists in the system

DROP TRIGGER IF EXISTS `recurring`.`ins_profile`//
CREATE TRIGGER `recurring`.`ins_profile` BEFORE INSERT ON `recurring`.`profiles`
FOR EACH ROW BEGIN
DECLARE am DECIMAL(6,2);
DECLARE fr CHAR(4);
DECLARE sa INT;
SELECT price, frequency, startAfter INTO am,fr,sa FROM products WHERE code=NEW.productCode;
IF NEW.createDate='0000-00-00' THEN
	SET NEW.createDate = CURDATE();
END IF;
SET NEW.amount=am;
SET NEW.frequency=fr;
SET NEW.startDate=ADDDATE(NEW.createDate,sa);
SET NEW.nextPaymentDate=ADDDATE(NEW.createDate,sa);
SET NEW.profileID = nextProfileID();
END
//

 

I'm used to using straight mySQL and PHP.  I'm using inherited code for this project and in another area this code has worked with the start transaction committ and rollback and trigger.  So I'm thinking I must be making a silly error breaking it.  I find it very strange it breaks with an error of 0 when I echo it out.

 

Thanks for any help.

 

[fenway]

That's a lot of code.  If there's no default, and you don't specify a field, and you're using mysql 5 in strict mode, it complains.  Either give it a default or specify it explicitly.

[michaelh613]

I know this code was ported from an older site that probably had a earlier version of mySQL.  Were the earlier version less likely to complain when a field wasn't entered or was/is there a setting that could have been set not to complain?

[fenway]

I know this code was ported from an older site that probably had a earlier version of mySQL.  Were the earlier version less likely to complain when a field wasn't entered or was/is there a setting that could have been set not to complain?

Yup, 4.1 doesn't care.