MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
1answer
49 views

JOIN to read first table and get possible relationship in the second table

Consider a main table as CREATE TABLE groups ( group_id int(11) NOT NULL AUTO_INCREMENT, group_title varchar(255), PRIMARY KEY(group_id) ) ENGINE=InnoDB and a second table for relationship as ...
3
votes
2answers
1k views

MySQL version confusion

How is this possible: I installed mysql-5.6.11-osx10.7-x86.dmg which is supposed to be version Mac OS X ver. 10.7 (x86, 32-bit), DMG Archive (32 bit!) But upon SHOW VARIABLES LIKE "%version%"; I get ...
1
vote
1answer
160 views

Is there a way to recover this database (MySQL)

I did a "cold" (db offline, all filesystem files) backup of a MySQL db. I wrote the backup to an ISO image. Unfortunately Joliet filenames are not default. I didn't realize that until attempting to ...
1
vote
2answers
211 views

MySQL Master/Slave. Actual use of Slave Machine

We have a MySQL server running in a mobile production trailer. The trailer is out on the road for weeks at a time. We have cell internet and wifi access is nearly all of our locations. Our office ...
4
votes
2answers
3k views

Partitioning and freeing disk space

I am running MySql innodb with innodb_file_per_table. As a daily script, I would like to create a new partition for a set of tables, as well as delete yesterday's partitions. What command would need ...
3
votes
1answer
867 views

Linked tables versus stored procedure performance in MySQL

Suppose I have a sub-form, in datasheet view for multiple records, that gives the user full CRUD control over a particular MySQL table. (The records are filtered by the parent-form). Option 1 is to ...
7
votes
1answer
4k views

MySQL Hanging Completely when `ALTER TABLE… ENABLE KEYS`

I know very little about database administration but I have to deal with some very large tables on my site. This server has 64GB of RAM and Intel Core i7-3820 (4 x 3600 MHz). Most of everything it ...
2
votes
1answer
50 views

How can I include more query in one query?

These are my tables: tbl_answers => aid qid answer uid dateposted emailnotify namedisplay status isbestanswer tbl_questions => qid question ...
0
votes
1answer
265 views

Does MySQL reserve memory for MEMORY tables based on max_heap_table_size

Currently I have a set of large MEMORY tables, and have previously calculated how much space I believe each will require and assigned max_heap_table_size accordingly so that the sum is not greater ...
0
votes
2answers
2k views

Is there a good reason to store big text data outside database?

