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

7
votes
2answers
18k views

Server requested authentication method unknown to the client [mysql_old_password]

Warning: PDO::__construct(): The server requested authentication method unknown to the client [mysql_old_password] SQLSTATE[HY000] [2054] The server requested authentication method unknown to the ...
0
votes
0answers
456 views

MySQL - Problems caused by SQL Security definer or invoker

I have a view used to be defined as: > **CREATE > ALGORITHM = UNDEFINED > DEFINER = `user1`@`localhost` > SQL SECURITY DEFINER VIEW `schema1`.`view1` AS > select ...
0
votes
1answer
2k views

MySQL returning only 1 row, multiple entries in the database

I am creating a list of all publications for employees, and I am currently stuck on a query. It runs with no syntactical errors, but it only returns one row. The ERD for the relevant tables is as ...
-1
votes
1answer
3k views

Use variables in filename with INTO OUTFILE (timestamped filename)

SELECT * FROM project INTO OUTFILE 'C:\desktop\[timestamped filename like 01/05/2014.csv]'; Can anyone help me out?
1
vote
1answer
10k views

MySQL SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded

I have database session enable, when website got thousands of visitor. It gives me error [30-Apr-2014 04:27:12 America/Denver] PHP Fatal error: Uncaught exception 'PDOException' with message ...
2
votes
2answers
22 views

Cause of delay after use `$database`

