http://www.mysqlkorea.co.kr
한글매뉴얼 5.0 , 한글매뉴얼 5.1 , MySQL 5.1 HA , 사용자매뉴얼
공지사항  
뉴스  
질문과 답변
DBA
Developer
Cluster
토크박스  
이벤트  
서포트 티켓  
최신글
foreign key 설정…
인텍스 추가 등에…
mysql master - s…
다대다 관계에서 …
mysql my파일과 …
 
질문과 답변 (Cluster) > 커뮤니티 존 > 질문과 답변 (Cluster)
에러 문의 드립니다.
글쓴이 : 검은날개   날짜 : 08-03-13 11:40   조회수 : 16289
클러스터를 운영을 하려고 구축중인데요
 
특정 테이블을 alter 명령으로 ndbcluster 로 변환을 할때 아래와 같은 에러가 발생합니다.
 
Error | 1296 | Got error 738 'Record too big' from NDB
Error | 1005 | Can't create table './lvs/#sql-64ce_38b.frm' (errno: 738)
 
 
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8052. You have to change some columns to TEXT or BLOBs
 
ndb를 사용할 경우 테이블에 대한 제약조껀때문에 그런건가요?
아니면 클러서터로 잡은 공간이 부족해서 인가요 ?
 
알려주시면 감사하겠습니다.
 
정진삼
shell> perror --ndb 738
  Error code 738:  Record too big: Permanent error: Schema error

두가지 대안
1. split tables
2. use blobs (no limit on blob size)
남동훈
NDB에서는 foreign key, 풀인덱스, text컬럼 인덱스를 지원하지 않는다.
(char/varchar 인덱스는 지원)

어트리뷰트 이름은 31자에서 자동으로 짤리게 된다.
데이터베이스 명, 테이블 명은 총 최대 122자 - NDB 테이블의 명의 최대 길이도
122자다.
한 테이블의 최대 컬럼과 인덱스 수는 128이고 템포러리 테이블을 지원하지 않는다.
NDB 클러스터를 사용하는 모든 테이블은 Primary key가 필요하다.
만약 사용자에서 정의도니 primary key가 없다면 보이지 않게 NDB에 의해
primary key가 생성될 것이다. 이런 보이지 않은 primary key는 테이블 레코드당
31 ~ 35 바이트씩 차지한다.
어떤 하나의 열에 제한된 최대 사이즈는 8KB이다.
각각의 BLOB or TEXT 컬럼은 264(256 + 8)바이트 추가된다

[원본]
Fun with NDBCLUSTER
MySQL 5.0 introduced NDB Cluster, a storage engine which enables running several MySQL servers in a cluster. It uses high-availability, high-redundancy version of MySQL adapted for the distributed computing environment. This all sounds really nice, and in theory, depending on your application and scalability planning, it could spare you MANY hours of developing (squeezing) database logic inside your application logic which then gets highly dependable on master/slave (active-pasive) configuration. And of course best reason of them all - instant failover. So I decided to give it a try. I used MySQL 5.0.45-0, because 5.1 sounded too much bleeding edge.

MySQL Cluster is currently not supported on Microsoft Windows, you'll have to install it somewhere else. Once you have it running, it's time to import some data. Database schema of a my test dump mostly used MyIsam storage engine and few InnoDB tables. Before the import, change Engine=NDB in the dump and be aware, import takes ages even on a really fast servers (200 kb dump = cca 1 min ??).

My best guess is, you will see a lot of HY00 errors when you start your import. Be patient, check one by one. Most of them are caused by non supported features of NDB but can be avoided.

Here is a brief coverage of reasons for the problems which I encountered:

NDB does not support foreign keys or FULLTEXT indexes or indexes on text columns (indexes on char/varchar are ok).
Attribute names are automatically truncated to 31 characters. Database names and table names can total a maximum of 122 characters - the maximum length for an NDB table name is 122 characters, less the number of characters in the name of the database of which that table is a part.
The maximum number columns and indexes per table is limited to 128.
Temporary tables are not supported.
Every table using the NDBCluster storage engine requires a primary key; if no primary key is defined by the user, then a “hidden” primary key will be created by NDB. This hidden primary key consumes 31-35 bytes per table record.
The maximum permitted size of any one row is 8KB. Note that each BLOB or TEXT column adds 256 + 8 = 264 bytes towards this total.
The last error is really nasty one and will cause you to rethink your database schema and make a lot of changes to your application):

ERROR 1118 (42000) at line 794: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 8052. You have to change some columns to TEXT or BLOBs


Some help:

The lack of foreign keys, can be resolved by using triggers (Enforcing Foreign Keys Programmatically in MySQL).
You need a lot of RAM in your severs because all data is in the RAM, unless you are using the 'DATA ON DISK' feature of MySQL 5.1.x. In that case you can have the non-indexed data on disk.
When calculating Cluster memory requirements, very useful is ndb_size.pl utility. This Perl script connects to a current MySQL (non-Cluster) database and creates a report on how much space that database would require if it used the NDBCluster storage engine. When you run the script, compare the suggested parameter values reported for your database to the default values for each shown here. If the default is higher than the recommended value from ndb_size.pl do not adjust the value. If the recommended vales are lower than the defaults use slightly larger numbers.
NDBCluster is something I'll definitively have my four eyes on and watch it grow. Bugtracker on mysql is crawling with bugs, but this is something to be expected. I've tested 5.0, and there is a lot of stuff allready fixed in 5.1. My biggest disappointment was the fact that new nodes can't be added to the cluster without shutting it down first. My conclusion is, that It is not yet ready for prime time. When it will be? when 5.1 is released into production I guess. Until then... We are on our own.

