| Bug #83239 | Inconsistent nonaggregated field in SELECT not in GROUP BY | ||
|---|---|---|---|
| Submitted: | 2 Oct 2016 4:16 | Modified: | 6 Oct 2016 21:28 |
| Reporter: | James cobban | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 5.7.15-0ubuntu0.16.04.1 | OS: | Ubuntu (16.04) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | sql_mode=only_full_group_by | ||
[3 Oct 2016 20:50]
MySQL Verification Team
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
[3 Oct 2016 22:12]
James cobban
There is nothing in the documentation of this feature to explain why the error is given in one case and not in 10 other identical cases. There are no non-aggregated fields in the query. Perhaps the documentation of this feature is inadequate.
[3 Oct 2016 22:55]
MySQL Verification Team
Please read carefully the documentation. The columns: PT_Population, PT_Transcriber, PT_Proofreader are in the select list and not named in the GROUP BY clause according the Manual you could use the function ANY_VALUE():
C:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > "
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.17 Source distribution PULL: 2016-SEP-27
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 5.7 > use p1
Database changed
mysql 5.7 > SELECT Page, SUM(GivenNames != '') AS namecount, SUM(Age != '') AS agecount, SUM(IDIR != 0) AS idircount,
-> PT_Population, PT_Transcriber, PT_Proofreader FROM Census1911 JOIN Pages ON PT_Census='CA1911'
-> AND PT_DistId=District AND PT_SdId=SubDistrict AND PT_Div=Division AND PT_Sched='1' AND
-> PT_Page=Page WHERE District=66 AND SubDistrict='14' AND Division='' GROUP BY Page ORDER BY Page;
ERROR 1055 (42000): Expression #5 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'p1.Pages.PT_Population' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
mysql 5.7 > SELECT Page, SUM(GivenNames != '') AS namecount, SUM(Age != '') AS agecount, SUM(IDIR != 0) AS idircount,
-> ANY_VALUE(PT_Population), ANY_VALUE(PT_Transcriber),ANY_VALUE(PT_Proofreader) FROM Census1911 JOIN Pages ON PT_Census='CA1911'
-> AND PT_DistId=District AND PT_SdId=SubDistrict AND PT_Div=Division AND PT_Sched='1' AND
-> PT_Page=Page WHERE District=66 AND SubDistrict='14' AND Division='' GROUP BY Page ORDER BY Page;
Empty set (0.00 sec)
[3 Oct 2016 23:36]
James cobban
Please excuse me if you feel I am beating a dead horse. The issue I am reporting is NOT that the MySQL server is objecting to the SELECT statement. It is that the 5.7.15 server is INCONSISTENT in issuing the error message. The very first word in my synopsis is "Inconsistent". The documentation of the feature, which I have of course read several times prior to this ever since I deployed 5.7 on my development site, does clearly specify that the GROUP BY clause should include all non-aggregated columns. The only completely portable resolution, and the one that I have applied to resolve the issue on my site, is to add the columns to the GROUP BY as described by the documentation. ANY_VALUE is not an acceptable alternative in my opinion because it is not supported by any other web server, not even MariaDB. Of course MariaDB does not have an equivalent of only_full_group_by. If I were you I would not recommend ever suggesting a problem resolution that does not work at least in both MySQL and Oracle. Oracle is, after all, footing the bills. I would also not recommend the use of ANY_VALUE because its results are unpredictable, in particular depending upon the order in which records were originally loaded into the table, and unpredictability is the enemy of good design. In my view ANY_VALUE only exists in MySQL because it saves the sort implied by MIN or MAX or potentially responding with multiple records as a result of GROUP BY. My problem as a database developer was that I was not aware that there WAS an error in my GROUP BY clause until I, by chance, ran the code against the 5.7.15 server for that one specific table, since the command worked with TEN other otherwise identical tables, and my production site is running 5.5. From the point of view of the MySQL community issuing the error in ALL of the cases is probably optimal. But 5.7.15 does NOT issue the error in all cases, only in 9% of the cases I tested. Therefore 5.7.15 is "Inconsistent". Ralph Waldo Emerson may have said 'A foolish consistency is the hobgoblin of little minds', but after all computers, with their finite programming, are "little minds", and I cannot see consistency in the functionality of any computer system as "foolish".
[4 Oct 2016 0:53]
MySQL Verification Team
Sorry but there is not inconsistency with SQL Standard columns in the select list not named in the group by clause give that error. BTW below the error with similar query with Oracle 12C:
PT_Population, PT_Transcriber,
*
ERROR at line 1:
ORA-00979: not a GROUP BY expression
[4 Oct 2016 1:01]
James cobban
I apologize that I am not making myself clear. I do not understand your problem in comprehending the issue that I am raising. I am saying that MySQL 5.7 is inconsistent with ITSELF, NOT with the documentation, NOT with the standard, and NOT with other implementations of SQL. If MySQL 5.7 had reported the error in ALL eleven of the cases that I tested, or it if had reported it in NONE of the eleven cases, then the MySQL implementation would be consistent and I would not have raised this concern. But MySQL 5.7 reports the problem in only ONE out of ELEVEN cases, and THAT is inconsistent. Pick either ALL or NONE, but working 9% of the time (or 91% of the time if that is your choice) is not acceptable.
[4 Oct 2016 1:19]
MySQL Verification Team
Sorry for me there is not an inconsistency I tested only the query and test case in this bug report not all others your did. So I could say you the test case you have presented in this bug report is according our documentation as expected behavior.
[4 Oct 2016 15:34]
James cobban
Now you are being obtuse to the point of insult. I presented TWO almost identical cases in which MySQL gives different results, not a single case in which it generated an error. You have indicated your belief that MySQL was supposed to issue an error message for this situation. I have been clear from the very beginning that my concern was that MySQL was NOT issuing an error message in ALL cases, only in 9% of the equivalent cases. That is inconsistent. However I have been willing to accept two alternatives, that either MySQL should flag all cases, or that it should flag none, as it did prior to 5.7. Either option is acceptable to me. Reporting the problem in only 9% of the tested cases is NOT acceptable. I appreciate that you are trying to be helpful and are likely a volunteer like myself, but the objective of this mechanism is to improve MySQL and we cannot improve the product if we refuse to accept that it is imperfect. You can close this problem as often as you want. I will keep reopening it.
[4 Oct 2016 17:45]
MySQL Verification Team
Thank you for the feedback. C:\dbs>c:\dbs\5.7\bin\mysql -uroot -p --port=3570 --prompt="mysql 5.7 > " Enter password: ****** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.17 Source distribution PULL: 2016-SEP-27 Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql 5.7 > use p1 Database changed mysql 5.7 > SELECT Page, SUM(GivenNames != '') AS namecount, SUM(Age != '') AS agecount, SUM(IDIR != 0) AS idircount, PT_Population, PT_Transcriber, PT_Proofreader FROM Census1901 JOIN Pages ON PT_Census='CA1911' AND PT_DistId=District AND PT_SdId=SubDistrict AND PT_Div=Division AND PT_Sched='1' AND PT_Page=Page WHERE District=66 AND SubDistrict='14' AND Division='' GROUP BY Page ORDER BY Page; Empty set (0.08 sec) mysql 5.7 > SELECT Page, SUM(GivenNames != '') AS namecount, SUM(Age != '') AS agecount, SUM(IDIR != 0) AS idircount, PT_Population, PT_Transcriber, PT_Proofreader FROM Census1911 JOIN Pages ON PT_Census='CA1911' AND PT_DistId=District AND PT_SdId=SubDistrict AND PT_Div=Division AND PT_Sched='1' AND PT_Page=Page WHERE District=66 AND SubDistrict='14' AND Division='' GROUP BY Page ORDER BY Page; ERROR 1055 (42000): Expression #5 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'p1.Pages.PT_Population' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by mysql 5.7 >
[5 Oct 2016 8:51]
Guilhem Bichot
Hello James. It's not that Miguel is being obtuse; those queries are a bit hard to grasp. Even for an experienced MySQL Support guy like him. Simplified failing query: mysql> SELECT PT_Transcriber FROM Census1911, Pages where PT_Census='CA1911' AND PT_DistId='' AND PT_SdId='' AND PT_Div=Division AND PT_Sched='1' AND PT_Page=Page AND District=66 AND SubDistrict='14' AND Division='' GROUP BY Page; ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.Pages.PT_Transcriber' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by Now I silence the error, to see how MySQL has understood the query: mysql> set sql_mode=''; mysql> explain SELECT PT_Transcriber FROM Census1911, Pages where PT_Census='CA1911' AND PT_DistId='' AND PT_SdId='' AND PT_Div=Division AND PT_Sched='1' AND PT_Page=Page AND District=66 AND SubDistrict='14' AND Division='' GROUP BY Page; ... 2 rows in set, 4 warnings (0,00 sec) mysql> show warnings; | Note | 1003 | /* select#1 */ select `test`.`Pages`.`PT_Transcriber` AS `PT_Transcriber` from `test`.`Census1911` join `test`.`Pages` where ((`test`.`Census1911`.`Division` = '') and (`test`.`Pages`.`PT_Page` = `test`.`Census1911`.`Page`) and (`test`.`Pages`.`PT_Census` = 'CA1911') and (`test`.`Pages`.`PT_DistId` = '') and (`test`.`Pages`.`PT_SdId` = '') and (`test`.`Pages`.`PT_Div` = convert(`test`.`Census1911`.`Division` using utf8)) and (`test`.`Pages`.`PT_Sched` = '1') and (`test`.`Census1911`.`District` = 66) and (`test`.`Census1911`.`SubDistrict` = '14')) group by `test`.`Census1911`.`Page` (don't forget to 'set sql_mode=default;' after that) The last line has the only important piece of information: "and (`test`.`Pages`.`PT_Div` = convert(`test`.`Census1911`.`Division` using utf8))" CREATE TABLE doesn't specify any charset for Pages.PT_Div so it's probably utf8 (it is, on my system); Census1911.Division is: `Division` VARCHAR(4) CHARACTER SET latin1 NOT NULL DEFAULT '', ... latin1! So the equality condition in your query: PT_Div=Division compares utf8 to latin1 so contains an implicit charset conversion, hence the CONVERT() added by MySQL to make it explicit. Recap: - the condition "Division='' ", as every VARCHAR comparison, can theoretically return different matching values of Division (for example because that column, using CHARACTER SET latin1, has collation latin1_swedish_ci, which is case-insensitive; btw the collation is visible by querying INFORMATION_SCHEMA.COLUMNS); as we search for '' maybe it won't return two values, but if we searched for a letter it could (think, case-insensitivity); MySQL has to prepare for the worse case, so it must assume two matching latin1 values (or more) - convert(`Census1911`.`Division` using utf8) is a function, which can theoretically return different utf8 values for the two latin1 values of Division mentioned above - thus, `Pages`.`PT_Div` has two utf8 values which aren't necessarily equal from the POV of the PK index of Pages (which uses a utf8 collation for comparison), so this column isn't uniquely determined, thus we don't have a single row in Pages, hence the error. To give an analogy: PT_Div= Division-to-utf8 AND Division='' is like: PT_Div = (last digit of Division) AND Division is 1 or 2; so PT_Div isn't uniquely determined, If I do alter table Census1911 modify column Division varchar(4) NOT NULL DEFAULT ''; (which changes the column to utf8 on my system). then no more CONVERT(), a simple comparison of two strings having same charset, query works. Using the analogy again: PT_Div = Division AND Division='' is now like: PT_Div = Division AND Division is 1 or 2; So, PT_Div can still be 1 or 2, but then the PK index of Pages, which uses the same collation as the columns *and as the equality operator*, considers 1 and 2 equal so actually, as it's a PK, there cannot be 1 and 2 in the table, so PT_Div is uniquely determined. If that analogy with integers 1 and 2 confuses you, forget it. Census1901 precisely uses the default charset (probably utf8), so there is no charset conversion and no error. Bottom line: not a bug.
[6 Oct 2016 21:28]
James cobban
Thank you. A very good analysis.
Description: SELECT Page, SUM(GivenNames != '') AS namecount, SUM(Age != '') AS agecount, SUM(IDIR != 0) AS idircount, PT_Population, PT_Transcriber, PT_Proofreader FROM Census1911 JOIN Pages ON PT_Census='CA1911' AND PT_DistId=District AND PT_SdId=SubDistrict AND PT_Div=Division AND PT_Sched='1' AND PT_Page=Page WHERE District=66 AND SubDistrict='14' AND Division='' GROUP BY Page ORDER BY Page This fails with Array ( [0] => 42000 [1] => 1055 [2] => Expression #5 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'jcobban_genealogy.Pages.PT_Population' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by when the first table is Census1911, but not when it is any of 10 other tables that all have the same structure in terms of primary key fields, other indexes, and all of the fields referenced in the SELECT. The JOIN for table Pages identifies every field of the unique key of the table so there can be only one joined record: CREATE TABLE `Pages` ( `PT_Census` VARCHAR(6) NOT NULL DEFAULT 'CA1881', `PT_DistId` DECIMAL(4, 1) NOT NULL, `PT_SdId` VARCHAR(5) NOT NULL, `PT_Div` VARCHAR(4) NOT NULL DEFAULT '', `PT_Sched` CHAR(1) NOT NULL DEFAULT '1', `PT_Page` INT(4) NOT NULL DEFAULT '0', `PT_Population` INT(2) DEFAULT '25', `PT_Image` VARCHAR(128) DEFAULT '', `PT_Transcriber` VARCHAR(64) DEFAULT '', `PT_ProofReader` VARCHAR(64) DEFAULT '', PRIMARY KEY (`PT_Census`, `PT_DistId`, `PT_SdId`, `PT_Div`, `PT_Sched`, `PT_Page`), KEY `PT_Image` (`PT_Image`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 The declaration of Census1911, which fails, is: CREATE TABLE `Census1911` ( `District` DECIMAL(4,1) NOT NULL, `SubDistrict` DECIMAL(4,0) NOT NULL, `Division` VARCHAR(4) CHARACTER SET latin1 NOT NULL DEFAULT '', `Page` INT(11) NOT NULL, `Line` INT(11) NOT NULL, `Family` CHAR(6) CHARACTER SET latin1 NOT NULL, `Surname` VARCHAR(120) DEFAULT NULL, ... PRIMARY KEY (`District`, `SubDistrict`, `Division`, `Page`, `Line`), KEY `Surname` (`Surname`), KEY `SurnameSoundex` (`SurnameSoundex`), KEY `GivenNames` (`GivenNames`), KEY `Relation` (`Relation`), KEY `BYear` (`BYear`), KEY `BPlace` (`BPlace`), KEY `Origin` (`Origin`), KEY `Nationality` (`Nationality`), KEY `Religion` (`Religion`), KEY `Occupation` (`Occupation`), KEY `IDIR` (`IDIR`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 and the declaration of Census1901, which works, is: CREATE TABLE `Census1901` ( `District` DECIMAL(4,1) NOT NULL, `SubDistrict` VARCHAR(4) NOT NULL, `Division` VARCHAR(4) NOT NULL DEFAULT '', `Page` INT(11) NOT NULL, `Line` INT(11) NOT NULL, `Family` CHAR(6) NOT NULL, `Surname` VARCHAR(120) DEFAULT NULL, .... PRIMARY KEY (`District`, `SubDistrict`, `Division`, `Page`, `Line`), KEY `Surname` (`Surname`), KEY `SurnameSoundex` (`SurnameSoundex`), KEY `GivenNames` (`GivenNames`), KEY `Relation` (`Relation`), KEY `BYear` (`BYear`), KEY `BPlace` (`BPlace`), KEY `Origin` (`Origin`), KEY `Nationality` (`Nationality`), KEY `Religion` (`Religion`), KEY `Occupation` (`Occupation`), KEY `Religion_2` (`Religion`), KEY `IDIR` (`IDIR`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 and there are nine other tables Census1851 through Census1921 which all have the same structure and which MySQL does not object to. To get MySQL to accept the query I have to add "PT_Population, PT_Transcriber, PT_Proofreader" to the GROUP BY, but ONLY for Census1911. How to repeat: CREATE TABLE `Census1911` ( `District` DECIMAL(4, 1) NOT NULL, `SubDistrict` DECIMAL(4, 0) NOT NULL, `Division` VARCHAR(4) CHARACTER SET latin1 NOT NULL DEFAULT '', `Page` INT(11) NOT NULL, `Line` INT(11) NOT NULL, `Family` CHAR(6) CHARACTER SET latin1 NOT NULL, `Surname` VARCHAR(120) DEFAULT NULL, `SurnameSoundex` CHAR(4) CHARACTER SET latin1 DEFAULT NULL, `GivenNames` VARCHAR(120) DEFAULT NULL, `Address` VARCHAR(48) DEFAULT NULL, `Sex` CHAR(1) CHARACTER SET latin1 DEFAULT NULL, `Relation` VARCHAR(32) DEFAULT NULL, `MStat` CHAR(1) CHARACTER SET latin1 DEFAULT NULL, `BDate` VARCHAR(8) DEFAULT NULL, `BYearTxt` VARCHAR(8) DEFAULT NULL, `BYear` INT(11) DEFAULT NULL, `Age` VARCHAR(8) DEFAULT NULL, `BPlace` VARCHAR(32) DEFAULT NULL, `ImmYear` VARCHAR(4) DEFAULT NULL, `NatYear` VARCHAR(4) DEFAULT NULL, `Origin` VARCHAR(16) DEFAULT NULL, `Nationality` VARCHAR(16) DEFAULT NULL, `Religion` VARCHAR(32) DEFAULT NULL, `Occupation` VARCHAR(48) DEFAULT NULL, `OccOther` VARCHAR(24) DEFAULT NULL, `Employer` CHAR(1) CHARACTER SET latin1 DEFAULT '', `Employee` CHAR(1) CHARACTER SET latin1 DEFAULT '', `OwnAcct` CHAR(1) CHARACTER SET latin1 DEFAULT '', `EmpWhere` VARCHAR(128) DEFAULT NULL, `WksEmp` SMALLINT(2) DEFAULT '0', `WksOth` SMALLINT(2) DEFAULT '0', `HpWEmp` SMALLINT(2) DEFAULT '0', `HpWOth` SMALLINT(2) DEFAULT '0', `IncomeEmp` INT(8) DEFAULT '0', `IncomeOth` INT(8) DEFAULT '0', `Remarks` VARCHAR(255) DEFAULT NULL, `IDIR` INT(10) UNSIGNED DEFAULT NULL, PRIMARY KEY (`District`, `SubDistrict`, `Division`, `Page`, `Line`), KEY `Surname` (`Surname`), KEY `SurnameSoundex` (`SurnameSoundex`), KEY `GivenNames` (`GivenNames`), KEY `Relation` (`Relation`), KEY `BYear` (`BYear`), KEY `BPlace` (`BPlace`), KEY `Origin` (`Origin`), KEY `Nationality` (`Nationality`), KEY `Religion` (`Religion`), KEY `Occupation` (`Occupation`), KEY `IDIR` (`IDIR`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 CREATE TABLE `Census1901` ( `District` DECIMAL(4, 1) NOT NULL, `SubDistrict` VARCHAR(4) NOT NULL, `Division` VARCHAR(4) NOT NULL DEFAULT '', `Page` INT(11) NOT NULL, `Line` INT(11) NOT NULL, `Family` CHAR(6) NOT NULL, `Surname` VARCHAR(120) DEFAULT NULL, `SurnameSoundex` CHAR(4) DEFAULT NULL, `GivenNames` VARCHAR(120) DEFAULT NULL, `Sex` CHAR(1) DEFAULT NULL, `Race` CHAR(1) DEFAULT NULL, `Relation` VARCHAR(32) DEFAULT NULL, `MStat` CHAR(1) DEFAULT NULL, `BDate` VARCHAR(8) DEFAULT NULL, `BYearTxt` VARCHAR(8) DEFAULT NULL, `BYear` INT(11) DEFAULT NULL, `Age` VARCHAR(8) DEFAULT NULL, `BPlace` VARCHAR(32) DEFAULT NULL, `BPlaceRu` CHAR(1) DEFAULT NULL, `ImmYear` VARCHAR(4) DEFAULT NULL, `NatYear` VARCHAR(4) DEFAULT NULL, `Origin` VARCHAR(16) DEFAULT NULL, `Nationality` VARCHAR(16) DEFAULT NULL, `Religion` VARCHAR(32) DEFAULT NULL, `Occupation` VARCHAR(48) DEFAULT NULL, `Address` VARCHAR(48) DEFAULT NULL, `Remarks` VARCHAR(255) DEFAULT NULL, `IDIR` INT(10) UNSIGNED DEFAULT NULL, `OwnMeans` CHAR(1) DEFAULT '', `Employer` CHAR(1) DEFAULT '', `Employee` CHAR(1) DEFAULT '', `OwnAcct` CHAR(1) DEFAULT '', `FactHome` VARCHAR(2) DEFAULT ' ', `MonthsFact` SMALLINT(2) DEFAULT '0', `MonthsHome` SMALLINT(2) DEFAULT '0', `MonthsOther` SMALLINT(2) DEFAULT '0', `IncomeEmp` INT(8) DEFAULT '0', `IncomeOth` INT(8) DEFAULT '0', `MonthsSchool` SMALLINT(2) DEFAULT '0', `CanRead` CHAR(1) NOT NULL DEFAULT '', `CanWrite` CHAR(1) NOT NULL DEFAULT '', `SpkEnglish` CHAR(1) DEFAULT '1', `SpkFrench` CHAR(1) DEFAULT '', `MotherTongue` VARCHAR(16) DEFAULT '', `Infirmities` VARCHAR(50) DEFAULT NULL, PRIMARY KEY (`District`, `SubDistrict`, `Division`, `Page`, `Line`), KEY `Surname` (`Surname`), KEY `SurnameSoundex` (`SurnameSoundex`), KEY `GivenNames` (`GivenNames`), KEY `Relation` (`Relation`), KEY `BYear` (`BYear`), KEY `BPlace` (`BPlace`), KEY `Origin` (`Origin`), KEY `Nationality` (`Nationality`), KEY `Religion` (`Religion`), KEY `Occupation` (`Occupation`), KEY `Religion_2` (`Religion`), KEY `IDIR` (`IDIR`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 CREATE TABLE `Pages` ( `PT_Census` VARCHAR(6) NOT NULL DEFAULT 'CA1881', `PT_DistId` DECIMAL(4, 1) NOT NULL, `PT_SdId` VARCHAR(5) NOT NULL, `PT_Div` VARCHAR(4) NOT NULL DEFAULT '', `PT_Sched` CHAR(1) NOT NULL DEFAULT '1', `PT_Page` INT(4) NOT NULL DEFAULT '0', `PT_Population` INT(2) DEFAULT '25', `PT_Image` VARCHAR(128) DEFAULT '', `PT_Transcriber` VARCHAR(64) DEFAULT '', `PT_ProofReader` VARCHAR(64) DEFAULT '', PRIMARY KEY (`PT_Census`, `PT_DistId`, `PT_SdId`, `PT_Div`, `PT_Sched`, `PT_Page`), KEY `PT_Image` (`PT_Image`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO Pages VALUES("CA1911",66.0,14,,1,1, 50,"http://data2.collectionscanada.gc.ca/1911/jpg/e001984068.jpg,"jcobban",,); INSERT INTO Pages VALUES("CA1911",66.0,14,,1,2, 49,"http://data2.collectionscanada.gc.ca/1911/jpg/e001984069.jpg,"jcobban",,); INSERT INTO Pages VALUES("CA1911",66.0,14,,1,3, 49,"http://data2.collectionscanada.gc.ca/1911/jpg/e001984070.jpg,"jcobban",,); INSERT INTO Pages VALUES("CA1911",66.0,14,,1,4, 50,"http://data2.collectionscanada.gc.ca/1911/jpg/e001984071.jpg,"jcobban",,); INSERT INTO Pages VALUES("CA1911",66.0,14,,1,5, 50,"http://data2.collectionscanada.gc.ca/1911/jpg/e001984072.jpg,"jcobban",,); INSERT INTO Pages VALUES("CA1911",66.0,14,,1,6, 23,"http://data2.collectionscanada.gc.ca/1911/jpg/e001984073.jpg,"jcobban",,);