MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
0answers
6 views

MySQL column type to boost performance

I'll have a table where: userid (link to user's id in users table) foldername (a varchar, max. 260 char) entry (varchar, max. 1024) data (varchar, max. 20kb) Single user will have several foldername ...
0
votes
0answers
10 views

Optimal Table Index for hourly data

I have a table that contains ~40,000,000 records and consisting of the following fields: idName - foreign key relationship to another table Date Hour Type (two possible types) Value Currently I ...
0
votes
1answer
20 views

MySQL dump - how to order tables?

It seems that mysqldump dumps tables in sequential order - using the same order tables are shown when running: show tables; I would like to reverse that order - is it possible to do without getting ...
0
votes
0answers
11 views

Insert data from a table and with parameters

I have 2 tables, one feeds data for 2 others but I must add external value too Is this query does the job ? MySqlCommand cmd1 = new MySqlCommand("INSERT INTO tableA (dt1, dt2) SELECT (dt1, dt2) FROM ...
0
votes
1answer
11 views

using foreign key as primary key

MySQL: If I have a user table and multiple other tables like seller, buyer etc, is it okay to use user_id foreign key as primary key inside tables like seller, buyer? That'll leave gaps in primary ...
0
votes
1answer
15 views

Why storing product fees in another table is preferred?

MySql: What is the benefit of storing several types of fees in another table like Product Table -> (productid...), ProductFee Table -> (productid, feetypeid, feeamount), ProductFeeType -> ...
1
vote
2answers
27 views

When is it safe to disable InnoDB doublewrite buffering?

MySQL InnoDB allows us to disable doublewrite buffering by setting innodb_doublewrite = 0. Other databases doesn't seem to allow this setting to be tweaked. How could InnoDB still be able to maintain ...
6
votes
2answers
544 views

Why do we use Group by 1 and Group by 1,2,3 in SQL query?

In SQL queries, we do use Group by clause to apply aggregate functions. But what is the purpose behind using numeric value instead of column name with Group by clause? For example: Group by 1.
0
votes
0answers
15 views

Advice on MYSQL DELETE on CASCADE, UPDATE on CASCADE features

I have 2 tables called copyrigihtinfo and sourceinfo and many to many to relationship table cp_src_junction with following data. copyrightinfo table cp_pk cp_info 1 copyright (c) 1991-2000, ...
0
votes
2answers
14 views

How can I make MySQL client read password from mylogin.cnf?

I'm trying to make the mysql client connect to a mysql server without requiring the password to be given interactively. Steps taken: 1) First create a mylogin.cnf file $ mysql_config_editor set ...
0
votes
0answers
19 views

Stored Procedure Issue in Simple Select Statement

Here is my Fiddle Here is My Table : CREATE TABLE IF NOT EXISTS `vehicle` ( `AutoID` int(11) NOT NULL AUTO_INCREMENT, `VehicleCode` varchar(20) NOT NULL, `NumberSeats` int(3) NOT NULL, ...
0
votes
1answer
21 views

Can we protect data form Database Administrator by encrypting the data?

How can we protect data on my database which is MySQL form database administrator stealing it? Will encrypting it prevent it from being stolen? If yes, how as he already has access to the database ...
0
votes
0answers
17 views

Large text column slows down whole table

I have a mysql database table table1 with a dozen columns in it. One column is a mediumtext column with lots of data in it and all the other columns are small (ints, timestamps, etc.). The presence ...
0
votes
0answers
11 views

In Forward Engineering I have an error called 'table does not exist'

This is my forward engineering code. -- MySQL Workbench Forward Engineering SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, ...
0
votes
1answer
10 views

odbc connection from windows machine to mysql server running on ubuntu?

I was able to install mysql server on an ubuntu machine and set up a test data base, created a user and granted that user access to the database. I also changed the binding address to the IP of the ...
0
votes
1answer
12 views

storing query results in a variable in mysql and using it in another query

I have a following query which runs perfectly fine: SELECT count(*) as total,l.state as state FROM db1.table1 cd inner join db2.table2 l ON ...
-1
votes
0answers
13 views

Cannot open ssh tunne on MySQL [on hold]

I got this error on this configuration Any Clue? Please Help!
0
votes
0answers
5 views

Can MySQL Index Merge an Composite index with a single column index?

I am tuning a query and currently have an index as following : Index 1 : (col1, col2) An example query would be : SELECT * FROM t1 WHERE col1 = '1' AND col3 = '2'; I have created an index on ...
0
votes
1answer
12 views

Update Trigger in MYSQL

Here is my requirement :- I have a table called "users" and field called "Admin". Field "id" is primary key. Wants to update "Admin" field to "1" if id=1000; Tried this ,but not working.I tried ...
2
votes
0answers
24 views

How to store a 64-digit key of alpha numerical values into MySQL Database

I am student studying in Database Administration (first year), one of our task requires a to design a login form for corporate and regular users. Below are the requirements for the username and ...
0
votes
1answer
17 views

Processlist show most of the queries are sending data for select query

I have faced some problem like sending data state in processlist. In my application I used parallel processing which send 50 count at a time, and that will do some MySQL database operation (select and ...
0
votes
0answers
16 views

Slow Query Performance - Can't Debug

I have a MySQL query that I am trying to debug the performance issue but cannot. I know that the majority of the query is reading/processing rows (Sending Data state). However, in the explain output, ...
1
vote
1answer
21 views

Is it possible: MySQL semisync and async replication on same master-slave pair?

I want to use master-slave MySQL (or MariaDB) replication to remote server as data protection from hardware failures. I have two goals: to keep low response time and don't lose important data. Good ...
0
votes
1answer
21 views

Relationship issue

I'm working on a ERD with mysql workbench, and i have a little question. I have a Database with this tables: company_address country state city I want to store the address of the company with all ...
0
votes
0answers
10 views

SSMA mysql double to sqlserver decimal, decimals getting cut off

I have a field that is a (signed) double in mysql, and I want it to be a decimal(18,4) in sqlserver. I have changed the Type Mapping for Source Types double and double precision to decimal[18][4] in ...
0
votes
1answer
17 views

xenforo vulnerability, limit max open connections by ip address

Trying to get some emergency help because my website is being attacked. It isn't as much as an actual attack, as it is a small bot to exploit xenforo vulnerability. ...
0
votes
1answer
49 views

Optimizing intersect query in MySQL

I'm facing a performances problem in our MariaDB server. We have a table linking users to pages, a user belongs to 1+ pages. I have a page that have users and I want to get the 10 best pages based ...
0
votes
1answer
13 views

MySQL audit storing application user ID

I am trying to set up auditing for changes to data in MySQL tables, for my Perl web application on Linux. I've had a look at this: ...
1
vote
2answers
23 views

MySQL returns floats rounded

> CREATE TABLE test(foo FLOAT); > INSERT INTO test VALUES(1.2899999); > SELECT * FROM test; +------+ | foo | +------+ | 1.29 | +------+ While it's obvious that FLOAT is not a precise data ...
1
vote
2answers
30 views

Optimal indexing for SELECT (mysql)

What indexes would you recommend for the following select (and why)? Keep in mind there can be tens of millions of rows. SELECT * FROM mytable WHERE md5 = ? AND created_at >= ? AND created_at ...
1
vote
1answer
22 views

trying to understand table size

Can somebody help explain the "data_length" of the table? Why is it so big in my example below of a table with just one column and one row? mysql> create table `network_type` ( `id` int(11) NOT ...
0
votes
1answer
21 views

MySQL 5.6 Datetime Range

According to MySQL 5.6 doc, the supported range for datetime data type is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. But when I tried inserting values that are earlier than '1000-01-01 00:00:00', ...
-5
votes
0answers
32 views

How much space does database takes to store twitter data? [on hold]

Context I am going to collect twitter-data(user,s followers, timestamp, content, url and Hashtags) weekly and it continues for an year. I have a total of 160 hash-tags, and I will be creating an ...
0
votes
1answer
22 views

MySQL Workbench is broken, is there another free, crossplatform tool I can use? [on hold]

I recently updated mysql-workbench-community to latest version 6.2.4 from 6.2.3, I now longer able to use it at all. I created several bug reports: MySQL Bugs: #75012: error while loading shared ...
0
votes
1answer
22 views

Duplicate entry error for unique key that doesn't exist

I have a model that had the following fields: +----------+----------+ | username | password | +----------+----------+ | AAA | *** | | BBB | *** | | CCC | *** | ...
0
votes
0answers
16 views

MySQL multi masters with replicas

Currently I have a Master-Master setup, however there is a need to create replicas servers in geographical areas. I am trying to figure out the best method to handle this. The kicker is not all ...
1
vote
1answer
42 views

choose Username or ID as primary key? [duplicate]

my sites dont need use ID at all, then I am trying to manage user by username. Assume my sites is abc.com, then access to user profile by URL: abc.com/user1 So if you want to view all info of that ...
0
votes
2answers
53 views

After mysql server restarts I cannot see my own database

By mistake I restarted MySQL server in Linux system. But MySQL server was unable to start. I renamed my.cnf to my_bak.cnf. After that MySQL server started. But I can not see my project database. I ...
0
votes
0answers
9 views

Deploy existing ms access file with forms as web application

We are using the following: a ms access file with local tables remote tables which are connected through the odbc connector to a external mysql database with many forms When using the ms access ...
0
votes
2answers
25 views

mysql.sock not found on CentOS 7 machine

I am getting the following error when I try to log into mysql from the CentOS 7 terminal of the machine where mysql is installed: ERROR 2002 (HY000): Can't connect to local MySQL server through ...
0
votes
0answers
21 views

Many transactions are getting stuck in the “cleaning up” state - what could cause that?

I'm trying to track down a locking issue, and found via SHOW PROCESSLIST that when the queries in question are waiting for locks and timing out, there are no other queries active in the process list. ...
1
vote
0answers
40 views

How to create the database for akinator clone? (github/projects/akinator) [on hold]

On sourceforge (http://Sourcecode.net/projects/akinator) I found a clone from akinator (an algorithm for finding a famous person or character via yes or no questions). From there I got the php files ...
0
votes
1answer
50 views

Can I lose data if I use InnoDB? [closed]

During a power failure, is it possible to lose data if I use InnoDB? I remember reading such things.
-1
votes
0answers
15 views

What is the difference between MySQL Innodb log files and MySQL binary logs? [duplicate]

I know that this question might sound a little dumb but I want to know exactly what is the difference between those 2. I know that the binary logs (log-bin variable) are used mostly for replication ...
0
votes
1answer
12 views

MySQL ORDER SELECT based on a score

I have a table 'member' and want to be able to search the table and order results by relevance. I have a single string to search on and want to search multiple fields. Example, given_name and ...
0
votes
1answer
41 views

How to call a windows program in mysql prompt in windows

I want to call an OS level commands from mysql prompt within Windows command line tool. In Unix* there is a system command available to execute OS commands within MySQL prompt. Any way of dong the ...
1
vote
1answer
20 views

MySQL slave replication permission error

I am having a rather frustrating issue. I have granted replication client to a user on the slave instance of MySQL. I can login using the username and password. I can exectute "SHOW SLAVE STATUS;" but ...
0
votes
0answers
21 views

Slow queries while using views in MySQL

We have MyISAM tables on MySQL 5.5 and there is this select * from theView where serial_number = 'QWE12345' or select * from theView where user_id = 1234 kind of queries when searching. When I ...
2
votes
3answers
52 views

How to dump only the MySQL database structure? [closed]

I am trying to take dump of MySQL database structure using the following commands: mysqldump -d -h localhost -u root -p databasename > dumpfile.sql mysqldump -d -h localhost -u root -p database ...
0
votes
1answer
34 views

dumping the database from Linux to Windows in Mysql

I have MySql db on Machine A , I have Mysql credentials. And accessing this MySQL from machine B which I have crdential's. But I couldn't take mysqldump from Machine B. Please help me. machene B is ...