mysql

Foreign Keys and MySQL Errors

I have the following script to create a table in MySQL version 5.1 which is to refer to 3 other tables. All 3 tables have been created using InnoDB, and all 3 tables have the ID column defined as INT. I have created other tables successfully which reference ACCOUNT and PERSON, however, this is the first table which references ADDRESS, s...

MySQL driver issues with INFORMATION_SCHEMA?

I'm trying out the Concurrence framework for Stackless Python. It includes a MySQL driver and when running some code that previously ran fine with MySQLdb it fails. What I am doing: Connecting to the MySQL database using dbapi with username/password/port/database. Executing SELECT * FROM INFORMATION_SCHEMA.COLUMNS This fails with me...

using php xmlreader to get data from xml to mysql

I am using php and xmlreader to retreive data from an xml file and insert into a mysql table. I chose xmlreader because the files supplied me are 500mb. I am new to all of this and am at a sticking point getting the data to insert properly into the mysql table. Sample xml from file... <us:ItemMaster> <us:ItemMasterHeader> <oa:I...

How does MySQL execute an aggregate query with a limit?

Does it group all possible results and then send back the results found within the given LIMIT? ...

Selecting records in groups by date - possible?

I don't think there is an elegant way to do this, but here goes. Database contains 5000 records with timestamps of 2 years. I need to pull the records under each day of the year. So it looks like.. 09/09/2009 - record938, record2, record493 09/10/2009 - record260, record485, record610 ...etc I cannot use GROUP BY. There are duplicat...

#1071 - Specified key was too long; max key length is 767 bytes

When I executed the following command: ALTER TABLE `mytable` ADD UNIQUE ( `column1` , `column2` ); I got this error message: #1071 - Specified key was too long; max key length is 767 bytes Information about column1 and column2: column1 varchar(20) utf8_general_ci column2 varchar(500) utf8_general_ci I think varchar(20) only req...

Way to retrieve MySql Fulltext indexs information from PHP?

I asked How to view MySql Fulltext Indexs and got myisam_ftdump as answer. But it's just a command line tool and I need to retrieve those information from PHP. Is there any way to retrieve MySql Fulltext indexs information from PHP? Thanks ...

Calculate greatest number of days between two consecutive dates

If you have an array of ISO dates, how would you calculate the most days between two sequential dates from the array? $array = array('2009-03-11', '2009-03-12', '2009-04-12', '2009-05-03', '2009-10-30'); I think I need a loop, some sort of iterating variable and a sort. I can't quite figure it out. This is actually being output from M...

need an sql query

I currently have two tables: 1. car(plate_number, brand, cid) 2. borrow(StartDate, endDate, brand, id) I want to write a query to get all available brand and count of available cars for each brand ...

Join results of two select statements

Hey all- I have a table serving as a transaction log: Date Action Qty 11-23 ADD 1 11-23 REMOVE 2 11-23 ADD 3 I would like a query to aggregate all of ADDs and all of the REMOVEs separately for a given date. Each of these select statements work fine, but they cannot be joined: select date, sum(qty) as Added from tabl...

Selecting specific values in a DB (sorta...)

Ok, I had a hard time coming up with a title, as you can probably tell. Anyway, I need to select 5 items from a DB excluding the first one. More specifically, I have items in a DB, each with a specific, auto-incrementing ID. I need to select the first 5 after the item with the highest ID. What would be the best way to do this? Thanks! -...

MySQL performance issue - lots and lots of ORing

The Facebook API has a call $facebook->api_client->friends_getAppUsers(); and that returns the friend ids of a user who is using the app. Now say I want to select from my db table all the rows that match all those friend ids. The only way I can think of doing it would be SELECT * FROM my_table WHERE uid IN(friend1, friend2, friend3, f...

How can I compile MySQL 5.1 on a 64 bit CentOS machine in order to achieve high performance?

I hope to achieve improved performance over the stock builds by creating a custom build of MySQL, tailored for 64-bit CentOS and InnoDB. However, I have no idea where to begin here. Would someone please be so kind as to provide me with a list of the steps I must take in order to accomplish this task? ...

database sharding strategy

For an online marketplace product under construction, I have a situation which requires implementing a database sharding solution. I am new to sharding and after reading the posts in this forum I feel a directory based sharding strategy using business entities will be suitable. But I am still not clear about the denormalization and data ...

skipping slow query log

If I save a statement in a text file and call it at command prompt like this... mysql -uroot -pPassWord DB_Name < somefile.txt The statements taking a long time do not appear in the slow query log even though all the statements do appear in the general and binary logs. Is it a bug? ...

MYSQL, how to get rows with field value of 'x' in this case ?

I am using PHP to query a mysql db. After the query, I am using mysql_fetch_array in a while loop to display all results inside a table. Now, depending on a variable ($adtypes) I want the query-results-array to change... Explanation: ($query_results is the name of the array containing the query results, basically something like SELEC...

How to switch old-passwords OFF in Mysql 4.1

Hi there, I am having a hard time migrating my MySQL 4.1 database from old 16-byte password hashes to the new 41-byte hashes. The problem is, that the mysqld server automatically starts with the "old-passwords = on" directive, which restricts setting new passwords to 41-bytes length. My question: Does anyone know how I can tell mysqld ...

select the first 5 records in a particular order for each foreign key

I have a table with three columns fk_id, sort_column, value_column for each fk__id_ I'd like to retrieve the first 5 records order by the sort_column I've tried this for several hours now and I don't have the slightest idea on how to do it I'm using MySQL I'd be grateful for any help Marc EDIT: I should probably clarify the outp...

Is there a MySQL equivalent of php's Preg_Replace?

When I search the db for a certain value, say a singular noun like "party", I want to be able to get the value "party" from the db, even though it's actually "parties". For that I thought about replacing the -ies suffix for -y, which didn't have any conspicuous exceptions (maybe "lies"). Is there a MySQL equivalent to the PHP Preg_Repla...

mysqldump with partitioned table

Hi, I have a large MyiSam table and I recently partitioned it. I backup the db every day usng mysqldump but rather than bakcup the entire large table, I'd like to just backup certain partition(s). Is there any way that I can specify which partitions to dump using mysqldump? ...