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
2answers
158 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
1answer
2k views

GeomFromText in trigger has error

I have this trigger: DELIMITER $$ CREATE TRIGGER geometrytrigger BEFORE INSERT ON users FOR EACH ROW BEGIN SET NEW.user_geometry=GeomFromText(NEW.user_geometry); END$$ DELIMITER ; when I insert a ...
0
votes
2answers
512 views

How can we check for data integrity when we upgrade from MySQL 4.1 to MySQL 5.5?

By data integrity, I mean: Schema integrity Data (record level) integrity Checksum was an option, but since the storage format for some data types in MySQL 5.5 has been changed, it won't be ...
1
vote
1answer
3k views

School database design

I am trying to design a database management system with my PHP and intermediate MySQL knowledge, keeping normalization in mind. These are the assumptions: The school has many students from grade 1 ...
4
votes
2answers
6k views

COUNT of rows with parent id equal to the row we have with a parent id of 0 in one query

I'd like to do the following in one query using MySQL: grab a row that has a parent_id of 0 grab a count of all the rows that have a parent_id of the row that we grabbed which has a parent_id of 0 ...
0
votes
2answers
118 views

Help creating this query

I'd like to integrate the following query: SELECT parent.id , COUNT(child.id) AS child_count FROM messages parent INNER JOIN messages child ON child.parent_id = parent.id ...
1
vote
0answers
41 views

How to use a database across two availability zones

I have servers in the USA and Japan for my website. At the moment they are both connecting to a MySQL server in the USA, which is obviously perfect for my local USA clients but quite slow for my ...
2
votes
0answers
830 views

How do I safely import MySQL data in a directory to a running MariaDB server?

We're integrating another office into ours. That includes some of their data. I have a new installed MariaDB 5.5 server. The data dir = /home/data/MariaDB. I've done a new/fresh install into that ...
3
votes
1answer
5k views

mysql works, mysqldump doesn't

I have mysql 5.5 on my ubuntu 12.04 server. This command: mysql -u root -p works perfectly, but this gives me error: mysqldump -u root -p mydb_name > a.sql mysqldump: Got error: 2002: Can't ...
1
vote
1answer
1k views

How do I get Pentaho Reporting/Kettle to produce PDF reports by business entity?

We use Kettle/PDI to handle usage records from legacy systems and feed them to an analysis database/DWH and also we report to our customers their activity and a backend commission report on a monthly ...
1
vote
2answers
403 views

multi master replication [duplicate]

Let us say I have a master(m) slave(s) replication. Now, in the scheme of things, I introduce another database/schema(foo) not associated with the master in any way and I want this schema to be ...
-3
votes
1answer
1k views

How to merge two rows in a table? [closed]

I have a table called Person with columns Col1, Col2, Col3, etc. In Col1 I have two values: ABC and XYZ. What I want is to replicate all the values from the other columns (Co2, Col3, etc.) for the ...
1
vote
1answer
970 views

Large table delete in mysql

I want to delete a table with 10,000,000 records. Due to some restrictions I can't do truncate on that table (I don't have the DROP privilege), I can think of two options: DELETE QUICK To set some ...
0
votes
1answer
157 views

Foreign key in MySQL

I am learning about database, I need to know something about the foreign key in MySQL. Consider the following two tables. UserGroupType. UserGroup. UserGroupType: UserGroup: In this table ...
3
votes
1answer
198 views

Most efficient ordering post database design

I have posts table that is has post_order column. I store order of each post in it. when I change the order of a row from 25 to 15, I should update all the row from 15 to end. It's good for few rows, ...
1
vote
1answer
3k views

Which is faster among MySQL Community Server, Percona and MariaDB [closed]

I have a small setup of MySQL Community Server version 5.6 running on KVM based VPS. 4 CPU cores are available and total memory is limited to 1GB. I use MyISAM tables mainly for Wordpress and few ...
4
votes
4answers
23k views

Broken mysql replication on slave server (error 1236)