URL : http://www.nivas.hr/blog/2007/09/19/fun-with-ndbcluster/
듀크
mysqlkorea에는 정말 mysql에 깊이 관심을 두고 계신 분이 많은 것 같아서 항상 즐겁네요 ㅎㅎ

우선 결론만 간단하게 말씀 드리자면 버그입니다.

하지만 5.0.6 버전 이후로는 버그가 수정되어 잘 돌아가고 있습니다.

저도 남동훈님 처럼 긁어서 올려야 되는 건가요?;;

우선 링크는 http://bugs.mysql.com/bug.php?id=8296

이하는 긁어서 붙였습니다. 다들 홧팅! ㅋ

[3 Feb 2005 18:14] Trudy Pelzer
Description:
When a VARBINARY declaration becomes greater than
the maximum allotted size (65535 bytes), MySQL
changes the data type to BLOB (or one of its variants).
This should not happen: all silent column data type
changes should not longer occur. Instead, the server
should reject the CREATE/ALTER statement, with a
message that indicates VARBINARY's maximum size
has been reached.

How to repeat:
mysql> create table t1 (col1 varbinary(65532));
Query OK, 0 rows affected (0.01 sec)
-- This is the expected result

mysql> create table t2 (col1 varbinary(65533));
ERROR 1118 (42000): Row size too large. The maximum row size for the used table
type, not counting BLOBs, is 65535. You have to change some columns to TEXT or
BLOBs
mysql> create table t3 (col1 varbinary(65534));
ERROR 1118 (42000): Row size too large. The maximum row size for the used table
type, not counting BLOBs, is 65535. You have to change some columns to TEXT or
BLOBs
mysql> create table t4 (col1 varbinary(65535));
ERROR 1118 (42000): Row size too large. The maximum row size for the used table
type, not counting BLOBs, is 65535. You have to change some columns to TEXT or
BLOBs
-- For tables t2, t3, and t4, this is the expected result, although
the error message may lead to confusion. (I'd like to come up
with a message that indicates VARBINARY is limited to 65535 bytes
less the row/column headers, but that's a separate issue.)

mysql> create table t5 (col1 varbinary(65536));
Query OK, 0 rows affected, 1 warning (0.01 sec)
-- This is the incorrect result. As soon as the maximum size
of 65535 is exceeded, no data type change should be
made; the server should return SQLSTATE 42000, just as for
tables t2, t3, and t4 above.

mysql> show warnings;
+-------+------+-----------------------------------------------+
| Level | Code | Message                                      |
+-------+------+-----------------------------------------------+
| Note  | 1246 | Converting column 'col1' from VARCHAR to BLOB |
+-------+------+-----------------------------------------------+
1 row in set (0.00 sec)
-- This result should never occur.

mysql> show tables;
+--------------+
| Tables_in_tp |
+--------------+
| t1          |
| t5          |
+--------------+

[3 Feb 2005 18:15] Trudy Pelzer
Note that this bug is related to Bug#8295 and also to Bug#7417.[10 Mar 2005 16:05] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/22899

[7 May 2005 16:51] Trudy Pelzer
Per Monty's comment, the job is to:

1) If sql_mode='traditional', there is no column data type change.
That is, the following returns an error:
CREATE TABLE t1 (col1 VARBINARY(65537));

2) If sql_mode='', there is a column data type change, with a warning generated:
CREATE TABLE t1 (col1 VARBINARY(65537));
results in a col1 BLOB field and the current warning message.

[9 May 2005 18:07] Ramil Kalimullin
Fixed in 5.0.6.

[12 May 2005 22:30] Paul DuBois
Noted in 5.0.6 changelog and "silent column
conversions" section.
검은날개
답변주신 님들 모두 감사 합니다.
mysql 버젼을 올려야 겟네요 ^^
검은날개
mysql-5.0.6 은 어디서 다운을 받아야 하는지요 ?? ^^;;
사이트에가도 5.x 는 5.0.51 만 보이네요.
아시는분 url 좀알려주심 감사하겠습니다.
듀크
여기서 다운 받으시면 됩니다.
http://dev.mysql.com/downloads/mysql/5.0.html

5.0.6 이상 버전이면 된다는 말씀이지 꼭 5.0.6이여야 하는 건 아닙니다.
검은날개
왜 저는 안될까요 ㅋㅋ
5.1로 재컴파일 중입니다 .
듀크
바이너리 설치로 해보세요.
검은날개
아이고 5.0.6-beta-max 버젼을 깔았는데요
show warnings;
+---------+------+--------------------------------------------------------------+
| Level  | Code | Message                                                      |
+---------+------+--------------------------------------------------------------+
| Warning | 1071 | Specified key was too long; max key length is 255 bytes      |
| Warning | 1071 | Specified key was too long; max key length is 255 bytes      |
| Error  | 1296 | Got error 738 'Record too big' from NDB                      |
| Error  | 1005 | Can't create table './rgboard/#sql-1928_88.frm' (errno: 738)

메시지가 발생 하네요 ㅋㅋ
강현성
버전이 가장 최근껄로 함 올려보세요
기왕이면 beta 말고
검은날개
리눅스 시스템이 x86_64 인데요 여전히 동일현상이네요
혹시 지금 잘되시는분 설치 파일좀 받아볼수 있을까요
이전글 클러스터는 아닌데 비슷해서 여기에 질문 남깁니다. 
다음글 mysql cluster에서의 lock? 
MySQL Korea 사이트의 컨텐츠 소유권은 (주)상상이비즈에 있으므로 무단전재를 금합니다.
Copyright ⓒ ssebiz All Rights Reserved.