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...
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? 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...
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 ...
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...
What is the command to list all triggers in a MySQL database? ...
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? ...
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...
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,...
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? ...
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? ...
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. ...
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...
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 ...
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? ...
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? 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. ...
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...
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. ...