0. Install the GNU ELPA package gnu-elpa.
1. emacs -Q
2. M-x package-initialize
3. Visit an SQL file (one with the extension .sql, like the attached
   file test.sql, so it will be visited in sql-mode).
4. At the prompt "Install package sql-indent? (yes or no) " type "no".
5. Now the current buffer displays the SQL file and the mode line
   displays "(SQL[ANSI])", but the buffer is not fontified and the echo
   area displays "File mode specification error: (error Abort!)".

This also happens in emacs-29 and emacs-30.  Typing `M-x
font-lock-fontify-buffer' makes the buffer correctly fontified.

When I add `M-x toggle-debug-on-error' to step 2 above and repeat the
recipe, I get the attached backtrace. According to this, running
sql-mode-hook invokes sql-indent-enable, whose definition is:

(when (fboundp 'sqlind-minor-mode)
    (sqlind-minor-mode (if sql-use-indent-support +1 -1)))

Further, the activated gnu-elpa package loads the feature
sqlind-minor-mode, so the above when-clause condition is satisfied, but
since the package sql-indent was not installed, sqlind-minor-mode is not
defined, causing the file mode specification error.  It's not clear to
me from this why the SQL file is not fontified, since IIUC the mode hook
is run at the end of the mode definition, so the mode fontification code
should already have been executed; and indeed, when I instrument
sql-mode, carry out the above recipe and step through sql-mode, the SQL
file gets fontified and there is no error.  Nevertheless, without
instrumentation the above recipe reliably results in the error and
unfontified display.  The following patch prevents the error and
displays the SQL file correctly fontified: