Zikula Community

• **unknown user**
  
  

  • Rank: Senior
  • Registered: Mar 16, 2002
  • Last visit: Oct 21, 2009
  • Posts: 2330

  Posted: Mar 18, 2005 - 09:24 AM

  I think this is set on a server to server basis, but was wondering if there's an average maximum size limit on an SQL query. Anyone have any idea?
  
  I'm working up a mod that's got a big ol' INSERT (2.7MB) and was wondering how many users might have trouble with this...
  
  I know the query could be separated out, but this is neither the goal nor the intention. The install depends on users being able to handle an INSERT of this magnitude.
  
  Any ideas on a typical maximum?

  • 
  •  
• **unknown user**
  
  

  • Rank: Senior
  • Registered: Mar 16, 2002
  • Last visit: Oct 21, 2009
  • Posts: 2204

  Posted: Mar 18, 2005 - 10:32 AM

  text or blob John? ph has a config option for default timeout, but there are ways of dealing with that on a per file basis.
  
  PS: check out the banner competition.. link below.. its a chance to let your skills out of the bag in a showcase and maybe make a few bucks doing so :)

  • 
  •  
• **unknown user**
  
  

  • Rank: Senior
  • Registered: Mar 16, 2002
  • Last visit: Oct 21, 2009
  • Posts: 2330

  Posted: Mar 18, 2005 - 10:39 AM

  It's just a basic text file... file.sql...
  
  I'm running it in the pninit.php file after the table is created. I was thinking that setting the set_time_limit(0) would do the trick, but this white-screens the init function (though it does populate the DB.) How can I set the script execution time to whatever it takes... ie, temporarily turn it off even...?
  
  I'll check out the banner competition! Love competitions!!

  • 
  •  
• **unknown user**
  
  

  • Rank: Senior
  • Registered: Mar 16, 2002
  • Last visit: Oct 21, 2009
  • Posts: 2204

  Posted: Mar 18, 2005 - 10:48 AM

  ..that's a rough spot to be testing.. deleting, remoiving, blah, blah, blah :) ..
  
  I'd try calling sleep(1); and maybe ignore_user_abort(); but AFAIK, queries aren't considered part of script execution, so I'm not sure if set_time_limit(); is helping or not.. no log errors on that white screen?

  • 
  •  
• **unknown user**
  
  

  • Rank: Senior
  • Registered: Mar 16, 2002
  • Last visit: Oct 21, 2009
  • Posts: 2330

  Posted: Mar 18, 2005 - 11:00 AM

  Lol...It's not such a bad place to test...I'm inserting a boat-load of data, loaded from split files... and looping through the files systematically...this works fine. I've stripped the individual files down to 3 inserts per file for testing purposes... but in the release version, the largest SQL file that's being run is going to be 2.7Mb...the others are comparable...so I just needed to be sure that the largest was going to pan out for most users...

  • 
  •  
• **unknown user**
  
  

  • Rank: Senior
  • Registered: Mar 16, 2002
  • Last visit: Oct 21, 2009
  • Posts: 2330

  Posted: Mar 18, 2005 - 11:03 AM

  ...oh...no errors on the whitescreen...

  • 
  •  
• **unknown user**
  
  

  • Rank: Senior
  • Registered: Mar 16, 2002
  • Last visit: Oct 21, 2009
  • Posts: 2204

  Posted: Mar 18, 2005 - 11:06 AM

  hmmm... try this at the top:
  
  error_reporting(E_ALL);

  • 
  •  
• **unknown user**
  
  

  • Rank: Senior
  • Registered: Mar 16, 2002
  • Last visit: Oct 21, 2009
  • Posts: 2330

  Posted: Mar 18, 2005 - 11:14 AM

  ...hmm... must of been a temp snafu... It panned out this time ... coming obediently back with a status message and the module admin page... (keeping in mind that my 4 SQL files are 3 inserts apiece for the test...but that shouldn't matter...)

  • 
  •  
• **unknown user**
  
  

  • Rank: Senior
  • Registered: Mar 16, 2002
  • Last visit: Oct 21, 2009
  • Posts: 2330

  Posted: Mar 18, 2005 - 11:43 AM

  Question... I've got the init script pulling the SQL files, running the INSERTS and it works pretty well...but I have the DB prefix hardcoded into the SQL files. In other words, it works fine on my dev site, but when others try to init the mod, having the prefix hardcoded will fubar the script...
  
  The files I'm using to store the queries are named like so:
  a.sql
  b.sql
  c.sql
  d.sql
  etc...
  Here's a look at my test script...pay no attn. to the die()...it's temp.
  

  Code

  set_time_limit(0);
  
  $P2Mod = (dirname(__file__));
  
  $letters = range('a','d');
  
  foreach($letters as $letter)
  {
      $file = $P2Mod.'/pnsql/'.$letter.'.sql';
  
      if(file_exists($file))
      {
          foreach(file($file) as $sql)
          {
              $dbconn->Execute($sql) or die(mysql_error());
          }
      }
  }

  
  
  I don't want to have users have to search/replace the DB prefix in all the files...but don't know what else to do...
  
  This is for a pnAPI dictionary mod...which stores the dictionary in the DB. I know there are other mods out there, and yes, I know storing the dictionary in the DB isn't the best method...but it's the best way to ensure that it's dependable and always available... ie, no off-site reliances...

  • 
  •  
• **unknown user**
  
  

  • Rank: Senior
  • Registered: Mar 16, 2002
  • Last visit: Oct 21, 2009
  • Posts: 2330

  Posted: Mar 18, 2005 - 11:57 AM

  ...so far, the only viable solution I can figure is to nix using their DB prefix...and just do it that way...

  • 
  •  
• **unknown user**
  
  

  • Rank: Senior
  • Registered: Mar 16, 2002
  • Last visit: Oct 21, 2009
  • Posts: 2330

  Posted: Mar 18, 2005 - 12:44 PM

  I did a test run on the entire she-bang...it took apx. 4 minutes to initialize the module, but initialize it did! 175,000+ records inserted...and it returned no errors...SWEET!

  • 
  •  
•  
•  

Users on-line

• 0 users

This list is based on users active over the last 60 minutes.