Skip to main content

DEPLOY.CONTEXT.SETTING — The settings this migration is about to run under, read instead of assumed

  • Category: safety
  • Severity: derived — the check picks one per finding, from info to high; see Why there is no single severity
  • Level: 0
  • Downtime class: none — a deploy check reports on the state of a target, not on a DDL operation. The findings it raises carry their own, in the tables below.
  • Stability: stable
  • Suites: deploy
  • Applies to: PostgreSQL and MySQL — two separate implementations behind one id, judging different settings

What it reports​

sqlens:predeploy runs immediately before migrate --force, so when this check runs a DDL statement is pending by construction. It reads the settings the server is configured with and reports the ones that make that pending statement dangerous.

The timing is the whole rule. Unbounded lock_timeout is an ordinary, defensible server setting — that is why the lint suite does not report it. What makes it a finding here is when here is: the same value is fine on Tuesday and is an outage at the moment a migration runs.

The findings arrive in one fixed order, the order of the lists below, so two runs against one server produce one sequence rather than a diff.

Each setting reports under its own id, DEPLOY.CONTEXT.SETTING.<NAME>, listed in the tables below, and every one of them links this page. A baseline entry or an ignore can name one of them and leave the others standing, and explain_rule answers each one with this entry.

It reads the value the migration runs under​

SQLens bounds its own session on purpose: lock_timeout and statement_timeout are set by the session defense before any read happens. So SHOW lock_timeout inside a SQLens run answers with SQLens' own value, and a check that believed it would report this package's hygiene back to you as your production configuration.

On PostgreSQL the judgments therefore read reset_val, what a fresh session gets, and a fresh session of which role is the second half of the answer:

  • lock_timeout is read for the role the migrations run as, the username of the migration connection. reset_val answers for the role that reads it, and the preflight reads through an account of its own, while a default stored with ALTER ROLE … SET reaches only the role it names. So the check reads the stored defaults in pg_db_role_setting, which any role may read: the migration role's own, then the one for this database or for every role, then the server's value. Then the pending statements are read: a migration that sets lock_timeout before its first strong lock does not wait under the role's value.
  • statement_timeout is read the same way, for the migration role, because it bounds the migration's own statements.
  • idle_in_transaction_session_timeout bounds the sessions a migration waits behind, not the migration, so it is read as the server's value: the default of this database or of every role, then reset_val while the reading role carries no default of its own.
  • max_wal_size reads reset_val as it is. No role default can change it.

Neither statement_timeout nor idle_in_transaction_session_timeout ever stops a deploy. So when their value is out of sight, because the preflight's account carries a default of its own or the stored defaults could not be read, each is reported as an undetermined note beside the result, and the deploy proceeds.

On MySQL the answer differs per variable:

  • sql_mode is set by Laravel on every connection it opens, from the strict and modes keys of the connection's configuration, and left at the server's value when neither is set. The session this check reads through is opened from the migration connection's own configuration, so it holds the value the migration runs under, and that is the one judged.
  • lock_wait_timeout is not set by Laravel, so a migration's session starts with the GLOBAL value, which is read. Then the pending statements are: a migration that runs SET SESSION lock_wait_timeout before its first metadata lock does not wait under the server's value.
  • foreign_key_checks and innodb_online_alter_log_max_size read the GLOBAL row.

PostgreSQL — four settings out of pg_settings​

FindingReported whenSeverityDowntime class
DEPLOY.CONTEXT.SETTING.LOCK_TIMEOUT_UNBOUNDEDthe role the migrations run as starts with lock_timeout = 0high, or info when the pending migrations bound their own sessionsblocking
DEPLOY.CONTEXT.SETTING.IDLE_IN_TRANSACTION_UNBOUNDEDidle_in_transaction_session_timeout is 0mediumblocking
DEPLOY.CONTEXT.SETTING.STATEMENT_TIMEOUT_UNBOUNDEDstatement_timeout is 0lowonline
DEPLOY.CONTEXT.SETTING.MAX_WAL_SIZE_AT_DEFAULTmax_wal_size reads as a plain number of megabytes at or below 1024lowonline

lock_timeout = 0 — a DDL statement blocked behind a long-running transaction waits indefinitely, and because ALTER TABLE queues an ACCESS EXCLUSIVE request, every read arriving behind it queues too. The table stops answering while nothing looks broken.

