Issue 1:
Describe the bug

Looks like latest sphinx shows many warnings on generate documentation.

To Reproduce

`sphinx-build -n -T -b html doc/build build/sphinx/html`

Error

Willaadd in next comment.

Versions

OS: Linux x86/64

Python: 3.8.13

SQLAlchemy: 1.4.35

Database: N/A

DBAPI (eg: psycopg, cx_oracle, mysqlclient): N/A

Additional context

list of modules with versions installed in build env

Package Version
----------------------------- -----------------
alabaster 0.7.12
attrs 21.4.0
Babel 2.9.1
build 0.7.0
changelog 0.5.7
charset-normalizer 2.0.12
codespell 2.1.0
cycler 0.11.0
distro 1.7.0
docutils 0.17.1
execnet 1.9.0
extras 1.0.0
fixtures 3.0.0
fonttools 4.32.0
gpg 1.17.1-unknown
greenlet 1.1.2
idna 3.3
imagesize 1.3.0
importlib-metadata 4.11.3
iniconfig 1.1.1
Jinja2 3.1.1
kiwisolver 1.3.2
libcomps 0.1.18
libsass 0.21.0
lit 14.0.1
Mako 1.2.0.dev0
MarkupSafe 2.1.1
matplotlib 3.5.1
meson 0.62.0
numpy 1.22.3
olefile 0.46
packaging 21.3
pbr 5.8.1
pep517 0.12.0
Pillow 9.1.0
pip 22.0.4
pluggy 1.0.0
py 1.11.0
Pygments 2.11.2
PyGObject 3.42.0
pyparsing 3.0.8
pytest 7.1.1
pytest-forked 1.4.0
pytest-xdist 2.5.0
python-dateutil 2.8.2
pytz 2022.1
requests 2.27.1
rpm 4.17.0
setuptools 62.0.0
six 1.16.0
snowballstemmer 2.2.0
Sphinx 4.5.0.dev20220404
sphinx-copybutton 0.4.0
sphinx-paramlinks 0.5.2
sphinxcontrib-applehelp 1.0.2.dev20220410
sphinxcontrib-devhelp 1.0.2.dev20220410
sphinxcontrib-htmlhelp 2.0.0
sphinxcontrib-jsmath 1.0.1.dev20220410
sphinxcontrib-qthelp 1.0.3.dev20220410
sphinxcontrib-serializinghtml 1.1.5
testtools 2.5.0
tomli 2.0.1
urllib3 1.26.9
wheel 0.37.1
zipp 3.8.0
zzzeeksphinx 1.3.3

Issue 2:
Describe the bug

When reflecting indexes from one table to another in MariaDB, the index containing Text field can't be created as the retrieved index information in metadata does not contain the information about 'mysql_length'.

mike-supplied MCVE

from sqlalchemy import Column
from sqlalchemy import create_engine
from sqlalchemy import Index
from sqlalchemy import Integer
from sqlalchemy import MetaData
from sqlalchemy import Table
from sqlalchemy import Text

metadata = MetaData()

test_table = Table(
"test_table",
metadata,
Column("signal_type", Integer(), nullable=False),
Column("signal_data", Text(1 << 18), nullable=False),
Index(
"ix_test_table_0a574",
*["signal_type", "signal_data"],
mysql_length={"signal_data": 256},
)
)

e = create_engine("mysql://scott:tiger@localhost/test", echo=True)

metadata.drop_all(e)
metadata.create_all(e)

new_metadata = MetaData()
reflected_table = Table("test_table", new_metadata,autoload_with=e)

assert list(test_table.indexes)[0].kwargs['mysql_length'] == {"signal_data": 256}
assert list(reflected_table.indexes)[0].kwargs['mysql_length'] == {"signal_data": 256}

original issue

To Reproduce

sqlalchemy as sa

def get_engine(db_url):
import logging

