Search Results

Search found 40581 results on 1624 pages for 'mysql select db'.

Page 274/1624 | < Previous Page | 270 271 272 273 274 275 276 277 278 279 280 281 | Next Page >

Quick Fix for GlassFish/MySQL NoPasswordCredential Found

- by MarkH

Just the other day, I stood up a GlassFish 3.1.2 server in preparation for a new web app we've developed. Since we're using MySQL as the back-end database, I configured it for MySQL (driver) and created the requisite JDBC resource and supporting connection pool. Pinging the finished pool returned a success, and all was well. Until we fired up the app, that is -- in this case, after a weekend. Funny how things seem to break when you leave them alone for a couple of days. :-) Strangely, the error indicated "No PasswordCredential found". Time to re-check that pool. All the usual properties and values were there (URL, driverClass, serverName, databaseName, portNumber, user, password) and were populated correctly. Yes, the password field, too. And it had pinged successfully. So why the problem? A bit of searching online produced enough relevant material to offer promise. I didn't take notes as I was investigating the cause (note to self), but here were the general steps I took to resolve the issue: First, per some guidance I had found, I tried resetting the password value to nothing (using () for a value). Of course, this didn't fix anything; the database account requires a password. And when I tried to put the value back, GlassFish politely refused. Hmm. I'd seen that some folks created a new pool to replace the "broken" one, and while that did work for them, it seemed to simply side-step the issue. So I deleted the password property - which GlassFish allowed me to do - and restarted the domain. Once I was back in, I re-added the password property and its value, saved it, and pinged...success! But now to the app for the litmus test. The web app worked, and everything and everyone was now happy. Not bad for a Monday. :-D Hope this helps, Mark

Read the article
Getting java.lang.ClassNotFoundException: com.mysql.jdbc.Driver Exception

- by Yashwant Chavan

Hi , I am getting Following Exception while configuring the Connection Pool in Tomcat This is Context.xml <Context path="/DBTest" docBase="DBTest" debug="5" reloadable="true" crossContext="true">       <Resource name="jdbc/TestDB" auth="Container" type="javax.sql.DataSource" maxActive="100" maxIdle="30" maxWait="10000" username="root" password="password" driverClassName="com.mysql.jdbc.Driver" url="jdbc:mysql:///BUSINESS"/> </Context> This is Bean Entry <bean id="dataSource" class="org.springframework.jndi.JndiObjectFactoryBean"> <property name="jndiName" value="jdbc/TestDB"></property> <property name="resourceRef" value="true"></property> </bean> org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Co nnection; nested exception is org.apache.tomcat.dbcp.dbcp.SQLNestedException: Ca nnot load JDBC driver class 'com.mysql.jdbc.Driver' at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(Dat aSourceUtils.java:82) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java: 382) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:45 8) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:46 6) at com.businesscaliber.dao.Dao.getQueryForListMap(Dao.java:66) at com.businesscaliber.dao.MiscellaneousDao.getDefaultSucessStory(Miscel laneousDao.java:109) at com.businesscaliber.listeners.BusinessContextLoader.contextInitialize d(BusinessContextLoader.java:40) at org.apache.catalina.core.StandardContext.listenerStart(StandardContex t.java:3795) at org.apache.catalina.core.StandardContext.start(StandardContext.java:4 252) at org.apache.catalina.core.ContainerBase.addChildInternal(ContainerBase .java:760) at org.apache.catalina.core.ContainerBase.addChild(ContainerBase.java:74 0) at org.apache.catalina.core.StandardHost.addChild(StandardHost.java:544) at org.apache.catalina.startup.HostConfig.deployWAR(HostConfig.java:831) at org.apache.catalina.startup.HostConfig.deployWARs(HostConfig.java:720 ) at org.apache.catalina.startup.HostConfig.deployApps(HostConfig.java:490 ) at org.apache.catalina.startup.HostConfig.start(HostConfig.java:1150) at org.apache.catalina.startup.HostConfig.lifecycleEvent(HostConfig.java :311) at org.apache.catalina.util.LifecycleSupport.fireLifecycleEvent(Lifecycl eSupport.java:120) at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1022) at org.apache.catalina.core.StandardHost.start(StandardHost.java:736) at org.apache.catalina.core.ContainerBase.start(ContainerBase.java:1014) at org.apache.catalina.core.StandardEngine.start(StandardEngine.java:443 ) at org.apache.catalina.core.StandardService.start(StandardService.java:4 48) at org.apache.catalina.core.StandardServer.start(StandardServer.java:700 ) at org.apache.catalina.startup.Catalina.start(Catalina.java:552) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl. java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAcces sorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:585) at org.apache.catalina.startup.Bootstrap.start(Bootstrap.java:295) at org.apache.catalina.startup.Bootstrap.main(Bootstrap.java:433) Caused by: org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot load JDBC driv er class 'com.mysql.jdbc.Driver' at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDat aSource.java:1136) at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSo urce.java:880) at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(D ataSourceUtils.java:113) at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(Dat aSourceUtils.java:79) ... 30 more Caused by: java.lang.ClassNotFoundException: com.mysql.jdbc.Driver at java.net.URLClassLoader$1.run(URLClassLoader.java:200) at java.security.AccessController.doPrivileged(Native Method) at java.net.URLClassLoader.findClass(URLClassLoader.java:188) at java.lang.ClassLoader.loadClass(ClassLoader.java:306) at java.lang.ClassLoader.loadClass(ClassLoader.java:251) at java.lang.ClassLoader.loadClassInternal(ClassLoader.java:319) at java.lang.Class.forName0(Native Method) at java.lang.Class.forName(Class.java:164) at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDat aSource.java:1130)

Read the article
Multiple database connection in Rails

- by Sanal

