MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

3
votes
1answer
807 views

Duplicate UNIQUE KEY error after ROLLBACK in MySQL

I have a table like this mysql> describe seudonimos; +--------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra ...
0
votes
1answer
3k views

Change path for binary logs and scheduling

I want to change the default name of binary log and path. I saw the documentation where it discusses about base_name. However, I just need to confirm how to use it correctly. I want binary logs to be ...
4
votes
3answers
10k views

Optimizing InnoDB default settings

I looked into my.ini and saw various default settings. My database is running on a single stand-alone PC. I want to optimize the performance of InnoDB and MySQL in general for performance. There is no ...
4
votes
2answers
6k views

Do INSERTs get auto-committed?

Our application fires an INSERT query to the MySQL Database to add records. I want to know whether or not the records get auto-committed. If I run the ROLLBACK command, when does the database perform ...
1
vote
1answer
223 views

MySQL Database Design Help?

I am going through my database and what I want to do is re-design it because I feel like it's very messy and that I am doing it wrong. This is just for practice; I've never had anyone teach me so I ...
1
vote
2answers
635 views

Are there any MySQL Triggers or Events that my program can wait for?

Say I want a live feed of emails on a web based email system. Emails are inserted into MySQL tables. Let's say I want my program to know immediately when the table is updated so it can check for new ...
9
votes
1answer
4k views

How to migrate large blob table from mysql to postgresql?

I'm now in process of migrating my MySQL database to PostgreSQL. Almost everything went fine (well, after lots of googling for correct mysqldump params etc.) except one table I have - actually the ...
2
votes
2answers
587 views

Slow Query logging everything

I have a mysql instance that is logging slow queries. I have it set to 1 sec, but I am getting back lots of these:(example) Query_time: 0.000237 Lock_time: 0.000064 Rows_sent: 0 Rows_examined: ...
6
votes
3answers
2k views

query regarding combining an update and an insert query into a single query in mysql

i want to track history of changes for a user, so that whenever he changes his profile, i need to take the old data and store in history and update with new data. I can use a select to get the old ...
8
votes
2answers
3k views

What are the benefits of using MySQL Cluster?

I have never dipped my hands in MySQL Cluster and want to know how it can benefit me. When is it best to use it, and does it have performance benefits?
3
votes
2answers
123 views

What would be the best way to model my simple table?

What would be faster: searching for the right row through a table with 17 different columns, each with a tinyint value of 1-4? Or, searching for the right row through a table with only 1 column with ...
2
votes
1answer
2k views

What is the corresponding MySQL TimeStamp in MS SQL Server?

I need a timestamp to be created automatically when I insert information in a table. mySQL has TimeStamp but MS SQL Server TimeStamp doesn’t correspond mySQL. Is there any other way around?
7
votes
1answer
5k views

Does mysqldump export indices, by default?

I played around a little with mysqldump and I was wondering, if it does export indices (FULLTEXT, INDEX,...) by default. I read up on it and I found this option: --disable-keys, -K which suggests, ...
3
votes
1answer
6k views

DROP PROCEDURE IF EXISTS not included in mysqldump

I'm dumping my stored procedures only using the following command: mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt databasename -u username -p > outputfile.sql but the ...
2
votes
2answers
2k views

Does mysql SUPER privilege include ALL?

I'm granting the following rights to a user: grant super on . to 'user'@'host'; does this include the rights on dbname given by: grant all on dbname.* to 'user'@'host'; thank you
3
votes
3answers
687 views

Restoring an old backup to latest MySQL release

Our old database is MySQL 5.1 server and is four years old. If I uninstall MySQL 5.1 Server and install the latest release and restore the backup, will I gain performance benefits? I guess the new ...
5
votes
2answers
133 views

Optimizing a four-year-old MySQL database

I developed an application with MySQL 5.1 back-end four years ago. After that, I only gave remote support a couple of times, but that too for application crashes, and not for database. The database is ...
2
votes
2answers
753 views

Is MySQL Replication appropriate for keeping a laptop in sync?

