RE: insert data

[Gerald Preston]

David,

I read them and installed 4.1.10 and I am back to square one:

I used the following code:

#!/perl
use warnings;
use strict;
use DBI;
#use DBD-mysql;

  my $group_name = beer;
  my $me = E;
  my $daily  = 03032005;
  my $item   = Bacardi;
  my $unit   = 2;
  my $qty= 3;
  my $amount = 6;
  my $tax= 0.41;
  my $total  = 6.41;

  my $dbh=DBI-connect( 'dbi:mysql:club', 'gjwpp88', 'x' ) or die
\n$DBI::errstr\n;

  my $sql = insert into wolfies( group_name, me, daily, item, unit, qty,
amount, tax, total )
  values( ?,  ?,  ?, ?,?,?,   ?,
?,   ? ) ;
  my $sth = $dbh-prepare( $sql ) or die $dbh-errstr if $dbh-err;

  $sth-execute( $group_name, $me, $daily, $item, $unit, $qty, $amount,
$tax, $total ) or warn Cannot execute FIRST Statement!!\n$DBI::errstr;

I get DBI connect'club','gjwpp88',.. failed; Client does not support
authentication protocol requested by server

I have done the following with no errors!

GRANT ALL PRIVILEGES ON club.* TO 'gjwpp88'@'local_host';

SET PASSWORD FOR 'gjwpp88'@'local_host' = PASSWORD('x');

UPDATE mysql.user SET Password = OLD_PASSWORD('x')
WHERE Host = 'local_host' AND User = 'gjwpp88';

SET PASSWORD FOR 'gjwpp88'@'local_host' = OLD_PASSWORD('x');

SELECT 'local_host', 'gjwpp88', Password FROM mysql.user
WHERE LENGTH('x')  16;

FLUSH PRIVILEGES;

I am still getting the same error I listed above.

Jerry

-Original Message-
From: Logan, David (SST - Adelaide) [mailto:[EMAIL PROTECTED] 
Sent: Monday, February 28, 2005 6:10 PM
To: Gerald Preston; mysql@lists.mysql.com
Subject: RE: insert data

Hi Gerald,

There are some good tutorials on the web for DBI access via perl to
mysql.

http://www.wbluhm.com/MySQLTut.html
http://perl.about.com/od/installandusemysql/l/aa090803b.htm
http://dev.mysql.com/doc/mysql/en/perl.html

and also

http://search.cpan.org/~timb/DBI-1.47/DBI.pm

You should be able to find several examples of exactly what you are
trying to achieve in one of these. The first one has an almost identical
query to that you are trying to achieve. If you can't select from the
table, then you are unlikely to be able to insert. I would follow the
tutorials, even if they are selects, and make sure they work and then
all you have to do is to change the SELECT to an INSERT statement and
away you go.

These have a very thorough examination of the setting up of the dsn etc.

I would also suggest

http://dev.mysql.com/doc/mysql/en/privilege-system.html

This gives a good explanation of how the GRANT/REVOKE/privileges system
works with MySQL. It is slightly different than Oracle and would be well
worth a read.

Regards

David Logan
Database Administrator
HP Managed Services
148 Frome Street,
Adelaide 5000
Australia

+61 8 8408 4273 - Work
+61 417 268 665 - Mobile
+61 8 8408 4259 - Fax


-Original Message-
From: Gerald Preston [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, 1 March 2005 10:10 AM
To: 'William R. Mussatto'; mysql@lists.mysql.com
Subject: RE: insert data

William,

I tried  GRANT ALL ON *.*  and got error  1064 4200: You have an
error
in your SQL syntax  ??

Jerry

-Original Message-
From: William R. Mussatto [mailto:[EMAIL PROTECTED] 
Sent: Monday, February 28, 2005 3:25 PM
To: mysql@lists.mysql.com
Subject: RE: insert data

Gerald Preston said:
 Michael,

 This is the actual code except for the :

   my $dbh = DBI-connect( 'DBI:mysql:database=club', '', '', {
 PrintError = 0 } ) or die $DBI::errstr;
   my $sql = insert into wolfies( group_name, me, daily, item, unit,
 qty,
 amount, tax, total )
   values( ?,  ?,  ?, ?,?,
?,
  ?,
 ?,   ? ) ;
   my $sth = $dbh-prepare( $sql ) or die $dbh-errstr if $dbh-err;

   $sth-execute( $group_name, $me, $daily, $item, $unit, $qty,
$amount,
 $tax, $total ) or warn Cannot execute FIRST
Statement!!\n$DBI::errstr;

 Question?  When I created the database club, is there anything I
needed
 to do concerning permissions or anything?

 I am lost here.  I have been writing code on a SUN Oracle systems for
 over five years.

 Regards,

 Jerry
Did you 'grant' user  access to all the tables in database club?

 -Original Message-
 From: Michael Stassen [mailto:[EMAIL PROTECTED]
 Sent: Monday, February 28, 2005 9:29 AM
 To: Gerald Preston
 Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com
 Subject: Re: insert data

  From perldoc DBD::mysql

use DBI;

$dsn = DBI:mysql:database=$database;host=$hostname;port=$port;

$dbh = DBI-connect($dsn, $user, $password);

 So it's not a syntax problem.  Even if it were, we should detect the
 error  long before calling prepare or execute.

 Perl is quite clearly telling you what is wrong.  Originally, you got

Can't call method prepare on an undefined value.

 for the line

my $sth = $dbh-prepare( $sql );

 which means that $dbh is undefined at the time of the call to prepare.

Re: Strange internal loop causing multiple updates on one record!?

[Jigal van Hemert]

I simplified the query and narrowed the problem down to the use of
SUBSTRING_INDEX:

UPDATE `param_str`
SET `value` =
   SUBSTRING_INDEX(
`value`,
';',
1
   ) + 1
 WHERE
 `id`=0  AND
 `name`='prf_hits'

Table structure:

CREATE TABLE `param_str` (
  `id` int(11) NOT NULL default '0',
  `name` varchar(32) NOT NULL default '',
  `value` varchar(255) default NULL,
  `match` varchar(255) default NULL,
  `match2` varchar(255) default NULL,
  `weight` tinyint(4) default NULL,
  KEY `id-name-value-match-match2`
(`id`,`name`,`value`(20),`match`(20),`match2`(20))
) TYPE=InnoDB;

Starting with value = 1
It often returns and affected rows count of the next bigger power of ten
minus the current value( i.e. 5 - affected rows: 95; 102 - 898; etc.)
Sometimes running the query repeatedly without another action in between
only 1 affected row is reported and the value is indeed only updated once.

Filed as http://bugs.mysql.com/8942

Regards, Jigal.

- Original Message - 
From: Jigal van Hemert [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Thursday, March 03, 2005 11:18 AM
Subject: Strange internal loop causing multiple updates on one record!?


 Tested on MySQL version 4.0.23, 4.1.8 and 4.1.10
 OS: RedHat Fedora Core 2 / RedHat 8
 Table type: InnoDB or MyISAM

 UPDATE `param_str`
 SET `value` =
  CONCAT_WS(
   ';',
   (
SUBSTRING_INDEX(
 COALESCE(`value`,''),
 ';',
 1
) + 1
   ),
   CONCAT_WS(
',',
SUBSTRING_INDEX(
 REPLACE(
  SUBSTRING(
   COALESCE(`value`,'') FROM
   LOCATE(
';',
COALESCE(`value`,'')
   ) + 1
  ),
  CONCAT(
   ',' ,
   '113',
   ','
  ),
  ','
 ),
 ',' ,
 -(9 -1)
),
'113'
   )
  ) WHERE
  `id`=6  AND
  `name`='prf_hits'



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

Re: FullText StopWordFile

[Gleb Paharenko]

Hello.



 Is the build in one for english only ?



On my Linux system with utf8 locale it works perfectly with

russian words. As the format of the file you may use such where

every word begins with a new line.





Gabriel PREDA [EMAIL PROTECTED] wrote:

 How internationalized is the ft_stopword_file ?

 

 How can I create a custom ft_stopword_file ?

 

 Is the build in one for english only ?

 

 Gabriel PREDA

 www.amr.ro

 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   ___/   www.mysql.com




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

Re: ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)

[Gleb Paharenko]

Hello.



 mysqladmin:connect to server at 'localhost' failed

 error: 'Can't connect to local MySQL server through socket

 '/tmp/mysql.sock' (2)'

...

 shellsafe_mysqld --user=root --pid-file=/var/db/mysql/mysql.pid

 --skip-grand-tables 

 [1] 28712

 shell Starting mysql deamon with databses from /var/db/mysql

 050303 21:04:02 mysql ended



Looks like mysqld didn't started properly. What is in the error log?





 shell mysql -u root -p

 mysql UPDATE mysql.user SET Password=PASSWORD('newpwd')  WHERE User='root';

 mysql flush privileges;

 and I checked this password another consol  I saw MySQL wasn't

 accepted  new passwd else, it didnt old password or without password.





We'll check this after solving the problem with the daemon's start.













Deniss Hennesy [EMAIL PROTECTED] wrote:

 Hi, 

 

 I have had to change mysql root passwd  

 My procedure is just below

 

 

 shell mysql -u root -p

 mysql UPDATE mysql.user SET Password=PASSWORD('newpwd')  WHERE User='root';

 mysql flush privileges;

 

 

 and I checked this password another consol  I saw MySQL wasn't

 accepted  new passwd else, it didnt old password or without password.

 

 

 

 

 

 Now neither new passwd nor old passwd also empty passwd is not acceped

 

 

 I change my mind and I tried that procedure

 

 shell kill `cat /var/db/host_name.pid`

 

 shell mysqld_safe --skip-grant-tables 

 

 shell mysqladmin -u root flush-privileges password newpwd

 

 

 mysqladmin:connect to server at 'localhost' failed

 error: 'Can't connect to local MySQL server through socket

 '/tmp/mysql.sock' (2)'

 

 Check that mysql is running and that the socket:'/tmp/mysql.sock' exits!

 

 

 3rd way is

 

 shell/usr/local/etc/rc.d/mysql-server.sh stop

 

 then

 

 shellsafe_mysqld --user=root --pid-file=/var/db/mysql/mysql.pid

 --skip-grand-tables 

 [1] 28712

 shell Starting mysql deamon with databses from /var/db/mysql

 050303 21:04:02 mysql ended

 

 

 So I suppose that it is not initialize MySQL deamon  isnt it???

 

 

 How I can recover mysql password?!??

 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   ___/   www.mysql.com




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

Re: Please help - MySQL4.1.10 don't run properly after data restored from 5.0.1

[Gleb Paharenko]

 But when I execute a complex query, it hangs there forever. I don't have

 a chance to see the result because I need to go home to have dinner. The

 same query run in a FreeBSD system in Mysql5.01 just takes 2 seconds.



Please, execute this statements while the huge query is running,

and send us the output:



  show processlist;

  show variables;

  show status;

 

Send us an output of SHOW CREATE TABLE your_table, for each table you use

in your queries. Send us the actual query. Please, run this statements

using mysql command line client.



 The questions I would like to ask is how to make sure mysqld is start up

 properly? Because it seems that the start script that come with the



Usually MySQL writes errors to error log. If you find something there, please,

send it us. To check that MySQL server is running you may use 'mysqladmin ping'.

Sometimes server started normally, but it's threads dies during the queries, 

messages about this are written into error log. See:



  http://dev.mysql.com/doc/mysql/en/error-log.html







 The second question is how to make sure all indexes are in-placed after

 the data restore?  I used the following script backup all data from



Usually indexes are created normally, and if you see them with show index

then everything should be ok. After receiving the information, which I

asked, we can make more exact conlusions.







sam wun [EMAIL PROTECTED] wrote:

 Hi,

 

 I have removed all old data and restore the backup data to the directory 

 /usr/local/mysql/data

 

 I also created root password with mysqladmin and flush its previliges...

 Execute the following perl+dbi script seems fine, it retrieves all data 

 from the customer table in DB which I just restored:

 #!/usr/bin/perl

 

 use DBI;

 

 $database = DB;

 $username = me;

 $pw = me0901;

 $dbh = DBI-connect(DBI:mysql:$database,$username,$pw);

 die Unable to connect: $DBI::errstr\n unless (defined $dbh);

 $sth = $dbh-prepare(q{SELECT * from customer}) or die Unable to prep 

 our query:.$dbh-errstr.\n;

 $rc = $sth-execute or die Unable to execute our query:.$dbh-errstr.\n;

 while ($aref = $sth-fetchrow_arrayref){

  for ($i=0; $i  $sth-{NUM_OF_FIELDS};$i++){

 print $sth-{NAME}-[$i].:.  $aref-[$i] . \n;

  }

 }

 $sth-finish;

 $dbh-disconnect;

 

 But when I execute a complex query, it hangs there forever. I don't have 

 a chance to see the result because I need to go home to have dinner. The 

 same query run in a FreeBSD system in Mysql5.01 just takes 2 seconds. 

 The MySQL 5.01 in FreeBSD is the source of the backup. I restore this 

 backup to MySQL 4.1.10 in Redhat9.0.

 

 I have checked the DB's indexes on the most complex table, it shows 

 indexes are being indexed.

 mysql show index from transaction;

 +-++-+--+-+---+-+--++--++-+

 | Table   | Non_unique | Key_name| Seq_in_index | Column_name | 

 Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |

 +-++-+--+-+---+-+--++--++-+

 | transaction |  0 | PRIMARY |1 | transcode   | 

 A |  161629 | NULL | NULL   |  | BTREE  | |

 | transaction |  1 | custcode|1 | custcode| 

 A | 715 | NULL | NULL   |  | BTREE  | |

 | transaction |  1 | prodcode|1 | prodcode| 

 A |3367 | NULL | NULL   |  | BTREE  | |

 | transaction |  1 | date|1 | date| 

 A |1197 | NULL | NULL   |  | BTREE  | |

 | transaction |  1 | netsales|1 | netsales| 

 A |   23089 | NULL | NULL   |  | BTREE  | |

 | transaction |  1 | salesvolume |1 | salesvolume | 

 A | 206 | NULL | NULL   |  | BTREE  | |

 | transaction |  1 | custcode_2  |1 | custcode| 

 A | 788 | NULL | NULL   |  | BTREE  | |

 | transaction |  1 | custcode_2  |2 | date| 

 A |   53876 | NULL | NULL   |  | BTREE  | |

 | transaction |  1 | prodcode_2  |1 | prodcode| 

 A | 923 | NULL | NULL   |  | BTREE  | |

 | transaction |  1 | prodcode_2  |2 | date| 

 A |  161629 | NULL | NULL   |  | BTREE  | |

 +-++-+--+-+---+-+--++--++-+

 10 rows in set (0.47 sec)

 

 One strange thing I found is when 

Re: mysql crash - innodb not starting

[mel list_php]

I did ps -aux
I see one mysqld running (mine, launched with --skip-innodb).
I shut it down.
ps -aux: nothing
I tried to start it again, doesn't work.
As it is running on a machine managed by the system service I went to see 
them after shutting it down again, they have no mysql process running. 
(still using ps-aux, but as root on that machine)

From that I think it means I don't have any mysql running  there, and I 
still get that error 11.
Or is there an other to see if a file is already used by a mysql process?
Do you think replacing the ibdata1 file by an empty file is possible?
Or doing a new install , copying my isam tables from the data directory and 
not touching the innodb files?
Doing a new install and setting up my tables (isam) with a backup dump is 
better?

I would prefer to repair the existing installation, but have no idea where 
to begin

Thanks a lot for your time.

From: Heikki Tuuri [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Subject: Re: mysql crash - innodb not starting
Date: Thu, 3 Mar 2005 23:20:56 +0200
Hi!
Error 11 means that you already have a mysqld process running on the same 
files.

Best regards,
Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM 
tables
http://www.innodb.com/order.php

--
Save the Pacific Northwest Tree Octopus! |
http://zapatopi.net/treeoctopus.html |
--
- Original Message - From: mel list_php 
[EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Thursday, March 03, 2005 2:25 PM
Subject: mysql crash - innodb not starting


Hi,
I just have a crash of my mysql 4.1.5 .(the machine where it was running
just shutdown)
Tried to restart it, and problem with innodb:
050303 11:58:46  [WARNING] Asked for 196608 thread stack, but got 126976
InnoDB: Unable to lock ./ibdata1, error: 11InnoDB: Error in opening
./ibdata1
050303 11:58:46  InnoDB: Operating system error number 11 in a file
operation.
InnoDB: Error number 11 means 'Resource temporarily unavailable'.
InnoDB: See also section 13.2 at http://www.innodb.com/ibman.php
InnoDB: about operating system error numbers.
InnoDB: Could not open or create data files.
InnoDB: If you tried to add new data files, and it failed here,
InnoDB: you should now edit innodb_data_file_path in my.cnf back
InnoDB: to what it was, and remove the new ibdata files InnoDB created
InnoDB: in this failed attempt. InnoDB only wrote those files full of
InnoDB: zeros, but did not yet use them in any way. But be careful: do not
InnoDB: remove old data files which contain your precious data!
050303 11:58:46  [ERROR] Can't init databases
050303 11:58:46  [ERROR] Aborting
050303 11:58:46  [NOTE] ./bin/mysqld: Shutdown complete
I'm sure I already saw that kind of problem, tried to go through the list
but didn't find the thread again. In addition I'm not very familiar with
that kind of thing.
I don't use innodb at all, only myisam, so I was able to restart with
--skip-innodb.
About the error code ressource unavailable I tried later on no 
difference,
the file ibdata1 is there with adequate permissions, I don't think that I
have a my.cnf...

To sum up I have no idea what to do, and I'm not a system guru!
My first ideas:
- removing the ibdata1 file and recreating an empty one
- I saw a force-recovery option but have no idea how it works
Hope somebody here can help me
Thanks a lot
_
It's fast, it's easy and it's free. Get MSN Messenger today!
http://www.msn.co.uk/messenger
--
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]

_
Want to block unwanted pop-ups? Download the free MSN Toolbar now!  
http://toolbar.msn.co.uk/

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

error message when creating tables

[Joppe A]

Hello all,

I have a problem with a script I have made, it always stops when it tries to 
creat a special table, I have been comparing this part of the script with the 
other parts that is working and cand find anything that looks wrong... 

The message MySQL write is: 

Can't create table './test_db/BOUNCE_MAILS.frm' (errno: 150)

What does errno: 150 means?

I have checked and mysql are allowed to write in the specific catalog...

Have any a clue?

/Joppe
-- 
___
Sign-up for Ads Free at Mail.com
http://promo.mail.com/adsfreejump.htm


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

Re: error message when creating tables

[Johan Höök]

Hi,
according to the manual:
http://dev.mysql.com/doc/mysql/en/innodb-foreign-key-constraints.html
150 is a foreign key definition would be incorrectly formed for the
altered table
/Johan
Joppe A wrote:
Hello all,
I have a problem with a script I have made, it always stops when it tries to creat a special table, I have been comparing this part of the script with the other parts that is working and cand find anything that looks wrong... 

The message MySQL write is: 

Can't create table './test_db/BOUNCE_MAILS.frm' (errno: 150)
What does errno: 150 means?
I have checked and mysql are allowed to write in the specific catalog...
Have any a clue?
/Joppe

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

Re: Correct way to use innodb_file_per_table?

[Mike Debnam]

Heikki,

 the best way would be to symlink whole database directories under the 
 datadir. Then also an ALTER TABLE keeps the new .ibd file on 
 the drive you 
 intended it to be on. If you symlink a single .ibd file, then 
 an ALTER will 
 create the new .ibd file as not symlinked.
 
 As an example, let us say you have three databases: 'database1', 
 'database2', and 'test'. You may shut down mysqld, copy all 
 the contents of 
 /datadir/database2 to drive 2, and then symlink the directory 
 /datadir/database2 to drive 2.
 


Hmm, ok. I have just one decent size database though. I want to split
the tables in that database between disks. I haven't turned on
innodb_file_per_table yet I'm trying to plan it out first, so I don't
know the file layout yet. If my data directory is /var/db/mysql and my
InnoDB data file is /var/db/mysql/ibdata1 then the table files will be
created under /var/db/mysql/MyDatabase/MyTable1.ibd,
/var/db/mysql/MyDatabase/MyTable2.ibd, etc it sounds like.

Is there a way to split those table files? So I could have something
like /data/disk1/MyTable1.ibd, /data/disk2/MyTable2.ibd?


Thanks for your help.

Mike

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

Re: mysql crash - innodb not starting

[Heikki Tuuri]

Mel,
do
ps -fA
Do you see more mysqld processes?
If the ibdata1 file stays locked even though there is no mysqld process, 
then this is a bug in Linux.

If you do not use InnoDB, you can delete ibdata1 and ib_logfiles.
Best regards,
Heikki
Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM 
tables
http://www.innodb.com/order.php

Order MySQL Network from http://www.mysql.com/network/
- Alkuperäinen viesti - 
Lähettäjä: mel list_php [EMAIL PROTECTED]
Vastaanottaja: mysql@lists.mysql.com
Kopio: [EMAIL PROTECTED]
Lähetetty: Friday, March 04, 2005 12:00 PM
Aihe: Re: mysql crash - innodb not starting


I did ps -aux
I see one mysqld running (mine, launched with --skip-innodb).
I shut it down.
ps -aux: nothing
I tried to start it again, doesn't work.
As it is running on a machine managed by the system service I went to see 
them after shutting it down again, they have no mysql process running. 
(still using ps-aux, but as root on that machine)

From that I think it means I don't have any mysql running  there, and I 
still get that error 11.

Or is there an other to see if a file is already used by a mysql process?
Do you think replacing the ibdata1 file by an empty file is possible?
Or doing a new install , copying my isam tables from the data directory 
and not touching the innodb files?
Doing a new install and setting up my tables (isam) with a backup dump is 
better?

I would prefer to repair the existing installation, but have no idea where 
to begin

Thanks a lot for your time.

From: Heikki Tuuri [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Subject: Re: mysql crash - innodb not starting
Date: Thu, 3 Mar 2005 23:20:56 +0200
Hi!
Error 11 means that you already have a mysqld process running on the same 
files.

Best regards,
Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up 
MyISAM tables
http://www.innodb.com/order.php

--
Save the Pacific Northwest Tree Octopus! |
http://zapatopi.net/treeoctopus.html |
--
- Original Message - From: mel list_php 
[EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Thursday, March 03, 2005 2:25 PM
Subject: mysql crash - innodb not starting


Hi,
I just have a crash of my mysql 4.1.5 .(the machine where it was running
just shutdown)
Tried to restart it, and problem with innodb:
050303 11:58:46  [WARNING] Asked for 196608 thread stack, but got 126976
InnoDB: Unable to lock ./ibdata1, error: 11InnoDB: Error in opening
./ibdata1
050303 11:58:46  InnoDB: Operating system error number 11 in a file
operation.
InnoDB: Error number 11 means 'Resource temporarily unavailable'.
InnoDB: See also section 13.2 at http://www.innodb.com/ibman.php
InnoDB: about operating system error numbers.
InnoDB: Could not open or create data files.
InnoDB: If you tried to add new data files, and it failed here,
InnoDB: you should now edit innodb_data_file_path in my.cnf back
InnoDB: to what it was, and remove the new ibdata files InnoDB created
InnoDB: in this failed attempt. InnoDB only wrote those files full of
InnoDB: zeros, but did not yet use them in any way. But be careful: do 
not
InnoDB: remove old data files which contain your precious data!
050303 11:58:46  [ERROR] Can't init databases
050303 11:58:46  [ERROR] Aborting

050303 11:58:46  [NOTE] ./bin/mysqld: Shutdown complete
I'm sure I already saw that kind of problem, tried to go through the list
but didn't find the thread again. In addition I'm not very familiar with
that kind of thing.
I don't use innodb at all, only myisam, so I was able to restart with
--skip-innodb.
About the error code ressource unavailable I tried later on no 
difference,
the file ibdata1 is there with adequate permissions, I don't think that I
have a my.cnf...

To sum up I have no idea what to do, and I'm not a system guru!
My first ideas:
- removing the ibdata1 file and recreating an empty one
- I saw a force-recovery option but have no idea how it works
Hope somebody here can help me
Thanks a lot
_
It's fast, it's easy and it's free. Get MSN Messenger today!
http://www.msn.co.uk/messenger
--
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]

_
Want to block unwanted pop-ups? Download the free MSN Toolbar now! 
http://toolbar.msn.co.uk/


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

Re: Correct way to use innodb_file_per_table?

[Heikki Tuuri]

Mike,
- Alkuperäinen viesti - 
Lähettäjä: Mike Debnam [EMAIL PROTECTED]
Vastaanottaja: mysql@lists.mysql.com
Kopio: [EMAIL PROTECTED]
Lähetetty: Friday, March 04, 2005 2:49 PM
Aihe: Re: Correct way to use innodb_file_per_table?


Heikki,
the best way would be to symlink whole database directories under the
datadir. Then also an ALTER TABLE keeps the new .ibd file on
the drive you
intended it to be on. If you symlink a single .ibd file, then
an ALTER will
create the new .ibd file as not symlinked.
As an example, let us say you have three databases: 'database1',
'database2', and 'test'. You may shut down mysqld, copy all
the contents of
/datadir/database2 to drive 2, and then symlink the directory
/datadir/database2 to drive 2.

Hmm, ok. I have just one decent size database though. I want to split
the tables in that database between disks. I haven't turned on
innodb_file_per_table yet I'm trying to plan it out first, so I don't
know the file layout yet. If my data directory is /var/db/mysql and my
InnoDB data file is /var/db/mysql/ibdata1 then the table files will be
created under /var/db/mysql/MyDatabase/MyTable1.ibd,
/var/db/mysql/MyDatabase/MyTable2.ibd, etc it sounds like.
Is there a way to split those table files? So I could have something
like /data/disk1/MyTable1.ibd, /data/disk2/MyTable2.ibd?
you can move the .ibd file where you want, and put a symlink in place.
But remember that an ALTER will recreate the table to its original database 
dir, because ALTER does not know about symlinks.

Thanks for your help.
Mike
Best regards,
Heikki
Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM 
tables
http://www.innodb.com/order.php

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

Re: mysql crash - innodb not starting

[mel list_php]

Hi Heikki,
Still only one process with ps -fA. And mysql doesn't want to start 
normally.

This morning I tried by deleting innodb files (ibdata1,ib_logfile0, 
ib_logfile1), and it didn't succeed in recreating them.

I just tried once again now and everything is fine, mysql starts and 
recreates the files.
Thank you very much for your help!

Just for information: if I had an innodb table (I'm thinking about it), the 
data would have been in there.
Any recovery method? Or I would have had to delete the files and restore the 
data from backup?

Anyway, thanks a lot!
Melanie

From: Heikki Tuuri [EMAIL PROTECTED]
To: mel list_php [EMAIL PROTECTED], mysql@lists.mysql.com
Subject: Re: mysql crash - innodb not starting
Date: Fri, 4 Mar 2005 15:50:26 +0200
Mel,
do
ps -fA
Do you see more mysqld processes?
If the ibdata1 file stays locked even though there is no mysqld process, 
then this is a bug in Linux.

If you do not use InnoDB, you can delete ibdata1 and ib_logfiles.
Best regards,
Heikki
Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM 
tables
http://www.innodb.com/order.php

Order MySQL Network from http://www.mysql.com/network/
- Alkuperäinen viesti - Lähettäjä: mel list_php 
[EMAIL PROTECTED]
Vastaanottaja: mysql@lists.mysql.com
Kopio: [EMAIL PROTECTED]
Lähetetty: Friday, March 04, 2005 12:00 PM
Aihe: Re: mysql crash - innodb not starting


I did ps -aux
I see one mysqld running (mine, launched with --skip-innodb).
I shut it down.
ps -aux: nothing
I tried to start it again, doesn't work.
As it is running on a machine managed by the system service I went to see 
them after shutting it down again, they have no mysql process running. 
(still using ps-aux, but as root on that machine)

From that I think it means I don't have any mysql running  there, and I 
still get that error 11.

Or is there an other to see if a file is already used by a mysql process?
Do you think replacing the ibdata1 file by an empty file is possible?
Or doing a new install , copying my isam tables from the data directory 
and not touching the innodb files?
Doing a new install and setting up my tables (isam) with a backup dump is 
better?

I would prefer to repair the existing installation, but have no idea where 
to begin

Thanks a lot for your time.

From: Heikki Tuuri [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Subject: Re: mysql crash - innodb not starting
Date: Thu, 3 Mar 2005 23:20:56 +0200
Hi!
Error 11 means that you already have a mysqld process running on the same 
files.

Best regards,
Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up 
MyISAM tables
http://www.innodb.com/order.php

--
Save the Pacific Northwest Tree Octopus! |
http://zapatopi.net/treeoctopus.html |
--
- Original Message - From: mel list_php 
[EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Thursday, March 03, 2005 2:25 PM
Subject: mysql crash - innodb not starting


Hi,
I just have a crash of my mysql 4.1.5 .(the machine where it was running
just shutdown)
Tried to restart it, and problem with innodb:
050303 11:58:46  [WARNING] Asked for 196608 thread stack, but got 126976
InnoDB: Unable to lock ./ibdata1, error: 11InnoDB: Error in opening
./ibdata1
050303 11:58:46  InnoDB: Operating system error number 11 in a file
operation.
InnoDB: Error number 11 means 'Resource temporarily unavailable'.
InnoDB: See also section 13.2 at http://www.innodb.com/ibman.php
InnoDB: about operating system error numbers.
InnoDB: Could not open or create data files.
InnoDB: If you tried to add new data files, and it failed here,
InnoDB: you should now edit innodb_data_file_path in my.cnf back
InnoDB: to what it was, and remove the new ibdata files InnoDB created
InnoDB: in this failed attempt. InnoDB only wrote those files full of
InnoDB: zeros, but did not yet use them in any way. But be careful: do 
not
InnoDB: remove old data files which contain your precious data!
050303 11:58:46  [ERROR] Can't init databases
050303 11:58:46  [ERROR] Aborting

050303 11:58:46  [NOTE] ./bin/mysqld: Shutdown complete
I'm sure I already saw that kind of problem, tried to go through the 
list
but didn't find the thread again. In addition I'm not very familiar with
that kind of thing.
I don't use innodb at all, only myisam, so I was able to restart with
--skip-innodb.

About the error code ressource unavailable I tried later on no 
difference,
the file ibdata1 is there with adequate permissions, I don't think that 
I
have a my.cnf...

To sum up I have no idea what to do, and I'm not a system guru!
My first ideas:
- removing the ibdata1 file and recreating an empty one
- I saw a force-recovery option but have no idea how it works
Hope somebody here can help me
Thanks a lot

RE: backup scripts

[Kelly . Brace]

Tim,
  Thanks, that did the trick.  One thing, I tried to zip the files up and 
I get permission denied when I attempt to do that with the command below. 
All of the files are there, but they don't get zipped up.  I also notice 
that the txt files and the sql files have slightly different ownership. 
The group owner is different.  Any ideas? Thanks,
Kelly

Script:
#!/bin/sh
date=`date -I`
sudo su -

mkdir /usr/local/mysql/bakups/$date

chown root.mysql /usr/local/mysql/bakups/$date

chmod 770 /usr/local/mysql/bakups/$date

/usr/local/mysql/bin/mysqldump --tab=/usr/local/mysql/bakups/$date --opt 
webcollab | bz2 -c  
/usr/local/mysql/bakups/$date/webcollab_backup-$date.sql.bz2

--



Kelly S. Brace
Information Technology Exchange Center
Twin Rise 200
1300 Elmwood Avenue
Buffalo, NY 14222
http://www.itec.suny.edu

Main: 716-878-4832
Fax: 716-878-3485
Office: 716-878-3895
Cell: 716-432-4978
[EMAIL PROTECTED]




Tom Crimmins [EMAIL PROTECTED] 
03/03/2005 03:06 PM

To
[EMAIL PROTECTED]
cc
mysql@lists.mysql.com
Subject
RE: backup scripts






Kelly,

You can find out what user mysqld is running as with the following:

#ps axu | grep mysqld

To change the permissions on the directory run the following as root:

#chown mysql.mysql /usr/local/mysql/bakups
#chmod 770 /usr/local/mysql/bakups

If it is a different user, substitute it in for mysql in the first 
command.

-- 
Tom Crimmins
Interface Specialist
Pottawattamie County, Iowa

On Thursday, March 03, 2005 13:58, [EMAIL PROTECTED] wrote:

 Tom,
   I seem to be having difficulty allowing the mysqld user wx privs on
 the /backup folder.   I read the following, but I don't see how to
 allow mysqld to w and x to that directory:
 http://dev.mysql.com/doc/mysql/en/mysqldump.html 
 
 Kelly
 
 On Thursday, March 03, 2005 13:12, [EMAIL PROTECTED]
 wrote: 
 
 Hi Kelly,
 
 Hello,
   When I attempt to try and run the backup:
 
 shell mysqldump --tab=/path/to/some/dir --opt db_name
 I get the following errors:
 ./mysqldump: Got error: 1: Can't create/write to file
 '/usr/local/mysql/bakups/config.txt' (Errcode: 13) when executing
 'SELECT INTO OUTFILE'
 
 [EMAIL PROTECTED] tom]$ perror 13
 Error code  13:  Permission denied
 
 You need to make sure that the user mysqld is running as has write and
 execute permission to /usr/local/mysql/bakups.
 
 Read the following page. Specifically the part about the tab option.
 http://dev.mysql.com/doc/mysql/en/mysqldump.html
 
 Or:
 
 shell mysqlhotcopy db_name /path/to/some/dir
 DBI-connect(;host=localhost;mysql_read_default_group=mysqlhotcopy)
 failed: Client does not support authentication protocol requested by
 server; consider upgrading MySQL client at ./mysqlhotcopy line 178
 
 You need a newer version of DBD-mysql (you can get this from CPAN),
 or you can use the old password option.
 
 http://dev.mysql.com/doc/mysql/en/old-client.html
 
 I followed the directions from:
 http://dev.mysql.com/doc/mysql/en/backup.html
 
 I also attempted to follow these directions, to no avail:
 --
 mysqlhotcopy, etc is great - but using it (and most other myql
 automation scripts) requires placing a user/password on the command
 line for all/some to see (ps axw)
 There doesn't appear to be a way to place the user/pass into a file
 somewhere and specify only that (secured) filename on the command
 line. I get around this in the case of mysqlhotcopy by taking a
 local copy of the script (perl) and hard-coding the auth info into
 that copy thus: 
 
 mysqlhotcopy - line 164ish:
 
 my $dbh =
 DBI-connect(dbi:mysql:$dsn;mysql_read_default_group=mysqlhotcopy,
 'backup_user', 'backup_password'},
 
 and again at around line 745:
 
 my $dbh =
 

DBI-connect(dbi:mysql:${db}${dsn};mysql_read_default_group=mysqlhotcopy,
 backup_user, backup_password,
 
 then, just to be sure,
 
 chown root.nobody mysqlhotcopy
 chmod 700 mysqlhotcopy
 
 Any ideas would be greatly appreciated.  I would really like to add
 this to a cronjob to have it run automatically. Thanks in advance!

Re: mysql crash - innodb not starting

[Heikki Tuuri]

Mel,
- Alkuperäinen viesti - 
Lähettäjä: mel list_php [EMAIL PROTECTED]
Vastaanottaja: [EMAIL PROTECTED]; mysql@lists.mysql.com
Lähetetty: Friday, March 04, 2005 4:13 PM
Aihe: Re: mysql crash - innodb not starting


Hi Heikki,
Still only one process with ps -fA. And mysql doesn't want to start 
normally.

This morning I tried by deleting innodb files (ibdata1,ib_logfile0, 
ib_logfile1), and it didn't succeed in recreating them.

I just tried once again now and everything is fine, mysql starts and 
recreates the files.
Thank you very much for your help!

Just for information: if I had an innodb table (I'm thinking about it), 
the data would have been in there.
the data is normally in the ibdata1 file.
Any recovery method? Or I would have had to delete the files and restore 
the data from backup?
Well, I have not heard of a case where Linux would keep a file lock on a 
file if mysqld is killed. And if there is a bug in Linux, a computer reboot 
should remove any file locks anyway.

Anyway, thanks a lot!
Melanie
Best regards,
Heikki
Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM 
tables
http://www.innodb.com/order.php

Order MySQL Network from http://www.mysql.com/network/

From: Heikki Tuuri [EMAIL PROTECTED]
To: mel list_php [EMAIL PROTECTED], mysql@lists.mysql.com
Subject: Re: mysql crash - innodb not starting
Date: Fri, 4 Mar 2005 15:50:26 +0200
Mel,
do
ps -fA
Do you see more mysqld processes?
If the ibdata1 file stays locked even though there is no mysqld process, 
then this is a bug in Linux.

If you do not use InnoDB, you can delete ibdata1 and ib_logfiles.
Best regards,
Heikki
Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up 
MyISAM tables
http://www.innodb.com/order.php

Order MySQL Network from http://www.mysql.com/network/
- Alkuperäinen viesti - Lähettäjä: mel list_php 
[EMAIL PROTECTED]
Vastaanottaja: mysql@lists.mysql.com
Kopio: [EMAIL PROTECTED]
Lähetetty: Friday, March 04, 2005 12:00 PM
Aihe: Re: mysql crash - innodb not starting


I did ps -aux
I see one mysqld running (mine, launched with --skip-innodb).
I shut it down.
ps -aux: nothing
I tried to start it again, doesn't work.
As it is running on a machine managed by the system service I went to see 
them after shutting it down again, they have no mysql process running. 
(still using ps-aux, but as root on that machine)

From that I think it means I don't have any mysql running  there, and I 
still get that error 11.

Or is there an other to see if a file is already used by a mysql process?
Do you think replacing the ibdata1 file by an empty file is possible?
Or doing a new install , copying my isam tables from the data directory 
and not touching the innodb files?
Doing a new install and setting up my tables (isam) with a backup dump is 
better?

I would prefer to repair the existing installation, but have no idea 
where to begin

Thanks a lot for your time.

From: Heikki Tuuri [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Subject: Re: mysql crash - innodb not starting
Date: Thu, 3 Mar 2005 23:20:56 +0200
Hi!
Error 11 means that you already have a mysqld process running on the 
same files.

Best regards,
Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up 
MyISAM tables
http://www.innodb.com/order.php

--
Save the Pacific Northwest Tree Octopus! |
http://zapatopi.net/treeoctopus.html |
--
- Original Message - From: mel list_php 
[EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Thursday, March 03, 2005 2:25 PM
Subject: mysql crash - innodb not starting


Hi,
I just have a crash of my mysql 4.1.5 .(the machine where it was 
running
just shutdown)

Tried to restart it, and problem with innodb:
050303 11:58:46  [WARNING] Asked for 196608 thread stack, but got 
126976
InnoDB: Unable to lock ./ibdata1, error: 11InnoDB: Error in opening
./ibdata1
050303 11:58:46  InnoDB: Operating system error number 11 in a file
operation.
InnoDB: Error number 11 means 'Resource temporarily unavailable'.
InnoDB: See also section 13.2 at http://www.innodb.com/ibman.php
InnoDB: about operating system error numbers.
InnoDB: Could not open or create data files.
InnoDB: If you tried to add new data files, and it failed here,
InnoDB: you should now edit innodb_data_file_path in my.cnf back
InnoDB: to what it was, and remove the new ibdata files InnoDB created
InnoDB: in this failed attempt. InnoDB only wrote those files full of
InnoDB: zeros, but did not yet use them in any way. But be careful: do 
not
InnoDB: remove old data files which contain your precious data!
050303 11:58:46  [ERROR] Can't init databases
050303 11:58:46  [ERROR] 

Re: insert data

[Roger Baklund]

Gerald Preston wrote:
[...]
I get DBI connect'club','gjwpp88',.. failed; Client does not support
authentication protocol requested by server
Check this:
URL: http://dev.mysql.com/doc/mysql/en/old-client.html 
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

select date_format('2004-10-03 15:06:14','%m/%d/%y %T');

[Scott Hamm]

How do I create a table using:

date_format('2004-10-03 15:06:14','%m/%d/%y %T');

That way I can import Access Data?
-- 
Power to people, Linux is here.

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

Replication from multiple masters to one slave

[Irek Sonina]

I saw some time ago a post on this list with a script for managing 
multiple masters but I have lost it and I can not found it on a google, 
mysql list archives, anywhere.

I have wrote a program in c for managing it but I would like to compare 
if I didn't forgot about something.

I would be very grateful for a help.
--
Irek Sonina

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

Re: MySQL hangs

[Philippe Poelvoorde]

Marcin Lewandowski wrote:
Hi,
I've got webserver with php and mysql-4.0.22
There I've got large phpbb2. 
That's it. phpbb2 is known to consume ressources, if I were you i 
wouldn't search further... not a mysql issue afaik

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

Re: Speed of Inserts into MyIsam tables from mysqlbinlog

[Eric Bergen]

Since playing back binary logs is just like doing normal queries the
tips on this page mostly apply.
http://dev.mysql.com/doc/mysql/en/insert-speed.html

Depending on your shell scripting level you might have to mysqlbinlog
to a file then
add in lock statments or whatever you decide to use. 

-Eric


On Fri, 4 Mar 2005 00:20:55 -0500, Thomas Lekai [EMAIL PROTECTED] wrote:
 I was running a table in InnoDB, and the table had about 6 indexes, none
 of which seemed to be affected when I ran mysqlbinlog against the tables
 in order to apply bin-logs from production against a test system.  I was
 manage to process upwards of 2300 queries per second by throwing about
 1.8 GB of memory into the innodb_buffer_pool.
 
 Now that I am dealing with MyIsam tables, I shifted my memory towards
 the key_buffer, and am considering using bulk_insert_buffer_size.  I am
 not sure what is a good value for the bulk_insert_buffer_size.  I have
 also removed all indexes that are not needed from the table while it is
 being loaded with data.
 
 My question, is there anything else that I can do to increase the speed
 of the inserts into this MyIsam table as the bin_logs are played back on
 the system?  This is also used to help gauge performance, so this method
 of inserting data is important to the process as a whole.
 
 Regards,
 
 Thomas.
 
 


-- 
Eric Bergen
[EMAIL PROTECTED]
http://www.ebergen.net

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

Console aliases

[jacob martinson]

Is it possible to setup aliases in the mysql console to cut down on
typing, i.e. instead of having to type out show tables I can just
type \dt as in postgresql?

Thanks,
Jacob

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

re: Exporting Data via Command Line

[MrExecutive]

Hello Guys,
   I am a newbie to mySQL and im trying to export my database via the 
mySQL Command line but i cant seem to find any good documentation on how 
to do this. More articles are telling me to use phpAdmin or some other 
gui tool. I would like to learn the commands anyone has a good doc on this?

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

Re: Exporting Data via Command Line

[Scott Hamm]

http://www.mysql.com would be a nice place to start.


On Fri, 04 Mar 2005 13:12:02 -0500, MrExecutive [EMAIL PROTECTED] wrote:
 Hello Guys,
 
 I am a newbie to mySQL and im trying to export my database via the
 mySQL Command line but i cant seem to find any good documentation on how
 to do this. More articles are telling me to use phpAdmin or some other
 gui tool. I would like to learn the commands anyone has a good doc on this?
 
 Thanks
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 
 


-- 
Power to people, Linux is here.

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

Re: Exporting Data via Command Line

[jacob martinson]

run:

mysqldump --help


On Fri, 04 Mar 2005 13:12:02 -0500, MrExecutive [EMAIL PROTECTED] wrote:
 Hello Guys,
 
 I am a newbie to mySQL and im trying to export my database via the
 mySQL Command line but i cant seem to find any good documentation on how
 to do this. More articles are telling me to use phpAdmin or some other
 gui tool. I would like to learn the commands anyone has a good doc on this?
 
 Thanks
 
 --
 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]

Re: Exporting Data via Command Line

[jacob martinson]

i'll usually pipe mysqldump into gzip if it's a large database, or gpg
if there's sensitive data in it.

-jacob


On Fri, 4 Mar 2005 12:22:08 -0600, jacob martinson
[EMAIL PROTECTED] wrote:
 run:
 
 mysqldump --help
 
 
 On Fri, 04 Mar 2005 13:12:02 -0500, MrExecutive [EMAIL PROTECTED] wrote:
  Hello Guys,
 
  I am a newbie to mySQL and im trying to export my database via the
  mySQL Command line but i cant seem to find any good documentation on how
  to do this. More articles are telling me to use phpAdmin or some other
  gui tool. I would like to learn the commands anyone has a good doc on this?
 
  Thanks
 
  --
  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]

update and get value

[Scott Purcell]

UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
 
 
I am writing some code that I need to actually get the menu_sequence from 
above, store it in a  variable and then do a increment. I do not know why I am 
having trouble with this, but it looks like I would have to
String seq = select id from menu_sequence
use seq,
then
String update = update menu_sequence set id=last_insert_id(id+1)
 
The reason is, I need to use the seq variable to create a true ID using some 
other algorithms. Anyway is there a better or simpler way to do this? I hate to 
do two queries and then I have to worry about synchronization.
 
thanks,
Scott
 

Scott K Purcell | Developer | VERTIS | 
555 Washington Ave. 4th Floor | St. Louis, MO 63101 | 
314.588.0720 Ext:1320 | [EMAIL PROTECTED] | http://www.vertisinc.com 
http://www.vertisinc.com/  

Vertis is the premier provider of targeted advertising, media, and 
marketing services that drive consumers to marketers more effectively. 
 

 

re: Exporting Data via Command Line

[SGreen]

MrExecutive [EMAIL PROTECTED] wrote on 03/04/2005 01:12:02 PM:

 Hello Guys,
 
 I am a newbie to mySQL and im trying to export my database via the 
 mySQL Command line but i cant seem to find any good documentation on how 

 to do this. More articles are telling me to use phpAdmin or some other 
 gui tool. I would like to learn the commands anyone has a good doc on 
this?
 
 
 Thanks
 

Try reading up on these commands:

(from a command shell prompt) mysqldump
http://dev.mysql.com/doc/mysql/en/mysqldump.html

(from within a MySQL client) SELECT ... INTO OUTFILE
http://dev.mysql.com/doc/mysql/en/select.html

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: update and get value

[SGreen]

Scott Purcell [EMAIL PROTECTED] wrote on 03/04/2005 01:42:40 PM:

 UPDATE menu_sequence SET id=LAST_INSERT_ID(id+1);
 
 
 I am writing some code that I need to actually get the menu_sequence
 from above, store it in a  variable and then do a increment. I do 
 not know why I am having trouble with this, but it looks like I would 
have to
 String seq = select id from menu_sequence
 use seq,
 then
 String update = update menu_sequence set id=last_insert_id(id+1)
 
 The reason is, I need to use the seq variable to create a true ID 
 using some other algorithms. Anyway is there a better or simpler way
 to do this? I hate to do two queries and then I have to worry about 
 synchronization.
 
 thanks,
 Scott
 
 
 Scott K Purcell | Developer | VERTIS | 
 555 Washington Ave. 4th Floor | St. Louis, MO 63101 | 
 314.588.0720 Ext:1320 | [EMAIL PROTECTED] | 
http://www.vertisinc.com 
 http://www.vertisinc.com/ 
 
 Vertis is the premier provider of targeted advertising, media, and 
 marketing services that drive consumers to marketers more effectively. 
 
 
 

If you wanted to know the last ID value generated in your session you need 
to use the LAST_INSERT_ID() function. LAST_INSERT_ID() is connection 
specific so you don't run the risk of getting someone else's most recently 
generated value. Is that what you were worried about when you mentioned 
synchronization ?

Details -- http://dev.mysql.com/doc/mysql/en/information-functions.html

What exactly are you trying to achieve? If you could be a little more 
specific, we could be more helpful.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

Re: mysql 4.0.18 crashing on startup

[Baba Buehler]

Heikki Tuuri wrote:
Baba,
an undo log seems to be corrupt. He should restart mysqld with:
innodb_force_recovery=4
dump all tables, and recreate the whole InnoDB installation.
This was roughly my assessment and recommended procedure as well.  The 
customer decided to restore the system from a full backup they had made 
about a week earlier.


Did the .err log contain any hint what could be behind the corruption?
No, the system had been up for a couple of months (since they last 
patched some other software) and the .err file didn't report any 
abnormalities up until MySQL was restarted and the stack traces began.

We scanned the disks on the system for bad blocks and found none, and 
the system had been on UPS power and had not suffered any harsh reboots.

We have many hundreds of MySQL/InnoDB servers in the field, and 
occationally see db corruption.  Most of the time we trace it back to 
faulty disk hardware, but occationally we see one like this where we 
can't find the smoking gun.

Is there any setting or anything you recommend turning on a system that 
is suspect to divine more information on where something like this may 
have originated?

thanks,
baba

Best regards,
Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up 
MyISAM tables
http://www.innodb.com/order.php

- Original Message - From: Baba Buehler [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Wednesday, March 02, 2005 6:32 PM
Subject: mysql 4.0.18 crashing on startup

I've got a customer with a mysql that is crashing on startup.  MySQL is
4.0.18 with InnoDB tables on a Linux 2.4.26 system.  MySQL binaries are
Linux x86 glibc static gcc from mysql.com
Can anyone divine what might have happened to cause this?
thanks,
baba

resolve_stack_dump gives:
0x8071f44 handle_segfault + 420
0x82a0e38 pthread_sighandler + 184
0x818cfeb trx_undo_rec_get_partial_row + 443
0x81302af row_purge_parse_undo_rec + 863
0x813033f row_purge + 127
0x81304d7 row_purge_step + 23
0x8115aa3 que_run_threads + 755
0x8189520 trx_purge + 2304
0x811b8dd srv_master_thread + 1389
0x829e5ec pthread_start_thread + 220
0x82c7dea thread_start + 4

the .err log shows this, ever repeating:
050222 10:49:57  mysqld started
050222 10:49:57  InnoDB: Started
/usr/sbin/mysqld: ready for connections.
Version: '4.0.18-standard'  socket: '/tmp/mysql4.sock'  port: 3306
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly 
built,
or misconfigured. This error can also be caused by malfunctioning 
hardware.
We will try our best to scrape up some info that will hopefully help
diagnose
the problem, but since we have already crashed, something is definitely
wrong
and this may fail.

key_buffer_size=1048576
read_buffer_size=4190208
max_used_connections=0
max_connections=35
threads_connected=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections
= 287603 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd=(nil)
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0xbfebf5b8, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
0x8071f44
0x82a0e38
0x818cfc1
0x81302af
0x813033f
0x81304d7
0x8115aa3
0x8189520
0x811bb6f
0x829e5ec
0x82c7dea
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://www.mysql.com/doc/en/Using_stack_trace.html and
follow instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.
Number of processes running now: 1
050222 10:49:58  mysqld restarted
050222 10:49:59  InnoDB: Started
/usr/sbin/mysqld: ready for connections.
Version: '4.0.18-standard'  socket: '/tmp/mysql4.sock'  port: 3306
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly 
built,
or misconfigured. This error can also be caused by malfunctioning 
hardware.
We will try our best to scrape up some info that will hopefully help
diagnose
the problem, but since we have already crashed, something is definitely
wrong
and this may fail.

key_buffer_size=1048576
read_buffer_size=4190208
max_used_connections=0
max_connections=35
threads_connected=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections
= 287603 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd=(nil)
Attempting backtrace. You 

BINARY LIKE '..' just does not work

[Artem Kuchin]

MySQL:  4.1.8a
OS: FreeBSD 5.3
The problem is that BINARY LIKE '..' returns no rows no
matter what specified in '..' however BINARY field_name LIKE '..'
work, but I cannot use it because this does not use index (EXPLAINs are
bellow). According to documentation BINARY LIKE shoud work. But it
does not.
Smells like a bug.
Defaukt charset for mysql (it is specified at compile time) is cp1251 if
it matters (test uses only latin chars).
Below is the test case:
(maybe someone else could run it on there server and tell me if it works
and also report mysql server version where it was tested).
CREATE TABLE voc2 (
 id int(10) unsigned NOT NULL default '0',
 word char(32) binary NOT NULL default '',
 counter int(10) unsigned NOT NULL default '0',
 PRIMARY KEY  (id),
 UNIQUE KEY i_vocabulary_word (word)
) TYPE=MyISAM;
insert into voc2 values(1,'falama',1);
insert into voc2 values(2,'lagraf',1);
insert into voc2 values(3,'folka',1);
insert into voc2 values(4,'pofik',1);
mysql select * from voc2 where word like 'f%';
Empty set (0.00 sec)
mysql select * from voc2 where word like '%f';
+++-+
| id | word   | counter |
+++-+
|  2 | lagraf |   1 |
+++-+
1 row in set (0.00 sec)
mysql select * from voc2 where word like '%f%';
+++-+
| id | word   | counter |
+++-+
|  1 | falama |   1 |
|  2 | lagraf |   1 |
|  3 | folka  |   1 |
|  4 | pofik  |   1 |
+++-+
4 rows in set (0.00 sec)
mysql select * from voc2 where word like BINARY 'f%';
Empty set (0.01 sec)
mysql select * from voc2 where word like BINARY '%f';
+++-+
| id | word   | counter |
+++-+
|  2 | lagraf |   1 |
+++-+
1 row in set (0.00 sec)
mysql select * from voc2 where word like BINARY '%f%';
+++-+
| id | word   | counter |
+++-+
|  1 | falama |   1 |
|  2 | lagraf |   1 |
|  3 | folka  |   1 |
|  4 | pofik  |   1 |
+++-+
4 rows in set (0.00 sec)
mysql select * from voc2 where binary word like  'f%';
+++-+
| id | word   | counter |
+++-+
|  1 | falama |   1 |
|  3 | folka  |   1 |
+++-+
2 rows in set (0.00 sec)
mysql explain select * from voc2 where binary word like  'f%';
++-+---+--+---+--+-+--+--+-+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | 
rows | Extra   |
++-+---+--+---+--+-+--+--+-+
|  1 | SIMPLE  | voc2  | ALL  | NULL  | NULL |NULL | NULL | 
4 | Using where |
++-+---+--+---+--+-+--+--+-+
1 row in set (0.00 sec)

mysql explain select * from voc2 where  word like binary 'f%';
++-+---+---+---+---+-+--+--+-+
| id | select_type | table | type  | possible_keys | key   | 
key_len | ref  | rows | Extra   |
++-+---+---+---+---+-+--+--+-+
|  1 | SIMPLE  | voc2  | range | i_vocabulary_word | i_vocabulary_word | 
32 | NULL |1 | Using where |
++-+---+---+---+---+-+--+--+-+
1 row in set (0.00 sec)

This sucks.
Regards,
Artem 

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

Re: replication with automatic master failover problems

[Atle Veka]

On Thu, 3 Mar 2005, Olivier Kaloudoff wrote:

 Hello,

   we're working on a two node setup to achieve high
 availability using 4.1.8;

   we first setup both servers with --bin-log, and only
 the slave server (db1) with --log-slave-updates. (binary logfile
 name is master on both server)

   replication from db0 to db1 runs smoothly.

   next, we stop both servers and db1 becomes master, with
 db0 slave. Replication still ok.

   second stop: db0 becomes master like at the beginning,
 db1 slave.

Do you shut down both databases when you swap their functions? If so did
you let the slave catch up before you shut it down?

Working with replication in this manner gets tricky real quick.. :) Do you
have the replication options in my.cnf, such as
master-host/master-user/etc?

If not, I would suggest managing replication manually using 'CHANGE MASTER
TO'. When you want to swap duties, do something like (assuming m1 =
current master, m2 = current slave):

m1: deny updates
m2: make sure slaving catches up, then issue 'SLAVE STOP'
m2: issue 'RESET MASTER'
(start sending write queries to m2)
m1: 'CHANGE MASTER TO MASTER_HOST = 'm2', MASTER_USER = '..', ...'

http://dev.mysql.com/doc/mysql/en/change-master-to.html

Since you have reset the master, when you do 'CHANGE MASTER TO ..' you
should not specify any binlog information as it will start from the
beginning. I have never tried this exact scenario myself, but hopefully
you can work with the ideas. :)



Atle
-
Flying Crocodile Inc, Unix Systems Administrator

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

stop and restart mysql on freebsd?

[Chip Wiegand]

I made a change to my.cnf and want it to restart mysqld_safe so it will 
re-read my.cnf. How do I do this?
Thanks,
Chip

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

Re: Read / Write statistics

[Atle Veka]

On Thu, 3 Mar 2005, E SA wrote:

 Hi,

 I am trying to obtain read/write statistics from our
 database (4.0.21).  I started by using the
 Bytes_received and Bytes_sent from show status;
 however, today one of them (Bytes_sent) reset itself
 to 0...

 Which really messes up my calculations...

 I need this data... Is there any other way to obtain
 it?

Unfortunately you need to be able to handle counter wraps/reset.. I
believe the counters are 32 bit (2^32).

I recommend using rrdtool as it has data types designed to handle 32 bit
counters (I do however recommend using a DERIVE ds vs. COUNTER). Cacti is
an open source tool based on rrdtool with a MySQL statistics plugin, also
something to look at:
http://forums.cacti.net/about6108-0-asc-0.html


Atle
-
Flying Crocodile Inc, Unix Systems Administrator

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

Re: stop and restart mysql on freebsd?

[Scott Baker]

Someone else may have to chime in, but I think this will work:
mysqladmin -u root -p refresh
According to the help refresh will Flush all tables and close and 
open logfiles. You may have to try it. Worst case just issue a 
shutdown and then start it up again.

Scott
Chip Wiegand wrote:
I made a change to my.cnf and want it to restart mysqld_safe so it will 
re-read my.cnf. How do I do this?
Thanks,
Chip

--
Scott Baker
Canby Telephone - Network Administrator - RHCE
Ph: 503.266.8253
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: stop and restart mysql on freebsd?

[Dan Nelson]

In the last episode (Mar 04), Chip Wiegand said:
 I made a change to my.cnf and want it to restart mysqld_safe so it
 will re-read my.cnf. How do I do this?

/usr/local/etc/rc.d/mysql-server.sh restart

-- 
Dan Nelson
[EMAIL PROTECTED]

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

Logging failed attempts

[MrExecutive]

Hello Guys,
I am using mySQL 4.1 for windows. How do i go about logging all failed 
login attempts?

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

Re: Exporting Data via Command Line

[Eric Bergen]

Lets not forget about mysqlhotcopy. 

-Eric


On Fri, 4 Mar 2005 13:42:48 -0500, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
 MrExecutive [EMAIL PROTECTED] wrote on 03/04/2005 01:12:02 PM:
 
  Hello Guys,
 
  I am a newbie to mySQL and im trying to export my database via the
  mySQL Command line but i cant seem to find any good documentation on how
 
  to do this. More articles are telling me to use phpAdmin or some other
  gui tool. I would like to learn the commands anyone has a good doc on
 this?
 
 
  Thanks
 
 
 Try reading up on these commands:
 
 (from a command shell prompt) mysqldump
 http://dev.mysql.com/doc/mysql/en/mysqldump.html
 
 (from within a MySQL client) SELECT ... INTO OUTFILE
 http://dev.mysql.com/doc/mysql/en/select.html
 
 Shawn Green
 Database Administrator
 Unimin Corporation - Spruce Pine
 


-- 
Eric Bergen
[EMAIL PROTECTED]
http://www.ebergen.net

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

Re: Read / Write statistics

[Gleb Paharenko]

Hello.



 however, today one of them (Bytes_sent) reset itself

 to 0...



This variable changes only in one place (sql/net_serv.cc)

and has an unsigned long type. I think it has just overhelmed.

You may use some network accounting tool to calculate the

traffic.







E SA [EMAIL PROTECTED] wrote:

 Hi,

 

 I am trying to obtain read/write statistics from our

 database (4.0.21).  I started by using the

 Bytes_received and Bytes_sent from show status; 

 however, today one of them (Bytes_sent) reset itself 

 to 0...

 

 Which really messes up my calculations...

 

 I need this data... Is there any other way to obtain

 it?

 

 I was doing:

 

 $Total_bytes = ( $Bytes_received + $Bytes_sent );

 $Percentage_received = ( ( $Bytes_received * 100 ) / \

 $Total_bytes ));

 

 However, it won't work when the counter clears itself.

 

 Is there a way to se that value to unsigned bigint?

 

 Is there any other way?

 

 Beforehand, thank you for your help!

 

 





 __ 

 Celebrate Yahoo!'s 10th Birthday! 

 Yahoo! Netrospective: 100 Moments of the Web 

 http://birthday.yahoo.com/netrospective/

 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   ___/   www.mysql.com




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

Re: Console aliases

[Gleb Paharenko]

Hello.



No. There are only short forms of each command. See:

  http://dev.mysql.com/doc/mysql/en/mysql-commands.html









jacob martinson [EMAIL PROTECTED] wrote:

 Is it possible to setup aliases in the mysql console to cut down on

 typing, i.e. instead of having to type out show tables I can just

 type \dt as in postgresql?

 

 Thanks,

 Jacob

 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   ___/   www.mysql.com




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

Re: MySQL 4.1.10 Crash

[Gleb Paharenko]

Hello.



 0x808d8d7 handle_segfault + 423

 0x82e68d8 pthread_sighandler + 184

 0x80929a3 ip_to_hostname__FP7in_addrPUi + 483

 0x809af45 check_connection__FP3THD + 213



Looks like it is a problem with gethostbyaddr(), which

solved in your version according to the manual at:

  http://dev.mysql.com/doc/mysql/en/linux-x86.html



As said at this page you should launch mysqld with --thread-stack=192K

(you may try a greater value, because 192K is default in 4.1.10).

If it doesn't help you may run mysqld with --skip-name-resolve. See:

  http://dev.mysql.com/doc/mysql/en/dns.html





  

Batara Kesuma [EMAIL PROTECTED] wrote:

 Hi,

 

 I use MySQL 4.1.10, and it crashes about once a day. I posted about this

 bug two days ago, but I forgot to put the stack trace result there,

 sorry for that. Now I put the stack trace result below. 

 

 My kernel is 2.6.10, RAM is 4 GB. Single CPU with HT (shows up as 2 CPU

 on linux). The MySQL that I use is the official RPM from mysql.com.

 

 Here is what I found in my error log:

 

 --- start: hostname.err 

 mysqld got signal 11;

 This could be because you hit a bug. It is also possible that this

 binary

 or one of the libraries it was linked against is corrupt, improperly

 built,

 or misconfigured. This error can also be caused by malfunctioning

 hardware.

 We will try our best to scrape up some info that will hopefully help

 diagnose

 the problem, but since we have already crashed, something is definitely

 wrong

 and this may fail.

 

 key_buffer_size=33554432

 read_buffer_size=2093056

 max_used_connections=80

 max_connections=400

 threads_connected=4

 It is possible that mysqld could use up to

 key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections

 = 3209660 K

 bytes of memory

 Hope that's ok; if not, decrease some variables in the equation.

 

 thd=0x56000440

 Attempting backtrace. You can use the following information to find out

 where mysqld died. If you see no messages after this, something went

 terribly wrong...

 Cannot determine thread, fp=0xbfe5f5a8, backtrace may not be correct.

 Stack range sanity check OK, backtrace follows:

 0x808d8d7

 0x82e68d8

 0x80929a3

 0x809af45

 0x809b709

 0x82e408c

 0x830d95a

 New value of fp=(nil) failed sanity check, terminating stack trace!

 Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and

 follow instructions on how to resolve the stack trace. Resolved

 stack trace is much more helpful in diagnosing the problem, so please do

 resolve it

 Trying to get some variables.

 Some pointers may be invalid and cause the dump to abort...

 thd-query at (nil)  is invalid pointer

 thd-thread_id=7005520

 The manual page at http://www.mysql.com/doc/en/Crashing.html contains

 information that should help you find out what is causing the crash.

 --- end: hostname.err ---

 

 And here is the stack trace result:

 

 [EMAIL PROTECTED] ~]# resolve_stack_dump -s mysqld.sym -n mysql.stack1

 0x808d8d7 handle_segfault + 423

 0x82e68d8 pthread_sighandler + 184

 0x80929a3 ip_to_hostname__FP7in_addrPUi + 483

 0x809af45 check_connection__FP3THD + 213

 0x809b709 handle_one_connection + 297

 0x82e408c pthread_start_thread + 220

 0x830d95a thread_start + 4

 

 Does anyone have the same problem? How do I fix this? Thank you very

 much.

 

 Regards,

 --bk

 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   ___/   www.mysql.com




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

Re: MySQL hangs

[Gleb Paharenko]

Hello.



In your file you have lots of threads in Closing_tables

state and only seven queries which make updates.  Means 

that the thread is flushing the changed table data to disk

and closing the used tables. This should be a fast operation.

If not, then you should verify that you don't have a full

disk and that the disk is not in very heavy use.







Marcin Lewandowski [EMAIL PROTECTED] wrote:

 Hi,

 

 I've got webserver with php and mysql-4.0.22

 

 There I've got large phpbb2. Sometimes, server's system load rapidly 

 grows, and mysql are locked. Normally, there are 7-10 mysql processes, 

 at this strange situation, there are 30-40.

 

 I don't know what can make such big system load in time of 1 minute.

 

 Maybe this could help: http://saepia.net/temp/mysql.txt

 

 It's 'show processlist' when mysql is locked.

 

 Mysql load is about 50 queries per second. There are mostly myisam 

 tables and they are stored on ide100 drive with dma enabled.

 

 Or maybe it's DoS?

 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   ___/   www.mysql.com




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

RE: insert data

[John Trammell]

Gerals Preston wrote:
[snip]
 my $sth = $dbh-prepare( $sql ) or die $dbh-errstr if $dbh-err;
[snip]

Regardless of other problems you may be having, I think you're not
doing what you want to do here.  How about instead:

my $sth = $dbh-prepare($sql);
$sth || die Error preparing sth from '$sql': , $dbh-errstr;

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

Questions regarding INNODB

[Michael Satterwhite]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
I just found out that the hosting company we're using has disabled
INNODB support in their MySQL configuration. I'm a bit upset that they
don't document crippling the server anywhere, but that's neither here
nor there.
When I asked them about this, I got the following reply:
InnoDB doesn't provide a mechanism to track disk quota and isn't
suitable for a shared server environment - structures etc all reside in
a shared area.
How many of you agree or disagree with their reply? Do you know hosting
companies that *DO* provide INNODB support? If so, obviously they don't
consider innodb to be unusable in a shared environment. How much
validity is there to their excuse for not providing the support? Other
comments welcome as well.
tia
- ---Michael
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.0 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFCKONcjeziQOokQnARAoQoAJ9RiwoxNaTUo9FOQyEwk2pRwpkamACfeoSA
vFJLop1ByO2Aj1vMs5RDZHI=
=YXbN
-END PGP SIGNATURE-
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Illegal mix of collations - new twist on a familiar problem...

[Stembridge, Michael]

When running this simple query:   
SELECT fileid FROM test WHERE ecn='0'


MySQL yields this error:
#1267 - Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and
(utf8_general_ci,COERCIBLE) for operation '='


The table collation is latin1_swedish_ci.  

I ran SHOW CREATE TABLE fileid and found this:  DEFAULT CHARSET=latin1 at
the end. 

Here is the table layout: 

fileid  int(10)
datereceiveddate
scn varchar(11) latin1_swedish_ci
ecn varchar(11) latin1_swedish_ci


Here is the problem:  The table collation was once utf8_general_ci (due to
mistake), it was switched back to latin1_swedish_ci.   Ever since then I've
had this error.  

Suggestions for getting the table back in line?  

Thanks, 
Michael

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

Re: insert data

[Michael Stassen]

Gerald Preston wrote:
David,
I read them and installed 4.1.10 and I am back to square one:
I used the following code:
snip
  my $dbh=DBI-connect( 'dbi:mysql:club', 'gjwpp88', 'x' ) or die
\n$DBI::errstr\n;
You do not specify the host, so you are connecting to the default, which is 
localhost.  Hence, you must be able to log in as [EMAIL PROTECTED].

snip
I have done the following with no errors!
GRANT ALL PRIVILEGES ON club.* TO 'gjwpp88'@'local_host';
Here, however, you create the user [EMAIL PROTECTED].  That's not the 
same thing.  You need to use

  GRANT ALL PRIVILEGES ON club.* TO 'gjwpp88'@'localhost';
See?  No underscore.
Similarly, you need to drop the underscore (localhost, not local_host) in 
all of the following.

SET PASSWORD FOR 'gjwpp88'@'local_host' = PASSWORD('x');
UPDATE mysql.user SET Password = OLD_PASSWORD('x')
WHERE Host = 'local_host' AND User = 'gjwpp88';
SET PASSWORD FOR 'gjwpp88'@'local_host' = OLD_PASSWORD('x');
SELECT 'local_host', 'gjwpp88', Password FROM mysql.user
WHERE LENGTH('x')  16;
FLUSH PRIVILEGES;
I am still getting the same error I listed above.
Jerry
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

mysqldump vs. 'mysql [db] file.sql'

[Stembridge, Michael]

mysqldump vs. 'mysql [db]  file.sql'

I stumbled across the latter method early on but notice that most folks
suggest using mysqldump instead; are there performance benefits with using
mysqldump, or some other reason?   

The same question could be applied to using mysqlimport vs. 'mysql [db] 
tablename.sql'.  

Thoughts? 

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

Re: Questions regarding INNODB

[Gary Richardson]

You could configure per table table spaces.. I suppose that would be a
little more trackable..


On Fri, 04 Mar 2005 16:38:20 -0600, Michael Satterwhite
[EMAIL PROTECTED] wrote:
 -BEGIN PGP SIGNED MESSAGE-
 Hash: SHA1
 
 I just found out that the hosting company we're using has disabled
 INNODB support in their MySQL configuration. I'm a bit upset that they
 don't document crippling the server anywhere, but that's neither here
 nor there.
 
 When I asked them about this, I got the following reply:
 
 InnoDB doesn't provide a mechanism to track disk quota and isn't
 suitable for a shared server environment - structures etc all reside in
 a shared area.
 
 How many of you agree or disagree with their reply? Do you know hosting
 companies that *DO* provide INNODB support? If so, obviously they don't
 consider innodb to be unusable in a shared environment. How much
 validity is there to their excuse for not providing the support? Other
 comments welcome as well.
 
 tia
 - ---Michael
 -BEGIN PGP SIGNATURE-
 Version: GnuPG v1.4.0 (GNU/Linux)
 Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
 
 iD8DBQFCKONcjeziQOokQnARAoQoAJ9RiwoxNaTUo9FOQyEwk2pRwpkamACfeoSA
 vFJLop1ByO2Aj1vMs5RDZHI=
 =YXbN
 -END PGP SIGNATURE-
 
 --
 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]

Re: insert data

[Michael Stassen]

Right.  First, I think the logic is flawed.  We should successfully 
prepare() or die.  Period.  If the call to prepare() failed ($sth is undef), 
we should not making dying conditional on yet another value.

More to the point, this line is actually the cause of the problem.  (Sorry I 
didn't see it earlier.)  You've run into the precedence rules:

  my $sth = $dbh-prepare( $sql ) or die $dbh-errstr if $dbh-err;
is read as
  (my $sth = $dbh-prepare( $sql ) or die $dbh-errstr) if $dbh-err;
That is, it is equivalent to
  if ($dbh-err)
  {
$sth = $dbh-prepare( $sql ) or die $dbh-errstr;
  }
Since the connect succeeded, $dbh-err is undef, so we never even call 
prepare!  Hence, $sth is undef when we get to execute, and you get the error 
message.  I expect this is what Joe (John Doe) was trying to tell us earlier.

The simplest solution would be to drop the if $dbh-err.  That is, change to
  my $sth = $dbh-prepare( $sql ) or die $dbh-errstr;
John's suggestion (below) is better still, as it adds helpful detail to the 
error message when there is one (though I don't see the need to make it a 
separate line of code).

Michael
John Trammell wrote:
Gerald Preston wrote:
[snip]
my $sth = $dbh-prepare( $sql ) or die $dbh-errstr if $dbh-err;
[snip]
Regardless of other problems you may be having, I think you're not
doing what you want to do here.  How about instead:
my $sth = $dbh-prepare($sql);
$sth || die Error preparing sth from '$sql': , $dbh-errstr;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysqldump vs. 'mysql [db] file.sql'

[Dan Nelson]

In the last episode (Mar 04), Stembridge, Michael said:
 mysqldump vs. 'mysql [db]  file.sql'
 
 I stumbled across the latter method early on but notice that most
 folks suggest using mysqldump instead; are there performance benefits
 with using mysqldump, or some other reason?

The latter can't generate a dump the way mysqldump can.  You can get
the definition with show create table, but there's no way to
automatically generate INSERT statements with correctly-escaped output.
 
 The same question could be applied to using mysqlimport vs. 'mysql [db] 
 tablename.sql'.  

mysqlimport is basically a wrapper around LOAD DATA INFILE.

-- 
Dan Nelson
[EMAIL PROTECTED]

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

Re: Console aliases

[jacob martinson]

Does anyone know if there are any 3rd party shells that can connect to
a mysql server that support aliases, or have any features that can
make working from the console faster?

Thanks!

-Jacob


On Fri, 4 Mar 2005 11:59:56 -0600, jacob martinson
[EMAIL PROTECTED] wrote:
 Is it possible to setup aliases in the mysql console to cut down on
 typing, i.e. instead of having to type out show tables I can just
 type \dt as in postgresql?
 
 Thanks,
 Jacob


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

Re: Questions regarding INNODB

[Heikki Tuuri]

Hi!
- Original Message - 
From: Gary Richardson [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Saturday, March 05, 2005 1:34 AM
Subject: Re: Questions regarding INNODB


You could configure per table table spaces.. I suppose that would be a
little more trackable..
yes. Hosting companies restrict MyISAM database sizes by setting limits on 
individual database directory sizes. With the my.cnf option in MySQL-4.1:

innodb_file_per_table
they can now do the same for InnoDB type tables.
Best regards,
Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM 
tables
http://www.innodb.com/order.php

Order MySQL Network from http://www.mysql.com/network/

On Fri, 04 Mar 2005 16:38:20 -0600, Michael Satterwhite
[EMAIL PROTECTED] wrote:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
I just found out that the hosting company we're using has disabled
INNODB support in their MySQL configuration. I'm a bit upset that they
don't document crippling the server anywhere, but that's neither here
nor there.
When I asked them about this, I got the following reply:
InnoDB doesn't provide a mechanism to track disk quota and isn't
suitable for a shared server environment - structures etc all reside in
a shared area.
How many of you agree or disagree with their reply? Do you know hosting
companies that *DO* provide INNODB support? If so, obviously they don't
consider innodb to be unusable in a shared environment. How much
validity is there to their excuse for not providing the support? Other
comments welcome as well.
tia
- ---Michael
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.0 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFCKONcjeziQOokQnARAoQoAJ9RiwoxNaTUo9FOQyEwk2pRwpkamACfeoSA
vFJLop1ByO2Aj1vMs5RDZHI=
=YXbN
-END PGP SIGNATURE-
--
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]


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