I'm using active_delegate for multiple connection in Rails. Here I'm using mysql as master_database for some models,and postgresql for some other models. Problem is that when I try to access the mysql models, I'm getting the error below! Stack trace shows that, it is still using the postgresql adapter to access my mysql models! RuntimeError: ERROR C42P01 Mrelation "categories" does not exist P15 F.\src\backend\parser\parse_relation.c L886 RparserOpenTable: SELECT * FROM "categories" STACKTRACE =========== d:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/connection_adapters/abstract_adapter.rb:212:in `log' d:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/connection_adapters/postgresql_adapter.rb:507:in `execute' d:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/connection_adapters/postgresql_adapter.rb:985:in `select_raw' d:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/connection_adapters/postgresql_adapter.rb:972:in `select' d:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/connection_adapters/abstract/database_statements.rb:7:in `select_all_without_query_cache' d:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/connection_adapters/abstract/query_cache.rb:60:in `select_all' d:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/connection_adapters/abstract/query_cache.rb:81:in `cache_sql' d:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/connection_adapters/abstract/query_cache.rb:60:in `select_all' d:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/base.rb:661:in `find_by_sql' d:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/base.rb:1553:in `find_every' d:/ruby/lib/ruby/gems/1.8/gems/activerecord-2.3.2/lib/active_record/base.rb:615:in `find' D:/ROR/Aptana/dedomenon/app/models/category.rb:50:in `get_all_with_exclusive_scope' D:/ROR/Aptana/dedomenon/app/models/category.rb:50:in `get_all_with_exclusive_scope' D:/ROR/Aptana/dedomenon/app/controllers/categories_controller.rb:48:in `index' here is my database.yml file postgre: &postgre adapter: postgresql database: codex host: localhost username: postgres password: root port: 5432 mysql: &mysql adapter: mysql database: project host: localhost username: root password: root port: 3306 development: <<: *postgre test: <<: *postgre production: <<: *postgre master_database: <<: *mysql and my master_databse model is like this class Category < ActiveRecord::Base delegates_connection_to :master_database, :on => [:create, :save, :destroy] end Anyone has any solution??

Read the article
Using a class within a class?

- by Josh

I built myself a MySQL class which I use in all my projects. I'm about to start a project that is heavily based on user accounts and I plan on building my own class for this aswell. The thing is, a lot of the methods in the user class will be MySQL queries and methods from the MySQL class. For example, I have a method in my user class to update someone's password: class user() { function updatePassword($usrName, $newPass) { $con = mysql_connect('db_host', 'db_user', 'db_pass'); $sql = "UPDATE users SET password = '$newPass' WHERE username = '$userName'"; $res = mysql_query($sql, $con); if($res) return true; mysql_close($con); } } (I kind of rushed this so excuse any syntax errors :) ) As you can see that would use MySQL to update a users password, but without using my MySQL class, is this correct? I see no way in which I can use my MySQL class within my users class without it seeming dirty. Do I just use it the normal way like $DB = new DB();? That would mean including my mysql.class.php somewhere too... I'm quite confused about this, so any help would be appreciated, thanks.

Read the article
My SQL query is only returning I need the parent aswell

- by sico87

My sql query is only returning the children of the parent I need it to return the parent as well, public function getNav($cat,$subcat){ //gets all sub categories for a specific category if(!$this->checkValue($cat)) return false; //checks data $query = false; if($cat=='NULL'){ $sql = "SELECT itemID, title, parent, url, description, image FROM p_cat WHERE deleted = 0 AND parent is NULL ORDER BY position;"; $query = $this->db->query($sql) or die($this->db->error); }else{ //die($cat); $sql = "SET @parent = (SELECT c.itemID FROM p_cat c WHERE url = '".$this->sql($cat)."' AND deleted = 0); SELECT c1.itemID, c1.title, c1.parent, c1.url, c1.description, c1.image, (SELECT c2.url FROM p_cat c2 WHERE c2.itemID = c1.parent LIMIT 1) as parentUrl FROM p_cat c1 WHERE c1.deleted = 0 AND c1.parent = @parent ORDER BY c1.position;"; $query = $this->db->multi_query($sql) or die($this->db->error); $this->db->store_result(); $this->db->next_result(); $query = $this->db->store_result(); } return $query; }

Read the article
Is it better to use a relational database or document-based database for an app like Wufoo?

- by mboyle

I'm working on an application that's similar to Wufoo in that it allows our users to create their own databases and collect/present records with auto generated forms and views. Since every user is creating a different schema (one user might have a database of their baseball card collection, another might have a database of their recipes) our current approach is using MySQL to create separate databases for every user with its own tables. So in other words, the databases our MySQL server contains look like: main-web-app-db (our web app containing tables for users account info, billing, etc) user_1_db (baseball_cards_table) user_2_db (recipes_table) .... And so on. If a user wants to set up a new database to keep track of their DVD collection, we'd do a "create database ..." with "create table ...". If they enter some data in and then decide they want to change a column we'd do an "alter table ....". Now, the further along I get with building this out the more it seems like MySQL is poorly suited to handling this. 1) My first concern is that switching databases every request, first to our main app's database for authentication etc, and then to the user's personal database, is going to be inefficient. 2) The second concern I have is that there's going to be a limit to the number of databases a single MySQL server can host. Pretending for a moment this application had 500,000 user databases, is MySQL designed to operate this way? What if it were a million, or more? 3) Lastly, is this method going to be a nightmare to support and scale? I've never heard of MySQL being used in this way so I do worry about how this affects things like replication and other methods of scaling. To me, it seems like MySQL wasn't built to be used in this way but what do I know. I've been looking at document-based databases like MongoDB, CouchDB, and Redis as alternatives because it seems like a schema-less approach to this particular problem makes a lot of sense. Can anyone offer some advice on this?

Read the article
Displaying unnecessary HTML when showing content from MySQL database.

- by ThatMacLad

My homepage pulls in content from my MySQL database to create a blog. I've got it so that it only displays an extract from the posts. For some reason it displays HTML tags as well rather than formatting it using the tags (See picture below). Any help is appreciated. Homepage: <html> <head> <title>Ultan Casey | Homepage</title> <link rel="stylesheet" href="css/style.css" type="text/css" /> </head> <body> <div class="wrapper"> <div id="upperbar"> <a href="#">Home</a> <a href="#">About Me</a> <a href="#">Contact Me</a> <a href="http://www.twitter.com/UltanKC">Twitter</a> <form id="search-form" action="/search" method="get"> <input type="text" id="textarea" size="33" name="q" value=""/> <input type="submit" id="submit" value="Search"/> </form> </div> <div id="banner"> <img src="images/banner.jpg"> </div> <div class="sidebar"></div> <div class="posts"> <?php mysql_connect ('localhost', 'root', 'root') ; mysql_select_db ('tmlblog'); $sql = "SELECT * FROM php_blog ORDER BY timestamp DESC LIMIT 5"; $result = mysql_query($sql) or print ("Can't select entries from table php_blog.<br />" . $sql . "<br />" . mysql_error()); while($row = mysql_fetch_array($result)) { $date = date("l F d Y", $row['timestamp']); $title = stripslashes($row['title']); $entry = stripslashes($row['entry']); $id = $row['id']; ?> <?php echo "<p id='title'><strong><a href=\"post.php?id=". $id . "\">" . $title . "</a></strong></p>"; ?><br /> <div class="post-thumb"><img src="thumbs/<?php echo $id ?>.png"></div> <?php echo htmlspecialchars(substr($entry, 0, 1050)) ?>... <br> <hr><br /> Posted on <?php echo $date; ?> </p> </div> </div> </p <?php } ?> </div> </div> </div> </body> </html> Image:

Read the article
Moving from MySQL to MySQLi? I have the code here but I don't get it

- by MuqMan

I have posted the code there, please help me out as I am a newbie, I don't know much in terms of deprecation and stuff. <?php session_start(); include('settings.php'); $issub = $_POST['issub']; if($issub == "yes") { require('settings.php'); $dbcon = mysql_connect($dbhost, $dbuser, $dbpword); if(!dbcon) { die('Could not connect'.mysql_error()); } $selectdb = mysql_select_db($db, $dbcon); $formset = 'yes'; $val = 0; $user = trim($_POST['username'], ' '); $luser = mysql_real_escape_string($user); $password = $_POST['password']; $lpassword = mysql_real_escape_string($password); $selectdb; $userq = mysql_query("SELECT user FROM users WHERE user='".$luser."'"); $userresult = @mysql_result($userq, 0); //echo $userresult; if($userresult == $user) { $val = $val + 1; $usercorrect = 'yes'; } else { $usercorrect = 'no'; } $dbselect; $passwordq = mysql_query("SELECT password FROM users where user='".$luser."'"); $passresult = @mysql_result($passwordq, 0); if($passresult == sha1($password)) { $val = $val + 1; $passcorrect = 'yes'; } else { $passcorrect = 'no'; } if ($val == 2) { $_SESSION['loggedin'] = 'yes'; $_SESSION['uloggedin'] = $user; header('location: logged.php'); } }?> <?php ini_set('display_errors', 1); require('testinclude.php'); ?> <body> <div id="loginform"> <form action="/login.php" method="post" > <input type="hidden" name="issub" value="yes" /> <?php if($usercorrect == 'no') { echo '<span class="required"><i><small>The email address or password you entered is incorrect, please try again.</a></small></i></span>'; } ?> <br /> email: <?php if ($issub == 'yes') { if($user == null){ echo '<br /><span class="required"><i><small>Please enter your email address</a></small></i></span>'; } } ?> <br /><input type="text" name="username" id="usename" /> <br /> password: <br /><input type="password" name="password" id="password" /> <br /> <input type="submit" value="login" /> </form> <div> </body>

Read the article
Rollback doesn't work in MySQLdb

- by Anton Barycheuski

I have next code ... db = MySQLdb.connect(host=host, user=user, passwd=passwd, db=db, charset='utf8', use_unicode=True) db.autocommit(False) cursor = db.cursor() ... for col in ws.columns[1:]: data = (col[NUM_ROW_GENERATION].value, 1, type_topliv_dict[col[NUM_ROW_FUEL].value]) fullgeneration_id = data[0] type_topliv = data[2] if data in completions_set: compl_id = completions_dict[data] else: ... sql = u"INSERT INTO completions (type, mark, model, car_id, type_topliv, fullgeneration_id, mark_id, model_id, production_period, year_from, year_to, production_period_url) VALUES (1, '%s', '%s', 0, %s, %s, %s, %s, '%s', '%s', '%s', '%s')" % (marks_dict[mark_id], models_dict[model_id], type_topliv, fullgeneration_id, mark_id, model_id, production_period, year_from, year_to, production_period.replace(' ', '_').replace(u'?.?.', 'nv') ) inserted_completion += cursor.execute(sql) cursor.execute("SELECT fullgeneration_id, type, type_topliv, id FROM completions where fullgeneration_id = %s AND type_topliv = %s" % (fullgeneration_id, type_topliv)) row = cursor.fetchone() compl_id = row[3] if is_first_car: deleted_compl_rus = cursor.execute("delete from compl_rus where compl_id = %s" % compl_id) for param, row_id in params: sql = u"INSERT INTO compl_rus (compl_id, modification, groupparam, param, paramvalue) VALUES (%s, '%s', '%s', '%s', %s)" % (compl_id, col[NUM_ROW_MODIFICATION].value, param[0], param[1], col[row_id].value) inserted_compl_rus += cursor.execute(sql) is_first_car = False db.rollback() print '\nSTATISTICS:' print 'Inserted completion:', inserted_completion print 'Inserted compl_rus:', inserted_compl_rus print 'Deleted compl_rus:', deleted_compl_rus ans = raw_input('Commit changes? (y/n)') db.close() I has manually deleted records from table and than run script two times. See https://dpaste.de/MwMa . I think, that rollback in my code doesn't work. Why?

Read the article
How can I Display an extract from MySQL database entry?

- by ThatMacLad

I'm after creating a webpage that includes a blog section and it currently displays the whole post on the homepage. I'd like to set it so that it only displays a certain part of the entry i.e 50 words. I'd then like to be able to set it so that I have a read more button below the post that links to the post id. I currently use post.php?=# (# = whatever the post id is). Here is the homepage: <!DOCTYPE html> <html> <head> <meta charset="UTF-8"> <title>Blog Name</title> <link rel="stylesheet" href="css/style.css" type="text/css" /> <body> <div id="upper-bar"> <div id="bar-content"> <a href="#">Home</a> <a href="#">Archives</a> <a href="#">Contact</a> <a href="#">About</a> <a href="#"><img src="images/twitter.png" id="tweet"></a><a href="#"><img src="images/feed.png" id="feed"></a> </div> </div> <div id="clear"> </div> <div class="main"> <h1>Blog Name</h1> <div class="post-col"> <?php mysql_connect ('localhost', 'root', 'root') ; mysql_select_db ('tmlblog'); $sql = "SELECT * FROM php_blog ORDER BY timestamp DESC LIMIT 5"; $result = mysql_query($sql) or print ("Can't select entries from table php_blog.<br />" . $sql . "<br />" . mysql_error()); while($row = mysql_fetch_array($result)) { $date = date("l F d Y", $row['timestamp']); $title = stripslashes($row['title']); $entry = stripslashes($row['entry']); $id = $row['id']; ?> <div id='post-info'><?php echo "<p id='title'><strong><a href=\"post.php?id=". $id . "\">" . $title . "</a></strong></p>"; ?><br /></div> <div id="post"> <?php echo $entry; ?>  </p> </div> </p> </div> <?php } ?> </div> </div> </body> </html>

Read the article
PHP vs Phpmyadmin

- by user330306

Hi there, I've got this code which i execute on phpmyadmin which works 100% Create Temporary Table Searches ( id int, dt datetime); Create Temporary Table Searches1 ( id int, dt datetime, count int); insert into Searches(id, dt) select a.id, now() from tblSavedSearches a; insert into Searches1(id, dt, count) select b.savedSearchesId, (select c.dt from tblSavedSearchesDetails c where b.savedSearchesId = c.savedSearchesId order by c.dt desc limit 1) as 'dt', count(b.savedSearchesId) as 'cnt' from tblSavedSearchesDetails b group by b.savedSearchesId; insert into tblSavedSearchResults(savedSearchId,DtSearched,isEnabled) select id,now(),0 from Searches where not id in (select savedSearchId from tblSavedSearchResults); update tblSavedSearchResults inner join Searches1 on tblSavedSearchResults.savedSearchId = Searches1.id Set tblSavedSearchResults.DtSearched = Searches1.dt, tblSavedSearchResults.isEnabled = 1; However when i put the same code in php as below it generates an error $dba = DbConnect::CreateDbaInstance(); $query = ""; $query.="Create Temporary Table Searches ( id int, dt datetime); "; $query.="Create Temporary Table Searches1 ( id int, dt datetime, count int); "; $query.="insert into Searches(id, dt) select a.id, now() from tblSavedSearches a; "; $query.="insert into Searches1(id, dt, count) "; $query.="select "; $query.=" b.savedSearchesId, "; $query.=" (select c.dt from tblSavedSearchesDetails c where b.savedSearchesId = c.savedSearchesId order by c.dt desc limit 1) as 'dt', "; $query.=" count(b.savedSearchesId) as 'cnt' "; $query.="from tblSavedSearchesDetails b "; $query.="group by b.savedSearchesId; "; $query.="insert into tblSavedSearchResults(savedSearchId,DtSearched,isEnabled) "; $query.="select id,now(),0 from Searches where not id in (select savedSearchId from tblSavedSearchResults); "; $query.="update tblSavedSearchResults "; $query.="inner join Searches1 on tblSavedSearchResults.savedSearchId = Searches1.id "; $query.="Set tblSavedSearchResults.DtSearched = Searches1.dt, tblSavedSearchResults.isEnabled = 1; "; $dba->DbQuery($query) or die(mysql_error()); I get the following error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Create Temporary Table Searches1 ( id int, dt datetime, count int) insert into S' at line 1 Please if someone could assist me with this ... Thanks

Read the article
Sql query Error: Operand should contain 1 column(s) in rails

- by dombesz

Hi everyone, SELECT * FROM jobs WHERE (SELECT DISTINCT jobs.* FROM jobs, job_requests WHERE (jobs.user_id = 1) OR (job_requests.user_id = 1 AND job_requests.job_id = jobs.id)) This sql gives me Mysql::Error: Operand should contain 1 column(s). If i execute the select from the where clause it works SELECT DISTINCT jobs.* FROM jobs, job_requests WHERE (jobs.user_id = 1) OR (job_requests.user_id = 1 AND job_requests.job_id = jobs.id) Could somebody explain me why? This query is generated by rails activerecord so the main select is needed.

Read the article
webscraper grabbing images, but not entering info into database

- by Jason

Hello, again. I'm having more issues with my script entering info into my database. The script below grabs a page, strips down the necessary info, then downloads the related image file. After that, it is supposed to enter the information gleaned from the URL into the database. For some reason, the script seems to iterate through the URLs, as I get downloaded images for each URL, but each URL's product is not entered into the database. The script will insert the first product's categories and product info, and then it just stops, and continues to download images. Any suggestions? <?php define('IN_PHPBB', true); $phpbb_root_path = (defined('PHPBB_ROOT_PATH')) ? PHPBB_ROOT_PATH : './'; $phpEx = substr(strrchr(__FILE__, '.'), 1); include($phpbb_root_path . 'common.' . $phpEx); include($phpbb_root_path . 'includes/simple_html_dom.' . $phpEx); // Start session management $user->session_begin(); $auth->acl($user->data); $user->setup(); set_time_limit(259200); function save($in, $out) { $ch = curl_init ($in); curl_setopt($ch, CURLOPT_HEADER, 0); curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1); curl_setopt($ch, CURLOPT_BINARYTRANSFER,1); $rawdata=curl_exec($ch); curl_close ($ch); if(file_exists($out)) { unlink($out); } $fp = fopen($out,'x'); fwrite($fp, $rawdata); fclose($fp); } function scrape($i) { $url = 'http:/xxxxxxxx/index.php?main_page=product_info&products_id='.$i.'&zenid=e4b7dde8de02e1df005d4549e2e3e529'; echo "$url -- "; $exists = file_get_contents($url); if ($exists != false) { $html = file_get_html($url); foreach($html->find('body') as $html) { $test = $html->find('#productName', 0); if ($test) { $item['title'] = trim($html->find('#productName', 0)->plaintext); $item['price'] = trim($html->find('#productPrices', 0)->plaintext); $item['cat'] = $html->find('#navBreadCrumb', 0)->plaintext; list($home, $item['cat'], $item['subcat'], $title) = explode("::", $item['cat']); $item['cat'] = str_replace(" ", "", $item['cat']); $item['subcat'] = str_replace("\n", "", str_replace(" ", "", $item['subcat'])); $item['desc'] = trim($html->find('#productDescription', 0)->plaintext); $item['model'] = $html->find('ul#productDetailsList', 0)->find('li', 0)->plaintext; $item['model'] = explode(":", $item['model']); $item['model'] = trim($item['model'][1]); $item['manufacturer'] = $html->find('ul#productDetailsList', 0)->find('li', 1)->plaintext; $item['manufacturer'] = explode(":", $item['manufacturer']); $item['manufacturer'] = trim($item['manufacturer'][1]); foreach($html->find('img') as $img) { if($img->alt == $item['title']) { $item['img_sm'] = $img->src; } } $ret[] = $item; } } $html->clear(); unset($html); unset($item); return $ret; } else { echo "Could not find page<br />"; } unset($exists); } $i = 1; $end = 9999999; while($i < $end) { $ret = scrape($i); if(isset($ret)) { foreach($ret as $v) { $item['title'] = $v['title']; $item['price'] = $v['price']; $item['desc'] = $v['desc']; $item['model'] = $v['model']; $item['manufacturer'] = $v['manufacturer']; $item['image'] = $v['image']; $item['cat'] = $v['cat']; $item['subcat'] = $v['subcat']; $item['img_sm'] = $v['img_sm']; } unset($ret); unset($v); $sm_img_src = "http://xxxxxx/".$item['img_sm']; $ext = strrchr($item['img_sm'], '.'); $filename = $item['model'] . $ext; $lg_img_src = "http://xxxxx/images/STC/".$filename; $new_sm = "./rip_images/small/{$filename}"; $new_lg = "./rip_images/large/{$filename}"; $item['image'] = $filename; save($lg_img_src,$new_lg); save($sm_img_src,$new_sm); //see if parent cat exists $sql = 'SELECT cat_id FROM ' . SHOP_CAT_TABLE . ' WHERE cat_name = "'.$db->sql_escape($item['cat']).'"'; $result = $db->sql_query($sql); $parent = $db->sql_fetchrow($result); $db->sql_freeresult($result); // if not exists if($parent['cat_id'] == '') { //add the parent cat to the db $sql_ary = array( 'cat_name' => $item['cat'], 'cat_parent' => 0 ); $sql = 'INSERT INTO '.SHOP_CAT_TABLE.' '.$db->sql_build_array('INSERT', $sql_ary); $db->sql_query($sql); $cat_id = $db->sql_nextid(); //see if subcat exists $sql = 'SELECT cat_id FROM ' . SHOP_CAT_TABLE . ' WHERE cat_name = "'.$db->sql_escape($item['subcat']).'"'; $result = $db->sql_query($sql); $row = $db->sql_fetchrow($result); $db->sql_freeresult($result); // if not exists if($row['cat_id'] == '') { //add subcat to db $sql_ary = array( 'cat_name' => $db->sql_escape($item['subcat']), 'cat_parent' => $cat_id ); $sql = 'INSERT INTO '.SHOP_CAT_TABLE.' '.$db->sql_build_array('INSERT', $sql_ary); $db->sql_query($sql); $item_cat = $db->sql_nextid(); } else //if exists { $item_cat = $row['cat_id']; } } else //if parent cat exists { //see if subcat exists $sql = 'SELECT cat_id FROM ' . SHOP_CAT_TABLE . ' WHERE cat_name = "'.$db->sql_escape($item['subcat']).'"'; $result = $db->sql_query($sql); $row = $db->sql_fetchrow($result); $db->sql_freeresult($result); // if not exists if($row['cat_id'] == '') { //add the subcat to the db $sql_ary = array( 'cat_name' => $db->sql_escape($item['subcat']), 'cat_parent' => $parent['cat_id'] ); $sql = 'INSERT INTO '.SHOP_CAT_TABLE.' '.$db->sql_build_array('INSERT', $sql_ary); $db->sql_query($sql); $item_cat = $db->sql_nextid(); } else //if exists { $item_cat = $row['cat_id']; } } $sql_ary = array( 'item_title' => $db->sql_escape($item['title']), 'item_price' => $db->sql_escape($item['price']), 'item_desc' => $db->sql_escape($item['desc']), 'item_model' => $db->sql_escape($item['model']), 'item_manufacturer' => $db->sql_escape($item['manufacturer']), 'item_image' => $db->sql_escape($item['image']), 'item_cat' => $db->sql_escape($item_cat) ); $sql = 'INSERT INTO ' . SHOP_ITEM_TABLE . ' ' . $db->sql_build_array('INSERT', $sql_ary); $db->sql_query($sql); garbage_collection(); echo 'Done<br />'; } $i++; unset($item); } ?>

Read the article
TIME REDUCE(OPTIMISE QUERY)

- by user2527657

select a.userid,(select firstName from user where userid=NOTUSED.userid) as z, (select max(login_time) from userLoginTime AS b where userid = a.user_id GROUP BY b.user_id ORDER BY b.user_id) as y From(SELECT DISTINCT a.user_id FROM user AS a LEFT OUTER JOIN (SELECT (userid) FROM userlogintime where serialid=15400012)AS b ON user.user_id = b.user_id where a.Serialid=15400012 AND b.userid IS NULL) NOTUSED, Relation r, user a where r.childuserid = NOTUSED.userid and guarduserid = a.userid

Read the article
In defense of SELECT * in production code, in some limited cases?

- by Alexander Kuznetsov

It is well known that SELECT * is not acceptable in production code, with the exception of this pattern: IF EXISTS( SELECT * We all know that whenever we see code code like this: Listing 1. "Bad" SQL SELECT Column1 , Column2 FROM ( SELECT c. * , ROW_NUMBER () OVER ( PARTITION BY Column1 ORDER BY Column2 ) AS rn FROM data.SomeTable AS c ) AS c WHERE rn < 5 we are supposed to automatically replace * with an explicit list of columns, as follows: Listing 2. "Good" SQL SELECT Column1 , Column2 FROM...(read more)

Read the article
webserver horrible slow, sometimes incredible fast

- by dhanke

i am running a small community ( 6000+ Members ) on a non-virtual 64-bit ubuntu 11.04 system. I am not a Linux-pro, not even advanced, i just tried to setup a webserver, which does nothing special actually. Delivering some dynamic PHP and RoR websites is its task. So it might be that my configuration files do look horrible bad. Also, i might use the wrong vocabulary, so in doubt, please ask. Having a current all-time record of 520 registered users (board-accounts, no system-users) online at same time, average server-load is about 2.0 - 5.0. Meantime (~250 users) average server load value is at about 0.4 - 0.8, sometimes, on some expensive searches a bit higher. everything fine. From time to time however, the load increases up to 120 (120.0, not 12.0 ;) ). In this time, its hard to even connect via SSH, but when i reach the server, and use top/htop/iotop to see whats happening, i cannot identify any process causing high CPU load. iotop tells me about a current reading/writing speed of about approx. 70kb/s, which is quite equal to power-off i think. Memory-Usage is max. at ~ 12GB of 16GB, so swap remains empty. now the odd (at least for me:) waiting some minutes ( since i always get a bit into a panic when this happens, it feels like 5 minutes, but i suppose its more like 20-30 minutes) and the server is back to normal. everything continues as normal. another odd fact: when i run hdparm -tT /dev/sda, i get answer like: /dev/sda: Timing cached reads: 7180 MB in 2.00 seconds = 3591.13 MB/sec Timing buffered disk reads: 348 MB in 3.02 seconds = 115.41 MB/sec when i run the same command while the server is "frozen", the answer is like /dev/sda: <- takes about 5 minutes until this line appears Timing cached reads: 7180 MB in 2.00 seconds = 3591.13 MB/sec <- 5 more minutes Timing buffered disk reads: 348 MB in 3.02 seconds = 115.41 MB/sec <- another 5 minutes so the values are the same, but the quoted time is completely wrong. using time command as prefix also tells me that ~ 15 minutes were used. I searched in dmesg, /var/log/[messages|syslog] - nothing found. /var/log/errors however tells me that: Jul 4 20:28:30 localhost kernel: [19080.671415] INFO: task php5-fpm:27728 blocked for more than 120 seconds. Jul 4 20:28:30 localhost kernel: [19080.671419] "echo 0 /proc/sys/kernel/hung_task_timeout_secs" disables this message. multiple times. now that message does tell me that php5-fpm task was blocked or did block ? - but not if that is the cause or just one of the results of that "freeze". Anyone? to cut the long story short, i dont know where even to start analyzing. So if you can give me any advice by looking at following specs and configs, or ask me to provide more information, i`d be glad. Specs: 6 Core AMD Phenom(tm) II X6 1055T Processor * 16 Gigabyte Ram 2x 1.5 TB Seagate ST1500DL003-9VT16L via SATA 3 via SoftwareRaid (i suppose) Services: (due to service --status-all, those with [ + ]) nginx Webserver 1.0.14 mySQL 5.1.63 Server Ruby on Rails 2.3.11 ( passenger-nginx-module ) php5-fpm 5.3.6-13ubuntu3.7 SSH ido2db Further services: default crontab + nightly backup. syslog-ng Website consists of 2 subdomains, forum. and www. where forum is a phpBB3.x PHP-Board, and www a Ruby on Rails 2.3.11 application (portal). Mini-Note: sometimes i notice that the forum is pretty slow, in contrast to the always-fast (except for this "freeze") portal. Both share the same Database, but the portal is using it read-only. The Webserver is nginx, using phusion passenger module to communicate with the ruby-application. Also, for the forum it communicates with php5-fpm via socket: relevant nginx configuration parts ( with comments/questions starting by ; ) ; in case of freeze due to too high Filesystem activity, maybe adding a limit? #worker_rlimit_nofile 50000; user www-data; ; 6 cores, so i read 6 fits. maybe already wrong? worker_processes 6; pid /var/run/nginx.pid; events { worker_connections 1024; } http { passenger_root /var/lib/gems/1.8/gems/passenger-3.0.11; passenger_ruby /usr/bin/ruby1.8; ; the forum once featured a chat, which was working w/o websockets. ; so it was a hell of pull requests (deactivated now, freeze still happening) keepalive_timeout 65; keepalive_requests 50; gzip on; server { listen 80; server_name www.domain.tld; root /var/www/domain/rails/public; passenger_enabled on; } server { listen 80; server_name forum.domain.tld; location / { root /var/www/domain/forum; index index.php; } ; satic stuff to be handled by nginx location ~* ^/style/.+.(jpg|jpeg|gif|css|png|js|ico|xml)$ { access_log off; expires 30d; root /var/www/domain/forum/; } ; now the php magic, note the "backend"-fcgi_pass location ~ .php$ { fastcgi_split_path_info ^(.+\.php)(.*)$; fastcgi_pass backend; fastcgi_index index.php; fastcgi_param SCRIPT_FILENAME /var/www/domain/forum$fastcgi_script_name; include fastcgi_params; fastcgi_param QUERY_STRING $query_string; fastcgi_param REQUEST_METHOD $request_method; fastcgi_param CONTENT_TYPE $content_type; fastcgi_param CONTENT_LENGTH $content_length; fastcgi_intercept_errors on; fastcgi_ignore_client_abort off; fastcgi_connect_timeout 60; fastcgi_send_timeout 180; fastcgi_read_timeout 180; fastcgi_buffer_size 128k; fastcgi_buffers 256 16k; fastcgi_busy_buffers_size 256k; fastcgi_temp_file_write_size 256k; fastcgi_max_temp_file_size 0; } location ~ /\.ht { deny all; } } ;the php5-fpm socket. i read that /dev/shm/ whould be the fastes place for this. bad idea in general? upstream backend { server unix:/dev/shm/phpfpm; } ... } php5-fpm settings (i changed this values due to php5-fpm error log messages higher and higher.. (freeze-problem was there before as well)* listen = /dev/shm/phpfpm user = www-data group = www-data pm = dynamic ; holy, 4000! well, shinking this value to earth-level gave me ; 100s of 502 bad gateway commands. this values were quite stable. ; since there are only max 520 users online i dont get it, why i would need ; as many children as configured here. due to keep-alive maybe? ; asking questions is easier for me since restarting server will make ; my community-members angry ;) pm.max_children = 4000 pm.start_servers = 100 pm.min_spare_servers = 50 pm.max_spare_servers = 150 pm.max_requests = 10 pm.status_path = /status ping.path = /ping ping.response = pong slowlog = log/$pool.log.slow ;should i use rlimit? ;rlimit_files = 1024 chdir = / mysql/my.cnf [client] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] user = mysql socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp skip-external-locking bind-address = 127.0.0.1 key_buffer = 16M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 myisam-recover = BACKUP ; high number, but less gives some phpBB errors. max_connections = 450 table_cache = 512 ; i read twice the cpu cores, bad? thread_concurrency = 12 join_buffer_size = 2084K concurrent_insert = 3 query_cache_limit = 64M query_cache_size = 512M query_cache_type = 1 log_error = /var/log/mysql/error.log log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 2 expire_logs_days = 10 max_binlog_size = 100M low_priority_updates=1 [mysqldump] quick quote-names max_allowed_packet = 16M [isamchk] key_buffer = 16M !includedir /etc/mysql/conf.d/ I used smartctl already, hdds seem to be fine. /proc/mdstatus quotes: Personalities : [raid1] [linear] [multipath] [raid0] [raid6] [raid5] [raid4] [raid10] md3 : active raid1 sda3[1] 1459264192 blocks [2/1] [_U] md1 : active raid1 sda1[0] 3911680 blocks [2/1] [U_] unused devices: ulimit -a core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited scheduling priority (-e) 0 file size (blocks, -f) unlimited pending signals (-i) 127727 max locked memory (kbytes, -l) 64 max memory size (kbytes, -m) unlimited open files (-n) 1024 pipe size (512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 real-time priority (-r) 0 stack size (kbytes, -s) 8192 cpu time (seconds, -t) unlimited max user processes (-u) 127727 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited I quote some questions in my configuration files, these are not (intentional) directly problem-related, but would be nice for me to know wether they are indeed questionable or done right. One additional Fact: my MYSQL-database is at 12GB size. i dont know if that does matter, but mytop sometimes shows me 4-5 seconds long insert queries, some are 20-30 seconds long. Its just a feeling that i am unable to prove (because i dont know how), but when i disable the database, the freeze seems not to happen. Example: i created a dummy rails application to see the development log. the app made some sql-queries, reads and inserts. the log quite often was like: DbTest Load (0.3ms) SELECT * FROM `db_test` WHERE (`db_test`.`id` = 31722) LIMIT 1 SQL (0.1ms) BEGIN DbTest Update (0.3ms) UPDATE `db_test` SET `updated_at` = '2012-07-04 23:32:34' WHERE `id` = 31722 - now the log stands still for 5-60 seconds. SQL (49.1ms) COMMIT - SQL-Update time in the log does not include freeze time Rendering test/index Completed in 96ms (View: 16, DB: 59) | 200 OK [http://localhost:9000/test] Bad part is: this mini-freeze here only happens from time to time as well. note: meanwhile i cannot even upload files via scp. I currently feel like running form bad to worse and back by googling for my server-problem due to immense lack of knowledge regarding server configurations. It still makes me wonder, why those problems even appear, since 250 users a time is not such a high amount, right? So my questions: whats wrong and how to fix? ;) or: what information can i provide to make the situation more clear? can you point at some critical bad configuration-line which i should consider to catch up in the documentation? are there any tools i can run to see some possible bottlenecks? any further advice? (next to: "pay someone who knows what he does" - its a private project, server costs enough already. :)) Thanks for your time and help. Best Regards, Daniel P.S.: i renamed the configfiles to domain.tld since i dont want to have any % more load to the server until its fixed. might be a exaggeratedly thought.. P.P.S: if i asked a complete duplicate question, sorry. my search results seemed to be quite specific in their own way.