I've setup Mysql master slave replication with the production server as master. I have three other machines as slaves: 1 laptop, 1 desktop, and 1 server. The desktop and laptop both get suspended to ...
4
votes
2answers
4k views

Prevent copying to temp table (sql)

I have two tables - comments and votes : comments(id, text, user_id, page_id) votes(id, value) There are 2,000,000 rows in the votes table. I created the following query: SELECT ...
1
vote
1answer
162 views

Complex mysql selection

I have the following table, id | booki_id | bet_id | bet | bettype | line | odds 1 | 123 | 321 | 1 | 3way | 0,0 | 2.3 2 | 123 | 321 | 2 | 3way | ...
2
votes
3answers
5k views

MySQL Slave lag in SHOW SLAVE STATUS does not match SHOW PROCESSLIST

I'm trying to figure out why the reporting of slave lag is different in SHOW SLAVE STATUS and SHOW PROCESSLIST, in MySQL 5.5.13. The only difference to other slaves of this master is that it is ...
3
votes
2answers
93 views

Exclude dataset from a query

I have 2 tables EXAM (StudentID, SubjectID, SchoolYear, Period, Mark) and CANCELED_EXAM (StudentID, SubjectID, SchoolYear, Period). Primary key for both tables are (StudentID, SubjectID, ...
1
vote
3answers
680 views

Best architecture solution for heavy data mining system, that can run on cloud

We are currently hosting our MySQL database on a dedicated server with lots of memory and processors. We would like to push it to a different architecture that would allow us to scale it easily, ...
2
votes
2answers
986 views

mysql inner join producing more results than it should

I am trying to get the roles name, verbs name, and privileges enabled value where privileges is a join table joining verbs and roles through a verb_id and roles_id foreign key. But I am having ...
3
votes
1answer
7k views

How do I open a MySQL Database Dump?

I work for a doctor's office, and we are in the process of switching EMR systems. The previous system has provided a physical 'dump' of the database. I however, don't know how to open this database ...
3
votes
1answer
412 views

MySQL Database Schema for Volleyball

I am developing a database for the recording of Volleyball results. I have a fixture database that tells me who plays who in each round. Teams Table, say IDs 1,2,3,4 Fixtures Table, Round No.1, Team ...
5
votes
2answers
2k views

What is the point of the TABLE_CATALOG column in INFORMATION_SCHEMA.TABLES?

In MySQL's table INFORMATION_SCHEMA.TABLES, there's a column named 'TABLE_CATALOG'. The documentation is sparse on this column and I'm wondering what the heck is the purpose of this? Any killer-app ...
4
votes
2answers
2k views

Query problem: there can be only one auto column

Can someone please tell me what's wrong with this table definition. mysql version is 5.1.52-log root@localhost spoolrdb> create table spoolqueue ( queue int, ...
6
votes
1answer
3k views

Mail alert from MySQL when databases are edited

How do I get mail from MySQL when root or someone else edits or updates a particular database? It should trigger sending a mail and log the edits. I have tried Triggers but its not working. Server ...
3
votes
2answers
3k views

Using MySQL triggers or transactions?

I want to ask your opinion in using MySQL triggers or transactions in a website. Actually I have a history payment table with - UserId | OperationId | Comment | Credits | Sign (debit or credit). So ...
6
votes
2answers
3k views

Trouble deciphering a deadlock in an innodb status log

We are accessing MySQL from the Microsoft ADO.NET connector. Occasionally we are seeing the following deadlock in our innodb status and haven't been able to identify the cause of the problem. It ...
5
votes
3answers
594 views

Would it be sensible to create a MySQL user for each user account of a web application?

The thought occurred to me recently, but I have a suspicion the idea might be insane. A couple benefits I'm perceiving are: facilitate automated logging via triggers control mysql connections on a ...
4
votes
1answer
701 views

What are your use-cases for HandlerSocket?

There's a bit of hype around: HandlerSocket It seems like faster, nosql access to MySQL data is becoming more desirable. Percona Server is now shipping with HandlerSocket built in. My question is, ...
4
votes
1answer
628 views

Speeding SQL queries for a large(?) WordPress database?

I am using a WordPress plugin called feedwordpress in order to run a planet like website (it takes content from other websites, with their permission) on WordPress (See it here). The plugin is great ...
4
votes
1answer
219 views

What program to use to change settings on db servers?

Having decided to set up a Master-Slave replication I found this article, which seems exactly what I want. So I fired up the MySQL Console in Navicat to give everything a try, and the commands do ...
5
votes
2answers
473 views

Estimating distribution of row access in mysql

I am trying to get an idea of the size of the "hot data" part of a rather large table, and I was wondering if this could be done directly in mysql. I know that with the percona version of mysql, I can ...
3
votes
4answers
380 views

How to get a column A which has the smaller column B grouping by column C?

I'd like to get the ID of a dataset for each category (my grouping column) which has the smallest "order" column. Here a set of data to explain my thoughts: CREATE TABLE DATAS ( ID INT(2) , ...
3
votes
1answer
376 views

Mysql fulltext boolean ignore phrase

I'm trying to come up with a mysql boolean search that matches "monkey" but ignores / doesn't take into account any matches of "monkey business". Something like the following is the general idea but ...
5
votes
3answers
494 views

What are the sharding heuristics for MySQL - number of records per table & number of tables per instance?

Designing an object store for ~10B objects and using mySQL for storing metadata. We know we need to shard and are looking for general heuristics to scale. Would appreciate pointers and hear about the ...
10
votes
3answers
1k views

What to do when a field in a table approaches the max signed or unsigned 32 bit integer?

In any given database that hold user records in the form of an unique auto-increment field (for the sake of the example, inter-user messages)... what to do when the time comes and it approaches the ...
6
votes
2answers
250 views

How should I Optimize storage for this table?

I'm using MySql to store a basic table of this format: id int(11) //Auto-Incrementing ID data varchar(5120) //Random input data, compressed by a program, not mysql. ...
4
votes
3answers
346 views

Help with complicated MySQL query

I asked an earlier question about modeling MySQL data modeling help. The recommendation was the diagram labeled Multiple Tables for Role and Sponsor. Now I'm trying to write the queries and ...
4
votes
2answers
202 views

Concurrency question?

I have a MySql (5.5) database with a MS Access frontend. If two users are viewing the same record at the same time how can I make each user aware of each other? For example how would I go about ...
2
votes
1answer
275 views

MySQL Update: why would rows matched change based on the set clause?

I ran an update query joining across a huge number of tables that updated one field, and it matched 29k some-odd rows. Running the same query with only an addition in the Set clause increased the ...
0
votes
1answer
136 views

Derived value not calculating as expected using combination of FK and Triggers (MySQL)

I'm having trouble with calculating a derived value using a combination of triggers and foreign key constraints. There are 3 tables involved in this arrangement responses ------------------------ ...
5
votes
1answer
2k views

What are user connections - when are the created and destroyed?

We've been getting an intermittent error on out site: Warning: mysql_connect() [function.mysql-connect]: User '557574_prod' has exceeded the 'max_user_connections' resource (current value: 10) ...
15
votes
3answers
19k views

What is the performance impact of using CHAR vs VARCHAR on a fixed-size field?

I have an indexed column that stores an MD5 hash. Thus, the column will always store a 32-character value. For whatever reason, this was created as a varchar rather than a char. Is it worth the ...
4
votes
3answers
625 views

Can I have create an InnoDB table without a PK? If so, would a secondary index be used as the clustered index?

Is it possible to create an InnoDB table without a Primary Key? Since InnoDB tables are structured as clustered index around the PK, what would be the structure of a table without PK? Would that ...
6
votes
3answers
7k views

How do I remove backslashes from records in MySQL

Long story short, I have backslashes in my data due to PHP magic quotes. I have straightened out what was wrong there, but have some records now which have unnecessary backslashes. 1 | This is an ...
5
votes
1answer
445 views

Dealing with temp tables when I have not control over db variables

I have no control over things like tmp_table_size and max_heap_table_size, and so as our tables grow the time taken by queries requiring temp tables is growing geometrically. I am wondering if there ...