(2.6.3-pl1) Not displaying results of complex query

pma-import commented 19 years ago

I created a MySQL addressbook which has 8 tables:

1Organizations - Organization names 1People - Contact names, & groups 2email - Stores multiple email addresses per contact 2im - multiple IM media type, & user names per contact 2locations - multiple addresses per contact 2phone - multiple phone numbers per contact (cell, home, etc) 2web - multiple URLs per contact 2workinfo - links People to Organizations

I wrote a query (or is it a script?) to create a flat-file Mozilla Thunderbird addressbook export for personal contacts, and another one for professional contacts. The query (or script?) does this, in order:

drops the temp table qryEmail if it exists creates 2 temp tables - one for each T-bird email-type table 1: contact ID and primary email table 2: contact ID and secondary email creates a temp table (qryEmail) that includes: contact ID, primary email, secondary email drops the first two email temp tables

drops the temp table qryPhone if it exists creates 5 temp tables - one for each T-bird phone-type table 1: contact ID & work phone table 2: contact ID & home phone table 3: contact ID & fax number table 4: contact ID & pager table 5: contact ID & cell creates a temp table (qryPhone) that includes: contact ID, work, home, fax, pager, cell drops the first 5 phone temp tables

drops the temp table qryAddress if it exists creates 2 temp tables - one for each T-bird address-type table 1: contact ID and home address table 2: contact ID and work address creates a temp table (qryAddress) that includes: contact ID, home address fields, work address fields drops the first 2 address temp tables

drops the temp table qryWeb if it exists creates 2 temp tables - one for each T-bird URL-type table 1: contact ID and home URL table 2: contact ID and work URL creates a temp table (qryWeb) that includes: contact ID, home URL, work URL drops the first 2 web (URL) tables

SELECTS from remaining temp tables, 1people, and 1organization to create the final Thunderbird flat file:

SELECT 1people.FirstName AS First Name , 1people.LastName AS Last Name , CONCAT( 1people.LastName, ', ', 1people.FirstName ) AS Display Name , CONCAT( LEFT( 1people.LastName, 3 ) , LEFT( 1people.FirstName, 3 ) ) AS Nickname , qryEmail.PrimaryEmail AS Primary Email , IF ( qryEmail.SecondaryEmail IS NULL , '', qryEmail.SecondaryEmail ) AS Secondary Email , IF ( qryPhone.WorkPhone IS NULL , '', qryPhone.WorkPhone ) AS Work Phone , IF ( qryPhone.HomePhone IS NULL , '', qryPhone.HomePhone ) AS Home Phone , IF ( qryPhone.FaxNumber IS NULL , '', qryPhone.FaxNumber ) AS Fax Number , IF ( qryPhone.PagerNumber IS NULL , '', qryPhone.PagerNumber ) AS Pager Number , IF ( qryPhone.MobileNumber IS NULL , '', qryPhone.MobileNumber ) AS Mobile Number , IF ( qryAddress.HomeAddress IS NULL , '', qryAddress.HomeAddress ) AS Home Address , IF ( 1 =1, '', '' ) AS Home Address 2 , IF ( qryAddress.HomeCity IS NULL , '', qryAddress.HomeCity ) AS Home City , IF ( qryAddress.HomeState IS NULL , '', qryAddress.HomeState ) AS Home State , IF ( qryAddress.HomeZip IS NULL , '', qryAddress.HomeZip ) AS Home ZipCode , IF ( qryAddress.HomeCountry IS NULL , '', qryAddress.HomeCountry ) AS Home Country , IF ( qryAddress.WorkAddress IS NULL , '', qryAddress.WorkAddress ) AS Work Address , IF ( 1 =1, '', '' ) AS Work Address 2 , IF ( qryAddress.WorkCity IS NULL , '', qryAddress.WorkCity ) AS Work City , IF ( qryAddress.WorkState IS NULL , '', qryAddress.WorkState ) AS Work State , IF ( qryAddress.WorkZip IS NULL , '', qryAddress.WorkZip ) AS Work ZipCode , IF ( qryAddress.WorkCountry IS NULL , '', qryAddress.WorkCountry ) AS Work Country , IF ( 2workinfo.PriLvl =1, 2workinfo.JobTitle, '' ) AS Job Title , IF ( 2workinfo.PriLvl =1, 2workinfo.Department, '' ) AS Department, IF ( 2workinfo.PriLvl =1, 2workinfo.OrgID, '' ) AS Organization, IF ( qryWeb.WorkWeb IS NULL , '', qryWeb.WorkWeb ) AS Web Page 1 , IF ( qryWeb.PersonalWeb IS NULL , '', qryWeb.PersonalWeb ) AS Web Page 2 , 1people.BirthYear AS Birth Year , 1people.BirthMonth AS Birth Month , 1people.BirthDay AS Birth Day , IF ( 1 =1, '', '' ) AS Custom 1 , IF ( 1 =1, '', '' ) AS Custom 2 , IF ( 1 =1, '', '' ) AS Custom 3 , IF ( 1 =1, '', '' ) AS Custom 4 , 1people.Notes FROM 1people LEFT JOIN qryEmail ON 1people.PersonID = qryEmail.PersonID LEFT JOIN qryPhone ON 1people.PersonID = qryPhone.PersonID LEFT JOIN qryAddress ON 1people.PersonID = qryAddress.PersonID LEFT JOIN 2workinfo ON 1people.PersonID = 2workinfo.PersonID LEFT JOIN qryWeb ON 1people.PersonID = qryWeb.PersonID WHERE qryEmail.PrimaryEmail != '' AND ( 1people.Category = 'Family' OR 1people.Category = 'Friends' ) GROUP BY 1people.LastName, 1people.FirstName;# Rows: 97