logging.getLogger("sqlalchemy").setLevel(logging.ERROR)
engine = sa.create_engine(db_url, isolation_level="READ_UNCOMMITTED")
yield engine
engine.dispose()

def get_metadata(engine) -> sa.MetaData:
metadata_obj = sa.MetaData()
metadata_obj.reflect(bind=engine)
return metadata_obj

def create_indices(engine):

metadata_obj = get_metadata(engine)

old_table = metadata_obj.tables["test_table"]
backup_table = metadata_obj.tables["test_table_backup"]

with engine.connect() as conn:
for index in old_table.indexes:
# the parent table must not be set in the Index constructor beforehand, because the index
# will then be "attached" to the table object and will be created when calling table.create()
print(index)
index.table = None
index._set_parent(backup_table)
index.create(bind=conn)

def create_backup_table(engine, old_table_name: str):

metadata = get_metadata(engine)

old_table = metadata.tables[old_table_name]

# duplicate table without indexes
# we have a reason to do this, belive me
backup_table = sa.Table(
"test_table_backup",
metadata,
*[column.copy() for column in old_table.columns],
)

with engine.connect() as conn:

backup_table.create(conn, checkfirst=True)

return backup_table

def create_old_table(engine):

metadata = get_metadata(engine)

old_table = sa.Table(
"test_table",
metadata,
sa.Column("signal_type", sa.Integer(), nullable=False),
sa.Column("signal_data", sa.Text(1 << 18), nullable=False),
sa.Index(
"ix_test_table_0a574",
*["signal_type", "signal_data"],
mysql_length={"signal_data": 256},
)
)

with engine.connect() as connection:
metadata.create_all(connection)

return old_table

def example(engine):

#cleanup of residual tables from previous test
drop_table = sa.text(f"DROP TABLE IF EXISTS test_table")
engine.execute(drop_table)

drop_table = sa.text(f"DROP TABLE IF EXISTS test_table_backup")
engine.execute(drop_table)

create_old_table(engine)
create_backup_table(engine, "test_table")

create_indices(engine)

if __name__ == "__main__":

example(get_engine("maradb_url"))

Error

self = <sqlalchemy.engine.base.Connection object at 0x7fdbf37614c0>, dialect = <sqlalchemy.dialects.mysql.mysqldb.MySQLDialect_mysqldb object at 0x7fdbf370b730>
constructor = <bound method DefaultExecutionContext._init_ddl of <class 'sqlalchemy.dialects.mysql.mysqldb.MySQLExecutionContext_mysqldb'>>
statement = 'CREATE INDEX ix_test_table_0a574 ON test_table_backup (signal_type, signal_data)', parameters = (), args = (<sqlalchemy.dialects.mysql.base.MySQLDDLCompiler object at 0x7fdbf375bd30>,)
conn = <sqlalchemy.pool.base._ConnectionFairy object at 0x7fdbf3761be0>, context = <sqlalchemy.dialects.mysql.mysqldb.MySQLExecutionContext_mysqldb object at 0x7fdbf36f3d90>

def _execute_context(
self, dialect, constructor, statement, parameters, *args
):
"""Create an :class:`.ExecutionContext` and execute, returning
a :class:`_engine.ResultProxy`.

"""

try:
try:
conn = self.__connection
except AttributeError:
# escape "except AttributeError" before revalidating
# to prevent misleading stacktraces in Py3K
conn = None
if conn is None:
conn = self._revalidate_connection()

context = constructor(dialect, self, conn, *args)
except BaseException as e:
self._handle_dbapi_exception(
e, util.text_type(statement), parameters, None, None
)

if context.compiled:
context.pre_exec()

cursor, statement, parameters = (
context.cursor,
context.statement,
context.parameters,
)

if not context.executemany:
parameters = parameters[0]

if self._has_events or self.engine._has_events:
for fn in self.dispatch.before_cursor_execute:
statement, parameters = fn(
self,
cursor,
statement,
parameters,
context,
context.executemany,
)

