MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
1answer
1k views

MySQL SELECT query hangs - Opening Tables INNODB

On my server I have 6 MySQL databases. They all power Wordpress websites. For some reason, 2 of these databases are not accessible. To my knowledge they use INNODB, but I can't confirm it as even ...
1
vote
1answer
22 views

Is it possible: MySQL semisync and async replication on same master-slave pair?

I want to use master-slave MySQL (or MariaDB) replication to remote server as data protection from hardware failures. I have two goals: to keep low response time and don't lose important data. Good ...
0
votes
1answer
15 views

MySQL audit storing application user ID

I am trying to set up auditing for changes to data in MySQL tables, for my Perl web application on Linux. I've had a look at this: ...
0
votes
1answer
21 views

Relationship issue

I'm working on a ERD with mysql workbench, and i have a little question. I have a Database with this tables: company_address country state city I want to store the address of the company with all ...
1
vote
1answer
186 views

How to improve the performance of SELECT queries with multiple JOINs on a 50GB database hosted on AWS m3.medium MySQL RDS?

Example query: SELECT p.ListAgentEmail, p.ListAgentFirstName, p.ListAgentLastName, p.ListingKey FROM Property_RES AS p INNER JOIN Property_RES_COUNTIES_OR_REGIONS ON ...
0
votes
1answer
13 views

Select Count on Big table take 15 minutes

I try to run select Count on table with like 16M rows, with MySql 5.5 and workbench 6.2. The query running almost 1000sec . Is it normal? The query : SELECT COUNT(1) FROM My_Table when I run ...
0
votes
1answer
194 views

DB disk space requirements comparision between MySQL versions

I have 2 labs where MySQL is installed. DataBases are created and deleted dynamically by certain internal processes. InnoDB is the Engine used. In one lab, MySQL version 5.0.77 is installed. The DB ...
1
vote
2answers
633 views

What is com_set_option?

As the title says, I'm wondering what is this command and why am I seeing a large amount of this query on my database? It's currently 13.30% of my total queries. I've tried googling this but there's ...
0
votes
1answer
115 views

Please suggest my.cnf configuration based upon mysqltuner findings

I have been facing a lot of slow browsing on my site nerdeky.com. My server administrator suggested me to use mysqltuner.pl to find and tune my databases. Can anyone select me what should be the good ...
0
votes
1answer
39 views

What does “as anonymous on” means in mysql's general log?

In mysql's general log I see things like: 141017 8:57:31 4 Connect root@localhost as anonymous on What is this "as anonymous on" thing even means? I log in as root@localhost. Am I still ...
0
votes
1answer
108 views

SQL SELECT - adding field to SELECT with JOIN is changing the results

I have a SQL sentence with JOIN, running on mysql 5.5.16: SELECT DISTINCT (ot.orders_id), ot.value FROM orders_status_history osh INNER JOIN orders_total ot ON ( osh.orders_id = ot.orders_id ) WHERE ...
0
votes
0answers
10 views

SSMA mysql double to sqlserver decimal, decimals getting cut off

I have a field that is a (signed) double in mysql, and I want it to be a decimal(18,4) in sqlserver. I have changed the Type Mapping for Source Types double and double precision to decimal[18][4] in ...
0
votes
1answer
157 views

How do I write this script?

I have a very large database and I want to select certain records and then set a column value such as: SELECT name, email FROM table WHERE zipcode = 12345 INTO outfile /tmp/file.csv; Then I ...
0
votes
1answer
26 views

xenforo vulnerability, limit max open connections by ip address

Trying to get some emergency help because my website is being attacked. It isn't as much as an actual attack, as it is a small bot to exploit xenforo vulnerability. ...
0
votes
2answers
56 views

After mysql server restarts I cannot see my own database

By mistake I restarted MySQL server in Linux system. But MySQL server was unable to start. I renamed my.cnf to my_bak.cnf. After that MySQL server started. But I can not see my project database. I ...
0
votes
1answer
239 views

Does MySQL load the entire row into memory, or just the columns used in WHERE, ORDER BY and GROUP BY?

I have a table that isn't very large row wise (in the 100ks range), but that contains a lot of raw data that is very large in size. Despite having a relatively little number of rows, it's around ...
5
votes
2answers
2k views

How could one monitor MySQL replication?

