MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
1answer
8 views

pt-online-schema-change MySQL Error: This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'

I've attempted to alter table using pt-online-schema-change in our master-master replication environment (Percona used as a cluster). It seems, that an operation completed successfully, but inspecting ...
0
votes
2answers
171 views

database structure - complicated requirements

I have a project to build a website but it's complicated and I'm having trouble figuring out what the best way to build the database would be to handle these particular requirements. The site is for ...
0
votes
1answer
63 views

Need to get the first 15 rows from 200k rows of data fast [on hold]

I have 3 tables with a lot of data (each one has 100k rows). I used UNION to get data from that. It takes more than 5 seconds as wait time in server to load the first page. I need only 15 rows from ...
1
vote
0answers
1k views

mysql 5.6 GTID replication 'Got fatal error 1236 from master when reading data from binary log'

I Checked SHOW SLAVE STATUS error is "'Got fatal error 1236 from master when reading data from binary log: ''The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master ...
0
votes
2answers
51 views

Database Design Help: MySql/PostgreSQL Single Database/Multiple Databases

We are planning to build a web based custom business analytics tool using PHP and MySQL/PostgreSQL. In our application, we are planning to upload customer’s data using a Excel sheet and these data ...
1
vote
1answer
24 views

In MySQL how do I grant a user access to a read replica only?

I have some users that I want to grant read only access to my database so I setup a replica server, but I don't want to grant them access to the master. I'd assume that this is a common use case, but ...
0
votes
1answer
14 views

Problem with MySQL Cluster in practice

I am researching MySQL cluster. For the purpose, I do a demo with 4 virtual machines. I have some problems in practice. Topology My topology contains four nodes Node 1: management node, ip address ...
0
votes
2answers
59 views

What is the best way to upgrade a MySQL version in a Linux Server

What is the best way to upgrade a MySQL version in a Linux Server I have database around 14 GB running with 8 -15 GB ram(diffrent deployments) .At present MySQL version is 5.5 and I’m planning to ...
1
vote
2answers
96 views

How to reduce mysql memory used

I'm using MySQL 5.1 and want to upgrade to latest version 5.6.17 (from Windows Installer) But when I installed, it used too much memory, about 420Mb although 5.1 vesion run only 8Mb memory. The setup ...
0
votes
1answer
213 views

Two different MYSQl instances on same machine?

I have MySQL 5.6 installed (Linux). /var/lib/mysql /etc/my.cnf I have a cold backup that I am recovering: /var/lib/mysql_backup /etc/mybackup.cnf In April I was able to get the backup started ...
15
votes
2answers
3k views

Why would SELECT * be magnitudes faster than SELECT foo?

Consider a table of values and hashes, like so: +------------+----------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | ...
0
votes
2answers
89 views

If an account has REQUIRE SUBJECT, does it still need a password?

I'm in the process of setting up SSL-secured replication between two servers. Each server has its own public/private keypair, and the CA cert is just the concatenation of the two public certs, like ...
-1
votes
4answers
68 views

Index on 'GROUP BY' with functions

I have a log table. It logs visitor data (time of visit, browser, window size ...). I want to create reports. I use a query like this (MySQL): SELECT COUNT(*) as raw_views ... FROM logs WHERE ...
0
votes
1answer
64 views

re-enabling a table for mysql replication

We were able to setup mysql master-slave replication with the following version on both master/slave: mysqld Ver 5.5.28-29.1-log for Linux on x86_64 (Percona Server (GPL), Release 29.1) One day, ...
0
votes
1answer
173 views

ib_logfile0 and ib_logfile1 got deleted

After restoring from a percona-xtrabackup without a preparation step, ib_logfile0 and ib_logfile1 were not present in the restore data. Therefor during startup the following message is observed: ...
0
votes
1answer
34 views

Transfer all read request to Slave in Master-Slave MySQL

I have configured master slave on my system (MYSQL). Now, is there is any script or way available or possible to transfer all read request to SLAVE. So, let me make my question here more clear. I ...
1
vote
3answers
2k views

How long will a temporary MEMORY table persist if I don't drop it (MySQL)

