MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
0answers
1 view

Redundant to have index + full text on same column?

If I have a full text index is it redundant to have a regular index on the same column? I am asking this because I need to support 5.5 and 5.6 at the same time and am using a stored procedure to ...
0
votes
0answers
13 views

Adding restaurants in database

I wanna store some vendors (like McDonald ,etc) and it's branches in a table. a vendor may,or may not have branches. I have created two tables: 1- A vendors table which have name,logo, etc. 2-A ...
0
votes
1answer
7 views

Amazon AWS RDS Aurora Row size too large (VB5)

We're trying to import our vbulletin 5 database into RDS/Aurora and getting this: ERROR 1118 (42000) at line 5733: Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ...
0
votes
0answers
21 views

What's better and faster — MySQL or XML based Filesystem [on hold]

Good day, my boss is trying to push a project to be used with a CMS called "Weblication". Weblication is a PHP Framework using only files in XML and processing everything with XSLT and PHP. So all ...
0
votes
1answer
12 views

ERROR 1091 (42000): Can't DROP 'created_date'; check that column/key exists

mysql> alter table bill_item add index idx_comp(created_date,product_id); Query OK, 0 rows affected, 1 warning (1 min 2.62 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql> alter table bill_item ...
0
votes
0answers
15 views

MySQL How to get the nearest child for every parent in a single query?

MySQL, I need to get the nearest location for each organization. Between organizations and locations is a one-to-many relation. One organization has many locations. The GPS coordinates are stored in ...
0
votes
0answers
22 views

MySQL - Optimizing Multiple WHERE Conditions

I have to run the following 2 condition query against a table: SELECT * FROM serial_numbers WHERE type = 'book' AND serial NOT REGEXP 'ISBN(?:-13)?:?\x20*(?=.{17}$)97(?:8|9)([ ...
0
votes
0answers
18 views

Switching from MySQL to MariaDB resulted in a high number of transactions

I switched from MySQL 5.5.44 to MariaDB 10.0 on a server and the number of transactions went through the roof without any increase in website traffic(the website traffic is the only thing that uses ...
0
votes
0answers
10 views

MariaDB: Querying a timestamp partition

I'm having difficulties getting my queries to make use of the partitions when I'm selecting. They need to be able to do > sometime > sometime and between sometime and anothertime. However, if I ...
0
votes
0answers
10 views

Can't bring up second MySQL Galera node - 2015-08-27 21:53:13 32140 [ERROR] WSREP: Failed to read 'ready <addr>'

I am trying to migrate from MySQL m/m replication to MySQL Galera. After successfully installing MySQL Galera on node no. 3, I configured it as the first node in the cluster and have imported the ...
1
vote
1answer
56 views

Speed up MariaDB?

I have a Wordpress Woocommerce store using WP All Import Pro to run automatic imports on a 15 minute cronjob schedule. It's usually trying to update 400 to 500 items from our supplier every 15 ...
0
votes
0answers
4 views

How to preseve Execute permissions for MySQL procedure with alter procedure (Drop/Create)

In MySQL you can not alter procedure rather you have to perform drop/create procedure to alter the body of the procedure. The problem is doing drop/create also removes the execute permissions given on ...
1
vote
1answer
14 views

MySQL get previous record and compare

Need to make a Comparable between the records of this table and take the total times the value calledstationid 42 preceded the value 34 (this is a system log table and calledstationid is not an ...
0
votes
0answers
9 views

MySQL - include a default value in LOAD XML INFILE statement

I have the follwing table: CREATE TABLE `tab1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `DEFAULT` VARCHAR(25) DEFAULT NULL, `ITEM` varchar(25) DEFAULT NULL, `TIMESTAMP` TIMESTAMP ...
1
vote
0answers
6 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
30 views

selecting same column twice for different value

I have a table like this; event_id pair versus score 1 1 11 100 1 1 12 0 ... 1 11 1 0 1 12 1 50 Now I want to ...
0
votes
0answers
16 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 ...
0
votes
1answer
28 views

Fastest way to do bulk import in MYSQL

I have got a question about the fastest way to copy a large number of records (around 10 million) from 1 table to another in MySQL. The target table has 1 primary key, 4 indexes and 3 foreign key ...
-4
votes
0answers
22 views

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

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
0answers
1 view

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, ...
2
votes
1answer
39 views

Deleting large amounts of cascading records with minimal locking

I'm looking for a strategy to purge large amounts of logically deleted data from a MySQL database. The deleted records are in a relatively small table, but are referred to with many ON DELETE CASACDE ...
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 ...
0
votes
1answer
35 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
1answer
48 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
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 ...
1
vote
4answers
27 views

Anyway to identify mysql slaves have read upto which binary logs

I have multiple slaves reading from one master.. But few of them are keep disconnecting due to network issue. Is there any way that i can make sure before setting up the bin log purging script on ...
1
vote
2answers
32 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
2answers
35 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
1answer
63 views

Matching data in two tables

I'm teaching myself SQL and I ran into a question that may be related to joining tables. I'm just a bit unsure. Say, you have two tables, one listing the customer's creditcard_number, and one listing ...
0
votes
0answers
24 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 ...
1
vote
2answers
57 views

Select highest id of each type in mysql

I am creating a database that keeps track of backups on our various servers. The query that I am using actually seems to work for the most part. The only issue is that it isn't getting the highest ...
0
votes
1answer
22 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
68 views

Slow SQL query with LEFT JOIN

What my query is doing: I have two SQL tables and I want to copy datarows from the one table to the other table. I am doing this for statistics. The source table includes all current ...
0
votes
1answer
13 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` ...
0
votes
0answers
31 views

SELECT … INTO variable results in NULL or idk

you can find all the relevant code on pastebin. The problem: the variable to_spend stays default. If I leave the default 0, it is 0, if change it to NULL, it is NULL. So the statement if(to_spend ...
0
votes
2answers
31 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 ...
1
vote
3answers
47 views

Users vs. People - where to keep the email address?

I'm designing an app similar to Facebook, called Headtome. I want to have users that create public profiles, including email addresses. Then, Mary can choose to message John, and that message goes ...
1
vote
1answer
15 views

MySQL queries taking very long/not finishing

I just started working with databases and have a problem with queries taking very long. My table has the following form CREATE TABLE table (column1 INT(10) AUTO_INCREMENT PRIMARY KEY, column2 ...
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; ...
2
votes
1answer
28 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
1answer
20 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
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
41 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
32 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, ...
0
votes
0answers
15 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 ...
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 ...
1
vote
0answers
53 views

Space usage when copying

Short version Copy table took up more space than I expected or tested Why would that be? Could it be the replication / binlog? If so, what then is written to the binlog during this query: INSERT ...
0
votes
0answers
21 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 ...
0
votes
0answers
23 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 ...
0
votes
1answer
31 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 ...