MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
0answers
3 views

How to implement a query insert into BEFORE INSERT trigger syntax

I want to insert data using trigger into 2 tables. I want to implement my query insert to a before insert trigger. Since I was newbie for using MYSQL, I'm confused how to implement it. insert into ...
-1
votes
0answers
22 views

Create Procedure

CREATE PROCEDURE validateZuoraFormat ( IN client varchar(100)= ' ', IN table varchar(100)= NULL, IN type varchar(100) = NULL, IN debug bit = 0, IN clientReport bit = 0, ...
0
votes
0answers
11 views

MySQL: How to count non-unique rows in a multiple criteria COUNT query?

I want to calculate, in one query, the percentage of researchers (chercheurs) that have co-signed written publications (between two dates, only for a type of publications and only for researchers ...
0
votes
1answer
13 views

Creating procedure in mysql

I am trying to build a procedure that i would use to check my data format before processing it to the net stop and i need this procedure to check the format of my file. So what i need is, my database ...
0
votes
1answer
15 views

Are mysql global variables values in bytes?

When I run the following in my mysql terminal mysql> SHOW GLOBAL VARIABLES LIKE 'tmp_table_size'; +----------------+----------+ | Variable_name | Value | +----------------+----------+ | ...
1
vote
1answer
27 views

Binary log backups in MySQL

I'm running a master-master replication with read-only second master. Now since my binary logs are not getting replicated in my passive master. Should I need to enable --log-slave-updates? If I ...
0
votes
1answer
15 views

How to use MySQLTuner on Amazon RDS

I've installed MySQLTuner locally and it works on my local database. When I try to run it on my Amazon RDS db it does not, it says my login credentials are invalid. $ perl mysqltuner.pl --host ...
-4
votes
0answers
15 views

sudo /usr/local/mysqlcluster/scripts/mysql_install_db --user=mysql --datadir=/var/lib/mysql-cluster --basedir=/usr/local/mysqlcluster --no-defaults [on hold]

/usr/local/mysqlcluster/scripts/mysql_install_db: 1: /usr/local/mysqlcluster/scripts/mysql_install_db: /usr/local/mysqlcluster/bin/my_print_defaults: not found Installing MySQL system tables... ...
2
votes
1answer
16 views

Which is better MySQL search strategy, varchar x 2 or text column?

I have a MySQL database for an SMS application. For the sms_msg column, I initially setup varchar(255) expecting SMS to be short anyway. Now I realize that's not enough and some messages are getting ...
1
vote
1answer
20 views

Is it safe to run optimze on mutliple tables in parallel in mysql (percona)

I have a slave on which no query is coming. Lot of data has been archived and deleted from almost all tables. Size of tables vary from 80Gb to few Mbs. And there are around 130 such tables. Mysql ...
0
votes
1answer
28 views

MySQL backup practices

I have a MySQL master-master replication with second master in read-only mode. My questions reg backup are: I have 10 databases so should I take daily backup as one dump containing all the ...
2
votes
1answer
37 views

GROUP BY rewrite to subqueries with easily interchangeable AND OR conditions

Tables: content tag_map extrainfo |id|title| |id|tagid| |id|lat|lng| |--|-----| |--|-----| |--|---|---| |1 |Lorem| |1 |8 | |3 |0,4|0,1| |2 |Ipsum| |2 |9 | ...
0
votes
1answer
26 views

Can I create a stored procedure in mysql using a txt file?

My server OS is Red Hat 6 with MySQL. I'm using this line in a .sh file to run some jobs on the server: mysql -u root -p[password] databasename < MySQL_JOBS_SENTENCES.txt The content of the .txt ...
-1
votes
2answers
47 views

How can we create a table from stored procedure?

CREATE DEFINER=`root`@`localhost` PROCEDURE `create_tb_one`(In tableName varchar(255)) BEGIN CREATE table tableName( ID INT NOT NULL, SNAME VARCHAR(100) NOT NULL, ...
1
vote
1answer
40 views

Table does not exist even if there is one

I have MySQL master-slave replication setup in Amazon EC2. For the sake of resilience, I have set up LVM on RAID 10 array, on both master as well as slave. I have been using ec2-consistent-snapshot ...
0
votes
1answer
29 views

MYSQL INSERT takes too long and each next portion takes more than previous

First 1000 takes 8 sec, next 1000 takes 12 sec and each next more and more. I have two databases with 1,5 million rows each. My goal is to fill empty table jf first db with relationship links. ...
1
vote
0answers
24 views

MySQL - Truncated incorrect datetime value on update statement

I have an application which produces log files. These log files include a Timestamp field in the format 2015-08-25T09:35:01 UTC. (there will be apx 60 logs produced at 0.25 GB per day). I need to ...
3
votes
2answers
80 views

Can I improve the performance of this MySQL query by adding an index?

I ran SET GLOBAL slow_query_log = 'ON'; in MySQL and this appeared in the log: SELECT U.*, DATE_FORMAT(U.TIMESTAMP_X, '%d.%m.%Y %H:%i:%s') TIMESTAMP_X, IF(U.LAST_ACTIVITY_DATE > DATE_SUB(NOW(), ...
0
votes
1answer
47 views

MySQL Trigger Update

How do I update 'emailupdate' date with a trigger when 'email' is updated through a script? +------+----------------+--------------+ | id | email | emailupdate | ...
0
votes
1answer
24 views

session sql mode + default sql mode

I am testing my application in mysql 5.7 and have some code that doesn't work with ONLY_FULL_GROUP_BY ON. I will fix this in a later release. I found a way to turn this off for just my application by ...
2
votes
1answer
47 views

MySQL BEFORE INSERT trigger with IF conditions

In my BEFORE INSERT trigger, I select data, then I check the value if it's null then I do insert data into table A then else into table B. I select data from a table in another database called ...
1
vote
0answers
35 views

Is not having 'named' timezone support common in MySQL?

I'm trying to set the timezone the database uses using: "SET time_zone = 'UTC'; Whilst this works on my development server it doesn't work on my staging server; I get this error: General error: ...
0
votes
0answers
27 views

How to synchronize same table in two MySQL databases

I have two databases with same table name and scheme. For short, let's assume that both have ID, Name, Surname columns. Database1 is a production one and deleting lossing data from its table is not. ...
-1
votes
0answers
18 views

Nice web GUI for inside of your database server? [on hold]

I have a little mysql server running. It is used for accounts and stuff that belongs to the user. I would like to have some web GUI thing to login on the server and have some inside in stuff like how ...
-1
votes
0answers
17 views

MySQL count rows values [on hold]

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
1answer
19 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
1answer
9 views

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 ...
1
vote
0answers
22 views

Organizing databases and tables 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: On the other server, it is on the parent directory as can be seen ...
0
votes
1answer
22 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
vote
1answer
20 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 ...
2
votes
0answers
14 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
19 views

invalid default value (mysql 5.7) for timestamp field

EDIT: When updating an existing database from mysql 5.6 and executing: UPDATE phppos_register_log SET shift_end = '2015-01-01 00:00:00' WHERE shift_end = '0000-00-00 00:00:00'; This produces: #1292 ...
4
votes
1answer
87 views
+50

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: ...
6
votes
1answer
42 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
31 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
20 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
32 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
32 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
62 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
18 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
25 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
27 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
28 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
38 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 ...
1
vote
3answers
49 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 ...
-1
votes
0answers
28 views

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

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
29 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
22 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
30 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
34 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 ...