All versions of MySQL (not Microsoft SQL Server). Please also add a version-specific tag like mysql-5.7 if that is relevant to the question.

learn more… | top users | synonyms

0
votes
1answer
269 views

MySQL server parameters default empty string to null

How can I set MySQL server parameters to default empty strings to null on insert? is this possible?
1
vote
1answer
87 views

What is the difference between Com_xxx and handler_xxx?

Initially my com_delete variable and handler_xxx variables are nil. | Com_delete | 0 |+ | Handler_delete | 0 | +----------------+-------+ then I issued a delete query delete from gt ...
1
vote
1answer
71 views

Is there a public repo where I can contribute to the MySQL .NET connector?

We have found ourselves making some performance and scale improvements for the MySQL .NET driver from here http://dev.mysql.com/downloads/connector/net/ We know where to get the source in a ZIP ...
2
votes
3answers
2k views

sort a varchar field numerically in mysql

I have the varchar field named _tid, normally which contains numerical field, but it is also possible that may be contain string field. Now i wanted to to sort the column by numerically rather than ...
2
votes
1answer
122 views

MySQL InnoDB transactions with lot of delete instructions

We have a process that runs frequently and does the following steps on a mysql database of type innodb. SET autocommit = 0; DELETE FROM table WHERE category='something'; -- every 300 rows or whatever ...
3
votes
3answers
4k views

What's the most efficient UUID column type

For storing a 128 bits UUID there are multiple storage options: a byte[16] column two bigint/long(64 bits) columns a CHAR(36) column - 32 hex digits + 4 dashes. a UUID database specific column, if ...
-1
votes
3answers
47 views

Setting up a root user password

I'm newbie in MySQL and i installed MySQL5.5 Server and Client in my Debian Wheezy. All i did was enter "aptitude install myqsl-server". Now, i have no idea what to do! I found a webpage that says i ...
2
votes
2answers
8k views

Checking if an Username is already in use

I am writing some scripts for my website, and I am currently focussing on checking if a username is already in use. I have tried writing a SELECT script where the datafield is selected and checks if ...
1
vote
0answers
865 views

How to generate data for News Feed efficiently like facebook , googleplus , twitter e.t.c?

