MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
0answers
3 views

One field with multiple values?

New here. I know that what I'm asking can't be done or shouldn't be done, but I really don't know how to do it. This is the problem: 2 tables: Drivers (id_driver, name, etc...) Trucks (id_truck, ...
0
votes
1answer
6 views

Workaround to avoid read-only table locking during execution of MySql OPTIMIZE command

We need to optimize a large table (on a weekly basis) in order to reclaim disk space, since records are being deleted regularly from the table. The table in question is an InnoDB table and the ...
0
votes
2answers
19 views

VARCHAR primary key - MySQL

Currently, I have a categories table with 2 columns - category VARCHAR(50) NOT NULL PRIMARY KEY and parent VARCHAR(50). The parent column is a foreign key (FK) to the category column. This seems like ...
0
votes
1answer
2k views

Amazon RDS - Can't connect to MySQL database server?

So I've been working on this issue for a while now, and I'll explain what I'm trying to do. I'm trying to connect to a MySQL database server, hosted by Amazon Web Services in the Amazon RDS service. ...
1
vote
1answer
3k views

MySQL crashes randomly with “Database was not shut down normally” message in log

I am having an issue where, for no apparent reason, my MySQL server crashes and has to restart. Essentially, the error log has entries like this: 131128 12:16:35 mysqld_safe Number of processes ...
0
votes
0answers
4 views

MySQLfabric — Manage setup problem

MySQL fabric does not get configured , getting these errors : [mysql@dev-dm-gitest101z /root]# mysqlfabric manage setup Error : [INFO] 1413953890.788408 - MainThread - Initializing persister: user ...
0
votes
2answers
36 views

How to allow remote connections to mysql from specific IPs only?

I have a fresh install of mysql server 5.2.6 on a 64 bit Ubuntu running on a remote server with static IP. Is there anyway to allow Mysql remote connections to be made from pre-approved IPs only? I ...
1
vote
1answer
23 views

Query where there are only two rows with specific data

I have a table with room which has a data of who are in it. Each room has have 0 to any number of people and each person can be in multiple rooms simultaneously. (simplified below) id name 1 ...
1
vote
1answer
14 views

mariadb - EXPLAIN works but ANALYZE throws ERROR 1064 (42000) error in SQL syntax

I want to see the execution plan of a query to find out why it is slow, but ANALYZE does not seem to work, it throws an error on the same query that EXPLAIN accepts. MariaDB [dbx]> explain select ...
0
votes
1answer
14 views

How to get all the months from the last paid date in MySql?

I need to get the all the months from the last_payment_Date for the user from that table for processing purposing. As of now i am using this query.. SELECT `Name`, `Acc_No` FROM `user_table_details` ...
1
vote
1answer
15 views

mysql - sum interval dates

I would like to sum the hours for each name, giving a total interval between START and END activities. It would be simple if I could subtract from each record the end of the beginning. For example, ...
0
votes
2answers
999 views

Mysql Server Login and Permission Deny Error

I have a problem with MySQL server. After upgrading MySQL server from 5.3 to 5.4, I am not able to log-in using my MySQL root password (Yes, I am able to log-in without password!). Also not able to ...
1
vote
1answer
306 views

optimizing MySQL for traffic analytics system

background : I've developed a URL shortener system like Bitly with same features , so the system also tracks clickers info and represent as graphs to the person who has shorten the link as ...
0
votes
0answers
10 views

How to model efficient permission checks for a social network?

The goal is to build a facebook like permission system. So that users can grant other users access on objects based on a direct assignment (allow user X to see posts) or allow users to access them ...
0
votes
2answers
56 views

Query to test and return timed lockout in MySQL

I have some functionality in my webapp that locks out users from certain behavior for a set amount of time (e.g., they have to wait 10 minutes before they can update a level). My query looks like ...
0
votes
2answers
238 views

What are the different ways to keep track of active and archived data?

I'm looking for different ways to keep track of both active and archived data so I can pro and con them. The system: I have a computer with a database on it. The database has several tables in it; ...
0
votes
1answer
173 views

Cross tab query with concat field

I want to create a cross tab query for my table.but when i am using concat i am unable to get ouput succesfully.can any one help me with my query. SET @sql = NULL; SELECT ...
0
votes
0answers
25 views

“DISTINCT” JOIN in mySQL

+----+-------------+-----------+---------+ | id | login_ip | hostname | user_id | +----+-------------+-----------+---------+ | 1 | 192.168.0.4 | 'host 1' | 1 | | 2 | 192.168.0.4 | 'host ...
0
votes
0answers
22 views

MySQL Upgrade from 5.5 to 5.6 causes system / table locks

We recently upgraded from MySQL 5.5 to 5.6 on AWS RDS. Before, we had a relatively performant database - definitely had its quirks, but it seemed to be tuned relatively well. After the upgrade, we're ...
2
votes
2answers
2k views

How do I add a MySQL linked server to SQL Server?

I am trying to add a linked server on SQL Server to MySQL Server. Note the SQL Server is installed on a separate machine with a different domain name. The SQL Server is 10.0.1.1 on old_domain.com. ...
0
votes
1answer
46 views

Retrieving month from date field in my database

i have dates stored in database with the function : CURDATE() , when a user makes an order . Now with a form where the admin chooses the month and the year , i want a query to display all the orders ...
3
votes
2answers
337 views

MySQL runs out of memory when importing innodb database

I have a small VPS with 512MB RAM and no swap (can't have any). I am importing a 1.5GB innodb database with mysql -u -p < database.sql Before the operation can finish it says : Lost connection ...
1
vote
2answers
40 views

Upgrading hardware for MySQL. If we get SSDs, are CPUs likely to become a bottleneck

We are upgrading hardware for a MySQL database server (DB is around 200GB). We're getting 4x Samsung 850 Pro SSDs and are planning to run them in a RAID10 configuration. Also, 128GB of RAM. The ...
0
votes
0answers
19 views

Too many keys specified; max 64 keys allowed org.hibernate.tool.hbm2ddl.SchemaExport

I am using hbm2ddl to create a MySQL 5.6 database. There are reasons why certain tables need to share keys with large numbers of other tables. For example, one table represents a datatype, which ...
1
vote
1answer
33 views

Can't start MySQL server because of wrong file permissions?

I'm trying to start the server and then fix the root user by $ mysql -u root mysql $mysql> UPDATE user SET Password=PASSWORD('my_password') where USER='root'; $mysql> FLUSH PRIVILEGES; But ...
3
votes
1answer
67 views

When is mariadb creating tmp tables?

I have found, that copying to tmp table is taking most of the time of a slow query, that I wanted to optimize. I watched the output of 'profiling' in a terminal. This happens on a pics table with 6000 ...
1
vote
1answer
30 views

Trying to combine one row from one table with mulitple rows from another table but not sure if I'm doing it right?

The Situation I have two tables unit and unit_translations defined as: CREATE TABLE `unit` ( `id` int(11) NOT NULL auto_increment, `title` varchar(255) NOT NULL COMMENT 'Title of unit', ...
1
vote
1answer
19 views

Converting 'False'(string) to False(boolean) During LOAD DATA LOCAL INFILE

In MySQL is there anyway of loading "False" into a boolean column. While loading data in table having boolean column and where data is in "True" or "False", it gives warning of: "Incorrect integer ...
0
votes
0answers
13 views

Best way to approach building search functionality using PHP on existing mySQL Database

I am looking for advice on how to approach an upcoming project I am due to undertake. The reason I am asking is that I'm just out of college and the company I work for is currently searching for a new ...
2
votes
1answer
121 views

The client need 'SUPER' privileges to the reports DB in order to create and maintain my Functions

I have problem with one of my client Provided super privileges to the client James on 'Reports ' database , but the problem is the client need 'SUPER' privileges to the reports DB in order to ...
0
votes
0answers
28 views

MYSQL Out of memory, table crashing

an specific table on my DB keeps crashing. I suspect to be a bad buffer setting on my.conf, it show this error before crashing (most of the time it needs to be repaired): 18:26:59 [ERROR] ...
2
votes
1answer
205 views

Bi-directional replication for the same MySQL table

AppA stores/retrieves data from dbA.tableA AppB stores/retrieves data from dbB.tableA tableA definition is the same across these databases. To start with dbB.tableA was copied from dbA.tableA ...
1
vote
3answers
52 views

Deleting data from a large table with replication

I need to delete all the rows from a given table. The table contains millions or records. The master database is being replicated to several slaves and I wish to do that without creating a replication ...
1
vote
1answer
119 views

Mysql - Select To XML

I'm Building Audit Trail with Trigger, and want to convert the inserted / updated / deleted value into XML and store it into another table. my trigger look like this: DELIMITER $$ CREATE ...
0
votes
1answer
11 views

Importing a SQL Dump File through XAMPP's Command Line

A few days ago, I exported a .SQL dump file with all my databases, which is about 150MB. The thing is I cannot import it through phpMyAdmin since the file is way to big. I remembered there was ...
0
votes
1answer
60 views

Tuning Mysql and Monitoring

I would like to know if anybody can point me towards a tool/way for MySQL database tuning ... Actualy the tunning of my.cnf is like below My database is in MyISAM | Mysql 5.1.73 key_buffer = 500M ...
0
votes
1answer
14 views

Using pt-table-checksum to compare a slave's DB to another slave

I understand that pt-table-checksum can do checksum compares between a master's DB and a slave's DB easily. However, let's say your master has two slaves replicating from it for the same DB, i.e. ...
0
votes
0answers
16 views

Using partitioned table, and dealing with auto_increment PK

I have a big table (>100 million rows) that tracks user's activities. It has an ID, which is PK and has auto_increment property. This is the only unique key. Table also has userid and datetime column. ...
3
votes
2answers
149 views

MySQL 1264 warning from libmysql

I'm using the libmysql.dll library to connect to my database from a 3rd party application (metatrader). I managed to connect and things are going almost fine. I use mysql_real_connect and ...
0
votes
1answer
22 views

increasing disk space for mysql data volume

I have a master mysql server which has approached 80% of the available disk space. Total disk space size is 200GB. Though, it's not an immediate concern but I have to deal with at some point soon. the ...
0
votes
0answers
14 views

How to store highly configurable relationships based on multiple conditions

I'm trying to devise a schema that needs to be extremely configurable when necessary, but also minimal wherever possible. There is a large number of condition types that would lead to a number of ...
0
votes
1answer
17 views

How to deny access to any system user who attempts to log in without password usage?

I have mysql-server-5.5 in Ubuntu Server 12.04 I realized that when typing mysql command logged as any system user I got access to mysql, despite I can only display test and information_schema ...
0
votes
1answer
22 views

InnoDB error: exceeded log capacity…but only during import

I see this error in mysql.log: InnoDb error: the age of last checkpoint is...which exceeds the log group capacity etc.. I think I understand it, but the fact is that it's happening only during a ...
0
votes
2answers
393 views

Can't create table, but table doesn't exist

I'm using these steps to create a table my_user, that already existed but somehow vanished from my database my_db: mysql> USE my_db; mysql> DROP TABLE my_user; mysql> ERROR 1051 (42S02): ...
0
votes
1answer
317 views

Trying to recover/import/transfer database from MySQL data dir

I used phpMyAdmin->export to create a backup of my database but I have now discovered it did not export the whole database, some tables was missed/refused. However at the time of doing the export I ...
0
votes
2answers
36 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 ...
0
votes
1answer
62 views

Can a slave with a dynamic ip update the master

How can I setup mySQL replication in such a way that a slave on localhost with dynamic IP can update the master as well as get updates from the master which has a static IP. The slave will not be ...
2
votes
1answer
334 views

MySQL table relations, inheritance or not?

Im building a micro CMS. Using Mysql as RDMS, and Doctrine ORM for mapping. I would like to have two types of pages. Static Page, and Blog Page. Static page would have page_url, and page_content ...
0
votes
1answer
493 views

MySQL failover - Master to Master Replication

My company is trying to implement a MySQL failover mechanism, to achieve higher availability in our webservices tier - we commercialize a SaaS solution. To that end we have some low-end VMs scattered ...
0
votes
0answers
15 views

MySQL on Server side PRIMARY KEY taking automatically random Id

I have problem in my database which i have uploaded in Database Version libmysql 5.1.73 In that i got problem like one PRIMARY_KEY field incrementing random values. inserted id's like 1, 2, 35, 67, ...