mysql

Do table locks scale? / Would row locks be more efficient for nested sets?

I'm using nested sets to store hierarchical data in a MyISAM table; the table consists of several hierarchical sets for each user. Each user will be the only one writing to his respective trees, but other users may read from them. Node deletion / Insertion requires that other rows in the same tree have their lft and rgt values updated,...

PHP: How to determine if a database connection is open

Hi, I have the following class method - class Someclass { /* Other properties and methods */ public function close_connection($connection=NULL) { return mysql_close($connection) } } Now, before calling the mysql_close function in the above code, I want to check if the $connection points to a open database con...

How to upload timestamp data from a file into mysql

shell> cat /data/mysql/tm.sql 1276609796 1276606325 mysql> CREATE TABLE `tm_table` ( f TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP); Query OK, 0 rows affected mysql> LOAD DATA INFILE '/data/mysql/tm.sql' INTO TABLE tm_table; Query OK, 2 rows affected Records: 2 Deleted: 0 Skipped: 0 Warnings: 2 mysql> SHOW WARNINGS; +---------+------+...

SELECT INTO Variable in MySQL DECLARE causes syntax error?

I´d like to SELECT a single value into a variable. I´d tried to following: DECLARE myvar INT(4); -- immediately returns some syntax error. SELECT myvalue FROM mytable WHERE anothervalue = 1; -- returns a single integer SELECT myvalue INTO myvar FROM mytable WHERE anothervalue = 1; -- does not work, also tried @myvar...

Weird Locks Problem in MySQL

Hi! Quite often my Server hangs and MySQL shows the following process list: Process 1: Time: 24 User: Worker Status: Locked Info: LOCK TABLES tRatings WRITE, tUsers WRITE Process 2: Time: 24 User: Worker Status: Updating Info: UPDATE tUsers SET fGender = '1' WHERE fID = 232049 Process 3: Time: 24 User: Worker Status: Locked Info: LOC...

random order within mysql order by case

My query selects results correctly using ORDER BY CASE. I would like to randomize the results within EACH case. i.e. ORDER BY CASE WHEN apples (randomize) THEN 1 WHEN pears (randomize) THEN 2, etc. So my results are still ordered by each case but within the results PER case they are random, each time the query is run. ...

How to check the Database Connection leakage in J2EE application ?

Is there anyway to check the Connection leakage in J2EE application ? The application is running on my local machine. It uses MySQL database. User Enters his details into the Database. In my opinion Connection leakage means not closing the Connection object properly. I am creating too many Database connections in my application. I want...

Convert MySql DateTime stamp into JavaScript's Date format

Does anyone know how I can take a mysql datetime stamp, such as YYYY-MM-DD HH:MM:SS and either parse it or convert it to work in JavaScript's Date() function, such as.. Date('YYYY, MM, DD, HH, MM, SS); Thank you! ...

MySql Where-Subquery syntax error

This code worked in Sqlite which i have more experience with. I cant figure out whats wrong here. If it helps SELECT 1 FROM Post WHERE body = 'a'; doesnt give me a syntax error but this does select 1 WHERE NOT EXISTS (SELECT 1 FROM Post WHERE body = 'a' ) ; code: INSERT INTO `Post` ( `name`, ... `date`) select @0, ... @6 WHERE NOT ...

controlling search relevance in MySQL

What is the possible ways to control search engine reverence in MySQL? let us assume we are building a search engine for a small web store where buyers can add there stuff for sell. The table is: CREATE TABLE `products` ( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `title` VARCHAR( 100 ) NOT NULL , `date` DATE NOT NULL , `buyer_level...

Checking if mySql datetime is older then 1 day from php now()

Hi, I have a record returned from MySQL that has a datetime field. What I want to do is take this value and see if it is older then 24 hours, I presume using PHP's time() to get the current time. At the moment if I echo them out I get: 1276954824 this is php's time() 2010-06-19 09:39:23 this is the MySQL datetime I pre...

disperse relational structure of MySQL table

Dear all, I have a table that consists of id (auto_increment) number int (can contain values from 10 to 12) myvalue (varchar) What I want to do is disperse the relational structure of this table for report purpose. I.e , I´d like to have something like: id (auto_increment) number10 (containing myvalue WHERE number=10) number11 (co...

Another Undefined Index (this one's defined though)

Alright, PHP is throwing this error (in the log) and it's quite detrimental to the application. PHP Notice: Undefined index: sessid in methods.php on line 7 Yes, I'm aware of what this error means, what I cannot figure out is why it's obviously defined and saying it's undefined. Here is the relevant code in methods.php $sessid = mys...

How long until MySQL transaction times out

How long can a MySQL transaction last until it times out? I'm asking because I'm planning to code an payment process for my e-commerce project somewhere along the lines of this (PHP/MySQL psuedo-code): START TRANSACTION; SELECT...WHERE id IN (1,2,3) AND available = 1 FOR UPDATE; //lock rows where "available" is true //Do payment proce...

Compare large sets of weighted tag clouds?

I have thousands of large sets of tag cloud data; I can retrieve a weighted tag clouds for each set with a simple select/group statement (for example) SELECT tag, COUNT( * ) AS weight FROM tags WHERE set_id = $set_id GROUP BY tag ORDER BY COUNT( * ) DESC What I'd like to know is this -- what is the best way to compare weighted tag clo...

Querying and making dumps of production databases

I have some large MySQL production tables that I need to dump so that I can run some analyses in Hadoop. What best practices I should know about making dumps and queries of production databases? Do I need to worry about affecting production performance if I'm just making dumps/reads (and no writes)? ...

SQL count returning one value (and not the total).

Hi all. I need to get the nid "alone" in this query. SELECT count(nid) as total,nid FROM bird_countries b group by nid order by total asc` Basically I need this to do something like update table set field=1 where id in (the other query); Right now, I'm getting the total of nid's and the nid, but can't find how to just get the nid ...

How important is it close an mysql connection in a website and why?

Possible Duplicate: close mysql connection important? How important is it close an mysql connection in a website and why? ...

Problem getting text field as string from MySQL with PHP

Hello, I'm having this problem that's driving me nuts. I've got a MySQL database in which there is a table that contains a text field. I am querying the table in PHP and trying to put the content of the text field for each row into a var. I am doing something like this: for ($i=0;$i<$nbrows;$i++){ $id = $data[$i]['ID']; $descript...

How to do this type of incrementation in PHP?

I am using this category script: <?php include("connect.php"); $nav_query = mysql_query("SELECT * FROM `categories` ORDER BY `id`"); $tree = ""; $depth = 1; $top_level_on = 1; $exclude = array(); array_push($exclude, 0); while ($nav_row = mysql_fetch_array($nav_query)) { $goOn = 1; for ($x = 0; $x < count($exclude); $x++) { ...