Database problem

Management and Administration
1

drbowen wrote on Thursday, October 27, 2005:

I have been up-loading images into the MySQL database using a routine that I wrote.  I have two tables. imagemetadata holds all the metadata and imagedata holds all the images.  Currently Imagemetadata has 9791 images with 1.3 megabytes taken up in the database.  The imagedata table is reporting 9687 images with 4.0 gigabytes in the table. I have 13 gigabytes of storage on the server in the /var directory.

The imagedata table has stopped accepting data.  The reason for this is unclear to me.  Does anyone know what might be causing this problem?

I’m a bit suspicious that this might be a MySQL limitation because the problem occured just as the imagedata table reached 4 gigabytes.

Sam Bowen, MD

2

ballards wrote on Thursday, October 27, 2005:

Dr. Bowen,

What Linux version are you running?
What version of MySQL are you using?
What is the table type of "imagedata"?
What is the column type you are using to store the image?

You probably should be using the "InnoDB" table type, available after MySQL 3.23.29. The 4GB limit translates to a 32-bit integer;  some older versions of Linux cannot handle more than a 32-bit file offset, which is why the file size is limited to 4GB. Never versions of Linux handle 64-bit offsets.

Ballards

3

drbowen wrote on Thursday, October 27, 2005:

Well, a bit of Googling has revealed that 4 gigabytes was typical of MySQL 3.22 but may be related to the file system I am using.

http://dev.mysql.com/doc/refman/5.0/en/table-size.html

This suggests that the problem may be with my underlying file system.

MySQL version 4.0.23
Slackware kernel (currently) 2.4.26
Reiserfs  3.6.18

Any thoughts on how to get out of this pickle.

Sam Bowen

4

drbowen wrote on Thursday, October 27, 2005:

As far as I know all the tables in OpenEMR are MyISAM and this is what I used.

column type is medium blob (I think)

5

drbowen wrote on Thursday, October 27, 2005:

I can easily reboot into a 2.65 kernel and this may help but it may be related to the MyISAM table type?

6

ballards wrote on Thursday, October 27, 2005:

Dr. Bowen,

Hmmm… I  use the redhat / ext3 file system myself, not reiserfs, so I will defer to persons more knowledgeable . I note that the curren version of reiserfs is 4+, (seehttp://www.namesys.com/v4/v4.html) but 3+ does not seem to have the limitation you describe. Your kernel version seems ok. What version of php are you using?

Ballards

7

sunsetsystems wrote on Thursday, October 27, 2005:

Sam, it looks like you may have already found the solution. The article you reference says this:

---------- Begin quoted text ----------

By default, MySQL creates MyISAM tables with an internal structure that allows a maximum size of about 4GB. You can check the maximum table size for a table with the SHOW TABLE STATUS statement or with myisamchk -dv tbl_name. See Section 13.5.4, SHOW Syntax.
If you need a MyISAM table that is larger than 4GB in size (and your operating system supports large files), the CREATE TABLE statement allows AVG_ROW_LENGTH and MAX_ROWS options. See Section 13.1.5, CREATE TABLE Syntax. You can also change these options with ALTER TABLE after the table has been created, to increase the table’s maximum allowable size. See Section 13.1.2, ALTER TABLE Syntax.

---------- End quoted text ----------

– Rod (http://www.sunsetsystems.com/)

8

drbowen wrote on Thursday, October 27, 2005:

SHOW TABLE STATUS;

reveals

imagedata | MyISAM | Dynamic | 26898 | 159675 |  4294958336 |  4294967295 | 1216512 |  0 | 98025

So the table size is 4294958336  with a maximum of 4294967295

this leaves just under 8 kilobytes of room in the table.

9

drbowen wrote on Thursday, October 27, 2005:

I don’t see an alter table option to switch table types.

The best working solution may be to create

imagedata_innodb

with the same structure as the already existing MyISAM table

imagedata

copy all data from

imagedata  ->   imagedata_innodb

drop table imagedata;

rename imagedata_innodb  ->  imagedata

10

drbowen wrote on Saturday, November 05, 2005:

ALTER TABLE imagedata type=innodb;

I now have a InnoDB table.

mysql> SHOW TABLE STATUS from openemr272 like ‘imagedata’;
±----------±-------±-----------±------±---------------±------------±----------------±-------------±----------±---------------±------------±------------±-----------±---------------±---------------------+
| Name      | Type   | Row_format | Rows  | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment              |
±----------±-------±-----------±------±---------------±------------±----------------±-------------±----------±---------------±------------±------------±-----------±---------------±---------------------+
| imagedata | InnoDB | Dynamic    | 27321 |         160717 |  4390961152 |            NULL |       425984 |         0 |          98025 | NULL        | NULL        | NULL       |                | InnoDB free: 6144 kB |
±----------±-------±-----------±------±---------------±------------±----------------±-------------±----------±---------------±------------±------------±-----------±---------------±---------------------+
1 row in set (0.12 sec)

This took about 19 minutes.

Does the new InnoDB table need to be enlarged before use?

11

ballards wrote on Sunday, November 06, 2005:

Dr. Bowen,

AFAIK, InnoDB tables automatically resize - because they are composed of multiple files unlike the other table types. I have never had to "enlarge" one.