ClassExtender Bobs Custom Database Tutorial Index Length Error

[smiffygit]

Summary

I’m following Bobs tutorial on creating custom DB tables. I’ve copied the schema and created the chunk and resource. When I browse the resource and submit the form I get:

Create Quotation Classes

Generating class and map files

Saving schema

Old class and map files removed

Schema parsed

createObjectContainer() failed

The error log shows:

(
[0] => 42000
[1] => 1170
[2] => BLOB/TEXT column ‘quote’ used in key specification without a key length
)

Research tells me this is a common error for mysql with this field/index type but I can’t find how to define the index length in the schema.

I’ve tried adding a value length to the schema but still get the same error

What should I be doing?

   <index alias="quote" name="quote" primary="false" unique="true" type="BTREE">
        <column key="quote" length="50" collation="A" null="false"/>
    </index>

Environment

Modx 3.1.2-pl

Ubuntu local

Mysql

Apache 2.4

PHP 8.3.2

[halftrainedharry]

Do you really need an index for a column of type TEXT or BLOB?
Try using the type VARCHAR instead, if the index is necessary.

[smiffygit]

Hi Harry

Thanks for the reply. I agree but I was just following the tutorial and didn’t want to deviate from it.

I changed the schema to varchar and it works so I shall continue and see how I get on.

[halftrainedharry]

I did a quick test with the following schema (using dbtype="text" with an index of length 50).

<?xml version="1.0" encoding="UTF-8"?>

<model package="MyPackage\Model\" baseClass="xPDO\Om\xPDOObject" platform="mysql" defaultEngine="InnoDB" version="3.0">

    <object class="MyTable" table="mypackage_mytable" extends="xPDO\Om\xPDOSimpleObject">
        <field key="quote" dbtype="text" phptype="string" null="false" default="" />

        <index alias="quote" name="quote" primary="false" unique="false" type="BTREE">
            <column key="quote" length="50" collation="A" null="false" />
        </index>
    </object>

</model>

With my local setup (MySQL 5.7.36), this seems to work and generate the correct SQL query in the MODX function createObjectContainer():

CREATE TABLE `modx_mypackage_mytable` (
	`id` INTEGER unsigned NOT NULL AUTO_INCREMENT, 
	`quote` TEXT NOT NULL, 
	PRIMARY KEY (`id`), 
	INDEX `quote` (`quote` (50))
) ENGINE=InnoDB

Not sure why it doesn’t work in your case.

@smiffygit

Mysql

What version of MySQL are you using?

[smiffygit]

8.0.45-0ubuntu0.24.04.1 - (Ubuntu)

It was a machine I set up myself - being a novice I could have done anything.
It’s not a big issue, Modx is running okay and I seem to be getting on okay so please don’t waste your time with it.

Thanks for your effort

[halftrainedharry]

Ok, I figured it out. It’s not an issue with MySQL.

The example schema in the tutorial uses the (very old) model version “1.0.0”

<model package="quotation" ... version="1.0.0">

This version still uses the “legacy indexes” and ignores the <index> tags in the schema.

It can be fixed by changing the model version to “1.1”

<model package="quotation" ... version="1.1">

---

@bobray Could you maybe change the schema in your tutorial, so that the example code runs without creating an error?

[bobray]

Done. Thanks!!!

Am I correct in assuming that all of the ClassExtender schemas need the same fix?

Bob

[halftrainedharry]

Thanks Bob for making the change. However, additionally it’s also necessary to add an appropriate value to the “length” attribute in the <column> element of the <index> for “quote”. E.g.:

<index alias="quote" name="quote" primary="false" unique="true" type="BTREE">
	<column key="quote" length="100" collation="A" null="false"/>
</index>

---

@bobray

Am I correct in assuming that all of the ClassExtender schemas need the same fix?

I did not look at the other schemas in ClassExtender. But generally a change is only necessary, if the schema has <index> elements with additional information (for the parser/table generator).

As an example: If you have a schema like this (<model ... version="1.0">), it won’t work.

<?xml version="1.0" encoding="UTF-8"?>
<model package="mypackage" baseClass="xPDOObject" platform="mysql" defaultEngine="InnoDB" version="1.0">
    <object class="mypackageMytable" table="mypackage_mytable" extends="xPDOSimpleObject">
        <field key="name" dbtype="varchar" phptype="string" precision="200" null="false" default="" index="index"/>
    </object>
</model>

index="index" tells the code to create an index for the column “name”, but the field size (precision="200") is too big for the index. For “InnoDB”, the maximun index length is 767 bytes, which gives you a maximum of 191 characters for a 4-byte character set (utf8mb4).

