All versions of MySQL (not Microsoft SQL Server). Please also add a version-specific tag like mysql-5.7 if that is relevant to the question.

learn more… | top users | synonyms

2
votes
1answer
671 views

3 Databases, Best Replication Setup?

We have 3 DB servers, two of them i.e. db1, db2, are located in the same data center. The other one i.e. drdb, is located in a different site. Currently, the replication setup for these servers is of ...
2
votes
2answers
426 views

Does MySQL have information_schema.VIEW_COLUMN_USAGE or an equivalent?

SQL Server has a an information_schema view, 'VIEW_COLUMN_USAGE', which you can use to determine the actual/base tables that the columns in a view reside in. I'm looking for the equivalent in MySQL - ...
6
votes
2answers
1k views

Fetch the newest row grouped by a column in MySQL

My problem seems like it should have a much simpler solution than what I have come up with. Starting with this data set: log_table +--------+-----------+------------------+---------+ | log_id | ...
2
votes
1answer
825 views

Populating an empty table using loops, conditionals, and accessing multiple tables

This question pertains to a MySQL database, although, in the end, this may be database neutral. I am trying to populate an empty table (let's call it NewTable) that I created. The data population is ...
1
vote
1answer
268 views

MySql - selecting into file and re-loading it with special characters

I have a MySql table called twitter_statuses which as the title suggests holds twitter statuses. here is the table structure. this table contains all sort of characters in the text column, and some ...
0
votes
1answer
69 views

Mysql restore all data from Master

MySQl Slave replication server got crashed and I am missing around 60 tables, some table structure and data which present in master and by DB size is more than 25Gb, How I Can restore to resolve ...
2
votes
2answers
177 views

MySQL Server Config, Small Timeframe, Extreme Usage-Scenario

I want to configure a MySQL server for the following scenario. A large amount of users will need to sign up for a university course selecting individual "timeslots". The signup page will open at a ...
2
votes
2answers
3k views

MySQL order by - one column conditional on another column

In MySQL I have a table (simplified for this question) CREATE TABLE `projects` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) NOT NULL, `priority` int(11) DEFAULT '1000000', ...
2
votes
1answer
3k views

MySQL: Unique constraint on large column

I am trying to create an InnoDB table that contains a VARCHAR column that can hold up to 3071 characters. I would like to enforce a UNIQUE constraint on the data of this column. MySQL appears to ...
1
vote
1answer
572 views

Optimizing User Defined Custom Columns

I am building a web based app that has the potential to have very high traffic and collect a lot of data. Most of this traffic will be utilizing the database. Database is MySQL InnoDB. I want to give ...
1
vote
1answer
1k views

Restoring MySQL database with db dump and binlogs

I have a database running on our production web server that I have set up as a master with a single slave in another datacenter. When reading through the MySQL replication setup instructions, they ...
1
vote
2answers
1k views

How can Stored Procedures and Triggers be Automatically Loaded When MySQL Starts

Is there any straightforward method of doing as the questions asks? We are using MySQL 5.5 & Linux.
6
votes
2answers
4k views

Scaling Percona datacenters: setup and replication

Since our startup has taken off nicely we are now meeting with some of those issues that you always assumed would NEVER effect you. We have already scaled alot of our application stack: we offloaded ...
2
votes
1answer
683 views

MySQL Unique Index Keylength

I am hashing keys from one table that provides a 256 bit (64 char length) alphanumeric string that I intend to use in a second table as a foreign key. The engine of choice here is InnoDB. There are ...
1
vote
2answers
142 views

Will changing server_id on master break replication?