if self._echo:
self.engine.logger.info(statement)
if not self.engine.hide_parameters:
self.engine.logger.info(
"%r",
sql_util._repr_params(
parameters, batches=10, ismulti=context.executemany
),
)
else:
self.engine.logger.info(
"[SQL parameters hidden due to hide_parameters=True]"
)

evt_handled = False
try:
if context.executemany:
if self.dialect._has_events:
for fn in self.dialect.dispatch.do_executemany:
if fn(cursor, statement, parameters, context):
evt_handled = True
break
if not evt_handled:
self.dialect.do_executemany(
cursor, statement, parameters, context
)
elif not parameters and context.no_parameters:
if self.dialect._has_events:
for fn in self.dialect.dispatch.do_execute_no_params:
if fn(cursor, statement, context):
evt_handled = True
break
if not evt_handled:
self.dialect.do_execute_no_params(
cursor, statement, context
)
else:
if self.dialect._has_events:
for fn in self.dialect.dispatch.do_execute:
if fn(cursor, statement, parameters, context):
evt_handled = True
break
if not evt_handled:
> self.dialect.do_execute(
cursor, statement, parameters, context
)

/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py:1276:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

self = <sqlalchemy.dialects.mysql.mysqldb.MySQLDialect_mysqldb object at 0x7fdbf370b730>, cursor = <MySQLdb.cursors.Cursor object at 0x7fdbf375bf70>
statement = 'CREATE INDEX ix_test_table_0a574 ON test_table_backup (signal_type, signal_data)', parameters = ()
context = <sqlalchemy.dialects.mysql.mysqldb.MySQLExecutionContext_mysqldb object at 0x7fdbf36f3d90>

def do_execute(self, cursor, statement, parameters, context=None):
> cursor.execute(statement, parameters)

/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py:608:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

self = <MySQLdb.cursors.Cursor object at 0x7fdbf375bf70>, query = b'CREATE INDEX ix_test_table_0a574 ON test_table_backup (signal_type, signal_data)', args = ()

def execute(self, query, args=None):
"""Execute a query.

query -- string, query to execute on server
args -- optional sequence or mapping, parameters to use with query.

Note: If args is a sequence, then %s must be used as the
parameter placeholder in the query. If a mapping is used,
%(key)s must be used as the placeholder.

Returns integer represents rows affected, if any
"""
while self.nextset():
pass
db = self._get_db()

if isinstance(query, str):
query = query.encode(db.encoding)

if args is not None:
if isinstance(args, dict):
nargs = {}
for key, item in args.items():
if isinstance(key, str):
key = key.encode(db.encoding)
nargs[key] = db.literal(item)
args = nargs
else:
args = tuple(map(db.literal, args))
try:
query = query % args
except TypeError as m:
raise ProgrammingError(str(m))

assert isinstance(query, (bytes, bytearray))
> res = self._query(query)

/usr/local/lib/python3.9/site-packages/MySQLdb/cursors.py:206:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

self = <MySQLdb.cursors.Cursor object at 0x7fdbf375bf70>, q = b'CREATE INDEX ix_test_table_0a574 ON test_table_backup (signal_type, signal_data)'

def _query(self, q):
db = self._get_db()
self._result = None
> db.query(q)

/usr/local/lib/python3.9/site-packages/MySQLdb/cursors.py:319:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

self = <_mysql.connection open to 'afs-db-test' at 0x560bdcd86ad0>, query = b'CREATE INDEX ix_test_table_0a574 ON test_table_backup (signal_type, signal_data)'

def query(self, query):
# Since _mysql releases GIL while querying, we need immutable buffer.
if isinstance(query, bytearray):
query = bytes(query)
> _mysql.connection.query(self, query)
E MySQLdb._exceptions.OperationalError: (1170, "BLOB/TEXT column 'signal_data' used in key specification without a key length")

