MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

1
vote
1answer
2k views

Postgresql vs MySQL - Which is better for join queries & writing data(inserts) [closed]

I have to design a database which will end up with 50M records in a single table(there will be other tables with lesser number of records). I'm more concerned with join queries & writing ...
1
vote
1answer
414 views

How to change values on specific days for report generated from mysql

I am generating an attendance report in mysql, by pulling the data from 3 tables like student name and roll no from admissions table, Absent details from absentees table and I have created a calendar ...
1
vote
3answers
3k views

Proper MySQL database maintenance

I hope this isn't too broad of a question. I'm certain it'll be able to help me and any future dba noobs that stumble upon it. I'm a systems admin that got put into the roll of DBA (because I helped ...
1
vote
2answers
122 views

Finding rows for a specified date range

I have a table which stores which teacher (teacherid) works for which group (groupid) of pupils starting from the date startdate: CREATE TABLE `pupilgroupteacher` ( `id` int(10) unsigned NOT NULL ...
1
vote
2answers
337 views

mysql innodb_flush_log_at_trx_commit clarification

I have been reading up on innodb as a storage engine, mainly because I have recently moved to AWS and they do not recommend myISAM which my databases current run on (and have for about 10 years) So ...
1
vote
2answers
2k views

Is it better to use AUTOCOMMIT = 0

Is it better to use (for perfomance tuning) AUTOCOMMIT = 0 before bulk inserts of data in MySQL, if the insert query looks like INSERT INTO SomeTable (column1, column2) VALUES ...
1
vote
1answer
945 views

does last_insert_id() returns correct result when writes from multiple sessions?

I have a table in which there is a combination of 4 columns that will act as a unique key. Instead of using a composite primary key, I want to keep an id column as the primary key. This field is on ...
1
vote
1answer
262 views

Giving a name to a dynamic column

I have written a query to fetch a time internal: select *, (Time + interval '2012-12-18 13:36:47' minute) from chatnotiservice; Is it possible to name the (Time + interval '2012-12-18 13:36:47' ...
1
vote
3answers
3k views

Is there a free software that can import excel into mysql on mac? [closed]

not csv format files, but .xls or .xlsx format excel files, is there any free software for mac, that can import data into mysql?
1
vote
1answer
924 views

procedure is not executing

Problem I am getting when I execute this proc: call `sp_frm4_px_prc_calc`(); Error Code : 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version ...
1
vote
3answers
246 views

How to convert MySQL Keyword to normal column

We thought of upgrading MySQL to new version 5.5.16. While testing the scripts with the new version, I had found one column Maxvalue is a Keyword in latest version. For this one, we need to change ...
1
vote
3answers
893 views

Please review the My.INI settings of my PC. File Attached

Below is the my.ini file of one of my PC running MySQL 5.5 with 1 GB RAM. It is not using concurrent connections and we are using a stand-alone PC for a single application and single user. # MySQL ...
1
vote
3answers
721 views

Best architecture solution for heavy data mining system, that can run on cloud

We are currently hosting our MySQL database on a dedicated server with lots of memory and processors. We would like to push it to a different architecture that would allow us to scale it easily, ...
1
vote
2answers
66 views

MySQL to SQL Server - Unixtime being major problem

I'm trying to convert this query to SQL Server. It already has a lot of updates. Still, I don't know what to do from here ( i'm not the best when it comes to programming in MySQL ) SELECT ...
1
vote
4answers
91 views

How to make a remote mysqldump?

I know that's it's possible to do a remote mysqldump if I specify --host but is it possible to create the file in the remote server as well?
1
vote
2answers
35 views

Index and rows placement within database files

Where are the indexes and data tables stored within a physical database file or a page of a physical file : Can there be different rows from different tables in the same physical page ? How does ...
1
vote
3answers
44 views

MySQL select with between optimization

I got issues with some geo IP query. So basically here is create code of table: CREATE TABLE `geo_ip_city` ( `id` INT(5) UNSIGNED NOT NULL AUTO_INCREMENT, `begin` BIGINT(20) NOT NULL DEFAULT ...
1
vote
3answers
212 views

HA - MySQL Master Master replication - Duplicate entry errors

I am running Percona MySQL 5.5.39 on two masters with keepalived monitoring a VIP which will failover to the standby master if the primary master goes dark. While testing the fault tolerance of my ...
1
vote
1answer
50 views

Unable to delete a record in Mysql

Let's say I have table name Company with two columns cname, location. In the table I am having two records with cname |location Asmetric |Dallas Sophore |Washington on this table when I ...
1
vote
2answers
79 views

Creating a database with only MySQL command line?

The linux server on which I'm attempting to create a MySQL database has many difficult to work with permissions. My supervisor has tried to install MySQL Workbench on the server, but has been unable ...
1
vote
3answers
261 views

How to auto replicate specific columns only from a table in MySQL

I want to make one master DB which replicates specific user data into other databases such as: username pass (hashed) email pin (hashed) timezone (int) They are all in one table and running on ...
1
vote
1answer
150 views

MySQL Enums have non-intuitive behaviour, why?

This very helpful article explains some oddities about how MySQL Enums behave: http://melp.nl/2009/05/mysql-enums-and-booleans/ There is no discussiong about why that behaviour exists. Furthermore, ...
1
vote
2answers
506 views

Why is MySQL not using the index with the higher cardinality?

I have an index called source with cardinality of 1122 and a second index called state with cardinality of 22. MySQL keeps using the state index when when query SELECT C1 FROM tbl WHERE source = 'x' ...
1
vote
3answers
3k views

Database design to track student attendance

I was having a doubt regarding the design of a database table to track students' attendance. Currently, my table students have atleast 4000 students. Creating a database for attendance to track their ...
1
vote
2answers
382 views

Slow query responce on different mysql versions, percona, MariaDB

LEFT JOIN is supposed to be faster than a NOT IN query. This is what I've read online. But practically, this seems to be opposite. I have the following query here, INSERT IGNORE INTO callrequests ...
1
vote
2answers
409 views

Left join not working like expected

I need to do a table join to find missing translations in a translation system. Let's say we have the following simplified table (id is the identifier of the string, the actual translation is not ...
1
vote
1answer
320 views

MyISAM vs InnoDB for Forums [closed]

I want to design a forum script with MySql. Which database engine would have better performance? MyISAM or InnoDB ?
1
vote
1answer
8k views

Enabling LOAD DATA LOCAL INFILE in mysql

LOAD DATA LOCAL INFILE is not enabled by default. Normally, it should be enabled by placing local-infile=1 in my.cnf. But it does not work for all installations. In my experience, it worked for Debian ...
1
vote
1answer
701 views

What does a round relationship icon mean in MySQL Workbench Visual Data Model

I have created a model in the Visual Data Model of MySQL Workbench, and on one single relationship, this icon shows up: What does it mean? The Relation is 1:1, and this icon shows only on this ...
1
vote
3answers
209 views

Is MySQL replication okay after writing to slave then making identical write to master?

I'm running MySQL 5.5.31 and I have a master/slave setup. I accidentally ran a mysql UPDATE on the slave, a simple one-row update like: UPDATE thetable SET somefield = 'asdf' WHERE id = 1234 I ...
1
vote
1answer
4k views

What is wrong with table_cache hit rate?

In my.cnf I have: table_cache = 524288 open_files_limit = 65535 Both are at max allowed value for mysql config. Both are less than max open file limit: # cat ...
1
vote
1answer
469 views

Watch output of MySQL query over time

I'd like to run a query every few seconds and show the output in a console window. From bash, using watch: watch "mysql -A -pXXXXX -h host -D dbname -u username --vertical -e \"SELECT rowID FROM IDs ...
1
vote
1answer
10k views

Performance difference between MySQL and PostgreSQL for the same schema/queries [closed]

I'm a newbie DBA, and I have experience in Microsoft SQL Server but I want to jump to FLOSS. I'm starting a company, and we develop an app (PHP) with a Postgres backend, and we did some tests ...
1
vote
3answers
130 views

Correct way to perform an UPDATE on multiple tables?

This query is working, but I am only just beginning to understand relational tables. I have spent the last few yeas doing all of the processing with PHP after retrieving the data I needed (oftentimes ...
1
vote
2answers
2k views

SQL Server to MySQL migration - how can I remove UCS-2 surrogate pairs in SQL Server?

I have been tasked with migrating a SQL Server 2005 database to MySQL 5.6 (these are both database servers runnig locally) and would really appreciate some help. -------Edit - Answered------- This ...
1
vote
1answer
189 views

INSTALL PLUGIN 'rpl_semi_sync_master' SONAME 'semisync_master.so' not working

I'm trying to install semi synchronous replication in my mysql 5.5 and trying to execute the below command: INSTALL PLUGIN 'rpl_semi_sync_master' SONAME 'semisync_master.so' but this command is ...
1
vote
1answer
1k views

Graph Database with “non-graph” data

--- Update --- Thanks for the comments and the help so far. And I apologize for not specifying the question further. I have updated the question below. --- Update --- Currently I am asked to develop ...
1
vote
6answers
18k views

How to search whole MySQL database for a particular string

is it possible to search a whole database tables ( row and column) to find out a particular string. I am having a Database named A with about 35 tables,i need to search for the string named "hello" ...
1
vote
2answers
381 views

Are MySQL transactions under-utilised?

I have a vague understanding of transactions that it means you can help the server understand that a series of queries are related. Scenario : Suppose I have a series of queries which creates a user ...
1
vote
3answers
6k views

How can I copy data from one MySQL server to another based on a SELECT statement (then delete the data from the original)?

I have a very large log table from which I want to copy rows, putting them into the same table structure on a new server. I don't want to copy everything, only old rows, so that the table on the main ...
1
vote
2answers
273 views

What exactly do database passwords protect?

I would like to know what exactly I am password protecting on my server when I set a database password. I am unclear as to what people are referring to when saying to password protect a database. Are ...
1
vote
3answers
177 views

Where can I learn about MySQL master/slave, master/master, or clustering setups?

What are the best resources you've read or watched about setting up master/slave or clustering for database replication? I don't know much about this. I have a database that is getting rather large ...
1
vote
2answers
713 views

How do I remove duplicate entries in a given timeframe?

My table looks like this: +-----------+ | id | <- primary key | time | <- DateTime | row2 | <- varchar | row3 | | ... | +-----------+ I want to remove all ...
1
vote
3answers
1k views

MySQL ignoring some params from my.cnf

I have a virtual box with 2 installed instances of MySQL. First instance: - /etc/mysql/my.cnf - /var/lib/mysql - port = 3306 Second instance: - /etc/mysql2/my.cnf - /var/lib/mysql2 - port = 3307 ...
1
vote
2answers
7k views

MySQL Workbench, how to refresh EER diagram?

I'm using MySQL Workbench to design my MySQL database and model how entities would look like and how they would relate to each other. Now, sometimes I use Doctrine ORM. When using Doctrine ORM, I ...
1
vote
1answer
159 views

mysql: show which users have permissions to a DB

Is there a way to show all users that have permissions on a database? I'm looking for something like: > show_permissions_for_db <DB>; +----------+----------------------+ | User | ...
1
vote
2answers
4k views

MySQL - INSERT INTO … SELECT … UNION

I'm having an issue with a fairly complex query where I need to store the ids from one table (based on heavy criteria) into a narrow, alternate table for future reference, but I keep running into a ...
1
vote
2answers
250 views

Mysql slows down temporarily after large file copying

We have a MySQL 5.0 server on a Centos box. 32GB memory. We have only one harddrive, 374GB capacity, 50GB available. All our tables are MyISAM. When we copy a large file (2GB-3GB) within the hard ...
1
vote
1answer
129 views

Help Optimizing Query

For the following table structure in MySQL 5.1.49: CREATE TABLE `leads` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `institution_id` int(10) unsigned NOT NULL, `lender_id` int(10) unsigned ...
1
vote
2answers
272 views

Does this cause overhead ? Select name from User where user_name LIKE '%alex%'

I'm wondering if this statement can cause real overhead if many concurrent users send the same request to mySQL InnoDB storage engine. Let's say I have a table that has the user_name, and someone want ...