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

-2
votes
1answer
145 views

MySQL MATCH vs JOIN

I have a lot of data to filter and therefore I wonder is it better to create somekind of indexes and save them to one table and use MATCH or LIKE to find matches or should I use many different tables ...
3
votes
2answers
7k views

My MySQL server time is not the same as my Server

So a month ago I had purchased a server from a hosting company. The info are; Linux Centos (latest version). I installed ZPanel which installs phpMyAdmin, MySQL and all the other good stuff. ...
1
vote
0answers
635 views

Delete operation mysql cluster

I am trying to delete some entries from table on a cluster and I get this error ERROR 1297 (HY000): Got temporary error 233 'Out of operation records in transaction coordinator (increase ...
2
votes
1answer
216 views

Scaling of PostGIS vs MySQL cos/sin calculations [closed]

I need to sort database rows according to GPS coordinates (or any other geographical representation). The way I've understood it, MySQL Spatial Indexes are too buggy at the time of writing (at least ...
0
votes
0answers
254 views

Percona XtraDB Cluster With Async slaves

I have set up a 3 node percona cluster in amazon ec2. As our client required, i also had to set up an asynchronous slaves in another datacenter as well. Each async slave pointed to single percona ...
1
vote
0answers
270 views

phpmyadmin Lock Down

I have a client that wants to use myphpadmin to manage his mysql database 5.x on my server. I found this article on how to hide databases from myphpadmin but I don't like this because if I don't ...
4
votes
1answer
522 views

Does the mysql parameter “max_connections” apply for unix socket connections?

The documentation does not specify if max_connections apply only to TCP connections. The way it is said, it looks like it does apply for any kind of connection, but I'd like to be sure because ...
3
votes
1answer
82 views

How identify tables with millions of entries

On a debian server with apache and mysql, how can I find out if any one table gets spammed? I have lots of different blogs, wordpress, wikis,... on my server of different customers. It seems like ...
1
vote
1answer
7k views

Trigger to increment a column value after update in a column of same table

I have a table with two columns: C1 | C2 1 | 3 Whenever C1 gets updated to 0, C2 should be incremented by 1. I have developed a trigger: create trigger increment after update on T1 begin ...
9
votes
1answer
11k views

Autoincrement primary key in CREATE TABLE … AS SELECT

I created table using a complicated select query via CREATE TABLE ... AS SELECT.... How can I add an autoincrement primary key in this query? For example: create table `user_mv` select ...
2
votes
1answer
789 views

Calculating the median value in a MySQL table with CodeIgniter [closed]

