MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
1answer
52 views

How to improve this query so it'll run quicker

I've got this query: SELECT m.vlanID, v.vlan_name, m.interfaceName, count(m.macAddress) as mac, n.nd_name, p.interfaceDescription, p.interfacePortType FROM macs m, network_devices n, ...
1
vote
0answers
9 views

MySQL/Percona Audit Log Plugin Not Working

I've been looking to implemement the Percona Audit Log plugin. So far I have installed it and configured it, and it seems to be working ok. Except, I have installed it on a slave Database as I don't ...
0
votes
1answer
37 views

How to recover deleted records by mistake in Mysql

I am using Mysql.And by mistake I deleted very important info.Is there a way to recover or even to see the deleted info over a month ago?
0
votes
2answers
59 views

How to know which user update/insert or delete in MYSQL database?

Using the triggers I could make the establishment of a historic recordings in MYSQL tables where i detect all changes in my database. My problem is that I want to know which user made ​​the change in ...
0
votes
0answers
20 views

Line terminators \r and \n

As i am working on this project i have couple of questions about line terminators and how to know which one to use when. 1) when i load data into two tables using just "\r", all desired data is ...
-4
votes
0answers
28 views

when I am executing this sql query it is taking 3 min time so i want to reduce that [closed]

SELECT p.NAME AS Commodity ,COALESCE(allocation.alloted_qty, 0.000) AS Allocated ,COALESCE(inward.inward_quantity, 0.000) AS Issued ,sales.quantity AS Sold ...
0
votes
1answer
1k views

How can I get Performance Statistics to work in MySQL Workbench?

I have a complex query that is performing in subsecond time on my laptop, while the same query takes 70 seconds on my production server under no load against an identical schema. The EXPLAIN plan is ...
0
votes
0answers
3 views

Waiting for table level lock on wordpress site

We are experiencing issues with mySQL loading 100% of the server's CPU. While checking the running processes we found many of the following: Waiting for table level lock | SELECT option_name, ...
1
vote
0answers
7 views

Is it possible to cascade delete a column value? [duplicate]

So say I delete catID from table A and table B has the same foreign key value? It's hard to explain so I'll try and show you with the two images below: items table Is it possible to delete the ...
1
vote
1answer
398 views

Mysql foreign key cascade delete

Does mysql have a way to delete all sub links if a row was deleted from a "parent" table? Example table setup is below, the issue I am trying to avoid is having to maintain a script that deletes the ...
0
votes
2answers
34 views

What storage engine do I need - Mysql?

I have designed a database and came to the point of actually deciding which storage engine is better for my case. I estimate that during the first few years I would have around 100,000 - 500,000 ...
0
votes
2answers
40 views

Setting up MySQL master-slave replication

I am trying to setup master-slave replication on MySQL 5.6. When I update my database in the master, the change is not reflected on the slave. When I show the process list on the slave it shows this ...
1
vote
0answers
12 views

fast execution analitycs database with thousand rows to displaying in php [migrated]

i've table with a thousand rows and i want to creating analitycs with chart display my front end php my table structure is and how i display this data : by user_agent column i display operating ...
0
votes
1answer
23 views

Slow query - just error count

I deal with slow queries in whole project. I indexed heavily but the results are nowhere. I had a lot of logs (thousands per minute) from SCADA systems and I am trying to count them for each SCADA ...
1
vote
2answers
36 views

Disaster recovery solution for MySQL

I have set a Master-master replication with the second Master running as read-only. This master-master setup is on the same location. Now I will have to setup an DR for this production at a different ...
0
votes
1answer
15 views

Fulltext Search Doesn't Match a Hashtag

We maintain a table that looks like this to perform search queries on our forums database. I'll draw your attention to the FULLTEXT columns: CREATE TABLE `forums_posts_search` ( `post_id` ...
1
vote
1answer
1k views

MySQL service stops after trying to grant privileges to a user

I am using MySQL 5.6, I was trying to create a super user for specific database but I got ERROR 1396 (HY000): Operation CREATE USER failed for 'superuser'@'%' Then I thought maybe there is already ...
3
votes
2answers
1k views

How much data is needed to show MySQL Cluster's performance scaling vs. InnoDB

I am evaluating MySQL Cluster as a possible replacement for an InnoDB schema. So far, I have tested it with 10s of MB of data, and found MySQL Cluster slower than InnoDB; however, I have been told ...
5
votes
3answers
1k views

Insert Speeds for large batches

In my application, my INSERTs seem to be taking a major chunk of the time. I have a large number of objects in memory (~40-50,000) which i want to insert into a table. Lets take a sample table ...
3
votes
1answer
3k 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
117 views

Mysql - unknown variable 'table_cache=64'

I use Mamp Pro 3. I upgraded Mamp's MySql to 5.6.24 using this script https://gist.github.com/tobi-pb/b9426db51f262d88515c. After that I ran the Mamp Pro and MySql cannot be started. So I looked the ...
0
votes
0answers
27 views

How to view the data from a .mysql file?

I have a .mysql database file. I need to open it and view the data. I've MySQL Server & MySQL Workbench installed into my Ubuntu 14.04. Now how do I open the .mysql file and view the data inside ...
0
votes
2answers
54 views

Remove duplicates from multiple joins

I'm using WordPress $wpdb->get_results for this query (it is simplified for question purposes): SELECT customer.id, customer.name, address.address appt.appointment_date, pay.date FROM customers ...
9
votes
5answers
49k views

Matching single column against multiple values without self-joining table in MySQL

We have a table that we use to store answers to questions. We need to be able to find users that have certain answers to particular questions. So, if our table consists of the following data: user_id ...
0
votes
1answer
50 views

Mysql Slow when join query running increaed

Our Mysql dedicated server box always have around 40 - 80 active user connection , so the query speed very good and Cpu usage low . (around 4 - 8 core from 24 core) but sometimes when some wordpress ...
1
vote
2answers
118 views

Query to Access a specfic data in the row using python

Am using wordpress and phpmyadmin for handling my database Below is the code I use to view entries on my form using python. In the form I ask users to check/uncheck specific questions. It works as ...
0
votes
1answer
69 views

compare results between two select queries mysql

i'm making test application where users will input their select queries in input box and the application have to compare them if they are identical with "correct answer" query. "Correct answer" query ...
0
votes
0answers
24 views

Is mysqlreport still used?

The mysqlreport main page doesn't exist anymore, and the tool has not been updated since 2008. However, the tool is still available via package managers in Linux. On the other hand, it's difficult to ...
2
votes
1answer
29 views

How to create a relatively complex MySQL query

I'm not good with MySQL but have been trying to create a query that would join from three tables where: Table 1 (macs) is like this: ipAddr, vlanID, macAddress, interfaceName Table 2 ...
0
votes
0answers
7 views

CALL MySQL Stored Procedure in a C application crashes the application with segmentation fault

I want to use CURSOR of MySQL in an application written in C. I created a stored procedure CREATE PROCEDURE curdemo(IN id INT) BEGIN DECLARE a INT; DECLARE flag BOOL DEFAULT FALSE; ...
0
votes
0answers
35 views

MySQL: Slow Query Performance - Parameter Sniffing?