I was analyzing my MySQL database performance and found that tables containing text fields are just huge, larger than 1Gb. This data is only used when loading single record (like article or blog ...
1
vote
1answer
2k views

MySQL Workbench - question about creating FK relationships

Forgive me a little new at databases and I came across something while working with Workbench that I have a question on. In a very simple testing database, with three tables: Company, Department, ...
0
votes
0answers
766 views

Properly index magento mysql tables to improve specific query performance

I am running magento community edition and its db performance is horrible. I have the following query: SELECT `width_idx`.`value`, COUNT(DISTINCT width_idx.entity_id) AS `count` FROM ...
1
vote
2answers
3k views

Is it better to use AUTOCOMMIT = 0

Is it better to use (for perfomance tuning) AUTOCOMMIT = 0 before bulk inserts of data in MySQL, if the insert query looks like INSERT INTO SomeTable (column1, column2) VALUES ...
4
votes
3answers
22k views

Cannot GRANT privileges as root

Because of having some problems, I decided to re-create all users except for root@localhost. This works fine, but the newly created user has no right to do anything. What I want is to simply give all ...
2
votes
2answers
379 views

MySQL many to many relation

Sorry is this seems a stupid question, but I have a table of server addresses and a second table with a list of hardware devices which use the addresses, it is possible for multiple devices to use the ...
3
votes
1answer
300 views

Will creating a view on a MySQL replication slave break the replication?

We need to run some very ugly queries on a MySQL replication cluster. Does anyone know if creating a view directly on the slave will break the replication of the other, otherwise untouched data ...
1
vote
2answers
199 views

TokuDB/InnoDB Question

What's the faster scheme for insert data to TokuDB or InnoDB Engine? Over 200 Insert per second. That's okay to answer of one engine. TABLE: `game` `gameId` bigint 20 (PK) `gameMapId` int 4 ...
2
votes
1answer
2k views

Percona Node fails to join: Operation not permitted

Joining a new Percona node fails with: 130521 7:15:44 [Warning] WSREP: Failed to prepare for incremental state transfer: Local state UUID (00000000-0000-0000-0000-000000000000) does not match group ...
0
votes
1answer
133 views

Looking for performance improvements for this query

This query is taking a long time to execute. We are expecting results in 0 sec, if possible. Please help me to do any other following: Rewrite the query Suggest any indexes Any other optimization ...
1
vote
1answer
2k views

ERROR 13 (HY000) When Creating Database

I'm trying to create a database and when I type in CREATE DATABASE epik; I get the error ERROR 13 (HY000): Can't get stat of './epik' (Errcode: 13) I am running kubuntu 13.04 with mysql 5.5.31.
0
votes
1answer
94 views

Index optimization

I'm building a 'tag' system for my blog posts. Currently my tags table has columns tagid - MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY and tag VARCHAR(30) NOT NULL UNIQUE. And I have another table ...
1
vote
1answer
604 views

MySQL Stalls and stops all queries

We occassionally have our servers stall and back up queres for 3-5 minutes. then back to normal but locks up all users. We made a new server with binlogs and log fiels on seperate disk from data. ...
1
vote
1answer
83 views

Query optimization when no row is returned

I am using explain to figure out what is happening in my query which is: explain select t from c where u1_id = 1 group by t; I see "Using where" in the Extra column of the resultset. But this only ...
1
vote
1answer
2k views

Varchar index - will hashing value make it faster?

I have a VARCHAR(1000) column in a table. It will contain strings that will not be guaranteed to be unique. I have a query that searches this column as part of a WHERE IN clause, the list of values in ...
0
votes
1answer
60 views

Index on a query with order

I have the following table with >1M rows: CREATE TABLE `wishlist_place` ( `id` int(11) NOT NULL AUTO_INCREMENT, `wishlist_id` int(11) DEFAULT NULL, `place_id` int(11) DEFAULT NULL, `city_id` ...
0
votes
1answer
44 views

How to link several tables?

I'm a newbie at Database Designing. What I want to do is perform several actions at once. I have a main users table which holds all the data for a user at a website. Then I have a primary key as the ...
2
votes
2answers
1k views

Slow INSERTs to MEMORY table

I've been trying to work this issue out, been really killing me! We've been experiencing some slow database queries for a Prestashop installation that we host, and a look into the query log revealed ...
1
vote
2answers
309 views

Slow query joining on subqueries with max

I'm creating a report based on user input (i.e. a search tool) that grabs data from eight tables and will output them to the screen. Here are the create table statements. I'm very aware of how badly ...
2
votes
1answer
1k views

How can I make a select statement get blocked?

Hello I am trying to intentionally make a SQL select statement get blocked by another simple SQL delete or update statement, for the purpose of learning. I prefer only InnoDB tables. To prepare the ...
0
votes
0answers
26 views

Should store procedures be cached?

Should store procedures be cached in MySQL? If so, How long it is stay in cache? In my case, When I call one stored procedure first time, it is giving me results in 1 second, after that it gives me ...
0
votes
1answer
2k views

Synchronise data from one table to another & vice-versa using triggers

We are migrating from one database structure to another - very slowly. The system is MySQL. There are two databases. For example's sake, we will call the databases old_db and new_db. Both databases ...
0
votes
1answer
480 views

How to get rows where count() is null?

This is my SQL request : SELECT COUNT( b0_.id ) AS sclr0, d1_.codeLieu AS codeLieu1, d1_.nomLieu AS nomLieu2, d1_.lngLieu AS lngLieu3, d1_.latLieu AS latLieu4, m2_.libelleMention AS ...
1
vote
1answer
138 views

How to “swap” out production tables

I have a MySQL table that holds a few thousand records which go out of date very quickly. A few times a day we run a slow process which needs to pull data from multiple sources and re-populate the ...
0
votes
1answer
246 views

Order of where clause evaluation

If I have a MySQL table which has content from the last 3 months I actually do the following: SELECT SQL_BUFFER_RESULT SQL_CACHE count(id) cnt FROM `mDelivered` where _campaign = 2 and `session` = ...
0
votes
1answer
87 views

Search speed increase

I've heard that some queries like DELETE, or changing the structure of a table can decrease the speed of the search in a table. First: is this true , or not? Second: is there any way to increase ...
1
vote
2answers
8k views

Query WHERE clause and JOIN

I'm having an issue using a WHERE clause and JOIN. SELECT * FROM `CallDetailRecord` WHERE `StartTime` >=1357102799000 AND `StartTime` <=1357880399000 JOIN `CallEvent` ON `EventID` = ...
1
vote
3answers
157 views

Master/Slave without populating tables

So I have some Zabbix instances which are using MySQL for their backends. What I would like to do is, for DR purposes, backup the DBs for each instance using a Master/Slave configuration, with the ...
3
votes
1answer
3k views

Insert Row With Foreign Key Equal to ID - Circular Reference

In MySQL database I have an events table which has a circular reference to itself with the following columns. id (PK) | user_id (FK) | event_id (FK) | title Each new row inserted has a circular ...
1
vote
0answers
50 views

Import - database not found error

When I issue the following command: mysql -u user -ppassword database_name < /path_to_database.sql I get: sh: database_name: not found And it stops processing. I have created database_name ...
6
votes
2answers
301 views

Is there any way to trace optimizer's work in MySQL?

Just like the MEMO structure in SQL Server which is kind of a "paper trail" of steps the optimizer takes in optimizing the query. Is there anything in MySQL through which I can get the information ...
0
votes
2answers
4k views

format of mysql query log

What is the format of the mysql query log? In particular, for lines like: 133 Query commit 133 Query rollback What does '133' stand for? And is it true that each line represents a round ...
0
votes
0answers
289 views

MySQL binary log failed to generate a new one

In my Linux standalone machine, I have set up MySQL on it. In my.cnf, I have the following cofiguration: [mysqld] log-bin=mysql-bin binlog_format=mixed expire_logs_days = 10 max_binlog_size = 100M ...
6
votes
1answer
11k views

What's the most efficient way to batch UPDATE queries in MySQL?

I'm writing an application that needs to flush out a large number of updates to the database for an extended period of time, and I've gotten stuck at how to optimize the query. Currently I'm using ...
0
votes
1answer
205 views

Make rows immutable allow Insert mysql5

I want to make the rows in a Mysql table immutable, once they are inserted I don't want it to be possible to change them. How can I do this? (ideally returning a suitable error message if any change ...
1
vote
2answers
493 views

Why would splitting up a query make it faster, and can I/should I fix this?

The following query is for a web-based game. It sets the isTiedForHighScore column to true for all solutions that tied the score for a given "map." UPDATE solutions INNER JOIN ( SELECT mapID, ...
3
votes
1answer
551 views

How do I prevent updates on an entire table (but allow inserts) in Mysql 5.5?

I want to make the rows in a Mysql table immutable, once they are inserted I don't want it to be possible to change them. How can I do this? This question handles making individual rows immutable, ...
0
votes
1answer
692 views

Do I need client certs for mysql ssl replication?

I'm setting up mysql replication using SSL, and have found two different guides. The first one creates both client and server certs, while the second one only creates server certs. I don't know ...
0
votes
1answer
469 views

optimize big sql query

My query has to return a statistics for example for march containing productname and price and its sidedishes (1:n relation) with the right price of each sidedish and the right tax for each sidedish ...
1
vote
1answer
776 views

“relation does not exist” trying to import mysql dump into postgres

environment: ubuntu 10.04 mysql server 5.1.69 postgres 9.2 Here's the sequence of steps: created a new postgres database, myDatabase executed this command: mysqldump -u root -p ...
0
votes
1answer
1k views

Large INSERTs performance optimization

I have 15 Amazon AWS EC2 t1.micro instances which simultaneously populate Amazon RDS MySQL d2.m2.xlarge database with data using large INSERTs (40000 rows in query). The queries are send ...