/usr/local/lib/python3.9/site-packages/MySQLdb/connections.py:259: OperationalError

The above exception was the direct cause of the following exception:

engine = Engine(mysql://root:***@afs-db-test/afs)

def test(engine):

#cleanup of residual tables from previous test
drop_table = sa.text(f"DROP TABLE IF EXISTS test_table")
engine.execute(drop_table)

drop_table = sa.text(f"DROP TABLE IF EXISTS test_table_backup")
engine.execute(drop_table)

create_old_table(engine)
create_backup_table(engine, "test_table")

> create_indices(engine)

tests/tools/test_delete_me.py:85:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
tests/tools/test_delete_me.py:30: in create_indices
index.create(bind=conn)
/usr/local/lib/python3.9/site-packages/sqlalchemy/sql/schema.py:4032: in create
bind._run_visitor(ddl.SchemaGenerator, self)
/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py:1656: in _run_visitor
visitorcallable(self.dialect, self, **kwargs).traverse_single(element)
/usr/local/lib/python3.9/site-packages/sqlalchemy/sql/visitors.py:145: in traverse_single
return meth(obj, **kw)
/usr/local/lib/python3.9/site-packages/sqlalchemy/sql/ddl.py:868: in visit_index
self.connection.execute(CreateIndex(index))
/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py:1011: in execute
return meth(self, multiparams, params)
/usr/local/lib/python3.9/site-packages/sqlalchemy/sql/ddl.py:72: in _execute_on_connection
return connection._execute_ddl(self, multiparams, params)
/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py:1068: in _execute_ddl
ret = self._execute_context(
/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py:1316: in _execute_context
self._handle_dbapi_exception(
/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py:1510: in _handle_dbapi_exception
util.raise_(
/usr/local/lib/python3.9/site-packages/sqlalchemy/util/compat.py:182: in raise_
raise exception
/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/base.py:1276: in _execute_context
self.dialect.do_execute(
/usr/local/lib/python3.9/site-packages/sqlalchemy/engine/default.py:608: in do_execute
cursor.execute(statement, parameters)
/usr/local/lib/python3.9/site-packages/MySQLdb/cursors.py:206: in execute
res = self._query(query)
/usr/local/lib/python3.9/site-packages/MySQLdb/cursors.py:319: in _query
db.query(q)
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

self = <_mysql.connection open to 'afs-db-test' at 0x560bdcd86ad0>, query = b'CREATE INDEX ix_test_table_0a574 ON test_table_backup (signal_type, signal_data)'

def query(self, query):
# Since _mysql releases GIL while querying, we need immutable buffer.
if isinstance(query, bytearray):
query = bytes(query)
> _mysql.connection.query(self, query)
E sqlalchemy.exc.OperationalError: (MySQLdb._exceptions.OperationalError) (1170, "BLOB/TEXT column 'signal_data' used in key specification without a key length")
E [SQL: CREATE INDEX ix_test_table_0a574 ON test_table_backup (signal_type, signal_data)]
E (Background on this error at: http://sqlalche.me/e/13/e3q8)

/usr/local/lib/python3.9/site-packages/MySQLdb/connections.py:259: OperationalError
---------------------------------------------------------------------------------------------- Captured stdout call -----------------------------------------------------------------------------------------------
Index('ix_test_table_0a574', Column('signal_type', INTEGER(display_width=11), table=<test_table>, nullable=False), Column('signal_data', MEDIUMTEXT(collation='utf8_czech_ci'), table=<test_table>, nullable=False))

Versions

OS: Linux - Manjaro

Python: 3.9.15

SQLAlchemy: 1.3.24

Database: MariaDB

DBAPI (eg: psycopg, cx_oracle, mysqlclient):

Additional context

The reason why we first create the backup_table without indices is that we migrate selected data first avoiding the cost of indexing on each write and add the index at the end which is significantly faster.