MySQL version 5.5 - please also tag with mysql for search purposes.

learn more… | top users | synonyms

0
votes
0answers
65 views

SQL Timeout when updating specific rows that goes away when other columns are updated

The data I'm going to provide won't be very complete, so I apologize in advance for that, and will happily supplement with more on request. I mostly just don't know where to begin looking, and don't ...
7
votes
3answers
23k views

Difference between On Delete Cascade & On Update Cascade in mysql

I have two tables in MySQL database- parent, child. I'm trying to add foreign key references to my child table based on the parent table. Is there any significant difference between ON UPDATE CASCADE ...
0
votes
0answers
13 views

Default Space in WAMP

What is the Default Space for MySQL in WAMP SERVER? We are using MySQL as the back-end for our application development, and it is running out of space. How can we increase the disk space? Or are ...
4
votes
3answers
7k views

“Could NOT find Curses” error when installing MySQL from source

I am installing MySQL Server from the source distribution which I downloaded from: http://dev.mysql.com/downloads/mysql/#downloads. I have downloaded the file (mysql-5.5.19.tar.gz). I am using ...
0
votes
1answer
30 views

What are the side effects of changing MySQL `character_set_server`?

I am having some strange issues with character sets on my system. After digging through all kinds of settings, I found a discrepancy in my character set settings. When I run show variables like ...
0
votes
0answers
45 views

begin end block in MySQL

I have a compound statement of begin end block consisting of some SQL statement as mentioned below. lab_name: BEGIN truncate table table_name; insert into table_name (a,b,c,d,e,f) ...
0
votes
1answer
91 views

sql_safe_updates set to ON by default

This is fresh mysql install from Debian apt. mysql> \s -------------- mysql Ver 14.14 Distrib 5.5.47, for debian-linux-gnu (x86_64) using readline 6.3 .. Server version: ...
0
votes
0answers
95 views

MariaDB: Can't connect to MySQL server on '127.0.0.1' (107)

What does it mean the error 107? Can't connect to MySQL server on '127.0.0.1' (107) I haven't found this error in any documentation: https://mariadb.com/kb/en/mariadb/mariadb-error-codes/ ...
0
votes
1answer
30 views

Is it safe to “FLUSH STATUS” in mysql?

We have deployed REDIS to hold some frequent query result to reduce number of connections made on Database server. We need to find out Max_used_connection after deploying REDIS. So we plan to reset ...
6
votes
1answer
116 views

Non-grouping field error with ONLY_FULL_GROUP_BY mode and tables that have the same column name

I'm finding that MySQL seems to have an issue when ONLY_FULL_GROUP_BY mode is enabled and the query includes a HAVING that refers to grouped columns from different tables that have the same name. ...
9
votes
4answers
7k views

How costly is opening and closing of a DB connection?

How CPU intensive is opening and closing of a DB connection (for a web app) in MySQL ... when the DB software is on localhost? ... when the DB software is on another machine?
0
votes
0answers
60 views

Optimize MySQL JOIN Query

I've been using following Query to fetch data from MySQL. SELECT SQL_CALC_FOUND_ROWS DISTINCT (wi_individual_g.ind_id), wi_individual_g.ind_id, wi_individual_g.ind_name, ...
4
votes
1answer
10k views

How do you tell which host is close to being blocked in MySQL?

I have a two part question: In cases were this error comes up, is there a way to see if a host is close to being blocked? And fire off an alert? PDOException: SQLSTATE[HY000] [1129] Host `hostname` ...
3
votes
2answers
5k views

How to COUNT number of rows with LIMIT?

I want to catch X rows, thus, I set LIMIT X; but how can I simultaneously count the total number of rows too? Currently, I use two separate queries to do so as SELECT COUNT(*) FROM col WHERE CLAUSE ...
1
vote
3answers
223 views

Mysql 5.5.45 crashing after refreshing web page rapidly(Too Many Connection warning)

I am using ec2 aws instance for my wordpress webiste.after every 5 to 10 minute and randomly my website going down i tried lots of things like increasing buffer memory ,memory limit but that not ...
0
votes
0answers
48 views

Replication Error_code: 1032

I an unable to fix this as I don't know what cause this error. Replication was stoped for maintenance activity and after completing the maintenance we started it and then after some point the ...
9
votes
2answers
11k views

MySQL equivalent of WITH in oracle

Is there a MySQL equivalent of the WITH clause in Oracle?
0
votes
0answers
34 views

