Query causes MySQL.jl to hang [Julia v0.7]

[essenciary]

I have an app where I run multiple queries on various table. They all run correctly, except this. This query causes the REPL to hang (the cursor moves on ENTER but no ctrl+c or ctrl+d to exit). As far as I can tell it hangs forever.

julia> MySQL.query(conn, "SELECT `hotels`.`id` AS `hotels_id`, `hotels`.`facilities` AS `hotels_facilities`, `hotels`.`themes` AS `hotels_themes`, `hotels`.`nr_rooms` AS `hotels_nr_rooms`, `hotels`.`country` AS `hotels_country`, `hotels`.`destination` AS `hotels_destination`, `hotels`.`nr_bars` AS `hotels_nr_bars`, `hotels`.`hotel_score` AS `hotels_hotel_score`, `hotels`.`nr_restaurants` AS `hotels_nr_restaurants`, `hotels`.`availability_score` AS `hotels_availability_score`, `hotels`.`checkin_to` AS `hotels_checkin_to`, `hotels`.`checkout_from` AS `hotels_checkout_from`, `hotels`.`address` AS `hotels_address`, `hotels`.`zipcode` AS `hotels_zipcode`, `hotels`.`currencycode` AS `hotels_currencycode`, `hotels`.`regions` AS `hotels_regions`, `hotels`.`checkin_from` AS `hotels_checkin_from`, `hotels`.`phone` AS `hotels_phone`, `hotels`.`descriptions` AS `hotels_descriptions`, `hotels`.`longitude` AS `hotels_longitude`, `hotels`.`name` AS `hotels_name`, `hotels`.`email` AS `hotels_email`, `hotels`.`latitude` AS `hotels_latitude`, `hotels`.`updated_at` AS `hotels_updated_at`, `hotels`.`master` AS `hotels_master`, `hotels`.`nr_halls` AS `hotels_nr_halls`, `hotels`.`stars` AS `hotels_stars`, `hotels`.`code` AS `hotels_code`, `hotels`.`checkout_to` AS `hotels_checkout_to`, `hotels`.`year_built` AS `hotels_year_built` FROM `hotels` WHERE (`hotels`.`id` = 218) ORDER BY hotels.id ASC LIMIT 1")

julia> versioninfo()
Julia Version 0.7.0-alpha.218
Commit 3477f29a19 (2018-06-21 14:32 UTC)
Platform Info:
  OS: macOS (x86_64-apple-darwin14.5.0)
  CPU: Intel(R) Core(TM) i7-6820HQ CPU @ 2.70GHz
  WORD_SIZE: 64
  LIBM: libopenlibm
  LLVM: libLLVM-6.0.0 (ORCJIT, skylake)

MySQL 5.7.22 running on localhost. Table attached
hotelhound+dev_2018-06-21.sql.zip

[essenciary]

I kept digging in order to check if maybe the query string is too long, or too many fields -- or maybe if one of the fields (data type, name, etc) is problematic.

Reducing the query to only this, consistently works:

MySQL.query(conn, "SELECT `hotels`.`id` FROM `hotels` WHERE (`hotels`.`id` = 218) ORDER BY hotels.id ASC LIMIT 1")

(id = Int32[218],)

This longer query also works:

MySQL.query(conn, "SELECT `hotels`.`id` AS `hotels_id`, `hotels`.`facilities` AS `hotels_facilities`, `hotels`.`themes` AS `hotels_themes`, `hotels`.`nr_rooms` AS `hotels_nr_rooms`, `hotels`.`country` AS `hotels_country`, `hotels`.`destination` AS `hotels_destination`, `hotels`.`nr_bars` AS `hotels_nr_bars`, `hotels`.`hotel_score` AS `hotels_hotel_score`, `hotels`.`nr_restaurants` AS `hotels_nr_restaurants`, `hotels`.`availability_score` AS `hotels_availability_score`, `hotels`.`checkin_to` AS `hotels_checkin_to`, `hotels`.`checkout_from` AS `hotels_checkout_from`, `hotels`.`address` AS `hotels_address`, `hotels`.`zipcode` AS `hotels_zipcode`, `hotels`.`currencycode` AS `hotels_currencycode`, `hotels`.`regions` AS `hotels_regions` FROM `hotels` WHERE (`hotels`.`id` = 218) ORDER BY hotels.id ASC LIMIT 1")