It stops the deploy when a pending statement takes a strong lock on a table that already exists before its session sets a lock_timeout of its own, or when not every pending migration could be captured, so nothing can say. When every such statement is bounded, or there is none, the value is still reported, as an info note. A SET lock_timeout bounds its connection for the rest of the run. A SET LOCAL bounds only the rest of its own migration's transaction, and nothing outside a transaction, where PostgreSQL discards it. A zero, DEFAULT, or a value that rounds to zero, such as '0.5ms', is no bound.

idle_in_transaction_session_timeout = 0 — a session that opened a transaction and went away holds its locks forever, and that is the single most common thing a migration blocks behind.

statement_timeout = 0 — reported rather than judged. It means a migration statement that turns out to be far more expensive than expected has no upper bound of its own, so the deploy ends when it ends.

max_wal_size at the shipped 1024 MB — a table rewrite generates far more WAL than that, which forces checkpoints throughout: the rewrite takes longer and the I/O spike lands on everything else.

MySQL — four variables out of performance_schema, plus one about the reading itself​

FindingReported whenSeverityDowntime class
DEPLOY.CONTEXT.SETTING.LOCK_WAIT_TIMEOUT_UNBOUNDEDthe server's lock_wait_timeout reads as a plain number of seconds above 3600, and a pending statement would wait under ithigh, or info when the pending migrations bound their own sessionsblocking
DEPLOY.CONTEXT.SETTING.FOREIGN_KEY_CHECKS_OFFforeign_key_checks is neither ON nor 1highonline
DEPLOY.CONTEXT.SETTING.SQL_MODE_NOT_STRICTthe migration connection's sql_mode carries neither STRICT_TRANS_TABLES nor STRICT_ALL_TABLEShighonline
DEPLOY.CONTEXT.SETTING.ONLINE_ALTER_LOG_AT_DEFAULTinnodb_online_alter_log_max_size reads as a plain number of bytes at or below 134217728 (128 MiB)mediumonline
DEPLOY.CONTEXT.SETTING.PROVENANCE_UNAVAILABLEthe variables came from SHOW GLOBAL VARIABLES rather than performance_schemainfoonline

lock_wait_timeout above an hour — MySQL ships 31536000, one year, which at deploy time is indistinguishable from waiting forever: a DDL blocked on a metadata lock sits there, and every statement that needs that table sits behind it. One hour is a declared line, not a measured one: past it the deploy has already failed in every way that matters to whoever is watching it.

It stops the deploy when a pending statement takes a metadata lock on a table that already exists before its session sets a lock_wait_timeout of at most an hour, or when not every pending migration could be captured, so nothing can say. When every such statement is bounded, or there is none, the server's value is still reported, as an info note. A SET GLOBAL in a migration does not count: it changes what later connections start with, and the session that ran it keeps its own value.

foreign_key_checks = OFF — a migration that adds a foreign key is accepted and validates nothing. The constraint exists in the schema and the data behind it was never checked; the failure surfaces later, as rows that violate a constraint the database believes it is enforcing.

sql_mode without strict mode — a column narrowed by this migration TRUNCATES the values that no longer fit and reports a warning rather than an error. The migration succeeds, the deploy goes green, and the data is gone. The value judged is the one the migration connection runs with; the finding names the server's as well.

innodb_online_alter_log_max_size at the shipped 128 MiB — an online ALTER buffers the DML that arrives while it runs; when the buffer fills, the ALTER fails — after doing most of its work, and under exactly the write load that made it fill.

PROVENANCE_UNAVAILABLE — performance_schema is off or not granted to this role, so the reading fell back to SHOW GLOBAL VARIABLES. The values above are correct; what is missing is where each came from. A value that looks deliberate may simply be the compiled-in default nobody ever set, and this report cannot tell you which. It is a finding rather than an undetermined verdict on purpose: undetermined would say the timeouts are unknown, when they were read perfectly well.

Why there is no single severity​

The check picks a severity per finding, because the damage it is describing differs by more than a degree.

High is for the two shapes that end badly on their own: a wait nothing will end while an ACCESS EXCLUSIVE or metadata lock queues everything behind it, and a migration that is accepted while silently not doing what it says — an unvalidated foreign key, a truncating column narrow.

