Text and length

[Leonardo Rossi]

Hi, I tried to use PostgreSQL instead MySQL. I note a problem connected with Text:

ProgrammingError: (psycopg2.ProgrammingError) type modifier is not allowed for type "text"
LINE 5:  value TEXT(35) NOT NULL,
                ^
[SQL: "\nCREATE TABLE bib00x (\n\tid SERIAL NOT NULL, \n\ttag VARCHAR(6) DEFAULT '' NOT NULL, \n\tvalue TEXT(35) NOT NULL, \n\tPRIMARY KEY (id)\n)\n\n"]

And, as they wrote in docs, Text doesn't accept length.

Docs: http://docs.sqlalchemy.org/en/latest/core/type_basics.html?highlight=text#sqlalchemy.types.Text

There is a possibility to specify length only for MySQL and ignore for other DB server?

[Mike Bayer]

sure do it like this:

TEXT().with_variant(TEXT(35), 'mysql')

http://docs.sqlalchemy.org/en/rel_1_0/core/type_api.html?highlight=with_variant#sqlalchemy.types.TypeEngine.with_variant

[Leonardo Rossi]

I did as you say: https://github.com/inveniosoftware/invenio/pull/3036

But when I try to create the database, it returns problems like:

ERROR in database [/home/vagrant/.virtualenvs/invenio2/src/invenio/invenio/base/scripts/database.py:203]:
knwKBRVAL
--------------------------------------------------------------------------------
Traceback (most recent call last):
  File "/home/vagrant/.virtualenvs/invenio2/src/invenio/invenio/base/scripts/database.py", line 199, in _creator
    creator(table)
  File "/home/vagrant/.virtualenvs/invenio2/src/invenio/invenio/base/scripts/database.py", line 212, in <lambda>
    lambda table: table.create(bind=db.engine))
  File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/sql/schema.py", line 725, in create
    checkfirst=checkfirst)
  File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1843, in _run_visitor
    conn._run_visitor(visitorcallable, element, **kwargs)
  File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1471, in _run_visitor
    **kwargs).traverse_single(element)
  File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 121, in traverse_single
    return meth(obj, **kw)
  File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py", line 766, in visit_table
    self.traverse_single(index)
  File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/sql/visitors.py", line 121, in traverse_single
    return meth(obj, **kw)
  File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py", line 785, in visit_index
    self.connection.execute(CreateIndex(index))
  File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 914, in execute
    return meth(self, multiparams, params)
  File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/sql/ddl.py", line 68, in _execute_on_connection
    return connection._execute_ddl(self, multiparams, params)
  File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 968, in _execute_ddl
    compiled
  File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1146, in _execute_context
    context)
  File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1332, in _handle_dbapi_exception
    exc_info
  File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 199, in raise_from_cause
    reraise(type(exception), exception, tb=exc_tb)
  File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_context
    context)
  File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 442, in do_execute
    cursor.execute(statement, parameters)
  File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/MySQLdb/cursors.py", line 205, in execute
    self.errorhandler(self, exc, value)
  File "/home/vagrant/.virtualenvs/invenio2/local/lib/python2.7/site-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorclass, errorvalue
OperationalError: (_mysql_exceptions.OperationalError) (1170, "BLOB/TEXT column 'm_value' used in key specification without a key length") [SQL: u'ALTER TABLE `knwKBRVAL` ADD INDEX `ix_knwKBRVAL_m_value`(m_value)']

[Mike Bayer]

here's what that error means:

http://stackoverflow.com/questions/1827063/mysql-error-key-specification-without-a-key-length

it refers to the "key length" of a MySQL index, which we support here:

http://docs.sqlalchemy.org/en/rel_1_0/dialects/mysql.html#index-length

So therefore you'd build this out like this:

t = Table(
    't', m,
    Column('id', Integer, primary_key=True),
    Column('data', TEXT().with_variant(TEXT(35), 'mysql'))
)
Index('data_idx', t.c.data, mysql_length=35)

however. For only 20 or 30 characters, using TEXT here is completely inappropriate. You should be using the String() or VARCHAR types for all backends. On the PG backend, VARCHAR and TEXT are the same but this is not true for other backends.

[Leonardo Rossi]

I continous have this problem also with your last solution.

from sqlalchemy.dialects import mysql