The query works with MySQL Query Browser. However, when I run the query with phpMyAdmin, I get "# Rows: 97", but none of them show up on the screen.

If there is a better way to accomplish my objective (a flat-file Mozilla Thunderbird addressbook export from my contacts database), I'm open to suggestions!

Original URL: "https://sourceforge.net/p/phpmyadmin/bugs/1579/":https://sourceforge.net/p/phpmyadmin/bugs/1579/
Original author: hplsbyufan
Found in version: Latest_Git

pma-import commented 19 years ago

SQL statements for Tables and Queries

Original author: hplsbyufan
Attachment: "http://sourceforge.net/p/phpmyadmin/bugs/_discuss/thread/5c45c542/8e70/attachment/TableDesAndqrys.zip":http://sourceforge.net/p/phpmyadmin/bugs/_discuss/thread/5c45c542/8e70/attachment/TableDesAndqrys.zip

pma-import commented 19 years ago

Logged In: YES user_id=1126753

You should be able to populate each table with data and do this query, but if not, I can provide some insert statements with faked names on request.

Original author: hplsbyufan

pma-import commented 19 years ago

Logged In: YES user_id=192186

I'd like to have some test data, otherwise it doesn't have anything to display...

Original author: nijel

pma-import commented 19 years ago

assigned_to: nobody --> nijel

Original author: nijel

pma-import commented 19 years ago

summary: No results - large qry, temp tables, possibly 864984 related --> Not displaying results of large query

Original author: nijel

pma-import commented 19 years ago

patch

Original author: nijel
Attachment: "http://sourceforge.net/p/phpmyadmin/bugs/_discuss/thread/5c45c542/0595/attachment/pma-multi-submit.patch":http://sourceforge.net/p/phpmyadmin/bugs/_discuss/thread/5c45c542/0595/attachment/pma-multi-submit.patch

pma-import commented 19 years ago

Logged In: YES user_id=192186

I reproduced it, attached patch should help you, can you please verify that?

Original author: nijel

pma-import commented 19 years ago

milestone: 465000 --> Latest_Git
status: open --> open-accepted

Original author: nijel

pma-import commented 19 years ago

Logged In: YES user_id=1126753

I am still working on some csv data and/or select statements for each table. I put the .patch file in my phpMyAdmin folder root, and it didn't make a difference, but I'm sure there is more to it. Do I rename the file, or perhaps paste it's contents over something in read_dump.php? I'm not php literate, though I would like to be...

Original author: hplsbyufan

pma-import commented 19 years ago

