Bug #9601 Some Queries Lost Through Connector
Submitted: 4 Apr 2005 9:48 Modified: 4 Apr 2005 17:58
Reporter: Eduard Maduro Email Updates:
Status: Can't repeat Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:3.51.11 OS:Windows (Windows XP SP2)
Assigned to: CPU Architecture:Any

[4 Apr 2005 9:48] Eduard Maduro 
Description:
My company finished making a ticket selling app using the following technologies:

- Visual Basic 6
- mySQL 4.1.10a w/ The myODBC Connection 3.51.11
- Crystal Reports 9 via the Crystal Reports Report Designer Component

The program basically allows the user to select seats in a seating chart, then issue a ticket that is created through Crystal.  The application also includes a series of End-Of-Day reports for accounting purposes.

Up until this past weekend, things were running relatively smoothly.  However, it seems the myODBC Connector is "deteriorating" because queries that worked without a flaw in the past now return erroneous data (Recordsets that would return 1 or more records now return "-1" Records!).  This is mainly apparent in using "SELECT *" queries or SELECT queries that call a relatively large number of specific fields.  What's ackward is that such queries WILL work in the Query Browser, but cease to work correctly in the VB App.

This issue is of serious importance seeing that I just wasted a weekend at a client site, wracking my brains out, wondering why certain queries would just stop working.  I've worked with MSAccess and SQL Server 2000 and neither ever gave me such an issue.  I would like a resolution to this matter ASAHP!  If this is what I think it is (myODBC Connector Bug), a patch to fix this issue would be of MAJOR importance and urgency!

Thanks

How to repeat:
CREATE TABLE ciclos (
  id int(11) NOT NULL auto_increment,
  nome varchar(255) NOT NULL default '',
  PRIMARY KEY  (id),
  UNIQUE KEY id (id),
  KEY id_2 (id)
) TYPE=MyISAM;

CREATE TABLE disponibilidade_auditorio (
  id int(11) NOT NULL auto_increment,
  id_evento int(11) default '0',
  data datetime default NULL,
  id_ciclo int(11) unsigned zerofill NOT NULL default '00000000000',
  formato varchar(255) default NULL,
  data_levantamento datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (id),
  UNIQUE KEY id (id),
  KEY id_2 (id,id_evento)
) TYPE=MyISAM;

CREATE TABLE escaloes_preco (
  id int(11) NOT NULL auto_increment,
  nome varchar(255) NOT NULL default '',
  defeito int(11) NOT NULL default '0',
  PRIMARY KEY  (id),
  UNIQUE KEY id (id),
  KEY id_2 (id)
) TYPE=MyISAM;

CREATE TABLE eventos (
  id int(11) NOT NULL auto_increment,
  id_tipo int(11) NOT NULL default '0',
  nome varchar(255) NOT NULL default '',
  nome_original varchar(255) NOT NULL default '',
  id_pais int(11) NOT NULL default '0',
  id_realizador int(11) NOT NULL default '0',
  actores text,
  ano int(11) NOT NULL default '0',
  id_produtor int(11) NOT NULL default '0',
  classificacao varchar(255) NOT NULL default '0',
  duracao int(11) NOT NULL default '0',
  id_distribuidor int(11) NOT NULL default '0',
  condicoes text NOT NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY id (id),
  KEY id_2 (id,id_tipo,id_pais,id_realizador,ano,id_produtor,id_distribuidor)
) TYPE=MyISAM;

CREATE TABLE precario (
  id int(11) NOT NULL auto_increment,
  id_evento int(11) NOT NULL default '0',
  id_ciclo int(11) NOT NULL default '0',
  id_escalao int(11) NOT NULL default '0',
  sala varchar(255) NOT NULL default '',
  id_seccao int(11) NOT NULL default '0',
  preco decimal(10,2) NOT NULL default '0.00',
  PRIMARY KEY  (id),
  UNIQUE KEY id (id),
  KEY id_2 (id,id_evento,id_escalao,sala,id_seccao,preco,id_ciclo)
) TYPE=MyISAM;

CREATE TABLE seccoes_salas (
  id int(11) unsigned zerofill NOT NULL auto_increment,
  nome varchar(255) NOT NULL default '',
  sala varchar(255) NOT NULL default '',
  subtab1 varchar(255) NOT NULL default '',
  subtab1_index int(11) NOT NULL default '0',
  subtab2 varchar(255) NOT NULL default '',
  subtab2_index int(11) NOT NULL default '0',
  subtab3 varchar(255) NOT NULL default '',
  subtab3_index int(11) NOT NULL default '0',
  subtab4 varchar(255) NOT NULL default '',
  subtab4_index int(11) NOT NULL default '0',
  PRIMARY KEY  (id),
  UNIQUE KEY id (id),
  KEY id_2 (id)
) TYPE=MyISAM;

CREATE TABLE tipos_evento (
  id int(11) NOT NULL auto_increment,
  nome varchar(11) NOT NULL default '',
  PRIMARY KEY  (id),
  UNIQUE KEY id (id),
  KEY id_2 (id)
) TYPE=MyISAM;

CREATE TABLE utilizadores (
  id int(11) NOT NULL auto_increment,
  nome varchar(255) NOT NULL default '',
  username varchar(255) NOT NULL default '',
  password varchar(255) NOT NULL default '',
  id_nivel int(11) NOT NULL default '0',
  PRIMARY KEY  (id),
  UNIQUE KEY id (id),
  KEY id_2 (id)
) TYPE=MyISAM;

