MySQL Communtiy

Restoring from a mySQLDump...

Date: 01/28/09 Keywords: mysql, database, sql

Folks, I'm having what I think is a weird problem restoring a DB from a mysqldump. What I'm trying to do is basically copy a DB from one mysql database server to another.

The mysqldump command I used originally was:

mysqldump --quick --add-locks --extended-insert -u root -p dbname > dbname.sql

When this didn't work upon restoration I also tried:

mysqldump --opt -u root -p dbname > dbname.sql

Both these commands created the dbname.sql file with no problems or complaints.

I copied the SQL file over to the target computer. I went into mySQL as root and did:

create database dbname;
use dbname;
source dbname.sql;

The import would start, but it wouldn't get very far. In the end, after not many seconds it would just...stop. Here is an example:

Query OK, 8708 rows affected (0.11 sec)
Records: 8708 Duplicates: 0 Warnings: 0

Query OK, 8750 rows affected (0.12 sec)
Records: 8750 Duplicates: 0 Warnings: 0

Query OK, 8740 rows affected (0.11 sec)
Records: 8740 Duplicates: 0 Warnings: 0

Query OK, 8758 rows affected (0.11 sec)
Records: 8758 Duplicates: 0 Warnings: 0

Query OK, 8745 rows affected (0.12 sec)
Records: 8745 Duplicates: 0 Warnings: 0

And it would just stop here. I waited several minutes, and the load average of the machine went down to normal levels. The mySQLd stopped showing up on top.

I'd then CNTRL-C the process and get:

^CQuery aborted by Ctrl+C
^CAborted
mysql: 0 files and 1 streams is left open

$

I've done this a few times and the results have been consistent(ly bad). As I mentioned I also did two separate mysqldumps and both files failed to import the same way.

While the import was happening I watched "top." There didn't seem to be a memory issue since the mysqld never used more than 0.3% of the memory, although it did use 96% of the CPU (which was fine).

The mysqlds are running on the same operating system - Fedora 10 x_64. The only difference between them is the dump was done on mysql 5.0.27, and I am trying to source to a mysqld v5.0.67.

Any thoughts?

Source: https://mysql.livejournal.com/133572.html
calling remote stored procedure

Date: 12/17/08 Keywords: mysql, database, sql

I need to "synchronize" the output of a stored procedure in one database (I don't have access to any tables there, just stored procedures) and a table in another database. So I need to call a remote stored procedure once in a while and populate a local table with its output. For the remote database, I know hostname, port, username, password and db name.

Is this possible in mysql? Any help would be appreciated

Source: http://community.livejournal.com/mysql/133145.html
calling remote stored procedure

Date: 12/17/08 Keywords: mysql, database, sql

I need to "synchronize" the output of a stored procedure in one database (I don't have access to any tables there, just stored procedures) and a table in another database. So I need to call a remote stored procedure once in a while and populate a local table with its output. For the remote database, I know hostname, port, username, password and db name.

Is this possible in mysql? Any help would be appreciated

Source: http://mysql.livejournal.com/133145.html
calling remote stored procedure

Date: 12/17/08 Keywords: mysql, database, sql

I need to "synchronize" the output of a stored procedure in one database (I don't have access to any tables there, just stored procedures) and a table in another database. So I need to call a remote stored procedure once in a while and populate a local table with its output. For the remote database, I know hostname, port, username, password and db name.

Is this possible in mysql? Any help would be appreciated

Source: https://mysql.livejournal.com/133145.html
MySQL IDE options

Date: 12/11/08 Keywords: mysql, database, sql

I'm starting a new consulting gig that requires me to work on a MySQL database. Typically, I use Rapid SQL as my IDE, but I have been informed that it doesn't support 5.x. What IDEs out there support MySQL 5?

Source: http://community.livejournal.com/mysql/132964.html
MySQL IDE options

Date: 12/11/08 Keywords: mysql, database, sql

I'm starting a new consulting gig that requires me to work on a MySQL database. Typically, I use Rapid SQL as my IDE, but I have been informed that it doesn't support 5.x. What IDEs out there support MySQL 5?

Source: http://mysql.livejournal.com/132964.html
MySQL IDE options

Date: 12/11/08 Keywords: mysql, database, sql

I'm starting a new consulting gig that requires me to work on a MySQL database. Typically, I use Rapid SQL as my IDE, but I have been informed that it doesn't support 5.x. What IDEs out there support MySQL 5?

Source: https://mysql.livejournal.com/132964.html
Entity Synonym Names

Date: 12/07/08 Keywords: html, sql, google

Heyo. I'm putting together a user search function and I would like to include a Facebook-style name recognition system that understands name synonyms. A search for Will would also return Bill and William, for example. To some extent I could see a Soundex search kinda accomplishing this, but I'm wondering if anyone has any really good ideas as to how to implement this. At the moment I'm trying to find a sql dump of name synonyms which I would use for cross-references.

Anyone done this before and have some words of wisdom?

*update*

I've found this: http://answers.google.com/answers/threadview/id/778986.html

... which is definitely helpful.

I created a SQL file and posted about it here: http://danconnor.com/wordpress/?p=138

I'm still open to suggestions though!

Source: http://community.livejournal.com/mysql/132846.html
Entity Synonym Names

Date: 12/07/08 Keywords: html, sql, google

Heyo. I'm putting together a user search function and I would like to include a Facebook-style name recognition system that understands name synonyms. A search for Will would also return Bill and William, for example. To some extent I could see a Soundex search kinda accomplishing this, but I'm wondering if anyone has any really good ideas as to how to implement this. At the moment I'm trying to find a sql dump of name synonyms which I would use for cross-references.

Anyone done this before and have some words of wisdom?

*update*

I've found this: http://answers.google.com/answers/threadview/id/778986.html

... which is definitely helpful.

I created a SQL file and posted about it here: http://danconnor.com/wordpress/?p=138

I'm still open to suggestions though!

Source: http://mysql.livejournal.com/132846.html
Entity Synonym Names

Date: 12/07/08 Keywords: html, sql, google

Heyo. I'm putting together a user search function and I would like to include a Facebook-style name recognition system that understands name synonyms. A search for Will would also return Bill and William, for example. To some extent I could see a Soundex search kinda accomplishing this, but I'm wondering if anyone has any really good ideas as to how to implement this. At the moment I'm trying to find a sql dump of name synonyms which I would use for cross-references.

Anyone done this before and have some words of wisdom?

*update*

I've found this: http://answers.google.com/answers/threadview/id/778986.html

... which is definitely helpful.

I created a SQL file and posted about it here: http://danconnor.com/wordpress/?p=138

I'm still open to suggestions though!

Source: https://mysql.livejournal.com/132846.html

Date: 10/28/08 Keywords: mysql, sql

I have a table as below (Slightly truncated, but the idea is there)

mysql> SELECT date, (sat0700 + sat0800 + sat0900 + sat1000) FROM alerts WHERE date LIKE '2008%' ORDER BY date ASC;

+------------+-----------------------------------------+
| date       | (sat0700 + sat0800 + sat0900 + sat1000) |
+------------+-----------------------------------------+
| 2008-09-13 |                                      39 |
| 2008-09-20 |                                       0 |
| 2008-09-27 |                                       9 |
| 2008-10-04 |                                      16 |
| 2008-10-11 |                                       9 |
| 2008-10-18 |                                       4 |
| 2008-10-25 |                                       1 |
+------------+-----------------------------------------+

I'd like to do some kind of select that adds a new column to the output for a rolling average

So...

mysql> SELECT date, (sat0700 + sat0800 + sat0900 + sat1000) AND SOMETHING ELSE FROM alerts WHERE date LIKE '2008%' ORDER BY date ASC;

+------------+-----------------------------------------+---------+
| date       | (sat0700 + sat0800 + sat0900 + sat1000) | average |
+------------+-----------------------------------------+---------+
| 2008-09-13 |                                      39 |       39|
| 2008-09-20 |                                       0 |     19.5|
| 2008-09-27 |                                       9 |       16|
| 2008-10-04 |                                      16 |       16|
| 2008-10-11 |                                       9 |     14.6|
| 2008-10-18 |                                       4 |    12.83|
| 2008-10-25 |                                       1 |    11.14|
+------------+-----------------------------------------+---------+

How would I go about writing such a query? The closest I've got so far is a running total, but can't seem to make the example fit an average

Source: http://community.livejournal.com/mysql/132135.html

SELECT subsequent Sundays

Date: 10/15/08 Keywords: no keywords

Is there a way to select a series of Sundays, based on any given date?

To clarify... A start date is submitted so I'd first need to find the next Sunday (if the start date didn't already start on Sunday). Then I can determine the following Sundays easily enough. The trick is finding the very next Sunday from my start date.

Ideas?

Source: http://community.livejournal.com/mysql/131930.html
SELECT subsequent Sundays

Date: 10/15/08 Keywords: no keywords

Is there a way to select a series of Sundays, based on any given date?

To clarify... A start date is submitted so I'd first need to find the next Sunday (if the start date didn't already start on Sunday). Then I can determine the following Sundays easily enough. The trick is finding the very next Sunday from my start date.

Ideas?

Source: http://mysql.livejournal.com/131930.html
SELECT subsequent Sundays

Date: 10/15/08 Keywords: no keywords

Is there a way to select a series of Sundays, based on any given date?

To clarify... A start date is submitted so I'd first need to find the next Sunday (if the start date didn't already start on Sunday). Then I can determine the following Sundays easily enough. The trick is finding the very next Sunday from my start date.

