Description:
I use a subselect in a view to retrieve a long pattern (varchar) to be used in REGEXP.
The problem is, that somehow this view get corrupted and is not listed anymore in INFORMATION_SCHEMA.VIEWS(?)
Any statement (I tried out) referencing the view returns:
mysql> describe vPrimNRTI;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'equal
(_latin1'Prim NRTI', '') limit 1)))' at line 1
mysql> select * from vPrimNRTI where patID=1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'equal
(_latin1'Prim NRTI', '') limit 1)))' at line 1
mysql> show create view vPrimNRTI;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'equal
(_latin1'Prim NRTI', '') limit 1)))' at line 1
This also occured on debian linux with 5.0.51-3-log (Debian).
The views I use are created all together from a source file.
a) When I recreated the view in question (cut and paste to mysql client), the error disapeared.
b) When I looked at the table INFORMATION_SCHEMA.VIEWS directly, selecting the TABLE_NAME, the views with the error where not listed anymore AND desc now returns:
mysql> desc vPrimNNRTI;
ERROR 1109 (42S02): Unknown table 'vprimnnrti' in information_schema
First I thougt the problem had something to do with the subselect because once I got a error message like "subselect returns more than one row" which is not possible by the declaration of the tables involved. That's the reason I added the LIMIT 1 clause.
Here are the tables and views:
CREATE TABLE sSearchPatterns (
name VARCHAR (20) NOT NULL PRIMARY KEY, #name of regexp
type CHAR (1) NOT NULL DEFAULT 'R', #R=regexp S=normal select condition
description VARCHAR (80) NOT NULL DEFAULT '', #
pattern VARCHAR (400) NOT NULL
) Engine=InnoDB COMMENT='Regular expression patterns used to select records';
INSERT INTO sSearchPatterns
(name, description, pattern)
VALUES
(
'Prim NRTI', 'Select primary resistance NRTI associated (transmitted) mutations',
'((^| )41[A-Z]*L)|((^| )65[A-Z]*R)|((^| )67[A-Z]*(G|N))|((^| )70[A-Z]*R)|((^| )74[A-Z]*V)|((^| )75[A-Z]*(A|M|T|S))|((^| )77[A-Z]*L)|((^| )115[A-Z]*F)|((^| )116[A-Z]*Y)|((^| )151[A-Z]*M)|((^| )184[A-Z]*(I|V))|((^| )210[A-Z]*W)|((^| )215[A-Z]*(Y|F|C|D|E|S|I|V))|((^| )219[A-Z]*(E|R|Q))'
),
(
'Prim NNRTI', 'Select primary resistance NNRTI associated (transmitted) mutations',
'((^| )100[A-Z]*I)|((^| )101[A-Z]*E)|((^| )103[A-Z]*(N|S))|((^| )106[A-Z]*(A|M))|((^| )181[A-Z]*(I|C))|((^| )188[A-Z]*(C|L|H))|((^| )190[A-Z]*(A|S|E|Q))|((^| )225[A-Z]*H)|((^| )230[A-Z]*L)|((^| )263[A-Z]*L)'
),
(
'Prim PI', 'Select primary resistance PI associated (transmitted) mutations',
'((^| )24[A-Z]*I)|((^| )30[A-Z]*N)|((^| )32[A-Z]*I)|((^| )46[A-Z]*I)|((^| )47[A-Z]*(A|V))|((^| )48[A-Z]*V)|((^| )50[A-Z]*(V|L))|((^| )53[A-Z]*L)|((^| )54[A-Z]*(V|L|M|A|T|S))|((^| )73[A-Z]*(C|S|T|A))|((^| )82[A-Z]*(A|F|T|S|M))|((^| )84[A-Z]*(V|A|C))|((^| )88[A-Z]*(D|S))|((^| )90[A-Z]*M)'
),
(
'Prim Revert', 'Select primary resistance revertant mutations',
'((^| )215[A-Z]*(A|C|D|E|L|N|S))'
),
(
'HIV-DB NRTI', 'Select NRTI mutations acc. Stanfords HIV-DB',
'((^| )41[A-Z]*L)|((^| )62[A-Z]*V)|((^| )65[A-Z]*R)|((^| )67[A-Z]*N)|((^| )70[A-Z]*(E|R))|((^| )74[A-Z]*(I|V))|((^| )75[A-Z]*(I|M|T))|((^| )77[A-Z]*(I|L))|((^| )115[A-Z]*F)|((^| )116[A-Z]*Y)|((^| )151[A-Z]*M)|((^| )184[A-Z]*(I|V))|((^| )210[A-Z]*W)|((^| )215[A-Z]*(F|Y))|((^| )219[A-Z]*(E|Q))'
)
;
#select isolates with sequences having primary NRTI mutations
CREATE OR REPLACE VIEW vPrimNRTI AS SELECT
i.patID, i.isolate, r.seqID, subst mut
FROM
tIsolates i INNER JOIN tRawSequences r ON (i.isolate=r.isolate)
INNER JOIN tdSeqMutations m ON (r.seqID=m.seqID)
WHERE
(m.contains='RT') AND
(subst REGEXP (SELECT pattern FROM sSearchPatterns WHERE name='Prim NRTI' LIMIT 1))
;
#select isolates with sequences having primary NNRTI mutations
CREATE OR REPLACE VIEW vPrimNNRTI AS SELECT
i.patID, i.isolate, r.seqID, subst mut
FROM
tIsolates i INNER JOIN tRawSequences r ON (i.isolate=r.isolate)
INNER JOIN tdSeqMutations m ON (r.seqID=m.seqID)
WHERE
(m.contains='RT') AND
(subst REGEXP (SELECT pattern FROM sSearchPatterns WHERE name='Prim NNRTI' LIMIT 1))
;
#select isolates with sequences having primary PI mutations
CREATE OR REPLACE VIEW vPrimPI AS SELECT
i.patID, i.isolate, subst mut
FROM
tIsolates i INNER JOIN tRawSequences r ON (i.isolate=r.isolate)
INNER JOIN tdSeqMutations m ON (r.seqID=m.seqID)
WHERE
(m.contains='PRO') AND
(subst REGEXP (SELECT pattern FROM sSearchPatterns WHERE name='Prim PI' LIMIT 1))
;
Although I'll write a better alternative using a stored procedure, I have a bad feeling about this.
How to repeat:
I'm not sure.
When I resubmitted the whole source file with the views, the error was gone.
I stopped the server, deleted all InnoDB files and own DBs, recreated all from the sql sources (the create views source beeing the last one) and the error was there again.
Then I inserted some sample data (including data for sSearchPatterns), resubmitted the create views script again and the error was gone.
I repeated the whole procedure, now inserting only data into sSearchPatterns before creating the views, all other tables are still empty => the error is gone!
Then I truncated the table sSearchPatterns and the error still is gone :-)
