mysql

SQL Delete: can't specify target table for update in FROM clause

I have a table *story_category* in my database with corrupt entries. The next query returns the corrupt entries. SELECT * FROM story_category WHERE category_id NOT IN ( SELECT DISTINCT category.id FROM category INNER JOIN story_category ON category_id=category.id); I tried to delete them excuting: DELETE FROM story_category WHERE c...

MySQL Partitioning / Sharding / Splitting - which way to go?

We have an InnoDB database that is about 70 GB and we expect it to grow to several hundred GB in the next 2 to 3 years. About 60 % of the data belong to a single table. Currently the database is working quite well as we have a server with 64 GB of RAM, so almost the whole database fits into memory, but we’re concerned about the future wh...

How do I do backups in MySQL?

How do I do backups in MySQL? I'm hoping there'll be something better than just running mysqldump every "x" hours. Is there anything like SQL Server has, where you can take a full backup each day, and then incrementals every hour, so if your DB dies you can restore up to the latest backup? Something like the DB log, where as long as...

MySQL UTF/Unicode migration tips

Does anyone have any tips or gotcha moments to look out for when trying to migrate MySQL tables from the the default case-insenstive swedish or ascii charsets to utf-8? Some of the projects that I'm involved in are striving for better internationalization and the database is going to be a significant part of this change. Before we look ...

How do you prevent SQL injection in LAMP applications?

Here are a few possibilities to get the conversation started: Escape all input upon initialization. Escape each value, preferably when generating the SQL. The first solution is suboptimal, because you then need to unescape each value if you want to use it in anything other than SQL, like outputting it on a web page. The second solut...

How do you list all triggers in a MySQL database?

What is the command to list all triggers in a MySQL database? ...

Setting Up MySQL Triggers

I've been hearing about triggers, and I have a few questions. What are triggers? How do I set them up? Are there any precautions, aside from typical SQL stuff, that should be taken? ...

Can I detect and handle MySQL Warnings with PHP?

I'm dealing with a MySQL table that defines the JobName column as UNIQUE. If somebody tries to save a new Job to the database using a JobName that is already in the database, MySQL throws a warning. I would like to be able to detect this warning, just like an error, in my PHP script and deal with it appropriately. Ideally I would like...

How-to: Ranking Search Results

I have a webapp development problem that I've developed one solution for, but am trying to find other ideas that might get around some performance issues I'm seeing. problem statement: a user enters several keywords/tokens the application searches for matches to the tokens need one result for each token ie, if an entry has 3 tokens,...

PHP + MySql + Stored Procedures, how do I get access an "out" value?

Documentation is severely lacking on anything to do with stored procedures in mysql with PHP. I currently have a stored procedure that I call via PHP, how can I get the value of an out parameter? ...

Max Table size you have seen in a MySQL DB

Hey guys, What is the maximum size you have seen a MySQL table? 2 Million at 50gig? 5million at 80gig? Any stories appreciated. Did you end up compressing data? Splitting the table if it grew too big? ...

Deploy MySQL Server + DB with .Net application.

HI All, We have a .Net 2.0 application which has a MySQL backend. We want to be able to deploy MySQl and the DB when we install the application and im trying to find the best solution. The current setup is to copy the required files to a folder on the local machine and then perform a "NET START" commands to install and start the mysql ...

What is the difference between UNION and UNION ALL

What is the difference between UNION and UNION ALL. ...

ASP.NET 3.5 Without Microsoft SQL Server - What do I lose?

I was just assigned to do a CMS using ASP.net 3.5 and MySQL. I am kind of new to ASP.NET development (quite sufficient with C#) and I am wondering what major ASP.NET and general .NET features I am losing when I don't have the option to use Microsoft SQL Server. I know already from quick Googling that I lose LINQ (and I was really lookin...

How do I conditionally set a column to its default value with MySqlParameter?

I have a table in a MySql database that stores user accounts. One of the columns, expires, stores an expiration date but defaults to NULL. I need to be able to remove an expiration date and set it back to the default value. Currently, all of my CRUD routines are written using MySqlCommand with parameters. Can this be done directly with ...

Best tool for synchronizing MySQL databases

I'm on a little quest of merging the structure of two MySql databases. Is there a tool for this with the might of Red-Gate's SQL Compare? Are there any free alternatives? ...

How do I change the password of the root user in MySQL?

I have long since forgotten the password for the root user on one of my boxes. Is there a way I can change it without having to log in to the instance, or will I have to reinstall? ...

How do I enable external access to MySQL Server?

How do I enable external access to MySQL Server? I can connect locally but I cannot connect from another box on the network. I just tried grant all privileges on *.* to root@'%' identified by '*****' with grant option; And restarted MySQL Server with no success. ...

How to efficiently SQL select newest entries from a MySQL database?

I have a database containing stock price history. I want to select most recent prices for every stock that is listed. I know PostreSQL has a DISTINCT ON statement that would suit ideally here. Table columns are name, closingPrice and date; name and date together form a unique index. The easiest (and very uneffective) way is SELECT * F...

One big SQL Select statement or several small ones (Performance)?

Hi everyone, I'm building a PHP page with data sent from MySQL. Is it better to have 1 SELECT query with 4 table joins, or 4 small SELECT queries with no table join (I do select from an ID). Which is faster and what is the pro/con of each method? PS. I only need one row from each tables. ...