Search Results

Search found 63877 results on 2556 pages for 'mysql error 1452'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 194/2556 | < Previous Page | 190 191 192 193 194 195 196 197 198 199 200 201  | Next Page >

  • Optimizing MySql query to avoid using "Using filesort"

    - by usef_ksa
    I need your help to optimize the query to avoid using "Using filesort".The job of the query is to select all the articles that belongs to specific tag. The query is: "select title from tag,article where tag='Riyad' AND tag.article_id=article.id order by tag.article_id". the tables structure are the following: Tag table CREATE TABLE `tag` ( `tag` VARCHAR( 30 ) NOT NULL , `article_id` INT NOT NULL , INDEX ( `tag` ) ) ENGINE = MYISAM ; Article table CREATE TABLE `article` ( `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY , `title` VARCHAR( 60 ) NOT NULL ) ENGINE = MYISAM Sample data INSERT INTO `article` VALUES (1, 'About Riyad'); INSERT INTO `article` VALUES (2, 'About Newyork'); INSERT INTO `article` VALUES (3, 'About Paris'); INSERT INTO `article` VALUES (4, 'About London'); INSERT INTO `tag` VALUES ('Riyad', 1); INSERT INTO `tag` VALUES ('Saudia', 1); INSERT INTO `tag` VALUES ('Newyork', 2); INSERT INTO `tag` VALUES ('USA', 2); INSERT INTO `tag` VALUES ('Paris', 3); INSERT INTO `tag` VALUES ('France', 3);

    Read the article

  • Mysql Replication

    - by ychian
    My current database design uses MyIsam mainly as the storage engine, I wonder if its possible to split some of the tables into MyIsam and some into Innodb in the same database. Reason of switching some of the tables to Innodb is because i need row-based locking which Innodb offers. I am not too sure whether this would have any effect on replication?

    Read the article

  • UPDATE REGEX MYSQL

    - by Simon
    I have a table of contacts and a table of postcode data. I need to match the first part of the postcode and the join that with the postcode table... and then perform an update... I want to do something like this... UPDATE `contacts` LEFT JOIN `postcodes` ON PREG_GREP("/^[A-Z]{1,2}[0-9][0-9A-Z]{0,1}/", `contacts`.`postcode`) = `postcodes`.`postcode` SET `contacts`.`lat` = `postcode`.`lat`, `contacts`.`lng` = `postcode`.`lng` Is it possible?? Or do I need to use an external script? Many thanks.

    Read the article

  • How to join mysql tables

    - by Ivan
    I've an old table like this: user> id | name | address | comments And now I've to create an "alias" table to allow some users to have an alias name for some reasons. I've created a new table 'user_alias' like this: user_alias> name | user But now I have a problem due my poor SQL level... How to join both tables to generate something like this: 1 | my_name | my_address | my_comments 1 | my_alias | my_address | my_comments 2 | other_name | other_address | other_comments I mean, I want to make a "SELECT..." query that returns in the same format as the "user" table ALL users and ALL alias.. Something like this: SELECT user.* FROM user LEFT JOIN user_alias ON `user`=`id` but it doesn't work for me..

    Read the article

  • Grouping timestamps by interval between timestamps, then calculating from group MySQL

    - by Emile
    To put this question into context, I'm trying to calculate "time in app" based on an event log. Assume the following table: user_id event_time 2 2012-05-09 07:03:38 3 2012-05-09 07:03:42 4 2012-05-09 07:03:43 2 2012-05-09 07:03:44 2 2012-05-09 07:03:45 4 2012-05-09 07:03:52 2 2012-05-09 07:06:30 I'd like to get the difference between the highest and lowest event_time from a set of timestamps that are within 2 minutes of eachother (and grouped by user). If a timestamp is outside of a 2 minute interval from the set, it should be considered a part of another set. Desired output: user_id seconds_interval 2 7 (because 07:03:45 - 07:03:38 is 7 seconds) 3 0 (because 07:03:42) 4 9 (because 07:03:52 - 2012-05-09 07:03:43) 2 0 (because 07:06:30 is outside 2 min interval of 1st user_id=2 set) This is what I've tried, although I can't group on seconds_interval (even if I could, I'm not sure this is the right direction): SELECT (max(tr.event_time)-min(tr.event_time)) as seconds_interval FROM some_table tr INNER JOIN TrackingRaw tr2 ON (tr.event_time BETWEEN tr2.event_time - INTERVAL 2 MINUTE AND tr2.event_time + INTERVAL 2 MINUTE) GROUP BY seconds_interval

    Read the article

  • MySql Query lag time / deadlock?

    - by Click Upvote
    When there are multiple PHP scripts running in parallel, each making an UPDATE query to the same record in the same table repeatedly, is it possible for there to be a 'lag time' before the table is updated with each query? I have basically 5-6 instances of a PHP script running in parallel, having been launched via cron. Each script gets all the records in the items table, and then loops through them and processes them. However, to avoid processing the same item more than once, I store the id of the last item being processed in a seperate table. So this is how my code works: function getCurrentItem() { $sql = "SELECT currentItemId from settings"; $result = $this->db->query($sql); return $result->get('currentItemId'); } function setCurrentItem($id) { $sql = "UPDATE settings SET currentItemId='$id'"; $this->db->query($sql); } $currentItem = $this->getCurrentItem(); $sql = "SELECT * FROM items WHERE status='pending' AND id > $currentItem'"; $result = $this->db->query($sql); $items = $result->getAll(); foreach ($items as $i) { //Check if $i has been processed by a different instance of the script, and if so, //leave it untouched. if ($this->getCurrentItem() > $i->id) continue; $this->setCurrentItem($i->id); // Process the item here } But despite of all the precautions, most items are being processed more than once. Which makes me think that there is some lag time between the update queries being run by the PHP script, and when the database actually updates the record. Is it true? And if so, what other mechanism should I use to ensure that the PHP scripts always get only the latest currentItemId even when there are multiple scripts running in parrallel? Would using a text file instead of the db help?

    Read the article

  • MySQL - How To Avoid Repair With Keycache?

    - by dvancouver
    I have had some experience with optimizing the my.cnf file but my database has around 4 million records (MyISAM). I am trying to restore from a mysqldump but every time I do I eventually get the dreaded "Repair With Keycache", that may take days. Is there anyway to get past this and let it roll as "Repair By Sorting"? I have 2GB RAM, Dual Cores, lots of extra hard-drive space. Snip out of my.cnf: set-variable = max_connections=650 set-variable = key_buffer=256M set-variable = myisam_sort_buffer_size=64M set-variable = join_buffer=1M set-variable = record_buffer=1M set-variable = sort_buffer_size=2M set-variable = read_buffer_size=2M set-variable = query_cache_size=32M set-variable = table_cache=1024 set-variable = thread_cache_size=256 set-variable = wait_timeout=7200 set-variable = connect_timeout=10 set-variable = max_allowed_packet=16M set-variable = max_connect_errors=10 set-variable = thread_concurrency=8

    Read the article

  • MySQL: Request to select the last 10 send/received messages to/by different users

    - by Yako malin
    I want to select the 10 last messages you received OR you sent TO different users. For example the results must be shown like that: 1. John1 - last message received 04/17/10 3:12 2. Thomy - last message sent 04/16/10 1:26 3. Pamela - last message received 04/12/10 3:51 4. Freddy - last message received 03/28/10 9:00 5. Jack - last message sent 03/20/10 4:53 6. Tom - last message received 02/01/10 7:41 ..... Table looks like: CREATE TABLE `messages` ( `id` int(11) NOT NULL AUTO_INCREMENT, `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `sender` int(11) DEFAULT NULL, `receiver` int(11) DEFAULT NULL, `content` text ) I think Facebook (and the iPhone) use this solution. When you go to your mail box, you have the last messages received/sent grouped by Users (friends). So I will take an example. If I have theses messages (THEY ARE ORDERED YET): **Mike** **Tom** **Pam** Mike Mike **John** John Pam **Steve** **Bobby** Steve Steve Bobby Only Message with ** should be returned because they are the LAST messages I sent/received By User. In fact I want the last message of EACH discussion. What is the solution?

    Read the article

  • MySQL Volleyball Standings

    - by Torez
    I have a database table full of game by game results and want to know if I can calculate the following: GP (games played) Wins Loses Points (2 points for each win, 1 point for each lose) Here is my table structure: CREATE TABLE `results` ( `id` int(10) unsigned NOT NULL auto_increment, `home_team_id` int(10) unsigned NOT NULL, `home_score` int(3) unsigned NOT NULL, `visit_team_id` int(10) unsigned NOT NULL, `visit_score` int(3) unsigned NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ; And a few testing results: INSERT INTO `results` VALUES(1, 1, 21, 2, 25); INSERT INTO `results` VALUES(2, 3, 21, 4, 17); INSERT INTO `results` VALUES(3, 1, 25, 3, 9); INSERT INTO `results` VALUES(4, 2, 7, 4, 22); INSERT INTO `results` VALUES(5, 1, 19, 4, 20); INSERT INTO `results` VALUES(6, 2, 24, 3, 26); Here is what a final table would look like: +-------------------+----+------+-------+--------+ | Team Name | GP | Wins | Loses | Points | +-------------------+----+------+-------+--------+ | Spikers | 4 | 4 | 0 | 8 | | Leapers | 4 | 2 | 2 | 6 | | Ground Control | 4 | 1 | 3 | 5 | | Touch Guys | 4 | 0 | 4 | 4 | +-------------------+----+------+-------+--------+

    Read the article

  • Help with MySQL Join Statement

    - by JasonS
    Hi, I just built a website and have realised that I need to have a top 3 highest rated albums.. I haven't built in something that keeps track of the ratings. Ratings are stored separately. Can someone show me how to put these together please. SELECT id, name FROM albums LIMIT 3 SELECT rating FROM ratings WHERE url=CONCAT('albums/show/', album.id) Let me just flesh it out a bit. I need to get back the following: From the albums table. id, name. From the ratings table I need to get back the average rating. ROUND((rating+rating+rating) / total ratings) The ratings. Users can rate everything on my website so I have a generic ratings table. The rating is stored with the url of the page it applies to. Hence, to get album ratings I need to have 'albums/show/{album_id}'. In hind sight I should have had a type and id field but it is a bit late now with a lunch iminient. Any help is much appreciated.

    Read the article

  • MYSQL query to return rows that are NOT in a set

    - by iglurat
    Hi, I have two tables: Contact (id,name) Link (id, contact_id, source_id) I have the following query which works that returns the contacts with the source_id of 8 in the Link table. SELECT name FROM `Contact` LEFT JOIN Link ON Link.contact_id = Contact.id WHERE Link.source_id=8; However I am a little stumped on how to return a list of all the contacts which are NOT associated with source_id of 8. A simple != will not work as contacts without any links are not returned. Thanks.

    Read the article

  • MySQL SET and ENUM types in CakePHP framework

    - by Andrew Bashtannik
    Hi! I need to use SET and ENUM types in my CakePHP 1.3 project. I found some advices, but all them are too old (2004-2006) and full of crazy methods, like modifying cake's core files. Also, CakePHP developers said that SET and ENUM types are not supported. Example: I have SET('alpha','beta') field, and I need to use this data as checkboxes in add & edit actions. Is there any way to add normal support (Form helpers etc.) of SET and ENUM fields?

    Read the article

  • using SET datatype in mysql

    - by Dan Snyder
    Is it possible to set a varaible to a query result such as: DECLARE result INT; SET result = (SELECT index FROM table WHERE data = 'xxxx' LIMIT 1); Assuming of course you know that there will only be one result set

    Read the article

  • I need unusual ordering mysql results

    - by Patrick
    Im trying to order results ASCENDING from the current date this is what im using now; SELECT * FROM friends JOIN bdays ON bdays.user = friends.friendname WHERE username = $userid ORDER BY DATE_FORMAT(date, '%m %d') any ideas? example ordering by date now, sorts the birthdays starting at january what i need, is instead of starting the list at january, is starting it from the current date. So, instead of; January February March April May June July August September November December It will order them like this; April (current month/day) May June July August September November December January February March April (all the way up to yesterday)

    Read the article

  • MySQL column not found

    - by vian
    The SQL query without where statement runs great and outputs good results, but when I include WHERE condition it shows Unknown column 'date1' in 'where clause'. What's the problem? SELECT IF(e.weekly, DATE_ADD(DATE(e.time), INTERVAL CEIL(DATEDIFF('2010-04-08', e.time)/7) WEEK), DATE(e.time)) AS `e.date1`, `v`.`lat`, `v`.`lng` FROM `events` AS `e` INNER JOIN `venues` AS `v` ON e.venue_id = v.id WHERE e.date1 > '2010-09-01'

    Read the article

  • mySQL Left Join on multiple tables with conditions

    - by Jarrod
    Hi, I have a problem. I have 4 tables: Invoice_Payment, Invoice, Client, and Calendar Basically, I have the below query, which works well, except, months with no date_due don't return. I.E only months with a date_due will be returned. I think I have to use a left join and join the calendar table with something like: LEFT JOIN Calendar ON Invoice_Payments.date_paid = Calendar.date_field. But I'm having no luck SELECT MONTHNAME(Invoice_Payments.date_paid) as month, SUM(Invoice_Payments.paid_amount) AS total FROM Invoice, Client, Invoice_Payments WHERE Client.registered_id = 1 AND Client.id = Invoice.client_id And Invoice.id = Invoice_Payments.invoice_id AND date_paid IS NOT NULL GROUP BY YEAR(Invoice_Payments.date_paid), MONTH(Invoice_Payments.date_paid) Any help appreciated.

    Read the article

  • PHP mySQL database check

    - by kielie
    Hi guys, I am having endless troubles with duplicate entries, so I need to check the database, and if a user has already entered that day, their entry will not be submitted and they will be redirected to a landing page that tells them they have already entered that day, and that they may only enter again tomorrow. The field I would like to check is the id_number field in the database, since each user has a unique id number, so basically, if a user with the same id number submitted on the same date they should be redirected to a landing page, how would I go about doing this? I am still new to a lot of this, so please be forgiving. Thanx in advance.

    Read the article

  • Why can i read dirty rows in MySql

    - by acidzombie24
    I cant believe this, i always throught the below would be concurrency safe. I write to a row in one transaction and i am able to read the dirty value from another transaction/command/connection! Why is this possible (not my main question) isnt this not desired and cause more troubles!?! Anyways, i expected that once i write to a row nothing else will be able to read to the row until the transaction is finished. And at least if the row can be still read that the clean (original) value will be read. (but maybe that would cause problems as well if the transaction doesnt use the newly commited data from the other transaction when it is ran) I would like count to == 11. I thought this would be safe in all variants of sql. What can i do to either 1) Not read the dirty value but clean 2) Have that row be locked until the transaction is finished? static MySqlConnection MakeConn() { string connStr = "server=192.168.126.128;user=root;database=TestDB;port=3306;password=a;"; MySqlConnection conn = new MySqlConnection(connStr); conn.Open(); return conn; } static Semaphore sem1 = new Semaphore(1, 1); static Semaphore sem2 = new Semaphore(1, 1); static void Main2() { Console.WriteLine("Starting Test"); // sem1.WaitOne(); Console.WriteLine("1W"); sem2.WaitOne(); Console.WriteLine("2W"); Thread oThread = new Thread(new ThreadStart(fn2)); oThread.Start(); var conn = MakeConn(); var cmd = new MySqlCommand(@" CREATE TABLE IF NOT EXISTS Persons ( P_Id int NOT NULL, name varchar(255), count int, PRIMARY KEY (P_Id) )", conn); cmd.ExecuteNonQuery(); cmd.CommandText = "delete from Persons; insert into Persons(name, count) VALUES('E', '4');"; cmd.ExecuteNonQuery(); cmd.CommandText = "select count from Persons;"; var count = (int)cmd.ExecuteScalar(); Console.WriteLine("Finish inserting. v={0}", count); sem2.Release(); Console.WriteLine("2R"); sem1.WaitOne(); Console.WriteLine("1W"); Console.WriteLine("Starting transaction"); using (var tns = conn.BeginTransaction()) { cmd.CommandText = "update Persons set count=count+1"; cmd.ExecuteNonQuery(); cmd.CommandText = "select count from Persons;"; count = (int)cmd.ExecuteScalar(); Console.WriteLine("count is {0}", count); sem2.Release(); Console.WriteLine("2R"); sem1.WaitOne(); Console.WriteLine("1W"); count += 5; //10 cmd.CommandText = "update Persons set count=" + count.ToString(); cmd.ExecuteNonQuery(); cmd.CommandText = "select count from Persons;"; count = (int)cmd.ExecuteScalar(); Console.WriteLine("count is {0}", count); tns.Commit(); } Console.WriteLine("finished transaction 1"); sem2.Release(); Console.WriteLine("2R"); sem1.WaitOne(); Console.WriteLine("1W"); cmd.CommandText = "select count from Persons;"; count = (int)cmd.ExecuteScalar(); Console.WriteLine("count is {0}", count); sem2.Release(); Console.WriteLine("2R"); //sem1.WaitOne(); Console.WriteLine("1W"); } static void fn2() { int count; Console.WriteLine("Starting thread 2"); sem2.WaitOne(); Console.WriteLine("1W"); var conn = MakeConn(); var cmd = new MySqlCommand("", conn); sem1.Release(); Console.WriteLine("1R"); sem2.WaitOne(); Console.WriteLine("2W"); using (var tns = conn.BeginTransaction()) { cmd.CommandText = "update Persons set count=count+1"; cmd.ExecuteNonQuery(); cmd.CommandText = "select count from Persons;"; count = (int)cmd.ExecuteScalar(); Console.WriteLine("count is {0}", count); sem1.Release(); Console.WriteLine("1R"); sem2.WaitOne(); Console.WriteLine("2W"); tns.Commit(); } Console.WriteLine("finished transaction 2"); sem1.Release(); Console.WriteLine("1R"); sem2.WaitOne(); Console.WriteLine("2W"); cmd.CommandText = "select count from Persons;"; count = (int)cmd.ExecuteScalar(); Console.WriteLine("count is {0}", count); //should be 11. 4 + 1x2(one each thread) += 5 from first thread == 11 sem1.Release(); Console.WriteLine("1R"); } console Starting Test 1W 2W Starting thread 2 Finish inserting. v=4 2R 1W 1R 1W Starting transaction count is 5 2R 2W count is 6 1R 1W count is 10 finished transaction 1 2R 2W finished transaction 2 1R 1W count is 10 2R 2W count is 10 1R

    Read the article

  • MySQL - NULL value check and Dynamic SQL inside stored procedure

    - by Mithun P
    DROP PROCEDURE IF EXISTS HaveSomeFun; CREATE PROCEDURE HaveSomeFun(user_id CHAR(50),house_id CHAR(50),room_id CHAR(50),fun_text TEXT,video_url CHAR(100)) BEGIN DECLARE query_full TEXT; SET @fields_part = 'INSERT INTO fun(FunKey,UserKey,FunBody,LastModified'; SET @values_part = CONCAT(') VALUES( NewBinKey(), KeyToBin(\"', user_id, '\"), \"', fun_text, '\", NOW() '); IF (house_id) THEN SET @fields_part = CONCAT(@fields_part, ', HouseKey'); SET @values_part = CONCAT(@values_part, ', KeyToBin(\'', house_id, '\')'); END IF; IF (room_id) THEN SET @fields_part = CONCAT(@fields_part, ', RoomKey'); SET @values_part = CONCAT(@values_part, ', KeyToBin(\'', room_id, '\')'); END IF; IF (video_url IS NOT NULL) THEN SET @fields_part = CONCAT(@fields_part, ', VideoURL'); SET @values_part = CONCAT(@values_part, ', "', video_url, '"'); END IF; SET query_full = CONCAT(@fields_part, @values_part, ' );'); SET @query_full = query_full; PREPARE STMT FROM @query_full; EXECUTE STMT; SELECT query_full; END; And CALL HaveSomeFun('29B455DE-A9BC-102D-9C16-00163EEDFCFC', '', 'F82C47A8-64DE-11DF-9D7E-0026B9481364', 'Jokes apart', ''); will construct the below string in the variable query_full INSERT INTO fun(FunKey,UserKey,FunBody,LastModified, VideoURL) VALUES( NewBinKey(), KeyToBin("29B455DE-A9BC-102D-9C16-00163EEDFCFC"), "Jokes apart", NOW() , "" ); But I need to get INSERT INTO fun(FunKey,UserKey,FunBody,LastModified, RoomKey, VideoURL) VALUES( NewBinKey(), KeyToBin("29B455DE-A9BC-102D-9C16-00163EEDFCFC"), "Jokes apart", NOW() , KeyToBin('F82C47A8-64DE-11DF-9D7E-0026B9481364'), "" ); Something is missing in the check IF (room_id) THEN. But I cannot impose IF (room_id IS NOT NULL) THEN since it will create KeyToBin('') and RoomKey is foreign key , KeyToBin('') will produce an invalid RoomKey. Any Idea?

    Read the article

  • Limiting selected rows count with a stored procedure parameter in MySQL

    - by ssobczak
    I have a procedure SelectProc wich contains SELECT statement. I want to add a procedure param LimitRowsCount and use it as following: CREATE PROCEDURE SelectProc (IN LimitRowsCount INTEGER UNSIGNED) BEGIN SELECT (...) LIMIT LimitRowsCount; END but this approach doesn't work. The SELECT itself contains nested subqueries so I can't create view from it. Is there a way more propper then dynamic SQL (prepared statements) ?

    Read the article

  • MySQL Select problems

    - by John Nuñez
    Table #1: qa_returns_items Table #2: qa_returns_residues I have a long time trying to get this Result: item_code - item_quantity 2 - 1 3 - 2 IF qa_returns_items.item_code = qa_returns_residues.item_code AND status_code = 11 THEN item_quantity = qa_returns_items.item_quantity - qa_returns_residues.item_quantity ELSEIF qa_returns_items.item_code = qa_returns_residues.item_code AND status_code = 12 THEN item_quantity = qa_returns_items.item_quantity + qa_returns_residues.item_quantity ELSE show diferendes END IF I tried this Query: select SubQueryAlias.item_code, total_ecuation, SubQueryAlias.item_unitprice, SubQueryAlias.item_unitprice * total_ecuation as item_subtotal, item_discount, (SubQueryAlias.item_unitprice * total_ecuation) - item_discount as item_total from ( select ri.item_code , case status_code when 11 then ri.item_quantity - rr.item_quantity when 12 then ri.item_quantity + rr.item_quantity end as total_ecuation , rr.item_unitprice , rr.item_quantity , rr.item_discount * rr.item_quantity as item_discount from qa_returns_residues rr left join qa_returns_items ri on ri.item_code = rr.item_code WHERE ri.returnlog_code = 1 ) as SubQueryAlias where total_ecuation > 0 GROUP BY (item_code); The query returns this result: item_code - item_quantity 1 - 2 2 - 2

    Read the article

  • mySql table returning JSON that needs formatting for a iPhone UITableview

    - by Michael Robinson
    I have a php query the returns the following JSON format from a table. [{"memberid":"18", "useridFK":"30", "loginName":"Johnson", "name":"Frank", "age":"23", "place":"School", }, It needs the following format: [{"memberid":"18" { "useridFK":"30", "loginName":"Johnson", "name":"Frank", "age":"23", "place":"School",} }, I can figure out where/how to convert this, Where would I create the formatting following: (1) In the php return? (2) the JSON instructions for deserialization? or (3) Some kinb of Obj-C coding instruction? My end use is a simple Drill Down table using the NSObject, so when I select "memberid" row, I'll get the child/detail list on the next UITableview. My Data.plist will look like the following: Root: Dictionary V Rows: Array V Item 0: Dictionary Title: String 18 V Children Array V Item 0 Dictionary Title String 30 etc. Thanks in advance, this site rocks.

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 190 191 192 193 194 195 196 197 198 199 200 201  | Next Page >