MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
0answers
3 views

MySQL count rows values

I'll give you an exemple of my table: id user_id box_id ip_id name cost 1 1 1 1 name 10 id user_id box_id ip_id name cost 2 2 1 1 name 10 how to count total ...
0
votes
0answers
3 views

mysql error handling stored procedure doesn't catch error

NOTE: I also tried CONTINUE instead of EXIT with same results. I am running mysql 5.5 and am trying to create a stored procedure that sends a variable out based on if it supports full text. ...
0
votes
0answers
1 view

mysql error handling in function

I am trying to do error handling as I can do in stored procedures in mysql but functions don't support to support them. Is there a way I can do something like this? delimiter // CREATE FUNCTION ...
0
votes
0answers
12 views

How do i struct the database/tables like the following in Phpmyadmin?

I have 2 servers, on one server the access6_identical sql file is on the second directory under identical as can be seen here: http://postimg.org/image/cjtdbm9or/ On the other server, it is on the ...
0
votes
1answer
12 views

Joining two tables [on hold]

How do I select all data from one table, based on all data selected from a different table? For example: Table 1: categories +-----+------------+ | cid | category | +-----+------------+ | 1-9 | ...
-1
votes
0answers
11 views

Triggers in MYSQL

I got a requirement like, i need to move the values from one table 1 to another table 2, it should only when table 1 rows exceed the value of 9. once i insert the 10th row to the table 1, old 9 rows ...
0
votes
1answer
16 views

MySQL - LOCK IN SHARE MODE for EXISTS query

I am starting a transaction and I need to check that a record exists, I don't need any values, but I need it to exist for the duration of the transaction for everything to remain valid. It's VERY ...
1
vote
0answers
9 views

Join integration query involving multiple associations

I'd like some assistance in fixing this query. Let me explain the background on what it currently does, and then what I need to integrate. First, here are my current tables. users (performers) table ...
0
votes
0answers
9 views

invalid default value (mysql 5.7) for timestamp field

1067 - Invalid default value for 'shift_start' This worked in mysql <=5.7. I couldn't find any documentation about this...What is the issue with this? CREATE TABLE `phppos_register_log` ( ...
1
vote
0answers
22 views

Issues with MySQL installation

First of all, excuse the length, this post is very long, and needs some background, second; it has been hard for me discerning the signal from the noise, please bear if some of this is not relevant: ...
2
votes
1answer
14 views

MySQL database design to sort and filter air traffic data

I am struggling to design the correct database / structure for what I'm trying to do. Let me try and explain the situation. Background We are a group of enthusiasts who monitor air traffic via ...
4
votes
0answers
26 views

Counting show variable result in mysql

I am reading this article and there doesn't seem a way to do something like this: SELECT COUNT(*) WHERE SHOW VARIABLES WHERE Variable_name = 'innodb_ft_cache_size' I can do the below but this ...
2
votes
1answer
19 views

MySQL users not visible after import

I have a Percona MySQL database 5.6 running in a linux machine. I have created new users called test1 and test2. What I did was I imported mysql database from my test setup to this new setup. After ...
2
votes
1answer
28 views

MySQL connection process slow for non-root user

I'm using Percona server 5.6 on Linux. When I connect to MySQL from the command line as the root user, it connects immediately. I have also created a new user in the database, but when I try to ...
0
votes
2answers
15 views

Percona XtraDB - Random deadlock

Randomly my database gets locked completely and queries hang and they pile up until it gets "Too many connections". Below is my mysql log file, I couldn't find anything related to the error system ...
1
vote
2answers
50 views

MySQL - How to make a DATEDIFF in years?

This is my first post (and English is not my mother tongue) so I hope I won't screw this post up. I am new to MySQL but I learned a lot with 100+ youtube tutorials and your numerous posts. I designed ...
0
votes
0answers
15 views

mysqldiff utility on all tables

I am using the mysql diff tool at http://dev.mysql.com/doc/mysql-utilities/1.3/en/mysqldiff.html It works great but I have to do this for each table mysqldiff --difftype=differ ...
0
votes
1answer
19 views

MYSQL count time between each in/out and show total

I am having an issue with my time clock query. Basically users clock in/out several times per day (can clock in/out unlimited times per day), and it is counting the time between the first clock in and ...
0
votes
3answers
25 views

Unique full text index?

Is it possible to create a UNIQUE FULLTEXT index? I am using a mysql diff tool that is complaining that I am using duplicate indexes. I have a column that needs to be unique but also used for full ...
0
votes
1answer
22 views

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 ...
1
vote
1answer
31 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
2answers
18 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
23 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
21 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 Trying to drop the index: ...
0
votes
0answers
18 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
26 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
20 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
1answer
12 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
14 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
62 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
1answer
12 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
10 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
7 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
30 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
25 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
47 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
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
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
5answers
28 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
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
2answers
37 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
68 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
25 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
61 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 ...