So to give the index a different length from the field, you have to add an <index> element to the schema, and change the model-version to “1.1”, so that the <index> element gets processed:

<?xml version="1.0" encoding="UTF-8"?>
<model package="mypackage" baseClass="xPDOObject" platform="mysql" defaultEngine="InnoDB" version="1.1">
    <object class="mypackageMytable" table="mypackage_mytable" extends="xPDOSimpleObject">
        <field key="name" dbtype="varchar" phptype="string" precision="200" null="false" default="" />
        <index alias="name" name="name" primary="false" unique="false" type="BTREE">
            <column key="name" length="191" collation="A" null="false" />
        </index>
    </object>
</model>

[bobray]

@halftrainedharry

… additionally it’s also necessary to add an appropriate value to the “length” attribute in the <column> element of the <index> for “quote”.

Thanks, but I’m confused by the many places in the MODX schema where the length is empty, such as the compound index for resources:

<index alias="content_ft_idx" name="content_ft_idx" primary="false" unique="false" type="FULLTEXT">
            <column key="pagetitle" length="" collation="A" null="false" />
            <column key="longtitle" length="" collation="A" null="false" />
            <column key="description" length="" collation="A" null="false" />
            <column key="introtext" length="" collation="A" null="true" />
            <column key="content" length="" collation="A" null="true" />
        </index>

And this index on the context_key field:

<index alias="context_key" name="context_key" primary="false" unique="false" type="BTREE">
            <column key="context_key" length="" collation="A" null="false" />
        </index>

[halftrainedharry]

@bobray

And this index on the context_key field:

When the length is not defined in the <index> element, the complete content of the column is used for the index. In this case the column context_key has a length of only 100 (<field key="context_key" dbtype="varchar" precision="100" ...), so the maximum index size will never be exceeded.

@bobray

such as the compound index for resources:

Here a fulltext index is used (type="FULLTEXT"), which works differently and doesn’t have the same size limit.

[bobray]

@halftrainedharry

<index alias="quote" name="quote" primary="false" unique="true" type="BTREE">
	<column key="quote" length="100" collation="A" null="false"/>
</index>

Ok. Would this fix it?

<index alias="quote" name="quote" primary="false" unique="true" type="FULLTEXT">
	<column key="quote" length="" collation="A" null="false"/>
</index>

I looked, and in my actual quotation DB, the index on the quote field is a FULLTEXT index.

[halftrainedharry]

I think a fulltext index only makes sense, if you intend to query the table with the specific SQL syntax → MATCH (...) AGAINST (...).

It’s also questionable if you even need an index for this column at all.

---

However you decide to fix the schema (no index, fulltext index, normal index with limited size), the main thing here is, that the example code in the tutorial should preferably run without throwing an error.

[bobray]

The use case is a database of quotations that allows you do to a full text search of the entire database for keywords in any field.

It does work.

[halftrainedharry]

@bobray

It does work.

Well I’m sure it does.

But my concern never was the live database table on your website, but the schema in your tutorial (for the Schema First approach).

Unfortunately, this schema currently still throws an error when you run the ClassExtender snippet to create the database table in the database.

---

Here is a (possible example for an) adapted schema, that should work (and also more closely matches the CREATE TABLE ... SQL query in the Database Table First section of the tutorial:

<?xml version="1.0" encoding="UTF-8"?>
<model package="quotation" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" tablePrefix="bobs_" version="1.1">
 
    <object class="QuotationData" table="quotation_data" extends="xPDOSimpleObject">
        <field key="quote" dbtype="mediumtext" phptype="string" null="false" />
        <field key="author" dbtype="varchar" precision="200" phptype="string" null="false" default="" />
        <field key="topic" dbtype="varchar" precision="20" phptype="string" null="false" default="" />
        
        <index alias="topic" name="topic" primary="false" unique="false" type="BTREE">
            <column key="topic" length="" collation="A" null="false" />
        </index>
 
        <index alias="author" name="author" primary="false" unique="false" type="BTREE">
            <column key="author" length="" collation="A" null="false" />
        </index>
        
        <index alias="quote" name="quote" primary="false" unique="false" type="FULLTEXT">
            <column key="quote" length="" collation="A" null="false"/>
            <column key="author" length="" collation="A" null="false" />
            <column key="topic" length="" collation="A" null="false" />
        </index>
 
    </object>
</model>

[bobray]

Thank you! I’ll try to find time to test it.