select statement for syslog like grouping of messages

[Andreas Heckwolf]

Dear all,

I do have a logging table with the fields: id, error_id, logtime and message. 
I am trying to create a consolidated list of log messages, syslog like, where 
consecutive, identical messages are combined and replaced by for example:
Last message repeated 5 times.

Anyone done this? Any hints would be greatly appreciated. I am using MySql 4.0.16.

Cheers,

Andreas

-- 
\_
Dipl.-Ing. Andreas Heckwolf  Voice : +358 40 5847 445
  Mobliz Ltd  Fax: +358 9 2517 2977
  Tekniikantie 12  02150 Espoo  Finland




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

User Conference Presentations

[Ed Reed]

Where's the presentations? They were supposed to be on the website at
the end of last week.

Thanks

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

Re: [PHP-DB] lamer noob with repeat question

[Dan Bowkley]

I've got it currently as method=get; I thought it had to be get in order to
manipulate variables in the URL, as I've got them...have I thought wrong?


- Original Message - 
From: Ross Honniball [EMAIL PROTECTED]
To: Dan Bowkley [EMAIL PROTECTED]
Sent: Monday, May 03, 2004 11:53 PM
Subject: Re: [PHP-DB] lamer noob with repeat question


 Have you tried putting in a

 form method=post action=script-name.php

 in your html?

 I'm haven't read all of your email but this is what I use and your html
 doesn't seem to have this. I think php needs  the 'action=' to know what
