Table of Contents

Class MySQLDelegate

Namespace
Quartz.Impl.AdoJobStore
Assembly
Quartz.dll

This is a driver delegate for the MySQL ADO.NET driver.

public class MySQLDelegate : StdAdoDelegate, IDriverDelegate, IDbAccessor
Inheritance
MySQLDelegate
Implements
Inherited Members

Properties

SchemaResourceName

The embedded script that creates this dialect's schema, or null when this delegate has none.

protected override string? SchemaResourceName { get; }

Property Value

string

Remarks

Null on StdAdoDelegate itself, because there is no dialect-neutral DDL: the six shipped delegates each name their own script, and a delegate written outside Quartz names one or leaves provisioning unavailable. Overriding it is the whole of what a delegate has to do to support CreateIfMissing.

The name is a manifest resource name, looked for in the assembly of the delegate's own type and then in each assembly up its base chain. So a delegate of somebody else's can embed a script of its own beside itself, and one that subclasses a shipped dialect delegate to change a statement or two inherits that dialect's script rather than having to carry a copy.

Methods

AddPagingParameters(DbCommand, int, int, bool)

Binds the LIMIT/OFFSET parameters in the order the clause names them, which is the reverse of the ANSI clause's order and matters to providers that bind positionally.

protected override void AddPagingParameters(DbCommand cmd, int skip, int take, bool takeLimited)

Parameters

cmd DbCommand
skip int
take int
takeLimited bool

ApplyPaging(string, bool)

MySQL pages with LIMIT/OFFSET rather than the ANSI clause.

protected override string ApplyPaging(string sql, bool takeLimited)

Parameters

sql string
takeLimited bool

Returns

string

Remarks

MySQL has no OFFSET-only form: skipping without limiting is written as a LIMIT of the largest BIGINT UNSIGNED value, which is what the manual prescribes.

GetCountMisfiredTriggersInStateSql()

The counting form of the misfire scan carries the same FORCE INDEX hint, on the unaliased table.

protected override string GetCountMisfiredTriggersInStateSql()

Returns

string

Remarks

Its predicate is the recovery statement's without the ORDER BY, so it wants the same index for the same reason — and it is the cheap peek every misfire-handler pass starts with, so it runs far more often than the sweep it guards. Measured on the same table with nothing misfired at all: 4,594 buffer pool reads and 111 ms became 8 reads and 0.7 ms.

GetRowLimit(int)

MySQL limits rows with a trailing LIMIT n.

protected override SqlRowLimit GetRowLimit(int count)

Parameters

count int

Returns

SqlRowLimit

GetSelectMisfiredTriggersToRecoverSql(int)

The misfire recovery statement carries a FORCE INDEX hint pointing at IDX_T_NFT_ST — the acquisition index, not IDX_T_NFT_ST_MISFIRE. The hint attaches to the aliased TRIGGERS table, since this statement joins the type tables onto it rather than selecting from TRIGGERS alone.

protected override string GetSelectMisfiredTriggersToRecoverSql(int count)

Parameters

count int

Returns

string

Remarks

It named the misfire index until #3608 measured what that costs. The sweep filters on SCHED_NAME and TRIGGER_STATE by equality, ranges on NEXT_FIRE_TIME, orders by NEXT_FIRE_TIME ASC, PRIORITY DESC and keeps MISFIRE_INSTR as a residual — which is exactly the acquisition index's shape since #3510 reshaped it, and is nothing like (SCHED_NAME, MISFIRE_INSTR, NEXT_FIRE_TIME, TRIGGER_STATE), whose second column is compared with <> and so stops the seek dead. Every other dialect's optimizer had already worked this out and picks the acquisition index on its own; MySQL is the one that cannot, because this hint tells it not to. Measured on a 100,000-trigger table with a 5,000-row backlog: 15,561 buffer pool reads and 66 ms became 129 reads and 0.7 ms.

Applied whatever the batch size, including an unlimited sweep. It used to be skipped for the unlimited one, which was an artefact of the row limit and the hint sharing an early return rather than a decision: the index a statement should read is not a function of how many rows it returns, and an unlimited sweep is the case that reads the most.

GetSelectNextTriggerToAcquireSql(TriggerAcquisitionSqlShape)

The acquisition statement carries a FORCE INDEX hint pointing at IDX_*_T_NFT_ST.

protected override string GetSelectNextTriggerToAcquireSql(TriggerAcquisitionSqlShape shape)

Parameters

shape TriggerAcquisitionSqlShape

Returns

string
Edit this page