--gtfs-zip error failing to build files and zip file

[mshodge]

Another issue sorry!

--gtfs-zip seems to throw up the following errors:

Writing agency.txt (node:12658) UnhandledPromiseRejectionWarning: Error: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 't1.tiploc_code' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by at PromisePool.query (/usr/local/lib/node_modules/dtd2mysql/node_modules/mysql2/promise.js:344:20) at CIFRepository.getStops (/usr/local/lib/node_modules/dtd2mysql/dist/src/gtfs/repository/CIFRepository.js:35:41) at OutputGTFSCommand.run (/usr/local/lib/node_modules/dtd2mysql/dist/src/cli/OutputGTFSCommand.js:26:50) at OutputGTFSZipCommand.run (/usr/local/lib/node_modules/dtd2mysql/dist/src/cli/OutputGTFSZipCommand.js:18:28) at container.getCommand.then.c (/usr/local/lib/node_modules/dtd2mysql/dist/src/index.js:7:18) at <anonymous> at process._tickCallback (internal/process/next_tick.js:188:7) at Function.Module.runMain (module.js:695:11) at startup (bootstrap_node.js:191:16) at bootstrap_node.js:612:3 (node:12658) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). (rejection id: 1) (node:12658) [DEP0018] DeprecationWarning: Unhandled promise rejections are deprecated. In the future, promise rejections that are not handled will terminate the Node.js process with a non-zero exit code. (node:12658) UnhandledPromiseRejectionWarning: Error: Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'trains.physical_station.minimum_change_time' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by at PromisePool.query (/usr/local/lib/node_modules/dtd2mysql/node_modules/mysql2/promise.js:344:20) at CIFRepository.getTransfers (/usr/local/lib/node_modules/dtd2mysql/dist/src/gtfs/repository/CIFRepository.js:20:41) at OutputGTFSCommand.run (/usr/local/lib/node_modules/dtd2mysql/dist/src/cli/OutputGTFSCommand.js:25:54) at OutputGTFSZipCommand.run (/usr/local/lib/node_modules/dtd2mysql/dist/src/cli/OutputGTFSZipCommand.js:18:28) at container.getCommand.then.c (/usr/local/lib/node_modules/dtd2mysql/dist/src/index.js:7:18) at <anonymous> at process._tickCallback (internal/process/next_tick.js:188:7) at Function.Module.runMain (module.js:695:11) at startup (bootstrap_node.js:191:16) at bootstrap_node.js:612:3 (node:12658) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). (rejection id: 2) Writing links.txt Writing trips.txt, stop_times.txt and routes.txt Writing calendar.txt Writing calendar_dates.txt { Error: Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'trains.physical_station.minimum_change_time' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by at PromisePool.query (/usr/local/lib/node_modules/dtd2mysql/node_modules/mysql2/promise.js:344:20) at CIFRepository.getTransfers (/usr/local/lib/node_modules/dtd2mysql/dist/src/gtfs/repository/CIFRepository.js:20:41) at OutputGTFSCommand.run (/usr/local/lib/node_modules/dtd2mysql/dist/src/cli/OutputGTFSCommand.js:25:54) at OutputGTFSZipCommand.run (/usr/local/lib/node_modules/dtd2mysql/dist/src/cli/OutputGTFSZipCommand.js:18:28) at container.getCommand.then.c (/usr/local/lib/node_modules/dtd2mysql/dist/src/index.js:7:18) at <anonymous> at process._tickCallback (internal/process/next_tick.js:188:7) at Function.Module.runMain (module.js:695:11) at startup (bootstrap_node.js:191:16) at bootstrap_node.js:612:3 message: 'Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column \'trains.physical_station.minimum_change_time\' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by', code: 'ER_WRONG_FIELD_WITH_GROUP', errno: 1055, sqlState: '42000', sqlMessage: 'Expression #4 of SELECT list is not in GROUP BY clause and contains nonaggregated column \'trains.physical_station.minimum_change_time\' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by' } (node:12658) PromiseRejectionHandledWarning: Promise rejection was handled asynchronously (rejection id: 2) (node:12658) PromiseRejectionHandledWarning: Promise rejection was handled asynchronously (rejection id: 1)

As a result the some text files, such as the stops.txt, are not created. Thus, the zip file is not created.

Michael

[linusnorton]

I just ran this through with MariaDB locally and it works. It might be a difference between MariaDB/MySQL or a config option. What version are you using?

[mshodge]

Hi there,

Running on:

mysql Ver 8.0.11 for macos10.13 on x86_64 (MySQL Community Server - GPL) macOS Sierra 10.12.2 node version v8.11.2

[linusnorton]

I've looked into the ONLY_FULL_GROUP_BY and unfortunately I think I would have to rework too many queries to make it work with that option set.

If you want to disable that option you can do so in your my.conf or per session with SET @@SQL_MODE = REPLACE(@@SQL_MODE, 'ONLY_FULL_GROUP_BY', '');

[mshodge]

Thanks Linus, all is working now on the conversion. Had some trouble using it in OpenTripPlanner, had to clean some of the GTFS files (mainly removing services in the stops_time file where stations were present in the stop file - typically because some of the trips weren't passenger trips in the latest timetable data, but were journeys from service depots). Thanks for your help on this though, great to have a working train GTFS!

[linusnorton]

Thanks, I have a fairly in-depth blog post about the conversion if you are interested: https://ljn.io/posts/CIF-to-GTFS/

Regarding those stops, I should do something about it. Essentially there are stops in the tiploc data with a CRS code that do not appear in the physical stations data. I'm not sure if that's an error on their end that will be corrected or something that is considered normal.