MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
1answer
10 views

Cannot add a column to an InnoDB table in Mariadb

I have this InnoDB table with less than 10 rows in mysql 5.5.40-MariaDB on Linux (x86_64). +-----------------+--------------+------+-----+-------------------+----------------+ | Field | ...
0
votes
1answer
7 views

Joining WordPress Comments with Post Titles from MySQL DB

I'm working with a wordpress database and need to get some data on the comments. The goal is to get comments from a decommissioned site. I have the database running on my local system and can ...
0
votes
1answer
15 views

Advisable number of foreign keys in a single table

I was wondering, keeping performance and optimizations in mind, while designing the database, is there any advisable number of foreign keys you should keep in a single table? At present, few of my ...
0
votes
0answers
12 views

MySQL TRIGGER with test on minute datetime

I try to create a TRIGGER on MySQL 5.6. In this TRIGGER, I test if the minute of my Date is equal to 0, 15, 30 OR 45. So I test this : IF (MINUTE(NEW.tb_date) IN (0, 15, 30, 45)) THEN ...; END ...
0
votes
1answer
40 views

Is making joins between different table spaces slower?

In MySQL and PostgreSQL, is making joins between different tablespaces inherently slower, compared to joining tables in the same tablespace?
-1
votes
0answers
21 views

query explain saidd row is a few but it took an hour to load

