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

learn more… | top users | synonyms

0
votes
0answers
45 views

how to force mysql 5.5 server to allow tls/ssl encrypted connections only?

given a mysql Server 5.5 community edition (which came with SLES 11sp4). i configured it to support TLS and it works fine. but i did not found any options or parameters to FORCE IT to do TLS/SSL ...
3
votes
3answers
269 views

Listing rows with a WHERE condition

I am struggling with a SQL query that lists hosts that are offline. Problem The problem occurs when I need to list hosts that don't have any ports open (status = 0), so looking at the table at the ...
3
votes
1answer
36 views

how to “revoke” a “GRANT OPTION” from an mysql user/database?

i have a mysql installation with lots of users and grants. But one grant is unique, since its supposed to be a simple DB user and usually does not need GRANT privileges. However someone added it when ...
3
votes
2answers
250 views

Different behavior between MySQL 5.5 and 5.6

I have this schema with data: CREATE TABLE `ticket` ( `id` INT NOT NULL, `name` VARCHAR(45) NULL, `type` INT(11) NULL, PRIMARY KEY (`id`)); CREATE TABLE `task` ( `id` int(11) NOT NULL, `...
0
votes
0answers
41 views

Can't connect to MySQL server via SSL

I'm trying to connect to a MySQL server via SSL. I know the server is set up correctly to handle SSL connections because I can connect fine using MySQL workbench, PHP, and command line MySQL client ...
1
vote
1answer
56 views

Cannot open some tables in phpmyadmin

Recently i get backup of my database from host in that backup i get table in .ibd,.frm,.myd format . I placed this files in xampp->mysql->data->test . Now i can open the tables in php myadmin....
0
votes
0answers
28 views

Trigger query fails to execute because of local variable

I have the following table: |-------------------------------------| |Column |Type |Null|Default| |-------------------------------------| |//**id**// | bigint(11) | No | | |soldier_id ...
1
vote
2answers
58 views

Counting rows from a subquery

Simple: I would like to count the number of rows from the sub-query. Note that status is whether the host is online or not. Bad code SELECT COUNT(ip_address) FROM `ports` ( SELECT DISTINCT ...
0
votes
2answers
74 views

MySQL SELECT n-th row between dates (time interval)

I have a server which records various sensor data each second and inserts it into a MySQL table. To view the sensor data on a dashboard I have a chart which also gets updated with live data each ...
1
vote
2answers
277 views

can't I use Current_timestamp as default value in mysql 5.5.47-cll

I am not able to set Current_timestamp as default value. My Mysql version is 5.5.47. Query is ALTER TABLE `downloads` ADD `date` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ; While it is working ...
1
vote
1answer
46 views

Incorrect backup size

We are storing our everyday full backup in remote storage. Everything seems to be working fine, but every seven day interval the archived folder size is much more than the everyday archived backup ...
1
vote
1answer
34 views

MySQL allows records longer than varchar(N)

We have a database with a field of type VARCHAR(255) but in it I can see some records having a length of larger than 255, such as 264. How can it be possible? Can it be that the database is corrupt? ...
0
votes
1answer
48 views

Select rows with most recent date for the given date

I have a table to manage the stock which looks like below product batch qty date Grape BAT0 42 10/3/2016 Apple BAT1 12 11/3/2016 Banana BAT2 15 11/3/2016 Orange ...
1
vote
1answer
117 views

MySQL. Capture Cis program makes incorrect query: SELECT DISTINCT “PartType” FROM “Capacitor” ORDER BY “PartType”

I am having problems to get one database running. I have a MySQL in my server, the server is running "5.5.48-MariaDB - MariaDB Server" with "UTF-8 Unicode (utf8)" I have a MySQL ODBC 64 bits 3.51 ...
4
votes
1answer
255 views

SQL query to compute longest travel time for each id number

I am a novice in SQL and I have a problem as follows. There are two tables TRNSCTN_TAB: CREATE TABLE TRNSCTN_TAB( `transactionId` int , `UniqueId` int , `transactionTimeStamp` varchar(...
0
votes
0answers
25 views

MySQL 5.5 client SSL connection only works when adding cipher list argument, why?

I set up the packaged MySQL server 5.5 and client on two Ubuntu VM. I configured the server to support SSL. I can connect to the server with the MySQL CLI tool between the VMs, but the connection is ...
0
votes
1answer
12 views

What configs influence whether user credentials/grants are written to the binlog in Mysql

I have a fairly trivial master-master setup. When testing this setup I see that mysql users are binlogged and replicated on slaves. However on each of the masters the binlog-do-db* is for a specific ...
0
votes
0answers
59 views

EXPLAIN SELECT query is writing to tmp table

I am getting a "copying to tmp table" when running an EXPLAIN SELECT on a query. This is a pseudo-query showing the structure of the query (I can't post the actual query): SELECT colA , colB , ...
1
vote
1answer
38 views

Altering a large table to INNODB

I have a table of 4GB(18 Million Rows) in MYISAM Engine on one of my slave server, and I want to convert it to INNODB Engine, the server is replicating from a master. I tried to use the pt-online-...
0
votes
1answer
29 views

Slave (SQL thread) replicates very slowly

See this video: https://mckay.media/Yxjp2 I have two MySQL replication slaves which are replicating from the same master. Both slaves are running on identical hardware. Each has its data directory ...
0
votes
0answers
14 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 ...
0
votes
1answer
27 views

commit statement missing for a dml statement in binary log

I have noticed 'commit' statement missing for a DML statement in binary log. Does it mean that this statement is rolled back in server? I am using mysql 5.5.40. sample binlog at 159527792 160229 ...
1
vote
3answers
253 views

MySQL database uses too much CPU

I have a MySQL database which uses too much CPU. I am actually performing benchmarking to the web servers which queries the database. Right now the database is being the bottleneck, it uses too much ...
0
votes
0answers
83 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 ...
0
votes
1answer
34 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
1answer
166 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: 5.5.47-0+deb8u1-...
0
votes
0answers
157 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/ https://...
0
votes
1answer
38 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
137 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. ...
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, ...
2
votes
2answers
54 views

How to get month name from week number and year [closed]

I have a week number and a year (e.g. 34 and 2015). I want to get the name of the month to which the specified week belongs. How can I do that?
0
votes
0answers
60 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 ...
1
vote
1answer
455 views

Slave not getting replicated SERVER_UUID error

Unable to start replication Master-Slave, I have searched a lot for this but unable to get the Server-UUID of both the server. Do we have any solution for this. Scenario: I have created new slave ...
1
vote
3answers
250 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
37 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
66 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 MySQL-server-5.5.41-1.el6....
0
votes
0answers
63 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 ...
3
votes
1answer
214 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 ...
2
votes
0answers
404 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, server-...
0
votes
1answer
24 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- ...
0
votes
1answer
118 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 ...
0
votes
1answer
52 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 ...
2
votes
0answers
113 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 ...
2
votes
1answer
65 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 -rw-...
0
votes
1answer
39 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 ...
1
vote
2answers
165 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 to ...
2
votes
1answer
60 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
103 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: http://pastie.org/private/...
0
votes
0answers
208 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 ...