to
 do when the user submits the form.

 At 04:37 PM 4/05/2004, you wrote:
 Anyone?
 - Original Message -
 From: Dan Bowkley [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Sent: Sunday, May 02, 2004 1:21 AM
 Subject: [PHP-DB] lamer noob with repeat question
 
 
   Hello everyone,
  
   I've been working on (read:tearing my hair out over) my mom's website
for
   some time now.  Specifically, I'm trying to get her work order
database up
   and running.
  
   The basic idea is this: you start out adding a new record by going to
   add.php.  It sees that you've not done anything yet and thus presents
you
   with a form to fill out.  That form gets submitted to add.php, which
sees
   that you're adding something.  It checks for a duplicate work order
number
   (and eventually other errors) and then either adds the stuff you
submitted
   into the DB, or pops an error and presents the form again.
  
   Alas, it does nothing.
  
   When you initially load the page, it works okay, sensing that you've
not
 yet
   done anything and displaying the form.  But when you submit data, it
spits
   out naught more than a blank page, and doesn't add anything to the
 database.
  
   Damned lazy script.
  
  
   What I've got so far is this:
  
   html
   headtitleThe Board Lady - Work Order Database 0.1a/title/head
   body
   ?php
   define ('DB_USER', 'user');
   define ('DB_PASSWORD', '');
   define ('DB_HOST', 'localhost');
   define ('DB_NAME', 'boardlady');
   $dbc = mysql_connect (DB_HOST, DB_USER, DB_PASSWORD) OR die ('Could
not
   connect to database: ' . mysql_error());
   @mysql_select_db (DB_NAME) OR die ('Could not connect to database: ' .
   mysql_error());
   $page_req=$HTTP_GET_VARS['action'];
   if ($page_req == ) {$page_req=0;}
   if ($page_req == 0) {
   echo SWORD data entrybr\n;
   echo form action=\add.php\ method=\get\;
   echo Work Order #: input type=\text\ name=\wo_num\br\n;
   echo Customer Name: input type=\text\ name=\name\ Phone: input
   type=\text\ name=\phone\br\n;
   echo Email Addy: input type=\text\ name=\email\ Date In: input
   type=\text\ name=\date\br\n;
   echo Board Type and SN: input type=\text\ name=\board_type\
Last 3
 of
   SN: input type=\text\ name=\last_three\br\n;
   echo Weight In: input type=\text\ name=\weight_in\ Weight Out:
 input
   type=\text\ name=\weight_out\br\n;
   echo input type=\hidden\ name=\action\ value=\1\\n;
   echo INPUT type=\submit\ value=\Add Work Order\ INPUT
   type=\reset\br\n;
   }
   if ($page_req == 1) {
   $wo_num=$HTTP_GET_VARS['wo_num'];
   $name=$HTTP_GET_VARS['name'];
   $phone=$HTTP_GET_VARS['phone'];
   $email=$HTTP_GET_VARS['email'];
   $date=$HTTP_GET_VARS['date'];
   $board_type=$HTTP_GET_VARS['board_type'];
   $last_three=$HTTP_GET_VARS['last_three'];
   $weight_in=$HTTP_GET_VARS['weight_in'];
   $weight_out=$HTTP_GET_VARS['weight_out'];
   $query_testingforadupe = SELECT job_no FROM boards WHERE job_no ==
 $job_no
   ORDER BY job_no ASC;
   $result_testingforadupe = @mysql_query ($query_testingforadupe);
   if ($result_testingforadupe) {
   echo That's a duplicate work order number, you ditz. Try again, this
time
   without screwing it all up.brbr\n;
   echo form action=\add.php\ method=\get\;
   echo Work Order #: input type=\text\ name=\wo_num\br\n;
   echo Customer Name: input type=\text\ name=\name\ Phone: input
   type=\text\ name=\phone\br\n;
   echo Email Addy: input type=\text\ name=\email\ Date In: input
   type=\text\ name=\date\br\n;
   echo Board Type and SN: input type=\text\ name=\board_type\
Last 3
 of
   SN: input type=\text\ name=\last_three\br\n;
   echo Weight In: input type=\text\ name=\weight_in\ Weight Out:
 input
   type=\text\ name=\weight_out\br\n;
   echo input type=\hidden\ name=\action\ value=\1\\n;
   echo INPUT type=\submit\ value=\Add Work Order\ INPUT
   type=\reset\br\n;
   }
   else {
   $query_insert = INSERT INTO boards (wo_num, name, phone, email, date,
   board_type, last_three, weight_in, weight_out) VALUES (\'$wo_num\',
   \'$name\', \'$phone\', \'$email\', \'$date\', \'$board_type\',
   \'$last_three\', \'$weight_in\', \'$weight_out\');
   $result_insert = @mysql_query ($query_insert);
   if ($result_insert == ) {
   echo input type=\hidden\ name=\action\ value=\0\\n;
   echo INPUT type=\submit\ value=\Continue\\n;
   }
   else {echo OOPS! Your programmer is an idiot!\n;}
   }}
   mysql_close();
   ?
   /body
   

Export query to text file

[Yingyos]

Hi,

I have MySQL 4.0.17 on Windows XP.
I use SELECT ... INTO OUTFILE print out query to text file.
If i write this command.
mysqlSELECT * from tbl1 INTO OUTFILE C:\\Query\\sample1.txt;

MySQL reponse OK.But i change command.

mysqlSELECT now() INTO OUTFILE C:\\Query\\sample2.txt; 

MySQL response by exit from console.

How's to use SELECT ... INTO OUTFILE with datetime or another function.

Thank for the reply,
Yingyos Santiprasert




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

Stupid mistake

[Niels Riis Kristensen]

Hi

I have made an horrendous stupid mistake. In migrating from OSX 10.3 to 
OSX Server 10.3 I backed up all the data, but forgot to dump my sql 
databases. I have the data, but simply copy paste into the relevant 
directories doesn't work.

Please help!

Niels Riis Kristensen
([EMAIL PROTECTED])
NRK Group
- Electronic Music Engraving
- Dynamic Web design
- E-Lists hosting
---
Send money safely over the Internet!
Click this link for more information: 
https://www.paypal.com/refer/pal=EAJLSE5TQELFC

smime.p7s
Description: S/MIME cryptographic signature

Re: [PHP-DB] lamer noob with repeat question

[Dan Bowkley]

I pruned it all down ti this:

html
headtitleThe Board Lady - Work Order Database 0.1a/title/head
body
?php
define ('DB_USER', '');
define ('DB_PASSWORD', '');
define ('DB_HOST', '');
define ('DB_NAME', '');
$dbc = mysql_connect (DB_HOST, DB_USER, DB_PASSWORD) OR die ('Could not
connect to database: ' . mysql_error());
@mysql_select_db (DB_NAME) OR die ('Could not connect to database: ' .
mysql_error());
$page_req=$HTTP_GET_VARS['action'];
if ($page_req == ) {
echo SWORD data entrybr\n;
echo form action=\add.php\ method=\get\;
echo Work Order #: input type=\text\ name=\wo_num\br\n;
echo Customer Name: input type=\text\ name=\name\ Phone: input
type=\text\ name=\phone\br\n;
echo Email Addy: input type=\text\ name=\email\ Date In: input
type=\text\ name=\date\br\n;
echo Board Type and SN: input type=\text\ name=\board_type\ Last 3 of
SN: input type=\text\ name=\last_three\br\n;
echo Weight In: input type=\text\ name=\weight_in\ Weight Out: input
type=\text\ name=\weight_out\br\n;
echo input type=\hidden\ name=\action\ value=\1\\n;
echo INPUT type=\submit\ value=\Add Work Order\ INPUT
type=\reset\br\n;
}
if ($page_req == 1) {
$wo_num=$HTTP_GET_VARS['wo_num'];
$name=$HTTP_GET_VARS['name'];
$phone=$HTTP_GET_VARS['phone'];
$email=$HTTP_GET_VARS['email'];
$date=$HTTP_GET_VARS['date'];
$board_type=$HTTP_GET_VARS['board_type'];
$last_three=$HTTP_GET_VARS['last_three'];
$weight_in=$HTTP_GET_VARS['weight_in'];
$weight_out=$HTTP_GET_VARS['weight_out'];
$query_insert = INSERT INTO boards (wo_num, name, phone, email, date,
board_type, last_three, weight_in, weight_out) VALUES ('$wo_num', '$name',
'$phone', '$email', '$date', '$board_type', '$last_three', '$weight_in',
'$weight_out');;
echo $query_insertbr\n$result_insertbr\n;
$result_insert = @mysql_query ($query_insert) or die(you suck!
$mysql_error);
}
mysql_close();
?
/body
/html

Which I figured would reduce the number of things that can pop an error to a
minimum.  When I post something, it echoes a rather beautiful mysql insert
statement...then tells me that I suck.  But it doesn't tell me why I
suck...$mysql_error is empty.  And it's not sticking anything into my
database.

Arrrg.



- Original Message - 
From: Dan Bowkley [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, May 04, 2004 12:18 AM
Subject: Re: [PHP-DB] lamer noob with repeat question


 I've got it currently as method=get; I thought it had to be get in order
to
 manipulate variables in the URL, as I've got them...have I thought wrong?


 - Original Message - 
 From: Ross Honniball [EMAIL PROTECTED]
 To: Dan Bowkley [EMAIL PROTECTED]
 Sent: Monday, May 03, 2004 11:53 PM
 Subject: Re: [PHP-DB] lamer noob with repeat question


  Have you tried putting in a
 
  form method=post action=script-name.php
 
  in your html?
 
  I'm haven't read all of your email but this is what I use and your html
  doesn't seem to have this. I think php needs  the 'action=' to know what
 to
  do when the user submits the form.
 
  At 04:37 PM 4/05/2004, you wrote:
  Anyone?
  - Original Message -
  From: Dan Bowkley [EMAIL PROTECTED]
  To: [EMAIL PROTECTED]
  Sent: Sunday, May 02, 2004 1:21 AM
  Subject: [PHP-DB] lamer noob with repeat question
  
  
Hello everyone,
   
I've been working on (read:tearing my hair out over) my mom's
website
 for
some time now.  Specifically, I'm trying to get her work order
 database up
and running.
   
The basic idea is this: you start out adding a new record by going
to
add.php.  It sees that you've not done anything yet and thus
presents
 you
with a form to fill out.  That form gets submitted to add.php, which
 sees
that you're adding something.  It checks for a duplicate work order
 number
(and eventually other errors) and then either adds the stuff you
 submitted
into the DB, or pops an error and presents the form again.
   
Alas, it does nothing.
   
When you initially load the page, it works okay, sensing that you've
 not
  yet
done anything and displaying the form.  But when you submit data, it
 spits
out naught more than a blank page, and doesn't add anything to the
  database.
   
Damned lazy script.
   
   
What I've got so far is this:
   
html
headtitleThe Board Lady - Work Order Database
0.1a/title/head
body
?php
define ('DB_USER', 'user');
define ('DB_PASSWORD', '');
define ('DB_HOST', 'localhost');
define ('DB_NAME', 'boardlady');
$dbc = mysql_connect (DB_HOST, DB_USER, DB_PASSWORD) OR die ('Could
 not
connect to database: ' . mysql_error());
@mysql_select_db (DB_NAME) OR die ('Could not connect to database: '
.
mysql_error());
$page_req=$HTTP_GET_VARS['action'];
if ($page_req == ) {$page_req=0;}
if ($page_req == 0) {
echo SWORD data entrybr\n;
echo form action=\add.php\ method=\get\;
echo Work Order #: input type=\text\ name=\wo_num\br\n;
echo Customer Name: input 

sp_spaceused

[nico ghilardi]

bonjour,
 
voici mon problème:
 
Systeme: windows NT4 server
   Sql server 6.5
 
Application: Deux bases SQL utilisé: une pour les bases
  une pour les données
 
Diag:
Apres un sp_spaceused sur la base des données,
il apparait que 160 Mo sont réservés et donc bloqué.
 
Action:
-Sp_spaceused sur chacune des tables de la base, je ne retrouve pas dans les tables, 
celle qui se reserve les 160 MO
-Truncate de la base,NOk le problème reste idem
-Lors d'un edit sur la base,j'obtient 0 MO disponnible pour les data et les logs et 
j'obtient -160 MO avec la commande sp_spaceused
 
Ma question:
-Connaissez vous une commande me permettant de savoir exactement quel table se reserve 
ces 160 MO vaquant?
-Ou une méthode pour liberer cet espace sans avoir à recreer les lecteurs?
 
Merci pour votre reponse
Cordialement
 
[EMAIL PROTECTED]




-
Yahoo! Mail : votre e-mail personnel et gratuit qui vous suit partout !
Créez votre Yahoo! Mail

Dialoguez en direct avec vos amis grâce à Yahoo! Messenger !

count() on multiple similar tables?

[Ken Gieselman]

Heya folks --

Trying to come up with a way to count across multiple tables, and failing
miserably.  I need a simple way, preferably better than looped queries, of
summing the number of rows in multiple tables.

Example:

I have multiple tables with the same column layout, due to the amount of data
expected to land in each one, broken up by year/month.  This is simplified
some, but should serve to illustrate the issue.

create table info_2004_03 (
  itemID   integer auto_increment NOT NULL,
  eventID  integer NOT NULL,
  eventNamechar(40),
  primary key (itemID)
);

create table info_2004_04 (
  itemID   integer auto_increment NOT NULL,
  eventID  integer NOT NULL,
  eventNamechar(40),
  primary key (itemID)
);

I need to get a total number of itemIDs over all the info_ tables.  I could just
query each of the tables individually and add the results, but looking for a
more graceful way of doing it, hopefully.

Thanks in advance!

ken
===
  Diplomacy is the weapon of the Civilized Warrior
- Hun, A.T.
 
Ken Gieselman   [EMAIL PROTECTED]
System Administratorhttp://www.endlessknot.com/~ken
Endlessknot Communications   http://www.endlessknot.com
===



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

Re: count() on multiple similar tables?

[Diana Soares]

You may use MERGE TABLES:

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

-- 
Diana Soares

On Tue, 2004-05-04 at 10:08, Ken Gieselman wrote:
 Heya folks --
 
 Trying to come up with a way to count across multiple tables, and failing
 miserably.  I need a simple way, preferably better than looped queries, of
 summing the number of rows in multiple tables.
 
 Example:
 
 I have multiple tables with the same column layout, due to the amount of data
 expected to land in each one, broken up by year/month.  This is simplified
 some, but should serve to illustrate the issue.
 
 create table info_2004_03 (
   itemID   integer auto_increment NOT NULL,
   eventID  integer NOT NULL,
   eventNamechar(40),
   primary key (itemID)
 );
 
 create table info_2004_04 (
   itemID   integer auto_increment NOT NULL,
   eventID  integer NOT NULL,
   eventNamechar(40),
   primary key (itemID)
 );
 
 I need to get a total number of itemIDs over all the info_ tables.  I could just
 query each of the tables individually and add the results, but looking for a
 more graceful way of doing it, hopefully.
 
 Thanks in advance!
 
 ken
 ===
   Diplomacy is the weapon of the Civilized Warrior
 - Hun, A.T.
  
 Ken Gieselman   [EMAIL PROTECTED]
 System Administratorhttp://www.endlessknot.com/~ken
 Endlessknot Communications   http://www.endlessknot.com
 ===



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

Re: count() on multiple similar tables?

[Ken Gieselman]

Great!  Thanks for the quick pointer!
ken

Quoting Diana Soares [EMAIL PROTECTED]:

 You may use MERGE TABLES:
 
 http://dev.mysql.com/doc/mysql/en/MERGE.html
 
 -- 
 Diana Soares
 
 On Tue, 2004-05-04 at 10:08, Ken Gieselman wrote:
  Heya folks --
  
  Trying to come up with a way to count across multiple tables, and failing
  miserably.  I need a simple way, preferably better than looped queries, of
  summing the number of rows in multiple tables.
  
  Example:
  
  I have multiple tables with the same column layout, due to the amount of
 data
  expected to land in each one, broken up by year/month.  This is simplified
  some, but should serve to illustrate the issue.
  
  create table info_2004_03 (
itemID   integer auto_increment NOT NULL,
eventID  integer NOT NULL,
eventNamechar(40),
primary key (itemID)
  );
  
  create table info_2004_04 (
itemID   integer auto_increment NOT NULL,
eventID  integer NOT NULL,
eventNamechar(40),
primary key (itemID)
  );
  
  I need to get a total number of itemIDs over all the info_ tables.  I could
 just
  query each of the tables individually and add the results, but looking for
 a
  more graceful way of doing it, hopefully.
  
  Thanks in advance!
  
  ken
 
 ===
Diplomacy is the weapon of the Civilized Warrior
  - Hun, A.T.
   
  Ken Gieselman  
 [EMAIL PROTECTED]
  System Administrator   
 http://www.endlessknot.com/~ken
  Endlessknot Communications  
 http://www.endlessknot.com
 
 ===
 
 
 


===
  Diplomacy is the weapon of the Civilized Warrior
- Hun, A.T.
 
Ken Gieselman   [EMAIL PROTECTED]
System Administratorhttp://www.endlessknot.com/~ken
Endlessknot Communications   http://www.endlessknot.com
===



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

Re: Query Log

[Egor Egorov]

Lou Olsten [EMAIL PROTECTED] wrote:
 I'm pretty sure that the answer to this is No, you cannot but I figured I'd check
 anyway...

 As I go back through my query log, I'd like to know the user that issued the 
 statement. 
 If the user is still connected, I can cross reference it with the SHOW PROCESSLIST 
 ID,
 but if they have signed off, is there a way to get the user then?

If you look in the general query log file you can see Id column where thread id is 
specified and username and host in the Argument column.
For update log and slow query log use --log-long-format option.



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [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: Stupid mistake

[Egor Egorov]

Niels Riis Kristensen [EMAIL PROTECTED] wrote:
 
 I have made an horrendous stupid mistake. In migrating from OSX 10.3 to 
 OSX Server 10.3 I backed up all the data, but forgot to dump my sql 
 databases. I have the data, but simply copy paste into the relevant 
 directories doesn't work.
 

How exactly did you make backup of the data? Did you just copy database directories? 
If so did you get any error message when you start MySQL server?



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [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: Accessing DBMS remotely: MySQL? FireBird?

[David Griffiths]

Look at the documentation regarding the users table in the mysql database.

You can specify an ip address (or subset of an ip address) on which users
are allowed to connect; others are rejected, via the hosts column.

IE if you specify 192.168.1.%, any user on the 192.168.1 portion of the
network (ie 192.168.1.1, 192.168.1.2, 192.168.1.3, etc up to 192.168.1.255)
are allowed to connect. Since you are connecting via a vpn, it sounds like
an IP address will be handed out by the VPN, and you should be able to
establish some security with your connections. You can have different rules
for different users and hosts. IE you can set it up so that the Fred user
on localhost doesn't need a password, but Susan on 192.168.1.% does. And you
can also set it up so that Susan can't modify any data in Table A (just read
data, not write), etc. The permissions setup in MySQL is really good; very
flexible.

Don't screw up the users table, though, as you might find yourself unable to
connect from any machine. Look at the MySQL Admin tool (or some other tool).
I believe that it has a GUI that lets you add/edit/remove users painlessly
(and reduces the risk of screw ups).

This list is not a great place to ask about Firebird.

David.


- Original Message -
From: The masked marvel [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Saturday, May 01, 2004 4:52 PM
Subject: Accessing DBMS remotely: MySQL? FireBird?


 Hi,

 Some of our customers have remote offices. I was wondering if it'd be safe
 to have a DBMS running at their central office, and have our client
 application running on hosts in the branches connect to it through a VPN
 via the Net?

 What happens if the connection goes south while a branch office was making
 changes? Does the DBMS just rollbacks changes automatically after a
time-out?

 Should we set up some kind of replication instead?

 Also, are there compeling reasons to go for Firebird instead of MySQL? I
 don't know enough about the capabilities of each DBMS today to make an
 educated choice.

 Thank you for any tip
 Fred.


 --
 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]

Unofficial MySQL 5.0.x Windows Build?

[Martijn Tonies]

Hi there,

Does someone have a MySQL 5.0.x current source build
available for the Win32 platform?

I'm not able to build it myself, but I would like to give the
stored procedures (db specific, not the current MySQL
5.0 Alpha stuff) a try and make Database Workbench
MySQL 5.0 ready...

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL  MS SQL
Server.
Upscene Productions
http://www.upscene.com


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

Re: Documentation on character sets for version 4.0.17

[Victoria Reznichenko]

David Jourard [EMAIL PROTECTED] wrote:
 
 I got the 4.0.17 documentation but when it discusses character sets it 
 discusses this topic wrt 4.1
 
 Where can I find documentation specifically in regards to the production 
 version on how to work with,  store, and search asian character sets 
 specifically Japanese.
 

In version 4.0.17 you should specify character set with --default-character-set 
option. For Japanese language take a look at sjis and ujis character sets:
http://dev.mysql.com/doc/mysql/en/Character_sets.html


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [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: Reading MS Access tables from MySQL or Perl

[Victor Pendleton]

Are you looking for an interface to read MS Access other than the default
application and MS Query?

-Original Message-
From: Nik Belajcic
To: [EMAIL PROTECTED]
Sent: 5/3/04 9:52 PM
Subject: Reading MS Access tables from MySQL or Perl

Hello,

This is partially off-topic as I am looking at reading Access tables
from either Perl (preferred) or MySQL, but if anyone has any
suggestions,
I will be most grateful.

My first thoughts were to write a Perl script which will read 2 columns
from an MS Access table and load them into MySQL. It would be run at a
click of a button from a web interface to display updated MySQL tables. 

However, after few hours of googling it appears that this is easier
said than done due to ODBC and DSN quirks. Direct import into MySQL
requires external utility like DBTools which defeats desired automation
of the procedure. Is there a known best way to do this and has anyone
done something similar?

Thanks in advance for any hints.
-- 
Nik Belajcic [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]

RE: Query Log

[Victor Pendleton]

Which query log are you referring to? The user and the host are both logged
in the slow query and general logs.

-Original Message-
From: Lou Olsten
To: [EMAIL PROTECTED]
Sent: 5/3/04 4:59 PM
Subject: Query Log

I'm pretty sure that the answer to this is No, you cannot but I
figured I'd check anyway...

As I go back through my query log, I'd like to know the user that issued
the statement.  If the user is still connected, I can cross reference it
with the SHOW PROCESSLIST ID, but if they have signed off, is there a
way to get the user then?

Thanks,

Lou

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

RE: Export query to text file

[Victor Pendleton]

The `INTO OUTFILE` clause is expecting a table reference. An alternatvie is
mysql -uuser -N -eselect now()  sample2.txt

-Original Message-
From: Yingyos
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: 5/4/04 2:20 AM
Subject: Export query to text file

Hi,

I have MySQL 4.0.17 on Windows XP.
I use SELECT ... INTO OUTFILE print out query to text file.
If i write this command.

mysqlSELECT * from tbl1 INTO OUTFILE C:\\Query\\sample1.txt;

MySQL reponse OK.But i change command.

mysqlSELECT now() INTO OUTFILE C:\\Query\\sample2.txt; 

MySQL response by exit from console.

How's to use SELECT ... INTO OUTFILE with datetime or another function.


Thank for the reply,
Yingyos Santiprasert






-- 
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]

InnoDB - Foreign Key - Error 150.

[Tucker, Gabriel]

Hello All:

I am having a problem with both V4.0.16 and 4.0.18.  Let me explain:

I have a database with two InnoDB tables in v4.0.16.  I backup up this database every 
night using the following command:

mysqldump --allow-keywords --host=$HOST --flush-logs --disable-keys --opt 
--port=$1 --socket=/bb/bin
/mysql/sockets/mysql.sock.$1 --master-data --all-databases -u mysql -p  
/bb/bin/mysql/backups/archive_$1.sql

I went to restore it the other day and I got this error:

ERROR 1005 at line 20: Can't create table './fxprmet/cur_reject_tk_sum.frm' (errno: 
150)

I thought this might be a version issue, so I copied all the database files to another 
location, and started it using v4.0.18.  I then dropped the foreign key constraint (no 
errors) and recreated it (no errors).  I backed up the database.  And when I went to 
restore it, I got the same error.

The schema [after I deleted and re-added the constraint]:

CREATE TABLE cur_reject_tk_sum (
  cur_reject_tk_id int(11) NOT NULL auto_increment,
  contrib_swift_cd char(4) NOT NULL default '',
  object_id char(8) binary NOT NULL default '',
  tick_date date NOT NULL default '-00-00',
  object_type_cd smallint(6) NOT NULL default '0',
  num_received int(11) default NULL,
  num_rejected int(11) default NULL,
  num_spikes_contrib int(11) default NULL,
  num_spikes_compos int(11) default NULL,
  num_spread int(11) default NULL,
  num_filter int(11) default NULL,
  num_delayed int(11) default NULL,
  num_maybe int(11) default NULL,
  num_diff int(11) default NULL,
  num_bid_gt_ask int(11) default NULL,
  num_ask_no_bid int(11) default NULL,
  num_double_bid_ask int(11) default NULL,
  time_first_reject time default NULL,
  time_last_reject time default NULL,
  PRIMARY KEY  (cur_reject_tk_id),
  UNIQUE KEY xpkcur_reject_tk_s (cur_reject_tk_id),
  UNIQUE KEY xak1cur_reject_tk_ (contrib_swift_cd,object_id,tick_date),
  KEY xif1cur_reject_tk_ (object_type_cd),
  KEY xie1cur_reject_tk_ (tick_date),
  KEY xie2cur_reject_tk_ (object_id),
  CONSTRAINT `gabe_test` FOREIGN KEY (`object_type_cd`) REFERENCES 
`object_type` (`object_type_cd`)
) TYPE=InnoDB;

--
-- Table structure for table `object_type`
--

CREATE TABLE object_type (
  object_type_cd smallint(6) NOT NULL default '0',
  descr varchar(254) default NULL,
  PRIMARY KEY  (object_type_cd),
  UNIQUE KEY xpkobject_type (object_type_cd)
) TYPE=InnoDB;

Any help would be appreciated!

Thanks - Gabe



Arise Arise A Rose A Rose

Gabriel Tucker

609 750 6668 - P
646 268 5681 - F




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

RE: InnoDB - Foreign Key - Error 150.

[Marvin Wright]

Hi,

Are you creating them in the correct order ?

object_type must exist before you can create cur_reject_tk_sum otherwise the
foreign key will give errors.

Marvin


-Original Message-
From: Tucker, Gabriel [mailto:[EMAIL PROTECTED]
Sent: 04 May 2004 14:46
To: Mysql General (E-mail)
Subject: InnoDB - Foreign Key - Error 150.


Hello All:

I am having a problem with both V4.0.16 and 4.0.18.  Let me explain:

I have a database with two InnoDB tables in v4.0.16.  I backup up this
database every night using the following command:

mysqldump --allow-keywords --host=$HOST --flush-logs --disable-keys
--opt --port=$1 --socket=/bb/bin
/mysql/sockets/mysql.sock.$1 --master-data --all-databases -u mysql
-p  /bb/bin/mysql/backups/archive_$1.sql

I went to restore it the other day and I got this error:

ERROR 1005 at line 20: Can't create table './fxprmet/cur_reject_tk_sum.frm'
(errno: 150)

I thought this might be a version issue, so I copied all the database files
to another location, and started it using v4.0.18.  I then dropped the
foreign key constraint (no errors) and recreated it (no errors).  I backed
up the database.  And when I went to restore it, I got the same error.

The schema [after I deleted and re-added the constraint]:

CREATE TABLE cur_reject_tk_sum (
  cur_reject_tk_id int(11) NOT NULL auto_increment,
  contrib_swift_cd char(4) NOT NULL default '',
  object_id char(8) binary NOT NULL default '',
  tick_date date NOT NULL default '-00-00',
  object_type_cd smallint(6) NOT NULL default '0',
  num_received int(11) default NULL,
  num_rejected int(11) default NULL,
  num_spikes_contrib int(11) default NULL,
  num_spikes_compos int(11) default NULL,
  num_spread int(11) default NULL,
  num_filter int(11) default NULL,
  num_delayed int(11) default NULL,
  num_maybe int(11) default NULL,
  num_diff int(11) default NULL,
  num_bid_gt_ask int(11) default NULL,
  num_ask_no_bid int(11) default NULL,
  num_double_bid_ask int(11) default NULL,
  time_first_reject time default NULL,
  time_last_reject time default NULL,
  PRIMARY KEY  (cur_reject_tk_id),
  UNIQUE KEY xpkcur_reject_tk_s (cur_reject_tk_id),
  UNIQUE KEY xak1cur_reject_tk_
(contrib_swift_cd,object_id,tick_date),
  KEY xif1cur_reject_tk_ (object_type_cd),
  KEY xie1cur_reject_tk_ (tick_date),
  KEY xie2cur_reject_tk_ (object_id),
  CONSTRAINT `gabe_test` FOREIGN KEY (`object_type_cd`) REFERENCES
`object_type` (`object_type_cd`)
) TYPE=InnoDB;

--
-- Table structure for table `object_type`
--

CREATE TABLE object_type (
  object_type_cd smallint(6) NOT NULL default '0',
  descr varchar(254) default NULL,
  PRIMARY KEY  (object_type_cd),
  UNIQUE KEY xpkobject_type (object_type_cd)
) TYPE=InnoDB;

Any help would be appreciated!

Thanks - Gabe



Arise Arise A Rose A Rose

Gabriel Tucker

609 750 6668 - P
646 268 5681 - F




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



This e-mail has been scanned for all viruses by Star Internet. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk



This e-mail has been scanned for all viruses by Star Internet. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk


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

RE: InnoDB - Foreign Key - Error 150.

[Tucker, Gabriel]

Marvin

I believe that is the problem with the restore.  When I create the archive file using 
the mysqldump command and options previously listed, I get the create table in the 
order listed below and thus, the foreign key constraint is created on table 
cur_reject_tk_sum before the object_type table has been created.

Now my questions are:
[1] Is the above scenario my problem?
[2] Is so, how can I correct it?  Is this a problem with the way I am using mysqldump? 
[see commands below]  Or, is this a problem with how I am restoring the database? 
[Which, I create a default mysql database on its own port and then run from the 
prompt mysql --port= --socket= -p  archive_file.sql

Thanks again
Gabe

-Original Message-
From: Marvin Wright [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 04, 2004 9:59 AM
To: Tucker, Gabriel; Mysql General (E-mail)
Subject: RE: InnoDB - Foreign Key - Error 150.


Hi,

Are you creating them in the correct order ?

object_type must exist before you can create cur_reject_tk_sum otherwise the
foreign key will give errors.

Marvin


-Original Message-
From: Tucker, Gabriel [mailto:[EMAIL PROTECTED]
Sent: 04 May 2004 14:46
To: Mysql General (E-mail)
Subject: InnoDB - Foreign Key - Error 150.


Hello All:

I am having a problem with both V4.0.16 and 4.0.18.  Let me explain:

I have a database with two InnoDB tables in v4.0.16.  I backup up this
database every night using the following command:

mysqldump --allow-keywords --host=$HOST --flush-logs --disable-keys
--opt --port=$1 --socket=/bb/bin
/mysql/sockets/mysql.sock.$1 --master-data --all-databases -u mysql
-p  /bb/bin/mysql/backups/archive_$1.sql

I went to restore it the other day and I got this error:

ERROR 1005 at line 20: Can't create table './fxprmet/cur_reject_tk_sum.frm'
(errno: 150)

I thought this might be a version issue, so I copied all the database files
to another location, and started it using v4.0.18.  I then dropped the
foreign key constraint (no errors) and recreated it (no errors).  I backed
up the database.  And when I went to restore it, I got the same error.

The schema [after I deleted and re-added the constraint]:

CREATE TABLE cur_reject_tk_sum (
  cur_reject_tk_id int(11) NOT NULL auto_increment,
  contrib_swift_cd char(4) NOT NULL default '',
  object_id char(8) binary NOT NULL default '',
  tick_date date NOT NULL default '-00-00',
  object_type_cd smallint(6) NOT NULL default '0',
  num_received int(11) default NULL,
  num_rejected int(11) default NULL,
  num_spikes_contrib int(11) default NULL,
  num_spikes_compos int(11) default NULL,
  num_spread int(11) default NULL,
  num_filter int(11) default NULL,
  num_delayed int(11) default NULL,
  num_maybe int(11) default NULL,
  num_diff int(11) default NULL,
  num_bid_gt_ask int(11) default NULL,
  num_ask_no_bid int(11) default NULL,
  num_double_bid_ask int(11) default NULL,
  time_first_reject time default NULL,
  time_last_reject time default NULL,
  PRIMARY KEY  (cur_reject_tk_id),
  UNIQUE KEY xpkcur_reject_tk_s (cur_reject_tk_id),
  UNIQUE KEY xak1cur_reject_tk_
(contrib_swift_cd,object_id,tick_date),
  KEY xif1cur_reject_tk_ (object_type_cd),
  KEY xie1cur_reject_tk_ (tick_date),
  KEY xie2cur_reject_tk_ (object_id),
  CONSTRAINT `gabe_test` FOREIGN KEY (`object_type_cd`) REFERENCES
`object_type` (`object_type_cd`)
) TYPE=InnoDB;

--
-- Table structure for table `object_type`
--

CREATE TABLE object_type (
  object_type_cd smallint(6) NOT NULL default '0',
  descr varchar(254) default NULL,
  PRIMARY KEY  (object_type_cd),
  UNIQUE KEY xpkobject_type (object_type_cd)
) TYPE=InnoDB;

Any help would be appreciated!

Thanks - Gabe



Arise Arise A Rose A Rose

Gabriel Tucker

609 750 6668 - P
646 268 5681 - F




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



This e-mail has been scanned for all viruses by Star Internet. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk



This e-mail has been scanned for all viruses by Star Internet. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk


--
MySQL General Mailing List
For 

RE: InnoDB - Foreign Key - Error 150.

[Marvin Wright]

Hi,

I don't think mysqldump takes foreign key constraints into account when
dumping them.
You could specify the tables that you want when you dump so you get the
correct order.

e.g.

mysqldump --allow-keywords --host=$HOST --flush-logs --disable-keys --opt 
  --port=$1 --socket=/bb/bin/mysql/sockets/mysql.sock.$1 
  --master-data DB_NAME object_type cur_reject_tk_sum 
  -u mysql -p  /bb/bin/mysql/backups/archive_$1.sql

I dont know your database name do you would have to substitute that.
this should create the dump in the correct order for your restore.

Marvin.


-Original Message-
From: Tucker, Gabriel [mailto:[EMAIL PROTECTED]
Sent: 04 May 2004 15:13
To: Mysql General (E-mail)
Subject: RE: InnoDB - Foreign Key - Error 150.


Marvin

I believe that is the problem with the restore.  When I create the archive
file using the mysqldump command and options previously listed, I get the
create table in the order listed below and thus, the foreign key constraint
is created on table cur_reject_tk_sum before the object_type table has been
created.

Now my questions are:
[1] Is the above scenario my problem?
[2] Is so, how can I correct it?  Is this a problem with the way I am using
mysqldump? [see commands below]  Or, is this a problem with how I am
restoring the database? [Which, I create a default mysql database on its
own port and then run from the prompt mysql --port= --socket= -p 
archive_file.sql

Thanks again
Gabe

-Original Message-
From: Marvin Wright [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 04, 2004 9:59 AM
To: Tucker, Gabriel; Mysql General (E-mail)
Subject: RE: InnoDB - Foreign Key - Error 150.


Hi,

Are you creating them in the correct order ?

object_type must exist before you can create cur_reject_tk_sum otherwise the
foreign key will give errors.

Marvin


-Original Message-
From: Tucker, Gabriel [mailto:[EMAIL PROTECTED]
Sent: 04 May 2004 14:46
To: Mysql General (E-mail)
Subject: InnoDB - Foreign Key - Error 150.


Hello All:

I am having a problem with both V4.0.16 and 4.0.18.  Let me explain:

I have a database with two InnoDB tables in v4.0.16.  I backup up this
database every night using the following command:

mysqldump --allow-keywords --host=$HOST --flush-logs --disable-keys
--opt --port=$1 --socket=/bb/bin
/mysql/sockets/mysql.sock.$1 --master-data --all-databases -u mysql
-p  /bb/bin/mysql/backups/archive_$1.sql

I went to restore it the other day and I got this error:

ERROR 1005 at line 20: Can't create table './fxprmet/cur_reject_tk_sum.frm'
(errno: 150)

I thought this might be a version issue, so I copied all the database files
to another location, and started it using v4.0.18.  I then dropped the
foreign key constraint (no errors) and recreated it (no errors).  I backed
up the database.  And when I went to restore it, I got the same error.

The schema [after I deleted and re-added the constraint]:

CREATE TABLE cur_reject_tk_sum (
  cur_reject_tk_id int(11) NOT NULL auto_increment,
  contrib_swift_cd char(4) NOT NULL default '',
  object_id char(8) binary NOT NULL default '',
  tick_date date NOT NULL default '-00-00',
  object_type_cd smallint(6) NOT NULL default '0',
  num_received int(11) default NULL,
  num_rejected int(11) default NULL,
  num_spikes_contrib int(11) default NULL,
  num_spikes_compos int(11) default NULL,
  num_spread int(11) default NULL,
  num_filter int(11) default NULL,
  num_delayed int(11) default NULL,
  num_maybe int(11) default NULL,
  num_diff int(11) default NULL,
  num_bid_gt_ask int(11) default NULL,
  num_ask_no_bid int(11) default NULL,
  num_double_bid_ask int(11) default NULL,
  time_first_reject time default NULL,
  time_last_reject time default NULL,
  PRIMARY KEY  (cur_reject_tk_id),
  UNIQUE KEY xpkcur_reject_tk_s (cur_reject_tk_id),
  UNIQUE KEY xak1cur_reject_tk_
(contrib_swift_cd,object_id,tick_date),
  KEY xif1cur_reject_tk_ (object_type_cd),
  KEY xie1cur_reject_tk_ (tick_date),
  KEY xie2cur_reject_tk_ (object_id),
  CONSTRAINT `gabe_test` FOREIGN KEY (`object_type_cd`) REFERENCES
`object_type` (`object_type_cd`)
) TYPE=InnoDB;

--
-- Table structure for table `object_type`
--

CREATE TABLE object_type (
  object_type_cd smallint(6) NOT NULL default '0',
  descr varchar(254) default NULL,
  PRIMARY KEY  (object_type_cd),
  UNIQUE KEY xpkobject_type (object_type_cd)
) TYPE=InnoDB;

Any help would be appreciated!

Thanks - Gabe



Arise Arise A Rose A Rose

Gabriel Tucker

609 750 6668 - P
646 268 5681 - F




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

Re: InnoDB - Foreign Key - Error 150.

[Josh Trutwin]

On Tue, 4 May 2004 09:46:27 -0400
Tucker, Gabriel [EMAIL PROTECTED] wrote:

 Hello All:
 
 I am having a problem with both V4.0.16 and 4.0.18.  Let me explain:
 
 I have a database with two InnoDB tables in v4.0.16.  I backup up
 this database every night using the following command:
 
   mysqldump --allow-keywords --host=$HOST --flush-logs
   --disable-keys --opt --port=$1
   --socket=/bb/bin/mysql/sockets/mysql.sock.$1 --master-data
   --all-databases -u mysql -p 
   /bb/bin/mysql/backups/archive_$1.sql
 
 I went to restore it the other day and I got this error:
 
 ERROR 1005 at line 20: Can't create table
 './fxprmet/cur_reject_tk_sum.frm' (errno: 150)

Try looking over the output of SHOW INNODB STATUS; there might be some helpful 
messages in there.

Josh

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

4.0 mysql client vs 4.1 installation

[Lou Olsten]

This is not a question, rather just some information I wanted to post in case someone 
else hits it and searches the lists.

I do a lot of straight command line connecting using the mysql.exe client for windows. 
 Aside from my other challenges with getting users set up properly, I ran into another 
one yesterday where I couldn't connect from my workstation, which was using a 4.0.18 
mysql.exe (size=294,980).  The error was pretty straightforward:

ERROR 1250: Client does not support authentication protocol requested by server; 
consider upgrading MySQL client

When I copied the mysql.exe file from my 4.1.1 alpha installation to my workstation, I 
could connect fine.  The size for the 4.1.1 windows mysql.exe file is 974,992.  From 
my testing so far, I haven't run into any backward-compatibility issues.

Lou

Re: InnoDB - Foreign Key - Error 150.

[Ken Menzel]

Hi Guy's,
first:  Your are correct it is contraints:
bash-2.05a$ perror 150
Error code 150:  Unknown error: 150
150 = Foreign key constraint is incorrectly formed

Second you could also  try:

set foreign_key_check=0;

at the beginning of the restore file.
set foreign_key_check=1;

at the end!
Best of luck,
Ken
- Original Message - 
From: Marvin Wright [EMAIL PROTECTED]
To: Tucker, Gabriel [EMAIL PROTECTED]; Mysql General
(E-mail) [EMAIL PROTECTED]
Sent: Tuesday, May 04, 2004 10:22 AM
Subject: RE: InnoDB - Foreign Key - Error 150.


 Hi,

 I don't think mysqldump takes foreign key constraints into account
when
 dumping them.
 You could specify the tables that you want when you dump so you get
the
 correct order.

 e.g.


mysqldump --allow-keywords --host=$HOST --flush-logs --disable-keys --
opt
   --port=$1 --socket=/bb/bin/mysql/sockets/mysql.sock.$1
   --master-data DB_NAME object_type cur_reject_tk_sum
   -u mysql -p  /bb/bin/mysql/backups/archive_$1.sql

 I dont know your database name do you would have to substitute that.
 this should create the dump in the correct order for your restore.

 Marvin.


 -Original Message-
 From: Tucker, Gabriel [mailto:[EMAIL PROTECTED]
 Sent: 04 May 2004 15:13
 To: Mysql General (E-mail)
 Subject: RE: InnoDB - Foreign Key - Error 150.


 Marvin

 I believe that is the problem with the restore.  When I create the
archive
 file using the mysqldump command and options previously listed, I
get the
 create table in the order listed below and thus, the foreign key
constraint
 is created on table cur_reject_tk_sum before the object_type table
has been
 created.

 Now my questions are:
 [1] Is the above scenario my problem?
 [2] Is so, how can I correct it?  Is this a problem with the way I
am using
 mysqldump? [see commands below]  Or, is this a problem with how I am
 restoring the database? [Which, I create a default mysql database
on its
 own port and then run from the prompt mysql --port= --socket= -p 
 archive_file.sql

 Thanks again
 Gabe

 -Original Message-
 From: Marvin Wright [mailto:[EMAIL PROTECTED]
 Sent: Tuesday, May 04, 2004 9:59 AM
 To: Tucker, Gabriel; Mysql General (E-mail)
 Subject: RE: InnoDB - Foreign Key - Error 150.


 Hi,

 Are you creating them in the correct order ?

 object_type must exist before you can create cur_reject_tk_sum
otherwise the
 foreign key will give errors.

 Marvin


 -Original Message-
 From: Tucker, Gabriel [mailto:[EMAIL PROTECTED]
 Sent: 04 May 2004 14:46
 To: Mysql General (E-mail)
 Subject: InnoDB - Foreign Key - Error 150.


 Hello All:

 I am having a problem with both V4.0.16 and 4.0.18.  Let me explain:

 I have a database with two InnoDB tables in v4.0.16.  I backup up
this
 database every night using the following command:

 mysqldump --allow-keywords --host=$HOST --flush-logs --disable-keys
 --opt --port=$1 --socket=/bb/bin
 /mysql/sockets/mysql.sock.$1 --master-data --all-databases -u mysql
 -p  /bb/bin/mysql/backups/archive_$1.sql

 I went to restore it the other day and I got this error:

 ERROR 1005 at line 20: Can't create table
'./fxprmet/cur_reject_tk_sum.frm'
 (errno: 150)

 I thought this might be a version issue, so I copied all the
database files
 to another location, and started it using v4.0.18.  I then dropped
the
 foreign key constraint (no errors) and recreated it (no errors).  I
backed
 up the database.  And when I went to restore it, I got the same
error.

 The schema [after I deleted and re-added the constraint]:

 CREATE TABLE cur_reject_tk_sum (
   cur_reject_tk_id int(11) NOT NULL auto_increment,
   contrib_swift_cd char(4) NOT NULL default '',
   object_id char(8) binary NOT NULL default '',
   tick_date date NOT NULL default '-00-00',
   object_type_cd smallint(6) NOT NULL default '0',
   num_received int(11) default NULL,
   num_rejected int(11) default NULL,
   num_spikes_contrib int(11) default NULL,
   num_spikes_compos int(11) default NULL,
   num_spread int(11) default NULL,
   num_filter int(11) default NULL,
   num_delayed int(11) default NULL,
   num_maybe int(11) default NULL,
   num_diff int(11) default NULL,
   num_bid_gt_ask int(11) default NULL,
   num_ask_no_bid int(11) default NULL,
   num_double_bid_ask int(11) default NULL,
   time_first_reject time default NULL,
   time_last_reject time default NULL,
   PRIMARY KEY  (cur_reject_tk_id),
   UNIQUE KEY xpkcur_reject_tk_s (cur_reject_tk_id),
   UNIQUE KEY xak1cur_reject_tk_
 (contrib_swift_cd,object_id,tick_date),
   KEY xif1cur_reject_tk_ (object_type_cd),
   KEY xie1cur_reject_tk_ (tick_date),
   KEY xie2cur_reject_tk_ (object_id),
   CONSTRAINT `gabe_test` FOREIGN KEY (`object_type_cd`) REFERENCES
 `object_type` (`object_type_cd`)
 ) TYPE=InnoDB;

 --
 -- Table structure for table `object_type`
 --

 CREATE TABLE object_type (
   object_type_cd smallint(6) NOT NULL default '0',
   descr varchar(254) default NULL,
   PRIMARY KEY  (object_type_cd),
   UNIQUE KEY xpkobject_type (object_type_cd)
 

RE: Need correct 'order by' syntax where field does not contain NULL

[Mike Johnson]

From: Eve Atley [mailto:[EMAIL PROTECTED]

 Hi. I had a MySQL DB set up and recently added a field 
 'order' to allow for exceptions in a web site menu 
 heirarchy. Fields should be ordered by 'order' field 
 first where it does not contain 'NULL', and then by 
 field 'title'. I had this previously:
 
 select * from navigation WHERE id = '.$category.' AND 
 active='y' ORDER BY title
 
 ...and now, when I put in:
 
 select * from navigation WHERE id = '.$category.' AND 
 active='y' ORDER BY order, title
 
 ...my menu shows nothing. How can I write my statement to 
 allow for ORDER by order where 'order' does not contain 
 'NULL', and then title?


I'm curious about what you're using for a MySQL client. Are you using a web-based tool 
such as phpMyAdmin? If so, it probably added this new column using backticks, which 
allowed a reserved word (order) to be used as the name. I'm fairly certain that's why 
your menu is not displaying anything -- your query is dying when it gets to the ORDER 
BY clause.

So first step, in your query, add backticks around order:
ORDER BY `order`, title

That'll allow MySQL to see it as a column name and not a reserved word.

My second point is that I saw two replies to this, both of which said something along 
the lines of:

SELECT * FROM navigation 
WHERE id = '.$category.' 
AND active='y' 
AND order IS NOT NULL 
ORDER BY order, title

...which is blatantly wrong. You asked for all records matching your original WHERE 
clauses, order first by the 'order' column if not null, and then by 'title' if 'order' 
is null. This query above will not return records for which 'order' is null.

While it may not be the best way to go about it, give this a shot:

SELECT *, IF(`order` IS NULL, 99, `order`) AS sort_ord 
FROM navigation 
WHERE id = '.$category.' 
AND active='y' 
ORDER BY sort_ord ASC, title

This generates a temporary column in your result set that is used as the sort order 
based on the values of 'order.' Note that if you use values higher than 99 in 'order,' 
you'll want to set the 99 higher.

For an example, try this out:

===

mysql CREATE TABLE test (title VARCHAR(10), `order` INT);
Query OK, 0 rows affected (0.01 sec)

mysql INSERT INTO test (title, `order`) VALUES ('a', NULL);
Query OK, 1 row affected (0.00 sec)

mysql INSERT INTO test (title, `order`) VALUES ('b', NULL);
Query OK, 1 row affected (0.00 sec)

mysql INSERT INTO test (title, `order`) VALUES ('c', 1);
Query OK, 1 row affected (0.00 sec)

mysql INSERT INTO test (title, `order`) VALUES ('d', NULL);
Query OK, 1 row affected (0.00 sec)

mysql INSERT INTO test (title, `order`) VALUES ('e', 3);
Query OK, 1 row affected (0.00 sec)

mysql INSERT INTO test (title, `order`) VALUES ('f', 2);
Query OK, 1 row affected (0.00 sec)

mysql SELECT * FROM test;
+---+---+
| title | order |
+---+---+
| a |  NULL |
| b |  NULL |
| c | 1 |
| d |  NULL |
| e | 3 |
| f | 2 |
+---+---+
6 rows in set (0.00 sec)

mysql SELECT *, IF(`order` IS NULL, 99, `order`) AS sort_ord FROM test ORDER BY 
sort_ord ASC, title ASC;
+---+---+--+
| title | order | sort_ord |
+---+---+--+
| c | 1 |1 |
| f | 2 |2 |
| e | 3 |3 |
| a |  NULL |   99 |
| b |  NULL |   99 |
| d |  NULL |   99 |
+---+---+--+
6 rows in set (0.00 sec)

===

If someone knows a better way to do this, I'd be curious to hear it. I imagine there's 
a more efficient way to do it, but I can't seem to stumble upon it.

Anyway, hope this helps.   :)


-- 
Mike Johnson
Web Developer
Smarter Living, Inc.
phone (617) 886-5539

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

Re: InnoDB - Foreign Key - Error 150.

[Victoria Reznichenko]

Tucker, Gabriel [EMAIL PROTECTED] wrote:
 Marvin
 
 I believe that is the problem with the restore.  When I create the =
 archive file using the mysqldump command and options previously listed, =
 I get the create table in the order listed below and thus, the foreign =
 key constraint is created on table cur_reject_tk_sum before the =
 object_type table has been created.
 
 Now my questions are:
 [1] Is the above scenario my problem?
 [2] Is so, how can I correct it?  Is this a problem with the way I am =
 using mysqldump? [see commands below]  Or, is this a problem with how I =
 am restoring the database? [Which, I create a default mysql database =
 on its own port and then run from the prompt mysql --port=3D =
 --socket=3D -p  archive_file.sql

Add to the beginning of the dump file command:

SET FOREIGN_KEY_CHECKS = 0;

and then restore tables.

Or in the mysql client execute the following commands:

SET FOREIGN_KEY_CHECKS = 0;
SOURCE archive_file.sql;
SET FOREIGN_KEY_CHECKS = 1;



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [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: InnoDB - Foreign Key - Error 150.

[Tucker, Gabriel]

Luciano

I am confused...  As far as I can tell, the set foreign_key_checks=0; is used with the 
load data infile command.  I am not using this command to restore the database.  The 
mysqldump command creates a file with the data and schema.  I restore it to a new 
instance that just has the mysql database using:
unix$ mysql --port=port --socket=socket -p  archive.sql

So, I am not sure where I would insert this line nor if it would work.  Should I 
insert it in the   archive.sql from the previous example?

Can I use the load data infile to restore the file I generated?

Also, in the mysqldump command I used the --disable-keys command, believing this 
would correct the problem.  Do you know why it does not?

I know I asked a bunch of questions, thanks for whatever you can offer!

Gabe


-Original Message-
From: Luciano Barcaro [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 04, 2004 10:03 AM
To: Tucker, Gabriel
Subject: Re: InnoDB - Foreign Key - Error 150.


Put in your script:

set foreign_key_checks=0;
in the first line.

mysqldump dumps tables in alphabetical order.

Tucker, Gabriel wrote:

 Hello All:
 
 I am having a problem with both V4.0.16 and 4.0.18.  Let me explain:
 
 I have a database with two InnoDB tables in v4.0.16.  I backup up this database 
 every night using the following command:
 
   mysqldump --allow-keywords --host=$HOST --flush-logs --disable-keys --opt 
 --port=$1 --socket=/bb/bin
   /mysql/sockets/mysql.sock.$1 --master-data --all-databases -u mysql -p  
 /bb/bin/mysql/backups/archive_$1.sql
 
 I went to restore it the other day and I got this error:
 
 ERROR 1005 at line 20: Can't create table './fxprmet/cur_reject_tk_sum.frm' (errno: 
 150)
 
 I thought this might be a version issue, so I copied all the database files to 
 another location, and started it using v4.0.18.  I then dropped the foreign key 
 constraint (no errors) and recreated it (no errors).  I backed up the database.  And 
 when I went to restore it, I got the same error.
 
 The schema [after I deleted and re-added the constraint]:
 
   CREATE TABLE cur_reject_tk_sum (
 cur_reject_tk_id int(11) NOT NULL auto_increment,
 contrib_swift_cd char(4) NOT NULL default '',
 object_id char(8) binary NOT NULL default '',
 tick_date date NOT NULL default '-00-00',
 object_type_cd smallint(6) NOT NULL default '0',
 num_received int(11) default NULL,
 num_rejected int(11) default NULL,
 num_spikes_contrib int(11) default NULL,
 num_spikes_compos int(11) default NULL,
 num_spread int(11) default NULL,
 num_filter int(11) default NULL,
 num_delayed int(11) default NULL,
 num_maybe int(11) default NULL,
 num_diff int(11) default NULL,
 num_bid_gt_ask int(11) default NULL,
 num_ask_no_bid int(11) default NULL,
 num_double_bid_ask int(11) default NULL,
 time_first_reject time default NULL,
 time_last_reject time default NULL,
 PRIMARY KEY  (cur_reject_tk_id),
 UNIQUE KEY xpkcur_reject_tk_s (cur_reject_tk_id),
 UNIQUE KEY xak1cur_reject_tk_ (contrib_swift_cd,object_id,tick_date),
 KEY xif1cur_reject_tk_ (object_type_cd),
 KEY xie1cur_reject_tk_ (tick_date),
 KEY xie2cur_reject_tk_ (object_id),
 CONSTRAINT `gabe_test` FOREIGN KEY (`object_type_cd`) REFERENCES 
 `object_type` (`object_type_cd`)
   ) TYPE=InnoDB;
 
   --
   -- Table structure for table `object_type`
   --
 
   CREATE TABLE object_type (
 object_type_cd smallint(6) NOT NULL default '0',
 descr varchar(254) default NULL,
 PRIMARY KEY  (object_type_cd),
 UNIQUE KEY xpkobject_type (object_type_cd)
   ) TYPE=InnoDB;
 
 Any help would be appreciated!
 
 Thanks - Gabe
 
 
 
 Arise Arise A Rose A Rose
 
 Gabriel Tucker
 
 609 750 6668 - P
 646 268 5681 - F
 
 
 
 

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

RE: Need correct 'order by' syntax where field does not contain NULL

[Chris DaMour]

Your first problem is the order not having backticks you have to do 

ORDER BY `order`, title or you'll get a sql error.

But the other thing you'll run into is that null evaluates to les than any
int, meaning the null rows will come before your numbered rows, however you
can't just switch to 

ORDER BY `order` DESC, title 

because than you're order will be backwards

What you need to do is trick the order evaluation like so

ORDER BY (0 - `order`) DESC, title

This lists by increasing order, then null by increasing title as now the
smallest values of `order` will now be the biggest


Alternatively You could do two selects, one ordering ascending on not null
columns UNION'd with a select ordered by title with order null

I don't know which would be faster.

-Original Message-
From: Mike Johnson [mailto:[EMAIL PROTECTED] 
Sent: Tuesday, May 04, 2004 10:43 AM
To: Eve Atley; [EMAIL PROTECTED]
Subject: RE: Need correct 'order by' syntax where field does not contain
NULL

From: Eve Atley [mailto:[EMAIL PROTECTED]

 Hi. I had a MySQL DB set up and recently added a field 
 'order' to allow for exceptions in a web site menu 
 heirarchy. Fields should be ordered by 'order' field 
 first where it does not contain 'NULL', and then by 
 field 'title'. I had this previously:
 
 select * from navigation WHERE id = '.$category.' AND 
 active='y' ORDER BY title
 
 ...and now, when I put in:
 
 select * from navigation WHERE id = '.$category.' AND 
 active='y' ORDER BY order, title
 
 ...my menu shows nothing. How can I write my statement to 
 allow for ORDER by order where 'order' does not contain 
 'NULL', and then title?


I'm curious about what you're using for a MySQL client. Are you using a
web-based tool such as phpMyAdmin? If so, it probably added this new column
using backticks, which allowed a reserved word (order) to be used as the
name. I'm fairly certain that's why your menu is not displaying anything --
your query is dying when it gets to the ORDER BY clause.

So first step, in your query, add backticks around order:
ORDER BY `order`, title

That'll allow MySQL to see it as a column name and not a reserved word.

My second point is that I saw two replies to this, both of which said
something along the lines of:

SELECT * FROM navigation 
WHERE id = '.$category.' 
AND active='y' 
AND order IS NOT NULL 
ORDER BY order, title

...which is blatantly wrong. You asked for all records matching your
original WHERE clauses, order first by the 'order' column if not null, and
then by 'title' if 'order' is null. This query above will not return records
for which 'order' is null.

While it may not be the best way to go about it, give this a shot:

SELECT *, IF(`order` IS NULL, 99, `order`) AS sort_ord 
FROM navigation 
WHERE id = '.$category.' 
AND active='y' 
ORDER BY sort_ord ASC, title

This generates a temporary column in your result set that is used as the
sort order based on the values of 'order.' Note that if you use values
higher than 99 in 'order,' you'll want to set the 99 higher.

For an example, try this out:

===

mysql CREATE TABLE test (title VARCHAR(10), `order` INT);
Query OK, 0 rows affected (0.01 sec)

mysql INSERT INTO test (title, `order`) VALUES ('a', NULL);
Query OK, 1 row affected (0.00 sec)

mysql INSERT INTO test (title, `order`) VALUES ('b', NULL);
Query OK, 1 row affected (0.00 sec)

mysql INSERT INTO test (title, `order`) VALUES ('c', 1);
Query OK, 1 row affected (0.00 sec)

mysql INSERT INTO test (title, `order`) VALUES ('d', NULL);
Query OK, 1 row affected (0.00 sec)

mysql INSERT INTO test (title, `order`) VALUES ('e', 3);
Query OK, 1 row affected (0.00 sec)

mysql INSERT INTO test (title, `order`) VALUES ('f', 2);
Query OK, 1 row affected (0.00 sec)

mysql SELECT * FROM test;
+---+---+
| title | order |
+---+---+
| a |  NULL |
| b |  NULL |
| c | 1 |
| d |  NULL |
| e | 3 |
| f | 2 |
+---+---+
6 rows in set (0.00 sec)

mysql SELECT *, IF(`order` IS NULL, 99, `order`) AS sort_ord FROM test
ORDER BY sort_ord ASC, title ASC;
+---+---+--+
| title | order | sort_ord |
+---+---+--+
| c | 1 |1 |
| f | 2 |2 |
| e | 3 |3 |
| a |  NULL |   99 |
| b |  NULL |   99 |
| d |  NULL |   99 |
+---+---+--+
6 rows in set (0.00 sec)

===

If someone knows a better way to do this, I'd be curious to hear it. I
imagine there's a more efficient way to do it, but I can't seem to stumble
upon it.

Anyway, hope this helps.   :)


-- 
Mike Johnson
Web Developer
Smarter Living, Inc.
phone (617) 886-5539

-- 
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: InnoDB - Foreign Key - Error 150.

[Tucker, Gabriel]

Oooops - I sent that last email before I read this one, please disregard.

This appears that it will solve my problem.  I will give it a try.

Thanks for all that replied!
Gabe

-Original Message-
From: Victoria Reznichenko [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 04, 2004 10:35 AM
To: [EMAIL PROTECTED]
Subject: Re: InnoDB - Foreign Key - Error 150.


Tucker, Gabriel [EMAIL PROTECTED] wrote:
 Marvin
 
 I believe that is the problem with the restore.  When I create the =
 archive file using the mysqldump command and options previously listed, =
 I get the create table in the order listed below and thus, the foreign =
 key constraint is created on table cur_reject_tk_sum before the =
 object_type table has been created.
 
 Now my questions are:
 [1] Is the above scenario my problem?
 [2] Is so, how can I correct it?  Is this a problem with the way I am =
 using mysqldump? [see commands below]  Or, is this a problem with how I =
 am restoring the database? [Which, I create a default mysql database =
 on its own port and then run from the prompt mysql --port=3D =
 --socket=3D -p  archive_file.sql

Add to the beginning of the dump file command:

SET FOREIGN_KEY_CHECKS = 0;

and then restore tables.

Or in the mysql client execute the following commands:

SET FOREIGN_KEY_CHECKS = 0;
SOURCE archive_file.sql;
SET FOREIGN_KEY_CHECKS = 1;



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [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]


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

Re: Documentation on character sets for version 4.0.17

[David Jourard]

Victoria ,

Thank-you.

At 01:35 PM 5/4/04 +0300, Victoria Reznichenko wrote:

David Jourard [EMAIL PROTECTED] wrote:

 I got the 4.0.17 documentation but when it discusses character sets it
 discusses this topic wrt 4.1

 Where can I find documentation specifically in regards to the production
 version on how to work with,  store, and search asian character sets
 specifically Japanese.

In version 4.0.17 you should specify character set with 
--default-character-set option. For Japanese language take a look at sjis 
and ujis character sets:
http://dev.mysql.com/doc/mysql/en/Character_sets.html


a. I'm working with a table where some fields are using the Latin 1 ch. 
set. and other fields using Japanese.

Can I work with both character sets?

Can I run a query with both or do they have to be separate.

b. Also the application is on a hosted web site so they do not have control 
over the settings of the mysql server.  How does one set the character 
set?  Is this done from the connection string or via the select query?

Thank-you
David J.
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.662 / Virus Database: 425 - Release Date: 4/20/04

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

[mysql] replication of database structure changes

[Jim]

Hi List,

Do changes in database structure replicate to the slaves from the master?  Is 
there a document somewhere in the manual (I have not found one) that explains 
what gets replicated and what does not?  Specifically, does an ALTER TABLE 
get replicated?  There is some mention that replication is for data and not 
for structure, but a formal explanation of what exactly that means is hard to 
find.

Thanks again.

Jim N.


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

Re: InnoDB - Foreign Key - Error 150.

[Josh Trutwin]

On Tue, 4 May 2004 11:01:59 -0400
Tucker, Gabriel [EMAIL PROTECTED] wrote:

 Luciano
 
 I am confused...  As far as I can tell, the set
 foreign_key_checks=0; is used with the load data infile command.  

Actually, I think that it is used for all operations on the DBMS, not just load data 
infile, INSERTS, UPDATES, DELETES as well.  This is usually what I do when I have a 
dump created with mysqldump and need to re-create the tables.  What would be nicer is 
if mysqldump had some smarts to dump things in the right order so this would not be an 
issue.  Or course, the logic to do that would be kind of complex, always having to 
dump the weakest tables (those with foreign keys) first.  :)

 I
 am not using this command to restore the database.  The mysqldump
 command creates a file with the data and schema.  I restore it to a
 new instance that just has the mysql database using:
 unix$ mysql --port=port --socket=socket -p  archive.sql
 
 So, I am not sure where I would insert this line nor if it would
 work.  Should I insert it in the   archive.sql from the previous
 example?
 
 Can I use the load data infile to restore the file I generated?
 
 Also, in the mysqldump command I used the --disable-keys command,
 believing this would correct the problem.  Do you know why it does
 not?

This disables the keys during each INSERT block, but once the INSERT's are done, then 
it tries to enable the keys, so you will still get problems.  --disable-keys is meant 
more as an optimization.  The recommendation to set foreign_key_checks=0 at the start 
of the dump file, then set foreign_key_checks=1 at the end of the dump file should 
work just fine, has for me anyway.

Josh

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

RE: InnoDB - Foreign Key - Error 150.

[Tucker, Gabriel]

Victoria 

That seemed to work well, thank you.

However, I received another error that I am not sure how to troubleshoot during the 
restore:
ERROR 1114 at line 83 in file: '/bb/bin/mysql/backups/archive_4320.sql': The 
table  'cur_reject_tk_sum' is full

What can I do here?

Thanks - Gabe

-Original Message-
From: Victoria Reznichenko [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 04, 2004 10:35 AM
To: [EMAIL PROTECTED]
Subject: Re: InnoDB - Foreign Key - Error 150.


Tucker, Gabriel [EMAIL PROTECTED] wrote:
 Marvin
 
 I believe that is the problem with the restore.  When I create the =
 archive file using the mysqldump command and options previously listed, =
 I get the create table in the order listed below and thus, the foreign =
 key constraint is created on table cur_reject_tk_sum before the =
 object_type table has been created.
 
 Now my questions are:
 [1] Is the above scenario my problem?
 [2] Is so, how can I correct it?  Is this a problem with the way I am =
 using mysqldump? [see commands below]  Or, is this a problem with how I =
 am restoring the database? [Which, I create a default mysql database =
 on its own port and then run from the prompt mysql --port=3D =
 --socket=3D -p  archive_file.sql

Add to the beginning of the dump file command:

SET FOREIGN_KEY_CHECKS = 0;

and then restore tables.

Or in the mysql client execute the following commands:

SET FOREIGN_KEY_CHECKS = 0;
SOURCE archive_file.sql;
SET FOREIGN_KEY_CHECKS = 1;



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [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]


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

RE: InnoDB - Foreign Key - Error 150.

[Tucker, Gabriel]

Hooray!

The last problem was b/c I did not have the same InnoDB settings in my cnf file.

Again, thank you all for your time in this matter!

Gabe

-Original Message-
From: [EMAIL PROTECTED] 
Sent: Tuesday, May 04, 2004 11:29 AM
To: Victoria Reznichenko; [EMAIL PROTECTED]
Subject: RE: InnoDB - Foreign Key - Error 150.


Victoria 

That seemed to work well, thank you.

However, I received another error that I am not sure how to troubleshoot during the 
restore:
ERROR 1114 at line 83 in file: '/bb/bin/mysql/backups/archive_4320.sql': The 
table  'cur_reject_tk_sum' is full

What can I do here?

Thanks - Gabe

-Original Message-
From: Victoria Reznichenko [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 04, 2004 10:35 AM
To: [EMAIL PROTECTED]
Subject: Re: InnoDB - Foreign Key - Error 150.


Tucker, Gabriel [EMAIL PROTECTED] wrote:
 Marvin
 
 I believe that is the problem with the restore.  When I create the =
 archive file using the mysqldump command and options previously listed, =
 I get the create table in the order listed below and thus, the foreign =
 key constraint is created on table cur_reject_tk_sum before the =
 object_type table has been created.
 
 Now my questions are:
 [1] Is the above scenario my problem?
 [2] Is so, how can I correct it?  Is this a problem with the way I am =
 using mysqldump? [see commands below]  Or, is this a problem with how I =
 am restoring the database? [Which, I create a default mysql database =
 on its own port and then run from the prompt mysql --port=3D =
 --socket=3D -p  archive_file.sql

Add to the beginning of the dump file command:

SET FOREIGN_KEY_CHECKS = 0;

and then restore tables.

Or in the mysql client execute the following commands:

SET FOREIGN_KEY_CHECKS = 0;
SOURCE archive_file.sql;
SET FOREIGN_KEY_CHECKS = 1;



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [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]


-- 
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: [mysql] replication of database structure changes

[Dan Nelson]

In the last episode (May 04), Jim said:
 Do changes in database structure replicate to the slaves from the
 master?  Is there a document somewhere in the manual (I have not
 found one) that explains what gets replicated and what does not? 
 Specifically, does an ALTER TABLE get replicated?  There is some
 mention that replication is for data and not for structure, but a
 formal explanation of what exactly that means is hard to find.

All commands that modify data, including CREATE|DROP TABLE|DATABASE,
replicated.

-- 
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: InnoDB - Foreign Key - Error 150.

[Luciano Barcaro]

Gabriel,
First of all, sorry for my poor english (I´m just a brazilian guy - eu 
quase não sei falar o portugues, imaginem o ingles então.)

Tucker, Gabriel wrote:
Luciano

I am confused...  As far as I can tell, the set foreign_key_checks=0; is used with the 
load data infile command.  I am not using this command to restore the database.  The 
mysqldump command creates a file with the data and schema.  I restore it to a new 
instance that just has the mysql database using:
unix$ mysql --port=port --socket=socket -p  archive.sql
The set foreign_key_checks=0 disables the referential integrity (for 
just one session only).
So, I am not sure where I would insert this line nor if it would work.  Should I insert it in the   archive.sql from the previous example?
Yes, you should insert in the beginning of the file, OR
you can do this:
mysql --port=port --socket=socket -p

set foreign_key_checks=0;   - Disables integrity
\. archive.sql  - Execute the script
exit- quits the client
Can I use the load data infile to restore the file I generated?
As far as I know, no, you can´t.

Also, in the mysqldump command I used the --disable-keys command, believing this would correct the problem.  Do you know why it does not?
No, the foreign key error is generated because mysqldump dumps table in 
a different order (alphabetical) that it should.
I know I asked a bunch of questions, thanks for whatever you can offer!

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

Re: Documentation on character sets for version 4.0.17

[Nils Valentin]

Hi David,
(B
(BYou may find my UC-2004 presentation useful as well as some UC-2003 
(Bpresentations from Mr. Gulutzan and Mr. Barkov:
(B
(Bwww.be-known-online.com/mysql
(Bmysql.planetmirror.com/Downloads/Presentations/MySQL-User-Conference-2003/National-Character-Sets-and-Unicode.pdf
(B
(BBasically the best guess would be the documentation that comes with your 
(Bdownload. Note that online documents at www.mysql.com are always a mixture of 
(Bseveral documentation versions really.
(B
(BI am not 100% if this answer all of your questions, but at least in my 
(Bpresentation you should find 90% of all the information you require.
(B
(BShould you have any other question, please do not hesitate to ask me.
(B
(BBest regards
(B
(BNils Valentin
(BTokyo/Japan
(B
(BOn Wednesday 05 May 2004 01:16, David Jourard wrote:
(B Victoria ,
(B
(B Thank-you.
(B
(B At 01:35 PM 5/4/04 +0300, Victoria Reznichenko wrote:
(B David Jourard [EMAIL PROTECTED] wrote:
(B   I got the 4.0.17 documentation but when it discusses character sets it
(B   discusses this topic wrt 4.1
(B  
(B   Where can I find documentation specifically in regards to the
(B   production version on how to work with,  store, and search asian
(B   character sets specifically Japanese.
(B 
(B In version 4.0.17 you should specify character set with
(B --default-character-set option. For Japanese language take a look at sjis
(B and ujis character sets:
(B  http://dev.mysql.com/doc/mysql/en/Character_sets.html
(B
(B a. I'm working with a table where some fields are using the Latin 1 ch.
(B set. and other fields using Japanese.
(B
(B Can I work with both character sets?
(B
(B Can I run a query with both or do they have to be separate.
(B
(B b. Also the application is on a hosted web site so they do not have control
(B over the settings of the mysql server.  How does one set the character
(B set?  Is this done from the connection string or via the select query?
(B
(B Thank-you
(B David J.
(B
(B-- 
(Bkind regards
(B
(BNils Valentin
(BTokyo/Japan
(B
(Bhttp://www.be-known-online.com/mysql/
(B
(B-- 
(BMySQL General Mailing List
(BFor list archives: http://lists.mysql.com/mysql
(BTo unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: [PHP-DB] lamer noob with repeat question

[Stephen E. Bacher]

$result_insert = @mysql_query ($query_insert) or die(you suck! $mysql_error);

Try mysql_error() instead of $mysql_error.

 - seb

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

mysqld-threads

[[EMAIL PROTECTED]]

Hi folks!

I have diffrent linux-machines running with the great mysqld.

But on some machines I have 20 running threads on some other only 2
threads.

If only 2 threads are running is that not bad for fast sql-results?
I have about 500 v-hosts and many sql-queries on my machine.

Does somebody know how I can add more stand-by threads for my mysqlds
that mysqld become faster?

Viele Gruesse,
Peter.
-- 
www: http://peter.tux.hm
www: http://tux.hm - Linux- und BSD-UserGroup im Weserbergland
gpg: http://blackhole.pca.dfn.de:11371/pks/lookup?op=getsearch=0x690A1AC2


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

Stupid Mistake - update [Was: Stupid mistake]

[Niels Riis Kristensen]

I backed up my data with CC-cloner without the boot feature - - - -yeah I know!

So I have the directory with the data in them, in the form of .frm, .MYD and -MYI files, but when I try to acces the data I get the error messege:

Quote> Can't find file: './vault/data.frm' (errno: 13)


Error

 SQL-query : 


x-tad-smallerSHOW/x-tad-smallerx-tad-smaller  /x-tad-smallerx-tad-smallerKEYS/x-tad-smallerx-tad-smaller  /x-tad-smallerx-tad-smallerFROM/x-tad-smallerx-tad-smaller /x-tad-smallerx-tad-smaller`data`/x-tad-smallerx-tad-smaller 

/x-tad-smaller MySQL said: 

x-tad-smaller#1017 - Can't find file: './vault/data.frm' (errno: 13)/x-tad-smallerx-tad-smaller /x-tad-smaller End quote >

The file is physically there. It is nested in the directory x-tad-bigger/usr/local/mysql-standard-4.0.18-apple-darwin6.8-powerpc/data/name-of-database>.

I have highly sensitive and nessecary data in the database. I hope you can help me.


/x-tad-biggerNiels Riis Kristensen
([EMAIL PROTECTED])

NRK Group
- Electronic Music Engraving
- Dynamic Web design
- E-Lists hosting

---
Send money safely over the Internet!
Click this link for more information: https://www.paypal.com/refer/pal=EAJLSE5TQELFC

smime.p7s
Description: S/MIME cryptographic signature

RE: Stupid Mistake - update [Was: Stupid mistake]

[Victor Pendleton]

You need to change permissions on the files

-Original Message-
From: Niels Riis Kristensen
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: 5/4/04 12:11 PM
Subject: Stupid Mistake - update [Was: Stupid mistake]

I backed up my data with CC-cloner without the boot feature - - - -yeah
I know! 

So I have the directory with the data in them, in the form of .frm, .MYD
and -MYI files, but when I try to acces the data I get the error
messege: 

Quote Can't find file: './vault/data.frm' (errno: 13) 


Error 

SQL-query :  


SHOW  KEYS  FROM `data`  

 MySQL said:  

#1017 - Can't find file: './vault/data.frm' (errno: 13)  End quote  

The file is physically there. It is nested in the directory
/usr/local/mysql-standard-4.0.18-apple-darwin6.8-powerpc/data/name-of-d
atabase. 

I have highly sensitive and nessecary data in the database. I hope you
can help me. 


Niels Riis Kristensen 
([EMAIL PROTECTED]) 

NRK Group 
- Electronic Music Engraving 
- Dynamic Web design 
- E-Lists hosting 

--- 
Send money safely over the Internet! 
Click this link for more information:
https://www.paypal.com/refer/pal=EAJLSE5TQELFC

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

Re: How can one found out why a query is not cached?

[Haitao Jiang]

Thanks Paul. But that my query doesn't violate any of
these conditions. Here is more detail:

mysql show status like %qcache%;
+-+---+
| Variable_name   | Value |
+-+---+
| Qcache_queries_in_cache | 0 |
| Qcache_inserts  | 0 |
| Qcache_hits | 0 |
| Qcache_lowmem_prunes| 0 |
| Qcache_not_cached   | 163   |
| Qcache_free_memory  | 511982024 |
| Qcache_free_blocks  | 1 |
| Qcache_total_blocks | 1 |
+-+---+
8 rows in set (0.00 sec)

mysql show variables like %cache%;
+--+--+
| Variable_name| Value|
+--+--+
| bdb_cache_size   | 8388600  |
| binlog_cache_size| 32768|
| have_query_cache | YES  |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100  |
| key_cache_age_threshold  | 300  |
| max_binlog_cache_size| 18446744073709551615 |
| query_cache_limit| 134217728|
| query_cache_min_res_unit | 1024 |
| query_cache_size | 51200|
| query_cache_type | ON   |
| table_cache  | 256  |
| thread_cache_size| 16   |
+--+--+
13 rows in set (0.00 sec)

Now issue the query:
mysql select SQL_CACHE UPC from PIMuzeIndex where UPC
= 08616218713;
+-+
| UPC |
+-+
| 08616218713 |
| 08616218713 |
| 08616218713 |
| 08616218713 |
| 08616218713 |
| 08616218713 |
| 08616218713 |
+-+
7 rows in set (0.00 sec)

Now check the qcache status, we can see no query is
cached:

mysql show status like %qcache%;
+-+---+
| Variable_name   | Value |
+-+---+
| Qcache_queries_in_cache | 0 |
| Qcache_inserts  | 0 |
| Qcache_hits | 0 |
| Qcache_lowmem_prunes| 0 |
| Qcache_not_cached   | 164   |
| Qcache_free_memory  | 511982024 |
| Qcache_free_blocks  | 1 |
| Qcache_total_blocks | 1 |
+-+---+
8 rows in set (0.01 sec)

Any idea? Please help.

Thanks

HT
--- Paul DuBois [EMAIL PROTECTED] wrote:
 At 17:02 -0700 5/3/04, Haitao Jiang wrote:
 I have a complex query which took 4 seconds, I set
 the
 query cache size to 512MB. BUt the query is not
 cached, it only returns 7 rows, so the cache size
 is
 not a problem here. Anyway I can find out why MySQL
 4.1 is not caching my queries?
 
 The criteria for caching are given on this page:
 

http://dev.mysql.com/doc/mysql/en/Query_Cache_How.html
 
 -- 
 Paul DuBois, MySQL Documentation Team
 Madison, Wisconsin, USA
 MySQL AB, www.mysql.com





__
Do you Yahoo!?
Win a $20,000 Career Makeover at Yahoo! HotJobs  
http://hotjobs.sweepstakes.yahoo.com/careermakeover 

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

Re: Stupid Mistake - update [Was: Stupid mistake]

[Michael Stassen]

Niels Riis Kristensen wrote:
I backed up my data with CC-cloner without the boot feature - - - -yeah 
I know!

So I have the directory with the data in them, in the form of .frm, .MYD 
and -MYI files, but when I try to acces the data I get the error message:

Quote Can't find file: './vault/data.frm' (errno: 13)
You can check mysql error codes with perror:

  perror 13
  Error code  13:  Permission denied
Your data files are not accessible to mysql.  Probably your restore from 
backup left them owned by another user (root, perhaps).  Assuming you run 
mysqld as the mysql user, you should

  cd /usr/local/mysql-standard-4.0.18-apple-darwin6.8-powerpc/
  sudo chown -R mysql:mysql data
Then try again.

Michael

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

Re: Documentation on character sets for version 4.0.17

[David Jourard]

Nils,

At 05:30 PM 5/4/04 +0100, you wrote:
Basically the best guess would be the documentation that comes with your
download. Note that online documents at www.mysql.com are always a mixture of
several documentation versions really.


Thats the problem.  Yesterday, I downloaded 4.0.1x yet the documentation 
that comes with it in regards to asian character set etc. is 4.1

But the application I'm working with is a perl/cgi/mysql thing on a virtual 
hosted site and is using 4.0.15 (which means I can only set things from the 
application level or via the connection string.)

I need to know how to set this up for this version not 4.1

From your documentation I gather I need to tell the server what character 
set to use and also about data coming from and going to the client.

My problem is I'm not too sure how to start.

Here is the data flow:

Storing Data

1. web page (uses shift_sjis) --
2. cgi app captures form data  with mixed fields eg. english and japanese
3. cgi app --- saves data to mixed fields in a table.
Is there something I do at the point data is being saved. Save one field in 
one kind of encoding and the other in another kind of coding.

OR is there something I indicate by a property of the field

Retrieving Data
---
1. cgi app searches on varchar fields both japanese and in english
2. cgi app searchs on text fields using fulltext engine for keyword searches.
Everything works fine for the english but the searches in japanese are not 
working.

I'm thinking maybe it has something to do with the encoding in the japanese 
fields which I'm not sure how to do at the field level for version 4.0.15

Thanks

David J.




---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.662 / Virus Database: 425 - Release Date: 4/20/04

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

Re: 4.0 mysql client vs 4.1 installation

[SGreen]

Lou Wrote
This is not a question, rather just some information I wanted to post in
case someone else hits it and searches the lists.

I do a lot of straight command line connecting using the mysql.exe client
for windows.  Aside from my other challenges with getting users set up
properly, I ran into another one yesterday where I couldn't connect from my
workstation, which was using a 4.0.18 mysql.exe (size=294,980).  The error
was pretty straightforward:

ERROR 1250: Client does not support authentication protocol requested by
server; consider upgrading MySQL client

When I copied the mysql.exe file from my 4.1.1 alpha installation to my
workstation, I could connect fine.  The size for the 4.1.1 windows
mysql.exe file is 974,992.  From my testing so far, I haven't run into any
backward-compatibility issues.

Lou
/Lou wrote

There is another way to resolve that issue. Your problem existed because
the password hash was lengthened as of 4.1 (which is why your pre 4.1.x
client couldn't authenticate with the new server.) You can keep using a
pre-4.1.x client or library (the window's ODBC drivers are at 3.51) by
using the new (as of 4.1) function OLD_PASSWORD() to re-encrypt the user's
password to the shorter (pre-4.1) hash.

You really need read this for more details:
http://dev.mysql.com/doc/mysql/en/Password_hashing.html

It covers the PASSWORD() and OLD_PASSWORD() functions as well as the
--old-passwords startup option in some depth. Pay close attention to the
warnings about what works and doesn't when you are using mixed version
authentication.

Shawn



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

RE: 4.0 mysql client vs 4.1 installation

[Victor Pendleton]

You can also use the `old-passwords` option in the my.cnf file.

-Original Message-
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: 5/4/04 1:09 PM
Subject: Re: 4.0 mysql client vs 4.1 installation


Lou Wrote
This is not a question, rather just some information I wanted to post in
case someone else hits it and searches the lists.

I do a lot of straight command line connecting using the mysql.exe
client
for windows.  Aside from my other challenges with getting users set up
properly, I ran into another one yesterday where I couldn't connect from
my
workstation, which was using a 4.0.18 mysql.exe (size=294,980).  The
error
was pretty straightforward:

ERROR 1250: Client does not support authentication protocol requested by
server; consider upgrading MySQL client

When I copied the mysql.exe file from my 4.1.1 alpha installation to my
workstation, I could connect fine.  The size for the 4.1.1 windows
mysql.exe file is 974,992.  From my testing so far, I haven't run into
any
backward-compatibility issues.

Lou
/Lou wrote

There is another way to resolve that issue. Your problem existed because
the password hash was lengthened as of 4.1 (which is why your pre 4.1.x
client couldn't authenticate with the new server.) You can keep using a
pre-4.1.x client or library (the window's ODBC drivers are at 3.51) by
using the new (as of 4.1) function OLD_PASSWORD() to re-encrypt the
user's
password to the shorter (pre-4.1) hash.

You really need read this for more details:
http://dev.mysql.com/doc/mysql/en/Password_hashing.html

It covers the PASSWORD() and OLD_PASSWORD() functions as well as the
--old-passwords startup option in some depth. Pay close attention to the
warnings about what works and doesn't when you are using mixed version
authentication.

Shawn



-- 
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: 4.0 mysql client vs 4.1 installation

[Paul DuBois]

At 10:39 -0400 5/4/04, Lou Olsten wrote:
This is not a question, rather just some information I wanted to 
post in case someone else hits it and searches the lists.

I do a lot of straight command line connecting using the mysql.exe 
client for windows.  Aside from my other challenges with getting 
users set up properly, I ran into another one yesterday where I 
couldn't connect from my workstation, which was using a 4.0.18 
mysql.exe (size=294,980).  The error was pretty straightforward:

ERROR 1250: Client does not support authentication protocol 
requested by server; consider upgrading MySQL client

When I copied the mysql.exe file from my 4.1.1 alpha installation to 
my workstation, I could connect fine.  The size for the 4.1.1 
windows mysql.exe file is 974,992.  From my testing so far, I 
haven't run into any backward-compatibility issues.

Lou
Additional reading:

http://dev.mysql.com/doc/mysql/en/Old_client.html
http://dev.mysql.com/doc/mysql/en/Password_hashing.html
http://dev.mysql.com/doc/mysql/en/Upgrading-from-4.0.html
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Stupid Mistake - update [Was: Stupid mistake]

[Niels Riis Kristensen]

That did the trick! SO many thanks

Niels Riis Kristensen
([EMAIL PROTECTED])
NRK Group
- Electronic Music Engraving
- Dynamic Web design
- E-Lists hosting
---
Send money safely over the Internet!
Click this link for more information:  
https://www.paypal.com/refer/pal=EAJLSE5TQELFC

On 4/5-2004, at 19.19, Victor Pendleton wrote:

You need to change permissions on the files

-Original Message-
From: Niels Riis Kristensen
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: 5/4/04 12:11 PM
Subject: Stupid Mistake - update [Was: Stupid mistake]
I backed up my data with CC-cloner without the boot feature - - - -yeah
I know!
So I have the directory with the data in them, in the form of .frm,  
.MYD
and -MYI files, but when I try to acces the data I get the error
messege:

Quote Can't find file: './vault/data.frm' (errno: 13)

Error

SQL-query :

SHOW  KEYS  FROM `data`

 MySQL said:

#1017 - Can't find file: './vault/data.frm' (errno: 13)  End quote 

The file is physically there. It is nested in the directory
/usr/local/mysql-standard-4.0.18-apple-darwin6.8-powerpc/data/name- 
of-d
atabase.

I have highly sensitive and nessecary data in the database. I hope you
can help me.
Niels Riis Kristensen
([EMAIL PROTECTED])
NRK Group
- Electronic Music Engraving
- Dynamic Web design
- E-Lists hosting
---
Send money safely over the Internet!
Click this link for more information:
https://www.paypal.com/refer/pal=EAJLSE5TQELFC


smime.p7s
Description: S/MIME cryptographic signature

Re: mysql-3.23.35.0 on AIX 4.3: CPU Hog

[Sasha Pachev]

Paul Sue wrote:
Hi,

 I downloaded mysql-3.23.35.0  for AIX 4.3 from bullfreeware.com and as I soon
as I start mysqld, it starts consuming almost all the CPU (hovers around 97%).
Any idea what might be the cause of this??
Paul:

I assume this happens with no or minimal server activity. If yes, the binary you 
are using is probably not fully compatible with your libraries/kernel. You can 
either try another binary, or build from source.

If this is some retired AIX server that you are just trying to put to work, and 
there is no other strong attachment to AIX, it might be more cost effective to 
dig up an old or not so old x86 machine, put Linux on it, and then install MySQL 
. My expectation is that PII and above with 128 MB or higher will outperform 
most older AIX machines. As for the newer, my intuition tells few even 
multi-processor machines will be able to outdo a uniprocessor AMD 1800+ (those 
can do 8000 indexed selects of one row per second). If some AIX fan wants to 
challenge my intuition with a benchmark, you are more than welcome.



--
Sasha Pachev
Create online surveys at http://www.surveyz.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysqld-threads

[Sasha Pachev]

[EMAIL PROTECTED] wrote:
Hi folks!

I have diffrent linux-machines running with the great mysqld.

But on some machines I have 20 running threads on some other only 2
threads.
If only 2 threads are running is that not bad for fast sql-results?
I have about 500 v-hosts and many sql-queries on my machine.
Does somebody know how I can add more stand-by threads for my mysqlds
that mysqld become faster?
The number of stand-by threads is controlled by thread_cache_size parameter.

--
Sasha Pachev
Create online surveys at http://www.surveyz.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: mysql-3.23.35.0 on AIX 4.3: CPU Hog

[Paul Sue]

I think the problem was that the binary was indeed probably incompatible
with my libraries/kernel. I installed an IBM supplied version and it seems to
work OK now, albeit with some strange warning messages from time to time.

I would have prefered using an x86 platform myself, but we had all these old
RS/6000 boxes lying around :)

Thanks,

Paul

-Original Message-
From: Sasha Pachev [mailto:[EMAIL PROTECTED]
Sent: Tuesday, May 04, 2004 12:08 PM
To: Paul Sue
Cc: [EMAIL PROTECTED]
Subject: Re: mysql-3.23.35.0 on AIX 4.3: CPU Hog


Paul Sue wrote:
 Hi,
 
  I downloaded mysql-3.23.35.0  for AIX 4.3 from bullfreeware.com and as I soon
 as I start mysqld, it starts consuming almost all the CPU (hovers around 97%).
 
 Any idea what might be the cause of this??

Paul:

I assume this happens with no or minimal server activity. If yes, the binary you 
are using is probably not fully compatible with your libraries/kernel. You can 
either try another binary, or build from source.

If this is some retired AIX server that you are just trying to put to work, and 
there is no other strong attachment to AIX, it might be more cost effective to 
dig up an old or not so old x86 machine, put Linux on it, and then install MySQL 
. My expectation is that PII and above with 128 MB or higher will outperform 
most older AIX machines. As for the newer, my intuition tells few even 
multi-processor machines will be able to outdo a uniprocessor AMD 1800+ (those 
can do 8000 indexed selects of one row per second). If some AIX fan wants to 
challenge my intuition with a benchmark, you are more than welcome.



-- 
Sasha Pachev
Create online surveys at http://www.surveyz.com/



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

Re: Need correct 'order by' syntax where field does not contain NULL

[Michael Stassen]

Mike Johnson wrote:

From: Eve Atley [mailto:[EMAIL PROTECTED]

Hi. I had a MySQL DB set up and recently added a field 
'order' to allow for exceptions in a web site menu 
heirarchy. Fields should be ordered by 'order' field 
first where it does not contain 'NULL', and then by 
field 'title'. I had this previously:

select * from navigation WHERE id = '.$category.' AND 
active='y' ORDER BY title

...and now, when I put in:

select * from navigation WHERE id = '.$category.' AND 
active='y' ORDER BY order, title

...my menu shows nothing. How can I write my statement to 
allow for ORDER by order where 'order' does not contain 
'NULL', and then title?

snip
While it may not be the best way to go about it, give this a shot:

SELECT *, IF(`order` IS NULL, 99, `order`) AS sort_ord 
FROM navigation 
WHERE id = '.$category.' 
AND active='y' 
ORDER BY sort_ord ASC, title

This generates a temporary column in your result set that is used as the
sort order based on the values of 'order.' Note that if you use values
higher than 99 in 'order,' you'll want to set the 99 higher.
snip
mysql SELECT *, IF(`order` IS NULL, 99, `order`) AS sort_ord 
FROM test ORDER BY sort_ord ASC, title ASC;
+---+---+--+
| title | order | sort_ord |
+---+---+--+
| c | 1 |1 |
| f | 2 |2 |
| e | 3 |3 |
| a |  NULL |   99 |
| b |  NULL |   99 |
| d |  NULL |   99 |
+---+---+--+
6 rows in set (0.00 sec)

===

If someone knows a better way to do this, I'd be curious to hear it. I
imagine there's a more efficient way to do it, but I can't seem to stumble
upon it.
ASC (ascending) is the default order, so you can leave it out.  That's not 
better or more efficient, it just saves typing.

Instead of guessing a max value, such as 99, why not use the max value for 
the type of the column to be *sure* the NULLs come last.  So, use 127 if 
`order` is a tinyint, 255 for tinyint unsigned, and so on.  I'll assume 
`order` is a tinyint and use 127 in my example below.

You could accomplish the same thing without the extra sort_ord column by 
moving your IF clause to the ORDER BY clause, like this:

  SELECT * FROM test ORDER BY IF(`order` IS NULL, 127, `order`), title;
  +---+---+
  | title | order |
  +---+---+
  | c | 1 |
  | f | 2 |
  | e | 3 |
  | a |  NULL |
  | b |  NULL |
  | d |  NULL |
  +---+---+
That's not any better efficiency-wise, but it saves the extra output.

==

Is there a better way?  Probably.  I'd start by renaming the column to 
something that isn't a reserved word, menu_ord perhaps, so I wouldn't have 
to remember to use backticks.  Then I'd consider whether a different scheme 
of values in that column would work better.  In general, you want to avoid 
filtering and/or sorting on a function of a column, if possible, because 
then an index on the column can't be used.

You want a custom sort order which is almost, but not quite, alphabetical by 
title.  As I see it, you've created a new column to hold the desired 
ordering, but you're only partly using it.  Instead of checking for NULLs 
when you order your select, why not replace the NULLs with something useful 
ahead of time?  Assuming the number of exceptions to be sorted first is less 
than 255, I'd do the following:

  UPDATE navigation SET `order`=255 WHERE `order` IS NULL;
  ALTER TABLE navigation
CHANGE `order` menu_ord TINYINT UNSIGNED NOT NULL DEFAULT 255;
Then you can simply

  SELECT * FROM navigation
  WHERE id = '.$category.' AND active='y'
  ORDER BY menu_ord, title
Alternatively, if this table is relatively static, it may be practical to 
assign appropriate values to menu_ord for every row.  Then you could simply 
ORDER BY menu_ord.

Michael

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

Threads on FreeBSD 4.9

[Max Clark]

Can't create a new thread (errno 35). If you are not out of available 
memory, you can consult the manual for a possible OS-dependent bug

I am running FreeBSD 4.9 with Mysql 4.0.18 compiled with Linux Threads. 
I am running large inbound concurrency on Postfix which is forking 
several processes. How do I tune my mysql db servers to resolve this error?

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

Re: Threads on FreeBSD 4.9

[Jeremy Zawodny]

On Tue, May 04, 2004 at 12:36:19PM -0700, Max Clark wrote:

 Can't create a new thread (errno 35). If you are not out of
 available memory, you can consult the manual for a possible
 OS-dependent bug
 
 I am running FreeBSD 4.9 with Mysql 4.0.18 compiled with Linux
 Threads.  I am running large inbound concurrency on Postfix which is
 forking several processes. How do I tune my mysql db servers to
 resolve this error?

How large is kern.maxdsiz on that machine?

How many concurrent connections are you trying to use?

Jeremy
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
[EMAIL PROTECTED]  |  http://jeremy.zawodny.com/

[book] High Performance MySQL -- http://highperformancemysql.com/

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

how to uninstall mysql

[adam]

Hello.

I have installed MySql on RH Linux, however since I have installed in the
wrong location I would like to uninstall.

 

Basically I have installed the binary version from the
mysql-standard-4.0.18-pc-linux-i686.tar.gz and 

I have followed the instructions in the INSTALL-BINARY, in particular I have
run the scripts/mysql_install_db script.

 

My question is: how can I uninstall it?

(Is it just stop server instances and delete the directory ?) 

Regards, Alex

Backup strategy

[Ron Gilbert]

I am wondering what the best backup strategy is for my database.

The database is used to store a very large number of binary files, 
ranging from a few K to 20MB's.  The database stores thousands of these 
files.  I can not put this data on the file server, it needs to be in 
the database.

Currently the database is about 1.7GB's and will grow over time to 4GB 
or higher.  I created 20 identical tables to hold the binary data.  I 
was worried about the 4GB/Tables limit, so figured I would spread it out 
over several tables, also there is no a single point of failure for 
loosing all my data.

To do nightly backups (I don't need anything more frequent), I copy the 
whole database directory to another HD on the same server, then the 
files that changed are rsync'd to another server.  One of the reason 
that I store the data in several tables is so only the tables that 
changed need to be rsync'd to the other machine.  It is not on a local 
net, so it can take a while to do.

In any given day, only 10 or so binary files are added, so not a lot 
changes from day to day, but it can be one some days

When I move to 4.1 and start using InnoDB tables (or should I), will the 
same technique of copying the whole directory and sync'ing only that 
tables that changed still work?

Is there a better way to be doing this given the huge amount of binary 
data I have?

I am running MySQL v3.23.58, but will probably be upgrading to 4.1 if it 
makes sense.  This is running on RH9, but will be moved to a FreeBSD 
server in the near future.

Thanks, Ron



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

innodb_tablespace_monitor

[Lou Olsten]

From the docs, it says:

You can use innodb_tablespace_monitor to check the integrity of the file space 
management inside the tablespace files. 

Does this mean that it is part of the SHOW INNODB STATUS command or is this something 
separate?

Thanks,

Lou

InnoBD Index Fragmentation

[Lou Olsten]

From the docs: If there are random insertions into or deletions from the indexes of 
a table, the indexes may become fragmented.

How can I go about determining if my indexes are, in fact, fragmented?

Thanks,

Lou

INSERT INTO dropping slashes from strings

[Steve Pugh]

Every time I think I've got it, I am reminded thatI don't got it.

Hello all, once again!  Can anyone tell me why the following takes 
place?  In my VB app I am adding records to the table Jobs with this 
code (trimmed way down - my actual INSERT statement populates about 20 
fields):

sqlstr = INSERT INTO Jobs VALUES(  _
Chr(34)  txtSceneFile  Chr(34)  )
adocn.execute sqlstr

Now, let's say that my txtSceneFile contains 
C:\Data\test\foo\bar.lws.  My Jobs table will show the following for 
the applicable data field: C: Data.   This, my frields, is a new one 
to me, to be sure!   Any ideas?

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

Re: INSERT INTO dropping slashes from strings

[Daniel Clark]

I think you want chr(39) (single quote).


 Every time I think I've got it, I am reminded thatI don't got it.

 Hello all, once again!  Can anyone tell me why the following takes
 place?  In my VB app I am adding records to the table Jobs with this
 code (trimmed way down - my actual INSERT statement populates about 20
 fields):

 sqlstr = INSERT INTO Jobs VALUES(  _
  Chr(34)  txtSceneFile  Chr(34)  )

 adocn.execute sqlstr

 Now, let's say that my txtSceneFile contains
 C:\Data\test\foo\bar.lws.  My Jobs table will show the following for
 the applicable data field: C: Data.   This, my frields, is a new one
 to me, to be sure!   Any ideas?

 Many thanks,
Steve


 --
 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 INTO dropping slashes from strings

[Steve Pugh]

Hi Daniel,

I tried that to no avail (I had actually had the code set initially to 
use single quotes thusly:

sqlstr = INSERT INTO Jobs VALUES('  _
txtSceneFile  ')
but it generated the same erroneous string.  Using the ascii-code 
equivalent (Chr(34) does the same.

It's just soodd!!

Daniel Clark wrote:

I think you want chr(39) (single quote).

 

Every time I think I've got it, I am reminded thatI don't got it.

Hello all, once again!  Can anyone tell me why the following takes
place?  In my VB app I am adding records to the table Jobs with this
code (trimmed way down - my actual INSERT statement populates about 20
fields):
sqlstr = INSERT INTO Jobs VALUES(  _
Chr(34)  txtSceneFile  Chr(34)  )
adocn.execute sqlstr

Now, let's say that my txtSceneFile contains
C:\Data\test\foo\bar.lws.  My Jobs table will show the following for
the applicable data field: C: Data.   This, my frields, is a new one
to me, to be sure!   Any ideas?
Many thanks,
  Steve
   



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

Re: INSERT INTO dropping slashes from strings

[Steve Pugh]

Err...what I meant to say here was ascii code equivalent (chr(39) (I 
tried single *and* double quotes).  Sorry about that.

Steve Pugh wrote:

Hi Daniel,

I tried that to no avail (I had actually had the code set initially to 
use single quotes thusly:

sqlstr = INSERT INTO Jobs VALUES('  _
txtSceneFile  ')
but it generated the same erroneous string.  Using the ascii-code 
equivalent (Chr(34) does the same.


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

Re: INSERT INTO dropping slashes from strings

[Daniel Clark]

Check the table sturcture for required fields and EXACT field names.  
mysql is case sensitive.

 I tried that to no avail (I had actually had the code set initially to
 use single quotes thusly:

 sqlstr = INSERT INTO Jobs VALUES('  _
  txtSceneFile  ')

 but it generated the same erroneous string.  Using the ascii-code
 equivalent (Chr(34) does the same.

 It's just soodd!!

 Daniel Clark wrote:

I think you want chr(39) (single quote).


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

Re: INSERT INTO dropping slashes from strings

[Daniel Clark]

How about:  (I reversed on set of quotes)

sqlstr = INSERT INTO Jobs VALUES('  txtSceneFile  ')

 Err...what I meant to say here was ascii code equivalent (chr(39) (I
 tried single *and* double quotes).  Sorry about that.

 Steve Pugh wrote:

 Hi Daniel,

 I tried that to no avail (I had actually had the code set initially to
 use single quotes thusly:

 sqlstr = INSERT INTO Jobs VALUES('  _
 txtSceneFile  ')

 but it generated the same erroneous string.  Using the ascii-code
 equivalent (Chr(34) does the same.


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

Re: INSERT INTO dropping slashes from strings

[Steve Pugh]

Serves me right to type this stuff instead of copying/pasting - my 
previous post was indeed wrong but also wasn't the exact code I used 
(yours was, with the single quote embedded in the doubles).

It's been a very dyslexic week for me.

On your previous response regarding field names and case sensitivity, 
I'm not actually referencing any field names in my INSERT INTO query - 
I'm just populating all of the fields in order, and the order is correct 
from what I can tell.  The other fields are solid (once I got past a 
poorly formatted date field, that is) but these strings with the 
backslashes stripping out and parts lopping off, that's the last rub.



Daniel Clark wrote:

How about:  (I reversed on set of quotes)

sqlstr = INSERT INTO Jobs VALUES('  txtSceneFile  ')

 

Err...what I meant to say here was ascii code equivalent (chr(39) (I
tried single *and* double quotes).  Sorry about that.
Steve Pugh wrote:

   

Hi Daniel,

I tried that to no avail (I had actually had the code set initially to
use single quotes thusly:
sqlstr = INSERT INTO Jobs VALUES('  _
   txtSceneFile  ')
but it generated the same erroneous string.  Using the ascii-code
equivalent (Chr(34) does the same.
 



 



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

Re: INSERT INTO dropping slashes from strings

[Dan Nelson]

In the last episode (May 04), Steve Pugh said:
 Hello all, once again!  Can anyone tell me why the following takes
 place?  In my VB app I am adding records to the table Jobs with
 this code (trimmed way down - my actual INSERT statement populates
 about 20 fields):
 
 sqlstr = INSERT INTO Jobs VALUES(  _
 Chr(34)  txtSceneFile  Chr(34)  )
 
 adocn.execute sqlstr
 
 Now, let's say that my txtSceneFile contains
 C:\Data\test\foo\bar.lws.  My Jobs table will show the following
 for the applicable data field: C: Data.  This, my frields, is a new
 one to me, to be sure!  Any ideas?

You need to escape all quotes, apostrophes, and backslashes, or use
bind variables and let ODBC handle the escaping for you.  Consider what
happens with a filename like

C:\temp\My filename's got quotes in it.doc

See http://dev.mysql.com/doc/mysql/en/String_syntax.html for more info.

-- 
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: INSERT INTO dropping slashes from strings

[Daniel Clark]

Hum. I've never tried inserting without the field names.

 Serves me right to type this stuff instead of copying/pasting - my
 previous post was indeed wrong but also wasn't the exact code I used
 (yours was, with the single quote embedded in the doubles).

 It's been a very dyslexic week for me.

 On your previous response regarding field names and case sensitivity,
 I'm not actually referencing any field names in my INSERT INTO query -
 I'm just populating all of the fields in order, and the order is correct
 from what I can tell.  The other fields are solid (once I got past a
 poorly formatted date field, that is) but these strings with the
 backslashes stripping out and parts lopping off, that's the last rub.


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

Re: INSERT INTO dropping slashes from strings

[Steve Pugh]

Ah!!

I was tripped up by the fact that things were fine when I was doing an 
ADO recordset.addnew, recordset!Fieldname = variable, 
recordset.Update kind of approach.  Switching to an INSERT query, and 
sure enough the backslashes need a little escape.

Don't we all need a little escape every now and then?

Thank you Dan!

Dan Nelson wrote:

In the last episode (May 04), Steve Pugh said:
 

Hello all, once again!  Can anyone tell me why the following takes
place?  In my VB app I am adding records to the table Jobs with
this code (trimmed way down - my actual INSERT statement populates
about 20 fields):
sqlstr = INSERT INTO Jobs VALUES(  _
   Chr(34)  txtSceneFile  Chr(34)  )
adocn.execute sqlstr

Now, let's say that my txtSceneFile contains
C:\Data\test\foo\bar.lws.  My Jobs table will show the following
for the applicable data field: C: Data.  This, my frields, is a new
one to me, to be sure!  Any ideas?
   

You need to escape all quotes, apostrophes, and backslashes, or use
bind variables and let ODBC handle the escaping for you.  Consider what
happens with a filename like
C:\temp\My filename's got quotes in it.doc

See http://dev.mysql.com/doc/mysql/en/String_syntax.html for more info.

 



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

GRANT to DB access

[Ron Gilbert]

Is there a way to create a GRANT for a DB so that only one user can
access to the database?  The only way I can see to do it involves taking
every user and GRANT them access to every other database, but not this
one.
The problem I face is that I share a server with three friends, and we
all create databases on the server, so everyone needs general super
user privileges.
I want to be able to create a database and keep the others from
accidentally accessing it.  Obviously they can just change the GRANTs if
they really want to get to it, this is really to keep accidents from
happening.
Does any of this make sense?  Am I missing something obvious?

Ron





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

MySQL hotcopy problem (poor man's replication)

[Andrew Loughe]

Hello,

MySQL version: 3.23.58

Each week I make a hotcopy of numerous database files.
I tar them and gzip them, and move them to an archive machine.
I then use a second machine to read from the archive, untar and ungzip
the files, and presto! I have exact copies of my databases running on
a backup server.
I even prove that the MyISAM files are the same using MD5 checksums.

Then why do I get different results from the queries?

The copied tables are sometimes missing weeks worth of data, even
though the MyISAM files appear to be identical.  Strange, eh!?
-Andy

--
Andrew Loughe =
NOAA/OAR/FSL/AD   R/FS5  |  email: [EMAIL PROTECTED]
325 Broadway |  wwweb: www-ad.fsl.noaa.gov/users/loughe
Boulder, CO  80305-3328  |  phone: 303-497-6211   fax: 303-497-6301
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: MySQL hotcopy problem (poor man's replication)

[Chris Hellberg]

Are you dropping then recreating the tables and data, or just doing a
diff between what's on the backup and what's in the zip file and
updating the difference?

I've been doing a drop on the tables and recreating them and it seems to
work ok.

 -Original Message-
 From: 
 [EMAIL PROTECTED]
 om 
 [mailto:[EMAIL PROTECTED]
 .mysql.com] On Behalf Of Andrew Loughe
 Sent: Wednesday, 5 May 2004 1:09 p.m.
 To: [EMAIL PROTECTED]
 Subject: MySQL hotcopy problem (poor man's replication)
 
 
 Hello,
 
 MySQL version: 3.23.58
 
 Each week I make a hotcopy of numerous database files.
 I tar them and gzip them, and move them to an archive machine.
 
 I then use a second machine to read from the archive, untar and ungzip
 the files, and presto! I have exact copies of my databases running on
 a backup server.
 
 I even prove that the MyISAM files are the same using MD5 checksums.
 
 Then why do I get different results from the queries?
 
 The copied tables are sometimes missing weeks worth of data, even
 though the MyISAM files appear to be identical.  Strange, eh!?
 
 -Andy
 
 -- 
 Andrew Loughe =
 NOAA/OAR/FSL/AD   R/FS5  |  email: [EMAIL PROTECTED]
 325 Broadway |  wwweb: www-ad.fsl.noaa.gov/users/loughe
 Boulder, CO  80305-3328  |  phone: 303-497-6211   fax: 303-497-6301
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/mysql? [EMAIL PROTECTED]
 
 
 

--
This communication, including any attachments, is confidential. 
If you are not the intended recipient, you should not read
it - please contact me immediately, destroy it, and do not
copy or use any part of this communication or disclose
anything about it. Thank you. Please note that this 
communication does not designate an information system for
 the purposes of the Electronic Transactions Act 2002.
--


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

Re: MySQL hotcopy problem (poor man's replication)

[Paul DuBois]

At 19:08 -0600 5/4/04, Andrew Loughe wrote:
Hello,

MySQL version: 3.23.58

Each week I make a hotcopy of numerous database files.
I tar them and gzip them, and move them to an archive machine.
I then use a second machine to read from the archive, untar and ungzip
the files, and presto! I have exact copies of my databases running on
a backup server.
I even prove that the MyISAM files are the same using MD5 checksums.

Then why do I get different results from the queries?

The copied tables are sometimes missing weeks worth of data, even
though the MyISAM files appear to be identical.  Strange, eh!?
Random guess:
Are you copying both the data (.MYD) and index (.MYI) files?
If you set up the complete set of files initially, but then only
copy the data files afterward, you'll be using index files that
don't match the data files.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

priviledges newbie

[Fajar Priyanto]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Hi all,
Can I create a user that can create and drop his database while at the same 
time can not drop databases that are not belong to him?
How do I set this priviledge?
TIA,
- -- 
Fajar Priyanto | Reg'd Linux User #327841 | http://linux.arinet.org
09:01:15 up 56 min, Mandrake Linux release 9.2 (FiveStar) for i586 
public key: https://www.arinet.org/fajar-pub.key
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFAmE12kp5CsIXuxqURAvJ6AJsEzsyjYwb42RJztTkxese1+AMifACaAtZm
2fVGYvRxTcNr7SB1dlbP1Tg=
=mEKb
-END PGP SIGNATURE-


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

Re: Export query to text file

[Yingyos]

Victor Pendleton wrote:

The `INTO OUTFILE` clause is expecting a table reference. An alternatvie is
mysql -uuser -N -eselect now()  sample2.txt
-Original Message-
From: Yingyos
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: 5/4/04 2:20 AM
Subject: Export query to text file
Hi,

I have MySQL 4.0.17 on Windows XP.
I use SELECT ... INTO OUTFILE print out query to text file.
If i write this command.
mysqlSELECT * from tbl1 INTO OUTFILE C:\\Query\\sample1.txt;

MySQL reponse OK.But i change command.

mysqlSELECT now() INTO OUTFILE C:\\Query\\sample2.txt; 

MySQL response by exit from console.

How's to use SELECT ... INTO OUTFILE with datetime or another function.

Thank for the reply,
Yingyos Santiprasert




 

Hi Victor Pendleton ,

I want format on text file .

mysql select date_format('2004-02-29','%X');
++
| date_format('2004-02-29','%X') |
++
| 2004   |
++
1 row in set (0.00 sec)
How 's to do?

Thank you for reply again,
Yingyos  Santipasert




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

Re: [PHP] Plz help quick - mysql/php/web server undefined function all of a sudden

[Ludwig Pummer]

Chip Wiegand wrote:

John,
Yep, looking at phpinfo.php shows no support for mysql. This is very 
strange. I know these things don't just happen by themselves. I also know 
there are only two people with the password to the server, myself and my 
boss (and he knows nothing about the server to begin with).
Anyway, looks like php needs to be configured to work with mysql. Now that 
it is the way it is, how do I go about that? According to pkg_info the 
version of php is 4.3.4_3. 
Being a port install how do I configure it to use mysql?
Thanks,
Chip
 

If you install it using the ports collection and not using any sort of 
automation tool like portupgrade, it should have a text-mode 
configurator which will give you the option of MySQL support if you 
enable the checkbox. You may need to 'make clean' in the port directory 
before running 'make' again to force it to give you the menu. It may 
fail to work if MySQL or Apache were not installed from the ports 
collection to start with, since the PHP port makes some assumptions 
about the paths to Apache and MySQL.

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