mysql

Configuring Django to use remote mysql server?

I am trying configure a Django application to use mysql. I want to use the mysql server available in my network and don't want to install a local copy. But when I am trying to install mysqldb it needs mysql_config from Mysql! What should I do if I want to use the mysql server in another machine? sh: mysql_config: command not found Tr...

How to break up reports by month with php and mysql?

Hi everybody, I'm trying to do something relatively simple here. Basically I have a table with a bunch of rows in it marked with a timestamp (format: 2009-05-30 00:14:57). What I'm wanting to is do is a query which pulls out all of the rows, and splits them by the month so I'm left with a final result like: February rowID name order d...

MySQL ON DUPLICATE KEY UPDATE with nullable column in unique key

Our MySQL web analytics database contains a summary table which is updated throughout the day as new activity is imported. We use ON DUPLICATE KEY UPDATE in order that the summarization overwrites earlier calculations, but are having difficulty because one of the columns in the summary table's UNIQUE KEY is an optional FK, and contains ...

Simple MySQL Query Taking 2 to 3 seconds?

I have a fairly simple process running that periodically pulls RSS feeds and updates articles in a MySQL database. The articles table is filled to about 130k rows right now. For each article found, the processor checks to see if the article already exists. These queries almost always take 300 milliseconds, and about every 10 or 20 tries...

Fluent Nhibernate: Can't create database connection to MySQL

I have been looking into Fluent Nhibernate, and it seems really promising. But... I can't seem to get it working with a MySQL database. I have the Fluent Nhibernate example project running fine with the SQLite database (Fluent NHibernate: Getting Started), but as soon as I change the database configuration to this: return F...

Mysql create table query problems?

I want to create a table, which name something like : name_thisyear_thisweekofyear, e.g -> name_2009_40 I tried this query: CREATE TABLE IF NOT EXISTS name_YEAR(NOW())_WEEKOFYEAR(NOW())(id int NOT NULL AUTO_INCREMENT PRIMARY KEY, phone_no int, created datetime, deleted datetime) It doesnt work. Please help Error Message: #1064 - Y...

Multiple database connection in Rails

I'm using active_delegate for multiple connection in Rails. Here I'm using mysql as master_database for some models,and postgresql for some other models. Problem is that when I try to access the mysql models, I'm getting the error below! Stack trace shows that, it is still using the postgresql adapter to access my mysql models! Runtime...

Problem using MySQLdb: Symbol not found: _mysql_affected_rows

A colleague got this error message when trying to use MySQLdb from Django: [...] ImproperlyConfigured("Error loading MySQLdb module: %s" % e) django.core.exceptions.ImproperlyConfigured: Error loading MySQLdb module: dlopen(/Users/roy/.python-eggs/MySQL_python-1.2.3c1-py2.5-macosx-10.5-i386.egg-tmp/_mysql.so, 2): Symbol not found: _my...

MySQL slow query in rare cases even though index is being used

Hi, I have a query in a large MySQL table (>4 million rows). This query is used in a stored procedure and it searches by surname and another numeric field. When I use different combinations of these search parameters, I get quick results (between 1 and 2s) but with some particular values, I get a query which takes 9s to return results o...

Full site search?

I was hoping someone could give me some advice, I am hoping to implement a full site search on a website that I building with codeIgnitor, PHP and mySQL. I was hoping someone would be able to over me some advice on wheather there are any free API's that enable a full site search, or any tips on building my own, how would be the best way ...

Adding binary checkbox values to MySQL database using PHP

I'm new to PHP, and I am creating a basic CMS using PHP and MySQL. I'm struggling to get the checkbox information from my HTML page across into the database. How can I make the values to appear as binary 0 or 1 values? The HTML document is written as follows: <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w...

Most efficient way of selecting the changes between timestamped snapshots

I have a table that holds data about items that existed at a certain time - regular snapshots taken. Simple example: Timestamp ID 1 A 1 B 2 A 2 B 2 C 3 A 3 D 4 D 4 E In this case, Item C gets created sometime between snapshot 1 and 2 and somet...

MySQL PHP count(*) returning something weird

I'm running the following query, expecting it to return an INTEGER, but it returns "Resource id #3" What am I doing wrong? $queryPlans = mysql_query("SELECT count(*) FROM infostash.rooms"); echo $queryPlans; There are actually 15 rows in this table, and I would like to return the number 15. Any suggestions? ...

restoring mysql db from the contents of split up mysqldump

Hi my database has started to go over 2GB in backed up size, so I'm looking at options for splitting the file and then reassembling it to restore the database. I've got a series of files from doing the following backup shell file: DATE_STRING=\date +%u%a\ BACKUP_DIR=/home/myhome/backups /usr/local/mysql_versions/mysql-5.0.27/bin/mys...

How to work with zero dates ("0000-00-00") in Hibernate?

I have MySql table that has a date field with zeroes ("0000-00-00") as its default value (field cannot be null, I can't change table structure). Hibernate doesn't like zero dates and throws exception during read or save. I managed to make it read records by setting MySql connection setting "zeroDateTimeBehavior=convertToNull" that conv...

Altering constraint delete action

I did this: ALTER TABLE `db`.`my_table` ADD CONSTRAINT FOREIGN KEY (`my_second_table_id`) REFERENCES `my_second_table` (`id`); I should have done this: ALTER TABLE `db`.`my_table` ADD CONSTRAINT FOREIGN KEY (`my_second_table_id`) REFERENCES `my_second_table` (`id`) ON DELETE SET NULL; Is it possible to ALTER this, or do I have...

How to optimize mysql date_format() for speed in where clause?

Hi guys, I'm trying to optimize relatively big mysql (myisam) table with 220,000 rows. The table itself is not so big - about 23.5MB in size. So, what's the real problem? - i got query like this: SELECT * FROM table WHERE DATE_FORMAT(date_field, '%m%d') = '1128' LIMIT 10 I tried to set an index on date_field but EXPLAIN show that the ...

MYSQL insert random from list.

I would like to add a random value to a table. While I know how to add random integers within a range, I am currently stumped on how to add a randomly selected item from a list. Let's say I have a MYSQL table for IM accounts. I would like to fill it with random data. INSERT INTO `im` (`im`, `service`) SELECT LOWER(`last`), RANDOM-SELEC...

how to import delimited data into mysql in phpmyadmin

This is a sample of the data im working with 24701||37.279788||-81.229023||WV||BLUEFIELD||MERCER as you can see its double pipe delimited. phpmyadmin is asking me for the following Fields Terminated By Fields Enclosed By Fields Escaped By Lines Terminated By what do i put to correctly import my data? ...

Determine Logic for MySQL Select - Reservation System

Hi all, I'm creating an equipment reservation (PHP/MySQL) for my school. My database looks like this: tblEvents: id start end equipID tblEquipment: id name description I'd like to know the best way to query the database based on a user's time parameters to see if a certain item is 'not available.' For example, if I have...