mysql

Random problem connecting to MySQL

Environment: RHEL 5 servers, MySQL 5.1.43, PHP 5.1.6 (using MySQLi). Currently only available within our internal VPN network. Servers ServerA: Webserver ServerB/C/D: Database server (1 master 2 slaves) The error (on ServerA) [Tue May 25 11:12:17 2010] [error] [client CLIENTIP] PHP Warning: mysqli::real_connect() [function.mysqli-real...

how to count specific row where variable can be more then one

how i can write the query ex;- select count(id) where animal = rat , elephant ,cat, mouse how i can do this in mysql. ex means count the row where animal = rat ,elephant, cat ,mouse ...

How do I select a bunch of random rows from a table?

I have a database of filenames that relate many-to-one to a set of subjects. How can I randomly select one filename from the several in the database that relate to a given subject? I'm working with Zend Framework, so if there is a ZF function that would help please mention it. ...

Is this MySql Query Statement correct?

Hi I would like to know whether this MySql statement will be executed correctly, "SELECT sum(price) FROM products WHERE productid IN (SELECT productid FROM shoppingcart WHERE sessionid=".$this->$sessionid.")" And if not please give me pointers as to where I am wrong. Thanks ...

how to update a specific field(column) of a row by that row's autoincrement value with some prefix in mysql?

i have below table it_id item_id item_name 1 ite_1 shirt 2 ite_10 pant 3 ite_11 socks 4 ite_12 shoes 5 ite_13 belt now i need to change item_id with with ite_(value of it_id of that row) means if it_id=x then item_id should be ite_x and so ...

Why is this loop over mysql resultset slow? (1.4ms per cycle)

The $res contains around 488k rows the whole loop takes 61s! that's over 1.25ms per cycle! What is taking all that time? while($row = $res->fetch_assoc()) { $clist[$row['upload_id']][$row['dialcode_id']][$row['carrier_id']]['std'] = $row['cost_std']; $clist[$row['upload_id']][$row['dialcode_id']][$row['carrier_id']]['ecn'] = $...

treat mysql longtext as integer in query

I have a set field in my db that is longtext. I have used prices in this field and cannot change the field type to integer. In my query however I need to sort by these fields and assume I should treat them as an integer. is there another way to query these results to sort by price as an integer and not longtext without having to change t...

is there any way to catch ROLLUP (mysql) figures as values in php?

Hi, I am using group by ID WITH ROLLUP in my mysql query. The thing is I want to store these totals in a variable in php. Is there any way to do that? Thanks. ...

Beginner Ruby / MSQL Error

http://localhost:3000/genre I'm using Instant Rails, I'm introducing scaffolding for the first time ever. So everything looks like its working fine, I did my own RoR page etc, today i come in today and when I go to localhost:3000 I get the following error message: Oops! Google Chrome could not connect to localhost:3000 Any suggestion...

MySQL Paritioning performance

Measured performance on key partitioned tables and normal tables separately. But we couldn't find any performance improvement with partitioning. Queries are pruned. Using MySQL 5.1.47 on RHEL 4. Table details: UserUsage - Will have entries for user mobile number and data usage for each date. Mobile number and Date as PRI KEY. UserPro...

Anchoring the Action URL of a Form

Hello, I am using a function that leads users to a file called "comments2.php": <form action="http://www...com/.../comments/comments2.php" method="post"> On comments2.php, data passed over from the form is inserted into MySQL: $query = sprintf("INSERT INTO comment VALUES (NULL, %d, %d, '%s', NULL)", $uid, $subid, $comment); mysql_...

'Illegal mix of collations' error (Ruby on Rails)

I'm trying to write a screen-scraper for a Rails app, scraping this page. Bit new to Rails, SQL and everything else really. According to the W3C validator, the page I'm scraping is encoded as iso-8859-1. My database is Latin-1. Just to be sure, when I get the value I need, I save it as Latin1: value = Iconv.iconv("latin1", "iso-8859-...

what's wrong with this code?

$child= array(); $i = 0; while($row = mysql_fetch_array($result, MYSQL_ASSOC)) { $child[i] = $row['userId']; $i++; } $i = 0; while($i<=5) { echo $child[i]; $i++; } It is printing same value. ...

Hibernate Communications Link Failure in Hibernate Based Java Servlet application powered by MySQL

Let me describe my question - I have a Java application - Hibernate as the DB interfacing layer over MySQL. I get the communications link failure error in my application. The occurence of this error is a very specific case. I get this error , When I leave mysql server unattended for more than approximately 6 hours (i.e. when there are ...

Creating an SQL query to list all clients, who spent over 1000$

So, I have three tables with the following rows: Customers (Customer_id and Name), Orders (Customer_id, Product_id, Quantity) and Products (Price). How do I write a query which shows all customers who spent more than 1000$? Do I have to join the tables? ...

MySQL SELECT combining 3 SELECTs INTO 1

Consider following tables in MySQL database: entries: creator_id INT entry TEXT is_expired BOOL other: creator_id INT entry TEXT userdata: creator_id INT name VARCHAR etc... In entries and other, there can be multiple entries by 1 creator. userdata table is read only for me (placed in other database). ...

Beginner MYSQL Error - Access Denied

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: N O) I've tried EVERYTHING, I've read through pages of answers and no-one seems to know the right one. When i try to log into my DB i just get the above error, I havent set a password or anything. Can't log into MySQL at all now, don't understand. Thanks f...

Which C# connector supports embedded MySql?

Hi, Do you know a .NET Connector that supports mysqld (embedded mysql)? AFAIK the official MySQL Connector/NET doesn't support it. Thanks ...

Innodb Lock out causing whole database down

I have searched many threads and stackoverflow but I couldn't found any solution. I am trying to insert records into few innodb tables randomly rather one condition matches which cause whole database down. I am getting this error "Lock wait timeout exceeded; try restarting transaction" One the question (#1103248) has been answered here ...

Help with MySQL and CASE WHEN with a range of values

I have an accounts table and a records table where accounts have multiple records. I would like to break down the account totals by "count of records" range. i.e. show the breakdown of Count of Records | Count 0-25 | 100 25 - 50 | 122 50 - 100 | 300 etc. I am using the following query, but I can't get it to group by "grp" which is ...