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

1
vote
1answer
2k views

Mysql - Load data infile query for Particular Values

I'm developing a Java swing application with back end Database MySQL . I've a screen to upload my datas to the Database using the Query like LOAD DATA local INFILE 'D:\\TEMP\\import\\jan_jun2013....
5
votes
1answer
6k views

mysqladmin not taking inline password

I am trying to set a cron job for taking backup from my slave machine. So I need to stop the slave I issued a command mysqladmin --user=root --password=test_pass stop-slave But it is throwing ...
1
vote
0answers
220 views

mysql.sock missing

I started my MYSQL database this morning but failed. And I tried all means I can think of: chown -R mysql.mysql /var/lib/mysql chmod g+w /var/run/mysqld chgrp mysql /var/run/mysqld chmod -R 755 /var/...
2
votes
1answer
892 views

MySQL Performance Issues

Over the past 3 weeks we have been having bottle-necking issues at our MySQL server. I have been using MonYOG to watch the processlist in an attempt to get a grasp on the issues. We are aware that ...
2
votes
1answer
16k views

Enabling LOAD DATA LOCAL INFILE in mysql

LOAD DATA LOCAL INFILE is not enabled by default. Normally, it should be enabled by placing local-infile=1 in my.cnf. But it does not work for all installations. In my experience, it worked for Debian ...
2
votes
3answers
708 views

xtrabackup prepare - after dump or before restore?

i have doubts about xtrabackup --apply-log. is there any difference when use it? should it be done right after dump or before restore (even few months later)? seems stupid, but maybe i missing ...
4
votes
1answer
6k views

Recover the disk space after deleting rows from table

I deleted millions of rows from one of the table with innodb engine. I know that I need to run optimize table to recover the space. But running that will cause lock on the table, which will affect the ...
1
vote
3answers
250 views

in-memory SQL database system [closed]

In an application, I am using mysql's TEMPORARY TABLE. Since this application does not need permanent tables, I wonder if I can find an alternative RDMS designed for memory tables. I hope to find an ...
6
votes
1answer
48k views

MySQL 5.6 DateTime Incorrect datetime value: '2013-08-25T17:00:00+00:00' with Error Code 1292

I'm using MySQL 5.6 and I have a program that runs the following SQL statement: UPDATE m_table SET s_time = '2013-08-25T17:00:00+00:00' WHERE id = '123' against my database. Unforutnately, I get the ...
3
votes
1answer
544 views

MySQL / Fusion IO Configuration Question

I need a little configuration optimization. I think what I have is pretty good but feel like there's still room for improvement. Current Config [mysqld] user=mysql datadir=/var/lib/mysql socket=/var/...
1
vote
0answers
79 views

MySQL probelm in port: 3306

All websites on my VPS getting very slow, after dedcting the problem found that there are a line take 99.9% of the CPU: /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql \ --plugin-dir=/usr/...
1
vote
0answers
117 views

Mysql encrypted database contents by proxy?

Forive the amateur nature of this question. I work with Wordpress, often transforming the vanilla flavour into custom made versions for clients. Increasingly clients require more and more marketing ...
2
votes
1answer
287 views

Sphinx: Indexer causes MySQL to hang

Not sure what's going on. I run indexer --all --rotate When it finishes mysql hangs and not accepting new connections. from my observation as soon as indexer finishes, all update,insert,delete ...
1
vote
0answers
25 views

How do I configure MySQL to store specific entire InnoDB tables on a different drive in Windows ( Server )?

After skimming the MySQL ( 5.5 ) docs on Partitioning, that seems to deal with breaking parts of the same table off onto other drives. I'm struggling to find information on how I pinpoint an ...
0
votes
0answers
218 views

Is STRICT_TRANS_TABLES required for replication?

Is STRICT_TRANS_TABLES required for replication? Particularly, I need to know about it for Percona Cluster.
3
votes
1answer
4k views

Finding swap causes of MySQL

In my centos 6.3 server I have a MySQL 5.5.33 database. It has 17 tables (15 InnoDB, 2 MyISAM) and total records 6.7M rows. I refactored my schemas and added indexes for my slow logs. My average query ...
5
votes
1answer
9k views

