MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
1answer
17 views

Mysql slow query log always include “# Time:”

I am trying to parse the mysql slow log with logstash so it's important that the format is consistent. I have noticed that if statements execute at the same time, the line # Time: yymmdd is only ...
1
vote
0answers
10 views

creating deadlock for distributed database in mysql-(Testing purpose)

I need to create a deadlock for distributed database in MySQL for testing purpose with a simple sql query(without front end), can anyone good with MySQL query help me out in this with sample query. ...
0
votes
1answer
20 views

Use Create Trigger to create New table using calculated values from common groupid column values

I have a TABLE_A in Database1 the contains rows with a common group id. I am wanting to use the CREATE TRIGGER function to generate a new TABLE_B in the same Database1 that contains calculated values ...
0
votes
0answers
9 views

Database Importing Collation

Migrating from DB2 found this error message: Error message: Incorrect string value: '\xF2\xAC\x9F\xB0or...' for column 'column_name' This is when trying to import an XML to MySQL. I should have a ...
4
votes
1answer
27 views

MySQL overhead of calling a Stored Procedure

Within Java, we access MySQL (v5.5.40) via JDBC -- a prepare statement, setting parameters then calling the stored procedure. It appears that every execute() has the overhead of 4 meta-calls (per ...
0
votes
0answers
5 views

MySQL log shows excessive meta queries on SP calls [duplicate]

We access MySQL via JDBC -- a prepare statement, parameters binding then invoking the actual stored procedure. It appears that every execute() has an associated "overhead" of 4 meta-calls (per MySQL ...
1
vote
1answer
37 views

Is there a performance hit for querying two different databases?

I have two tables, A and B, which are located in different databases on the same MySQL server. I would like to create a derived table C from tables A and B, as an inner join between the two tables. ...
0
votes
2answers
14 views

Structure database(s) dealing with different time points

I have few data sets that give a score for a specific account at a specific point in time. So, account A might have a different score each quarter going back 5 years so. There aren't a ton of data ...
0
votes
1answer
20 views

mysql prevent update other salve-master db while any record update in slave db

I have master and 3 slave (slave1,salve2,slave3) db in same host. So when I update any record in slave1 db its affected on all other master and salves db. My Question is: How to prevent update all ...
1
vote
0answers
9 views

MySQL performance of trigger when many rows are affected

I have a question on MySQL triggers which I hope someone might be able to help with. As part of our platform, we have a table (accounting), which grows rapidly. We also have an archived version of ...
1
vote
0answers
40 views

Getting count of visits for multiple different outlets

I'm trying to get the distinct count of id that visited different outlets. I have more than 50 outlets so I don't think I can use the IN operator. CREATE TABLE transact ( year int NOT NULL, card_id ...
0
votes
1answer
18 views

How to enable queries logs in MariaDB

I'm running mysql Ver 15.1 Distrib 5.5.42-MariaDB, for Linux (x86_64) using readline 5.1 in CentOS 6.6 server. I'm trying to get queries logging info and I'm doing by enabling log support at ...
0
votes
2answers
10 views

MariaDB/InnoDB: What to do on “Locks in Status-Log but no locked table found”

We migrated lately from MySQL to MariaDB. SELECT VERSION() 10.0.13-MariaDB-log When executing this: SHOW ENGINE INNODB STATUS I get (shortened): ... ---TRANSACTION 279257007, ACTIVE 23981 ...
0
votes
1answer
26 views

How to join tables without identifiers?

For school I have to do this: Query what salary scale an employee belongs to displaying these collumns: s_schaal.schaal, werknemer.name, werknemer.salaris. But there are no identifiers between the ...
0
votes
1answer
14 views

How does one create a Matrix Report to convert sql columns as rows? [duplicate]

I'm trying to create a product listing. Naturally Sql prints the data vertically forming rows after rows like this: Rowa. product_img#1|Product_name|Product_price|Product_short_description Rowb. ...
2
votes
0answers
61 views

Why is this MySQL query “using where” instead of “using index”?

Could you help me with this query, please? SELECT p.patid, MAX(c1.eventdate) as eventdate from patient as p left join op_adv_effects._clinical as c1 on p.patid = c1.patid left join ...
1
vote
1answer
20 views

MySQL Brute Force Defense

I was wondering what defense MySQL server uses to protect against brute force attacks on the user credentials? I hope this isn't too simple of a question! I have tried to Google it, but can't seem to ...
1
vote
0answers
25 views

Can I populate a table in mysql without ending up with duplicated entries [migrated]

I have a database. However whenever I am trying to add products to the cart table from a shopping page, I realize that the same item is added multiple times to the table if the add button for that ...
2
votes
2answers
25 views

MySQL - SELECT on MyISAM Table on index - Does table size (rowcount) matter?

I was arguing with a coworker if SELECT * WHERE t BETWEEN ? AND ? on a MyIsam table where t is an index is significantly slower for huge tables. The hypothetically returned dataset is always the ...
0
votes
1answer
29 views

Speed up procedure

I was wondering if this procedure can be executed faster. There are indexes on the joins and where's. Depeding on the size of product_id_CSV it takes approx. 2.5 sec. CREATE ...
1
vote
1answer
27 views

Is it possible to restore a MySql database only using files from Data directory?

I have a mysql installed inside a windows virtual machine. The hard drive was crashed and the system is not able to restore all again. But I can have access to entire c: ROOT. So I went to ...
0
votes
2answers
60 views

Write a query to create skill table. Please note that the field description can be NULL and the default value is NULL [on hold]

I wrote this query for this question. but it does not work create table skill ( id bigint(20), description varchar(255)DEFAULT NULL, name varchar(255), primary key(id) );
1
vote
1answer
6 views

mysql dump import incredibly slow on my developer's machine

I have an SQL dump, it's pretty big (411 MB) and it took 10 minutes to import on server A, the same import on my workstation B has an estimate (pipeviewer) of 8 hours to import (it imported 31 MB in ...
1
vote
0answers
28 views

MySQL method for inserting data from JSON using Python Connector

I have been using Python to insert parsed data from a JSON server response into a MySQL database. The JSON data consists of around 10,000 responses, each holding around 40 nested pieces of ...
0
votes
2answers
17 views

How to deny connections to localhost and force to use FQDN instead

I have a server running Apache and MySQL. Connections to MySQL work fine when using localhost in the connection settings. Now I'd like to force our developers to use a FQDN instead of localhost. This ...
0
votes
1answer
16 views

Mysql's internal mysql database in replication

I searched around but the word game seems to delude any valid result and could not find anything relevant. Mysql (the RDBMS) has an internal database named mysql, this database has information about ...
0
votes
0answers
7 views

Restoring a Full Backup with innobackupex with “innobackupex: completed OK!” but do not start mysql 5.6 [on hold]

i am unable to start mysql, followed process given below Operating System: CentOS 6.6 (64 BIT) MySQL: Ver 14.14 Distrib 5.6.24 Percona: percona-xtrabackup.x86_64 (2.2.10-1.el6) mysql> CREATE USER ...
0
votes
0answers
11 views

Insert in PXC Cluster are not equally distributed

I have another query with regards to pxc cluster i have 4 nodes in pxc cluster all nodes have the same cnf file and one server as haproxy. Config File Detail [mysqld] datadir=/var/lib/mysql ...
0
votes
0answers
15 views

How to make an indexed search over a list (set)?

A follow-up question for How to most efficiently store possible combinations of categories? An answer pointed to SET datatype. As far as I know, the default search option for it, FIND_IN_SET, doesn't ...
0
votes
1answer
9 views

Wiki instance mirrored to another box

I've never really done anything like this before, but I have a wiki instance setup on my internal network. Since it is a support wiki, in the case that our internal network is inaccessable through our ...
1
vote
1answer
28 views

Ticket-like table and growing database

I manage a MySQL database through phpMyAdmin. I have a table for cinema cupouns tickets. I upload a bunch of tickets, and then I assign tickets to users. Each ticket contains, id -> ...
0
votes
1answer
9 views

Checking for duplicate entries for Column A - Table A based on user_id from Table B

I have two tables which have a 1 - 1 relationship. Table A - Skill_id Tabel A - Entry_id Table B - Entry_id Table B - Author_id The two entry_id columns are a primary key - foreign key. I need to ...
0
votes
1answer
30 views

auto_increment incremented on non-affecting queries

When performing the following query on 'sessions': INSERT IGNORE INTO sessions (user_id, archived) VALUES (3, 0) while: CREATE TABLE `sessions` ( `id` bigint(20) unsigned NOT NULL ...
0
votes
1answer
33 views

Storing small array of many possibilities

Let's say I need to store the text of a series of books, line per line in a database, and present them in a (CMS-based) website. I thought the correct setup would be: a 'library' table: containing ...
0
votes
1answer
25 views

How to run a mysqldump from a .txt or .sql file inside a command line window?

If I run the follow line directly in command line window it will works good. C:\xampp\mysql\bin>mysqldump -u root d3bi>C:\Users\me\Desktop\test\testDump.sql How can I run a .txt or .sql file ...
0
votes
1answer
13 views

manual transfer fails with table does not exist

I performed a manual transfer of data manually (zip up the dir and rsync'ed it over. I moved the members.ibd (the only .ibd file) to a save place, extracted and then replaced members.ibd with the ...
0
votes
0answers
14 views

Error when creating multiple tables at once

I'm using mysql through the CLI in OS X. The problem I'm having, is that when I create multiple (more than 3 in this case) tables, I receive errors, but if I enter the code, 3 tables at a time, I get ...
0
votes
1answer
16 views

Create a default value for session valid_until MySQL

Server version: 5.6.22 Homebrew The table: CREATE TABLE `sessions` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `user_id` bigint(20) unsigned NOT NULL, `date_created` datetime ...
0
votes
2answers
50 views

Schema review for system to advertise cars

Ok, so I am carrying out a rather simple task as part of a job requiretement. Here is the task in a nutshell: Design a form for creating ad for selling car Form has 2 have: 2 drop downs: Car make ...
0
votes
0answers
30 views

I have a table from which I can retrieve information but I cannot update or delete information

I can update and delete items via PhpMyAdmin. I changed the owner to mysql. My program is a clone of insert, update, and delete that is functioning for other tables within the database. I receive a ...
2
votes
2answers
70 views

Retrieve total available days in date range compared against multiple records

Given the following MySQL database structure for a booking system, how can I retrieve all available days between two user supplied dates? | ID | code | date_arrival | date_departure | 4 | APT01 | ...
-2
votes
0answers
11 views

Amazon RDS, I can't login to db from other computers [on hold]

I'm having issues with connecting to mysql db hosted by amazon (trial). I have written application that connect to my db. Everything is fine when I connect from my pc but when my friend tryed to ...
5
votes
2answers
723 views

Why are mysql password hashes internally saved with a star (asterisk)?

I was reading upon some mysql internals, when going through the mysql.user table in the my mysql shell, I get mysql> select * from mysql.user limit 1 \G *************************** 1. row ...
1
vote
1answer
27 views

how to cache a subset for cascading select queries in mysql

Here is another database problem I stumbled upon. I have a date-range partitioned MyIsam look-up table with 200M records and ~150 columns. On this Table I need to perform cascading SELECT-Statements ...
0
votes
2answers
35 views

How to most efficiently store possible combinations of categories?

I have a table of categories (currently with 14 rows, not likely to change). A need to cache combinations of categories emerged, so I'm creating a table of allowed combinations (only few hundreds of ...
0
votes
1answer
73 views

Select only rows that equal x but not x and y?

I'm having quite the challenge with selecting hierarchical data in mysql although I feel the schema should be optimized for it since it's based on the wordpress database model. I've got 4 tables... ...
-1
votes
1answer
26 views

What MySQL activity eats CPU? [on hold]

I have noticed weird CPU consumption peaks for MySQL process. When I monitor running queries, it's empty or some reasonable queries being executed. How do I better address the problem? Is there a ...
1
vote
1answer
16 views

Is it safe to disable InnoDB doublewrite buffering when setting Innodb_page_size = filesystem page size?

As I understand, doublewrite buffering is needed to avoid partial write, because Innodb_page_size default is 16K, while filesystem page size normallly is 4K; I wonder if I set "Innodb_page_size = ...
1
vote
0answers
10 views

MySQL Cluster 7.4.6 - Analyze table failure on 15m reference table

I'm running MySQL Cluster 7.4.6 on Azure VM's with the following infrastructure: 2 x Mgmt Nodes: Ubuntu 14.10LTS, 2 cores, 3.5GB ram 2 x MySQL API Nodes: Ubuntu 14.10LTS, 2 cores, 3.5GB ram 2 x Data ...
0
votes
0answers
23 views

manual data migration/rescue MySQL version: 5.6.23

MySQL died and could not be started. So I transferred my database files via rsync to another server and restore from back up. Now I am trying to access my data with my updated records. mysql> use ...