qt 连接 mysql 数据库超时

素颜马尾好姑娘i 2023-02-11 07:13 43阅读 0赞

mysql> show variables like '%timeout';
    +-----------------------------------+----------+
    | Variable_name                     | Value    |
    +-----------------------------------+----------+
    | connect_timeout                   | 10       |
    | delayed_insert_timeout            | 300      |
    | have_statement_timeout            | YES      |
    | innodb_flush_log_at_timeout       | 1        |
    | innodb_lock_wait_timeout          | 50       |
    | innodb_rollback_on_timeout        | OFF      |
    | interactive_timeout               | 28800    |
    | lock_wait_timeout                 | 31536000 |
    | mysqlx_connect_timeout            | 30       |
    | mysqlx_idle_worker_thread_timeout | 60       |
    | mysqlx_interactive_timeout        | 28800    |
    | mysqlx_port_open_timeout          | 0        |
    | mysqlx_read_timeout               | 30       |
    | mysqlx_wait_timeout               | 28800    |
    | mysqlx_write_timeout              | 60       |
    | net_read_timeout                  | 30       |
    | net_write_timeout                 | 60       |
    | rpl_stop_slave_timeout            | 31536000 |
    | slave_net_timeout                 | 60       |
    | wait_timeout                      | 28800    |
    +-----------------------------------+----------+
    20 rows in set, 1 warning (0.00 sec)

enum mysql_option {
      MYSQL_OPT_CONNECT_TIMEOUT,
      MYSQL_OPT_COMPRESS,
      MYSQL_OPT_NAMED_PIPE,
      MYSQL_INIT_COMMAND,
      MYSQL_READ_DEFAULT_FILE,
      MYSQL_READ_DEFAULT_GROUP,
      MYSQL_SET_CHARSET_DIR,
      MYSQL_SET_CHARSET_NAME,
      MYSQL_OPT_LOCAL_INFILE,
      MYSQL_OPT_PROTOCOL,
      MYSQL_SHARED_MEMORY_BASE_NAME,
      MYSQL_OPT_READ_TIMEOUT,
      MYSQL_OPT_WRITE_TIMEOUT,
      MYSQL_OPT_USE_RESULT,
      MYSQL_REPORT_DATA_TRUNCATION,
      MYSQL_OPT_RECONNECT,
      MYSQL_PLUGIN_DIR,
      MYSQL_DEFAULT_AUTH,
      MYSQL_OPT_BIND,
      MYSQL_OPT_SSL_KEY,
      MYSQL_OPT_SSL_CERT,
      MYSQL_OPT_SSL_CA,
      MYSQL_OPT_SSL_CAPATH,
      MYSQL_OPT_SSL_CIPHER,
      MYSQL_OPT_SSL_CRL,
      MYSQL_OPT_SSL_CRLPATH,
      MYSQL_OPT_CONNECT_ATTR_RESET,
      MYSQL_OPT_CONNECT_ATTR_ADD,
      MYSQL_OPT_CONNECT_ATTR_DELETE,
      MYSQL_SERVER_PUBLIC_KEY,
      MYSQL_ENABLE_CLEARTEXT_PLUGIN,
      MYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDS,
      MYSQL_OPT_MAX_ALLOWED_PACKET,
      MYSQL_OPT_NET_BUFFER_LENGTH,
      MYSQL_OPT_TLS_VERSION,
      MYSQL_OPT_SSL_MODE,
      MYSQL_OPT_GET_SERVER_PUBLIC_KEY,
      MYSQL_OPT_RETRY_COUNT,
      MYSQL_OPT_OPTIONAL_RESULTSET_METADATA,
      MYSQL_OPT_SSL_FIPS_MODE,
      MYSQL_OPT_TLS_CIPHERSUITES,
      MYSQL_OPT_COMPRESSION_ALGORITHMS,
      MYSQL_OPT_ZSTD_COMPRESSION_LEVEL
    };

[`wait_timeout`][wait_timeout]