Get result of joining multiple tables as one row

I have these 2 tables: table1: id | name --------- 1 | john 2 | jack table2: id | profile_id | institution ----------------------------- 1 | 1 | SFU 2 | 1 | UBC 3 | 2 ...
0
votes
1answer
964 views

Problems migrate database from 4 to 5.5 mysql

I have a server with 4.0 mysql version innodb engine. I need to migrate to other server with 5.5 version (linux) ok I dump the full database using mysqldump, when i try to restore on the 5.5 ...
3
votes
1answer
225 views

Mysql Fragmentation. How bad is too bad?

I ran a table status for my companies production site and its showing close to around '49085939712' for just about every table in my database and we have around 400 tables. I am not a DB ...
0
votes
1answer
554 views

Restrict access to rows by usename

In my database i store data of different users (e.g. addresses or invoices) in the corresponding tables. I want to make sure that a logged in user only has access to it's own data in the tables (so ...
2
votes
1answer
89 views

Correct database solution?

So I work for a smaller company and currently we need to do our data processing with a desktop solution. We're running into memory limitations and the overall solution in most people's eyes (non-...
1
vote
1answer
4k views

mysql replication for certain dababases

I am new to mysql and I have a query in regards to mysql replication. Currently, I have a master database that replicates to four slave servers at the moment. I would need to set up a few ...
3
votes
3answers
249 views

Find numbers not used in a column

How can I find in a column filled with numbers, the ones that are not used. Table_A: 'id' int(11) unsigned NOT NULL AUTO_INCREMENT, 'client_code' int(4) unsigned NOT NULL, 'client_name' ...
2
votes
1answer
4k views

What are the ports and directionality of the TCP connections used in MySQL replication?

I wanted to know how replication works in mysql. Both slave and master have mysql server running say on port 3306. Slave acts like a client and master acts like a server. Does slave always bind to ...
1
vote
1answer
640 views

Promote a slave to master; fall back to former master if new master has issues?

I've been managing mysql db's for some years now, and have gone through several iterations of promoting a slave to take over the role of its former master. My understanding has always been that after ...
3
votes
1answer
33k views

How to structure IF condition in MySQL trigger?

I am trying to write a MySQL trigger. I have two tables like this: Table A------------------------------Table B order_id--------sku---------------order_id----order_#----sku_copy 568---------AAA-...
0
votes
1answer
153 views

Two types of data, so two type of databases?

For a social network site, I need to propose a DB. The application is written in Java & will be hosted on VPS(s) initially. Broadly classified there is two type of data to be stored at backend: ...
1
vote
1answer
1k views

Drop privilege to tables only, Not to database

I created a user with drop privilege to a database. How can I create a user who has the privilege to drop tables of a specified database, but not database.
1
vote
1answer
170 views

MySQL Function Error

In an inherited database I have two tables of current interest, records and logins. login tells me the location of the user and where and when his login occured (in and out times included). The fun ...
2
votes
2answers
767 views

Setting innodb_buffer_pool_size in for Mysql

We have a dedicated DB box with with 8 GB of RAM and running Redhat linux 64 bit OS. Our MySQL Data + Index size almost 7.8 GB. About setting innodb_buffer_pool_size, I have read 2 suggestions Set ...
0
votes
1answer
1k views

Define a column as MD5 sum of other columns in MySql

Suppose I have following table - id name -- ---- 1 kshitiz Is it possible to define another column that is the md5 sum (or some other hash) of values in the previous two columns? id ...
2
votes
1answer
464 views

Mysql foreign key cascade delete

Does mysql have a way to delete all sub links if a row was deleted from a "parent" table? Example table setup is below, the issue I am trying to avoid is having to maintain a script that deletes the ...
0
votes
2answers
62 views

Guaranteeing a FK relationship through multiple tables

I'm using MySQL / InnoDB, and using foreign keys to preserve relationships across tables. In the following scenaro (depicted below), a 'manager' is associated with a 'recordLabel', and an 'artist' is ...
1
vote
1answer
6k views

SQL Limit + count group?

I'm tracking all the visitors of my site using this table: Visitors table In my admin panel, I have an overview of which countries visited my site, where they came from, ..., but because over 20+ ...
0
votes
1answer
137 views

Use surrogate or natural PK if a row is uniquely identified by two or more columns?

I am having a hard time choosing between natural and surrogate PK for my database table. The database is for a ranking system for an MOBA game. Here is more or less what I would do if I were to go ...
2
votes
1answer
3k views

“Use Database” command inside a stored procedure

I would like to dynamically use a database inside a stored procedure but the use database_name command is not supported inside procedures. What alternatives are there?
2
votes
1answer
821 views

Subselect dependent on first select value

I wish to do a select of all data matching a particular id. I would also like to return a selection of all data matching the alt_id field in the first select. This example works fine for the main ...
0
votes
1answer
218 views

How does MySQL or PostgreSQL deal with multi-column indexes in ActiveRecord?

I'm creating indexes for my models right now and I want to know how MySQL and PostgreSQL deal with an index with more than 1 column like: add_index :users, [:username, :created_at] That should ...
0
votes
1answer
144 views

MySQL Cluster - strange syntax error

I have MySQL Cluster instance and want one column (BLOB) to be stored on disk instead of memory. I tried to follow this tutorial: http://mikaelronstrom.blogspot.com/2006/02/how-to-define-table-that-...
3
votes
1answer
6k views

Merge multiple tables in a database

I have a MySQL database with table names like 1,2,3... upto 1000 Each tables have 2000 rows and has the same structure. This is how all my 2000 table's structure look like id,url,title,content id ...
0
votes
0answers
1k views

MYSQL 5.6.12 Lock wait timeout exceeded, yet no transactions\locks currently taking place?

On a development database with no other users other than myself, I'm getting the following error #1205 - Lock wait timeout exceeded; try restarting transaction when trying to run a simple alter ...
0
votes
1answer
232 views

Whether or not to create separate tables

I am working on a project, where I need to store the information related to A and B, the A and B both have identical columns.There might be thousands of As and Bs. Should I use "type" column to ...
1
vote
1answer
59 views

MySQL Grouping Order

Is it possible to force the order in which a result set is grouped? I have a table with multiple languages in it, and I'm doing a GROUP_CONCAT to get a comma separated list of the product name in each ...
1
vote
1answer
9k views

Dynamic SQL is not allowed error for creating function in MySQL

According to this question and answer of Angelo I create a MySQL function as below: DELIMITER $$ CREATE FUNCTION hierarchy_connect_by_parent_eq_prior_id(value INT, reqdTable VARCHAR(50)) RETURNS INT ...
2
votes
2answers
207 views

MySQL - unique constraints design

I am using MySQL and looking to solve a problem with relational data. I am not sure it is possible but wanted to ask the community. Please consider the diagram: I would like to add a unique ...
4
votes
2answers
937 views

Equivalents to `ora_rowscn` in other databases?

Do other databases other than Oracle have ora_rowscn (http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns007.htm) equivalents, or do you have to implement them yourself in your fields, ...
1
vote
1answer
5k views

MySQL: ERROR 126 (HY000): Incorrect key file for table + slow logical backups

I've got '/tmp' directory mounted with 'tmpfs' and for some reason this is causing the following error: mysql> SELECT DISTINCT table_schema FROM information_schema.tables WHERE table_schema NOT IN ...
0
votes
0answers
112 views

MySQL Access Denied, tried a few things, pulling hair

I'm trying to get to know Django (my first attempts at a framework, or any backend work for that matter), and I'm seriously stumped by MySQL, and SQL in general. I'm trying to create a new database ...
0
votes
1answer
133 views

Renaming a production database

We have a production database that we have identified needs some maintenance as a colleague leaves - some column deletion and renaming, and a couple of table renames We're trying to find the best (...
2
votes
1answer
511 views

mysqldump―output details

I am about to write a little program to process database dump files created by mysqldump and I would like to make sure that my expectations about the »anatomy« of mysqldump's output are right. So ...