mysql

group_concat on an empty join in MySQL

Hello, I've got the following problem: I have two tables: (simplified) +--------+ +-----------+ | User | | Role | +--------+ +-----------+ | ID<PK> | | ID <PK> | +--------+ | Name | +-----------+ and M:N relationship between them +-------------+ | User_Role | +-------------+ | User<...

MySQL id sequence

Is this a correct way for id generation in MySQL ? INSERT INTO Picture (PictureId,First_pick,Title,Description,File_Name,Is_Vertical)VALUES ((SELECT max(pictureid)+1 FROM Picture),0,?,?,?,?) I mean if it is guaranted that PictureId will be unique when this query is run by many threads ? I can't modify table structure. Should I...

innodb lock wait timeout

As per the documentation link given below: When a lock wait timeout occurs, the current statement is not executed. The current transaction is not rolled back. (Until MySQL 5.0.13 InnoDB rolled back the entire transaction if a lock wait timeout happened. You can restore this behavior by starting the server with the --innodb_rollback_on...

can this problem be solved with a single SQL query?

I have the two following tables (with some sample datas) LOGS: ID | SETID | DATE ======================== 1 | 1 | 2010-02-25 2 | 2 | 2010-02-25 3 | 1 | 2010-02-26 4 | 2 | 2010-02-26 5 | 1 | 2010-02-27 6 | 2 | 2010-02-27 7 | 1 | 2010-02-28 8 | 2 | 2010-02-28 9 | 1 | 2010-03-01 ST...

Setting up an efficient and effective development process

I am in the midst of setting up the development environment (PHP/MySQL) for my start-up. We use three sets of servers: LIVE - the servers which provide the actual application TEST - providing a testing version before it is actually released DEV - the development servers The development servers run SVN with each developer checking out t...

Evaluate column value into rows

I have a column whose value is a json array. For example: [{"att1": "1", "att2": "2"}, {"att1": "3", "att2": "4"}, {"att1": "5", "att2": "6"}] What i would like is to provide a view where each element of the json array is transformed into a row and the attributes of each json object into columns. Keep in mind that the json array doesn...

How to store Java Date to Mysql datetime...?

can any body tell me how can i store Java Date to Mysql datetime...? when i am trying to do so...only date is stored and time remain 00:00:00 in mysql date stores like this... 2009-09-22 00:00:00 i want not only date but also time...like 2009-09-22 08:08:11 please help me.... EDIT---- i am using JPA(Hibernate) with spring mydomain cl...

Am I underestimating MySQL ?

Hi I'm about to implement a feature on my website that recommends content to users based on the content they already have in their library (a la Last.fm). A single table holds all the records of the content they have added, so a row might look something like: -------------------- | userid | content | -------------------- | 28 | ...

MySQL cluster questions

Hi! I've read much of the MySQL Cluster docs and some tutorials yet I still have some things not clear, and the major of them right now are: When a data node restarts (crash and goes up again) will it's data still be available? Updates/Additions will work as usual? Will it "sync"? Does a cluster work faster than standalone? In particu...

What would be the most efficient way to do this search (mysql or text)?

Suppose I have 500 rows of data, each with a paragraph of text (like this paragraph). That's it.I want to do a search that matches part of words. (%LIKE%, not FULL_TEXT) What would be faster? SELECT * FROM ...WHERE LIKE "%query%"; This would put load on the database server. Select all. Then, go through each one and do .find >= 0 This ...

MySQL short-circuit OR

table id | word SELECT id FROM table WHERE word = "hello" OR word = "ello" OR word = "llo" OR word = "lo" OR word = "o" I need to get only first id that is found. If not first - then second. If not second - then third .... Is it possible to get only first OR that is in the database, without checking all of them? ...

Cannot Access the CSS while developing in Ubuntu?

Hi, I am developing a web application using PHP and mysql.I was doing the intial development in a windows machine and now shifted to ubuntu ultimate edition.I installed apache/PHP/Mysql and configured them. I placed my web folder in /var/www and when I give the URL in browser,I am not getting the page with CSS. Not Working: href="./c...

MYSQL join, return first matching row only from where join condition using OR

Hello everyone, I'm having a problem with a particular MySQL query. I have table1, and table2, table2 is joined onto table1. Now the problem is that I am joining table2 to table1 with a condition that looks like: SELECT table1.*, table2.* JOIN table2 ON ( table2.table1_id = table1.id AND ( table2.lang = 'fr' ...

Best practice for storing global data in PHP?

Hi I'm running a web application that allows a user to log in. The user can add/remove content to his/her 'library' which is displayed on a page called "library.php". Instead of querying the database for the contents of the users library everytime they load "library.php", I want to store it globally for PHP when the user logs in, so tha...

How can I find all records for a model without doing a long list of "OR" conditions?

I'm having trouble composing a CakePHP find() which returns the records I'm looking for. My associations go like this: User ->(has many)-> Friends , User ->(has many)-> Posts I'm trying to display a list of all a user's friends recent posts, in other words, list every post that was created by a friend of the current user logged in. T...

Does MySQL have time-based triggers?

I would like to execute a procedure periodically, how to do that in MySQL? ...

MySQL: Changing order of auto-incremented primary keys?

Hi, I have a table with a auto-incremented primary key: user_id. For a currently theoretical reason, I might need to change a user_id to be something else than it was when originally created through auto-incrementation. This means there's a possibility that the keys will not be in incremental order anymore: PK: 1 2 3 952 // changed k...

php: what is the fastest way to display related data between three db tables

Hi, I'm looking for the best way to display data from 3 tables. Let me explain. I have one master table that lists events each event can have a number of categories so I'm using a join table linking the categories to the events. I want to loop through all the events and display under each event the categories that have been related via ...

MySQL, return only rows where there are duplicates among two columns.

I have a table in MySQL of contact information ; first name, last name, address, etc. I would like to run a query on this table that will return only rows with first and last name combinations which appear in the table more than once. I do not want to group the "duplicates" (which may only be duplicates of the first and last name, ...

Limiting selected rows count with a stored procedure parameter in MySQL

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 ca...