per query timeouts not respected

[Wijnand]

When running "ALTER TABLE" queries on a large table I discovered the given timeout was not respected. After 5 seconds I got an error: Mariaex.Protocol (#PID<0.3419.0>) disconnected: ** (Mariaex.Error) [tcp] recv failed with: :timeout

I did not try it with a direct connection to Mariaex, in this case I perform the queries via Ecto (and thus DBConnection). Ecto.Adapters.SQL.query!(MyApp.Repo, "select sleep(6)", [], timeout: 7000)

I traced the given timeout option through ecto and db_connection, but MariaEx.Protocol is where its get lost., in handle_execute to be precise.

I don't know what the correct solution should be so I have no pull request for you, sorry.

[fishcakez]

@Wijnand the issue is at: https://github.com/xerions/mariaex/blob/f4fe6f6f5f746f0ac9476e94891faf2fc915170b/lib/mariaex/protocol.ex#L1055. We should use an :infinity timeout here when doing a query/transactions. However we probably don't want to wait forever when handshaking or doing a ping. It might be helpful to see how postgrex handles this situation.

[fishcakez]

To be clear an :infinity timeout is ok when doing queries/transactions because DBConnection will trigger a disconnect/ socket close using a timer. This will interrupt the receive.

[cristianberneanu]

I encountered the same problem and fixed it by setting the timeout value in the start_link call.

[dbhobbs]

I believe I'm seeing this issue surface when modifying the schema of a larger table (~700_000 records) in an Ecto migration.

The alter table(:table_name) do ... end statement will timeout (even though Ecto migrations have a timeout of :infinity). This causes the migration to fail, however, because of implicit commits (https://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html) the schema alteration will have happened. The migration is attempted again (because it previously failed) and now it will continually fail with the error #{code => 1060, message => <<"Duplicate column name 'new_column_name'">>} requiring manual intervention to get the database back in sync with our migrations.

[capeskychung]

Have someone use mongodb have this problem : "Timeout while waiting for migration commit"

[fishcakez]

For DBConnection drivers we should always pass :infinity to socket receive and rely on DBConnection to handle the timeout when not inside connect/1. Inside connect/1 requires the driver to handle timeouts. #239 is an improvement but we are still not doing the correct thing.

[thawatchai]

@cristianberneanu Could you give me more details what you've done in the following?

I encountered the same problem and fixed it by setting the timeout value in the start_link call.

Thank you very much.

[cristianberneanu]

Somthing like this: {:ok, connection} = Mariaex.start_link(parameters ++ [timeout: :timer.hours(1)])