MySQL Extended Inserts..

For bug reports and fixes, installation issues, and new ideas for technical features.

Moderator: SEOW Developers

Post Reply
B16Enk
Posts: 22
Joined: Tue 04 Sep 2007 5:03 am
Contact:
Contact B16Enk

MySQL Extended Inserts..

Post by B16Enk »

..really make my head hurt.

I have yet to find a way to run the MySQL Queries that you guys have generated using Extended inserts, I have to run through each query and convert them to standard inserts, which takes some time on the reference db with over 550K rows!! :o

OK so I cheat and use a macro now but..

Could you please consider not using extended inserts for future releases?

This opens up the possibility of using a utility such as bigdump to import the database first time every time, something I am building into the SEOW Server-in-a-box that I am in the process of updating.. ;) ;) .

I'm sure this would be appreciated more widely!
Top
IV/JG7_4Shades
Posts: 2211
Joined: Mon 08 Jan 2007 11:10 pm
Location: Perth, Western Australia

Post by IV/JG7_4Shades »

Hi B16Enk,

Oh, the reason I use extended inserts is to reduce load times for some of the more standard tools. For example, using extended inserts can reduce load times by a factor of 50 or more, especially if you are loading your .sql file to a remote MyQSL server.

I just had a look at the BIGDUMP documentation. Seems neat that it can do what it says, but really it is a horrible cludge to get around what is really a phpMyAdmin weakness.

Tell me, B16Enk, are you using the individual databases in your installer, or are you trying to load the full (reference DB)? I can release standard SQL format files (no extended inserts), but it would be nice to hear if many other people feel the same that you do.

Cheers,
4Shades
IV/JG7_4Shades
SEOW Developer

Image
Top
B16Enk
Posts: 22
Joined: Tue 04 Sep 2007 5:03 am
Contact:
Contact B16Enk

Post by B16Enk »

Hi M8.

I'm building it with the individual 'lite' databases, using Navicat 8.

I find, for instance, that Manchurialite.sql is one query that times out, for both the installer and for a native MySQL install on my dev system.

For a test I converted the full reference DB to standard inserts and used BigDump on the installer, and it loaded the DB without any issue :)

I have had similar problems uploading databases via phpMyAdmin to remote hosts (and for most hosting solutions this is the only tool available) and BigDump has always come through.

I have to say that Navicat will fail on remote servers too with Manchuria (there are two or three others that are the same) and using either BigDump, or the time honoured way of breaking the query into smaller chunks works well. Of course if it's an extended insert then some tables still won't import..

Far be it for me to pop up and say that problems I experience are the norm, but I would be curious to know if others would like a choice of standard or extended..
Top
B16Enk
Posts: 22
Joined: Tue 04 Sep 2007 5:03 am
Contact:
Contact B16Enk

Post by B16Enk »

Forgot to say..

What I am planning on releasing is an installer that utilises BigDump to populate the databases.

This has two distinct advantages:

1. Much smaller package that with a few clicks will provide exactly what the end user requires (an installer with all DBs populated would be around 200Mb) populating only the database(s) they require.

2. Ability to update the installer easily, by the end user, to the most current version of SEOW.

I'm planning this to all be driven from a web interface, a server admin interface, to reduce the technical know how requirements.

I have the next two weeks off work and have set this as a personal challenge :)
Top
IV/JG7_4Shades
Posts: 2211
Joined: Mon 08 Jan 2007 11:10 pm
Location: Perth, Western Australia

Post by IV/JG7_4Shades »

Just for people's general information, the SEOW Installation page at the SEOWiki says to add the following line to your MySQL "my.ini" file:
#extend query chunk size to something sensible
max_allowed_packet=64M
That would normally give you enough memory allocation to load long extended SQL inserts required for the Airbase_Layouts, Tile_Map_Codes and Highways tables in the SEDB. Of course, some ISPs don't like people fiddling with MySQL server setups, but others allow it.

B16Enk, I don't have time to give you standard DB files just now, and I will be away for the next 8 days on business, sorry! Please note that I am hoping to make the new SEOW release around Christmas time, and that release will certainly have .sql files in both formats (if I remember!).

Cheers,
4Shades
IV/JG7_4Shades
SEOW Developer

Image
Top
B16Enk
Posts: 22
Joined: Tue 04 Sep 2007 5:03 am
Contact:
Contact B16Enk

Post by B16Enk »

Thanks Shades, no worries on the standard queries for now, I have converted all the current ones :)

I hear what you say regarding ISPs/Hosting companies not allowing custom MySQL my.ini files.

Even though I have shell access it is 'jailed' and of course this is not possible.

Sounds like a cool Christmas present coming from the SEOW Developers!! :o :D
Top
102nd-HR-cmirko
Posts: 179
Joined: Tue 16 Jan 2007 8:29 am
Contact:
Contact 102nd-HR-cmirko

Post by 102nd-HR-cmirko »

B16Enk wrote:Forgot to say..

What I am planning on releasing is an installer that utilises BigDump to populate the databases.

This has two distinct advantages:

1. Much smaller package that with a few clicks will provide exactly what the end user requires (an installer with all DBs populated would be around 200Mb) populating only the database(s) they require.

2. Ability to update the installer easily, by the end user, to the most current version of SEOW.

I'm planning this to all be driven from a web interface, a server admin interface, to reduce the technical know how requirements.

I have the next two weeks off work and have set this as a personal challenge :)
this sounds great :) !

SEOW to the masses :)

lol

thx for the present..., shout if you need help :)
Top
Post Reply

Return to “Technical Discussion”