I have created table as post ( post_id int(15) auto_increment primary key , user_id int(11) , post_text varchar(2000) , post_type int(1), ...
1
vote
1answer
111 views

Joining Three Tables by using IDs as Foreign Keys

I'm starting out with learning MySQL and so far I realized I need to JOIN three tables. The three tables are Users Groups Members There is a list of users, a list of groups that users can join, ...
1
vote
1answer
85 views

Mysql super slow

I have a database of movies that I downloaded, it is around 51mb in size. One of the tables has more than 750k records(all separate insert statements(I believe this is bad)). The rest aren't so bad ...
0
votes
1answer
45 views

MySQL database configuration for fastening the select and insert queries

There are several DBEngines are available for MySQL DB. Currently I'm using InnoDB. My actual problem is, I have a table that consist of millions of data. Therefore the select queries that given to ...
5
votes
1answer
23k views

MYSQL - UPDATING multiple rows with different values in one query (for dummies)

I am trying to understand how to UPDATE multiple rows with different values and I just don't get it. The solution is everywhere but to me it looks difficult to understand. For instance, two updates ...
2
votes
2answers
224 views

Does having many databases affect MySQL performance?

I have huge unused databases in MySQL. Do they affect my MySQL engine performance even if they are unused? Do they consume memory on their own? Thanks
0
votes
0answers
184 views

Which local port does MySQL Workbench forward to in 'Standard TCP/IP over SSH' connections?

So if I was to set up an ssh tunnel for MySQL in terminal I would forward remote 3306 to local 3307: ssh -L 3307:127.0.0.1:3306 user@domain.com mysql -h 127.0.0.1 -P 3307 -u user -p db When ...
2
votes
1answer
940 views

Strange things with Avg_row_length

The data from one tables is dumped to file and inserted into calls_old table with load data infile it shows Avg_row_length 7366. Later I dumped table with mysqldump, droped table, then created again ...
2
votes
2answers
10k views

My.cnf with 64GB RAM

May the following my.cnf file be fine for a dedicated server with 64GB of RAM (on Debian)? I'm running just one website on the server and on high load data insert in some table takes centuries...it ...
1
vote
2answers
202 views

[SOLVED]The right syntax for handling an UPDATE trigger and logging what happened per column?

SOLVED The users table: CREATE TABLE `users` ( `id` int(8) unsigned NOT NULL AUTO_INCREMENT, `email` varchar(45) DEFAULT NULL, `username` varchar(16) DEFAULT NULL, `salt` varchar(16) DEFAULT ...
3
votes
1answer
717 views

Why didn't MySQL keep the innodb_file_format after loading a mysqldump

I was loading a mysql dump today when I ran into ERROR 1118 (42000) at line 279: Row size too large (> 8126) Which I thought was weird because I know I had turned on row compression and was ...
2
votes
1answer
324 views

Optimal table design for a medical center rental service and recurrent shifts

I'm trying to figure out the optimal design for the following situation I'm being presented. I'm developing a medical web application with MySQL as db engine. A medical center has 14-16 offices/rooms ...
0
votes
1answer
33 views

Upgrade mysql from 5.0 to 5.5 in master-master configuration leads to broken replication

We created two new mysql servers running 5.5. Our old server were 5.0. The two servers are set up with master-master replication - this was the case for the two 5.0 servers which had no issues. We ...
0
votes
2answers
2k views

MySQL Community Edition Capacity

I am using MySQL Community Edition on Amazon RDS service. It is a very large database, 2/3 tables has about 10 millions records and 4-5 tables has 7-8 millions records. Questions How much capacity ...
1
vote
1answer
2k views

What is a good/healthy mysql query execution time?

What is a good/healthy mysql query execution time? To my calculations: The site gets 1000 unique visitors per hr 10 page view for each unique visitor 5 minutes for each unique visitor ...
0
votes
2answers
73 views

Help required to design a database [closed]

Firstly I would like to apologies if this is not the correct place to ask this question but I know how good community spirit is around here from my time in Stack Overflow. I am very new to the world ...
1
vote
0answers
176 views

invalidating query cache entries(key) [duplicate]

when I tried to delete some records from a MYSQL server (version 5.0.95-log) and checked the process list I got the result as follows mysql> show processlist \G *************************** 2. ...
0
votes
3answers
396 views

MySQL: querying for latest messages in flat reply system

I've created a message system schema in MySQL as follows. It consists of two tables (messages, recipients). `Message_ID` int(11) NOT NULL AUTO_INCREMENT, `Message_Sender_ID` int(11) NOT NULL, ...
1
vote
1answer
2k views

ALTER TABLE AUTO_INCREMENT number in a Trigger

I want to have my auto increment column to increment by step of 3 upon insert. Can I do something like: Alter table `mytable` auto_increment = (select max(id) from mytable) + 3; in a Trigger? I ...
2
votes
3answers
2k views

Block user access to certain tables

DBMS allows the admin to grant table access to a user using something like: GRANT ALL ON mydb.mytbl1, mydb.mytbl2 TO 'someuser'@'somehost'; However, is it possible to grant all tables to a user and ...
0
votes
0answers
174 views

Slow query (Wordpress)

Can I speed up somehow the following query? Somehow to stop from creating a tmp table/filesort. Tried a couple of indexes but no luck. mysql> explain SELECT wp_posts.ID FROM wp_posts LEFT JOIN ...
1
vote
1answer
133 views

Correct way to write this query?

I'm trying to write a query that basically is searching by price, though sometimes the items may have a discount on them and this price isn't stored in the DB, only the discount percent and dates the ...
0
votes
2answers
2k views

mysql no longer defaulting to /tmp/mysql.sock

I was testing some software to see if it could connect to mysql correctly when mysql is not using /tmp/mysql.sock. I stopped mysql, editted /etc/my.cnf to add socket=/var/lib/mysql/mysql.sock ...
1
vote
0answers
204 views

Dealing with Multiple User Roles

I'm working on a project based on a CMS, where there is a built-in roles table. However, the CMS allows a user to have one role only using role_id And these are the requirements that struggling me: ...
1
vote
2answers
2k views

mysqld constant CPU usage when absolutly nothing is connected and no queries

I'm using mysql on a windows development machine, and all was good for a long time. But today my PC was running slowly, and I noticed mysqld.exe is using between 25% and 40% CPU. I've tried ...
1
vote
0answers
827 views

Why does this MySQL date_sub statement return wrong values?

I had a complex statement that was returning strange values and I traced it to the following line: SELECT id, updated FROM status WHERE updated > date_sub(NOW(), INTERVAL 6 MINUTE) ORDER BY ...
6
votes
4answers
18k views

How to change a MySQL previous slave to be a master and remove slave status information?

I have a master -> slave configuration where the master failed. I've been able to reset the old-slave to be a master and the old-master to slave from it. Fine. What I can't seem to do is to remove ...
2
votes
1answer
4k views

Unique insert performance: Primary Key vs Unique Index

I have a table of unique values (domains_unique), with collumn domain varchar(255), with more than 20 mil records. What's the fastest way to insert into the table, by keeping the domain unique ...
0
votes
2answers
117 views

Should I create a multi-column UNIQUE index?

I have created the following MySQL table, which stores which sections of our site a user visited (and the Count of visits): CREATE TABLE `Visits` ( `ID` int(10) unsigned NOT NULL AUTO_INCREMENT, ...
1
vote
0answers
1k views

“Temporary file write failure” when trying to change primary key in MySQL

In MySQL, When I trying to change primary key by this query: ALTER TABLE `resources` DROP PRIMARY KEY, ADD PRIMARY KEY (`role_id`, `category_id`, `lang`, `model`, `rule`); I execute this query ...
0
votes
1answer
43 views

Trying to get this MySQL Trigger working

create trigger check_td before for each row begin declare v_code_ID varchar(25); declare c_Worker1 cursor for select code_ID from Worker1; declare c_Worker2 cursor for select code_ID from Worker2; ...
2
votes
1answer
468 views

How does the alter command work internally when adding a column?

Can anyone explain what the internal operations are that takes place when an alter command is issued? How is column modified? Does it use created_tmp_tables or created_tmp_files? As per my test, I ...
-2
votes
1answer
66 views

How to freeze a view in standard SQL? [closed]

I have the following: create view ONE as select ID workers_A; create view TWO as select ID workers_B; create view FINAL as (select * from ONE) UNION (select * from TWO); That works correctly, but ...
0
votes
0answers
129 views

tables don't exist after unmounting and remounting external drive where database was installed

I want to create a database that's too big to keep on my own machine, so I'm trying to create one on an external hard drive. It works up until I unmount and remount the drive, at which point show ...
1
vote
3answers
353 views

Mysql to Oracle communication

I have a website running with php and mysql on Centos(Amazon instance). The newly brought ERP(to be integrated with existing system) uses Oracle as db(located on a separate Windows Server). The orders ...
0
votes
2answers
158 views

“GROUP BY” in ranges?

Suppose I have a table with a numeric column (lets call it "score"). I'd like to generate a table of counts, that shows how many times scores appeared in each range. For example: score range | ...
0
votes
0answers
144 views

Would an INNER JOIN query in a sharded relational database hit the same shard when using a compound primary key?

In this question I have asked about modulus calculation in sharded tables, this design is used: CREATE TABLE Orders (order_id INT PRIMARY KEY, ...); CREATE TABLE LineItems (order_id INT, lineitem_id ...
2
votes
1answer
85 views

Backing up large MySQL DB for DR purposes

Fundamentally I am just trying to come up with the best way to back up my MySQL DB nightly. Complicated by the fact that: its a 400 gb DB and growing its running on windows I am not permitted any ...
0
votes
0answers
14 views

Specific id to appear first in result of query [duplicate]

I have a sample query where: Select gID, entID, cID, gString From tblGF Where ((entID=$entID And cID=0) Or (entID=$entID And cID=$cID)) And (gFP='BB' Or gFP='CP' Or gFP='PS' Or gID=$gID) Order ...
3
votes
6answers
1k views

Database Normalization - Can one field point to another?

This may sound like a dumb question, but I am a software developer new to database design... So the concept makes sense to me, but maybe this doesn't carry-- Is it possible for a field in a record ...
1
vote
1answer
477 views

wordpress table is crashed and should be repaired

I run a wordprees website and use a managed VPS server with cPanel/WHM. Got bellow error log and check the table. I repaired the table with repair table command from phpmyadmin. But this problem occur ...
2
votes
1answer
164 views

How can I solve this error InnoDB: is in the future?

InnoDB: is in the future! Current system log sequence number 412018685584. InnoDB: Your database may be corrupt or you may have copied the InnoDB InnoDB: tablespace but not the InnoDB log files. See ...