By Aurian Noreinor on

Hi there,

I am trying to create a custom module in Drupal 6.x. The module needs an existing database for it's values.
I have the database as a database.sql file which has the values in the form of insert statements.

Normally this code works in the .install file as long as there are only few values:

	$datasql = file_get_contents($module_path .'/sql/database.sql');
	db_query($datasql);

But since the sql file is now 8 MB, db_query is not able to run it and it gives an "MySQL has gone away" .

I cannot change the packet size as suggested by another user. Does anyone have a fix for this? Any kind of database script to automatically load any database when i install a module?

Aurian

⋅ Categories: Drupal 6.x

Comments

dman’s picture

dman commented

Yeah...
I'm not surprised that db_query has trouble with an 8MB query!

Well, I'd like to suggest batch API, but even that would crumble under the strain just trying to count the number of things to be done.
If you insist on distributing data as raw SQL, I guess you can only fall back to the commandline and construct a eval("mysql -u $dbuser -p$dbpass $dbname < $filename");
sort of thing. There are lots of unknowns and a lot of error handling to do there, but it works. See the backup_migrate module for some ideas.

I have doubts about this raw-sql approach, but don't know enough to suggest what would suit your purpose better.

.dan. is the New Zealand Drupal Developer working on Government Web Standards

Aurian Noreinor’s picture

Aurian Noreinor commented

Dman.. thanks for the quick response.
How would you go about something like this?
I need to have a dataset to use in my module but I want to be able to install it easily without having to manually use something like PhpMyAdmin to load the data.
The user should just have to enable the module. The module would then create the table structure and load the data.

Some noob questions...
Is there an alternative to this raw sql approach?
Should I read the sql file line by line and create individual insert statements for each set of values and pass to db_query?

dman’s picture

dman commented

How would you go about something like this?

Well, I gave you a sketch of the key code required, and a reference to an example. :-}

Is there an alternative to this raw sql approach?

Depends on the actual dataset and how you expect to maintain it. What is it?

Should I read the sql file line by line and create individual insert statements for each set of values and pass to db_query?

Normally that could be a possibility, but with 8MB, anything could fail.

Can you break the input into sane chunks, (say 100 queries per file). Then you could use batch API to process them in a queue.

.dan. is the New Zealand Drupal Developer working on Government Web Standards