Medium and low descend from there to conditions that make a bad day worse rather than making one: a missing bound on an idle transaction, a statement with no ceiling of its own, a default that turns a rewrite into a slower rewrite. The one medium that does not fit that sentence is ONLINE_ALTER_LOG_AT_DEFAULT, and it is medium rather than high because it is the default being reported: the ALTER fails only if the write traffic during it fills the buffer, and this check has not seen that traffic.

Info is the one finding that is about the reading rather than the server.

Both …_AT_DEFAULT findings additionally carry heuristic confidence; everything else this check reports is deterministic.

What stops the deploy​

Only a high finding. The severities above draw the line, and the verdict follows it:

EngineStops the deployTravels with a passing result
PostgreSQLLOCK_TIMEOUT_UNBOUNDEDIDLE_IN_TRANSACTION_UNBOUNDED, STATEMENT_TIMEOUT_UNBOUNDED, MAX_WAL_SIZE_AT_DEFAULT
MySQLLOCK_WAIT_TIMEOUT_UNBOUNDED, FOREIGN_KEY_CHECKS_OFF, SQL_MODE_NOT_STRICTONLINE_ALTER_LOG_AT_DEFAULT, PROVENANCE_UNAVAILABLE

A finding below the line is still reported, as a pass finding with its value and its sentence. The deploy proceeds with it named rather than silently.

Every finding used to stop the deploy, and that contradicted this page. On a managed PostgreSQL the gate refused over statement_timeout = 0, which cannot be set server-wide without bounding the very migration the deploy is about to run, and over max_wal_size at its default, which such a host does not let anybody change. On MySQL a role that could not read performance_schema stopped every deploy over an info note.

idle_in_transaction_session_timeout = 0 is below the line for a reason of its own. Its danger reaches a migration only through a lock wait, and how long that wait may last is lock_timeout, which stops the deploy on its own when it is 0.

With nothing pending, nothing stops the deploy: on both engines every setting is reported as not_applicable with the reason nothing_pending, whatever its severity, and without the sentence about a change that is not happening. MySQL's provenance note stays a pass, because it describes the reading rather than the change. This matters most on MySQL, which ships lock_wait_timeout at one year: before the split, every deploy on a server at its defaults stopped over it, including the ones that carried no migration.

What to do about it​

The two timeout zeros on PostgreSQL are a decision, then one line. The check judges zero against non-zero and nothing else, so any non-zero value clears the finding — which is deliberately not a recommendation of a number. Decide how long a DDL statement may wait for its lock, and how long a session may sit idle inside a transaction, then set them. Only lock_timeout stops the deploy until you do. SQLens will not set either for you: it reads, and never sets a server-level value, not even for the duration of its own run.

On PostgreSQL there are two more places for that line. On a host where the server configuration is out of reach, a default on the role the migrations run as clears it: ALTER ROLE <migration role> SET lock_timeout = '10s'. A default on the preflight's own account does not, because the migrations never run as it. Or each migration sets its own bound before its first strong lock, which is what PG.L3.MISSING_LOCK_TIMEOUT asks for, and the deploy proceeds with the value reported as a note:

DB::statement("SET LOCAL lock_timeout = '3s'");

SET LOCAL belongs in a migration that runs inside a transaction. One that declares $withinTransaction = false needs a plain SET.

statement_timeout = 0 is reported, not judged. The decision is whether the deploy should carry an upper bound of its own. Nothing here says it must.

foreign_key_checks and sql_mode are the two to fix rather than weigh — in two different places, which is the part that is easy to get wrong. sql_mode is a startup option; foreign_key_checks is not one at all.

# my.cnf — a column narrowed by a migration should fail rather than truncate.
sql_mode = "STRICT_TRANS_TABLES"
-- foreign_key_checks has no startup option, so it CANNOT go in my.cnf: mysqld refuses to boot with
-- `unknown variable 'foreign-key-checks=ON'`. A global OFF is installed at run time, and run time
-- is where it goes back — a migration that adds a foreign key should validate it.
SET GLOBAL foreign_key_checks = ON;

Watch for the split here: Laravel sets sql_mode per connection from database.connections.*.strict and …modes, and this check reads the connection the migrations run on. So 'strict' => true clears it on a server that is not strict, and 'strict' => false raises it on a server that is. The server's own value is what every other client gets, and MY.L5.SQL_MODE_NOT_STRICT in the audit suite judges that one.