I have few MySql absolutely isolated trees, each one of them has one master and few slaves. For purposes of automation I need to set the same server_id to all masters (this will simplify the usage ...
3
votes
2answers
2k views

Blocking drop database command

I created a MySQL database and created a new user dedicated to that database with all permissions. The new user then proceeded of dropping the database in addition to all tables. I need to block that ...
1
vote
2answers
285 views

SQL query based on foreign key relationship

I have two tables, a Project and an Action: Project has the following fields: project_name Action has the following fields: action_name project status There is a one-to-many relationship ...
0
votes
1answer
2k views

Partitioning not working need primary key?

We are trying some partitioning as below. ALTER TABLE tblreceipt PARTITION BY RANGE (MONTH(receiptDate)) ( PARTITION p1 VALUES LESS THAN (TO_DAYS('2012-10-20')), PARTITION p2 VALUES LESS THAN ...
3
votes
1answer
450 views

How does R-Tree outperform B-Tree for simple check if a point is within a rectangle

For things like find nearest neighbors, I can undertand that R=Tree can outperform B-Tree. R-Tree can kick out more obviously false points. However, for simple check of a point in a rectangle, it's ...
1
vote
0answers
67 views

Partition guides and possible solutions?

We have a table called tblLink which is the main table and have its primary key in many other tables as shown below. The issue now we want to keep only the latest 3 months data and the rest should be ...
-3
votes
1answer
141 views

percona innobackup failed

I'm trying to get percona innobackup but I'm struggling with this since 4hrs. Tried all possible ways from many forums. interms of changing my.cnf, parsing arguments but still not able to get this ...
2
votes
0answers
521 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 ...
0
votes
1answer
352 views

Most efficient way to structure segmented data

I am faced with a set of data which I am currently unsure on how to store efficiently. The data in question relates to a calendar which stores information on two different categories of event which ...
6
votes
1answer
4k views

Disable console output when executing a SQL file using MySQL source command

I begin by launching the MySQL command line tool: mysql --user=myusername --password=mypassword --silent --force -b The last -b option is used to disable beep on error. Then I choose a database: ...
4
votes
1answer
301 views

MySQL table design of logging tables

I'm not a DBA by any means, but I have a basic understanding of some of the underlying principles of database optomization. Currently we have a table that logs session data, it writes about ...
1
vote
1answer
1k views

`show procedure status` doesn't list all of the procedures?

mysql-server-5.5.28-1.el5.remi I'm pulling my hair with this problem: only EMS SQL Manager can list all of my store procedures (1763). mysql client, mysql-workbench, Navicat, ... only see some of ...
1
vote
1answer
143 views

Slow-running query on a new VM

The below query is taking a lot of time, more than an hour. Is there anything I could to to tune it, server-wise or query-wise? Its just 23000 records. EXPLAIN SELECT bc.charge , ...
1
vote
1answer
1k views

calculate the difference between two column aliases in a SELECT statement?

I'm not sure how to do the last part of my query, which is doing calculations directly with the aliases: (SUM(task_avg - minutes) AS difference. I don't think I can do it as easy as I'd like, but ...
0
votes
1answer
169 views

Handling error of foreign key

How to set a FK value NULL upon receiving error for foreign key constraint? Consider a simple table a CREATE TABLE test ( id int(11) NOT NULL AUTO_INCREMENT ex_key int(11), FOREIGN ...
4
votes
1answer
8k views

What exactly are iblog files in mysql

I would like to understand these ibdata files as these play vital role in the crash recovery procedure. I could not find proper resources over the web for this.
1
vote
0answers
133 views

MySQL: Fixing Unique key constraint violation

I have a database with an unique key constraint. When I import this database from a dump created by mysqldump I get '14698-3' for key 'UNQ_CATALOGS EARCH_FULLTEXT_PRODUCT_ID_STORE_ID' But when I ...
0
votes
2answers
1k views

how to estimate rebuild index on 140 million records?

My existing project has data of about 140million records in Contentq table. And I have planned to rotate the table just incase not to face any load on the table. So I have planned to rotate data older ...
1
vote
1answer
2k views

Slow complex query with group/order

I have a problem with a very important query in my application. It is very slow when adding group and order to it. So the full query is: SELECT m.id, m.title, m.title_text, ...
0
votes
1answer
371 views

How to avoid certain characters in MySQL full text search?

I have a column named parcel_number of varchar type. It contains values like ab-cd-ef and uvwxyz. A search for abcdef or uv-wx-yz does not return those records. How should I write the query to meet ...
3
votes
1answer
566 views

Model with variable number of “properties” of different types.

I would like to have a table of microscope slides, where on top of the basic id, name,date_created would each slide also have a number of editable "properties". Imagine one slide having ...
1
vote
1answer
215 views

MySQL duplicate data entry issue

I know this issue can be solved using unique key constraint on column and some insert command variation. But I exactly want to know that at last why the following approach goes wrong sometimes and a ...
3
votes
0answers
127 views

Once in a while synchronization of the independently updated mysql databases problem

I have a production database and a test database running on MySQL with MyISAM and InnoDB tables, which are updated independently. The Production database is updated by system and sometimes by a user. ...
0
votes
1answer
8k views

PHPMyAdmin incredibly slow [closed]

Does anyone else's phpmyadmin take an incredibly long time to load? like 68 seconds after i click a table it finally gets there...! its also very slow when i click the "add data from text file into ...
0
votes
1answer
2k views

How can I enable my-huge file on windows server?

I installed Windows Server 2008 and MySQL 5.5. Now I need configure MySQL for huge InnoDB tables and a lot of connections. I see that on my Windows Server, there is a my-huge file(configs files that ...
1
vote
1answer
606 views

replicate master/slave without disturbing master for dump

Is there a way to replicate master/slave without disturbing master for dump like mysqldump or cold copy of data files? I'm ok to bring down the instance only once for master to enable log-bin. But ...
5
votes
1answer
4k views

MySQL INNODB SELECT query hanging on copying to tmp table

I have a query that is hanging (seemingly) randomly on a specific query. Most of my website users do not have a problem when this query runs but a few users are having timeout issues on the website ...
3
votes
1answer
972 views

Robust Mysql failover using free tools [closed]

I am looking for a free solution to set up Mysql in an active/passive configuration, in order to provide high-availability. In particular, a Bugzilla installation, and MediaWiki needs to stay alive ...
5
votes
2answers
1k views

How to determine which SQL Server user or database overuses server resources

I have a Microsoft SQL Server instance on my server with more than 100 databases and users. Sometimes, the SQL Server process use 100% of server CPU and I want to know which query/user/database is ...
2
votes
1answer
110 views

Which of the following data duplication options across shards is recommended?

High performance mysql book suggests that for sharding a blog application, one may want to put comments data across 2 shards: first, on the shard of a person posting comment, and on the shard where ...
8
votes
3answers
2k views

MySQL LDAP authentication

Is LDAP authentication and authorization in MySQL possible? What are the requirements (MySQL version, third-party software etc.)? How it's organized? Thanks.
1
vote
1answer
2k views

MySQL '%' hostname wildcard

I've used the following to grant all privileges on mydb to myusername: GRANT ALL PRIVILEGES ON mydb.* to myusername @'%' IDENTIFIED BY 'mypassword'; I thought that the % is a wildcard for all ...
3
votes
1answer
5k views

error 1118: row size too large. utf8 innodb

I have tried to find a solution guys, and I am guessing that someone has met this before. But I cannot get hits down below 5000... most of which don't match my question. Using MySQL 5.6 - windows ...
3
votes
2answers
898 views

mysqldump fails to dump database

I m using following sql query for creating db backup mysqldump -u[username] -p[password] -h[hostname] [database] | gzip > [backup file path] This generates zipped .sql file in path specified but ...
5
votes
2answers
4k views

How can you stop MySQL slave from replicating changes to the 'mysql' database?

I have my slave set to not replicate the 'mysql' database as described in this SHOW SLAVE STATUS\G; Slave_IO_State: Waiting for master to send event Master_Host: 127.0.0.1 ...
1
vote
4answers
735 views

database sharding on mysql

I need to do database sharding(horizontal) based on MySQL. My database schema is as following: I have tables A,B,C A,B is Global(which need not to be sharding) C have huge data so it need to be ...