Read the article
sql developer cannot establish connection to oracle db with listener running

- by lostinthebits

I am working from home and connected to my work's vpn. I have tried to connect to the work db with sql developer (the latest version and the previous version) on the following environments: mac os x 10.8.5 (with sql developer launched and installed directly on the iMac. sql developer launched and installed directly on a vm on same computer (guest Ubuntu 12.04 LTS) sql developer launched and installed directly on a vm on same computer (guest Windows 7.0 Professional) I get Status Failure Test Failed : IO Error - The Network Adapter could not establish the connection. I have read dba forums and googled and the most common suggestion is that the oracle listener is not up and running. I can conclusively say this is not the case because I have the option of using remote desktop and accessing the oracle db in question on my work computer. If the listener was down, according to my DBA, no one would be able to connect. My sysadmin and dba are stumped so I assume it is something unique to my home system. The reason I do not want to continue with the remote desktop workaround is because remote desktop has an annoying (infuriating often) lag.

Read the article
MS SQL Server 2005 Express rebuild master DB problem

- by PaN1C_Showt1Me

Hi ! There has been a power loss on our server and i cannot start the SQL service because the master DB is corrupted (as the log states). I found many articles recommending running the setup.exe with optional parameters: This is what I did: I've downloaded SQLEXPR32.EXE from MS page and ran it The first problem was, that it extracted all the setup files and started the default installation procedure. (which was unuseful for me as I need those params). If I canceled it, all the extracted files disappeared. That's why I decided to copy the extracted files somewhere and than cancel the default installation. Now I'm trying to run the setup.exe from the extraction: setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=xxxxx it asks me if I want to rewrite the system db, which is what I need, but then while installing I get this error: *An installation package for the product Microsoft SQL Server 2005 Express Edition cannot be found. Try the installation again using a valid copy of the installation package 'SqlRun_SQL.msi'* Then it tries to install something and it states: cannot install because the same instance name already exists. But I don't want to install a new instance .. Any idea how to solve this, please? Thank you in advance !