Logged In: YES user_id=192186

You can apply it using

pach -p0 < pma-multi-submit.patch

in phpMyAdmin root directory.

Original author: nijel

pma-import commented 19 years ago

Logged In: YES user_id=1126753

My web server is remote, via 1and1.com. How do I run that command? ftp prompt? telnet? putty ssl?

Original author: hplsbyufan

pma-import commented 19 years ago

Logged In: YES user_id=192186

Okay, let's do it simpler way, just overwrite read_dump.php with attached one :-)

Original author: nijel

pma-import commented 19 years ago

Original author: nijel
Attachment: "http://sourceforge.net/p/phpmyadmin/bugs/_discuss/thread/5c45c542/4c9e/attachment/read_dump.php":http://sourceforge.net/p/phpmyadmin/bugs/_discuss/thread/5c45c542/4c9e/attachment/read_dump.php

pma-import commented 19 years ago

Logged In: YES user_id=1126753

I replaced the file, and got this error when I ran the query:

error 500: Internal Server Error

The server encountered an unexpected condition which prevented it from fulfilling the request.

Original author: hplsbyufan

pma-import commented 19 years ago

Logged In: YES user_id=1126753

OK, I have some csv dumps ready now, in case you still want them. I will convert them to sql statements later if needed.

Original author: hplsbyufan

pma-import commented 19 years ago

CSV dumps for Tables

Original author: hplsbyufan
Attachment: "http://sourceforge.net/p/phpmyadmin/bugs/_discuss/thread/5c45c542/f8d1/attachment/csvDumps.zip":http://sourceforge.net/p/phpmyadmin/bugs/_discuss/thread/5c45c542/f8d1/attachment/csvDumps.zip

pma-import commented 19 years ago

Logged In: YES user_id=1126753

BTW, the csv dumps will not load all records in the 1people table unless you remove the unique index on the first 3 characters for first and last name, which is just intended to keep duplicates out.

Original author: hplsbyufan

pma-import commented 19 years ago

Logged In: YES user_id=192186

I see no reason, why you could get internal server error. Is it possible to get more verbose information?

Original author: nijel

pma-import commented 19 years ago

Logged In: YES user_id=1126753

I tried again, refreshing the page before I ran the query. I still get that error with the attached read_dump.php file. I ran a sniffer trace, but didn't find anything useful. I'll provide a copy if you want to look at it. I tried with Firefox (my preferred browser) and Internet Explorer. Both give the same error:

error 500: Internal Server Error

The server encountered an unexpected condition which prevented it from fulfilling the request.

I will try looking at my web log reports and see if I can find more information there. Any recomendations on getting a more robust error?

Original author: hplsbyufan

pma-import commented 19 years ago

Logged In: YES user_id=192186

Original author: nijel

pma-import commented 19 years ago

Logged In: YES user_id=192186

I believe this is already fixed in cvs, can you please try cvs snapshot?

Original author: nijel

pma-import commented 19 years ago

Logged In: YES user_id=1126753

Are you referring to 2.6.1-pl3 or the cvs snapshot for the next release? I have not tried 2.6.1-pl1, pl2, or pl3... I hadn't noticed their releases.

Original author: hplsbyufan

pma-import commented 19 years ago

Logged In: YES user_id=192186

I mean cvs snapshot. In pl releases are included only fixes for critical bugs and this is not one.

Original author: nijel

pma-import commented 19 years ago

Logged In: YES user_id=1126753

I've never used a cvs before. I'm reading the documentation, and I've installed Python 2.4, WinCVS, and CVSNT. I'll probably need some time to figure this out, but I will try to load the CVS in the next couple of days.

Original author: hplsbyufan

pma-import commented 19 years ago

Logged In: YES user_id=192186

You don't need cvs at all, check our donwload page ;-)

Original author: nijel

pma-import commented 19 years ago

Logged In: YES user_id=1126753

That was much more convenient downloading from your home page. I tried the 20050308 CVS with my config.inc.php from 2.6.1. It did not work. I'll try the CVS config.inc.php next.

If that does not work, I'm going to rewrite the entire script with backtics around all table and field names, and apostrophe's (single quotes) around all values.

