MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
0answers
652 views

Which tool to use to generate an ER Diagram that will allow navigation between tables

DB= Mysql 5.X , OSX/Windows I have a DB with over 400 tables. I want to use a tool to reverse engineer the db to an ER diagram that will allow me to navigate between tables. So for example, when I'm ...
0
votes
0answers
220 views

Converting UUID's from CHAR(32) to BIN(16)

I have a few questions relating to UUID's, but before I go on, let me explain our schema a bit, first, we're on MySQL 5.5 (Percona's version to be exact)... In each applicable table, we use a two ...
0
votes
0answers
51 views

MySQL/MariaDB CLI limits max password?

I've got strange problem - my MariaDB instance CLI (Linux) does enforce limit on how long password can be. For example, setting password like this UPDATE mysql.user SET ...
0
votes
0answers
12 views

specify database while executing mysql binlog

We have different mysql servers with same database names, is it possible to restore their binlogs into one server (different database names) ?
0
votes
0answers
202 views

MySQL 5.1 replication broken after crash, duplicate key error, why? No redo log corruption

What do you think is the reason for the duplicated key errors in our MySQL 5.1.61 replication slave, after the slave machine crashed? It's not corrupted redo logs. Details follows: We have ...
0
votes
0answers
121 views

MySQL error 1241 operand should contain 1 column

I'm trying to write the following query, which gives me error 1241 saying operand should contain only 1 column: SELECT mobobj.description AS Description, mobID AS ID, location.description AS Location ...
0
votes
0answers
207 views

Alphanumeric field in MySQL

I need to create a character field with some rules, they are, Field name: contract_number. Rules: Contract Number Contract number must be alphanumeric. Contract number must be unique. Contract ...
0
votes
0answers
25 views

How to Replicate data from read only sql database to Active one

We are applying logs in every 30 min to one SQL db which has to be in Restoration Mode/Read only mode as we are applying logs continuously. Now we have to Set up Replicaion of this Read only Db to ...
0
votes
0answers
865 views

How can I create a view in a stored procedure on MySQL?

I am trying to dynamically create views based on an SQL query and have a trigger recreate them when the fields in the view are to change. When I call the procedure I end up with a permission denied ...
0
votes
0answers
66 views

mysqlcheck -o --all-databases does not finish

I have a problem with my MySQL Databaseserver. I have many tables and there is quite some traffic going on there (around 4 connections per second). Since the phpMyAdmin advised me to do a OPTIMIZE ...
0
votes
0answers
46 views

Quick MySQL Backup (1 file per table) - v2

Improving bash shell script written by RolandoMySQLDBA. Continuing the following thread: Quick MySQL Backup (1 file per table) I tried to adapt your script, but after the first rsync was done, I get ...
0
votes
0answers
176 views

negative look-ahead workaround for replacing strings

I need to replace in MySQL all text that starts with href="SOMETHING" but not with href="/cnSOMETHING". My first solution was to use REGEX replace with DB (?<=href\=")((?!\/cn)[^]+?)(?=") ...
0
votes
0answers
52 views

Mysql: Add a slave to an already existing Master-Master setup

We already have a Master-Master MySQL setup running live in the network. Now we are asked to add a slave to the setup. Is it feasible. If yes, what's the steps I need to follow. Also what are the ...
0
votes
0answers
139 views

MYSQL 5.5 SERVER MULTI MASTER REPLICATION

Myself using mysql 5.5 in master side and in slave side(remote connection) It seems the configuration like log_bin,binlog_do_db are correct but it shows Slave_Io_Running : Connecting ...
0
votes
0answers
46 views

Analyze a Database to Find Failed Foreign Key Constraints

I aggressively deleted a ton of data after using this query: SET FOREIGN_KEY_CHECKS = 0; Is there any easy way I can analyze the entire database to check to see if I have any hanging records that ...
0
votes
0answers
47 views

using another table column as array

SET @qdate := '2014-02-17'; SELECT com.trading_code, com.business_segment_id, dmkt.close_price, dmkt.cdate, dmkt.turnover, dmkt.volume, sh.total_no_share, ...
0
votes
0answers
42 views

RDS did not restore to a specific point-of-time on my MySQL instance. Why did that happen?

I'm using a MySQL 5.6.13 instance on RDS and accidentally wiped all the data in it (something like 2.5GB). I used the restore to point-in-time feature to recover all the data and it only recovered the ...
0
votes
0answers
22 views

Correct Syntax to use a Foreign Key as Part of a primary key

I'm interested in making part of the primary key of one table foreign key. What is the syntax to do so in mySQL. I'd like to create the following two tables: Person Table : {person_ssn} -> name, ...
0
votes
0answers
893 views

Conceptual data model for a billing system

I am working on existing MySQL database, my task is to create a new data model for a billing system on that existing application, I have created a conceptual data model, as given below, My ...
0
votes
0answers
42 views

Which RDBMS do return better error messages?

When you have some syntactical error in a query you will be a error message like: SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server ...
0
votes
0answers
48 views

Best way to store combobox options for an EAV table

I don't know if this question belongs to StackOverflow, but here it is: I have an EAV table: mysql> describe keywords; +-------------+--------------+------+-----+---------+----------------+ | ...
0
votes
0answers
98 views

MariaDB : Larger innodb_buffer_pool_size causing SQL updates to be slower

Has anyone here encountered such scenario before where and update statement took longer time to run when innodb_buffer_pool_size is bigger. I've run a few tests using the same statements on around ...
0
votes
0answers
102 views

Is US/Pacific timezone in MySQL automatically adjusting to DST?

I'd like to define a view in MySQL which will convert some system data always written in UTC to our headquarter time, which is San Francisco. If I use CONVERT_TZ(..., 'UTC', 'US/Pacific'), will it ...
0
votes
0answers
34 views

How can the performance of a query that has a condition on two fields of each row be improved?

I have an images table with approximately 5 million rows in it. For the purposes of this question lets say the table looks like this: CREATE TABLE `images` ( `id` int(11) NOT NULL AUTO_INCREMENT, ...
0
votes
0answers
27 views

How to recover data in a MySQL DB?

Log file says: Recently disk space on my VPS got filled up. Now my DB looks corrupted, MySQL error log says: 140319 01:28:57 mysqld started 140319 1:28:57 InnoDB: Started; log sequence number 0 ...
0
votes
0answers
15 views

Using mysql without --skip-grant-tables

I have some problem with my mysql server, after an upgrade to mysql5.6 faild, Once the reinstallation to mysql5.1 succeeded I was only able to see one table "information_schema" and the rest hidden ...
0
votes
0answers
43 views

MySQL: Large data slow reads

I have very large table with 17,044,833 Rows and 6.4 GB in size. I am running the simple query below and it takes like 5 seconds. Any ideas what optimizations can I do to improve the speed of this ...
0
votes
0answers
41 views

SQL Error while restoring big database in Mysql

I have successfully created a MYSQL dump on RHEL 6 machine. The dump file is having 40 GB. When I try to restore it in an UBUNTU machine I got this error.
0
votes
0answers
91 views

Opimizing Mysql

Below is a Snapshot of my Server info. And the results of mysqltuner.pl Some help with commands would be appreciated. I'm new to this and have gotten pretty far, but I'm having issue with Mysql ...
0
votes
0answers
69 views

Why is adding a FK constraint slow in MySQL?

I'm doing some experimentation in MySQL 5.1.53 and I've run across a case that I just don't understand. CREATE TABLE A ( `pk` INT(11) NOT NULL, `fk` INT(11) NOT NULL, PRIMARY KEY `pk_A` ...
0
votes
0answers
35 views

mysql id sequencial problem

I create 3 records with the id 1,2,3 I delete record 3, then in the future add another record, it then goes 1,2,4. how to id get sequential order when it is deleted the record like 1,2,3
0
votes
0answers
36 views

Multi language where this feature never existed

I need to implement multi language system in an existing system that was not developed for this. The number of entries in the tables that will hold data that has to be translated will not exceed 5 ...
0
votes
0answers
28 views

Install/bootstrap mysql without name-based users

Like many people, I run mysql with skip-name-resolve (docs), and only ever use IP addresses to connect to avoid DNS issues and overhead. When installing, mysql_install_db accepts a --skip-name-resolve ...
0
votes
0answers
34 views

How to accelerate database load?

I have created mobile website with Jquery Mobile and Mysql database. the table have 3000 rows, and I Think the data still quite small. But After I load it using database to Select All it can be done ...
0
votes
0answers
32 views

Storing often unused but required data

I have a table images, which I use to store a file name, file hash and date created. I also need the width, height and watermark overlay of each image, but only on very small occasions do I need to ...
0
votes
0answers
25 views

Number position integrity within a set

We have a table which has a column defined for set(to group rows) and a column defined for hierarchy(order position), how do we enforce hierarchy ordering position integrity within each set? An ...
0
votes
0answers
76 views

DB2 manual asynchrounous real-time replication to NON-DB2 database

I have been given a Proof-of-Concept task to find out whether it is possible to set up manual (=no 3rd party tools allowed) asynchrounous real-time replication in DB2 that replicates all data changes ...
0
votes
0answers
41 views

MySQL manual asynchrounous real-time replication to NON-MySQL database

I have been given a Proof-of-Concept task to find out whether it is possible to set up manual (No 3rd party tools allowed) asynchronous real-time replication in MySQL that replicates all data changes ...
0
votes
0answers
74 views

how to archive old entries from mysql on rds

I have an AWS RDS MySQL DB. I have ec2 instances and an s3. What I would like to do is only retain 60days old data and archive the rest. I'm still new at this so I'm not really sure what to do. What ...
0
votes
0answers
117 views

Mysql profiler different duration information_schema.profiling

Hi! I have a problem and no idea how to resolve it. I'm trying to use mysql profiler. I'm using the command: set profiling=1; to turn it on. Later, i execute my query which is correlated ...
0
votes
0answers
29 views

Altering NOT NULL column in offline table

Regarding the MySQL Cluster documentation we are not allowed to modify a column to NOT NULL on ONLINE tables. i.e. ALTER TABLEfoo_barMODIFY COLUMNbazdecimal(11,8) NOT NULL Our hosting provider tells ...
0
votes
0answers
32 views

How to write a simple application for copying a MySQL database for periodical backup

I want to use MySQL for my Java application as a database engine. I have two different applications that must be run on two separate computer networks. The first program saves the data in the ...
0
votes
0answers
123 views

Selecting posts, user details and votes from 3 SQL tables

I have a user table with id, username, email etc.. post table with id, uid (user id), content etc and vote table with id, uid, pid (post id), vote (ENUM : '1' and '-1'). In the vote column of Vote ...
0
votes
0answers
56 views

MySQL Server constantly crashes

my apologies for having already posted on this issue, but it has moved on so far I feel a new question is the only way to go. First a little history - I have a MySQL server which runs a few small ...
0
votes
0answers
55 views

MySQL query hanging on copying to tmp table

I'm stuck with a query hanging on 'copying to tmp table' for about 25 seconds according to the profiling. The query is used for forecasting the number of results after filtering by job_type / ...
0
votes
0answers
60 views

Union within derived table, (related_id=a AND related_id=b) OR (related_id=z)

I have the following tables: users, tags, tags_data. tags_data contains tag_id and user_id columns to link the users with tags in a 1 user to many tags relationship. What is the best way of listing ...
0
votes
0answers
49 views

MySQL import low use of resources

I'm importing a largish DB into MySQL (20tables of ~25mill records). As expected this is taking a long time, but I'm curious as to why the process is running using only 0.000 to 0.700 %CPU and 0.87 ...
0
votes
0answers
88 views

Dynamic Query creation by looping through Query-Result?

after my first question regarding pivoting a table (here) got great answers I'm now struggling with how to adapt this to variable table sizes. I need something like a "FOR EACH IN" which creates a ...
0
votes
0answers
179 views

Postgres vs MySQL Database Maintenance

I have been using Postgres and MySQL for a long time now. There are a few routine maintenance chores that must be performed on a regular basis to keep a PostgreSQL server running smoothly. As as ...
0
votes
0answers
45 views

MySQL beginner in troubles

My MySQL knowledge isn't really good. However, I tried to move my websites media folder to an other location and afterwards wanted to update the links in my database: UPDATE wp_posts SET ...