My question is: how to fix broken mysql replication on slave server (error 1236)? server 1 (192.168.1.105) server 2 (192.168.1.106) I ran these steps to re-sync: At the master - server 1 (192.168.1....
4
votes
1answer
8k views

InnoDB log sequence number is in the future

130711 23:03:40 InnoDB: Error: page 23 log sequence number 3336825872455 InnoDB: is in the future! Current system log sequence number 235089. InnoDB: Your database may be corrupt or you may have ...
1
vote
0answers
111 views

Is it possible to keep track of changes to tables structure?

Is it somehow possible to keep track of changes made to the STRUCTURE (not the data) made to tables? I mean, i don't want to know when rows are inserted/updated/deleted, what i want is to know when ...
3
votes
1answer
1k views

Rare error lasts only a second “Can't connect to local MySQL server through socket '/tmp/mysql.sock' (146)” 146=ECONNREFUSED

This is a rare error that occurs on Solaris 10, have seen it from Perl, where this line DBI->connect(...) or die "Whoops! $DBI::errstr" outputs Whoops! Can't connect to local MySQL server through ...
0
votes
2answers
536 views

MySQL stored procedure create database by month

I'm a systems admin, and I'm not a dba, I'm wondering if I could use MySQL stored procedure to Query main database and move records by month to another database located on the same system. This ...
1
vote
1answer
519 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 ...
3
votes
1answer
5k views

Create Table If Not Exists Hanging

I am still new to using databases, but currently I am using a mysql database and attempting to make my bash script unbreakable because it might be used in several different environments (dev, qa, etc)....
2
votes
1answer
1k views

MySQL replication between datacenters

I am trying to figure out the best MySQL replication topology, but I am not sure the best way to lay it out. datacenter A | datacenter B ____ | ____ | | | | | | AA | |...
1
vote
1answer
505 views

Inserting filtered data from one table to another

I need to find the number of employees who are not in all departments. I have one more table T2 in which same department column is present. Now I want to insert only those values of department which ...
0
votes
2answers
3k views

Order table Database design

I'm in need of some help with the database design of order table of a shopping cart for online food ordering system. I have a design below. Tables about which I'm Concerned are order_details and ...
0
votes
1answer
555 views

Need to find the number of employee who are not in all department

i have a table T1 which has two columns empname , department every emp must be in 37 department , but there are some employee who are in less than 37 department . I need to find the names of emp who ...
1
vote
0answers
229 views

Mysql key_buffer_size already 16GB but some fulltext searches are still slow

I have a query w/ full text search which takes 5 - 10 secs. key_buffer_size is already 16GB. Table has 15 million records and has a compound fulltext index on fields: categories and company. Table ...
7
votes
1answer
9k views

Does mysqldump -all-databases include all objects

Does mysqldump -all-databases include all objects ? I have to migrate all databases to new server.
4
votes
4answers
22k views

Recursive self joins

I have a comments table, which can be simplified down to this: comments ======= id user_id text parent_id where parent_id is nullable, but might be a key for its parent comment. Now, how can I ...
2
votes
1answer
5k views

MySQL Query: Delete from 'table2' where column = 'value' IF column in 'table1' = 'value'

I am trying to execute a MySQL query to delete rows from 'table2' where column = 'value' IF column in 'table1' = 'value' I have 2 tables... Table 1 is called accounts Table 2 is called ...
0
votes
0answers
1k views

Push Data from MySQL to SQL Server

I am not sure if my title is correct, but I will try my best to explain the current problem. Current Scenario: I have a php web application with MySQL database (let's call it DB1) which is stored ...
-1
votes
1answer
63 views

What could I possibly be doing wrong with this update statement

UPDATE pcdbak SET pcdbak.Title = pcd.Title FROM pcd, pcdbak WHERE pcdbak.eCommNum = pcd.eCommNum This was the error message: [Err] 1064 - You have an error in your ...
0
votes
1answer
487 views

MAMP PRO not showing all MAMP database

I was earlier using MAMP and Now switched to MAMP PRO. I used to use MAMP mysql as root default user and having lots off database as that user. If i start MAMP and go to terminal with mysql command ...
2
votes
0answers
918 views

The nature of the relationship between two tables in MySQL

Good day. I'm designing a database for a research project where we're predicting whether or not young female athletes will rupture their ACL(s) based on the nature of a test jump recorded with a ...
2
votes
2answers
2k views

Changing the mysql.proc table directly after upgrade

Our host upgraded our mysql install without us knowing from roughly 5.1 to 5.5. This caused a problem because the table definition of mysql.proc is different as discussed here: http://bugs.mysql....
-1
votes
1answer
590 views

How to create a mysqldump file automatically from SQL server database on a daily basis

I want to create a mysqldump file from a SQL Server database, I know its possible using MYSQL workBench, what i want is to make it completely automate. So the script runs on a daily or weekly basis ...
1
vote
1answer
111 views

Unable to generate a MySQL query

I have a table in a MySQL database as below: ID Timestamp State 1 2013-07-10 09:00:00 Good 1 2013-07-10 09:10:00 Good 1 2013-07-10 09:20:00 Good 1 2013-07-10 09:30:00 Bad 1 2013-...
14
votes
4answers
20k views

How to estimate/predict data size and index size of a table in MySQL

I am finding what is the best way to estimate size of a table for that i have studied lot of blogs and forums but unable to find any accurate answer For an example we have a table City with InnoDB ...
9
votes
2answers
11k views

MySQL equivalent of WITH in oracle

Is there a MySQL equivalent of the WITH clause in Oracle?
6
votes
1answer
242 views

Database restructure - beneficial?

I have a table for email messages. Then, I have a table that has the message parts. The Parts table contains a field_id, message_id, and data At the time I used Parts to name the table and ...
1
vote
1answer
678 views

Can I force mysql server to resolve the external ip to localhost?

I'm finally succeeded with ssh tunneling. My point is to open the mysql server to local users only (user@localhost etc.) while providing a remote control to my customers through ssh tunneling. The ...
0
votes
0answers
683 views

Trouble converting MySql Database to SQL Server

I have an mysql database that contains 38 tables. I used "SQL Server Migration Assistant for Mysql" tool to convert this database to SQL server Database. I successfully converted 37 tables. Only one ...
1
vote
1answer
532 views

Mysqlbinlog statement reading

I am trying to perform a point in time on a specific database using a snapshot and mysql's transaction logs. I am using the following to pull statements from the time of the snapshot to the time of ...
2
votes
1answer
1k views

Would adding indexes to my foreign keys improve performance on this MySQL query?

Consider the following query: SELECT `locations`.`id` AS `location_id`, `locations`.`address`, `locations`.`lat`, `locations`.`lng`, `tickets`.`status_id`, `customers`.`name`, `tickets`....
8
votes
3answers
7k views

How to determine master in mysql master-slave

I am setting up MySQL Master-slave replication and I am trying to figure out how to handle the failover situation where I promote the slave to master (in the event that the master goes down). My ...
-1
votes
1answer
383 views

MySQL 5.6 Delayed Replication - cancel delay questions

I'm using the MySQL 5.6 Master/Slave replication, with Delay set for 2 hours. My questions are : Q1. What is the proper way to cancel the Delay at the slave - i.e. "roll forward" all the changes ...
4
votes
1answer
17k views

Display Monthly Attendance Report in MySql

I am doing a School Management System in php using Mysql DB. I am stuck in my Project. Please anybody suggest what I am doing wrong. I have two tables in my database; one is to store Students records ...
3
votes
1answer
6k views

mysql ibdata1 corrupt

I do not know whether this is a mysql(version 5.6.11) bug, the case is as following. today when i update mysql conf and kill -9 and restart mysqld, i find that it can't restart anymore. after having a ...
0
votes
2answers
4k views

Difference between two recordsets

I'm looking to calculate the difference between two record sets in MySQL. Say I have the following table: colname type attrbutes id uInt PK date date PK count uInt With this data: id ...