For a personal weather project, I am trying to define and run the following stored procedure: DROP PROCEDURE IF EXISTS history_between$$ CREATE PROCEDURE history_between (IN date_start DATETIME, ...
1
vote
2answers
889 views

Trying to recover/import/transfer database from MySQL data dir

I used phpMyAdmin->export to create a backup of my database but I have now discovered it did not export the whole database, some tables was missed/refused. However at the time of doing the export I ...
2
votes
0answers
15 views

Mysql History Trigger Auto Increment bug or problem

I want to log history of a table, I created a history table and triggers for update,insert and delete operations. I do my test on realtime replication slave. When a record inserted master's main ...
0
votes
0answers
46 views

Mysql query performance is really bad

I have a very simple query in MySQL which is taking hell lot of time. It ran for 18 hours and didn't complete Update OrderDetail OD Join TEMP_PARTMASTER_LATEST_UPDATES PM Using ...
0
votes
0answers
18 views

Using mysqldump and command line client

Very new to db admin. I am using Windows Server 2012 R2 in a testing environment, trying to teach myself basically. Looking into db replication. I have a very simple and newbie question. I am ...
1
vote
1answer
30 views

Losing the last column of data

After Loading data into the table and running a query on it, it tends to lose the last column of it. Like somewhere in import process it lost it. The file i was using was UTF-8 .csv format. A Is ...
1
vote
1answer
34 views

Best way to store data in tables

I've been thinking on how to store data efficiently in database tables, I'll give the examples in visual look. See pictures below. I've come to think to store data in database using compressed values ...
2
votes
2answers
2k views

Improve MySQL Query Performance - Too many records

I am using MySQL RDMS for a contact management system. Now a days my database grew in size and contains more than 1 million records. Checking duplicate phone numbers became a big issue during ...
2
votes
1answer
29 views

How to:: Master-Master replication

I have setup Master-Master replication on two servers. Now, my question is that if I need to have the second master as only read-only, what should I do? I tried doing mysql> flush tables with ...
0
votes
1answer
29 views

replication filtering replicate-rewrite and do-table Mysql 5.6 (innodb engine )

i have a master / slave replication setup based on statement based replication. On master database name DB1 , i have 5 tables and i want to replicate only two table on slave's database name DB2. Also ...
0
votes
0answers
24 views

Mysql terribly slow on Xampp

I've looked if it was Apache, but on websites without a database, it loads pretty fast, but on my Joomla site the localhost takes like 15 seconds or more to load a page. I edited the hosts file of ...
4
votes
1answer
52 views

identify gaps and islands

I have a database that has the following structure +---------------------+------------+---------------+ | timestamp | EngOilP_sd | CompOilLVL_sd | ...
1
vote
3answers
62 views

MySQL restore old backup over new data

We have a MySQL backup. Did some changes on our database and some data was lost. In the mean time new records were added. We have to restore the mysql backup over our latest data. Some of the tables ...
0
votes
1answer
33 views

Insert but Don't Commit MySql

I am pretty new to MySql and Databases, and I was wondering in python sometimes I forgot to commit after an insert, and then when I do then next insert and commit that one the, auto-incrementing id ...
0
votes
0answers
24 views

Doubt on Master-Master replication

I'm setting up a master-master replication where in the second master is only read only there by reducing the issues of conflicts. My doubt is that to reduce the downtime when my master goes down I ...
3
votes
1answer
814 views

SQL set profiling

I'm setting up an automatic database backup for a website. When I try to do a backup it comes up with the error" MySQL error: The 'SHOW PROFILE' feature is disabled; you need MySQL built with ...
0
votes
1answer
279 views

MySQL 5.6 replication causes 'waiting for table lock'

All of the sudden queries on slave server stopped with status "Waiting for table level lock" I restart mysql service and stop replications and locking does not show up anymore. Once I turn ...
3
votes
3answers
55 views

MySQL adds broken comment script after each line

Every time I try to create a table using MySQL Workbench 6.3 CE it adds "broken" comments at the end of the script. Example: I am trying to create a table called "user". This is what the table ...
2
votes
0answers
27 views

Getting error 1064 in automatically-generated code from MySQL workbench [duplicate]

I have started using mySQL workbench (Version 6.3.4.0) and I have used the EER Diagram tool to design a schema. So far, so good. However, when I try and run the Forward Engineering, I get the error ...
0
votes
1answer
948 views

MySQL workbench: Cannot Connect to Database Server (ssh)

General pointers would be appreciated -- it's likely I'm misunderstanding the mechanisms involved in a remote connection through ssh. I can use ssh to connect remotely, and then, through the CLI, ...