because they use the default engine type which is myISAM in most cases. This small patch fixes this to use in-memory HEAP tables instead. It degrades gracefully to iuse MyISAm should HEAP not be available.

CommentFileSizeAuthor
#20 heap.d5.patch1.64 KBFreso
#8 heap_0.patch1.61 KBkilles@www.drop.org
heap.patch1.61 KBkilles@www.drop.org
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

moshe weitzman’s picture

moshe weitzman CreditAttribution: moshe weitzman commented

seems like a good idea. subscribing

m3avrck’s picture

m3avrck CreditAttribution: m3avrck commented

How does this affect memory usage on the server? Is there a recommended "make an extra 128kb available" type deal?

Sounds like a great idea!

killes@www.drop.org’s picture

killes@www.drop.org CreditAttribution: killes@www.drop.org commented

There will be of course higher memory usage, but I am not sure how big it will be. The tables are usually not that big and the memory should be reclaimed at the end of the page request.

brashquido’s picture

brashquido CreditAttribution: brashquido commented

Not being any sort of MySQL guru I'd assume you'd do this for a performance improvement? Any idea what kind of ball park improvement you'd get by doing this?

m3avrck’s picture

m3avrck CreditAttribution: m3avrck commented

AFAIK, this only affects searching, that is the only module in core that makes use of it. Views_search makes use of it as well.

Souvent22’s picture

Souvent22 CreditAttribution: Souvent22 commented

Subscribing

pwolanin’s picture

pwolanin CreditAttribution: pwolanin commented

Based on the docs on mysql.com, it seems the right approach for such a temporary table. I think I saw somewhere else that this is already running on drupal.org?

http://dev.mysql.com/doc/refman/4.1/en/memory-storage-engine.html

killes@www.drop.org’s picture

killes@www.drop.org CreditAttribution: killes@www.drop.org commented
Version: 5.x-dev » 6.x-dev
Status: Needs review » Reviewed & tested by the community
FileSize
heap_0.patch1.61 KB

Indeed, this patch is running without a problem on drupal.org for about two months or so. I've rerolled it to remove fuzz and declare it ready to go.

Steven’s picture

Steven CreditAttribution: Steven commented
Version: 6.x-dev » 5.x-dev

Committed to HEAD. Should this be backported to 5?

Steven’s picture

Steven CreditAttribution: Steven commented
Status: Reviewed & tested by the community » Patch (to be ported)
killes@www.drop.org’s picture

killes@www.drop.org CreditAttribution: killes@www.drop.org commented

IMO yes. If Neil applies this to 5 I'll apply it to 4.7.

moshe weitzman’s picture

moshe weitzman CreditAttribution: moshe weitzman commented

I don't see how this could be called a bug, and thus i am a bit suspicious about backport. my .02

kbahey’s picture

kbahey CreditAttribution: kbahey commented

There is potentially scenarios where memory could be exhausted, e.g. on shared accounts.

Just to make sure this will not break memory limits, can we please have this simple patch run on Drupal.org for a week or so, with this line added:

watchdog('debug', 'Temp table search rows: ' . $count);

Before the line:

$count_query = "SELECT $count";

in do_search() in search.module.

I am more concerned about common terms that return too many rows, e.g. "drupal" or "module".

Anonymous’s picture

Anonymous (not verified) CreditAttribution: Anonymous commented

I updated my development area and all of a sudden I was getting an invalid filename for some C:\TEMP file. The log message is below:

Can't create/write to file 'C:\TEMP\#sql_388_0.MYI' (Errcode: 13) query: SELECT DISTINCT b.* FROM DEV_blocks b LEFT JOIN DEV_blocks_roles r ON b.module = r.module AND b.delta = r.delta WHERE b.theme = 'garland' AND b.status = 1 AND (r.rid IN (1) OR r.rid IS NULL) ORDER BY b.region, b.weight, b.module in E:\devshop\www\drupalCVS\HEAD-DEV\includes\database.mysql.inc on line 173.

I reverted this patch and things began working again. This error occurred following a successful install.php before creating the id 1 user.

Anonymous’s picture

Anonymous (not verified) CreditAttribution: Anonymous commented

I updated my development area and all of a sudden I was getting an invalid filename for some C:\TEMP file. The log message is below:

Can't create/write to file 'C:\TEMP\#sql_388_0.MYI' (Errcode: 13) query: SELECT DISTINCT b.* FROM DEV_blocks b LEFT JOIN DEV_blocks_roles r ON b.module = r.module AND b.delta = r.delta WHERE b.theme = 'garland' AND b.status = 1 AND (r.rid IN (1) OR r.rid IS NULL) ORDER BY b.region, b.weight, b.module in E:\devshop\www\drupalCVS\HEAD-DEV\includes\database.mysql.inc on line 173.

I reverted this patch and things began working again. This error occurred following a successful install.php before creating the id 1 user.

Anonymous’s picture

Anonymous (not verified) CreditAttribution: Anonymous commented

I updated my development area and all of a sudden I was getting an invalid filename for some C:\TEMP file. The log message is below:

Can't create/write to file 'C:\TEMP\#sql_388_0.MYI' (Errcode: 13) query: SELECT DISTINCT b.* FROM DEV_blocks b LEFT JOIN DEV_blocks_roles r ON b.module = r.module AND b.delta = r.delta WHERE b.theme = 'garland' AND b.status = 1 AND (r.rid IN (1) OR r.rid IS NULL) ORDER BY b.region, b.weight, b.module in E:\devshop\www\drupalCVS\HEAD-DEV\includes\database.mysql.inc on line 173.

I reverted this patch and things began working again. This error occurred following a successful install.php before creating the id 1 user.

Anonymous’s picture

Anonymous (not verified) CreditAttribution: Anonymous commented

Apologies. I had received a ``500 - Internal Server Error'' in posting and had ass-u-me-d that the post had not been posted.

Heine’s picture

Heine CreditAttribution: Heine commented

I updated my development area and all of a sudden I was getting an invalid filename for some C:\TEMP file.

How did you reach that conclusion?

Error 13 means "OS error code 13: Permission denied" on WinXP. Can you please check the privileges of the user account that runs the mysql server? Also, check whether the file already exists, but is simply not writeable by the current user (example: another user created such a file).

# is a perfectly valid character on NTFS and FAT32.

Anonymous’s picture

Anonymous (not verified) CreditAttribution: Anonymous commented

@Heine: Thanks for pointing out my fallicies based on what I believed to be an MS limitation just because it was MS. The ``Permission Denied'' error is being caused by slow closing of handles of the open file and the create request for the file happens before all handles to the file are closed. This seems to be prevalent on my system with some unknown criteria exacerbating the issue. I hope to try again to see the frequency of the issue. I now conclude that this is not a bug for Drupal but may cause issues to arrise as if it were. Therefore, it may be wise to consider filtering the patch to LAMP only but waiting for issues about it to queue is also a worthy consideration. Another solution is to make this use configurable.

Freso’s picture

Freso CreditAttribution: Freso commented
Status: Patch (to be ported) » Needs review
FileSize
heap.d5.patch1.64 KB

Whether it should go into D5 or not is not my call, but here's a port for 5.x anyway. (Not tested.)

drumm’s picture

drumm
he/him
Location NY, US
CreditAttribution: drumm commented

This looks like a good idea, but will need testing in a couple hosting environments.

drumm’s picture

drumm
he/him
Location NY, US
CreditAttribution: drumm commented
Status: Needs review » Fixed

Hasn't been an issue in D6 for over a year, so committed to 5.x.

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.