MySQL slow query log - how slow is slow?
What do you find is the optimal setting for mysql slow query log parameter, and why? ...
mysql
mysql: Got a packet bigger than 'max_allowed_packet' bytes
Hi, I'm importing a mysql dump and getting the following error: $ mysql foo < foo.sql ERROR 1153 (08S01) at line 96: Got a packet bigger than 'max_allowed_packet' bytes The dump is a mysqldump of a FogBugz database. it's FogBugz 6.1.33, Unix. Apparently FogBugz stores attachments in the database, which makes for very large inserts. ...
MySQL Trigger & Stored Procedure Replication
Ok,I'm running a setup with a single master and a number of slaves. All writes go through the master and are replicated down to the slaves which are used strictly for reads. Now I have a stored procedure (not function) which is called by a trigger on an insert. According to the MySQL docs, for replication triggers log the call to the t...
Fetch one row per account id from list
I have a table with game scores, allowing multiple rows per account id: scores (id, score, accountid). I want a list of the top 10 scorer ids and their scores. Can you provide an sql statement to select the top 10 scores, but only one score per account id? Thanks! ...
How does one create an index on the date part of DATETIME field in MySql
How do I create an index on the date part of DATETIME field? mysql> SHOW COLUMNS FROM transactionlist; +-------------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------------+------------------+------+-----+---------+----------...
Practical limit to length of SQL query (specifically MySQL)
Is it particularly bad to have a very, very large SQL query with lots of (potentially redundant) WHERE clauses? For example, here's a query I've generated from my web application with everything turned off, which should be the largest possible query for this program to generate: SELECT * FROM 4e_magic_items INNER JOIN 4e_magic_item_lev...
What causes tables to need to be repaired?
Every so often I get an error saying one of my tables "is marked as crashed and should be repaired". I then do a REPAIR TABLE and repair it. What causes them to be marked as crashed and how can I prevent it? I am using MyISAM tables with MySQL 5.0.45. ...
how to trim leading zeros from alphanumeric text in mysql function
What mysql functions are there (if any) to trim leading zeros from an alphanumeric text field? Field with value "00345ABC" would need to return "345ABC". ...
MySQL triggers + replication with multiple databases.
I am running a couple of databases on MySQL 5.0.45 and am trying to get my legacy database to sync with a revised schema, so I can run both side by side. I am doing this by adding triggers to the new database but I am running into problems with replication. My set up is as follows. Server "master" Database "legacydb", replicates to s...
What is the best solution for database connection pooling in python?
I have developed some custom DAO-like classes to meet some very specialized requirements for my project that is a server-side process that does not run inside any kind of framework. The solution works great except that every time a new request is made, I open a new connection via MySQLdb.connect. What is the best "drop in" solution...
How to have silverlight get its data from MySQL
I've written a small hello world test app in Silverlight which i want to host on a Linux/Apache2 server. I want the data to come from MySQL (or some other linux compatible db) so that I can databind to things in the db. I've managed to get it working by using the MySQL Connector/.NET: MySqlConnection conn = new MySqlConnection("Server=...
Best way to store event times in (My)SQL database
I'm trying to decide on the best way to store event times in a MySQL database. These should be as flexible as possible and be able to represent "single events" (starts at a certain time, does not necessarily need an end time), "all day" and "multi day" events, repeating events, repeating all day events, possibly "3rd Saturday of the mont...
What is the SQL command to return the field names of a table?
Say I have a table called myTable. What is the SQL command to return all of the field names of this table? If the answer is database specific then I need SQL Server right now but would be interested in seeing the solution for other database systems as well. ...
"Mysql server has gone away" with Rails
After our rails app has run for a while, it starts throwing 500s with "Mysql server has gone away". Often this happens overnight. It's started doing this recently, with no obvious change in our server configuration. Mysql::Error: MySQL server has gone away: SELECT * FROM `widgets` Restarting the mongrels (not the mysql server) fixes ...
MySQL Training videos
Hi were can I find training videos for MySQL ? ...
How do stop mysql on a Mac OS install
I installed it it via mac ports, what is the command I need to stop the server (i need to test how my app behave when MySQL is dead) ...
The ultimate MySQL legacy database nightmare
Table1: Everything including the kitchen sink. Dates in the wrong format (year last so you cannot sort on that column), Numbers stored as VARCHAR, complete addresses in the 'street' column, firstname and lastname in the firstname column, city in the lastname column, incomplete addresses, Rows that update preceeding rows by moving data fr...
Compare time part of datetime field in Hibernate
I've got an application that uses a hibernate(annotations)/mysql combination for ORM. In that application, I got an entity with a Date field. I'm looking for a way to select on that date within a time range (so hh:mm:ss without the date part). In MySQL there's a function TIME(expression) that can extract the time part and use that in t...
Is Oracle RDBMS more stable, secure, robust, etc. than MySQL RDBMS?
I've worked on a variety of systems as a programmer, some with Oracle, some with MySQL. I keep hearing people say that Oracle is more stable, more robust, and more secure. Is this the case? If so in what ways and why? For the purposes of this question, consider a small-medium sized production DB, perhaps 500,000 records or so. ...
`active' flag or not?
OK, so practically every database based application has to deal with "non-active" records. Either, soft-deletions or marking something as "to be ignored". I'm curious as to whether there are any radical alternatives thoughts on an `active' column (or a status column). For example, if I had a list of people CREATE TABLE people ( id ...