Concrete5 database index specified in db.xml but doesn't "take". How do I make it work?

Environment: Windows7, IIS7, MySQL 5.1.57, Concrete5 5.4.1.1

I'm getting this rather odd error message when I place my custom block:

Fatal error: Uncaught exception 'ADODB_Exception' with message 'mysql error: [-1: No primary key found for table btCtL] in BlockRecord::UpdateActiveTable(0, 0) ' in C:\inetpub\wwwroot\Concrete5\concrete\libraries\3rdparty\adodb\adodb-exceptions.inc.php:78 Stack trace: #0 
C:\inetpub\wwwroot\Concrete5\concrete\libraries\3rdparty\adodb\adodb-active-record.inc.php(494): adodb_throw('mysql', 'BlockRecord::Up...', -1, 'No primary key ...', 0, 0, Object(ADODB_mysql)) #1 
C:\inetpub\wwwroot\Concrete5\concrete\libraries\3rdparty\adodb\adodb-active-record.inc.php(402): ADODB_Active_Record->Error('No primary key ...', 'UpdateActiveTab...') #2 
C:\inetpub\wwwroot\Concrete5\concrete\libraries\3rdparty\adodb\adodb-active-record.inc.php(136): ADODB_Active_Record->UpdateActiveTable(false) #3 
C:\inetpub\wwwroot\Concrete5\concrete\libraries\block_view.php(30): ADODB_Active_Record->__construct('btCtL') #4 
C:\inetpub\wwwroot\Concrete5\concrete\libraries\block_controller.php(222): BlockRecord->__construct('btCtL') #5 
C:\inetpub\wwwroot\Concrete in C:\inetpub\wwwroot\Concrete5\concrete\libraries\3rdparty\adodb\adodb-exceptions.inc.php on line 78 

The db.xml in this case reads

<?xml version="1.0"?>
<schema version="0.3">
<table name="btCtL">
    <field name="bID" type="I">
    <unsigned />
    <key />      
    </field>
    <field name="Number_1" type="C" size="12"></field>
    <field name="Number_2" type="C" size="12"></field>
    <field name="Number_3" type="C" size="12"></field>
    <field name="Number_4" type="C" size="12"></field>
    <field name="Number_5" type="C" size="12"></field>
    <field name="Number_6" type="C" size="12"></field>
    <field name="Number_7" type="C" size="12"></field>
    <field name="Number_8" type="C" size="12"></field>
    <field name="Number_9" type="C" size="12"></field&开发者_开发技巧gt;
    <field name="Number_10" type="C" size="12"></field>
</table>
</schema>

The error has to do with there being "No primary key found for table btCtL" which is demonstrably false. If I go into phpAdmin and force the issue, it works, but I can't be doing that for every client site, nor can I expect clients to do it themselves.

Is this a bug? Is there a workaround? Or am I just thick?

The bID field for a block needs to be declared as an unsigned key, but not primary and not autoincrement:

<field name="bID" type="I"><KEY /><UNSIGNED /></field>

Not sure why it can't be primary, but definitely can't be autoincrement because bID is assigned by the Concrete5 system, and it changes every time the block is updated (because of the versioning system).

BTW, after you change your db.xml file, you'll need to update the schema by going to Dashboard -> Add Functionality, click "Edit" next to the block in question, then click the "Refresh" button.

The problem seems to be Concrete5's. I have just used MySQLInstanceConfig.exe to change from "Best Support for multilingualism" (what I had wanted in the first instance) to "Standard Character Set". This takes the character set from utf8 to Latin1. After this change a Refresh creates a PRIMARY key on bID.

Alternatively, it's MySQL 5.1's problem and may have been fixed in 5.5. Am yet to try it out.