(hotels_id = Int32[218], hotels_facilities = Union{Missing, String}["[]"], hotels_themes = Union{Missing, String}["[]"], hotels_nr_rooms = Union{Missing, Int32}[0], hotels_country = Union{Missing, String}["co"], hotels_destination = Union{Missing, String}["19657"], hotels_nr_bars = Union{Missing, Int32}[0], hotels_hotel_score = Union{Missing, Float32}[0.0], hotels_nr_restaurants = Union{Missing, Int32}[0], hotels_availability_score = Union{Missing, Float32}[0.0], hotels_checkin_to = Union{Missing, String}[""], hotels_checkout_from = Union{Missing, String}[""], hotels_address = Union{Missing, String}["Carrera 17 B No. 54-34"], hotels_zipcode = Union{Missing, String}[""], hotels_currencycode = Union{Missing, String}[""], hotels_regions = Union{Missing, String}["[\"1523\"]"])

However, this works only sometimes -- after multiple tests, it looks like if I run this query right away (so right after starting a Julia session), it will hang. However, if I run the query after the above simpler queries which do work, this will also return:

MySQL.query(conn, "SELECT `hotels`.`id` AS `hotels_id`, `hotels`.`facilities` AS `hotels_facilities`, `hotels`.`themes` AS `hotels_themes`, `hotels`.`nr_rooms` AS `hotels_nr_rooms`, `hotels`.`country` AS `hotels_country`, `hotels`.`destination` AS `hotels_destination`, `hotels`.`nr_bars` AS `hotels_nr_bars`, `hotels`.`hotel_score` AS `hotels_hotel_score`, `hotels`.`nr_restaurants` AS `hotels_nr_restaurants`, `hotels`.`availability_score` AS `hotels_availability_score`, `hotels`.`checkin_to` AS `hotels_checkin_to`, `hotels`.`checkout_from` AS `hotels_checkout_from`, `hotels`.`address` AS `hotels_address`, `hotels`.`zipcode` AS `hotels_zipcode`, `hotels`.`currencycode` AS `hotels_currencycode`, `hotels`.`regions` AS `hotels_regions`, `hotels`.`checkin_from` AS `hotels_checkin_from`, `hotels`.`phone` AS `hotels_phone`, `hotels`.`descriptions` AS `hotels_descriptions`, `hotels`.`longitude` AS `hotels_longitude`, `hotels`.`name` AS `hotels_name`, `hotels`.`email` AS `hotels_email`, `hotels`.`latitude` AS `hotels_latitude` FROM `hotels` WHERE (`hotels`.`id` = 218) ORDER BY hotels.id ASC LIMIT 1")

(hotels_id = Int32[218], hotels_facilities = Union{Missing, String}["[]"], hotels_themes = Union{Missing, String}["[]"], hotels_nr_rooms = Union{Missing, Int32}[0], hotels_country = Union{Missing, String}["co"], hotels_destination = Union{Missing, String}["19657"], hotels_nr_bars = Union{Missing, Int32}[0], hotels_hotel_score = Union{Missing, Float32}[0.0], hotels_nr_restaurants = Union{Missing, Int32}[0], hotels_availability_score = Union{Missing, Float32}[0.0], hotels_checkin_to = Union{Missing, String}[""], hotels_checkout_from = Union{Missing, String}[""], hotels_address = Union{Missing, String}["Carrera 17 B No. 54-34"], hotels_zipcode = Union{Missing, String}[""], hotels_currencycode = Union{Missing, String}[""], hotels_regions = Union{Missing, String}["[\"1523\"]"], hotels_checkin_from = Union{Missing, String}[""], hotels_phone = Union{Missing, String}[""], hotels_descriptions = Union{Missing, String}["null"], hotels_longitude = Union{Missing, Float32}[-73.1], hotels_name = Union{Missing, String}["Hotel El Saman"], hotels_email = Union{Missing, String}[""], hotels_latitude = Union{Missing, Float32}[7.11])

[essenciary]

However, the slightly longer version of the query does not work:

MySQL.query(conn, "SELECT `hotels`.`id` AS `hotels_id`, `hotels`.`facilities` AS `hotels_facilities`, `hotels`.`themes` AS `hotels_themes`, `hotels`.`nr_rooms` AS `hotels_nr_rooms`, `hotels`.`country` AS `hotels_country`, `hotels`.`destination` AS `hotels_destination`, `hotels`.`nr_bars` AS `hotels_nr_bars`, `hotels`.`hotel_score` AS `hotels_hotel_score`, `hotels`.`nr_restaurants` AS `hotels_nr_restaurants`, `hotels`.`availability_score` AS `hotels_availability_score`, `hotels`.`checkin_to` AS `hotels_checkin_to`, `hotels`.`checkout_from` AS `hotels_checkout_from`, `hotels`.`address` AS `hotels_address`, `hotels`.`zipcode` AS `hotels_zipcode`, `hotels`.`currencycode` AS `hotels_currencycode`, `hotels`.`regions` AS `hotels_regions`, `hotels`.`checkin_from` AS `hotels_checkin_from`, `hotels`.`phone` AS `hotels_phone`, `hotels`.`descriptions` AS `hotels_descriptions`, `hotels`.`longitude` AS `hotels_longitude`, `hotels`.`name` AS `hotels_name`, `hotels`.`email` AS `hotels_email`, `hotels`.`latitude` AS `hotels_latitude`, `hotels`.`updated_at` AS `hotels_updated_at`, `hotels`.`master` AS `hotels_master`, `hotels`.`nr_halls` AS `hotels_nr_halls`, `hotels`.`stars` AS `hotels_stars`, `hotels`.`code` AS `hotels_code`, `hotels`.`checkout_to` AS `hotels_checkout_to`, `hotels`.`year_built` AS `hotels_year_built` FROM `hotels` WHERE (`hotels`.`id` = 218) ORDER BY hotels.id ASC LIMIT 1")

It hangs and looking in the Activity Monitor, the Julia process is using around 100% CPU

[essenciary]

It looks like running a query for the first time uses HUUUUUGE resources. And the longer the query (more columns), the crazier the number of resources used.

julia> @time MySQL.query(conn, "SELECT `hotels`.`id` FROM `hotels` WHERE (`hotels`.`id` = 218) ORDER BY hotels.id ASC LIMIT 1")
  3.279884 seconds (7.26 M allocations: 365.009 MiB, 4.10% gc time)
(id = Int32[218],)

julia> @time MySQL.query(conn, "SELECT `hotels`.`id` FROM `hotels` WHERE (`hotels`.`id` = 218) ORDER BY hotels.id ASC LIMIT 1")
  0.000446 seconds (223 allocations: 14.875 KiB)
(id = Int32[218],)

julia> @time MySQL.query(conn, "SELECT `hotels`.`id` AS `hotels_id`, `hotels`.`facilities` AS `hotels_facilities`, `hotels`.`themes` AS `hotels_themes`, `hotels`.`nr_rooms` AS `hotels_nr_rooms`, `hotels`.`country` AS `hotels_country`, `hotels`.`destination` AS `hotels_destination`, `hotels`.`nr_bars` AS `hotels_nr_bars`, `hotels`.`hotel_score` AS `hotels_hotel_score`, `hotels`.`nr_restaurants` AS `hotels_nr_restaurants`, `hotels`.`availability_score` AS `hotels_availability_score`, `hotels`.`checkin_to` AS `hotels_checkin_to`, `hotels`.`checkout_from` AS `hotels_checkout_from`, `hotels`.`address` AS `hotels_address`, `hotels`.`zipcode` AS `hotels_zipcode`, `hotels`.`currencycode` AS `hotels_currencycode`, `hotels`.`regions` AS `hotels_regions` FROM `hotels` WHERE (`hotels`.`id` = 218) ORDER BY hotels.id ASC LIMIT 1")
  1.853213 seconds (6.09 M allocations: 350.277 MiB, 2.80% gc time)
(hotels_id = Int32[218], hotels_facilities = Union{Missing, String}["[]"], hotels_themes = Union{Missing, String}["[]"], hotels_nr_rooms = Union{Missing, Int32}[0], hotels_country = Union{Missing, String}["co"], hotels_destination = Union{Missing, String}["19657"], hotels_nr_bars = Union{Missing, Int32}[0], hotels_hotel_score = Union{Missing, Float32}[0.0], hotels_nr_restaurants = Union{Missing, Int32}[0], hotels_availability_score = Union{Missing, Float32}[0.0], hotels_checkin_to = Union{Missing, String}[""], hotels_checkout_from = Union{Missing, String}[""], hotels_address = Union{Missing, String}["Carrera 17 B No. 54-34"], hotels_zipcode = Union{Missing, String}[""], hotels_currencycode = Union{Missing, String}[""], hotels_regions = Union{Missing, String}["[\"1523\"]"])