lock_wait_timeout needs a value no higher than the hour this check draws its line at (3600 seconds — exactly 3600 clears the finding, only a larger value raises it). The line is declared so it is arguable; the year MySQL ships is not. Either the server gets it, or each migration sets it for itself before its first metadata lock, which is what MY.L3.MISSING_LOCK_WAIT_TIMEOUT asks for:

DB::statement('SET SESSION lock_wait_timeout = 5');

The two …_AT_DEFAULT findings are the ones that genuinely say "decide, then act". Neither has measured anything of yours: max_wal_size is judged against PostgreSQL's compiled-in default rather than against the WAL your rewrite will produce, and innodb_online_alter_log_max_size against MySQL's, rather than against the write traffic that will arrive during the ALTER. The question they put to you is whether this deploy rewrites a large table or runs an online ALTER under load. If it does not, the default is fine and the answer is a decision rather than a change.

PROVENANCE_UNAVAILABLE is fixed where the reading was blocked, by turning performance_schema on or granting this role access to it. Nothing about the values changes either way.

When the check cannot answer​

A check that cannot look reports undetermined with a named reason. It never reports a pass.

  • The whole reading failed. The verdict names the reason and says what follows from it — the timeouts this migration will run under are unknown on PostgreSQL, the conditions it will run under on MySQL — and in both cases that is not the same as them being fine.
  • One setting could not be judged. The verdict is setting_unreadable: followed by one clause per setting. On PostgreSQL those clauses keep three states apart, because they send you to three different places: the row was withheld from the reading role (present, value masked), the name could not be seen by the reading role at all (a privilege, and the value may well be unsafe), or the name is not in this server's settings at all. Only some of those are fixable with a GRANT — pg_settings filters by privilege silently, with no error and nothing in the result to say a row was missing. On MySQL there is no masked-value state; a missing name means the server does not have that variable.
  • The migration role's lock_timeout is out of sight. When the preflight's own account carries a lock_timeout default and the migration role has none, reset_val shows the account's default instead of the server's value, and the check will not put one role's value in the other's place. Remove the account's default, or give the migration role one of its own. The same answer comes when no migration role is configured and some role carries a default of its own; set the username of the migration connection. A stored default the check cannot read as a duration, and a catalog of stored defaults that could not be read, are reported the same way.
  • Undetermined does not throw away what was established. The findings that were judged still travel with the undetermined verdict, because a run that saw three of four problems reporting none of them would be the worse answer. What the verdict says is that the check cannot claim the settings are fine — a different sentence from "there is nothing here".

Undetermined blocks the deploy. See pre-deploy permissions for the privileges each reading needs and for --allow-undetermined, the one door out.

What it does not claim​

  • It never changes what it reads. No SET GLOBAL on MySQL, no server-level value on PostgreSQL, not even "just for this run". The session defense applies to SQLens' own connection and nothing else.
  • It reads the RUNNING value, never a configuration file. A line edited on disk and not yet reloaded is invisible to this check, so a fix applied that way clears the finding only after the server has picked it up. On PostgreSQL reset_val is what a fresh session for this role and this database gets, so an ALTER ROLE … SET or ALTER DATABASE … SET can make it differ from the file as well. For lock_timeout the role is the one the migrations run as.
  • It does not see what a connection passes when it opens. A value the migration connection sends at login, such as -c lock_timeout=5s in PGOPTIONS, is stored nowhere this check reads.
  • Nothing here says a blocker exists. idle_in_transaction_session_timeout and lock_wait_timeout say only that if one appears, nothing will end the wait. What is actually holding a lock right now is a separate finding — DEPLOY.PREFLIGHT.LOCK_BLOCKER and DEPLOY.PREFLIGHT.METADATA_LOCK_BLOCKER.
  • The two default findings are the default being reported, not a computed need. How much WAL a rewrite produces depends on the table, and how much DML arrives during an online ALTER depends on the traffic. This check has been told neither.
  • These are the only settings it judges. Four names per engine, plus MySQL's provenance finding. A setting outside that list is not read for this verdict, so a quiet result here is not a statement about the server's configuration as a whole.
  • A value it cannot parse as a plain number produces no finding. max_wal_size, lock_wait_timeout and innodb_online_alter_log_max_size are compared numerically, and anything else is left unjudged rather than guessed at.
  • On MySQL, a missing source is not "nobody configured this". That is precisely what PROVENANCE_UNAVAILABLE exists to prevent the report from implying.