mysql

Get group key from bridge table

I'm developing an ETL process, and need a bridge table for a one-to-many relationship between a fact table and a dimension table (MySQL database). There is a limited number of combinations (some thousands), so I want to re-use group keys from the bridge table to to limit the size. Any group of dimensions belonging to a fact row will con...

Mysql query to fetch data

i have a table "request" with 4 columns namely: 1.recId :long primary key 2.interactionId:long 3.requestedBy:boolean 4.requestedType:boolean and data is as follows: VALUES (185,455699,0,5), (186,455746,0,1), (187,455746,1,1), (188,455752,0,1), (189,455753,0,1), (190,455753,1,1), (191,455754,1,1) i want a query to fetch all the row...

How to grant "select" on more than one table in MySQL?

i want to do something like grant select on arpg.t_account,arpg.t_char to peeker@'localhost'; but fails, is there a way to do so? ...

Removing out-of-sync data from MySQL host with replication

I have a master-master replication setup and just found out that sometime during the initial testing (2 years ago) two extra rows where inserted in to only one host and now I'd like to remove them so that the DB's are fully in sync. Can I just issue a delete of those rows on the host in question without replication going crazy because of...

Peer review maatkits mk-parallel-dump and mk-parallel-restore usage?

Hiya Im trying to make use of maatkit as a means of dumping a database and then restoring to another database. For dumps: mk-parallel-dump --user abc --password xyz --databases $db --base-dir /tmp/dump For restore: mk-parallel-restore --create-databases --user abc --password xyz --database devdb /tmp/dump My question is, is my logic ...

mySQL & Relational databases: How to handle sharding/splitting on application level?

Hi everybody, I have thought a bit about sharding tables, since partitioning cannot be done with foreign keys in a mySQL table. Maybe there's an option to switch to a different relational database that features both, but I don't see that as an option right now. So, the sharding idea seems like a pretty decent thing. But, what's a good ...

how do i use MySql ENUM type? (and is it right for a news feed)

I'm creating a table called news_feed, this will log all the actions a user performs. Lets say I want to store user1234 deleted article412 at midday I then need a table like so timestamp | user_id | subject_type | subject_id | method . there would be 5 potential methods to log, methods = add/edit/delete/update/report and l...

Need a mysql query to extract data from mm-forms

http://cl.ly/201a11b430bf60ef9e97 is what the rows look like, after I clean them up a bit: SELECT fk_form_joiner_id,form_key,value FROM wp_contactform_submit_data WHERE form_key!='page_post_id' AND form_key!='captcha-802' AND form_key!='page_post_title' AND form_key!='user_ID' What I need is a query where it would transform this into...

Changing character encoding in MySQL, PHP scripts, HTML

So, I have built on this system for quite some time, and it is currently outputting Latin1 (ISO-8859-1) to the web browser, and this is the components: MySQL - all data is stored with the Latin1 character set PHP - All PHP text files are stored on disk with Latin1 encoding HTML - The output has the http-equiv="content-type" content="...

displaying data between date ranges mysql

Hi Guys, I need to work out a period of 6 months (backwards) from a given date. Example date: 07/06/2010 00:00:00 needs to count back 6 months and display: 07/12/2009 00:00:00 I have been scanning through: http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html and non of these functions make sense to me :( Hope this m...

Mysql SELECT and INSERT Error

I am using sort field in order to sort records. My code is this $query = "SELECT max(sort) FROM $this->table LIMIT 1;"; $result = mysql_query($query); $row = mysql_fetch_row($result); $sort = $row[0]+1; $query = "INSERT INTO "$this->table." VALUES ( '', '$ini', '$time', '$ip', '0', '$type', '$sort', '$title', '$image...

Best way to migrate servers without losing any data and with no downtime(?)

This is a methodology question from a freelancer, with a corollary on MySQL.. Is there a way to migrate from an old dedicated server to a new one without losing any data in-between - and with no downtime? In the past, I've had to lose MySQL data between the time when the new server goes up (i.e., all files transferred, system up and read...

MySQL: SUM in WHERE clause

Hi guys, I've got this table CREATE TABLE `subevents` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(150) DEFAULT NULL, `content` text, `class` tinyint(4) NOT NULL DEFAULT '1', PRIMARY KEY (`id`) ) ENGINE=MyISAM Each row can have a different value in the 'class' field. I'd like to select any number of rows, ordered...

Some help needed with a SQL query

Hello, I need some help with a MySQL query. I have two tables, one with offers and one with statuses. An offer can has one or more statuses. What I would like to do is get all the offers and their latest status. For each status there's a table field named 'added' which can be used for sorting. I know this can be easily done with two ...

mysql_close doesn't kill locked sql requests

I use mysqld Ver 5.1.37-2-log for debian-linux-gnu I perform mysql calls from c++ code with functions mysql_query. The problem occurs when mysql_query execute procedure, procedure locked on locked table, so mysql_query hangs. If send kill signal to application then we can see lock until table is locked. Create the following SQL table ...

"Undoing deletes" in webapplication?

Hi everybody, I have seen more and more of the websites that offers a undo option after pressing a delete button. How is the logic done behind the button? Is the item deleted by javascript and "dissapears" from the users screen and a scheduled delete added, that gives the user time to undo it or how does it work? What are the other op...

Is it safe to delete rotated MySQL binary logs?

I have a MySQL server with binary logging active. Once a days logs file is "rotated", i.e. MySQL seems to stop writing to it and creates and new log file. For example, I currently have these files in /var/lib/mysql -rw-rw---- 1 mysql mysql 10485760 Jun 7 09:26 ibdata1 -rw-rw---- 1 mysql mysql 5242880 Jun 7 09:26 ib_logfile0 -rw-rw---...

How can I pull data from other websites and get it stored in my local database

I pull a data from other website by using curl but how to store in my local database. $url = 'http://sample.com/'; $ch = curl_init(); curl_setopt($ch, CURLOPT_URL, $url); curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1); $data = curl_exec($ch); curl_close($ch); echo $data; I just want post some categories and sample of data. ...

When doing a UNION in mysql how can I do a where on the results

Hi I am doing a union over several tables. It's a little long but works! (SELECT user_id,added_date,group_id,'joined',0,0,'' FROM group_members WHERE status = 1) UNION (SELECT user_id,added_date,object_id,'made a comment',0,0,'' FROM comments WHERE object_type = 11 AND status = 1) UNION (SELECT user_id,added_date,group_id,'made the even...

Appropriate data field type for true/false value?

Hi everybody, What's the most appropriate (read least data consuming) data field to store a true/false/1/0 value in a mySQL database? I have previously used a one character long tinyint, but I am not sure if it's the best solution? Thanks! ...