Read the article
Simple end-to-end load and bottleneck monitoring for DB-based web sites

- by T.J. Crowder

What tools do you use / would you recommend for monitoring a Linux-based, DB-based website's servers for bottlenecks and load? The obvious goal being to know when growth has gotten to the point where it's necessary to scale up (or out) one or more of the bits and pieces because the current system won't be managing the load if an observed trend continues. I'm looking for general recommendations based on standard Linux load metrics, disk I/O metrics, network I/O metrics, etc., but if specifics are helpful: It'll be Tomcat6 using APR (possibly with a Varnish or similar caching and balancing front-end), MySQL, and either Ubuntu 8.04 LTS or 10.04 LTS depending on timing. I know about top, vmstat, iostat, bwmon and the like that collect and parse info from the /proc file system (et. al.); and obviously MySQL provides a lot of queriable performance information. I could use those directly, probably automating periodic monitoring logs with scripts and such. But I have a suspicion that I'd be reinventing a wheel... For example, Hyperic HQ seems to be along the lines of what I'm looking for. Others? Meta: I tend to think of "recommendation" questions as needing to be CW because there's no one right answer, but I see a lot of these here that aren't CWs, so I haven't marked it as one. I'll happily do so if enough people think I should.

Read the article
Which database to use and system/db administration by layman [closed]