i have a query down here select if ( month(td.date) = month('2011-01-01') and year(td.date) = year('2011-01-01') and month(td.date) = month('2011-01-02') ...
-1
votes
0answers
8 views

Access denied for user to database 'mysql' in amazon RDS

I am trying to restore the 'Mysql' database of my master in the DR and i am facing access denied issue. I have also tried to restore only user table with no luck. Tried searching google but dont see ...
0
votes
0answers
6 views

On not public server: Host name example.com could not be resolved: Name or service not known

This is not a duplicate (please read carefully) Digging in mysql logs of a production server I found a few entries with the error above, all pointing to the same host name. Each entry appears ~30 ...
0
votes
1answer
14 views

Mysql join not working

I have database with two tables: logs & src_info mysql> describe logs; +-----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | ...
0
votes
0answers
4 views

Mariadb on Centos 7 not changing max connections based on my.cnf

The following configuration in my.cnf is not being followed. Max_connections go back to 151. please help as i have 16gb of dedicated ram that i would like to use properly. [mysqld] ...
1
vote
0answers
18 views

mysqldump: Got error: 1045: Access denied for user 'root'@'localhost' (using password: YES) when trying to connect [duplicate]

I am troubleshooting a backup script (bash), and I have limited working knowledge of the current configuration since the last 3 predecessors did not leave any documentation. I do know that my server ...
1
vote
1answer
31 views

MySQL Data Files Missing?

We are upgrading our website and I was checking on the size of the new database compared to the old one and was really surprised to find that in the /var/lib/mysql/ directory, the only files there ...
0
votes
1answer
15 views

Can a trasaction be rolled back if a table is edited outside a transaction?

I am learning how transactions work using this short script CREATE TABLE t_test (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB; START TRANSACTION; INSERT INTO t_test VALUES (1); SELECT * FROM ...
0
votes
1answer
21 views

Mysql daemon failed to start: unknown variable password=xxx

Mysqld don't restart anymore...In the log, the apparently obvious reason: mysqld started [Warning] Ignoring user change to 'root' because the user was set to 'mysql' earlier on the command line ...
0
votes
0answers
5 views

How to display multiple row records of similar ID's in single row?

I have two tables user and work. Work table has multiple user's work experience.I have to retrieve most recent 2 companies on the basis of start date. Conditions : 1.If Work Table has multiple ...
1
vote
1answer
25 views

Is it possible to pick next row's cell value on same column if condition not matched?

I have a table flaggedcomment and a table price. In flaggedcomment, there are two empty columns Price_DateTime and Price_Open which I want to copy from price, depending on the ON conditions. Query as ...
1
vote
1answer
33 views

UPDATE/INSERT into Dynamic Columns

I am trying to update columns based on the value of the inserted column of the table 'my_table'. I want to be able to record the timestamp of the values changed. I am using the triggers but I can't ...
1
vote
1answer
9 views

Mysql Master-Slave replication catchup and bin_logs

I have set up MYSql Replication using 2x 128gig ram 24 core cloud servers. we have a massive live database that cannot experience down time due to the high volume of data input. I have found while ...
0
votes
0answers
16 views

MySQL : How to know if Trigger was fired

Someone can help me please , I want to log error about triggers in MySQL 5.5 I'll like to know if it's possible to know if a trigger was'nt fired ?
0
votes
0answers
11 views

Did this trigger break replication?

I added a trigger to a replicated database. DELIMITER $$ USE `database`$$ DROP TRIGGER /*!50032 IF EXISTS */ `some_ins_after`$$ CREATE /*!50017 DEFINER = 'user'@'%' */ TRIGGER ...
1
vote
0answers
7 views

How to set the trigger when field(contain json value) is updated?

I have one table in which there is one field called info.This field hold json value. I want to create trigger on this field.I want to split this json format string into record I mean I want to insert ...
0
votes
0answers
15 views

MySQL Script to automate creation of audit (history) tables and triggers?

For regulatory and fraud requirements, we need to record every change to most of the tables in the DB. The way we did this very successfully with a previous project is to have a copy of each table, ...
1
vote
2answers
26 views

MySQL 5.7 - Where is the mysql_secret file on Windows 7?

I've just installed a copy of MySQL 5.7 for testing, and I love the fact that it now only creates a single root account, and puts the password into a file called .mysql_secret. There's just one ...
-2
votes
0answers
24 views

Databases is not valid [on hold]

I am getting this error: One or more selected databases is not valid , how do I resolve this error.
0
votes
0answers
12 views

Manage orders based off product attributes and quantity

I'm developing a database for an online store in which the Products have multiple attributes and attribute types. ATRIBUTES TYPES atrtype_id name (like size, color...) ATRIBUTES atribute_id ...
-1
votes
0answers
13 views

ID That Will Never Be Used

I'm writing some tests for a Django application and in these tests I need to provide an ID for the view in question. I also need to test for an ID that is not in the DB. At the moment I am using ...
0
votes
1answer
18 views

Mysql query hangs on import

I dumped all my databases in order to import them into a slave. mysqldump --all-databases > dump.sql The import seems to be very very slow. Checking the processlist, I saw a simple insert on a ...
0
votes
0answers
32 views

Can an index be added to a PRIMARY with a null key?

I have a query that takes a long time to return, roughly 12-15 seconds, which I'd like to optimize. The query below is the result of running the original query with SHOW WARNINGS. If I run EXPLAIN ...
1
vote
1answer
24 views

MySQL Count where all conditions evaluate to 1

What is the best way to get count for all the records, where case evaluates to one for all the conditions. I have the query as: SELECT uuid , COUNT( CASE WHEN (( dt = 1 or level0 = ...
0
votes
1answer
18 views

Deadlock caused by INSERT trigger - thread looking for lock it already has?

Using MySQL 5.6.17. I have a table with an INSERT trigger that reads values from that table and 4 other tables, combines the results and writes that to a sixth table. Ever since I added this trigger, ...
0
votes
1answer
18 views

Can I extrapolate from the progress reporting in MyISAM ALTER?

I'm migrating a 300 million row MyISAM table to add some indexes. Just because it might be relevant, here's my ALTER statement: ALTER TABLE my_table ADD UNIQUE INDEX my_table_abcd (a, b, c, d), ...
0
votes
0answers
13 views

How to interface a database to a mobile app and a Pc client [on hold]

I have a background as an embedded programmer (avrs and pics) and I have recently been taksed by my employer to build a data acquisition network that (i)Reads data from sensors and transmits them ...
0
votes
0answers
14 views

linking a single table (comment) to multiple tables (article,link,attachment)?

the main idea is that i can post an article , link or an attachment and i can receive comments on each of these types of posts (the structure of the comment is the same) , the problem here is that i ...
1
vote
1answer
11 views

My connection to a Mysql DB has been blocked after spamming queries, for how long?

I am creating a web application that depends on querying a legacy DB without an API. It's MySql. Off course the lack of an API eventually got me in trouble. A feedback loop while testing the app ...
0
votes
0answers
19 views

Multiple user roles with different attributes

I need to design a database that has multiple users, with multiple roles. For instance, a user can be an admin, company, employee, or customer. Each user can have any number of these roles. Right now ...
0
votes
0answers
7 views

Will a covering index save a table hit if only the commoly-used length of a `text` field is indexed?

I would like to add a covering index for a table that contains a text field. The maximum size of the field that is in use is ~2000 ASCII characters, 95% is below 800 characters. I am considering ...
1
vote
1answer
37 views

Selecting, ranking and applying a point score based on rank

I am writing an MVP system based on votes from users. The table will look like vote | game | year --------- ------ ------ player 1 | 2 | 2015 player 1 | 2 | 2015 player 2 | 2 | 2015 ...
1
vote
0answers
21 views

Grouping results with left and inner join with zero counts

I have a query like SELECT `campaign_question_options`.`text`, COUNT(`campaign_submission_answers`.`answer`) as `count` FROM `campaign_questions` INNER JOIN ...
0
votes
1answer
29 views

Query working in phpMyAdmin but not in Java [on hold]

CREATE TABLE IF NOT EXISTS `employee` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, `designation` varchar(200) NOT NULL, `dob` date NOT NULL, `doj` date ...
0
votes
1answer
37 views

Why does MariaDB 10.1.2 blow away MySQL 5.6.22 in sysbench?

Problem Maria DB is doing roughly twice the number of transactions that mysql is using standard oltp benchmark. The VM and db's are both fresh installs from vendor apt repos with default configs. ...
0
votes
1answer
18 views

adding autoincrement to id already part of foreign key

I would like to add the autoincrement property for all my table's columns named id in a schema. However, most of them are part of a foreign key constraint. Is there any other way to do it without ...
0
votes
1answer
23 views

Would we see a performance increase by moving one table to another physical drive?

We have a database of about 600MiB. One table is particularly large and occupies about 70% of that space. We quite often have to query this table - sometimes as far back as a few years, so we can't ...
0
votes
1answer
18 views

Reusing covering index for fields in FROM, from another query's index

For the following query I have a covering index as so: SELECT bar_id FROM foo WHERE user_id=:user_id AND date_sent=:date_sent; ALTER TABLE foo ADD INDEX (user_id, date_sent, bar_id); Now I have ...
-3
votes
1answer
32 views

How to check table [on hold]

How to check which tables a user have access to on a server?
0
votes
0answers
22 views

How can I optimize a query with multiple left joins?

The working example that I have been provided with takes about 20 minutes to fully process. http://paste2.org/G8BZvNHe What are some basic strategies for making this more efficient? Edit: I've ...
0
votes
1answer
16 views

multiple table inner join didn't get expected values

Student_register table: regNo pro(HNDIT,HNDA,HNDFT) name start_day etc ... more columns Semester: regNo curSemmester(3,4,5) name The query I used to get values: SELECT ...
0
votes
0answers
20 views

Retrieve a list of users based on some conditions [on hold]

What am I doing wrong on this query? The main issue it's that the var_dump is returning null. global $wpdb; $users = $wpdb->get_results( $wpdb->prepare( "SELECT u.ID AS ...
0
votes
0answers
27 views

mysql trigger, let trigger recalculate throug whole table after correction of errors

Raised this question before but no answer found I have one table (table1) with energy data updated every 5 min I am using a trigger to calculate day values on every update and fill another table ...
0
votes
1answer
12 views

how to overcome these type of issues where null value inserted into primary key not null column in table

how to overcome these type of issues where null value inserted into primary key not null column in table
0
votes
0answers
27 views

Performance issue after reorganizing table in MYSQL

There is MYSQL(5.6.22-log Community Edition) database environment which has databases size varies between 1-40GB and using INNODB storage engine, it's serves for the several number of OLTP based ...