**Corrupt file** - "create_tables.sql" in download zip

[berberama]

Install instructions here: http://www.letodms.com/doc/doku.php?id=install:install

But... When going to step #6 "Execute “create_tables.sql” to preload database"

MySQL displays error for database as:

Code:

Error

SQL query:

-- --------------------------------------------------------
--
-- Table structure for table `tblCategory`
--
CREATE TABLE `tblCategory` (
`id` int( 11 ) NOT NULL AUTO_INCREMENT ,
`name` text NOT NULL ,
PRIMARY KEY ( `id` ) ,
UNIQUE (
`name`
)
) ENGINE = MYISAM DEFAULT CHARSET = utf8;

MySQL said: Documentation
#1170 - BLOB/TEXT column 'name' used in key specification without a key length

How do we fix that. I can see table created, but they do not work (because of above error) - check.php displays:

Code:

settings->_rootDir : Ok
settings->_httpRoot : Ok
settings->_contentDir : Ok
settings->_ADOdbPath : Ok
database - table 'tblAcls' : Error

table 'tblAcls' Table not found
database - table 'tblDirPath' : Error

table 'tblDirPath' Table not found
database - table 'tblEvents' : Error

table 'tblEvents' Table not found
database - table 'tblMandatoryApprovers' : Error

table 'tblMandatoryApprovers' Table not found
database - table 'tblMandatoryReviewers' : Error

table 'tblMandatoryReviewers' Table not found
database - table 'tblNotify' : Error

table 'tblNotify' Table not found
database - table 'tblPathList' : Error

table 'tblPathList' Table not found
database - table 'tblSessions' : Error

table 'tblSessions' Table not found
database - table 'tblUsers' : Error

table 'tblUsers' 'isAdmin' not found ;
database : Error

Again, I can see all tables, but because of error (in the .sql file) they do not work.

What can I do to fix this?

Thank you.

[berberama]

Hi All...

... *bump*

Please can someone help with this?

Thanks muchly!

[berberama]

I found an answer which might help people --> http://stackoverflow.com/questions/18270...key-length

I think it suggests there's something wrong with the length or characters size?

But I'm not fully versed so have difficulty understanding. maybe some LetoDMS Guru can look at this and fix the .sql file?

I hope so!!!

Pretty please...

[berberama]

After much trying, I've narrowed down the offending MySQL entry to:

Code:

CREATE TABLE `tblCategory` (
  `id` int(11) NOT NULL auto_increment,
  `name` text NOT NULL,
  PRIMARY KEY  (`id`),
    UNIQUE (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Which throws this error:

#1170 - BLOB/TEXT column 'name' used in key specification without a key length

MySQL version is: 5.1.41
PHP version is: 5.2.13
Server API is: CGI/FastCGI
Apache version is: 2.2.15
OS versions is: centOS 5.6 i686

Please can someone help? Thanks!

---

I was directed to the following information by another PHP developer:

Note: He was able to duplicate this issue on 3 of his servers!

When creating a new table or altering an existing table with primary keys, unique constraints and indexes, or when defining a new index with Alter Table manipulation statement in MySQL database, the following error may occur and prohibit the the command from completing:

ERROR 1170 (42000): BLOB/TEXT column ‘field_name’ used in key specification without a key length

The error happens because MySQL can index only the first N chars of a BLOB or TEXT column. So The error mainly happen when there is a field/column type of TEXT or BLOB or those belongs to TEXT or BLOB types such as TINYBLOB, MEDIUMBLOB, LONGBLOB, TINYTEXT, MEDIUMTEXT, and LONGTEXT that you try to make as primary key or index. With full BLOB or TEXT without the length value, MySQL is unable to guarantee the uniqueness of the column as it’s of variable and dynamic size. So, when using BLOB or TEXT types as index, the value of N must be supplied so that MySQL can determine the key length. However, MySQL doesn’t support limit on TEXT or BLOB. TEXT(88) simply won’t work.

The error will also pop up when you try to convert a table column from non-TEXT and non-BLOB type such as VARCHAR and ENUM into TEXT or BLOB type, with the column already been defined as unique constraints or index. The Alter Table SQL command will fail.

The solution to the problem is to remove the TEXT or BLOB column from the index or unique constraint, or set another field as primary key. If you can’t do that, and wanting to place a limit on the TEXT or BLOB column, try to use VARCHAR type and place a limit of length on it. By default, VARCHAR is limited to a maximum of 255 characters and its limit must be specified implicitly within a bracket right after its declaration, i.e VARCHAR(200) will limit it to 200 characters long only.

Sometimes, even though you don’t use TEXT or BLOB related type in your table, the Error 1170 may also appear. It happens in situation such as when you specify VARCHAR column as primary key, but wrongly set its length or characters size. VARCHAR can only accepts up to 256 characters, so anything such as VARCHAR(512) will force MySQL to auto-convert the VARCHAR(512) to a SMALLTEXT datatype, which subsequently fail with error 1170 on key length if the column is used as primary key or unique or non-unique index. To solve this problem, specify a figure less than 256 as the size for VARCHAR field.

Info from: http://www.mydigitallife.info/mysql-erro...ey-length/

In essence, he told me to use varchar type (not text) and place a length limit of 255.

I will try that shortly and report.

[dkeaton]

I am getting the exact error. I cannot install 3.2 or 3.1 because of this issue. Any solutions??

[steinm]

After much trying, I've narrowed down the offending MySQL entry to:

Code:

CREATE TABLE `tblCategory` (
  `id` int(11) NOT NULL auto_increment,
  `name` text NOT NULL,
  PRIMARY KEY  (`id`),
    UNIQUE (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Which throws this error:

#1170 - BLOB/TEXT column 'name' used in key specification without a key length

MySQL version is: 5.1.41
PHP version is: 5.2.13
Server API is: CGI/FastCGI
Apache version is: 2.2.15
OS versions is: centOS 5.6 i686

Please can someone help? Thanks!

---

I was directed to the following information by another PHP developer:

...

just remove the
UNIQUE('name')

Uwe

[developer]

I faced the same problem, and what I did is to replace 'name' with 'id' instead :
CREATE TABLE `tblCategory` (
`id` int(11) NOT NULL auto_increment,
`name` text NOT NULL,
PRIMARY KEY (`id`),
UNIQUE (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Note : You may see some duplicate tables error afterwards as some tables were created halfway before this error. But the overall creation should have completed successfully.

[heraco]

I used the script and it was ok: create_tables-innodb.sql