<table> 
 <thead> 
  <tr> 
   <th>Property</th> 
   <th>Value</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <th>Command-Line Format</th> 
   <td><code>--wait-timeout=#</code></td> 
  </tr> 
  <tr> 
   <th>System Variable</th> 
   <td><code><a href="https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_wait_timeout" rel="nofollow">wait_timeout</a></code></td> 
  </tr> 
  <tr> 
   <th>Scope</th> 
   <td>Global, Session</td> 
  </tr> 
  <tr> 
   <th>Dynamic</th> 
   <td>Yes</td> 
  </tr> 
  <tr> 
   <th><a href="https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html#optimizer-hints-set-var" rel="nofollow"><code>SET_VAR</code></a>&nbsp;Hint Applies</th> 
   <td>No</td> 
  </tr> 
  <tr> 
   <th>Type</th> 
   <td>Integer</td> 
  </tr> 
  <tr> 
   <th>Default Value</th> 
   <td><code>28800</code></td> 
  </tr> 
  <tr> 
   <th>Minimum Value</th> 
   <td><code>1</code></td> 
  </tr> 
  <tr> 
   <th>Maximum Value (Other)</th> 
   <td><code>31536000</code></td> 
  </tr> 
  <tr> 
   <th>Maximum Value (Windows)</th> 
   <td><code>2147483</code></td> 
  </tr> 
 </tbody> 
</table>

The number of seconds the server waits for activity on a noninteractive connection before closing it.

On thread startup, the session [`wait_timeout`][wait_timeout] value is initialized from the global [`wait_timeout`][wait_timeout] value or from the global [`interactive_timeout`][interactive_timeout] value, depending on the type of client (as defined by the `CLIENT_INTERACTIVE` connect option to [`mysql_real_connect()`][mysql_real_connect]). See also [`interactive_timeout`][interactive_timeout].

### 28.7.27 C API Automatic Reconnection Control ###

**The MySQL client library can perform an automatic reconnection to the server if it finds that the connection is down when you attempt to send a statement to the server to be executed**. If auto-reconnect is enabled, the library tries once to reconnect to the server and send the statement again.

Auto-reconnect is disabled by default.

If it is important for your application to know that the connection has been dropped (so that it can exit or take action to adjust for the loss of state information), be sure that auto-reconnect is disabled. To ensure this, call [`mysql_options()`][mysql_options] with the `MYSQL_OPT_RECONNECT` option:

bool reconnect = 0;
    mysql_options(&mysql, MYSQL_OPT_RECONNECT, &reconnect);

If the connection has gone down, the effect of [`mysql_ping()`][mysql_ping] depends on the auto-reconnect state. If auto-reconnect is enabled, [`mysql_ping()`][mysql_ping] performs a reconnect. Otherwise, it returns an error.

Some client programs might provide the capability of controlling automatic reconnection. For example, [**mysql**][mysql] reconnects by default, but the [`--skip-reconnect`][--skip-reconnect] option can be used to suppress this behavior.

If an automatic reconnection does occur (for example, as a result of calling [`mysql_ping()`][mysql_ping]), there is no explicit indication of it. To check for reconnection, call[`mysql_thread_id()`][mysql_thread_id] to get the original connection identifier before calling [`mysql_ping()`][mysql_ping], then call [`mysql_thread_id()`][mysql_thread_id] again to see whether the identifier changed.

Automatic reconnection can be convenient because you need not implement your own reconnect code, but if a reconnection does occur, several aspects of the connection state are reset on the server side and your application will not be notified.

The connection-related state is affected as follows:

*  Any active transactions are rolled back and autocommit mode is reset.
 *  All table locks are released.
 *  All `TEMPORARY` tables are closed (and dropped).
 *  Session system variables are reinitialized to the values of the corresponding global system variables, including system variables that are set implicitly by statements such as [`SET NAMES`][SET NAMES].
 *  User variable settings are lost.
 *  Prepared statements are released.
 *  [`HANDLER`][HANDLER] variables are closed.
 *  The value of [`LAST_INSERT_ID()`][LAST_INSERT_ID] is reset to 0.
 *  Locks acquired with [`GET_LOCK()`][GET_LOCK] are released.
 *  The association of the client with the Performance Schema [`threads`][threads] table row that determines connection thread instrumentation is lost. If the client reconnects after a disconnect, the session is associated with a new row in the [`threads`][threads] table and the thread monitoring state may be different. See [Section 26.12.19.5, “The threads Table”][threads].

If reconnection occurs, any SQL statement specified by calling [`mysql_options()`][mysql_options] with the `MYSQL_INIT_COMMAND` option is re-executed.

If the connection drops, it is possible that the session associated with the connection on the server side will still be running if the server has not yet detected that the client is no longer connected. In this case, any locks held by the original connection still belong to that session, so you may want to kill it by calling [`mysql_kill()`][mysql_kill].