Finally, I'll try renaming my tables since they all start with numbers, and I'll see if that makes a difference.

Does that sound like a good plan?

Original author: hplsbyufan

pma-import commented 19 years ago

Logged In: YES user_id=192186

Okay, so this bug is fixed, there is no reason why it shouldn't work with other config.

Original author: nijel

pma-import commented 19 years ago

priority: 5 --> 1
summary: Not displaying results of large query --> (in 2.6.2) Not displaying results of large query
status: open-accepted --> open-fixed

Original author: nijel

pma-import commented 19 years ago

Logged In: YES user_id=1126753

The script/query is still not working for me in phpmyadmin, but it does work in MySQL Query Browser. I'm trying a couple revisions of my script/query to see if that works better, but I'm not convinced it is a fixed bug since the same script/query works in another program. However, I'll continue investigating possible flaws in my database and query design just in case.

Original author: hplsbyufan

pma-import commented 19 years ago

Logged In: YES user_id=192186

Sorry, I read wrong your answer...

Original author: nijel

pma-import commented 19 years ago

priority: 1 --> 5
summary: (in 2.6.2) Not displaying results of large query --> Not displaying results of large query
status: open-fixed --> open-accepted

Original author: nijel

pma-import commented 19 years ago

Logged In: YES user_id=1126753

No problem. FYI, I am now rebuilding the query from the ground up to see when the problem starts occuring. I'll post results in the next couple of days.

Original author: hplsbyufan

pma-import commented 19 years ago

Logged In: YES user_id=1126753

I have done some testing (more to come), and this may not be an issue with large queries as much as complex ones.

I'm finding the same result when using a query tab, and another error when running from the popup query window.

The issue seems to occur when I create tables 2 levels deep, temp or not, it doesn't matter. In other words, If I do this:

create temp table temp1
create temp table temp2
create temp table temp3 that combines info from temp1&2
select * from temp3

the results don't display. Also, if I do this:

create table temp1
create table temp2
create table temp3 that combines info from temp1&2
select * from temp3

the results still do not display. Note that these tables are not temp tables, and I must delete them manually.

More details later. Hope this helps...

Original author: hplsbyufan

pma-import commented 19 years ago

Logged In: YES user_id=192186

Could you please post here exact query that caused problems? I'm afraid that problem will be in comments around that select statement.

Original author: nijel

pma-import commented 19 years ago

Logged In: YES user_id=1126753

nigel,

I will post an exact query soon. I have no comments in the select statement.

Original author: hplsbyufan

pma-import commented 19 years ago

Logged In: YES user_id=1126753

Finally, here you go:

First, create some test tables for contact info, email info, and phone info.

DROP TABLE IF EXISTS tstContacts; CREATE TABLE IF NOT EXISTS tstContacts ( ContactID tinyint(4) NOT NULL auto_increment, Created datetime NOT NULL default '0000-00-00 00:00:00', Modified timestamp(14) NOT NULL, FirstName varchar(10) NOT NULL default '', MiddleName varchar(10) NOT NULL default '', LastName varchar(10) NOT NULL default '', PrefName enum('1','2','3') NOT NULL default '1', Suffix enum('','Jr','Sr') NOT NULL default '', Categories set('Professional','Family','Johnson','Stout','Friends','Co-Workers') NOT NULL default '', Birthday date NOT NULL default '0000-00-00', Notes mediumtext NOT NULL, PRIMARY KEY (ContactID) ) TYPE=MyISAM AUTO_INCREMENT=5 ;

INSERT INTO tstContacts VALUES (1, '2005-03-30 17:34:33', '20050330173433', 'John', 'Jo', 'Doh', '1', '', 'Professional', '1970-07-04', 'Fake Guy #1'); INSERT INTO tstContacts VALUES (2, '2005-03-30 17:34:51', '20050330191314', 'John', 'James', 'Doo', '1', '', 'Family', '1971-07-05', 'Fake Guy #2'); INSERT INTO tstContacts VALUES (3, '2005-03-30 19:05:00', '20050330190500', 'Brad', 'Low', 'Schmoe', '1', '', 'Professional', '1972-07-04', 'Fake Guy #3'); INSERT INTO tstContacts VALUES (4, '2005-03-30 19:05:00', '20050330190500', 'Sue', 'Joan', 'Heimer', '1', '', 'Friends', '1973-07-05', 'Fake Gal #4');