I am not a DBA, but I am interested in knowing how replication monitoring is done on MySQL severs. In order to know if a slave is in sync with its master, one would typically check ...
0
votes
2answers
2k views

Partition by year and sub-partition by month mysql

I have a database with a date column having datatype as date. I am using mysql 5.6 The table is expected to have 10k rows each day. I want to create partition on the table on year range and then sub ...
3
votes
1answer
329 views

Removing “duplicates” within a time range

I have a table that contains number plate data with a date and time stamp, collected by a plate-reading CCTV camera at a site entrance. Vehicles stopping at the site entrance will create multiple ...
9
votes
2answers
4k views

How to select specific rows if a column exists or all rows if a column doesn't

I'm writing a script that gets a count of rows for a few tables, however for some tables I want to only get a count of rows where a flag is set (in this case active=1). Is there a way I can do this ...
3
votes
1answer
3k views

Allocating 8GB memory to MySQL on a 64bit system

Specs - MySQL 5.0.x , Redhat 5.9 , Physical memory - 16GB I am trying to set innodb buffer pool size to 8GB (innodb_buffer_pool_size=8G). When i do, and start mysql, i get following error - ...
5
votes
1answer
203 views

SELECT INTO OUTFILE vs INSERT INTO … SELECT

During my cursory research I have not been able to find a definitive answer on the benefits that SELECT INTO OUTFILE provides over INSERT INTO ... SELECT. Upon reading the docs related to INSERT INTO ...
0
votes
2answers
86 views

VARCHAR primary key - MySQL

Currently, I have a categories table with 2 columns - category VARCHAR(50) NOT NULL PRIMARY KEY and parent VARCHAR(50). The parent column is a foreign key (FK) to the category column. This seems like ...
1
vote
1answer
45 views

choose Username or ID as primary key? [duplicate]

my sites dont need use ID at all, then I am trying to manage user by username. Assume my sites is abc.com, then access to user profile by URL: abc.com/user1 So if you want to view all info of that ...
0
votes
1answer
236 views

MySQL: Transaction and Deadlock

I have table "tbl_data", where have a lot of rows (3 - 5 million). I have JAVA (*.jar) module from which I INSERT/UPDATE data into this table. If I start *.jar file (for example, "run.jar") and after ...
1
vote
2answers
24 views

MySQL returns floats rounded

> CREATE TABLE test(foo FLOAT); > INSERT INTO test VALUES(1.2899999); > SELECT * FROM test; +------+ | foo | +------+ | 1.29 | +------+ While it's obvious that FLOAT is not a precise data ...
1
vote
2answers
31 views

Optimal indexing for SELECT (mysql)

What indexes would you recommend for the following select (and why)? Keep in mind there can be tens of millions of rows. SELECT * FROM mytable WHERE md5 = ? AND created_at >= ? AND created_at ...
1
vote
1answer
22 views

trying to understand table size