- by blah

So my friend and I got briliant ;) idea for a business. Since it is not predictable whether it will work out or not, we decided to keep cost as low as possible to start with, in particular not to hire anyone. If it will work out as expected it will generate enough profit to hire professionals in few months. But for the first few months we'll be doing everything by ourselfs. He's a business/finance major, and I'm a software developer, so obviously I have to take care of IT :) It will be a webapp, written in python/django. My questions regarding this project: 1) What database should I choose? I'm experienced with oracle, and have been working with SQL Server for a while, but both of them are too expensive(at least now). It's a developer experience, I've never done any dba stuff. I'm looking for something free(as in beer). Looks like MySql or PostgreSQL are most popular in this sector. I would appreciate any comments on which db to choose. I'm open to any suggestions(it doesn't have to be MySql or Postgre). Here's what I know about data: It will be almost dates and numbers, a little bit of text. Searched mainly by dates. Data will almost never be updated, mostly inserted and browsed. From 30k to 300k new records/month. 2) Servers. My idea is to rent two dedicated servers. During normal operation one would be a web server(debian/apache), other would be a db server(debian/?). My recovery plan is to install everything on both, and in case of trouble with one of machines just run everything on the other one. Does it even makes sense? Any other tips appreciated. Thanks.

Read the article
Why is my mysql database timestamp changing by itself?