This is my model file in codeigniter. I need to calculate the median for this variable: budget_breakdown.breakdown_grants. How can I do this calculation? function reports_details1() { ...
1
vote
0answers
107 views

Any drawback to lots of creating and renaming tables at InnoDB?

I'm trying to optimize an important cronjob for my program, and what it basically does is generate a long list of user ids with their score points. I have a table called user_ranks and it will bulk ...
2
votes
1answer
556 views

MySQL replication error 1146

Any idea why there is '1146 - table doesn't exists' on 'CREATE TABLE IF NOT EXISTS...' statement. It actually happened only on one of the slaves (there are two in total): Last_SQL_Error: Error 'Table ...
4
votes
1answer
846 views

Better to create a table for each user account, or one huge table for all user data in MYSQL

I'm creating a web directory that will allow individual users to signup for an account and store essentially text documents in a mysql database entry. Initially there will likely be only a few ...
3
votes
0answers
106 views

Table Structure: 2 Related Entities Share the Same Entity

I was hoping I could get some advice on my table structure. Im writing a simple workout tracker for me and my buddies. These are the requirements: Multiple users A user can have multiple workouts A ...
0
votes
2answers
562 views

How can I write a routine to check, optimize and analyze all tables in databases in MySQL [closed]

How can I create a routine in MySQL to check, optimize and then analyse the tables? Also how can I schedule this routine to run every night at set time? Thanks
2
votes
2answers
2k views

changing TEMPORARY TABLE default collation

when I use CREATE TEMPORARY TABLE to create a temporary table, the collation of this table is utf8_unicode_ci by default. why by default it is utf8_unicode_ci? How can I change the default?
0
votes
2answers
3k views

How to do aggregate functions in GROUP_CONCAT

I am generating a report to show outstanding Fee Like which Fee Type, How much amount is Fixed for that Fee Type, How Much Amount Paid for that Fee type and finally Balance Amount to be paid for that ...
2
votes
1answer
172 views

How do I determine when scaling is necessary or helpful?

I have a MySQL Server that I use for the back-end storage of an online game. The game writes to the database to persist character information such as world location, health status, ability stats, etc ...
-1
votes
1answer
893 views

Calculating employee's work time

I need to store and analyze employee's work time. Currently, I've developed this table structure CREATE TABLE `hirefire` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `employeeid` int(10) ...
9
votes
3answers
16k views

How can I determine how many IOPs I need for my AWS RDS database?

I am migrating part of a mysql database into AWS. The data in question is 99% write, and each row has about 1k of varchar fields, a datetime and 4 ints. I estimate that we will need between 20-25k ...
0
votes
1answer
949 views

my.cnf validation [closed]

We have moved our server from old 8 GB RAM Server to new 16 GB RAM server so that we could have better performance. The server is still consuming lot of MEMORY. The tables in the database are not ...
2
votes
1answer
742 views

TokuDB database size unknown in phpmyadmin

In mysql server, I installed the TokuDB storage engine. I converted database engine InnoDB to TokuDB, but my table sizes show unknown in phpmyadmin.
1
vote
1answer
591 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
votes
1answer
877 views

select query not displaying column names with records

If I execute a select query it is displaying only records not column names like as follows mysql> select movie_desc from movies limit 1\G; ************* 1. row ************* If the ...
1
vote
2answers
2k views

Distinct rows from a join

I have two MySQL tables: ╔═══════════════════╗ ║ wpss_question_set ║ ╠═══════════════════╣ ║ id ║ ║ quesset ║ ╚═══════════════════╝ ╔════════════════╗ ║ wpss_questions ║ ...
1
vote
1answer
2k views

Need advice for table design for multi-user access

I have an inventory application that needs to support multi-user. As of right now, only one user can access and manage their items. I've gotten a lot of requests to support multiple users so they can ...
0
votes
2answers
209 views

Mysql Command required for getting values from my.cnf

I am new to MySQL DBA, i have a doubt, Is there any command for finding my.cnf configuration file variables and its respective value which is mention in my.cnf file in mysql prompt itself. like we ...
0
votes
2answers
40 views

How to optimise this Mysql query

How do I optimise this query . Its taking long time. The inner query is fine. But outer query is taking more time . Please guide.Have attached the explain output. SELECT users.ID, IFNULL( CONCAT( ...
0
votes
1answer
359 views

Query to get reposts from people the user is following

I have a table posts where all the posts by users are stored, the structure of this table is as follows. | post_id | post_user | post_content | post_date | The users table is as follows. | user_id ...
1
vote
0answers
658 views

MySQL slave doesn't reconnect to master after master is rebooted

I've got a new MySQL replication setup with one master and one slave. In all of the following scenarios, the slave automatically reconnects to the master and resumes replication where it left off: ...
1
vote
1answer
109 views

What indexes to add on MySQL table

I've been trying to optimize my table, but am yet unable to get the desired results. CREATE TABLE IF NOT EXISTS `puzzles` ( `puzzleID` int(7) NOT NULL, `puzzleMask` varchar(30) CHARACTER SET utf8 ...
0
votes
3answers
1k views

Importing 40 gb mysql file via phpmyadmin

I have a 40 GB MySQL dump file. Can anyone give me the idea how to import the file via phpmyadmin?
0
votes
1answer
442 views

Database design for surveys in MySQL

I'm trying to design an app where users take a new survey everyday (that's not the main part of app but it's an important part). I've never designed a database before so what I'm trying to do might ...
0
votes
1answer
433 views

Max_packet_allowed setting for Windows 7

We have windows xp machine with older version of mysql. So when we need to import huge data we just set the max_allowed_packet=1500M in the C:\Program Files\MySQL\MySQL Server 5.1 . Now we loaded the ...
1
vote
1answer
949 views

When MySQL table gets too large

I have a really large MySQL table counting more than 12 billion rows at the moment (table scheme: InnoDB). From time to time the problems get more and more. The biggest problem is that altering the ...
1
vote
1answer
86 views

MySQL 5.6 DESTDIR not work propely

I'm try compiling MySQL 5.6 from source in Ubuntu 12.04 x64. But when I execute make install DESTDIR="/etc/mysql", not work was expected. MySQL is installed in /etc/mysql/usr/local/mysql. What's ...
1
vote
1answer
170 views

Increased server load after InnoDB deadlock

I am using InnoDB instead of MyISAM for a couple of months right now, and it has really optimized my game database-wise. I was aware of the danger of deadlocks, but hadn't had any problems with it, ...
1
vote
1answer
387 views

Creating a table with parent_id from a flat table

I have a flat table (id, city, county, state), Example (1,'Beckingen', 'Merzig-Wadern', 'Saarland') A city belongs to a county. A county belongs to a state. First my attempt was to divide the ...
7
votes
1answer
42k views

How to get rid of “maximum user connections” error ?

I am using MySQLi for my webapp but whenever I want to visit some particular page, I get mysqli_connect() [function.mysqli-connect]: (42000/1203): User ***_user already has more than ...
0
votes
1answer
139 views

Optimizing and maintaining test databases for a web development team

Right now when we test, we mysqldump from the production database, load it into the test database server, and then use scripts to scrub sensitive data. As the database is getting bigger, this process ...
4
votes
2answers
2k views

Replication error

We have a slave server that has stopped replication due to the following error: Slave SQL: Query caused different errors on master and slave. What could be the cause of this error? And what would ...
4
votes
1answer
947 views

Running the DBT2 test results in 0.00 NOTPM

I'm trying to run the MySQL DBT2 Benchmark Tool, I have unzipped the archive and run the following commands: ./configure --with-mysql make make install mkdir /tmp/dbt datagen -w 3 -d /tmp/dbt --mysql ...
0
votes
2answers
377 views

MySQL row does not persist, but primary key with auto-increment is incremented

I have a MySQL database that is acting oddly. I insert a new row and observe that for a brief period of time (less than 30 seconds), the row persists as expected in the table. After this time, though, ...
3
votes
1answer
785 views

Alter mysql database engine doesn't work from cluster to other

I got into this issue when I tried to alter my database table engine to InnoDB from ndbcluster, It did not work. But When I changed engine from InnoDB to ndbcluster it worked. It did not throw any ...
7
votes
3answers
20k views

Why do the MySQL bin log files still exist after a purge or flush?

I've used PURGE BINARY LOGS as well as FLUSH LOGS, but the mysql directory still contains these files: mysql-bin.000025 mysql-bin.000024 mysql-bin.000023 mysql-bin.000022 mysql-bin.000021 ...
2
votes
1answer
2k views

InnoDB: Error: pthread_create returned 12

have installed MySQL in a particular folder. I was able to run the server and create accounts, databases etc. However, now whenever I try to start the server, I get an error: $ mysqld_safe ...
0
votes
0answers
243 views

MySQL SSL encryption

Does setting MASTER_SSL to 1 in change master ensure encryption without specifying options MASTER_SSL_CA, MASTER_SSL_CAPATH, MASTER_SSL_CERT, MASTER_SSL_KEY ? After setting MASTER_SSL to 1, show ...
3
votes
1answer
182 views

MySQL Replication not proceeding

I have a weird replication problem I have not seen before. It's basic mysql replication with single master and two slaves. One of the slaves is not executing replication events and seems just stuck ...
1
vote
1answer
176 views

Replication Master/Master, both master stop by themself

I did a master/master replication with MySQL on a Gentoo OVH Release 2, all work fine in my test phase, i put it in production, the replication works fine in 1 or 2 days but this morning, i don't know ...