MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

2
votes
1answer
2k views

MySQL, optimize and replication

I have two MySQL (MariaDB actually) servers, a master and a slave with standard replication. Two times already in less than a month I've had my slave become unusable. This is what I experienced: ...
2
votes
0answers
19 views

MySQL is too slow [duplicate]

I don't know why mysql is too slow for querying about 39000 records which include inner join with other two tables. I have country, region and city tables as follows: tbl_country CREATE TABLE ...
2
votes
2answers
1k views

Why would increase in innodb_buffer_pool_size slow down MySQL?

5.1.68-cll - MySQL Community Server on CentOS The system has 32GB of RAM. I increased innodb_buffer_pool_size from 10240M to 15360M (10GB -> 15GB). Time taken for a series of identical operations ...
2
votes
1answer
1k views

How to optimize multiple self-JOINs?

I'm looking for advice on either optimizing multiple self-joins, or a better table/DB design. One of the tables looks as follows (relevant cols only): CREATE TABLE IF NOT EXISTS CountryData ( ...
2
votes
0answers
95 views

MySQL Partitioning Performance

I have a MyISAM table with billions of rows that is still causing me problems because the indexes, even after I shortened them as much as possible, do not fit in my 64GB of RAM. I am unable to index ...
2
votes
2answers
183 views

Repeated values in group_concat

