MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
0answers
16 views

Validating a format

I am trying to create a procedure that would validate the format of the file and give me errors if the format is wrong. i have a data dictionary that has al the parameters in one schema and the data ...
0
votes
0answers
11 views

Amazon RDS CPU is about 55-65% after optimizing some tables (Mysql)

I was having high RAM consumption and, looking for a solution, I executed the script mysqltunner to monitor possible optimizations in the Mysql database. Its first advice was I had to run OPTIMIZE ...
1
vote
1answer
15 views

Records not getting replicate in Mysql 5.5 to MySQL 5.6

1. I have a MySQL 5.5 server (community edition) on one Linux server which is master. 2. I have a MySQL 5.6 server (community edition ) on another Linux server which is slave. ...
2
votes
2answers
34 views

MariaDB server with 80K-100K DataBases

BACKGROUND : We have an environment where there is a new DB created almost every minute and it will be Dropped after few days or weeks (maximum 1 month) such that the space used is about 300GB~400GB ...
0
votes
2answers
37 views

Mysqldump treats some table as View

When doing a mysqldump, some tables are being skipped because mysqldump treats them as View rather than Table although they're 100% tables. So for some tables it looks like -- Retrieving table ...
0
votes
1answer
12 views

Of the multiple cnf files in /etc/mysql/conf.d/ dir, which would be the last one to be read (and will enventually take precedence)?

For some reasons (and only for some days to come) we are going to have 2 cnf files in /etc/mysql/conf.d directory, apart from the default one /etc/mysql/my.cnf. Now I know this can be disastrous, and ...
0
votes
2answers
14 views

Optimization of variables in my.cnf (mysql) for Drupal

I just get a Dedicated Server running UBUNTU and I need to optimize the variables in my.cnf for Drupal 7. I understand that this is tailed job. Then Is there any tool to help me to do this? I am a ...
-4
votes
0answers
13 views

nested set model with multiple trees in multiple tables [on hold]

I have develop a MLM (multi-level marketing) system and so use nested set model in hierarchy users. A tree in MLM may have over 1,000,000 rows. It also should have multiple tables where each table is ...
2
votes
1answer
36 views

Best database design for website ranking

I am working on a project in which I need to rank websites in two way - Global rank and their rank in a particular country. There are various factors based on which both are calculated. There are ~200 ...
1
vote
1answer
13 views

set global sql mode but showing a different mode

I have this command to run in my docker file mysql -e "SET GLOBAL sql_mode = 'STRICT_ALL_TABLES';" But when i check the mysql mode it shows as mysql> select @@sql_mode; ...
1
vote
2answers
34 views

MySQL - Small table popping up in slowlog, refuses to use index

So I have two very very small tables (~20 rows) that are hit very frequently and pop up in the slowlog (I'm also logging tables not using indexes). I tested using a large multipart index (that's just ...
1
vote
1answer
22 views

Why am I getting error 1064 when creating a table? [duplicate]

Here is my DDL statement: create table if not exists 'fmrecruit'.'resource_type'( 'ID' bigint not null auto_increment, 'type' bigint null, 'first_name' varchar(30) not null, 'middle_name' ...
3
votes
2answers
29 views

Storing html in mysql text fields, or include files? (html formatted articles)

I'm wondering if I should store long html formatted articles in my MySQL database, or if it would be better to store a reference to a file, and include that instead. It is not just paragraphs, but ...
0
votes
1answer
28 views

MariaDB/InnoDB: Temp file write failure

When attempting to alter a table to enable compression by modifying the KEY_BLOCK_SIZE I received the error: ERROR 1296 (HY000): Got error 64 'Temp file write failure' from InnoDB The command ...
1
vote
1answer
19 views

Recording history of ranks

Once per day, this is being executed to rank everyone in the database based on points. SET @r=0; UPDATE ranks SET previousRank=rank, rank= @r:= (@r+1) ORDER BY points DESC; The field ...
3
votes
1answer
22 views

Storing prices with price history and different specificity (global, user group, single user) in MySQL database

I need to store prices with their history. A price can have different specificity. It can refer to a single user (most specific), group (less specific) and product price (global - least specific). ...
0
votes
0answers
13 views

Would it be a good idea to create a system using MS Access as a front end and MySQL as a backend? [on hold]

I've recently been working on a MS Access project. I had to re structure the DB as almost nothing was normalized and some issues were encountered. After finishing the DB ...
3
votes
1answer
28 views

Mysql suddenly crashing after several months

I'm away on leave and mysql on an EC2 (m3.medium general purpose) based server I have The displeasure of managing has suddenly started crashing. I have very intermittent internet connectivity through ...
0
votes
0answers
42 views

How come MySQL LIKE is faster then FULLTEXT?

So, I was trying to optimize my MySQL queries, but found out that i'm actually doing this wrong. I've changed my query from using SELECT * FROM test WHERE tst_title LIKE '1%' To: SELECT * FROM ...
1
vote
1answer
26 views

Table replication with extra TIMESTAMP column on slave

I have a table on master and same table on slave with an extra column like InsertTime timestamp DEFAULT CURRENT_TIMESTAMP I want to keep record of time when there is update on slave. Replication ...
1
vote
2answers
45 views

Duplicate Same Table for Multiple locations

We have an "appointments" table with following schema. appointments(app_id,.....,app_type,location_id) app_type - type of appointment location_id - keeping the location for multiple location setup. ...
0
votes
1answer
26 views

Importing MySQL tables from other database in live site with mysqldump can cause trouble?

Scenario: I want to replicate MySQL tables from one database to other database. Possible best solution: May be to use MySQL Replication feature. Current solution on what I'm working as workaround ...
1
vote
1answer
63 views

Same SQL stall on server but work on local ( on slower machine )

I have this strange issue that because of my limited knowledge about Mysql I cannot understand, I will really appreciate if somebody can enlighten me and point me to how to debug the issue. ...
0
votes
0answers
24 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
41 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
24 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
16 views

Creating procedure in mysql [on hold]

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
16 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
30 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
30 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
23 views

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

when iam using --force also not working ERROR: /usr/local/mysql-cluster/scripts/mysql_install_db: /usr/local/mysql-cluster/bin/my_print_defaults: not found Neither host 'administrator-HP-Pro-3330-MT' ...
2
votes
1answer
35 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
3answers
49 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
29 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
58 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
28 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
53 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
32 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
29 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
85 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
58 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
25 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
49 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
43 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 ...