DROP TABLE IF EXISTS tstEmail; CREATE TABLE IF NOT EXISTS tstEmail ( ContactID smallint(6) NOT NULL default '0', EmailAddress varchar(35) NOT NULL default '', Bulk enum('0','1') NOT NULL default '0', PrefLvl tinyint(4) NOT NULL auto_increment, PRIMARY KEY (ContactID,PrefLvl), UNIQUE KEY UniqueEmail (EmailAddress) ) TYPE=MyISAM COMMENT='My Addressbook' AUTO_INCREMENT=1 ;

INSERT INTO tstEmail VALUES (1, 'email@fakeco.com', '0', 1); INSERT INTO tstEmail VALUES (2, 'fake@email.com', '0', 1); INSERT INTO tstEmail VALUES (3, 'hlp@schmoe.com', '0', 1); INSERT INTO tstEmail VALUES (3, 'dude@schmigger.com', '0', 2);

DROP TABLE IF EXISTS tstPhone; CREATE TABLE IF NOT EXISTS tstPhone ( PhoneID smallint(6) NOT NULL auto_increment, ContactID smallint(6) NOT NULL default '0', PhoneNumber varchar(19) NOT NULL default '', PhoneType enum('Work','Home','Cell','Pager','Fax','Free','Alt-Work') NOT NULL default 'Home', PRIMARY KEY (PhoneID), UNIQUE KEY UniquePhoneType (ContactID,PhoneType), UNIQUE KEY UniquePhone (ContactID,PhoneNumber) ) TYPE=MyISAM AUTO_INCREMENT=7 ;

INSERT INTO tstPhone VALUES (1, 1, '801-555-1212', 'Work'); INSERT INTO tstPhone VALUES (2, 1, '801-555-1213', 'Alt-Work'); INSERT INTO tstPhone VALUES (3, 1, '801-555-1214', 'Cell'); INSERT INTO tstPhone VALUES (4, 3, '801-555-1215', 'Work'); INSERT INTO tstPhone VALUES (5, 3, '801-555-1216', 'Pager'); INSERT INTO tstPhone VALUES (6, 2, '801-555-1217', 'Home');

Second, I extract and select email info into a Thunderbird Addressbook flatfile compatible format.

THIS WORKS:

CREATE TEMPORARY TABLE qryEmail1 SELECT tstContacts.ContactID, IF (tstEmail.PrefLvl='1',tstEmail.EmailAddress,'') AS PrimaryEmail FROM tstContacts LEFT JOIN tstEmail ON tstContacts.ContactID=tstEmail.ContactID WHERE tstEmail.PrefLvl='1';

CREATE TEMPORARY TABLE qryEmail2 SELECT tstContacts.ContactID, IF (tstEmail.PrefLvl='2',tstEmail.EmailAddress,'') AS SecondaryEmail FROM tstContacts LEFT JOIN tstEmail ON tstContacts.ContactID=tstEmail.ContactID WHERE tstEmail.PrefLvl='2';

SELECT tstContacts.ContactID, qryEmail1.PrimaryEmail, qryEmail2.SecondaryEmail FROM tstContacts LEFT JOIN qryEmail1 ON tstContacts.ContactID=qryEmail1.ContactID LEFT JOIN qryEmail2 ON tstContacts.ContactID=qryEmail2.ContactID;

Third, I change the final select statement from the first step into a temp table, and select from it in another statement.

THIS ALSO WORKS:

CREATE TEMPORARY TABLE qryEmail1 SELECT tstContacts.ContactID, IF (tstEmail.PrefLvl='1',tstEmail.EmailAddress,'') AS PrimaryEmail FROM tstContacts LEFT JOIN tstEmail ON tstContacts.ContactID=tstEmail.ContactID WHERE tstEmail.PrefLvl='1';