Ideas?

Source: https://mysql.livejournal.com/131930.html
Select records having a datetime one second apart from each other

Date: 09/25/08 Keywords: no keywords

I have a small (250,000 records) table with data and a datetimefield. Most datetime fields are several minutes or days apart, but some are literally one second apart. How would I query the table to show only those rows?

Source: http://community.livejournal.com/mysql/131659.html
Select records having a datetime one second apart from each other

Date: 09/25/08 Keywords: no keywords

I have a small (250,000 records) table with data and a datetimefield. Most datetime fields are several minutes or days apart, but some are literally one second apart. How would I query the table to show only those rows?

Source: http://mysql.livejournal.com/131659.html
Select records having a datetime one second apart from each other

Date: 09/25/08 Keywords: no keywords

I have a small (250,000 records) table with data and a datetimefield. Most datetime fields are several minutes or days apart, but some are literally one second apart. How would I query the table to show only those rows?

Source: https://mysql.livejournal.com/131659.html
Find datetimes with time of 00:00:00

Date: 09/09/08 Keywords: no keywords

What is the best/fastest way to find records with datetimes having times of 00:00:00? ex: `2008-01-23 00:00:00`

Using LIKE '%00:00:00' takes a relatively long time (2 seconds looking across 500,000 records).

Source: http://community.livejournal.com/mysql/131436.html
Find datetimes with time of 00:00:00

Date: 09/09/08 Keywords: no keywords

What is the best/fastest way to find records with datetimes having times of 00:00:00? ex: `2008-01-23 00:00:00`

Using LIKE '%00:00:00' takes a relatively long time (2 seconds looking across 500,000 records).

Source: http://mysql.livejournal.com/131436.html
Find datetimes with time of 00:00:00

Date: 09/09/08 Keywords: no keywords

What is the best/fastest way to find records with datetimes having times of 00:00:00? ex: `2008-01-23 00:00:00`

Using LIKE '%00:00:00' takes a relatively long time (2 seconds looking across 500,000 records).

Source: https://mysql.livejournal.com/131436.html