Can somebody help explain the "data_length" of the table? Why is it so big in my example below of a table with just one column and one row? mysql> create table `network_type` ( `id` int(11) NOT ...
1
vote
0answers
42 views

How to create the database for akinator clone? (github/projects/akinator) [closed]

On sourceforge (http://Sourcecode.net/projects/akinator) I found a clone from akinator (an algorithm for finding a famous person or character via yes or no questions). From there I got the php files ...
0
votes
1answer
25 views

MySQL Workbench is broken, is there another free, crossplatform tool I can use? [closed]

I recently updated mysql-workbench-community to latest version 6.2.4 from 6.2.3, I now longer able to use it at all. I created several bug reports: MySQL Bugs: #75012: error while loading shared ...
0
votes
1answer
23 views

Duplicate entry error for unique key that doesn't exist

I have a model that had the following fields: +----------+----------+ | username | password | +----------+----------+ | AAA | *** | | BBB | *** | | CCC | *** | ...
-5
votes
0answers
32 views

How much space does database takes to store twitter data? [closed]

Context I am going to collect twitter-data(user,s followers, timestamp, content, url and Hashtags) weekly and it continues for an year. I have a total of 160 hash-tags, and I will be creating an ...
0
votes
1answer
22 views

MySQL 5.6 Datetime Range

According to MySQL 5.6 doc, the supported range for datetime data type is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. But when I tried inserting values that are earlier than '1000-01-01 00:00:00', ...
0
votes
1answer
125 views

Quickly etablishing if a remote MySQL connection is valid using PHP

I have a hosted website (Namecheap Pro shared hosting) that connects to a remote MySQL database using a read-only user privilege. This remote DB is updated by a separate system. The website works ...
0
votes
1answer
41 views

How to call a windows program in mysql prompt in windows

I want to call an OS level commands from mysql prompt within Windows command line tool. In Unix* there is a system command available to execute OS commands within MySQL prompt. Any way of dong the ...
0
votes
0answers
22 views

Many transactions are getting stuck in the “cleaning up” state - what could cause that?

I'm trying to track down a locking issue, and found via SHOW PROCESSLIST that when the queries in question are waiting for locks and timing out, there are no other queries active in the process list. ...
0
votes
0answers
17 views

MySQL multi masters with replicas

Currently I have a Master-Master setup, however there is a need to create replicas servers in geographical areas. I am trying to figure out the best method to handle this. The kicker is not all ...
2
votes
3answers
53 views

How to dump only the MySQL database structure? [closed]

I am trying to take dump of MySQL database structure using the following commands: mysqldump -d -h localhost -u root -p databasename > dumpfile.sql mysqldump -d -h localhost -u root -p database ...
0
votes
1answer
74 views

NDB Cluster seems to ignore foreign_key_checks=OFF

According to MySQL documentation on the local foreign_key_checks variable Beginning with MySQL Cluster NDB 7.3.2, setting this variable has the same effect on NDB tables as it does for InnoDB ...
0
votes
2answers
145 views

How to select the latest record of each hour in a day

I want to select the latest record of each hour in a given date, based on the datetime column 'reading_on' in my table , what is the best way to do it. I already tried group by HOUR(reading_on) with ...
0
votes
1answer
34 views

dumping the database from Linux to Windows in Mysql

I have MySql db on Machine A , I have Mysql credentials. And accessing this MySQL from machine B which I have crdential's. But I couldn't take mysqldump from Machine B. Please help me. machene B is ...
1
vote
1answer
509 views

Simple MySQL table copy from one server to another?

this question has kindof been answered in a number of ways. Nothing that quite fits the bill (as far as I understand it). I have MySQL Table X on Server A. I would like an exact copy of this table, ...
0
votes
1answer
99 views

pt-table-checksum - Verify MySQL replication integrity

export/import: $ mysqldump -h6dhdbm01 -p clee2 users > 6dhdbm01-clee2.users.sql Enter password: $ echo $? 0 $ mysql clee2 < 6dhdbm01-clee2.users.sql $ echo $? 0 $ pt-table-checksum: $ ...
0
votes
2answers
29 views

mysql.sock not found on CentOS 7 machine

I am getting the following error when I try to log into mysql from the CentOS 7 terminal of the machine where mysql is installed: ERROR 2002 (HY000): Can't connect to local MySQL server through ...
0
votes
2answers
792 views

How to fix slow LIMIT queries with big offsets without heavily rewriting them?

Imagine a very basic example of your average discussion board. For example: CREATE TABLE threads ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, title VARCHAR(100), PRIMARY KEY (id) ) CREATE ...
0
votes
1answer
1k views

How to perform a while loop on the query result

I created a trigger AFTER an UPDATE of a column in a MySQL database. When the trigger detects a certain value, it should perform some actions. This is what I have at the moment and is working fine. ...
0
votes
2answers
172 views

mysql need help optimizing query

I have a table containing servers logs, and another table containing rules to match certain and drop them. The logs tables contains around 1 million rows, and there are about 20-30 rules. The ...
0
votes
1answer
26 views

Update records that match like regex from another table

I have two tables - ProductCategories and Products ProductCategories --- productid categoryid 1 2 3 4 Products -------- productid productname 1 Apple 2 ...
0
votes
1answer
49 views

GROUP BY Returning Different Totals [on hold]

I have two very similar queries that are returning different totals. The first query, where the total [of subscription_payment.price] is calculated with PHP (and verified in Excel), is the same query ...
0
votes
1answer
51 views

Can I lose data if I use InnoDB? [closed]

During a power failure, is it possible to lose data if I use InnoDB? I remember reading such things.