class KnwKBRVAL(db.Model):

    """Represent a KnwKBRVAL record."""

    __tablename__ = 'knwKBRVAL'
    m_key = db.Column(db.String(255), nullable=False, primary_key=True,
                      index=True)
    m_value = db.Column(
        db.Text().with_variant(mysql.TEXT(30), 'mysql'),
        nullable=False)
    id_knwKB = db.Column(db.MediumInteger(8), db.ForeignKey(KnwKB.id),
                         nullable=False, server_default='0',
                         primary_key=True)


Index('knwkbrval_m_value_idx', KnwKBRVAL.m_value, mysql_length=30)

I can resolve only if I use directly TEXT from mysql dialect:

from sqlalchemy.dialects import mysql

class KnwKBRVAL(db.Model):

    """Represent a KnwKBRVAL record."""

    __tablename__ = 'knwKBRVAL'
    m_key = db.Column(db.String(255), nullable=False, primary_key=True,
                      index=True)
    m_value = db.Column(
        mysql.TEXT(30),
        nullable=False)
    id_knwKB = db.Column(db.MediumInteger(8), db.ForeignKey(KnwKB.id),
                         nullable=False, server_default='0',
                         primary_key=True)


Index('knwkbrval_m_value_idx', KnwKBRVAL.m_value, mysql_length=30)

Can you help me to find the correct way? :) thanks a lot! :D

[Mike Bayer]

here is a standalone test case using that model (the ForeignKey is commented out also there's no MediumInteger type):

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


from sqlalchemy.dialects import mysql

class KnwKBRVAL(Base):

    """Represent a KnwKBRVAL record."""

    __tablename__ = 'knwKBRVAL'
    m_key = Column(String(255), nullable=False, primary_key=True,
                      index=True)
    m_value = Column(
        Text().with_variant(mysql.TEXT(30), 'mysql'),
        nullable=False)
    id_knwKB = Column(
        mysql.MEDIUMINT(8),
        #ForeignKey(KnwKB.id),
         nullable=False, server_default='0',
         primary_key=True)


Index('knwkbrval_m_value_idx', KnwKBRVAL.m_value, mysql_length=30)


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

here is the output on the MySQL database:

CREATE TABLE `knwKBRVAL` (
    m_key VARCHAR(255) NOT NULL, 
    m_value TEXT(30) NOT NULL, 
    `id_knwKB` MEDIUMINT(8) NOT NULL DEFAULT '0', 
    PRIMARY KEY (m_key, `id_knwKB`)
)


2015-04-23 10:48:55,679 INFO sqlalchemy.engine.base.Engine ()
2015-04-23 10:48:55,687 INFO sqlalchemy.engine.base.Engine COMMIT
2015-04-23 10:48:55,687 INFO sqlalchemy.engine.base.Engine CREATE INDEX knwkbrval_m_value_idx ON `knwKBRVAL` (m_value(30))
2015-04-23 10:48:55,687 INFO sqlalchemy.engine.base.Engine ()
2015-04-23 10:48:55,714 INFO sqlalchemy.engine.base.Engine COMMIT
2015-04-23 10:48:55,715 INFO sqlalchemy.engine.base.Engine CREATE INDEX `ix_knwKBRVAL_m_key` ON `knwKBRVAL` (m_key)
2015-04-23 10:48:55,715 INFO sqlalchemy.engine.base.Engine ()
2015-04-23 10:48:55,722 INFO sqlalchemy.engine.base.Engine COMMIT

can you modify the above standalone test case to illustrate your error? then we'll know what it's doing, thanks.

[Leonardo Rossi]

Finally, I think I found the problem in my case: https://github.com/inveniosoftware/invenio/blob/master/invenio/ext/sqlalchemy/engines/mysql.py#L33-L76

This old hack generate the problem... T_T Thanks a lot for the help! :)

[Leonardo Rossi]

And for primary keys? :)

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.dialects import mysql


Base = declarative_base()

class KnwKBRVAL(Base):

    """Represent a KnwKBRVAL record."""

    __tablename__ = 'knwKBRVAL'
    __table_args__ = {'mysql_engine': 'MyISAM'}

    m_value = Column(
        mysql.TEXT(30),
        nullable=False, primary_key=True)


e = create_engine("mysql://root:@localhost/testdb", echo=True)
Base.metadata.create_all(e)

sqlalchemy.exc.OperationalError: (_mysql_exceptions.OperationalError) (1170, "BLOB/TEXT column 'm_value' used in key specification without a key length") [SQL: u'\nCREATE TABLE `knwKBRVAL` (\n\tm_value TEXT(30) NOT NULL, \n\tPRIMARY KEY (m_value)\n)ENGINE=MyISAM\n\n']