I have two tables, first the table food and Second is Activity: INSERT INTO food (`id`, `foodName`) VALUES (1, 'food1'), (2, 'food2'), (3, 'food3'), (4, 'food4'), (5, ...
2
votes
1answer
3k views

Mysql Forcing close of thread 946 user

My mysql box keeping shutting down and up. Below is snippet of the log file. There is quite a number of places I notice this Forcing close of thread 946 user: 130426 12:36:28 [Note] Event Scheduler: ...
2
votes
0answers
133 views

Changing Charset to utf8 for all the fields and tables in mysql

How should I use the mysql and awk to change the charset for all the fields and tables for a specific database? I did not include username or password, so for sure it will not work. Should I run it ...
2
votes
1answer
213 views

Avoiding Multiple Queries when Searching for Records Associated with a Set of Records

So, I am sure I have done something really stupid while designing this, and I'm open to schema changes if they'll really help me out. On to the problem: I have a custom shopping cart system (backed ...
2
votes
1answer
218 views

In MySQL, does the order of the columns in a WHERE clause affect query performance,why? [closed]

I have a query that doesn't use any indexes: SELECT 32, guid, 1, 1, 1, 0, 5 FROM test WHERE level >= 20 AND ( ( fun_GetIndexValue(data, 354) ...
2
votes
1answer
264 views

Bi-directional replication for the same MySQL table

AppA stores/retrieves data from dbA.tableA AppB stores/retrieves data from dbB.tableA tableA definition is the same across these databases. To start with dbB.tableA was copied from dbA.tableA ...
2
votes
1answer
1k views

MySQL - run SELECT statement on another server without defining the table structure

In MySQL I can query information on another server using federated tables, as long as I've defined the same table structure locally. In MS SQL Server, however, I can run any SQL statement against a ...
2
votes
1answer
669 views

MySQL specific database configuration file

In MySQL's configuration file I've globally disabled autocommit as so. [mysqld] autocommit=0 I need to turn MySQL's autocommit on for a specific Ruby on Rails database though. It could be for the ...
2
votes
0answers
78 views

How can one improve the performance of a query that selects on a low cardinality column in MySQL?

I have a "State" column that has a low cardinality (three possible value), on which most of my queries perform an equality selection (WHERE col = 'blah') AFAIK you need something like a bitmap index ...
2
votes
0answers
616 views

MySQL: Improve performance for one row insert into table with unique constraint

I have a table with ~ 40 mil records, with a unique index on the url collumn, of type varchar(255). Now the insert speed is about 30/s, is this expected? how could I improve it? I can't use bulk ...
2
votes
1answer
797 views

Monitor user to query processlist

We have a corporate monitoring system based in Zabbix. Its MySQL monitoring is quite basic, so I want to add more info. To do so, I've done a script that retrieves this info from MySQL and sends it to ...
2
votes
1answer
340 views

Does MySQL have problems with nested insert like with subqueries in where?

Query 1: INSERT `personal`.`locations` SELECT DISTINCT `s`.* FROM `references` `t` JOIN `locations` `s` ON `first_id` = `s`.`id` WHERE `lat` >= 37.3 AND `lat` <= ...
2
votes
1answer
164 views

Can I normalize my experimental data table further?

I am attempting to get a MySQL database to 3NF, but I'm unsure as to how to get there. I'm new to database design, so please correct me if my understanding is incorrect. I have 6 columns: ...
2
votes
2answers
2k views

How do I use MySQL Workbench to edit an existing, in-production database?

This is basically a repost of this question, which didn't give a satisfactory answer. I'm finishing up the design of a database using an EER diagram. I now want to forward engineer and import the ...
2
votes
3answers
528 views

MySQL replication - some values are not being replicated

MySQL Replication issues I might sound clue less here as i am :) , I am having problem with mysql replication, i.e. replication seems to be missing in some random occasion. My structure looks like: ...
2
votes
2answers
437 views

MySQL index on column comparison

How do you go about setting an index to be used for a column comparison like WHERE col1 > col2? col1 and col2 are of DATETIME type.
2
votes
0answers
344 views

Dynamic MySQL partitioning based on UnixTime

My DB design includes multiple MYISAM tables with measurements collected online, Each row record contains auto-incremented id, some data and an integer representing unixtime. I am designing an aging ...
2
votes
1answer
450 views

Analysis of MySQL database in real time Amazon RDS similar to phpMyAdmin

I'm using a single Amazon RDS instance (medium) to power several WordPress websites running on a single Amazon EC2 instance. I used to have my databases on another EC2 instance and installed ...
2
votes
1answer
119 views

MySQL Start/Stop

Could anyone explain to me what happens during MySQL startup and shutdown? And i would like to know about the safe startup/shutdown process.
2
votes
1answer
3k views

I have to update all the rows of a Column in a table. Here is my query

So My tables are People: People_name(VARCHAR 100), People_id (INT, PRIMARY KEY AUTO INCREMENT) Tracks: Track_id(INT PRIMARY KEY), People_id_references (int ,Foreign key references ...
2
votes
0answers
711 views

SQL set profiling

I'm setting up an automatic database backup for a website. When I try to do a backup it comes up with the error" MySQL error: The 'SHOW PROFILE' feature is disabled; you need MySQL built with ...
2
votes
1answer
140 views

Ranking System with Different Categories

I want to create a system where the user can be ranked by several different factors. For example, the user competes in the following categories Wins Points I need to know what the position of the ...
2
votes
1answer
211 views

innobackupex is failing while exporting backup

I am using perl script which is working fine in one of my other box which used to take individual schema backups & finally take all schema together using percona innobackupex. I have following ...
2
votes
1answer
9k views

mysqldump: Error: 'Can't create/write to file

Can't create temporary files on /var/tmp. mysqldump: Error: 'Can't create/write to file '/var/tmp/mysql.k6NKGk/#sql_5dc_2.MAI' (Errcode: 2)' when trying to dump tablespaces On my OS. id mysql ...
2
votes
0answers
401 views

MySQL database structure: more columns or more rows [closed]

I collect how people tag topics with categories in table like: ID | topic_id | votes_Category_1 | votes_Category_2 |.......... | votes_Category_12 This table holds 1 row per topic and dynamically ...
2
votes
0answers
36 views

Manually drop table from MySQL database [duplicate]

Possible Duplicate: Inserting query result to another table hangs on “Copying to temp table on disk” on MySQL To look at the background my problem; I have run a query to import ...
2
votes
2answers
217 views

Implementation Idea for login structure (login id with two different tables)

I am creating a sample web application for a business and I am stuck at the login implementation. There is supposed to be two different users (login types?). I will have employees and customers. I ...
2
votes
0answers
462 views

Incorrect data when we using the CONVERT_TZ function on DST boundaries

We have a problem in converting time zone from "America/New_York" to "UTC". This problem occurs only on times representing 2.00 AM during the daylight saving. Timezone conversion with correct output ...
2
votes
1answer
3k views

How do I completely remove MySQL from a 64-bit windows 7 machine?

I'm new to MySQL and struggling to find a version and workbench which is stable on my 64 bit Windows 7 machine. I've decided to attempt to completely remove MySQL from my machine and to restart the ...
2
votes
0answers
3k views

'Access Denied' in MySQL on AWS RDS

I'm trying to use an ETL (in my current case, the free version of Talend). I've successfully set up a MySQL on Amazon RDS, logged in through the MySQL Workbench for Mac, and uploaded my company's ...
2
votes
1answer
85 views

Replicate from master-master setup

I have MySQL Master-Master replication on 2 servers. I want to replicate to another slave server. When I configure the slave to replicate from master1, only data added on master1 is replicated. ...
2
votes
0answers
77 views

MySQL Partioning: Is there a performance tradeoff between number of partitions and size of each partition?

I have a large table (several 100 million rows) that I would like to efficiently partition. My question is whether there is a tradeoff between partition size and number of partitions. As far as I ...
2
votes
0answers
945 views

MySQL Server hitting 100% unexpectedly (Amazon AWS RDS)

We've been struggling with this issue for months. This week we upped our RDS instance to the highest performing instance and although the occurrences have reduced, we're still seeing our database ...
2
votes
0answers
7k views

Alternative OpenSource/free to MySQL Cluster Manager Client? [closed]

I need to deploy a MySQL cluster but initially no bucks for the MySQL Cluster Manager Client. It's at least $5k and I actually think in my case it will go way over that. I looked a lot on Google (a ...
2
votes
2answers
166 views

Beginner problems administering users on mysql

Very new MySQL user here. Two questions for the community. I seem to have created an account that I can't delete/drop. I can't seem to get MySQL to actually require newly created accounts to require ...
2
votes
0answers
158 views

How to TRIM and CONCAT at the same time [closed]

I have a query am working on where I want to Trim a leading character and the concat other three characters my query is as follows SELECT CONCAT ('255', TRIM(LEADING '0' FROM `mobile`) FROM ...
2
votes
0answers
1k views

MySQL re-install fails under Mountain Lion

I have problems to reinstall MySQL on Mac OS X after it was running on my system. I uninstalled it because the PreferencePane wasn't working as expected, using the procedure described here: ...
2
votes
2answers
943 views

Why are parallel writes in MySQL as slow as sequential writes?

I have many MySQL write-only processes. They all go to the same MySQL master. I run them sequentially to get a baseline time. I basically chain together several mysql command-line clients, ...
2
votes
1answer
264 views

Best approach to design a database model for this situation? Clients and records

We need to store the following information for each client: Three types of record per file, for example (obviously, with real data): HDR2007022399999900000000000Soft/XX 0.0 XXX ...
2
votes
0answers
674 views

Index usage for database server

How can i fix the Index usage on one of our database server? It says from our Nagios control panel that: Service: DB Index Usage Host: MySQL Cluster Address: ( our IP ) State: CRITICAL Additional ...
2
votes
1answer
378 views

Change the same field from all the tables from a particular database

Suppose I have a database sitedb and in that I have 20 tables and a primary key(say uid) for one table is used in other tables as dependencies. uid is of type int with length 10. Lets say that the ...
2
votes
0answers
95 views

Can't connect to MySQL Cluster Storage (data) nodes

I've deployed a MySQL Cluster with a few nodes, and I want to access the storage nodes directly from another machine using NDBAPI. When I tried to connect from the same host it worked, but when I try ...
2
votes
1answer
106 views

mysqladmin user account not secure?

I still playing with my own DB trying to learn and saw this: I could change the root password without any problem at all... If I'm in the server I can create an algorithm to start testing password ...
2
votes
0answers
307 views

MYSQL & Access ODBC

I am trying to connect mysql with access, i am settings up the ODBC correctly from the data sources, however i get a driver error. I am using access 2010 and the driver version of the ODBC conector ...
2
votes
4answers
218 views

MySql Database solution for specific web app problem

Well, I need to create a comment system for posts that people add to my web app. This web app is just like facebook or myspace posts. Users add posts and people can comment on them. But the problem is ...