mysql

Binary Data in MySQL

How do I store binary data in MySQL? ...

Throw Error In MySQL Trigger

If I have a trigger BEFORE UPDATE on a table. How can I throw an error that prevents the update on that table?...

How to export data from SQL Server 2005 to MySQL

I've been banging my head against SQL Server 2005 trying to get a lot of data out. I've been given a database with nearly 300 tables in it and I need to turn this into a MySQL database. My first call was to use bcp but unfortunately it doesn't produce valid CSV - strings aren't encapsulated so you can't deal with any row that has a str...

SQL Server 2005 implementation of MySQL REPLACE INTO?

MySQL has this incredibly useful yet properitary REPLACE INTO SQL Command. I wonder: Can this easily be emulated in SQL Server 2005? Starting a new Transaction, doing a Select() and then either UPDATE or INSERT and Commit is always a little bit a pain in the a.., especially when doing it in the application and therefore always keeping 2 ...

Python and MySQL

I can get Python to work with Postgresql but I cannot get it to work with MySQL. The main problem is that on the shared hosting account I have I do not have the ability to install things such as Django or PySQL, I generally fail when installing them on my computer so maybe it's good I can't install on the host. I found bpgsql really goo...

How big can a MySQL database get before performance starts to degrade.

At what point does a MySQL database start to lose performance? Does physical database size matter? Do number of records matter? Is any performance degradation linear or exponential? Ive got (what I believe to be) a large database, almost 2GB and about 15M records. Should I be cleaning this out, or am I OK for another few years? ...

Mechanisms for tracking DB schema changes

What are the best methods for tracking and/or automating DB schema changes? Our team uses Subversion for version control and we've been able to automate some of our tasks this way (pushing builds up to a staging server, deploying tested code to a production server) but we're still doing database updates manually. I would like to find o...

Auto Generate Database Diagram MySQL

I'm tired of opening Dia and creating a database diagram at the beginning of every project. Is there a tool out there that will let me select specific tables and then create a database diagram for me based on a MySQL database? Preferably it would allow me to edit the diagram afterward since none of the foreign keys are set... Here is wh...

What all do I need to escape when sending a (My)SQL query?

When you execute a SQL query, you have to clean your 'strings' or users can execute malicious SQL on your website. I usually just have a function escape_string(blah), which: Replaces escapes () with double escapes (\). Replaces single quotes (') with an escaped single quote (\'). Is this adequate? Is there a hole in my code? Speedy...

MySQL/Apache Error in PHP MySQL query

I am getting the following error: Access denied for user 'apache'@'localhost' (using password: NO) When using the following code: <?phpinclude("../includes/connect.php");$query = "SELECT * from story";$result = mysql_query($query) or die(mysql_error());echo "<h1>Delete Story</h1>";if (mysql_num_rows($result) > 0) { while($row =...

Multiple Updates in MySQL

I know that you can insert multiple rows at once, is there a way to update multiple rows at once (as in, in one query) in MySQL? Edit: For example I have the following Name   id  Col1  Col2Row1   1    6     1Row2   2    2     3Row3   3    9     5Row4   4    16    8 I want to combine all the following Updates into one query UPDATE tab...

How do I create a new Ruby on Rails application using MySQL instead of SQLite?

I want to create my Rails application with MySQL, because I like it so much. How can I do that in the latest version of Rails?...

SQLite vs MySQL

SQLite is a flat-file database and MySQL is a normal database. That's great but I'm not sure which is faster where or better for what? What are the pros and cons of each option?...

SQL: Distribution of table in time

I have a MySQL table with approximately 3000 rows per user. One of the columns is a datetime field, which is mutable, so the rows aren't in chronological order. I'd like to visualize the time distribution in a chart, so I need a number of individual datapoints. 20 datapoints would be enough. I could do this: select timefield from entr...

Full complete MySQL database replication? Ideas? What do people do?

Currently I have two Linux servers running MySQL, one sitting on a rack right next to me under a 10 Mbit/s upload pipe (main server) and another some couple of miles away on a 3 Mbit/s upload pipe (mirror). I want to be able to replicate data on both servers continuously, but have run into several roadblocks. One of them being, under My...

How do I Concatenate entire result sets in MySQL?

I'm trying out the following query: SELECT A,B,C FROM table WHERE field LIKE 'query%' UNION SELECT A,B,C FROM table WHERE field LIKE '%query' UNION SELECT A,B,C FROM table WHERE field LIKE '%query%' GROUP BY B ORDER BY B ASC LIMIT 5 That's three queries stuck together, kindasorta. However, the result set that comes back reflects resul...

Long-time LAMP Developer moving to VB.NET

I've been developing with PHP5/MySQL for years, but recent circumstances have pushed me to move towards the .NET platform. I'd be interested to hear any advise from someone who has gone through this transition... What are some things you wish you knew when you started? I also have a Java background so I'm not scared of the OO aspect ...

How do I retrieve my MySQL username and password?

I lost my MySQL username and password. How do I retrieve it?...

Bigger than a char but smaller than a blob

Char's are great because they are fixed size and thus make for a faster table. They are however limited to 255 characters. I want to hold 500 characters but a blob is variable length and that's not what I want. Is there some way to have a fixed length field of 500 characters in MySQL or am I going to have to use 2 char fields?...

Issues using MS Access as a front-end to a MySQL database back-end?

Two users wanted to share the same database, originally written in MS Access, without conflicting with one another over a single MDB file. I moved the tables from a simple MS Access database to MySQL using its Migration Toolkit (which works well, by the way) and set up Access to link to those tables via ODBC. So far, I've run into the ...