CREATE TABLE vendas_eventos (
  id int(11) NOT NULL auto_increment,
  id_utilizador int(11) unsigned zerofill NOT NULL default '00000000000',
  codigo varchar(255) NOT NULL default '000000000',
  sala varchar(255) NOT NULL default '',
  id_evento int(11) NOT NULL default '0',
  data datetime NOT NULL default '0000-00-00 00:00:00',
  id_ciclo int(11) unsigned zerofill NOT NULL default '00000000000',
  id_escalao int(11) unsigned zerofill NOT NULL default '00000000000',
  cadeira varchar(255) NOT NULL default '',
  preco decimal(10,2) unsigned zerofill NOT NULL default '00000000.00',
  comprado int(11) unsigned zerofill NOT NULL default '00000000000',
  reservado int(11) unsigned NOT NULL default '0',
  nome varchar(255) NOT NULL default '',
  contacto varchar(255) NOT NULL default '',
  bi varchar(255) NOT NULL default '',
  local varchar(255) NOT NULL default '',
  fila varchar(255) NOT NULL default '',
  numero int(255) NOT NULL default '0',
  nome_evento varchar(255) NOT NULL default '',
  sessao varchar(255) NOT NULL default '',
  classificacao varchar(255) NOT NULL default '',
  condicoes text NOT NULL,
  data_compra datetime NOT NULL default '0000-00-00 00:00:00',
  data_levantamento datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (id),
  UNIQUE KEY id (id),
  KEY id_2 (id,id_evento,cadeira)
) TYPE=MyISAM;

INSERT INTO escaloes_preco VALUES("1", "Normal", "1");
INSERT INTO escaloes_preco VALUES("2", "Idosos", "0");
INSERT INTO escaloes_preco VALUES("3", "Jovens", "0");
INSERT INTO escaloes_preco VALUES("4", "Convite", "0");

INSERT INTO eventos VALUES("1", "1", "aaa", "aaa", "158", "1", "ffdsffdsfds", "2005", "1", "sadsa", "120", "1", "dsdsadsa");

INSERT INTO precario VALUES("1", "1", "1", "1", "Grande Auditório", "1", "70.00");
INSERT INTO precario VALUES("2", "1", "1", "1", "Grande Auditório", "2", "75.00");

INSERT INTO seccoes_salas VALUES("00000000001", "1ª PLATEIA", "Grande Auditório", "tabFormat", "0", "tabFloor0", "0", "tabStage", "0", "", "0");
INSERT INTO seccoes_salas VALUES("00000000002", "2ª PLATEIA", "Grande Auditório", "tabFormat", "0", "tabFloor0", "0", "tabStage", "1", "", "0");
INSERT INTO seccoes_salas VALUES("00000000003", "BALCÃO 1 LATERAL", "Grande Auditório", "tabFormat", "0", "tabFloor0", "1", "tabBalcao", "0", "tabBalcao1LR", "0");
INSERT INTO seccoes_salas VALUES("00000000004", "BALCÃO 1 CENTRAL", "Grande Auditório", "tabFormat", "0", "tabFloor0", "1", "tabBalcao", "0", "tabBalcao1LR", "1");
INSERT INTO seccoes_salas VALUES("00000000005", "BALCÃO 2 LATERAL", "Grande Auditório", "tabFormat", "0", "tabFloor0", "1", "tabBalcao", "1", "tabBalcao2LR", "0");
INSERT INTO seccoes_salas VALUES("00000000006", "BALCÃO 2 CENTRAL", "Grande Auditório", "tabFormat", "0", "tabFloor0", "1", "tabBalcao", "1", "tabBalcao2LR", "1");
INSERT INTO seccoes_salas VALUES("00000000007", "CAMAROTES 1º PISO LATERAL", "Grande Auditório", "tabFormat", "1", "tabCamarote1", "0", "", "0", "", "0");
INSERT INTO seccoes_salas VALUES("00000000008", "CAMAROTES 1º PISO CENTRAL", "Grande Auditório", "tabFormat", "1", "tabCamarote1", "1", "", "0", "", "0");
INSERT INTO seccoes_salas VALUES("00000000009", "CAMAROTES 2º PISO LATERAL", "Grande Auditório", "tabFormat", "2", "tabCamarote2", "0", "", "0", "", "0");
INSERT INTO seccoes_salas VALUES("00000000010", "CAMAROTES 2º PISO CENTRAL", "Grande Auditório", "tabFormat", "2", "tabCamarote2", "1", "", "0", "", "0");

INSERT INTO utilizadores VALUES("2", "Utilizador", "user", "password", "1");

In the VB App with those technologies:

INSERT INTO vendas_eventos(id_utilizador,id_ciclo,sala,id_evento,data,id_escalao,cadeira,preco,local,fila,numero,nome_evento,sessao,classificacao,condicoes) VALUES(1,0,'Grande Auditório',1,'2005-04-02 00:00:00',1,'A1','70','1ª PLATEIA',
'A','1','aaa','-N/D-','M/7','-N/D-')

Create a Recordsetwith the following query:

SELECT * FROM vendas_eventos WHERE id_utilizador=1 AND comprado=0 AND reservado=0

At this point, such should pull up 1 record with this query (the record inserted in the INSERT above).  Instead, it returns -1!

Suggested fix:
N/A
[4 Apr 2005 17:58] MySQL Verification Team 
I wasn't able to repeat using Access 2003.
[4 Apr 2005 18:05] Eduard Maduro 
That's because Access uses the Jet Driver.  I'm now pretty much dead certain that this is a myODBC issue because if I take the query in question and run it through the mySQL Query Browser, it returns the data I want, but in the app, that same query returns "-1" records.

I have my doubts that you won't be able to simulate this error in anything elese except the environment I mentioned.