I'm using a recursive stored procedure in MySQL to generate a temporary table called id_list, but I must use the results of that procedure in a follow up select query, so I can't DROP the temporary ...
0
votes
1answer
25 views

MySQL Syntax Error When Creating a Trigger

I am trying to create a trigger but I am getting a syntax error and I am not really sure why I am. CREATE TRIGGER Section_Insert AFTER INSERT ON Section -> FOR EACH ROW BEGIN -> INSERT ...
2
votes
2answers
66 views

mysqldump --single-transaction, yet update queries are waiting for the backup

If I use mysqldump --single-transaction, according to the docs it should do flush tables with read lock to get a consistent state and then start a transaction and no writers should be waiting. ...
0
votes
1answer
153 views

Restrict access to rows by usename

In my database i store data of different users (e.g. addresses or invoices) in the corresponding tables. I want to make sure that a logged in user only has access to it's own data in the tables (so ...
0
votes
1answer
49 views

Enforcing minimum cardinality in One To many design?

If we have the following One to Many database design. Team +------+-----------+ | t_id | team_name | +------+-----------+ | 100 | germany | +------+-----------+ FootballPlayers ...
1
vote
1answer
34 views

Why does adding a column to a MySQL table take incredibly long?

I am using MySQL version 5.6 and I have a database that is in total around 110 GB in size. One of the tables, "outputs" is 49 GB in size. I tried to add a simple BOOL column to the outputs table with ...
0
votes
0answers
17 views

wp db issue, need help selecting table values

Hey I am trying to figure out how to select only those items that have same ID but different values in another column. it's Wordpress database structure. My table sample would look something like ...
-2
votes
1answer
58 views

Errors writing MySQL Queries

I'm having trouble writing some MySQL queries. I have been having a lot of trouble getting these queries to work. a) For each class that has at least one qualified professor to teach, list the number ...
0
votes
1answer
20 views

Get the most present elements from many fields

Here is my table: id|item0|item1|item2 --+-----+-----+----- 1 | 10 | 12 | 12 2 | 12 | 68 | 13 3 | 15  | 13 | 10 I just want to get the most present number of all itemX columns. The query ...
1
vote
2answers
143 views

How to duplicate related rows

Tables: Payments with auto-increment ID and one-to-many relationship to Projects such that one payment (say £10) has two records in Projects allocating the £10 between the projects. Now next month, ...
0
votes
1answer
40 views

GROUP BY performance

I have a query like this: SELECT HOUR(datetime) as Hour, COUNT(*) as hits, SUM(clicks) FROM visitors_log_table WHERE datetime >= CURDATE() GROUP BY Hour WITH ROLLUP It works fine. But I ...
1
vote
1answer
40 views

MySQL CHAR(500)?

I have a table in which one of the fields is always going to be exactly 500 ASCII characters. I'd like it to be saved inline in the database and be as efficient as possible, as it will be a big ...
0
votes
1answer
112 views

Nested query not recognize external table

i need to a query that show week by week the total amount of imports of any company and in addition add a ranking position depending on that amount. I've made this query but the problem is the inner ...
0
votes
0answers
23 views

Should I separate my preferences table?

I have a hobby site where users can find other hobbyists of similar interest. Each user can set a series of preferences that get saved into user_prefs. Right now there are 4 types of preferences, an ...
0
votes
1answer
25 views

How to query group for increase or decrease in value

In MySQl I have a table positions and I would like to find out if position of certain app for certain feed in a certain country type has been increasing/decreasing and by how much from the last ...
1
vote
2answers
36 views

MySQL user without SUPER privilege

This may be a simple question and may not be the right place to ask but I couldn't find any useful information from Google. Here is my question. When creating users in mysql using grant all ...
0
votes
1answer
41 views

Replication in mysql with many servers

There are 3 shops. In each of these is a mysql server. I want all servers to synchronize their date if at least one server changes the date. I read a bit of literature and realized that I need ...
0
votes
1answer
15 views

Can I Create a Database and Tables in One SQL Command From PHP?