CREATE TEMPORARY TABLE qryEmail2 SELECT tstContacts.ContactID, IF (tstEmail.PrefLvl='2',tstEmail.EmailAddress,'') AS SecondaryEmail FROM tstContacts LEFT JOIN tstEmail ON tstContacts.ContactID=tstEmail.ContactID WHERE tstEmail.PrefLvl='2';

CREATE TEMPORARY TABLE qryEmail SELECT tstContacts.ContactID, qryEmail1.PrimaryEmail, qryEmail2.SecondaryEmail FROM tstContacts LEFT JOIN qryEmail1 ON tstContacts.ContactID=qryEmail1.ContactID LEFT JOIN qryEmail2 ON tstContacts.ContactID=qryEmail2.ContactID WHERE qryEmail1.PrimaryEmail IS NOT NULL;

SELECT tstContacts.ContactID, qryEmail.PrimaryEmail, qryEmail.SecondaryEmail FROM tstContacts LEFT JOIN qryEmail ON tstContacts.ContactID=qryEmail.ContactID;

Fourth, I do same thing for contact phone info, all in one step.

THIS WORKS:

CREATE TEMPORARY TABLE qryPhone1 SELECT tstContacts.ContactID, IF(tstPhone.PhoneType='Work',tstPhone.PhoneNumber,'') AS WorkPhone FROM tstContacts LEFT JOIN tstPhone ON tstContacts.ContactID=tstPhone.ContactID Where tstPhone.PhoneType='Work';

CREATE TEMPORARY TABLE qryPhone2 SELECT tstContacts.ContactID, IF(tstPhone.PhoneType='Home',tstPhone.PhoneNumber,'') AS HomePhone FROM tstContacts LEFT JOIN tstPhone ON tstContacts.ContactID=tstPhone.ContactID Where tstPhone.PhoneType='Home';

CREATE TEMPORARY TABLE qryPhone3 SELECT tstContacts.ContactID,IF(tstPhone.PhoneType='Fax',tstPhone.PhoneNumber,'') AS FaxNumber FROM tstContacts LEFT JOIN tstPhone ON tstContacts.ContactID=tstPhone.ContactID Where tstPhone.PhoneType='Fax';

CREATE TEMPORARY TABLE qryPhone4 SELECT tstContacts.ContactID, IF(tstPhone.PhoneType='Pager',tstPhone.PhoneNumber,'') AS PagerNumber FROM tstContacts LEFT JOIN tstPhone ON tstContacts.ContactID=tstPhone.ContactID Where tstPhone.PhoneType='Pager';

CREATE TEMPORARY TABLE qryPhone5 SELECT tstContacts.ContactID, IF(tstPhone.PhoneType='Cell',tstPhone.PhoneNumber,'') AS MobileNumber FROM tstContacts LEFT JOIN tstPhone ON tstContacts.ContactID=tstPhone.ContactID Where tstPhone.PhoneType='Cell';

CREATE TEMPORARY TABLE qryPhone SELECT tstContacts.ContactID, qryPhone1.WorkPhone, qryPhone2.HomePhone, qryPhone3.FaxNumber, qryPhone4.PagerNumber, qryPhone5.MobileNumber FROM tstContacts LEFT JOIN qryPhone1 ON tstContacts.ContactID=qryPhone1.ContactID LEFT JOIN qryPhone2 ON tstContacts.ContactID=qryPhone2.ContactID LEFT JOIN qryPhone3 ON tstContacts.ContactID=qryPhone3.ContactID LEFT JOIN qryPhone4 ON tstContacts.ContactID=qryPhone4.ContactID LEFT JOIN qryPhone5 ON tstContacts.ContactID=qryPhone5.ContactID;

SELECT tstContacts.ContactID, qryPhone.WorkPhone, qryPhone.HomePhone, qryPhone.FaxNumber, qryPhone.PagerNumber, qryPhone.MobileNumber FROM tstContacts LEFT JOIN qryPhone ON tstContacts.ContactID=qryPhone.ContactID;

Fifth, to combine phone info and email info into a Thunderbird compatible addressbook flatfile, I combine the qryEmail, qryPhone, and tstContacts tables.

THIS DOES NOT WORK: