Fwd: load data

[Ananda Kumar]

Hi All,
We have an application where we load data on a daily basis and then do some
analysis and the move this data into different tables.

Data is comming in  files. The date format in the file  is "dd-mon-
hh24:mi:ss', but as you all know, in mysql , the default date format is
"-mm-dd hh24:mi:ss". How can i specifiy this format in the load data
infile script.

I tried this

LOAD DATA LOCAL INFILE 'abc.txt' INTO TABLE abc FIELDS TERMINATED BY ','
LINES TERMINATED BY '^V\n' (doj timestamp "dd-mon- hh24:mi:ss");

but data is not getting inserted.

Please help me.

regards
anandkl

Re: View pocedures/backup procedures

[Dan Nelson]

In the last episode (Jul 10), Andrey Dmitriev said:
> How do I view and backup my procedural code?
> 
> In oracle it would be something like
> Select text from user_source where name='MY_PROCEDURE' order by line;
> 
> I did mysqldump, and didn't see any of the functions or procedures 
> created.
> 
> In Oracle they'd be backed up either via RMAN or EXP.

You need to add the --routines flag to mysqldump.

-- 
Dan Nelson
[EMAIL PROTECTED]

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

View pocedures/backup procedures

[Andrey Dmitriev]

How do I view and backup my procedural code?

In oracle it would be something like
Select text from user_source where name='MY_PROCEDURE' order by line;

I did mysqldump, and didn't see any of the functions or procedures 
created.

In Oracle they'd be backed up either via RMAN or EXP.

Thanks,
Andrey


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: How to restore 1 database from mysqldump of all databases

[Mogens Melander]

A quick script solution:

create a dump pr. table in .

#!/bin/bash

for tbl in `echo "use ;show tables;"|mysql -s -u  -p`
do
  mysqldump -u  p db $tbl > $tbl.sql
done

and the other way:

cat | mysql -u  -p 

On Tue, July 10, 2007 15:47, Rolando Edwards wrote:
> When the mysqldump ran against all databases, the USE  command
> should have appeared above each section of that databases dump. Try using
> Perl or the head or tail Unix command to hunt down the Database you are
> dumping. Read all lines until the next USE  command.
>
> Example: If you mysqldumped databases DB1, DB2 (copyright IBM), DB3, DB4,
> and DB5
> and you want ot restore just DB3, locate the string "USE DB3" (say its
> line 20)
> and "USE DB4" (say its line 25). Copy lines 20 to 25 to
> another SQL script.
> Then run that new script.
>
>
> - Original Message -
> From: "waldo tumanut" <[EMAIL PROTECTED]>
> To: mysql@lists.mysql.com
> Sent: Tuesday, July 10, 2007 9:34:28 AM (GMT-0500) America/New_York
> Subject: How to restore 1 database from mysqldump of all databases
>
>
> Question: How do I restore 1 database from amysqldump of all databases?
>
> Obviously a newbie.  I've searched the list but couldn't find the answer.
> Can someone help?
>
> Waldo Tumanut
> Database Analyst
>
>
> 
> CONFIDENTIALITY NOTICE: This electronic mail transmission (including any
> accompanying attachments) is intended solely for its authorized
> recipient(s), and may contain confidential and/or legally privileged
> information. If you are not an intended recipient, or responsible for
> delivering some or all of this transmission to an intended recipient, be
> aware that any review, copying, printing, distribution, use or disclosure
> of the contents of this message is strictly prohibited. If you have
> received this electronic mail message in error, please contact us
> immediately by electronic mail at [EMAIL PROTECTED]
> and destroy the original and all copies of this transmission (including
> any attachments).
>
> Thank you.
> 
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
> --
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.
>


-- 
Later

Mogens Melander
+45 40 85 71 38
+66 870 133 224



-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: mysqldump/restore

[John Mancuso]

#!/usr/bin/perl


#dump all files
@db=`mysql -e -ppassword "show databases" | sed "s/-//g" | sed "s/
//g"`;
foreach $db(@db){
chomp($db);
`mysqldump $db -ppassword > $db.sql`;
}

#multiple files will load simultaneously. multiple processes should run
much faster
foreach $db(@db){
`mysql -ppassword $db < $db.sql &`;
} 


How 'bout something like that? Not perfect but should be ok

John Mancuso
Linux Administrator/MySQL DBA
IT Infrastructure
American Home Mortgage
w: 631-622-6382
c: 516-652-2475

-Original Message-
From: B. Keith Murphy [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, July 10, 2007 4:36 PM
To: mysql
Subject: mysqldump/restore

hey all. Let me throw out a quick question. We are upgrading from some
version of 4.1 to the latest version of 5.0. MySQL recommends that you
dump and re-import data with this type of move. Not only do they
recommend the dump/import - it would be nice to optimize all our tables
at the same time!! 

I have done a test dump of a couple of gigs of data with just a straight
mysqldump. That worked, but the import is happening slower than
Christmas. I know that one technique for speeding up the imports is to
where you configure the dump so that it generates file(s) with the
schema for the table(s) and file(s) with actual data. Then you can use
the load data infile command to import the data after generating the new
tables (maybe by using 'mysql  database.sql' to get a complete schema for all the tables
in the database and then just 'mysql < database.sql' to set up all the
tables at the same time. You can't do that with the import of the actual
data. You have to individually load the data into each table. something
like 'load data infile /root/table1.txt'. 

This is fine for one..but what happens when you have 100 tables? I can't
take the time to sit there and hit the up-arrow to retrieve the command
and then type in the new file name. Is there some way to automate this
data import? 

Thanks, 

Keith 

--
B. Keith Murphy
Database Administrator
iContact
2635 Meridian Parkway, 2nd Floor
Durham, North Carolina 27713
(o) 919-433-0786
(c) 850-637-3877 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

mysqldump/restore

[B. Keith Murphy]

hey all. Let me throw out a quick question. We are upgrading from some version 
of 4.1 to the latest version of 5.0. MySQL recommends that you dump and 
re-import data with this type of move. Not only do they recommend the 
dump/import - it would be nice to optimize all our tables at the same time!! 

I have done a test dump of a couple of gigs of data with just a straight 
mysqldump. That worked, but the import is happening slower than Christmas. I 
know that one technique for speeding up the imports is to where you configure 
the dump so that it generates file(s) with the schema for the table(s) and 
file(s) with actual data. Then you can use the load data infile command to 
import the data after generating the new tables (maybe by using 'mysql 
 
database.sql' to get a complete schema for all the tables in the database and 
then just 'mysql < database.sql' to set up all the tables at the same time. You 
can't do that with the import of the actual data. You have to individually load 
the data into each table. something like 'load data infile /root/table1.txt'. 

This is fine for one..but what happens when you have 100 tables? I can't take 
the time to sit there and hit the up-arrow to retrieve the command and then 
type in the new file name. Is there some way to automate this data import? 

Thanks, 

Keith 

-- 
B. Keith Murphy 
Database Administrator 
iContact 
2635 Meridian Parkway, 2nd Floor 
Durham, North Carolina 27713 
(o) 919-433-0786 
(c) 850-637-3877 

RE: 2PC on master-slave with replication

[Rick James]

No.

Transactions involve only one machine.

Furthermore, writing to the master and then reading from a slave runs the
risk of replication delays.  Granted, delays are usually sub-second, but if
something big goes thru replication, it can get behind.  Your application
should understand the delay and either live with or code around it -- such
as going to the master for things (eg your transaction) that must be
consistent.  Leave the bulk of reads for slaves. 

> -Original Message-
> From: Darpan Dinker [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, July 10, 2007 12:37 AM
> To: mysql@lists.mysql.com; [EMAIL PROTECTED]
> Subject: 2PC on master-slave with replication
> 
> Hi,
> Is it possible to have a transaction with an update statement 
> pointed to 
> the master and a select statement pointed to the slave. If 
> the isolation 
> level of this transaction is REPEATABLE_READ or SERIALIZABLE, will a 
> commit involve 2PC?
> Thanks,
> Darpan
> 
> -- 
> MySQL Replication Mailing List
> For list archives: http://lists.mysql.com/replication
> To unsubscribe:
> http://lists.mysql.com/[EMAIL PROTECTED]
> 
> 


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: User variables in update statement

[Baron Schwartz]

Hi Scott,

Scott Haneda wrote:

Is it possible to use user variables in an update statement, I can find only
scant docs on it, but they do not pertain to what I am trying to do.

Given this select:

SELECT user_id, order_status, quantity_chosen, month_price, each_price,
sales_tax, sales_tax_rate,
@NEW_each_price:=(each_price + .06) as NEW_each_price,
@NEW_month_price:=(quantity_chosen * @NEW_each_price) as NEW_month_price,
@postage_rate:=(0.30) as postage_rate,
@cost_of_postage:=(quantity_chosen * @postage_rate) as postage,
@taxable_price:=(@NEW_month_price - @cost_of_postage) as taxable,
(@taxable_price * sales_tax_rate) as NEW_sales_tax

As you can see, I am using variables to make some calcs, sure, I can do it
long hand, but it gets long and confusing.

Given the above, I end up with some vars like NEW_month_price,
NEW_each_price etc, I would like to update ... SET price = NEW_each_price,
but of course, that does not work.

Suggestions?


It's possible, but I'm not sure it was ever intended.  Still, I have found some very 
useful applications for it.  The trick is to put the assignment inside a function, 
which will a) make sure the assignment happens b) present the assignment as an 
expression that can go on the right-hand-side of a SET clause.


I explained in detail how it works here:
http://www.xaprb.com/blog/2006/12/15/advanced-mysql-user-variable-techniques/

But only read that if you want the background, which is a little tangential for your 
purposes.  Read this instead:


update tbl set
   -- Set each_price to its present value, while setting @NEW_each_price
   each_price = greatest(least(0, @NEW_each_price := each_price + .06)),
   price = @NEW_each_price,
   

You can see some complex examples of that technique in this article:
http://www.oreillynet.com/pub/a/mysql/2007/03/01/optimize-mysql-rank-data.html?page=3

Following the examples in that article, you can do a lot more flexible things than I 
just demonstrated.


--
Baron Schwartz
http://www.xaprb.com/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: error

[David T. Ashley]

On 7/10/07, Octavian Rasnita <[EMAIL PROTECTED]> wrote:


I have exported a database using mysqldump from MySQL 5.0.27, and I have
tried importing it in a MySQL 5.0.41, but it gives the following error:

ERROR 1071 (42000) at line 483: Specified key was too long; max key length
is 1000 bytes



Hint:  Please post line 483 as well as the lines that give the offending
table design.

User variables in update statement

[Scott Haneda]

Is it possible to use user variables in an update statement, I can find only
scant docs on it, but they do not pertain to what I am trying to do.

Given this select:

SELECT user_id, order_status, quantity_chosen, month_price, each_price,
sales_tax, sales_tax_rate,
@NEW_each_price:=(each_price + .06) as NEW_each_price,
@NEW_month_price:=(quantity_chosen * @NEW_each_price) as NEW_month_price,
@postage_rate:=(0.30) as postage_rate,
@cost_of_postage:=(quantity_chosen * @postage_rate) as postage,
@taxable_price:=(@NEW_month_price - @cost_of_postage) as taxable,
(@taxable_price * sales_tax_rate) as NEW_sales_tax

As you can see, I am using variables to make some calcs, sure, I can do it
long hand, but it gets long and confusing.

Given the above, I end up with some vars like NEW_month_price,
NEW_each_price etc, I would like to update ... SET price = NEW_each_price,
but of course, that does not work.

Suggestions?
-- 
-
Scott HanedaTel: 415.898.2602
 Novato, CA U.S.A.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: How to restore 1 database from mysqldump of all databases

[Dwalu Z. Khasu]

On Tue, 10 Jul 2007 [EMAIL PROTECTED] wrote:

=>Thanks for your response, Rolando.  I know all the DML to create the
=>database is in the mysqldump.  I was looking for a command or command
=>options, if there are any, to extract the DML for the database, like what
=>you can do with mysqlbinlog.
=>
=>
Should be mysqldump --no-data  (or -d) -- Try mysqldump --help.
-- 
- Dwalu
.peace
--
I am an important person in this world -
Now is the most important time in my life -
My mistakes are my best teachers -
So I will be fearless.
- Student Creed

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Dynamic ORing

[Dave G]

Prepared Statements with the IN function worked beautifully.  Performance
is slightly degraded, but I get the functionality I want and can live with
the slight performance hit.

Thanks again.

Dave

> Hi Dave,
>
> Dave G wrote:
>> Hello all,
>>
>> I'm trying to do some processing on the front end to optimize my query
>> on
>> the backend.  I would like to generate a list of id's for this query
>> like
>> so:
>>
>> SELECT REPLACE('3,4,5,6,7,8,9',',',' OR element_id=') INTO @tmp;
>>
>> Then use it like:
>>
>> mysql> select @tmp;
>> +---+
>> | @tmp
>>  |
>> +---+
>> | 3 OR element_id=4 OR element_id=5 OR element_id=6 OR element_id=7 OR
>> element_id=8 OR element_id=9 |
>> +---+
>> 1 row in set (0.00 sec)
>>
>> mysql> select * from display__Element where [EMAIL PROTECTED];
>
> MySQL sees that element_id is a number and coerces @tmp into a number.
> Check the
> output of 'show warnings' immediately after this statement.  Another way
> to see what's
> happening is to run 'select 0 + @tmp'.  MySQL is truncating after the
> first non-digit
> characters.
>
> What you want to do is use a prepared statement; prepare the statement as
> concat("select...", @tmp) and then execute the result.
>
>> ++--++
>> | element_id | display_name | data_units |
>> ++--++
>> |  3 | Sync ||
>> ++--++
>> 1 row in set (0.00 sec)
>>
>> mysql> select * from display__Element where element_id=3 OR element_id=4
>> OR element_id=5 OR element_id=6 OR element_id=7 OR element_id=8 OR
>> element_id=9;
>
> A better approach is to use an IN() list, which MySQL might even be able
> to optimize
> better:
>
> WHERE element_id IN(3,4,5,6,7,8,9);
>
> DANGER: do NOT use an IN() subquery, of the form
>
> WHERE element_id IN(select element_id from some_other_table)
>
> MySQL optimizes this kind of query very poorly.
>
> Baron
>



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: How to restore 1 database from mysqldump of all databases

[waldo_tumanut]

Thanks for your response, Rolando.  I know all the DML to create the
database is in the mysqldump.  I was looking for a command or command
options, if there are any, to extract the DML for the database, like what
you can do with mysqlbinlog.

- Message from Rolando Edwards <[EMAIL PROTECTED]> on Tue, 10 Jul 2007
09:47:15 -0400 (EDT) -
   
   To: waldo tumanut <[EMAIL PROTECTED]>   
   
   cc: mysql@lists.mysql.com   
   
  Subject: Re: How to restore 1 database from mysqldump of all databases   
   

When the mysqldump ran against all databases, the USE  command
should have appeared above each section of that databases dump. Try using
Perl or the head or tail Unix command to hunt down the Database you are
dumping. Read all lines until the next USE  command.

Example: If you mysqldumped databases DB1, DB2 (copyright IBM), DB3, DB4,
and DB5
and you want ot restore just DB3, locate the string "USE DB3" (say its line
20)
and "USE DB4" (say its line 25). Copy lines 20 to 25 to another
SQL script.
Then run that new script.




CONFIDENTIALITY NOTICE: This electronic mail transmission (including any 
accompanying attachments) is intended solely for its authorized recipient(s), 
and may contain confidential and/or legally privileged information. If you are 
not an intended recipient, or responsible for delivering some or all of this 
transmission to an intended recipient, be aware that any review, copying, 
printing, distribution, use or disclosure of the contents of this message is 
strictly prohibited. If you have received this electronic mail message in 
error, please contact us immediately by electronic mail at [EMAIL PROTECTED] 
and destroy the original and all copies of this transmission (including any 
attachments).

Thank you.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Dynamic ORing

[Dave G]

Thank you Baron, I'll give that a shot, and thanks for the IN() list tip
... much appreciated.

Dave

> Hi Dave,
>
> Dave G wrote:
>> Hello all,
>>
>> I'm trying to do some processing on the front end to optimize my query
>> on
>> the backend.  I would like to generate a list of id's for this query
>> like
>> so:
>>
>> SELECT REPLACE('3,4,5,6,7,8,9',',',' OR element_id=') INTO @tmp;
>>
>> Then use it like:
>>
>> mysql> select @tmp;
>> +---+
>> | @tmp
>>  |
>> +---+
>> | 3 OR element_id=4 OR element_id=5 OR element_id=6 OR element_id=7 OR
>> element_id=8 OR element_id=9 |
>> +---+
>> 1 row in set (0.00 sec)
>>
>> mysql> select * from display__Element where [EMAIL PROTECTED];
>
> MySQL sees that element_id is a number and coerces @tmp into a number.
> Check the
> output of 'show warnings' immediately after this statement.  Another way
> to see what's
> happening is to run 'select 0 + @tmp'.  MySQL is truncating after the
> first non-digit
> characters.
>
> What you want to do is use a prepared statement; prepare the statement as
> concat("select...", @tmp) and then execute the result.
>
>> ++--++
>> | element_id | display_name | data_units |
>> ++--++
>> |  3 | Sync ||
>> ++--++
>> 1 row in set (0.00 sec)
>>
>> mysql> select * from display__Element where element_id=3 OR element_id=4
>> OR element_id=5 OR element_id=6 OR element_id=7 OR element_id=8 OR
>> element_id=9;
>
> A better approach is to use an IN() list, which MySQL might even be able
> to optimize
> better:
>
> WHERE element_id IN(3,4,5,6,7,8,9);
>
> DANGER: do NOT use an IN() subquery, of the form
>
> WHERE element_id IN(select element_id from some_other_table)
>
> MySQL optimizes this kind of query very poorly.
>
> Baron
>



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Dynamic ORing

[Baron Schwartz]

Hi Dave,

Dave G wrote:

Hello all,

I'm trying to do some processing on the front end to optimize my query on
the backend.  I would like to generate a list of id's for this query like
so:

SELECT REPLACE('3,4,5,6,7,8,9',',',' OR element_id=') INTO @tmp;

Then use it like:

mysql> select @tmp;
+---+
| @tmp
 |

+---+
| 3 OR element_id=4 OR element_id=5 OR element_id=6 OR element_id=7 OR
element_id=8 OR element_id=9 |
+---+
1 row in set (0.00 sec)

mysql> select * from display__Element where [EMAIL PROTECTED];


MySQL sees that element_id is a number and coerces @tmp into a number.  Check the 
output of 'show warnings' immediately after this statement.  Another way to see what's 
happening is to run 'select 0 + @tmp'.  MySQL is truncating after the first non-digit 
characters.


What you want to do is use a prepared statement; prepare the statement as 
concat("select...", @tmp) and then execute the result.



++--++
| element_id | display_name | data_units |
++--++
|  3 | Sync ||
++--++
1 row in set (0.00 sec)

mysql> select * from display__Element where element_id=3 OR element_id=4
OR element_id=5 OR element_id=6 OR element_id=7 OR element_id=8 OR
element_id=9;


A better approach is to use an IN() list, which MySQL might even be able to optimize 
better:


   WHERE element_id IN(3,4,5,6,7,8,9);

DANGER: do NOT use an IN() subquery, of the form

   WHERE element_id IN(select element_id from some_other_table)

MySQL optimizes this kind of query very poorly.

Baron

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Dynamic ORing

[Dave G]

Hello all,

I'm trying to do some processing on the front end to optimize my query on
the backend.  I would like to generate a list of id's for this query like
so:

SELECT REPLACE('3,4,5,6,7,8,9',',',' OR element_id=') INTO @tmp;

Then use it like:

mysql> select @tmp;
+---+
| @tmp
 |
+---+
| 3 OR element_id=4 OR element_id=5 OR element_id=6 OR element_id=7 OR
element_id=8 OR element_id=9 |
+---+
1 row in set (0.00 sec)

mysql> select * from display__Element where [EMAIL PROTECTED];
++--++
| element_id | display_name | data_units |
++--++
|  3 | Sync ||
++--++
1 row in set (0.00 sec)

mysql> select * from display__Element where element_id=3 OR element_id=4
OR element_id=5 OR element_id=6 OR element_id=7 OR element_id=8 OR
element_id=9;
++---++
| element_id | display_name  | data_units |
++---++
|  3 | Sync  ||
|  4 | Graph Samples | V  |
|  5 | First E   | V  |
|  7 | Graph Sample  | V  |
|  8 | Test Graph| V  |
++---++
5 rows in set (0.00 sec)

mysql>

The problem is that when I try to use a variable that is a string with
OR's contained, it only uses the first one.  Anybody know what is going on
here?

David Godsey


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

monitoring mysql

[John Mancuso]

I am running a fairly large Innodb database on mysql 5.x and would like
to know what would be the best variables to monitor (for Nagios)- for
instance: threads
connected,Innodb_data_pending_reads,Innodb_data_pending_writes, slow
queries etc

 
Aborted_clients
Aborted_connects
Binlog_cache_disk_use
Binlog_cache_use
Bytes_received
Bytes_sent
Com_admin_commands
Com_alter_db
Com_alter_table
Com_analyze
Com_backup_table
Com_begin
Com_change_db
Com_change_master
Com_check
Com_checksum
Com_commit
Com_create_db
Com_create_function
Com_create_index
Com_create_table
Com_dealloc_sql
Com_delete
Com_delete_multi
Com_do
Com_drop_db
Com_drop_function
Com_drop_index
Com_drop_table
Com_drop_user
Com_execute_sql
Com_flush
Com_grant
Com_ha_close
Com_ha_open
Com_ha_read
Com_help
Com_insert
Com_insert_select
Com_kill
Com_load
Com_load_master_data
Com_load_master_table
Com_lock_tables
Com_optimize
Com_preload_keys
Com_prepare_sql
Com_purge
Com_purge_before_date
Com_rename_table
Com_repair
Com_replace
Com_replace_select
Com_reset
Com_restore_table
Com_revoke
Com_revoke_all
Com_rollback
Com_savepoint
Com_select
Com_set_option
Com_show_binlog_events
Com_show_binlogs
Com_show_charsets
Com_show_collations
Com_show_column_types
Com_show_create_db
Com_show_create_table
Com_show_databases
Com_show_errors
Com_show_fields
Com_show_grants
Com_show_innodb_status
Com_show_keys
Com_show_logs
Com_show_master_status
Com_show_ndb_status
Com_show_new_master
Com_show_open_tables
Com_show_privileges
Com_show_processlist
Com_show_slave_hosts
Com_show_slave_status
Com_show_status
Com_show_storage_engines
Com_show_tables
Com_show_triggers
Com_show_variables
Com_show_warnings
Com_slave_start
Com_slave_stop
Com_stmt_close
Com_stmt_execute
Com_stmt_fetch
Com_stmt_prepare
Com_stmt_reset
Com_stmt_send_long_data
Com_truncate
Com_unlock_tables
Com_update
Com_update_multi
Com_xa_commit
Com_xa_end
Com_xa_prepare
Com_xa_recover
Com_xa_rollback
Com_xa_start
Compression
Connections
Created_tmp_disk_tables
Created_tmp_files
Created_tmp_tables
Delayed_errors
Delayed_insert_threads
Delayed_writes
Flush_commands
Handler_commit
Handler_delete
Handler_discover
Handler_prepare
Handler_read_first
Handler_read_key
Handler_read_next
Handler_read_prev
Handler_read_rnd
Handler_read_rnd_next
Handler_rollback
Handler_savepoint
Handler_savepoint_rollback
Handler_update
Handler_write
Innodb_buffer_pool_pages_data
Innodb_buffer_pool_pages_dirty
Innodb_buffer_pool_pages_flushed
Innodb_buffer_pool_pages_free
Innodb_buffer_pool_pages_latched
Innodb_buffer_pool_pages_misc
Innodb_buffer_pool_pages_total
Innodb_buffer_pool_read_ahead_rnd
Innodb_buffer_pool_read_ahead_seq
Innodb_buffer_pool_read_requests
Innodb_buffer_pool_reads
Innodb_buffer_pool_wait_free
Innodb_buffer_pool_write_requests
Innodb_data_fsyncs
Innodb_data_pending_fsyncs
Innodb_data_pending_reads
Innodb_data_pending_writes
Innodb_data_read
Innodb_data_reads
Innodb_data_writes
Innodb_data_written
Innodb_dblwr_pages_written
Innodb_dblwr_writes
Innodb_log_waits
Innodb_log_write_requests
Innodb_log_writes
Innodb_os_log_fsyncs
Innodb_os_log_pending_fsyncs
Innodb_os_log_pending_writes
Innodb_os_log_written
Innodb_page_size
Innodb_pages_created
Innodb_pages_read
Innodb_pages_written
Innodb_row_lock_current_waits
Innodb_row_lock_time
Innodb_row_lock_time_avg
Innodb_row_lock_time_max
Innodb_row_lock_waits
Innodb_rows_deleted
Innodb_rows_inserted
Innodb_rows_read
Innodb_rows_updated
Key_blocks_not_flushed
Key_blocks_unused
Key_blocks_used
Key_read_requests
Key_reads
Key_write_requests
Key_writes
Last_query_cost
Max_used_connections
Not_flushed_delayed_rows
Open_files
Open_streams
Open_tables
Opened_tables
Qcache_free_blocks
Qcache_free_memory
Qcache_hits
Qcache_inserts
Qcache_lowmem_prunes
Qcache_not_cached
Qcache_queries_in_cache
Qcache_total_blocks
Questions
Rpl_status
Select_full_join
Select_full_range_join
Select_range
Select_range_check
Select_scan
Slave_open_temp_tables
Slave_retried_transactions
Slave_running
Slow_launch_threads
Slow_queries
Sort_merge_passes
Sort_range
Sort_rows
Sort_scan
Ssl_accept_renegotiates
Ssl_accepts
Ssl_callback_cache_hits
Ssl_cipher
Ssl_cipher_list
Ssl_client_connects
Ssl_connect_renegotiates
Ssl_ctx_verify_depth
Ssl_ctx_verify_mode
Ssl_default_timeout
Ssl_finished_accepts
Ssl_finished_connects
Ssl_session_cache_hits
Ssl_session_cache_misses
Ssl_session_cache_mode
Ssl_session_cache_overflows
Ssl_session_cache_size
Ssl_session_cache_timeouts
Ssl_sessions_reused
Ssl_used_session_cache_entries
Ssl_verify_depth
Ssl_verify_mode
Ssl_version
Table_locks_immediate
Table_locks_waited
Tc_log_max_pages_used
Tc_log_page_size
Tc_log_page_waits
Threads_cached
Threads_connected
Threads_created
Threads_running
Uptime

 
John Mancuso
Linux Administrator/MySQL DBA
IT Infrastructure
American Home Mortgage
w: 631-622-6382
c: 516-652-2475
 

Re: How to restore 1 database from mysqldump of all databases

[Rolando Edwards]

When the mysqldump ran against all databases, the USE  command should 
have appeared above each section of that databases dump. Try using Perl or the 
head or tail Unix command to hunt down the Database you are dumping. Read all 
lines until the next USE  command.

Example: If you mysqldumped databases DB1, DB2 (copyright IBM), DB3, DB4, and 
DB5
and you want ot restore just DB3, locate the string "USE DB3" (say its line 
20)
and "USE DB4" (say its line 25). Copy lines 20 to 25 to another SQL 
script.
Then run that new script.


- Original Message -
From: "waldo tumanut" <[EMAIL PROTECTED]>
To: mysql@lists.mysql.com
Sent: Tuesday, July 10, 2007 9:34:28 AM (GMT-0500) America/New_York
Subject: How to restore 1 database from mysqldump of all databases


Question: How do I restore 1 database from amysqldump of all databases?

Obviously a newbie.  I've searched the list but couldn't find the answer.
Can someone help?

Waldo Tumanut
Database Analyst



CONFIDENTIALITY NOTICE: This electronic mail transmission (including any 
accompanying attachments) is intended solely for its authorized recipient(s), 
and may contain confidential and/or legally privileged information. If you are 
not an intended recipient, or responsible for delivering some or all of this 
transmission to an intended recipient, be aware that any review, copying, 
printing, distribution, use or disclosure of the contents of this message is 
strictly prohibited. If you have received this electronic mail message in 
error, please contact us immediately by electronic mail at [EMAIL PROTECTED] 
and destroy the original and all copies of this transmission (including any 
attachments).

Thank you.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

How to restore 1 database from mysqldump of all databases

[waldo_tumanut]

Question: How do I restore 1 database from amysqldump of all databases?

Obviously a newbie.  I've searched the list but couldn't find the answer.
Can someone help?

Waldo Tumanut
Database Analyst



CONFIDENTIALITY NOTICE: This electronic mail transmission (including any 
accompanying attachments) is intended solely for its authorized recipient(s), 
and may contain confidential and/or legally privileged information. If you are 
not an intended recipient, or responsible for delivering some or all of this 
transmission to an intended recipient, be aware that any review, copying, 
printing, distribution, use or disclosure of the contents of this message is 
strictly prohibited. If you have received this electronic mail message in 
error, please contact us immediately by electronic mail at [EMAIL PROTECTED] 
and destroy the original and all copies of this transmission (including any 
attachments).

Thank you.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: why can I not edit my database using mySQL administrator ?

[Rolando Edwards]

Are you using MySQL Administrator for Windows ?

Are you using it against MySQL running in Linux ?
If so, you may prefer MySQL Administrator for Linux.

Just a couple more thoughts ...

- Original Message -
From: "cfaust-dougot" <[EMAIL PROTECTED]>
To: "Weiqi Wang" <[EMAIL PROTECTED]>, mysql@lists.mysql.com
Sent: Monday, July 9, 2007 4:57:31 PM (GMT-0500) America/New_York
Subject: RE: why can I not edit my database using mySQL administrator ?

Are you sure the MySQL user you have defined in your GUI tool has the proper 
table permissions?
 
Just a thought
 
-Chris



From: Weiqi Wang [mailto:[EMAIL PROTECTED]
Sent: Mon 7/9/2007 4:46 PM
To: mysql@lists.mysql.com
Subject: why can I not edit my database using mySQL administrator ?




Dear everyone:

I'm trying to simplify the operating of mySQL by using a GUI tool, mySQL 
administrator. But it seems that I was only allowed to view the databases which 
I have made up, but not allowed to edit them. Is it because I have to buy a 
lisence or something which I didn't buy? Thanks for any answer!

Best Regards,


yours sincerely,

Weiqi

==
Weiqi Wang
Hertford College
Dep. of Engineering Science
Oxford 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

error

[Octavian Rasnita]

Hi,

I have exported a database using mysqldump from MySQL 5.0.27, and I have 
tried importing it in a MySQL 5.0.41, but it gives the following error:


ERROR 1071 (42000) at line 483: Specified key was too long; max key length 
is 1000 bytes


Isn't mysqldump exporting the data correctly?

Can I solve this problem?

Thank you.

Octavian


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

2PC on master-slave with replication

[Darpan Dinker]

Hi,
Is it possible to have a transaction with an update statement pointed to 
the master and a select statement pointed to the slave. If the isolation 
level of this transaction is REPEATABLE_READ or SERIALIZABLE, will a 
commit involve 2PC?

Thanks,
Darpan

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]