MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

3
votes
1answer
50 views

Oracle sequences to MySQL

Migrating from Oracle to MySQL we've found some many incompatibility issues. We've some workarounds for many of them, however we still have one: The Sequences We have this table CREATE TABLE ...
4
votes
2answers
98 views

Count rows with inner joined tables

I have 3 tables: Players: mysql> SELECT * FROM players; ...
1
vote
1answer
36 views

Read insert statements from text file

Good day All, I have a 1000 text files that each contain 1000 insert into statements. What is the most efficient way to read and execute each insert statement into a MySQL DB table? Thanks for any ...
2
votes
1answer
37 views

Split brain scenario - RAC and PXC

Even though split brain scenario occurs in both Oracle RAC and Percona's XtraDB Cluster, a two node cluster is allowed and split brain scenario is resolved in RAC but a two node is not recommended in ...
2
votes
1answer
25 views

SQLyog and MariaDB engine

I am quite new in MariaDB and I use SQLyog Community Edition as the frontend GUI. Whenever I create a new table, on the Engine dropdown, there is no XtraDB option. Is there any additional steps I am ...
3
votes
3answers
58 views

MySQL - Unable to Convert Primary Key to Auto Increment

I have a MySQL table where the Primary Key (int) was set by the software to be equal to the Primary Key (auto incremented) of the main table. Because of a change in design I now need the field to auto ...
2
votes
0answers
50 views

How can I organize complex queries?

I am analysing a relatively large (25GB compressed MySQL dump) static data set. Some of the queries I'm writing are several tens of lines long, which makes them difficult to understand, modify, and ...
2
votes
1answer
43 views

How to add DATETIME value for session user

I am maintaining two tables: Employee table consists of username and password(for logging in) log table consists of login and logout time for the session user With the click of a button, I should ...
2
votes
2answers
100 views

INSERT… ON DUPLICATE KEY UPDATE not working as I expect