void QSqlDatabase::setConnectOptions(const QString &options = QString())

Sets database-specific options. This must be done before the connection is opened or it has no effect (or you can close() the connection, call this function and open() the connection again).

The format of the options string is a semicolon separated list of option names or option=value pairs. **The options depend on the database client used:**

<table> 
 <thead> 
  <tr> 
   <td> <p>ODBC</p> </td> 
   <td> <p><span style="color:#f33b45;"><strong>MySQL</strong></span></p> </td> 
   <td> <p>PostgreSQL</p> </td> 
  </tr> 
  <tr> 
   <td> 
    <ul> 
     <li>SQL_ATTR_ACCESS_MODE</li> 
     <li>SQL_ATTR_LOGIN_TIMEOUT</li> 
     <li>SQL_ATTR_CONNECTION_TIMEOUT</li> 
     <li>SQL_ATTR_CURRENT_CATALOG</li> 
     <li>SQL_ATTR_METADATA_ID</li> 
     <li>SQL_ATTR_PACKET_SIZE</li> 
     <li>SQL_ATTR_TRACEFILE</li> 
     <li>SQL_ATTR_TRACE</li> 
     <li>SQL_ATTR_CONNECTION_POOLING</li> 
     <li>SQL_ATTR_ODBC_VERSION</li> 
    </ul></td> 
   <td> 
    <ul> 
     <li>CLIENT_COMPRESS</li> 
     <li>CLIENT_FOUND_ROWS</li> 
     <li>CLIENT_IGNORE_SPACE</li> 
     <li>CLIENT_ODBC</li> 
     <li>CLIENT_NO_SCHEMA</li> 
     <li>CLIENT_INTERACTIVE</li> 
     <li>UNIX_SOCKET</li> 
     <li>MYSQL_OPT_RECONNECT</li> 
     <li>MYSQL_OPT_CONNECT_TIMEOUT</li> 
     <li>MYSQL_OPT_READ_TIMEOUT</li> 
     <li>MYSQL_OPT_WRITE_TIMEOUT</li> 
     <li>SSL_KEY</li> 
     <li>SSL_CERT</li> 
     <li>SSL_CA</li> 
     <li>SSL_CAPATH</li> 
     <li>SSL_CIPHER</li> 
    </ul></td> 
   <td> 
    <ul> 
     <li>connect_timeout</li> 
     <li>options</li> 
     <li>tty</li> 
     <li>requiressl</li> 
     <li>service</li> 
    </ul></td> 
  </tr> 
  <tr> 
   <td> <p>DB2</p> </td> 
   <td> <p>OCI</p> </td> 
   <td> <p>TDS</p> </td> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td> 
    <ul> 
     <li>SQL_ATTR_ACCESS_MODE</li> 
     <li>SQL_ATTR_LOGIN_TIMEOUT</li> 
    </ul></td> 
   <td> 
    <ul> 
     <li>OCI_ATTR_PREFETCH_ROWS</li> 
     <li>OCI_ATTR_PREFETCH_MEMORY</li> 
    </ul></td> 
   <td> <p>none</p> </td> 
  </tr> 
  <tr> 
   <td> <p><a rel="nofollow">SQLite</a></p> </td> 
   <td> <p>Interbase</p> </td> 
   <td>&nbsp;</td> 
  </tr> 
  <tr> 
   <td> 
    <ul> 
     <li>QSQLITE_BUSY_TIMEOUT</li> 
     <li>QSQLITE_OPEN_READONLY</li> 
     <li>QSQLITE_OPEN_URI</li> 
     <li>QSQLITE_ENABLE_SHARED_CACHE</li> 
    </ul></td> 
   <td> 
    <ul> 
     <li>ISC_DPB_LC_CTYPE</li> 
     <li>ISC_DPB_SQL_ROLE_NAME</li> 
    </ul></td> 
   <td>&nbsp;</td> 
  </tr> 
 </tbody> 
</table>

Examples:

...

// MySQL connection

db.setConnectOptions("SSL_KEY=client-key.pem;SSL_CERT=client-cert.pem;SSL_CA=ca-cert.pem;CLIENT_IGNORE_SPACE=1"); // use an SSL connection to the server

if (!db.open()) {

db.setConnectOptions(); // clears the connect option string

...

}

...

// PostgreSQL connection

db.setConnectOptions("requiressl=1"); // enable PostgreSQL SSL connections

if (!db.open()) {

db.setConnectOptions(); // clear options

...

}

...