I tried separating the CREATE DATABASE and CREATE TABLES commands into different mysqli_query statements. The Database gets created but I get an error with: $create_tables = "CREATE TABLE ...
0
votes
1answer
545 views

Inserting query result to another table hangs on “Copying to temp table on disk” on MySQL

I started the process of inserting returned results to another table. The query groups the rows in respect of indexed IDs. This causes 149,000,000 rows to be decreased to 460,000 rows. The query ...
0
votes
2answers
333 views

How to find the same salaries and department names?

Write a query to display last name ,department number,and salary of any employee whose department number and salary both match the department number and salary of any employee using the following ...
1
vote
1answer
569 views

Slow insert with MySQL full-text index

I use a full-text index in a MySQL table, and each insert into this table takes about 3 seconds. It seems that MySQL rebuilds (a part) of the full text index after each insert/update. Is this right? ...
1
vote
2answers
1k views

How to add 'root' MySQL user back on MAMP?

On PhpMyAdmin, I removed 'root' user by mistake. I was also logged in as 'root'. How can I add the user 'root' back, on MAMP?
1
vote
1answer
105 views

MariaDB: Created_tmp_disk_tables high after start

today i looked in the status of my MySQL server which is based on MariaDB and i noticed that Created_tmp_disk_tables is very high (over 168k). I think this is the reason for my high I/O usage. I try ...
1
vote
1answer
1k views

statistics State in MySQL Processlist

I have a busy MySQL Server having query SELECT sys_sess_state, index_state, timeout_lvl, last_queued_dt, last_polled_wait_dt, create_id, create_dt, modify_id, modify_dt FROM PQR_AM_SYSTEM_SESSION ...
3
votes
1answer
2k views

MySQL: Lock wait timeout exceeded

I have a developer that has been trying to alter a large table (~60M rows). Via LiquidBase/JDBC they're running ALTER TABLE foo DROP FOREIGN KEY fk_foo_1; Today while it was running I checked in ...
0
votes
1answer
878 views

How can I set a default session sql_mode for a given user?

I want to have a default session sql_mode that is different from the global sql_mode. I can set it manually, but I'd like to do it automatically. I thought I could add it to .my.cnf, but that ...
0
votes
1answer
36 views

Mysql replication is enabled but stuck at “waiting for binlog to be updated”

I have set up replication between an existing database (server-id=23), and a new one (server-id=51). I have followed the steps in this stackoverflow question, but replication is not happening. On ...
0
votes
1answer
31 views

How to safely add column to replicated mysql database

We have 2 databases, one is a replication used for read only, with around a million records, we want to add a new column to the master db, to one of the tables. Do I just go on MySql workbench and ...
1
vote
1answer
38 views

Method for identifying max_allowed_packet queries generating errors in MySQL

I'm wondering if anyone knows of a good way to track down queries causing max_allowed_packet errors. Even if you turn on general query logging the query isn't logged as the connections aborted ...
0
votes
1answer
86 views

(My)SQL: management and duplication of multiple sub-databases

I have a pretty simple question, but it seems that there are controversies as to how to solve it in the best possible way. Say I have a web app that makes online surveys. My challenge is that each of ...
0
votes
1answer
38 views

Does “Connect” statement for every query indicate broken connection pooling?

there's only one client application running queries against a MySQL instance. After enabling MySQL's general query log (configured to log every query) I noticed that every query starts with a ...
2
votes
1answer
136 views

Avoiding Multiple Queries when Searching for Records Associated with a Set of Records

So, I am sure I have done something really stupid while designing this, and I'm open to schema changes if they'll really help me out. On to the problem: I have a custom shopping cart system (backed ...
0
votes
1answer
50 views

drop table from outside?

A poorly designed script ran on my MySQL 5.6 instance for 24 hours before I stopped it by killing the MySQL command line client and rebooting the machine while the script was still running. The ...
2
votes
2answers
991 views

XtraBackup fails with my.cnf Percona Configuration

After using a percona optimized .cnf file the backup fails Installing my.cnf # service mysql stop (Installed my.cnf) # rm ib_logfile0 # rm ib_logfile1 # service mysql start Mysql creates new log ...