I have a table called "Example" CREATE TABLE IF NOT EXISTS `example` ( `id` int(11) NOT NULL AUTO_INCREMENT, `a` int(11) NOT NULL, `b` int(11) NOT NULL, `c` int(11) NOT NULL, PRIMARY KEY ...
0
votes
0answers
15 views

Total sum of database field value with where condition in Laravel [migrated]

I table student_atendence table fields are (id ,studid,attendence). This is my table student_atendence: studid attendence 28 1 31 1 32 1 28 ...
0
votes
2answers
40 views

How to run update query without interrupt other processes

I want to update a table and what I want to do is when other processes want to use this table, It will pause my update query and when other processes complete, it will continue to update.Is there any ...
3
votes
2answers
60 views

MySQL indexes maintenance

I made a lot of research about how to maintain indexes in MySQL to prevent fragmentation and to optimize somehow the execution of some queries. I am familiar with that formula that calculates the ...
1
vote
1answer
45 views

Dropping Foreign Key In Production Environment

I have been asked to drop foreign keys on a number of databases in our production environment. I am relatively new to MySQL... what do I need to be aware of when doing this? E.g. full table locks, ...
0
votes
0answers
21 views

MySQL - replication. Do slave DB schema need to resemble master

I want to do wildcard replication in mysql. I want to replicate only one table (say T1). The database in master and slave are different. Only the T1 is common between the two database. can this be ...
4
votes
3answers
113 views

group_concat and group by together

I am trying to write a query using group_concat, concat and other functions, but I am getting stuck not able to join and group the data to represent them in 1 cell. Here's how my data is... table1 ...
5
votes
3answers
417 views

How can I force MySQL to IGNORE ALL indexes?

I have read articles about FORCE index, but how can I force MySQL to IGNORE ALL indexes? I tried SELECT * FROM tbl IGNORE INDEX(*), but I was not successful. As for why I (and others) need to do ...
2
votes
1answer
20 views

sorting output by incrementing IP in mysql

I want an incrementing list of IP #'s in a subnet to display on a php web page in a table. Right now, 10.1.2.3 would display after 10.1.2.10 in the sequence. My current query is this: SELECT * FROM ...
1
vote
1answer
46 views

Database design for 2 1-to-many?

It's the first time here so I don't know if it's the right place to ask.. I'm not good in database design, just learning, so I'd like some advice if possible. The case: I'm building a really small ...
0
votes
0answers
31 views

Tools to insert data faster in MySql database

I am trying to insert data from a flat file which is around 9 GB with about 15 million rows, I used SQl-server 2012 for that. I noticed data insert is much faster with sql server and it took around 30 ...
1
vote
2answers
72 views

tmp_table_size with mostly InnoDB tables

I use MySQL version 5.6.25-0ubuntu0.15.04.1 on 64-bit Ubuntu 15.04. I have 2GB RAM and the disk is SSD (so, in my mind, writing to disk isn't that costly). I currently have databases for just two ...
0
votes
0answers
45 views

Mysql after datadir change issue

I moved my mysql datadir (copied the folder to another location) however it worth noting that i still kept my socket and pid in the previous location /var/lib/mysql. Obviously i changed the datadir in ...
1
vote
0answers
95 views

Slow Tally Tables

I have a pain point with a relatively large application concerning 'tally tables', which are used for keeping track of the number of items against 'campaigns' (placed by a client), and 'publishers' / ...
0
votes
1answer
40 views

Copying MySQL schema, some tables not getting recognized!

From the 'data' folder of my colleague I copied the entire 'MyDatabase' folder and pasted that in my own 'data' folder.Now, some tables are getting recognized while others are not.**All the tables are ...
0
votes
2answers
18 views

MySQL - replicate some tables between transaction and report database

I have two different database (on two different hosts). One database is transactional and another database is reporting. Let us say, it stores information about departments and their employees. ...
1
vote
1answer
71 views

Benefits of using prepared statements in stored procedures?

Except from helping avoid SQL injection, what are the benefits - if any - of using prepared statements inside a store procedure (assume query is only executed once in the procedure)? Does the engine ...
2
votes
3answers
84 views

Change the Row Order to alphabetical then numeral

I have a table with a username as unique & primary key. I ran this Query to order them in ascending order ALTER TABLE `table` ORDER BY `Username` ; So the rows are ordered like this ...
0
votes
2answers
91 views

mysql 100% usage on all cpu [closed]

On mysql community 5.5 (centos 6), VM on hyper-v win 2008 r2, 4 CPU, 48 GB RAM Which setting(s) that I would need to change to reduce cpu usage and get my web app (joomla) to respond faster without ...
1
vote
1answer
60 views

mysql update query almost 5 minutes slower in 5.6 vs 5.5

EDIT 6: I always thought an ALTER table recreated everything. When I add the optimizes shown in edit 5; it is always fast. (no more 4 minutes waits). The odd part is; if I am doing a lot with my ...
3
votes
1answer
117 views

Supertypes and subtypes

I am trying to design a database that has supertype/subtypes. I have the following users: - basic user - business user - administrator (super user). All three users share a common table (users) ...
0
votes
1answer
51 views

copy tables from one database to another

I have two Databases on mysql. They are used for completely different things and contain different tables. The problem is I need them both on the same server that can only host 1 db (and I can't ...
1
vote
3answers
38 views

Mysql privileges and replication

I want to recreate the whole privilege system in my database, and that includes the slave user on the master. It means i have to stop the slave, run a set master command again and than start it. My ...
0
votes
1answer
23 views

What is the best way to update a Production database with Test instance changes

I have deployed a Mysql database into Production. As I work on the next release of software I am making changes to a test database. I now need to update our Production system without losing the data ...
1
vote
2answers
35 views

Why is replication stopped?

Got the following nagios alert this morning for our MySQL replica: 14:49 PROBLEM: mysql2/MySQL replication check is CRITICAL, 10.1.0.133 is 72870 seconds behind, Aug 09, 21:49 UTC When I look at ...
2
votes
2answers
41 views

Extracting two particular values from a multi valued column

I have a table SUBSCRIBER and in it the data is stored in a way that bars is a multi-valued column . Different values of bars are stored in different rows with the ID as following : ID BARS 1 , ...
0
votes
0answers
21 views

Need to Sync Oracle DB table data with mysql [duplicate]

I have a table with some data in oracle which i want to sync with mysql table once in a day, like a cron job. Is there any tool available which i can use as a cron job in Linux?. Thanks,
2
votes
1answer
22 views

Too many TEXT fields leads to record too big

I have a table with multiple 9 TEXT columns, each containing JSON data. I usually UPDATE all the columns at once. I can't seem to add a 10th TEXT column. The table is InnoDB. What can I do?
0
votes
2answers
16 views

Keep mysql.socks

I moved mysql datadir to /storage-s01/mysql, but socket is still located at the same directory /var/lib/mysql/mysql.sock. Do I have to keep socket at the same directory as datadir? BTW, If I want to ...
0
votes
1answer
37 views

How to populate a table's foreign keys from other tables?

I have a database with the following tables: Airport ( iata PK, airport, city, state, country, lat, long) cancellation_cause ( cod_cancellation PK, description) Manufaturer (id_manufacturer PK, ...
1
vote
2answers
174 views

Avoid foreign keys on an 800-GB database?

This question is for the experienced developers out there. My friend recently told me that his employer has a legacy code base whose MySQL database has now grown to around 800 GB in size. What's ...
1
vote
1answer
31 views

Apply the select privilege to all users in mysql

I have 30 plus MySQL users their permissions vary from one another. I want to GRANT SELECT privilege to all of the 30 users. Is there any one command that I can use that can grant select to all 30 ...
0
votes
0answers
53 views

Import CSV to MySQL with long text fields

I'm trying to import a CSV file (';' separated) to a MySQL table. Normaly, I use a SQL like: LOAD DATA LOCAL INFILE 'local-path/file.csv' INTO TABLE tmp_table CHARACTER SET latin1 FIELDS TERMINATED ...
0
votes
1answer
33 views

GRANT EXECUTE ON PROCEDURE unable to USE database

I am attempting to grant a user execute privileges to a single stored procedure without any other privileges in a MySQL 5.1.45 instance. The stored procedure is defined with SQL SECURITY as DEFINER. ...
0
votes
0answers
37 views

MYSQL- concurrency issue

I use JDBC to connect to MySQL. order_items table order_item_id order_id quantity unit_price shipping_price business_id workflow_id delivery_id item_id Orders table billing_address_id ...
2
votes
1answer
57 views

Deciding what columns to use for partitioning

I am having trouble deciding what fields of my table to base the partition. Currently there are a total of 8 indexes on the table out of a total of 14 columns. The problem is the indexes are taking up ...
0
votes
0answers
25 views

Error while deleting a foreign key

I can't delete foreign key in MySQL, I get this error message: ERROR 1025 (HY000): Error on rename of './database/table_name' to './hp/#sql2-4b1-26' (errno: 152) My system is Ubuntu 14.4 lts
0
votes
2answers
22 views

MySQL - trying to combine `GROUP_CONCAT` with `COUNT()`

I am trying to combine GROUP_CONCAT with COUNT(), and failing. I have the following data: CREATE TABLE `tab1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `team` varchar(45) DEFAULT NULL, ...
0
votes
2answers
49 views

Create foreign key doesn't work, column types match

I'm trying to create a foreign key in mysql workbench, but keep getting this error: I've checked the columns on both tables, and they match up as well as I can make them. One is CHAR(21) not null ...
1
vote
1answer
28 views

How to detect MySQL slave writes?

In a MySQL master-slave replication architecture, how do you detect if there have been (erroneously, since this should never happen!) direct writes on the slave? For now, I'm running this command on ...
0
votes
1answer
26 views

Can I split mysql the search results into two tables in php?

I have index tables with multiple columns: s | s1 | s2 | s3 | s4 | s5 | s6 | s7 | s8 | s9 | s10 | s11 | s12 | ------------------------------------------------------------------- 0 | 1 | 0 | 1 | 1 ...
1
vote
2answers
33 views

Mysql query to retrieve the latest updates based on DATETIME for each parameter

I have a station_data table where sensors from different weather stations report their data. Sample data: id(AI),station_code,parameter_code,value,date_time 1,1,1,1.234,2015-01-01 00:00 ...