Is double one to many a bad design? It looks duplicate when joining both one-to-many tables

Suppose I have user table, book table, DVD table. User can borrow n book, n DVD at the same time, a book or a DVD can be borrowed by a user. (I understand it is many-to-many situation in real life, ...
0
votes
0answers
50 views

Mysql Replication break Error_code: 1032

Created new slave replication, it was working fine but suddenly after 2 days its giving me this error and the replication stopped. Both on Centos 6.7 Master Version ...
3
votes
1answer
151 views

MySQL replication - invalidating query cache entries (table)

We lately moved to MySQL V5.5 on CentOS release 6.7 with master and 2 slaves. Since then the replication process lags a lot behind the master with the line in the process list of: invalidating ...
0
votes
0answers
32 views

MySQL replication Got fatal error 1236

I am getting this error when starting a new slave replication from master. Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded ...
0
votes
1answer
86 views

Restoring MySQL dump ERROR 1449

I took hot backup (dump) from my Master MySQL DB with below command mysqldump -uroot -p --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -A > ~/dump.sql ...
1
vote
0answers
306 views

Using SSL produces error “protocol version mismatch”

Using MySQL Server (5.5.46-0) on Ubuntu 14.04.2 with protocol version 10. Configuring SSL and defining SSL cert files locations in My.cnf file. Have defined absolute paths for CA-cert.pem, ...
0
votes
1answer
23 views

Mysql Replication backup and restoration failed multipal time

I tried restoring MySQL backup several time but unfortunately it gets failed by some or the new reason. I have 2 host Master & Slave with below details. Server1_Master os-centos6.7 mysql- ...
21
votes
3answers
88k views

ALTER TABLE - Rename a column

This got me nuts! I want to rename a column from read-more to read_more in blog table Tried all this: ALTER TABLE blog RENAME COLUMN read-more to read_more; ALTER TABLE blog CHANGE COLUMN ...
2
votes
0answers
93 views

MySQL: Is the read committed isolation level safe with mixed binary log format?

When using the MIXED format with the READ COMMITTED isolation level, am I safe? The docs state (http://dev.mysql.com/doc/refman/5.5/en/set-transaction.html#isolevel_read-committed) If you use ...
0
votes
1answer
66 views

Join on with modified ON

I am trying to create table with summary of few other tables. All solutions i came up with are very slow and clearly not optimal. Initially I tried with multitude of subqueries but that was very slow. ...
0
votes
1answer
47 views

MySQL optimizer difference between 5.6.27 and 5.5

We've just updated the MySQL version of the database of one of our clients from the 5.5 to the 5.6, everything is working fine except one simple query which is causing us troubles. Having the ...
3
votes
2answers
779 views

MySQL users corrupt

I have a strange situation here: From time to time I cannot log in with any of my mysql users. I even cannot make a mysql dump. So I started searching in mysql files and I found that users.MYD and ...
2
votes
1answer
56 views

why my MySQL binary log file size is so small?

I have a lot of small binary log files , each of them just only 126 Bytes -rw-rw----. 1 mysql mysql 126 Jan 6 16:13 mysql-bin.000001 -rw-rw----. 1 mysql mysql 126 Jan 6 16:16 mysql-bin.000002 ...
11
votes
2answers
18k views

Cannot configure nor start MySQL

On a brand new Kubuntu 14.04 install, I ran sudo aptitude install mysql-server-core-5.6. The install could not complete due to dependencies of some KDE packages on mysql-server-core-5.5, which would ...
0
votes
1answer
195 views

installing mysql 5.1 alongside mysql 5.5

mysql 5.5 is installed. I need 5.1 to install an old website so it can be updated. The installer refuses to let me install onto the disk with 5.5. I don't have any other disks. I downloaded the ...
0
votes
1answer
28 views

load data infile in mysql server 5.5 can't load data when foreign key exist

I have a database (TPC-DS benchmark base) with 24 tables(17 dimensions tables, 7 facts tables) I generated this database then I created it in mysql server 5.5 I used the instruction load data local ...
3
votes
2answers
162 views

How to convert timezone in MySQL using the timezone name?

I want to convert date from one timezone to another timezone in MySQL, MySQL query which I am trying is SELECT DATE_FORMAT(CONVERT_TZ('2015-12-18 06:00:00', '+0:00', '-05:00' ),'%b %d %Y %h:%i %p') ...
1
vote
2answers
104 views

Can error 1231 after importing a MySQL database be a compatibility issue?

ERROR 1231 (42000): Variable 'character_set_client' can't be set to the value of 'NULL' Query OK, 0 rows affected (0.00 sec) ERROR 1231 (42000): Variable 'collation_connection' can't be set ...
2
votes
1answer
59 views

Joining two fast subqueries is very slow

DB: MySQL 5.5.20 (WampServer) OS: Win 7 HDD: Western Digital 3TB Caviar Green, 3.5", IntelliPower, 64MB, Sata3 (WD30EZRX) Memory: 8GB my.ini: http://pastie.org/private/s7l9aadyi0qhfl5bcrnnqq I've ...
2
votes
2answers
92 views

Optimizing a simple query on a large table

DB: MySQL 5.5.20 (WampServer, default configuration) OS: Win 7 HDD: Western Digital 3TB Caviar Green, 3.5", IntelliPower, 64MB, Sata3 (WD30EZRX) Memory: 8GB MySQL my.ini: ...
1
vote
2answers
2k views

Slow INSERT/UPDATE on InnoDB

I have a problem with the performance of my MySQL server, let me explain: I instaled a MySQL server, version 5.5.20 Server version: 5.5.20-log Source distribution SELECT queries runs fast on ...
2
votes
0answers
40 views

Composite index works for larger set of data, but not smaller

This is a follow on question to optimise my.ini for key query Data model: DROP TABLE IF EXISTS `contract`; CREATE TABLE IF NOT EXISTS `contract` ( `id` int(11) NOT NULL AUTO_INCREMENT, ...
0
votes
0answers
137 views

Change MySQL stored procedure 'Database Collation' name

I have imported a DB from Local machine to server machine. While importing the Database, the character set values of the DB are set by system default to "Latin". I have changed the character set to ...
2
votes
2answers
32 views

MySQL Database Archive

We have 300 databases in our environment mixed of MyISAM and Innodb(innodb_file_per_table has been enabled) tables.Using MySQL 5.5.19 version. I we are planning archive inactive database.Is there any ...
3
votes
1answer
58 views

How we can recover MySQL database (Innodb,myisam)

I have found some recovery tool innodb-recovery but it works only on linux machine, I would like to know how to recover innodb tables on windows. Transaction ID based recovery Cancel based, Change ...
1
vote
1answer
1k views

what could cause a mysql process to close (clean)

We are using mysql master-master (version 5.5.32) for an API manager called Layer7. The 2 hosts holding the instances are in the same ESX From time to time we have mysql doing a clean shutdown for ...
1
vote
2answers
146 views

Mysql 5.5.25 must be restarted to connect

We have MySQL v5.5.25 running on Windows Server 2008 R2. Occasionally people are unable to connect, and the only way we have found to fix this is to restart the mysql service. This happens every 2-3 ...
0
votes
0answers
2k views

mysqld upgrade 5.5 -> 5.6 mysql Error: Failed while fetching Server version! Could be due to unauthorized access

After updating mysql with yum to 5.6 I am getting such an error message when trying to run mysql [root@host rpm]# mysql_upgrade Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck ...
1
vote
3answers
124 views

why in explain in mysql row count is very high

i am using mysql, here is query- mysql> select count(*) from payments as Payment WHERE `Payment`.`fordate` BETWEEN '2015-11-28 00:00:00' AND '2015-12-31 23:59:59'; +----------+ | count(*) | ...
0
votes
1answer
37 views

Why is MySQL 5.5 ignoring this index?

I have a pretty simple query that is not using an index I expect it to because of the WHERE. Table: CREATE TABLE `cba_device_log` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `device_id` varchar(30) ...
0
votes
0answers
41 views

MySql server failing to start

I was reinstalling backup image on Raspberry Pi and I noticed that MySql server isn't running. I've looked in /var/log/syslog and I bumped into this: Dec 4 20:06:00 node mysqld_safe: Starting mysqld ...
0
votes
2answers
39 views

How to reconfigure primary key columns

I have a series of tables in my database that are using DATE as the primary key. I would like to reconfigure the tables so that an AUTO_INCREMENT ID field is the primary key. What is the best way to ...
0
votes
2answers
3k views

MySQL 5.5 Fail start Fedora 16

I installed mysql and mysql-server from the repos (MySQL version 5.5). Then tried to start it, but got an error. [root@server]# service mysqld start Redirecting to /bin/systemctl start ...