ERROR 1005 (HY000): Can't create table '.\testDataBase\#sql-ec4_c.frm' (errno: 139)

[Sunil Vishwas]

(I am using mysql-5.0.15-win32)
Why I am getting this error and is there any way I can fix it, or is it
a bug?
Between I don't get this error if I change the field size to 767 or
below:
 
drop table Address;

CREATE TABLE `Address` 
(
 `RecId` CHAR(32) NOT NULL, 
 `WebAddress` VARCHAR(1000), 
 CONSTRAINT PKAddress PRIMARY KEY(RecId)
);
 
CREATE INDEX AddressWebAddressWebAddress ON Address (WebAddress);
 

Need explanation on string data size and storage requirement.

[Sunil Vishwas]

I was looking into the storage requirement for the various data types
and
got confused by following comment:

'For the CHAR, VARCHAR, and TEXT types, L and M in the preceding table
should be interpreted as number of bytes before MySQL 4.1 and as number
of
characters thereafter.'

Then I looked into other pages like
http://dev.mysql.com/doc/mysql/en/string-type-overview.html
http://dev.mysql.com/doc/mysql/en/string-type-overview.html  and got
more
confuse.

How it would be possible to use the same storage size to store the equal
number of characters and byte, if a character is not just a byte long?

For example UTF-8 based Unicode character may take 1 to 4 bytes.

Any help would be highly appreciated. 
 

RE: Need explanation on string data size and storage requirement.

[Sunil Vishwas]

Hi Bruce,

I am not much worried about pre 5.0.11-Beta, my question is:

How the following tables would be stored:

CREATE TABLE NonUnicodeTable (NonUnicodeColumn LONGTEXT);
CREATE TABLE UnicodeTable (UnicodeColumn LONGTEXT UNICODE);

Would both of the above tables have 2^32 - 1 characters? 

Thank you,
Sunil Vishwas 

-Original Message-
From: Bruce Dembecki [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, August 23, 2005 12:29 PM
To: Sunil Vishwas
Cc: mysql@lists.mysql.com
Subject: Re: Need explanation on string data size and storage
requirement.


On Aug 23, 2005, at 10:20 AM, Sunil Vishwas wrote:

 I was looking into the storage requirement for the various data types 
 and got confused by following comment:

 'For the CHAR, VARCHAR, and TEXT types, L and M in the preceding table

 should be interpreted as number of bytes before MySQL 4.1 and as 
 number of characters thereafter.'

 Then I looked into other pages like
 http://dev.mysql.com/doc/mysql/en/string-type-overview.html
 http://dev.mysql.com/doc/mysql/en/string-type-overview.html  and got

 more confuse.

 How it would be possible to use the same storage size to store the 
 equal number of characters and byte, if a character is not just a byte

 long?

 For example UTF-8 based Unicode character may take 1 to 4 bytes.

There is the issue... before 4.1 the limit was a size, from 4.1 onwards
the limit is the number of characters... So clearly from 4.1 onwards
your table sizes may vary depending on your character set choices.

Look at it like this... If you have a limit of 8 bytes on a password
field, and you convert your database to UTF-8 in 4.1 your password could
be much larger than 8 bytes, and may be truncated if the limit were size
based, instead it is character based. Allowing a full conversion of data
from 4.0 to 4.1 if managed correctly.

Best Regards, Bruce

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

What is MySql equivalent to SQL Server's sp_fkeys?

[Sunil Vishwas]

I need a MySql 
solution equivalent to sp_fkeys procedure of Sql Server 
2000.
As shown in the 
attached file I managed to get most of the part working, but steel searching for 
DELETE_RULE and UPDATE_RULE equivalents to programmatically check CASCADEupdate or 
delete.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Querying value of lower_case_table_names

[Sunil Vishwas]

Hi,
 
Is there any way to check value of of 'lower_case_table_names'? I need
to an equivalent of  (MS Sql Server statement)
 
exec sp_server_info @attribute_id=16
 
Thank you,
Sunil Vishwas