Questions tagged [mysql-5]
Generic MySQL version 5 tag. Please use a mysql-5.x tag instead. Also tag with mysql for search purposes.
178 questions
4
votes
5
answers
10k
views
Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
I tried restarting the Mysql server but its not working for me. I am sure the server is running and the permissions on the folder /var/lib/mysql/ are correct (777).
When i tried to open the file ...
- 101
2
votes
4
answers
11k
views
MySQL: How to avoid case sensitivity in SQL queries when migrate from Windows to Linux?
I have migrated my Database from Windows to Linux server. In many places, query has been written without focusing their cases (uppercase / lowercase). So, these queries are not getting executed and ...
151
votes
4
answers
256k
views
Is it safe to delete mysql-bin files?
I have MM Replication in mysql, and I want to squeeze some free space in the box be deleting unnecessary files, I came across these mysql-bin files inside /var/db/mysql/ There are hundreds of those ...
- 1
1
vote
2
answers
93
views
MySQL with subquery becomes slow
I have 3 MySQL tables: products, orders and users.
I want to show all ordered products where not all products are "pre-orders" or "discounts". Note: one user can have multiple ...
- 79.4k
13
votes
4
answers
52k
views
Can't remote access MySQL server running on Mac OS X
I have a MySQL server running on my work computer, which runs Mac OS X Maverick. I want to access it from home computer, which also has Mac OS Maverick.
From terminal, if in 1 tab, I ssh into my ...
1
vote
1
answer
3k
views
How to write signal function in MySQL that can be called from Triggers and Stored Functions?
In my Database I have a table: Employee with recursive association, an employee can be boss of other employee.
The Table Description:
mysql> DESC Employee;
+-------------+--------------+----...
- 184k
6
votes
2
answers
15k
views
Performance settings for MyISAM tables - keep everything in memory
I have a 8-core MySQL server with 32 GB of RAM, holding 40 MyISAM tables (total: ~4GB of data).
Since the databases size is several times smaller than the available memory, how can I make full usage ...
- 184k
3
votes
3
answers
13k
views
Computed/Generated columns in MySQL 5
How do I create a table with a formula column in MySQL version 5?
For example: I have a table named Product, which has 5 columns:
id int,
amount int,
sold_out int,
faulty int,
remain_amount is (...
- 101
63
votes
8
answers
138k
views
Restore mysql database with different name
How can we restore mysql database with different name from mysqldump file.
I dont't want to open dump file and edit it. Any other better methods?
- 101
2
votes
2
answers
472
views
MySQL query slow even with indexes
Structure of my table
mysql> show create table t_group_tag_relation\G
*************************** 1. row ***************************
Table: t_group_tag_relation
Create Table: CREATE TABLE `...
- 718
7
votes
1
answer
9k
views
Is there a way to find my.cnf in shared hosting?
I'm curious is there a way to find my.cnf in shared hosting or is this file can be configured just by DBAs and no one can set his/her own settings?
- 184k
19
votes
1
answer
156k
views
How to get rid of "maximum user connections" error?
I am using MySQLi for my webapp but whenever I want to visit some particular page, I get mysqli_connect() [function.mysqli-connect]: (42000/1203): User ***_user already has more than '...
- 184k
111
votes
3
answers
172k
views
What is the default order of records for a SELECT statement in MySQL?
Suppose you have the following table and data:
create table t (
k int,
v int,
index k(k)
) engine=memory;
insert into t (k, v)
values (10, 1),
(10, 2),
(10, 3);
When ...
- 103
3
votes
1
answer
92
views
Structure of my database design?
I've been developing a C# Bus System app using SQL, and I need some advice. I don't know if asking on here is the right thing to do, but I essentially need someone to analyse the .SQL file I have, ...
- 133
-1
votes
1
answer
1k
views
Mysql Select all Rows after nth Position
I wish to fetch all rows from table after the nth Position as i given offset value
my table like
id | Value
1 | A |
2 | B |
3 | C |
4 | D |
5 | E |
...
N | R |
I wants the 3rd row Data to ...
- 56.5k
5
votes
4
answers
24k
views
mysql duplicate entry error 1062 when restoring backup
Sorry, I seen similar threads but I still couldn't find it addressing my issue plus, I needed some more info on this.
Requirement: To create an exact replica 'db4' of an existing DB 'db3'.
Procedure ...
7
votes
2
answers
3k
views
Will a mysql db import be interrupted if my ssh session times out?
I have a large-ish (several GB) mysql DB that I'm planning to import via
mysql -u root -p mydb < mydb.sql
in a SSH session.
What would happen if my SSH connection drops?
Is it safer to run ...
- 121
2
votes
3
answers
3k
views
Table Creation Order
I have designed my database model with entities and they have relations along with normalization. Now I want to implement my design in my database engine. Before implementing I am puzzled with the ...
- 823
7
votes
2
answers
31k
views
query execution time in mysql
I have run the query in mysql which consists of approximately 33million records when i have executed the query
select population1 < 200000 from city;
it shows like this after execution
34459393 ...
- 1
2
votes
1
answer
4k
views
MySQL: trying to create root user
Upgrading MySQL database to 5.5 and it seems that the root user has disappeared.
I'm trying to create it again using this commands:
javier@javier-mbp:~$ sudo pkill mysqld
[sudo] password for javier: ...
- 226
2
votes
1
answer
6k
views
ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full
There is a SAN mounted 1TB partition for Mysql backups in our server with version 5.0.45-log. From two days, im seeing the log entries ERROR 1114 (HY000) at line 6308 in file & The table ...
- 1
10
votes
3
answers
6k
views
Users complain that system runs slow when mysqldump is in progress
The MYSQL database (ibdata1) is of size 73 GB and is configured to run as a dedicated database server on Windows 2008 O/S for INNODB tables.
We are running the backup using mysqldump
mysqldump --...
- 184k
8
votes
3
answers
9k
views
Changed max_allowed_packet and still receiving 'Packet Too Large' error
I use mysqldump to create a flat file for backup purposes. I have used this file to recreate the database on an alternate server. I ran the import process through ssh on the command line and I ...
- 184k
2
votes
1
answer
1k
views
why is mysql still writing data two hours after all transactions have stopped
I have a mysql server used mostly as data warehouse. It loads about 4-5GB of data per day (actual database size, not the size of the source data).
Lately, I noticed something peculiar. After I ...
CommunityBot
- 1
2
votes
1
answer
1k
views
MySQL : Does 'bytes_sent' and 'bytes_received' include mysqldump data?
I'm trying to estimate my db traffic not including backups (mysql 5.1.41).
I use show global status; to get the parameters bytes_sent and bytes_received.
Questions
Do these numbers include ...
- 184k
1
vote
2
answers
13k
views
Table Design for Student Attendance hourly for each day
I have created a table (attendance_details) for storing every day attendance of Students in MySQL. Need to capture attendance for hourly (1-8hrs) every day. I have the student details in separate ...
- 1
1
vote
1
answer
3k
views
Duplicate entry in MySQL slave
I have a MySQL master-slave replication setup. When I checked show slave status, I got the result as Slave_Sql_running:No and last error as Duplicate entry 'x' on query.
But when I tried to delete ...
- 90.3k
51
votes
6
answers
46k
views
In MySQL, does the order of the columns in a WHERE clause affect query performance?
I am having performance issues on certain database queries that have large possible result sets.
The query in question, I have three ANDs in the WHERE clause
Does the order of the clauses matter?
...
CommunityBot
- 1
6
votes
3
answers
11k
views
MySQL secure random string generator
I am looking to generate a cryptographically secure string for password reset in MySQL. I know I could do this at the application level in PHP; but that requires the OpenSSL extension which many of my ...
- 101
4
votes
2
answers
9k
views
Setting up MySQL Cluster on Two Systems
I am attempting to setup a MySQL Cluster.
I have the folllowing setup
one management node
two data nodes
another machine on which two more nodes reside
Below is the config.ini file:
[ndbd ...
- 196
4
votes
1
answer
136
views
Find problematic MySQL users and databases
We have a MySQL 5.1 server that is an "open" service: anyone on campus can request a database. As such, we have several thousand databases on this one server.
As you might imagine, sometimes one user'...
- 30.6k
2
votes
1
answer
768
views
How we can enable `index_condition_pushdown`?
I have optimizer_switch variable as
mysql> show variables like 'optimizer_switch';
+------------------+-------------------------------------------------------------------------------------------+
|...
- 64.7k
2
votes
1
answer
787
views
Sleeping connections
I have a dedicated MySQL server(8GB) with following configuration.
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
lower_case_table_names = 1
default-storage-engine=...
CommunityBot
- 1
4
votes
2
answers
4k
views
How do you copy a table from MySqlServer_A to MySqlServer_B?
I have a daily task that collects data from 3 MySql Servers and then pushes that aggregate data back out to each of the servers deleting the previous days data. All servers are MySql. I would like to ...
- 184k
2
votes
1
answer
4k
views
How to update mysql my.cnf without getting error?
I updated the MySQL my.conf file and and restarted my server without a problem.
As soon as I tried to access a table I received the error
mysql #1033 - Incorrect information in file:
I reverted ...
- 184k
112
votes
3
answers
392k
views
ALTER TABLE - Rename a column
This is driving me nuts!
I want to rename a column from read-more to read_more in my blog table
I tried all this:
ALTER TABLE blog RENAME COLUMN read-more to read_more;
ALTER TABLE blog CHANGE ...
- 105
45
votes
2
answers
115k
views
InnoDB: Error: Table "mysql"."innodb_table_stats" not found after upgrade to mysql 5.6
I upgraded to mysql 5.6 from 5.5, and now my logs are littered with such messages on startup
I found a possible solution here, but it does not seem official.
http://forums.mysql.com/read.php?22,...
- 103
2
votes
1
answer
637
views
How does MySQL coerce types during joins?
Why do these two queries behave differently:
mysql> explain select FOOBAAR_hhs.relationship_group from FOOBAAR_hhs left join FOOBAAR on FOOBAAR_hhs.relationship_group = FOOBAAR.relationship_group ...
- 184k
3
votes
2
answers
6k
views
Can we assign mysql Key_buffer_size value for particular table?
In some documents I found that we can assign key_buffer_size to myisam table. This also includes the following steps:
SET GLOBAL myisam_table.key_buffer_size = 4194304;
CACHE INDEX test.myisam_table ...
- 184k
4
votes
2
answers
24k
views
MySQL Event does not run
i am running mysql 5.1.41 that came bundled with xampp on windows. the problem is that the event doesnt run automatically even when the event scheduler is ON. i have a table named ta_table using ...
- 184k
2
votes
2
answers
712
views
Keywords - column or separate table
I am building a website on MySQL with php, and I have 400+ products in a table. I have three columns with comma separated values, each has 1-3 values separated by commas in each row.
I was wondering ...
- 25.1k
10
votes
4
answers
26k
views
How to get the most out of MySQL on a QuadCore machine with 16 GB of RAM?
I'm running a MySQL 5.5 server on my workstation for scientific data analysis and wonder how to configure MySQL in order to get the most out of it performance-wise. The types of query that I typically ...
- 184k
30
votes
3
answers
41k
views
Why query_cache_type is disabled by default start from MySQL 5.6?
We've upgraded to MySQL 5.6 and start seeing the loading of db server increased significantly, and finally found out the query_cache_type is defaulted to off start from 5.6.
We enabled it again and ...
CommunityBot
- 1
2
votes
1
answer
7k
views
MySQL Master 5.5 - Slave 5.6 - unknown system variable 'binlog_checksum', Error_code: 1193
I see the below warning on the my slave server:
2016-11-18 22:48:03 12808 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, ...
CommunityBot
- 1
5
votes
1
answer
2k
views
MySQL v5.1.73 - can the binlog_format betweem Master & slave be different?
We are in the process of testing MySQL replication before using it in production. The MySQL is being setup by Puppet in our case and I noticed that the binlog_format setting is different between ...
- 184k
7
votes
1
answer
14k
views
Any known issues upgrading from MySQL 5.1.73 to 5.6.21?
I am planning my MySQL DB ver5.1.73 running on a CentOS 6.5 machine to MySQL 5.6.21.
I understand the basic procedure for this based on what I have read on StackOverflow, but I was curious if anyone ...
- 184k
3
votes
1
answer
7k
views
Moving MySQL Cold Backup to Another DB Server
I am new to MySQL and want to know the info whether taking a cold backup from source and copying it onto new server with same version require any additional configurations settings to be changed.
As ...
- 184k
1
vote
2
answers
5k
views
Steps to upgrade MySQL from version 5.0 to version 5.5
I have been developing under MySQL v5.0 for a while now and need to upgrade to v5.5 to be in step with the hosted production server.
After having searched for precise steps to do this migration, I am ...
- 184k
2
votes
1
answer
2k
views
Is it okay to have different lower_case_table_names value on master and slave?
I have a OSX MySQL master with lower_case_table_names set to 2. I have just setup a Linux MySQL slave with lower_case_table_names=1.
I haven't started replication on the slave, but everything looks ...
- 184k
1
vote
2
answers
3k
views
MySQL Community Edition: Can I replicate my entire server with another server in one go?
I need to migrate all the databases from one server to another. There are about 100 databases, and all databases are used daily and updated/inserted too and read through their respective websites.
...
- 184k