mysql line of script refuses to execute (no errors/warnings)

Question

I have a very long script that imports a csv file into a holding table, does some massaging, then copies the data from the holding table into the final table.

When I run the script, everything executes except line 494:

UPDATE `database`.`holding` 
SET `rcnt_rslts`=TRUE 
WHERE `test_rcnt`=TRUE 
  AND (`rcntrslt`='pos' OR `rcntrslt`='neg' OR `rcntrslt`='indeterminate');

rcnt_rslts in both tables is BOOL/TINYINT(1) and the lines above an below are nearly identical and are executed just fine. There are no other references to rcnt_rslts anywhere else in the script (verified with find/replace).

If I past that line directly into the CMI, it works.

Is there a line/size limit and mysql just randomly picked a line to ignore?

edit: if it matters, all values in rcnt_rslts are NULL after the script runs.

@muistooshort: Hi mu. no, no error messages or warnings

Jakob Jingleheimer
– Jakob Jingleheimer

2011-10-05 04:42:34 +00:00
Commented Oct 5, 2011 at 4:42 — Jakob Jingleheimer
– Jakob Jingleheimer, Commented Oct 5, 2011 at 4:42

Johan · Accepted Answer · 2011-10-05 08:16:03Z

1

OK I'll give it a try:

1- Maybe you made a typo in rcntrslt, do it like this and you only list it once.

UPDATE `database`.holding 
SET rcnt_rslts=TRUE 
WHERE test_rcnt=TRUE 
  AND (rcntrslt IN ('pos','neg','indeterminate'));

2- = can be case sensitive, LIKE never is, try:

UPDATE `database`.holding 
SET rcnt_rslts=TRUE 
WHERE test_rcnt=TRUE 
  AND (rcntrslt LIKE 'pos' OR rcntrslt LIKE 'neg' OR rcntrslt LIKE 'indeterminate');

3- What other values can rcntrslt have, you not reverse all the tests?

UPDATE `database`.holding 
SET rcnt_rslts = TRUE 
WHERE test_rcnt<>FALSE AND test_rcnt IS NOT NULL 
  AND (rcntrslt NOT IN ('othervalue','test1','gdsd')
  AND rcntrslt IS NOT NULL;

4- Maybe there is some pollution in rcntrslt causing spaces or other non-printable chars to be there.

UPDATE `database`.holding 
SET rcnt_rslts=TRUE 
WHERE test_rcnt=TRUE 
  AND (rcntrslt LIKE '%pos%' OR rcntrslt LIKE '%neg%' 
       OR rcntrslt LIKE '%indeterminate%');

5- If you want to debug, you can always do some diagnosic SELECTS in there to see what's going on.

CREATE TABLE log (
  id integer auto_increment not null primary key,
  logtime timestamp,
  reason varchar(255) not null,
  tablename varchar(255) not null,
  fieldnames varchar(10000) not null,
  values varchar(10000) not null,
  INDEX log_time (logtime),
  INDEX log_table (tablename),
  INDEX log_reason (tablename, reason)) ENGINE = InnoDB;

INSERT INTO log (reason, tablename, fieldnames, values)
  SELECT 
    'all values','holding', 'test_rcnt,rcntrslt'
    , CONCAT(IFNULL(test_rcnt,'NULL'), ',' ,IFNULL(rcntrslt,'NULL')
  FROM holding;

INSERT INTO log (reason, tablename, fieldnames, values)
  SELECT 
    'test_rcnt = TRUE','holding', 'test_rcnt,rcntrslt'
    , CONCAT(IFNULL(test_rcnt,'NULL'), ',' ,IFNULL(rcntrslt,'NULL')
  FROM holding 
  WHERE test_rcnt = TRUE;

etc.

Now check the log to see what's wrong.

answered Oct 5, 2011 at 8:16

Johan

77.4k28 gold badges204 silver badges346 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ypercubeᵀᴹ Over a year ago

I thought that = and LIKE are case sensitive (or not), according to the collation used.

Johan Over a year ago

@ypercube, no LIKE is never case sensitive.

Jakob Jingleheimer Over a year ago

@Johan, thanks for your response. I had to go away on business so I'm just getting caught up. will try this out soon. thanks!

Collectives™ on Stack Overflow

mysql line of script refuses to execute (no errors/warnings)

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related