- by Scarface

Hey guys quick question, I have an entry that I put in my database, and as I echo the value, the value in the database stays the same while the data echoed keeps increasing, which is messing up my function. If anyone knows whats going down, would appreciate any suggestions. <?php include("../includes/connection.php"); $query="SELECT * FROM points LEFT JOIN users ON points.user_id=users.id WHERE points.topic_id='82' AND users.username='gman'"; $check=mysql_query($query); while ($row=mysql_fetch_assoc($check)){ $points_id=$row['points_id']; echo $timestamp=$row['timestamp']; } ?>

Read the article
Best practices for querying an entire row in a database table? (MySQL / CodeIgniter)

- by Walker

Sorry for the novice question! I have a table called cities in which I have fields called id, name, xpos, ypos. I'm trying to use the data from each row to set a div's position and name. What I'm wondering is what's the best practice for dynamically querying an unknown amount of rows (I don't know how many cities there might be, I want to pull the information from all of them) and then passing the variables from the model into the view and then setting attributes with it? Right now I've 'hacked' a solution where I run a different function each time which pulls a value using a query ('SELECT id FROM cities;'), then I store that in a global array variable and pass it into view. I do this for each var so I have arrays called: city_idVar, city_nameVar, city_xposVar, city_yposVar then I know that the city_nameVar[0] matches up with city_xposVar[0] etc. Is there a better way?

Read the article
Is there any way to modify a column before it is ordered in MySQL?

- by George Edison

I have a table with a field value which is a varchar(255). The contents of the field can be quite varied: $1.20 $2994 $56 + tax (This one can be ignored or truncated to $56 if necessary) I have a query constructed: SELECT value FROM unnamed_table ORDER BY value However, this of course uses ASCII string comparison to order the results and does not use any numerical type of comparison. Is there a way to truly order by value without changing the field type to DECIMAL or something else? In other words, can the value field be modified ('$' removed, value converted to decimal) on the fly before the results are sorted?

Read the article
Safe to KILL a mysql process REPLACEing records in a large myisam table?

- by threecheeseopera

I have a REPLACE query running for a few days now on a few MyISAM tables, the largest having 20+million records. I need it to stop. It is, basically: REPLACE INTO really_large_table (a,b,c,d) SELECT e,f,g,h FROM big_table INNER JOIN huge_table ON big_table.x LIKE CONCAT('%', huge_table.y, '%'); I need to KILL it, and I am worried that I may corrupt really_large_table. Because the sub-query itself takes a significant amount of time, the REPLACEing probably occurs (relatively) infrequently; if this is true, does this make it less likely for the data to become corrupted? For the curious, here is the SO question asked about the query I am trying to kill.

Read the article
Optimizing MySQL statement with lot of count(row) an sum(row+row2)...

- by Zombies

I need to use InnoDB storage engine on a table with about 1mil or so records in it at any given time. It has records being inserted to it at a very fast rate, which are then dropped within a few days, maybe a week. The ping table has about a million rows, whereas the website table only about 10,000. My statement is this: select url from website ws, ping pi where ws.idproxy = pi.idproxy and pi.entrytime > curdate() - 3 and contentping+tcpping is not null group by url having sum(contentping+tcpping)/(count(*)-count(errortype)) < 500 and count(*) > 3 and count(errortype)/count(*) < .15 order by sum(contentping+tcpping)/(count(*)-count(errortype)) asc; I added an index on entrytime, yet no dice. Can anyone throw me a bone as to what I should consider to look into for basic optimization of this query. The result set is only like 200 rows, so I'm not getting killed there.

Read the article

< Previous Page | 270 271 272 273 274 275 276 277 278 279 280 281 | Next Page >