julia> @time MySQL.query(conn, "SELECT `hotels`.`id` AS `hotels_id`, `hotels`.`facilities` AS `hotels_facilities`, `hotels`.`themes` AS `hotels_themes`, `hotels`.`nr_rooms` AS `hotels_nr_rooms`, `hotels`.`country` AS `hotels_country`, `hotels`.`destination` AS `hotels_destination`, `hotels`.`nr_bars` AS `hotels_nr_bars`, `hotels`.`hotel_score` AS `hotels_hotel_score`, `hotels`.`nr_restaurants` AS `hotels_nr_restaurants`, `hotels`.`availability_score` AS `hotels_availability_score`, `hotels`.`checkin_to` AS `hotels_checkin_to`, `hotels`.`checkout_from` AS `hotels_checkout_from`, `hotels`.`address` AS `hotels_address`, `hotels`.`zipcode` AS `hotels_zipcode`, `hotels`.`currencycode` AS `hotels_currencycode`, `hotels`.`regions` AS `hotels_regions` FROM `hotels` WHERE (`hotels`.`id` = 218) ORDER BY hotels.id ASC LIMIT 1")
  0.001031 seconds (1.55 k allocations: 87.172 KiB)
(hotels_id = Int32[218], hotels_facilities = Union{Missing, String}["[]"], hotels_themes = Union{Missing, String}["[]"], hotels_nr_rooms = Union{Missing, Int32}[0], hotels_country = Union{Missing, String}["co"], hotels_destination = Union{Missing, String}["19657"], hotels_nr_bars = Union{Missing, Int32}[0], hotels_hotel_score = Union{Missing, Float32}[0.0], hotels_nr_restaurants = Union{Missing, Int32}[0], hotels_availability_score = Union{Missing, Float32}[0.0], hotels_checkin_to = Union{Missing, String}[""], hotels_checkout_from = Union{Missing, String}[""], hotels_address = Union{Missing, String}["Carrera 17 B No. 54-34"], hotels_zipcode = Union{Missing, String}[""], hotels_currencycode = Union{Missing, String}[""], hotels_regions = Union{Missing, String}["[\"1523\"]"])

julia> @time MySQL.query(conn, "SELECT `hotels`.`id` AS `hotels_id`, `hotels`.`facilities` AS `hotels_facilities`, `hotels`.`themes` AS `hotels_themes`, `hotels`.`nr_rooms` AS `hotels_nr_rooms`, `hotels`.`country` AS `hotels_country`, `hotels`.`destination` AS `hotels_destination`, `hotels`.`nr_bars` AS `hotels_nr_bars`, `hotels`.`hotel_score` AS `hotels_hotel_score`, `hotels`.`nr_restaurants` AS `hotels_nr_restaurants`, `hotels`.`availability_score` AS `hotels_availability_score`, `hotels`.`checkin_to` AS `hotels_checkin_to`, `hotels`.`checkout_from` AS `hotels_checkout_from`, `hotels`.`address` AS `hotels_address`, `hotels`.`zipcode` AS `hotels_zipcode`, `hotels`.`currencycode` AS `hotels_currencycode`, `hotels`.`regions` AS `hotels_regions`, `hotels`.`checkin_from` AS `hotels_checkin_from`, `hotels`.`phone` AS `hotels_phone`, `hotels`.`descriptions` AS `hotels_descriptions`, `hotels`.`longitude` AS `hotels_longitude`, `hotels`.`name` AS `hotels_name`, `hotels`.`email` AS `hotels_email`, `hotels`.`latitude` AS `hotels_latitude` FROM `hotels` WHERE (`hotels`.`id` = 218) ORDER BY hotels.id ASC LIMIT 1")
 44.534217 seconds (202.54 M allocations: 12.060 GiB, 0.75% gc time)