For a certain project, it seems like the command use <database>; takes an extraordinarily long time (often a minute or more). (This seems to be the case across a couple of different instances on ...
1
vote
1answer
386 views

RDS MySQL instance is unavailable, probably during binlog rotation

One of my customers uses a few EC2 linuxes with PHP application, connected to a standalone MySQL RDS. He reported application outage. During my investigation I found that this outage happened ...
2
votes
1answer
969 views

InnoDB engine is not loading after tuning parameters

After installing MySQL-5.1.73, I placed default my-huge.cnf into /etc/my.cnf and started the mysql service. It was running without any issue. Now, I tunned some parameters in /etc/my.cnf file. Below ...
1
vote
2answers
750 views

Strange behaviour with MySQL 5.1 TO_DAYS partitioning schema

I have this table definition: CREATE TABLE `va_ventas` ( `ID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, `Date` DATE NOT NULL, `Time` TIME NULL DEFAULT NULL, `Country` VARCHAR(3) NULL DEFAULT NULL, ...
0
votes
1answer
57 views

Will MySQL redirect a read request to the master after a certain time? [closed]

I can't find what variable controls this or any information about the time limit. Thanks!
2
votes
1answer
65 views

How to calculate the space I need for the indexes?

CREATE TABLE info( home CHAR(10) CHARACTER SET latin1 NOT NULL, id TINYINT UNSIGNED NOT NULL, galaxy TINYINT UNSIGNED NOT NULL, galaxy_pos INT UNSIGNED NOT NULL, events TEXT NOT NULL, UNIQUE ...
-1
votes
1answer
291 views

Why Do I Still Get a Duplicate Entry Error When There is No Duplicate Entry?

I am trying to insert this data into my database: https://docs.google.com/spreadsheet/ccc?key=0AjQflI82acM3dFI1Mk04dWlYSFJtM0hsZ0g4ZlExMWc&usp=sharing I receive the following error: 1062 - ...
1
vote
1answer
179 views

How to find the length of multiple column index in MySQL

Is there a way to find out what is the length in bytes for the unique index (NKU) key created through the following command: CREATE TABLE test_ ( testId bigint(20) NOT NULL, name varchar(75) ...
0
votes
1answer
86 views

what is the best configuration for innodb_flush_log_at_trx_commit?

There are 3 different configuration is available for innodb_flush_log_at_trx_commit innodb_flush_log_at_trx_commit = 0 innodb_flush_log_at_trx_commit = 1 innodb_flush_log_at_trx_commit = 2 Which ...
0
votes
1answer
82 views

How does partitioning and the lack of a primary key affect ON DUPLICATE KEY UPDATE

Consider a table with the following columns: phone BIGINT NOT NULL PRIMARY KEY, name VARCHAR(64), state VARCHAR(64) We would like to partition the table on state, however that would require ...
3
votes
1answer
7k views

Deleting & Reclaiming space from InnoDB table

I have a 700GB InnoDB table which I'm not writing any more data to (only reading). I would like to delete the older data it holds and reclaim that disk space (as I'm running out of it). The delete ...
3
votes
2answers
4k views

MySQL COUNT Query taking too long time to execute

I have below query that takes 7 secs to run SELECT COUNT(*) AS `count` FROM `yuldi`.`businesses` AS `Business` LEFT JOIN `yuldi`.`businesses_categories` AS `BusinessesCategory` ON ...
-1
votes
1answer
343 views

Problem with intersection table

I seem to have came to a problem which I am having trouble solving, possibly because I have misunderstood intersections properly(or perhaps I shouldn't even use intersections for the goal). If we ...
-2
votes
0answers
470 views

How to make make my query run fast? [closed]

How to make this query run faster? Table contains over 2 million rows of data and takes around 30 to 120 seconds to process the query. SELECT INET_NTOA(blocks.startIpNum), ...
-5
votes
1answer
88 views

“general_logs” is not being written [closed]

I wonder why my data inside my "mysql" table "general_log" is not writing more record, stock given the last day was yesterday 04/27/2014. My my.cnf file is as follows: [mysqld] ...
0
votes
1answer
1k views

MySQL (on Windows) general log too large to rotate properly?

First off, I'm not sure if this should go to Server Fault or Database Administrators. It could be a Windows Server 2008R2 problem. But I am asking here first in the chance that someone else running ...
0
votes
2answers
910 views

How to preserve ID generated from a PRIMARY KEY when moving data into a new table?

I have a large table (~4 million rows, ~100 fields) which I need to split into a few smaller tables by type so it doesn't become unmanageable as it grows. Each entry has an ID which is an ...
3
votes
1answer
1k views

Mysql 5.5 InnoDB growing CPU - Going crazy

Good morning, I'm currently facing a problem where CPU usage keeps going up until the server crash. I've tried multiple best practices proposed in this forum and on Percona, reinstalled the server ...
1
vote
2answers
404 views

Nested query efficiency in MySQL

I have a table with several attributes like this: id id2 attribute -------------------------------- 1 100 blue 2 100 red 3 100 green 4 100 white 5 102 blue 6 102 green 7 102 ...
2
votes
1answer
179 views

Moving data from an old table into a newly created table with different field names / number of fields

I've been handed over management of a database, and I have had very little exposure to MySQL (or databases in general) so far. I would like to do two things, both of which are (I expect) fairly ...
3
votes
2answers
2k views

MySQL Full Text search increase relevance for exact matches?

I have a MySQL database of ~10,000 organisation names that I want to be able to search. I would like to use a full text search because this would enable me to find "institute of doobry" by searching ...
0
votes
1answer
1k views

Difference between [mysql] and [mysqld] in MySQL configuration file

There are multiple sections in MySQL configuration file: [mysql] [mysqld] [myisamchk] [mysqld_safe] [mysqlhotcopy] [mysqldump] [client] I found that same variables with different values have ...
1
vote
2answers
8k views

Access denied for user 'root'@'localhost' (using password yes)

I was trying to install MySQL 5.6.15 to my computer and got this error: unable to update security settings. access denied for user 'root'@'localhost' (using password yes) I guess current root ...
0
votes
1answer
81 views

Mysql query in Database

How to get all the tables from database with all values in tables using single query.. I tried using $sql = "SHOW TABLES FROM $dbname"; $result = mysql_query($sql); $arrayCount = 0 while ($row = ...
0
votes
1answer
44 views

Couldn't connect to databasee

I have a dedicated webserver and a dedicated database server Often , i got errors connecting to database server couldn't connect to database Here is my current /etc/my.cnf configuration file ...
1
vote
2answers
127 views

Are relay logs necessary for replication?

I'm trying to understand when/where using relay log is beneficial. According the documentation, mysql captures and writes events from the master's binary log as the result of the I/O thread. Then, the ...
0
votes
0answers
66 views

MySQL production vs. development

I have a production database server which I am using for a live web site. I want an exact copy of this database on a separate server, so I can develop against it. My development will involve ...
0
votes
2answers
702 views

How to change the path of data during database restore + SQL Server 2008

I am trying to restore a big database in SQL Server 2008 R2. The database is about 700GB in size. My SQL Server is installed in C drive which has only 100GB of space left in it. I have the .bak file ...
1
vote
2answers
215 views

Securing Your MySQL Server within your team?

Recently I have been having an issue with our internal team members changing the password of the root user. I noticed the following things: OLD_PASSWORD = 1 (So all passwords on our development ...
0
votes
0answers
28 views

Database cache problem

I have server based on Linux 64 Bit centos 6.5 and Mysql innoDB engine I created database for php script require database installation , after i successfully finished installation for first time i ...
1
vote
1answer
178 views

Saving LZW encoded data into a mySQL database

I send LZW compressed data from the client side with JavaScript to the server. The problem is that the data becomes corrupted after saving it to database. So my question is, what collation should I ...
0
votes
1answer
127 views

How to update a column in a table with data from another, unrelated tables columns data jus in order it selected?

There are two tables that unrelated. I want to update a field in first table with data that selected from second table in some order. There is no relations between tables, fields and data. I have just ...
1
vote
2answers
2k views

Two MySQL Server accessing same database over NFS

I want to run two separate MySQL instances on two separate machines, but using same data directory over nfs, is it possible with InnoDB Storage Engine? Application operation will assure the following: ...
1
vote
1answer
48 views

Am I using the ORDER BY clause incorrectly

I am trying to enumerate a database, created specifically for the purpose of learning SQL injections. I am trying to discover the number of columns a particular select statement might be using. I do ...
5
votes
4answers
4k views

How to check for existence of an INDEX on a column independent of used SQL database

Is there are unified way of how to check for the existance of an INDEX for a given column irregardless of the actual SQL database system used? For MySQL one could for instance check for the existance ...
1
vote
1answer
441 views

MySQL: Access denied messages

I have just installed Ubuntu on my server and MySQL. I can't make any change in MySQL (seeting password, creating database, etc.) as I ALWAYS get an Access denied error message. I have followed the ...
0
votes
1answer
809 views

MySQL 5.7.4 Startup Times Out

I recently upgraded the MySQL Server installation on my Windows 8.1 Pro x64 laptop from 5.7.3 to 5.7.4 using the MySQL Installer, and after doing so, attempting to start the MySQL Server service, ...
2
votes
2answers
36 views

Can creating indexes colide with future database upgrades?

I have a mysql Database with very slow querys. I am using the slow query log to monitor those. This is a database created by a third party software that we're using. Their developers didn't do any ...
3
votes
3answers
931 views

A little clarification on how 'LIMIT' works

Not being a full blooded member of the DBA clan, but more of a dev clan, I find myself trying to figure out some slow running queries. The subject of my question however is not 'Why my queries are ...
1
vote
1answer
2k views

Can I use a Percona Xtrabackup backup to restore a single table?

I've been playing around with the Xtrabackup tool for a month now, and I got to admit : it is really powerful ! I'm still testing it to backup database servers between 5 and 150 GB and the ...
0
votes
2answers
375 views

MySQL row does not persist, but primary key with auto-increment is incremented

I have a MySQL database that is acting oddly. I insert a new row and observe that for a brief period of time (less than 30 seconds), the row persists as expected in the table. After this time, though, ...
0
votes
0answers
72 views

MySql query keep running for a long time

Following query keep running for a long time and I'm using MySql 5.5.34 on XAMPP on Windows SELECT id_package, `pck`.`fk_sales_order`, `pck`.`fk_picklist`, count(pci.id_package_item) as items, ...
0
votes
1answer
2k views

Select row with MAX(), or choose one if no MAX() exists

I have a MySQL InnoDB table with the following columns: id: UNSIGNED bigint(20) AUTO_INCREMENT (PRIMARY KEY) name_id: UNSIGNED bigint(20) time_stamp: decimal(26,9) event: enum('add', 'init', ...
0
votes
1answer
2k views

Please explain how this query generates a calendar table in MySQL

This page has a pretty neat way of generating a robust calendar table. I've copy-pasted it below: CREATE TABLE calendar_table ( dt DATE NOT NULL PRIMARY KEY, y SMALLINT NULL, q tinyint ...
0
votes
1answer
882 views

exceeding 'max_updates' resource when importing large database

I need to import a large database on my shared server.I'm using the following statement in a shell to import my database mysql -u USERNAME -p PASSWORD --default-character-set=utf8 DATABASE_NAME < ...