(hotels_id = Int32[218], hotels_facilities = Union{Missing, String}["[]"], hotels_themes = Union{Missing, String}["[]"], hotels_nr_rooms = Union{Missing, Int32}[0], hotels_country = Union{Missing, String}["co"], hotels_destination = Union{Missing, String}["19657"], hotels_nr_bars = Union{Missing, Int32}[0], hotels_hotel_score = Union{Missing, Float32}[0.0], hotels_nr_restaurants = Union{Missing, Int32}[0], hotels_availability_score = Union{Missing, Float32}[0.0], hotels_checkin_to = Union{Missing, String}[""], hotels_checkout_from = Union{Missing, String}[""], hotels_address = Union{Missing, String}["Carrera 17 B No. 54-34"], hotels_zipcode = Union{Missing, String}[""], hotels_currencycode = Union{Missing, String}[""], hotels_regions = Union{Missing, String}["[\"1523\"]"], hotels_checkin_from = Union{Missing, String}[""], hotels_phone = Union{Missing, String}[""], hotels_descriptions = Union{Missing, String}["null"], hotels_longitude = Union{Missing, Float32}[-73.1], hotels_name = Union{Missing, String}["Hotel El Saman"], hotels_email = Union{Missing, String}[""], hotels_latitude = Union{Missing, Float32}[7.11])

julia> @time MySQL.query(conn, "SELECT `hotels`.`id` AS `hotels_id`, `hotels`.`facilities` AS `hotels_facilities`, `hotels`.`themes` AS `hotels_themes`, `hotels`.`nr_rooms` AS `hotels_nr_rooms`, `hotels`.`country` AS `hotels_country`, `hotels`.`destination` AS `hotels_destination`, `hotels`.`nr_bars` AS `hotels_nr_bars`, `hotels`.`hotel_score` AS `hotels_hotel_score`, `hotels`.`nr_restaurants` AS `hotels_nr_restaurants`, `hotels`.`availability_score` AS `hotels_availability_score`, `hotels`.`checkin_to` AS `hotels_checkin_to`, `hotels`.`checkout_from` AS `hotels_checkout_from`, `hotels`.`address` AS `hotels_address`, `hotels`.`zipcode` AS `hotels_zipcode`, `hotels`.`currencycode` AS `hotels_currencycode`, `hotels`.`regions` AS `hotels_regions`, `hotels`.`checkin_from` AS `hotels_checkin_from`, `hotels`.`phone` AS `hotels_phone`, `hotels`.`descriptions` AS `hotels_descriptions`, `hotels`.`longitude` AS `hotels_longitude`, `hotels`.`name` AS `hotels_name`, `hotels`.`email` AS `hotels_email`, `hotels`.`latitude` AS `hotels_latitude` FROM `hotels` WHERE (`hotels`.`id` = 218) ORDER BY hotels.id ASC LIMIT 1")
  0.005150 seconds (2.64 k allocations: 143.609 KiB)
(hotels_id = Int32[218], hotels_facilities = Union{Missing, String}["[]"], hotels_themes = Union{Missing, String}["[]"], hotels_nr_rooms = Union{Missing, Int32}[0], hotels_country = Union{Missing, String}["co"], hotels_destination = Union{Missing, String}["19657"], hotels_nr_bars = Union{Missing, Int32}[0], hotels_hotel_score = Union{Missing, Float32}[0.0], hotels_nr_restaurants = Union{Missing, Int32}[0], hotels_availability_score = Union{Missing, Float32}[0.0], hotels_checkin_to = Union{Missing, String}[""], hotels_checkout_from = Union{Missing, String}[""], hotels_address = Union{Missing, String}["Carrera 17 B No. 54-34"], hotels_zipcode = Union{Missing, String}[""], hotels_currencycode = Union{Missing, String}[""], hotels_regions = Union{Missing, String}["[\"1523\"]"], hotels_checkin_from = Union{Missing, String}[""], hotels_phone = Union{Missing, String}[""], hotels_descriptions = Union{Missing, String}["null"], hotels_longitude = Union{Missing, Float32}[-73.1], hotels_name = Union{Missing, String}["Hotel El Saman"], hotels_email = Union{Missing, String}[""], hotels_latitude = Union{Missing, Float32}[7.11])

[quinnj]

The package has gone through a lot of change since this was originally supported; in #139, I'm overhauling the package yet again w/ a focus on safety and reliability, so we shouldn't see these issues any more.