Error compiling 3.23.54a (errno)
While trying to upgrade, i ran into this compiler error: /bin/sh ../libtool --mode=link g++ -O3 -DDBUG_OFF -O2 -march=pentium4 -mcpu=pentium4 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -fno-rtti -fno-exceptions -fno-implicit-templates -fno-exceptions -fno-rtti -rdynamic -o mysql mysql.o readline.o sql_string.o completion_hash.o -lreadline -lncurses ../libmysql/libmysqlclient.la -lz -lcrypt -lnsl -lm g++ -O3 -DDBUG_OFF -O2 -march=pentium4 -mcpu=pentium4 -msse2 -D_GNU_SOURCE -D_FILE_OFFSET_BITS=64 -D_LARGEFILE_SOURCE -fno-rtti -fno-exceptions -fno-implicit-templates -fno-exceptions -fno-rtti -rdynamic -o .libs/mysql mysql.o readline.o sql_string.o completion_hash.o -lreadline -lncurses ../libmysql/.libs/libmysqlclient.so -lz -lcrypt -lnsl -lm -Wl,--rpath -Wl,/usr/lib/mysql mysql.o(.text+0x374e): In function `com_source(String*, char*)': : undefined reference to `errno' This is on a 'plain' rh8 + updates. Previous versions compiled without such errors.. Anyone know whats up with this? Any easy fixes? -- Chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL 3.23.54 for Solaris 7
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Mon, 16 Dec 2002, Nick wrote: I am running the Solaris 8 binary on all of my Ultra Sparc II Solaris 7 servers with no problems at this time, however I am not doing any of the advanced features, such as MySQL-Max or replication. Thanks for the hint! I was not sure, that this actually worked, but it's good to know! Bye, LenZ - -- For technical support contracts, visit https://order.mysql.com/?ref=mlgr __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mr. Lenz Grimmer [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Production Engineer /_/ /_/\_, /___/\___\_\___/ Hamburg, Germany ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.0 (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQE9/udySVDhKrJykfIRAqMNAKCCK+5TSOhcnItAqAK8+wR/bis6hACggGtu qnQGD8qN83SQ+lwoA1wWbas= =KFnI -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
sql query LOAD DATA INFILE question
sql,query Hi, I am looking at the following situation: I am reading some files arriving every minute and parsing them and creating a set of files ready to be inserted into tables. on the fly. While I am waiting for the next burst of files, I want to insert these into the tables, then erase the files. Normally LOAD DATA INFILE LOCAL works fine here. The problem is that the machine holding these tables is a different one. The question is, is it possible to run LOAD DATA INFILE to do the inserts on the remote machine, or is it better to first ftp the files over, then run LOAD DATA INFILE there? I assume LOAD INFILE is faster than via mysql -u user -ppasswd DBfile.sql where file.sql contains sql INSERT query From the LOAD DATA INFILE documentation it is not clear to me how this can be done, if it can be done. Thanks, - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: How can I duplicate a mysql template database?
Thanks for the reply Bill, however this did not solve the problem. I
still receive the same error with your method as well.
1064: You have an error in your SQL syntax near ';
CREATE TABLE Departments (
DeptID int(10) unsigned NOT NULL auto_increment,' at line 4
Are you first creating a new database first with mysql_create_db()
before trying to run your schema? What error message do you get? The
code you've posted is only creating a table.
Yes. I am:
$V2DB = V2_SL.$CompanyID;
$result = mysql_create_db($V2DB, $linkI);
mysql_select_db ($V2DB, $linkI) or die (Could not select .$V2DB.
Database);
Also, rather than opening and reading in an external file or
doing all
those $sql .= lines, you might have an easier time using
heredoc syntax,
which would let you do something like
$sql = SQL
CREATE TABLE IP_Dept (
IP_Addr int(10) unsigned NOT NULL default
DeptID int(10) unsigned NOT NULL default
...etc
SQL;
$result = mysql_query($sql,$linkI);
Thanks for that tip, however it fails the same as opening the file and
same as $sql .=
(hopefully $linkI is your mysql connection) This way you
don't have to bother with all that quoting.
Yes, $linkI is my connection identifier.
I just have an awful feeling that PHP/mySQL won't let me stack commands
like that because if I just do one table, like this:
$sql = SQL
CREATE TABLE Schedule (
ScheduleID int(10) unsigned NOT NULL auto_increment,
TargetRange char(255) default NULL,
ScannerID int(10) unsigned NOT NULL default '0',
RunEvery char(50) default NULL,
NextRun datetime default NULL,
LastRun datetime default NULL,
PRIMARY KEY (ScheduleID)
) TYPE=MyISAM;
SQL;
It works. However I'm dreading doing this one table at a time. Grr.
Daevid Vincent wrote:
I need to use PHP to duplicate the schema of a mysql database. This
seems like it should be a simple task -- taking a 'template' db and
cloning it with a new name.
I've tried things like:
$filename = myDB.sql;
$fd = fopen ($filename, r);
$sql = fread ($fd, filesize ($filename));
fclose ($fd);
And
$lines = file($filename);
foreach ($lines as $line_num = $line) { $sql .= $line;
}
And
$sql .= CREATE TABLE IP_Dept (;
$sql .= IP_Addr int(10) unsigned NOT NULL default
'0',;
$sql .= DeptID int(10) unsigned NOT NULL default
'0';
$sql .= );;
$sql .= CREATE TABLE ResolveTable (;
$sql .= IP_Addr int(10) unsigned NOT NULL default
'0',;
$sql .= Name char(255) NOT NULL default '',;
$sql .= Custom char(1) default NULL,;
$sql .= Global char(1) default 'Y',;
$sql .= OSVersion char(255) default NULL,;
$sql .= RowID int(10) unsigned NOT NULL
auto_increment,;
$sql .= Display enum('Yes','No') NOT NULL default
'Yes',;
$sql .= PRIMARY KEY (RowID);
$sql .= );;
echo PRE.$sql./PREP;
$result = mysql_query($sql,$linkI);
But ALL of them fail! Ugh!!! Can I not stack commands like that? Is
there some way to read in a .sql file via PHP? The problem
is that my
web pages are on a web server and the db is on a mysql
server which are
different machines, so calling a system() or other execute style
function won't work for me.
I figured, Ah! Why don't I just make a template db on the
server and
issue some SQL command to 'clone' that and rename it.
You'd think that
was pretty straight forward, but I can't find any examples
or commands
to do this seemingly trivial task. Ideas?
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try:
http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql MAX client
Dear All, Where can i find mysql client and devel .rpm files compatible for MySQL-Max-3.23.53a-1.i386.rpm Thanks in advance. Regards, Muruganandam g - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Mysql - Upgrading from 3.23.53 to 3.23.54
Please, anybody could help me with this? Anybody have upgraded its MySQL release from 3.23.53 to Mysql release 3.23.54... I need to upgrade for fixing a bug in 3.23.53 and I would like to know if it necesary to reinstall as the one solution. Thanks a lot. - Original Message - From: Santiago Alba [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, December 16, 2002 3:24 PM Subject: Mysql - Upgrading from 3.23.53 to 3.23.54 Hi, I have installed MySQL 2.23.53 release and I want to upgrade to MySQL 2.23.54... how can I do this? Have i got to reinstall? Is there any script? Any suggest? Thanks in advance - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL optimization
Hi, It seems that you don't have any index on your tables. You should at least create an index on flObjectID in all tables. You should also use 'explain' on your query to make sure that indexes are properly used. Hope this helps Joseph Bueno John Glenn wrote: I'm looking for recomendations on improving system performance. Using the SQL command below on a MySQL server I find result times averaging three seconds. Being very inexperienced with database programming I would appreciate any comments on whether this is expected behaviour, or where my design might improve. The details of my system are below. The data I'm looking for starts with tblItems which holds a list of Items we're looking to buy. I want the name of the item from tblStock, and statistics on prices we've found in our history (if they exist). I've never done a multi table join before and this is what I've come up with: 300 Mhz, 32 MB RAM CPU: 65% idle, RAM: 12MB Free OS: Slackware Linux 8.1 (2.4.18) Mysqld Ver 3.23.53a for pc-linux-gnu on i686 mysql select fldName, fldQuantity, fldTotalCost, tblntItems.fldObjectID, avg(fldQuote), count(fldQuote), max(fldQuote), min(fldQuote) from tblStock, tblntItems left join tblntQuotes on tblntItems.fldObjectID = tblntQuotes.fldObjectID where tblntItems.fldObjectID = tblStock.fldObjectID group by fldObjectID; mysql show fields from tblStock; +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | fldObjectID | int(11) | YES | | NULL| | | fldName | varchar(255) | YES | | NULL| | | fldCost | int(11) | YES | | NULL| | | fldRetail | int(11) | YES | | NULL| | | fldUpdate | varchar(255) | YES | | NULL| | +-+--+--+-+-+---+ 5 rows in set (0.00 sec) aprox 7000 items. mysql show fields from tblntQuotes; +-+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-+---+ | fldObjectID | int(11) | YES | | NULL| | | fldDate | date| YES | | NULL| | | fldSource | int(11) | YES | | NULL| | | fldQuote| double | YES | | NULL| | +-+-+--+-+-+---+ 4 rows in set (0.00 sec) approx 130 records. mysql show fields from tblntItems; +--+-+--+-+-+---+ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-+---+ | fldObjectID | int(11) | YES | | NULL| | | fldQuantity | int(11) | YES | | NULL| | | fldTotalCost | double | YES | | NULL| | +--+-+--+-+-+---+ 3 rows in set (0.00 sec) approx 100 records. Regards, John Glenn - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: How can I duplicate a mysql template database? [hack]
Seems to me there should be a built in SQL command to duplicate a
database. Jeepers. Or to read in a .sql file from PHP and create a
database out of it (which was the original intent).
Anyways, here's a 'hack'. I'd still love to hear anyone else's more
elegant solution.
$V2DB = V2_SL.$CompanyID;
$result = mysql_create_db($V2DB, $linkI);
if (!$result) $errorstring .= Error creating .$V2DB.
databaseBR\n.mysql_errno($linkI).: .mysql_error($linkI).BR\n;
mysql_select_db ($V2DB, $linkI) or die (Could not select .$V2DB.
Database);
/*
//TODO: None of these below here work. Ugh! so frustrating!!
//$filename = /mypath/todb/V2_DB.sql;
//$fd = fopen ($filename, r);
//$sql = fread ($fd, filesize ($filename));
//fclose ($fd);
//$lines = file($filename);
//foreach ($lines as $line_num = $line) { $sql .= $line; }
//$sqlTables = explode(;,$sql);
//foreach ($sqlTables as $table)
//{
// echo PRE$table/PREphr\n;
// $result = mysql_query($sql,$linkI);
// if (!$result) $errorstring .= Error creating .$V2DB.
.$table. tableBR\n.mysql_errno($linkI).:
.mysql_error($linkI).BR\n;
//}
*/
//You must have already created the V2_Template database.
//This will make a clone of it, including data.
$tableResult = mysql_list_tables (V2_Template);
while ($row = mysql_fetch_row($tableResult))
{
$tsql = CREATE TABLE .$V2DB...$row[0]. AS SELECT * FROM
V2_Template..$row[0];
echo $tsql.BR\n;
$tresult = mysql_query($tsql,$linkI);
if (!$tresult) $errorstring .= Error creating
.$V2DB...$row[0]. tableBR\n.mysql_errno($linkI).:
.mysql_error($linkI).BR\n;
}
-Original Message-
From: Daevid Vincent [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, December 17, 2002 1:16 AM
To: [EMAIL PROTECTED]
Cc: 'Bill Lovett'
Subject: RE: How can I duplicate a mysql template database?
Thanks for the reply Bill, however this did not solve the problem. I
still receive the same error with your method as well.
1064: You have an error in your SQL syntax near ';
CREATE TABLE Departments (
DeptID int(10) unsigned NOT NULL auto_increment,' at line 4
Are you first creating a new database first with mysql_create_db()
before trying to run your schema? What error message do you
get? The
code you've posted is only creating a table.
Yes. I am:
$V2DB = V2_SL.$CompanyID;
$result = mysql_create_db($V2DB, $linkI);
mysql_select_db ($V2DB, $linkI) or die (Could not select .$V2DB.
Database);
Also, rather than opening and reading in an external file or
doing all
those $sql .= lines, you might have an easier time using
heredoc syntax,
which would let you do something like
$sql = SQL
CREATE TABLE IP_Dept (
IP_Addr int(10) unsigned NOT NULL default
DeptID int(10) unsigned NOT NULL default
...etc
SQL;
$result = mysql_query($sql,$linkI);
Thanks for that tip, however it fails the same as opening the file and
same as $sql .=
(hopefully $linkI is your mysql connection) This way you
don't have to bother with all that quoting.
Yes, $linkI is my connection identifier.
I just have an awful feeling that PHP/mySQL won't let me
stack commands
like that because if I just do one table, like this:
$sql = SQL
CREATE TABLE Schedule (
ScheduleID int(10) unsigned NOT NULL auto_increment,
TargetRange char(255) default NULL,
ScannerID int(10) unsigned NOT NULL default '0',
RunEvery char(50) default NULL,
NextRun datetime default NULL,
LastRun datetime default NULL,
PRIMARY KEY (ScheduleID)
) TYPE=MyISAM;
SQL;
It works. However I'm dreading doing this one table at a time. Grr.
Daevid Vincent wrote:
I need to use PHP to duplicate the schema of a mysql
database. This
seems like it should be a simple task -- taking a
'template' db and
cloning it with a new name.
I've tried things like:
$filename = myDB.sql;
$fd = fopen ($filename, r);
$sql = fread ($fd, filesize ($filename));
fclose ($fd);
And
$lines = file($filename);
foreach ($lines as $line_num = $line) { $sql .= $line;
}
And
$sql .= CREATE TABLE IP_Dept (;
$sql .= IP_Addr int(10) unsigned NOT NULL default
'0',;
$sql .= DeptID int(10) unsigned NOT NULL default
'0';
$sql .= );;
$sql .= CREATE TABLE ResolveTable (;
$sql .= IP_Addr int(10) unsigned NOT NULL default
'0',;
$sql .= Name char(255) NOT NULL default '',;
$sql .= Custom char(1) default NULL,;
$sql .= Global char(1) default 'Y',;
$sql .= OSVersion char(255) default NULL,;
$sql .= RowID int(10) unsigned NOT NULL
auto_increment,;
$sql .= Display enum('Yes','No') NOT NULL default
'Yes',;
$sql .= PRIMARY KEY (RowID);
$sql .= );;
echo
Re: How can I duplicate a mysql template database?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Daevid -- ...and then Daevid Vincent said... % % Thanks for the reply Bill, however this did not solve the problem. I % still receive the same error with your method as well. % % 1064: You have an error in your SQL syntax near '; % CREATE TABLE Departments ( % DeptID int(10) unsigned NOT NULL auto_increment,' at line 4 You mentioned a echo PRE.$sql./PREP; in your first post; what do you see? % % Are you first creating a new database first with mysql_create_db() % before trying to run your schema? What error message do you get? The % code you've posted is only creating a table. % % Yes. I am: % % $V2DB = V2_SL.$CompanyID; % $result = mysql_create_db($V2DB, $linkI); % mysql_select_db ($V2DB, $linkI) or die (Could not select .$V2DB. % Database); That's good, and we'll assume for the moment that your script doesn't error through to die but instead makes a successful connection. By the way, I see you quoting around your variables like print This is . $var . here ; and should note that that's way much extra work. I just tested (to be sure ;-) and $v = variable ; print print : this includes $v right herebr\n ; echo echo : this includes $v right herebr\n ; print print : this quotes . $v . for mebr\n ; echo echo : this quotes . $v . for mebr\n ; die (die : This is $v for youbr\n) ; happily gives me print : this includes variable right here echo : this includes variable right here print : this quotes variable for me echo : this quotes variable for me die : This is variable for you as it oughta. But I digress... % ... % heredoc syntax, % which would let you do something like % % $sql = SQL ... % % Thanks for that tip, however it fails the same as opening the file and % same as $sql .= How does it fail? Give us more detail. % % (hopefully $linkI is your mysql connection) This way you % don't have to bother with all that quoting. % % Yes, $linkI is my connection identifier. % % I just have an awful feeling that PHP/mySQL won't let me stack commands % like that because if I just do one table, like this: % % $sql = SQL % CREATE TABLE Schedule ( ... % SQL; % % It works. However I'm dreading doing this one table at a time. Grr. No, that doesn't make sense. It shouldn't matter how you create and populate the variable, as long as the end result content is the same. Since it works when you lay it out manually, it seems very probable that your filling it out in steps has some problems. Show us what you get when you print your $sql statement so that we can debug actual practice rather than theory ;-) HTH HAND mysql query, :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://www.justpickone.org/davidtg/Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE9/v9fGb7uCXufRwARApqsAJ0dKO9KUBgdxGUUZALfyCYtXnZeXACgntaM Go7VwkhEHVLAoqFUWLjayK4= =l7G9 -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How can I duplicate a mysql template database? [hack]
Hi,
There is an easier way to duplicate a database; from the comand
line, you can run:
mysqladmin create db2
mysqldump db1 | mysql db2
If you want to duplicate the schema only (without the data):
mysqladmin create db2
mysqldump --no-data db1 | mysql db2
(You may have to add host/user/password options but I just wanted to
show the idea).
Hope this helps,
Joseph Bueno
Daevid Vincent wrote:
Seems to me there should be a built in SQL command to duplicate a
database. Jeepers. Or to read in a .sql file from PHP and create a
database out of it (which was the original intent).
Anyways, here's a 'hack'. I'd still love to hear anyone else's more
elegant solution.
$V2DB = V2_SL.$CompanyID;
$result = mysql_create_db($V2DB, $linkI);
if (!$result) $errorstring .= Error creating .$V2DB.
databaseBR\n.mysql_errno($linkI).: .mysql_error($linkI).BR\n;
mysql_select_db ($V2DB, $linkI) or die (Could not select .$V2DB.
Database);
/*
//TODO: None of these below here work. Ugh! so frustrating!!
//$filename = /mypath/todb/V2_DB.sql;
//$fd = fopen ($filename, r);
//$sql = fread ($fd, filesize ($filename));
//fclose ($fd);
//$lines = file($filename);
//foreach ($lines as $line_num = $line) { $sql .= $line; }
//$sqlTables = explode(;,$sql);
//foreach ($sqlTables as $table)
//{
//echo PRE$table/PREphr\n;
//$result = mysql_query($sql,$linkI);
//if (!$result) $errorstring .= Error creating .$V2DB.
.$table. tableBR\n.mysql_errno($linkI).:
.mysql_error($linkI).BR\n;
//}
*/
//You must have already created the V2_Template database.
//This will make a clone of it, including data.
$tableResult = mysql_list_tables (V2_Template);
while ($row = mysql_fetch_row($tableResult))
{
$tsql = CREATE TABLE .$V2DB...$row[0]. AS SELECT * FROM
V2_Template..$row[0];
echo $tsql.BR\n;
$tresult = mysql_query($tsql,$linkI);
if (!$tresult) $errorstring .= Error creating
.$V2DB...$row[0]. tableBR\n.mysql_errno($linkI).:
.mysql_error($linkI).BR\n;
}
-Original Message-
From: Daevid Vincent [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, December 17, 2002 1:16 AM
To: [EMAIL PROTECTED]
Cc: 'Bill Lovett'
Subject: RE: How can I duplicate a mysql template database?
Thanks for the reply Bill, however this did not solve the problem. I
still receive the same error with your method as well.
1064: You have an error in your SQL syntax near ';
CREATE TABLE Departments (
DeptID int(10) unsigned NOT NULL auto_increment,' at line 4
Are you first creating a new database first with mysql_create_db()
before trying to run your schema? What error message do you
get? The
code you've posted is only creating a table.
Yes. I am:
$V2DB = V2_SL.$CompanyID;
$result = mysql_create_db($V2DB, $linkI);
mysql_select_db ($V2DB, $linkI) or die (Could not select .$V2DB.
Database);
Also, rather than opening and reading in an external file or
doing all
those $sql .= lines, you might have an easier time using
heredoc syntax,
which would let you do something like
$sql = SQL
CREATE TABLE IP_Dept (
IP_Addr int(10) unsigned NOT NULL default
DeptID int(10) unsigned NOT NULL default
...etc
SQL;
$result = mysql_query($sql,$linkI);
Thanks for that tip, however it fails the same as opening the file and
same as $sql .=
(hopefully $linkI is your mysql connection) This way you
don't have to bother with all that quoting.
Yes, $linkI is my connection identifier.
I just have an awful feeling that PHP/mySQL won't let me
stack commands
like that because if I just do one table, like this:
$sql = SQL
CREATE TABLE Schedule (
ScheduleID int(10) unsigned NOT NULL auto_increment,
TargetRange char(255) default NULL,
ScannerID int(10) unsigned NOT NULL default '0',
RunEvery char(50) default NULL,
NextRun datetime default NULL,
LastRun datetime default NULL,
PRIMARY KEY (ScheduleID)
) TYPE=MyISAM;
SQL;
It works. However I'm dreading doing this one table at a time. Grr.
Daevid Vincent wrote:
I need to use PHP to duplicate the schema of a mysql
database. This
seems like it should be a simple task -- taking a
'template' db and
cloning it with a new name.
I've tried things like:
$filename = myDB.sql;
$fd = fopen ($filename, r);
$sql = fread ($fd, filesize ($filename));
fclose ($fd);
And
$lines = file($filename);
foreach ($lines as $line_num = $line) { $sql .= $line;
}
And
$sql .= CREATE TABLE IP_Dept (;
$sql .= IP_Addr int(10) unsigned NOT NULL default
'0',;
$sql .= DeptID int(10) unsigned NOT NULL default
'0';
$sql .= );;
$sql .= CREATE TABLE ResolveTable (;
$sql .= IP_Addr int(10) unsigned NOT NULL default
'0',;
$sql .= Name char(255) NOT NULL default '',;
$sql .= Custom char(1) default NULL,;
$sql .= Global char(1) default 'Y',;
Re: global autocommit setting
Hi! Heikki Tuuri writes: Jari, I am Cc:ing this to [EMAIL PROTECTED], so that other readers can follow the discussion. There is still no my.cnf option to set AUTOCOMMIT=0 globally though users have requested it a lot. I think that 4.1 will support a SQL script which is automatically executed for all new connections. That is naturally the most versatile method of session initialization. Then you could write the SQL command SET AUTOCOMMIT=0 to that script. I am Cc:ing this to the MySQL AB developer who is responsible for global variables. He can inform us of the status of 4.1. Some news: - AUTOCOMMIT will become a global option in 4.0 and 4.1 within one month. Right now it is only available in session mode. - The automatically executed SQL script option will appear in 4.1 within two months. - mysql client will soon (~ within two weeks) accept --init-command=... option, which can be used in this case too. Example: mysql --init-command=SET AUTOCOMMIT=0. This will be available in 4.1 However, you can probably already do this in your client; just tell your client to read options additionally from a special group, for example [my_group]. A Perl client could do it like this for example: $dbh= DBI-connect(DBI:mysql:test . ;mysql_read_default_file=/path/to/my/defaut/options/file . ;mysql_read_default_group=my_group) In file '/path/to/my/defaut/options/file', within the group [my_group], add this: -- [my_group] init-command=SET AUTOCOMMIT=0 -- This will execute the above command every time your client connects, or reconnects to server. Search documentation about 'mysql_options()' C API function for more details. If the 4.1 SQL script method will be delayed, then I have to consider adding a new option autocommit=0 Regards, - Jani For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Jani Tolonen [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: server-bin files
On Monday 16 December 2002 22:37, Bc. Radek Krejèa wrote: I found SERVER-bin.* files from today in my MySQL directory, where are databases stored. What are theese files and why was created. SERVER-bin.001 SERVER-bin.002 SERVER-bin.index These files are binary log files: http://www.mysql.com/doc/en/Binary_log.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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: NOW()-TIMESTAMP does not return accurate results
On Tuesday 17 December 2002 09:39, Troy Kruthoff wrote: Invalid reporting of date calc How-To-Repeat: note: SESSIONTS is TIMESTAMP type SELECT (NOW()-SESSIONTS) FROM WEBSESSIONS; +---+ | (NOW()-SESSIONTS) | +---+ | 1261 | +---+ 1 row in set (0.00 sec) mysql SELECT (UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(SESSIONTS)) FROM WEBSESSIONS; +---+ | (UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(SESSIONTS)) | +---+ | 748 | +---+ 1 row in set (0.00 sec) Fix: Cast fields as UNIX_TIMESTAMP to get valid seconds between to periods. It's not a bug. You can't apply arithmetics directly on the datetime fields. -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Help struggling newbie, please!!
Hi I am trying to get a handle on MySQL anticipating a PHP application I want to implement on a website next year. I am using WinXP and the website is on a Linux server. I have created a new database, but am having problems creating tables. Its not too obvious how to go about it. I have consulted the manual but of course its all Linux based. I want to get an application called phpMember ver 0.1.0 ( http://product.cybergl.co.id/dev/expose.php?cmd=installversion_id=53 ) working. Point 2 of the installation says: Create the required tables within your website databse as provided in phpMember.sql You can use the mysql-client for this: mysql (db-name) phpMember.sql Any tips about how I can do this in Windows? I can't figure out how the hell I'm supposed to do this. I'm used to using ASP and just creating an Access DB. I did a bit of PHP a couple of years ago with postgres but its all a bit hazy now. Maybe I'm way off track here - I sure could use a few pointers. Cheers Martin Albury Australia - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: How can I duplicate a mysql template database? [hack]
Unfortunately, command line is not an option for me. As I said before,
the php scripts are on a web server and the database is on another
server.
-Original Message-
From: Joseph Bueno [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, December 17, 2002 2:47 AM
To: [EMAIL PROTECTED]
Subject: Re: How can I duplicate a mysql template database? [hack]
Hi,
There is an easier way to duplicate a database; from the comand
line, you can run:
mysqladmin create db2
mysqldump db1 | mysql db2
If you want to duplicate the schema only (without the data):
mysqladmin create db2
mysqldump --no-data db1 | mysql db2
(You may have to add host/user/password options but I just wanted to
show the idea).
Hope this helps,
Joseph Bueno
Daevid Vincent wrote:
Seems to me there should be a built in SQL command to duplicate a
database. Jeepers. Or to read in a .sql file from PHP and create a
database out of it (which was the original intent).
Anyways, here's a 'hack'. I'd still love to hear anyone else's more
elegant solution.
$V2DB = V2_SL.$CompanyID;
$result = mysql_create_db($V2DB, $linkI);
if (!$result) $errorstring .= Error creating .$V2DB.
databaseBR\n.mysql_errno($linkI).:
.mysql_error($linkI).BR\n;
mysql_select_db ($V2DB, $linkI) or die (Could not select .$V2DB.
Database);
/*
//TODO: None of these below here work. Ugh! so frustrating!!
//$filename = /mypath/todb/V2_DB.sql;
//$fd = fopen ($filename, r);
//$sql = fread ($fd, filesize ($filename));
//fclose ($fd);
//$lines = file($filename);
//foreach ($lines as $line_num = $line) { $sql .= $line; }
//$sqlTables = explode(;,$sql);
//foreach ($sqlTables as $table)
//{
// echo PRE$table/PREphr\n;
// $result = mysql_query($sql,$linkI);
// if (!$result) $errorstring .= Error creating .$V2DB.
.$table. tableBR\n.mysql_errno($linkI).:
.mysql_error($linkI).BR\n;
//}
*/
//You must have already created the V2_Template database.
//This will make a clone of it, including data.
$tableResult = mysql_list_tables (V2_Template);
while ($row = mysql_fetch_row($tableResult))
{
$tsql = CREATE TABLE .$V2DB...$row[0]. AS SELECT * FROM
V2_Template..$row[0];
echo $tsql.BR\n;
$tresult = mysql_query($tsql,$linkI);
if (!$tresult) $errorstring .= Error creating
.$V2DB...$row[0]. tableBR\n.mysql_errno($linkI).:
.mysql_error($linkI).BR\n;
}
-Original Message-
From: Daevid Vincent [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, December 17, 2002 1:16 AM
To: [EMAIL PROTECTED]
Cc: 'Bill Lovett'
Subject: RE: How can I duplicate a mysql template database?
Thanks for the reply Bill, however this did not solve the problem. I
still receive the same error with your method as well.
1064: You have an error in your SQL syntax near ';
CREATE TABLE Departments (
DeptID int(10) unsigned NOT NULL auto_increment,' at line 4
Are you first creating a new database first with mysql_create_db()
before trying to run your schema? What error message do you
get? The
code you've posted is only creating a table.
Yes. I am:
$V2DB = V2_SL.$CompanyID;
$result = mysql_create_db($V2DB, $linkI);
mysql_select_db ($V2DB, $linkI) or die (Could not select .$V2DB.
Database);
Also, rather than opening and reading in an external file or
doing all
those $sql .= lines, you might have an easier time using
heredoc syntax,
which would let you do something like
$sql = SQL
CREATE TABLE IP_Dept (
IP_Addr int(10) unsigned NOT NULL default
DeptID int(10) unsigned NOT NULL default
...etc
SQL;
$result = mysql_query($sql,$linkI);
Thanks for that tip, however it fails the same as opening
the file and
same as $sql .=
(hopefully $linkI is your mysql connection) This way you
don't have to bother with all that quoting.
Yes, $linkI is my connection identifier.
I just have an awful feeling that PHP/mySQL won't let me
stack commands
like that because if I just do one table, like this:
$sql = SQL
CREATE TABLE Schedule (
ScheduleID int(10) unsigned NOT NULL auto_increment,
TargetRange char(255) default NULL,
ScannerID int(10) unsigned NOT NULL default '0',
RunEvery char(50) default NULL,
NextRun datetime default NULL,
LastRun datetime default NULL,
PRIMARY KEY (ScheduleID)
) TYPE=MyISAM;
SQL;
It works. However I'm dreading doing this one table at a
time. Grr.
Daevid Vincent wrote:
I need to use PHP to duplicate the schema of a mysql
database. This
seems like it should be a simple task -- taking a
'template' db and
cloning it with a new name.
I've tried things like:
$filename = myDB.sql;
$fd = fopen ($filename, r);
$sql = fread ($fd, filesize ($filename));
fclose ($fd);
And
$lines = file($filename);
foreach ($lines as $line_num = $line) { $sql .= $line;
}
And
Re: How can I duplicate a mysql template database? [hack]
I am not a PHP expert but I think you can run external commands from PHP,
so it should be possible to run mysqladmin, mysqldump and mysql from PHP.
This will work even if you database is on a remote server; just specify
the hostname (ex: mysqladmin -h mysqlhost create db2)
Daevid Vincent wrote:
Unfortunately, command line is not an option for me. As I said before,
the php scripts are on a web server and the database is on another
server.
-Original Message-
From: Joseph Bueno [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, December 17, 2002 2:47 AM
To: [EMAIL PROTECTED]
Subject: Re: How can I duplicate a mysql template database? [hack]
Hi,
There is an easier way to duplicate a database; from the comand
line, you can run:
mysqladmin create db2
mysqldump db1 | mysql db2
If you want to duplicate the schema only (without the data):
mysqladmin create db2
mysqldump --no-data db1 | mysql db2
(You may have to add host/user/password options but I just wanted to
show the idea).
Hope this helps,
Joseph Bueno
Daevid Vincent wrote:
Seems to me there should be a built in SQL command to duplicate a
database. Jeepers. Or to read in a .sql file from PHP and create a
database out of it (which was the original intent).
Anyways, here's a 'hack'. I'd still love to hear anyone else's more
elegant solution.
$V2DB = V2_SL.$CompanyID;
$result = mysql_create_db($V2DB, $linkI);
if (!$result) $errorstring .= Error creating .$V2DB.
databaseBR\n.mysql_errno($linkI).:
.mysql_error($linkI).BR\n;
mysql_select_db ($V2DB, $linkI) or die (Could not select .$V2DB.
Database);
/*
//TODO: None of these below here work. Ugh! so frustrating!!
//$filename = /mypath/todb/V2_DB.sql;
//$fd = fopen ($filename, r);
//$sql = fread ($fd, filesize ($filename));
//fclose ($fd);
//$lines = file($filename);
//foreach ($lines as $line_num = $line) { $sql .= $line; }
//$sqlTables = explode(;,$sql);
//foreach ($sqlTables as $table)
//{
// echo PRE$table/PREphr\n;
// $result = mysql_query($sql,$linkI);
// if (!$result) $errorstring .= Error creating .$V2DB.
.$table. tableBR\n.mysql_errno($linkI).:
.mysql_error($linkI).BR\n;
//}
*/
//You must have already created the V2_Template database.
//This will make a clone of it, including data.
$tableResult = mysql_list_tables (V2_Template);
while ($row = mysql_fetch_row($tableResult))
{
$tsql = CREATE TABLE .$V2DB...$row[0]. AS SELECT * FROM
V2_Template..$row[0];
echo $tsql.BR\n;
$tresult = mysql_query($tsql,$linkI);
if (!$tresult) $errorstring .= Error creating
.$V2DB...$row[0]. tableBR\n.mysql_errno($linkI).:
.mysql_error($linkI).BR\n;
}
-Original Message-
From: Daevid Vincent [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, December 17, 2002 1:16 AM
To: [EMAIL PROTECTED]
Cc: 'Bill Lovett'
Subject: RE: How can I duplicate a mysql template database?
Thanks for the reply Bill, however this did not solve the problem. I
still receive the same error with your method as well.
1064: You have an error in your SQL syntax near ';
CREATE TABLE Departments (
DeptID int(10) unsigned NOT NULL auto_increment,' at line 4
Are you first creating a new database first with mysql_create_db()
before trying to run your schema? What error message do you
get? The
code you've posted is only creating a table.
Yes. I am:
$V2DB = V2_SL.$CompanyID;
$result = mysql_create_db($V2DB, $linkI);
mysql_select_db ($V2DB, $linkI) or die (Could not select .$V2DB.
Database);
Also, rather than opening and reading in an external file or
doing all
those $sql .= lines, you might have an easier time using
heredoc syntax,
which would let you do something like
$sql = SQL
CREATE TABLE IP_Dept (
IP_Addr int(10) unsigned NOT NULL default
DeptID int(10) unsigned NOT NULL default
...etc
SQL;
$result = mysql_query($sql,$linkI);
Thanks for that tip, however it fails the same as opening
the file and
same as $sql .=
(hopefully $linkI is your mysql connection) This way you
don't have to bother with all that quoting.
Yes, $linkI is my connection identifier.
I just have an awful feeling that PHP/mySQL won't let me
stack commands
like that because if I just do one table, like this:
$sql = SQL
CREATE TABLE Schedule (
ScheduleID int(10) unsigned NOT NULL auto_increment,
TargetRange char(255) default NULL,
ScannerID int(10) unsigned NOT NULL default '0',
RunEvery char(50) default NULL,
NextRun datetime default NULL,
LastRun datetime default NULL,
PRIMARY KEY (ScheduleID)
) TYPE=MyISAM;
SQL;
It works. However I'm dreading doing this one table at a
time. Grr.
Daevid Vincent wrote:
I need to use PHP to duplicate the schema of a mysql
database. This
seems like it should be a simple task -- taking a
'template' db and
cloning it with a new name.
I've tried things like:
$filename = myDB.sql;
$fd = fopen ($filename, r);
$sql = fread ($fd, filesize ($filename));
fclose ($fd);
And
$lines =
RE: How can I duplicate a mysql template database?
You mentioned a echo PRE.$sql./PREP; in your first post; what do you see? I see exactly what I expected to see. Basically the contents of a db.sql file read in that looks like this roughly: CREATE TABLE table1 ( blah int(10), foo char(5) ); CREATE TABLE table2 ( blee int(10), fee char(5) ); Etc.. Everything looks exactly like the .sql file does and the .sql file works perfectly if I redirect it in via the command line or even via an system() call. That's good, and we'll assume for the moment that your script doesn't error through to die but instead makes a successful connection. Yes. All that stuff works great. By the way, I see you quoting around your variables like print This is . $var . here ; Yes, its' for readability in HomeSite. It color codes things, and that's a nice way to see it proper. How does it fail? Give us more detail. With the error I posted earlier: 1064: You have an error in your SQL syntax near '; CREATE TABLE Departments ( DeptID int(10) unsigned NOT NULL auto_increment,' at line 4 It's always on the second table. Order is irrelevant. Notice the ; that it chokes on. That only happens when I separate the CREATE commands of course. But there is no other way to deliminate them. % I just have an awful feeling that PHP/mySQL won't let me stack commands % like that because if I just do one table, like this: % % $sql = SQL % CREATE TABLE Schedule ( ... % SQL; % % It works. However I'm dreading doing this one table at a time. Grr. No, that doesn't make sense. It shouldn't matter how you create and populate the variable, as long as the end result content is the same. Since it works when you lay it out manually, it seems very probable that your filling it out in steps has some problems. I think I'm right ;-) Try if for yourself and see what I mean. Just try to create a db and two tables and you'll see it choke. d - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How can I duplicate a mysql template database?
You cannot execute several SQL statements within a single query. You are trying to reinvent 'mysql' client batch processing; if you want to do that, you have to write an SQL parser in PHP that split your input in independant SQL statements and execute them one by one :( Daevid Vincent wrote: You mentioned a echo PRE.$sql./PREP; in your first post; what do you see? I see exactly what I expected to see. Basically the contents of a db.sql file read in that looks like this roughly: CREATE TABLE table1 ( blah int(10), foo char(5) ); CREATE TABLE table2 ( blee int(10), fee char(5) ); Etc.. Everything looks exactly like the .sql file does and the .sql file works perfectly if I redirect it in via the command line or even via an system() call. That's good, and we'll assume for the moment that your script doesn't error through to die but instead makes a successful connection. Yes. All that stuff works great. By the way, I see you quoting around your variables like print This is . $var . here ; Yes, its' for readability in HomeSite. It color codes things, and that's a nice way to see it proper. How does it fail? Give us more detail. With the error I posted earlier: 1064: You have an error in your SQL syntax near '; CREATE TABLE Departments ( DeptID int(10) unsigned NOT NULL auto_increment,' at line 4 It's always on the second table. Order is irrelevant. Notice the ; that it chokes on. That only happens when I separate the CREATE commands of course. But there is no other way to deliminate them. % I just have an awful feeling that PHP/mySQL won't let me stack commands % like that because if I just do one table, like this: % % $sql = SQL % CREATE TABLE Schedule ( ... % SQL; % % It works. However I'm dreading doing this one table at a time. Grr. No, that doesn't make sense. It shouldn't matter how you create and populate the variable, as long as the end result content is the same. Since it works when you lay it out manually, it seems very probable that your filling it out in steps has some problems. I think I'm right ;-) Try if for yourself and see what I mean. Just try to create a db and two tables and you'll see it choke. d - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: Help struggling newbie, please!!
On Tuesday 17 December 2002 13:56, Martin Finnegan wrote: I am trying to get a handle on MySQL anticipating a PHP application I want to implement on a website next year. I am using WinXP and the website is on a Linux server. I have created a new database, but am having problems creating tables. Its not too obvious how to go about it. I have consulted the manual but of course its all Linux based. I want to get an application called phpMember ver 0.1.0 ( http://product.cybergl.co.id/dev/expose.php?cmd=installversion_id=53 ) working. Point 2 of the installation says: Create the required tables within your website databse as provided in phpMember.sql You can use the mysql-client for this: mysql (db-name) phpMember.sql Any tips about how I can do this in Windows? I can't figure out how the hell I'm supposed to do this. I'm used to using ASP and just creating an Access DB. I did a bit of PHP a couple of years ago with postgres but its all a bit hazy now. Maybe I'm way off track here - I sure could use a few pointers. On Windows you can do the same in the DOS prompt. -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: Mysql - Upgrading from 3.23.53 to 3.23.54
On Monday 16 December 2002 16:24, Santiago Alba wrote: I have installed MySQL 2.23.53 release and I want to upgrade to MySQL 2.23.54... how can I do this? Have i got to reinstall? Is there any script? As usual ;) Seriously speaking - depends on the way have you installed it. -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: mysql MAX client
On Tuesday 17 December 2002 22:04, Muruganandam wrote: Dear All, Where can i find mysql client and devel .rpm files compatible for MySQL-Max-3.23.53a-1.i386.rpm You can use any recent 3.23.x version of client. -devel.rpm should be the same as -shared.rpm if it's used. -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: sql query LOAD DATA INFILE question
On Tuesday 17 December 2002 11:08, moka at hol dot gr wrote: I am looking at the following situation: I am reading some files arriving every minute and parsing them and creating a set of files ready to be inserted into tables. on the fly. While I am waiting for the next burst of files, I want to insert these into the tables, then erase the files. Normally LOAD DATA INFILE LOCAL works fine here. The problem is that the machine holding these tables is a different one. The question is, is it possible to run LOAD DATA INFILE to do the inserts on the remote machine, or is it better to first ftp the files over, then run LOAD DATA INFILE there? I assume LOAD INFILE is faster than via mysql -u user -ppasswd DBfile.sql where file.sql contains sql INSERT query From the LOAD DATA INFILE documentation it is not clear to me how this can be done, if it can be done. If you use LOAD DATA LOCAL INFILE, file must be located on the client box. So, you can load data to the remore server. ftp is faster, more reliable, but LOAD DATA LOCAL is easier to use and cheaper. Besides, to use LOAD DATA you should have FILE privilege. -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql - Upgrading from 3.23.53 to 3.23.54
Hi. You need automake ver. 1.5 don't use 1.5d (i did it and spent couple of hours to discover, why source rpm 3.23.54a don't rebuild ) autoconf is 2.52 libtool is 1.4.3 Regards. - Original Message - From: Egor Egorov [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, December 17, 2002 2:02 PM Subject: re: Mysql - Upgrading from 3.23.53 to 3.23.54 On Monday 16 December 2002 16:24, Santiago Alba wrote: I have installed MySQL 2.23.53 release and I want to upgrade to MySQL 2.23.54... how can I do this? Have i got to reinstall? Is there any script? As usual ;) Seriously speaking - depends on the way have you installed it. -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --r-e-k-l-a-m-a- Masz dosc placenia prowizji bankowi ? mBank - zaloz konto http://epieniadze.onet.pl/mbank - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: RE: more about using sets
What exactly is wrong about using ENUM's? Luc sql,mysql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: Help struggling newbie, please!!
You might also wish to consider downloading and installing Webmin. It's one of the easiest installs I have encountered. Simply tar the tarball and proceed into the new subdirectory (IE webmin-1.030) and perform a /setup.sh . Webmin will allow you easy access to your server via a web browser. Simply enter http://serverip:1/ after the installation. To manage your MySQL databases, simply left-click on the Server button and select the MySQL server icon. You should be able to accomplish a great deal with this GUI. For tasks not covered by Webmin, you will need to familiarize yourself with Telnet. If, as was the case with our distribution, Telnet is disabled by default, you will need to familiarize yourself with PuTTY.exe for accessing your server via a secure shell. GL Victoria Reznichenko [EMAIL PROTECTED] 12/17/02 08:02AM On Tuesday 17 December 2002 13:56, Martin Finnegan wrote: I am trying to get a handle on MySQL anticipating a PHP application I want to implement on a website next year. I am using WinXP and the website is on a Linux server. I have created a new database, but am having problems creating tables. Its not too obvious how to go about it. I have consulted the manual but of course its all Linux based. I want to get an application called phpMember ver 0.1.0 ( http://product.cybergl.co.id/dev/expose.php?cmd=installversion_id=53 ) working. Point 2 of the installation says: Create the required tables within your website databse as provided in phpMember.sql You can use the mysql-client for this: mysql (db-name) phpMember.sql Any tips about how I can do this in Windows? I can't figure out how the hell I'm supposed to do this. I'm used to using ASP and just creating an Access DB. I did a bit of PHP a couple of years ago with postgres but its all a bit hazy now. Maybe I'm way off track here - I sure could use a few pointers. On Windows you can do the same in the DOS prompt. -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem to access MySQL via MySQLFront
Hi, Here is my configuration : Linux SME Server 5.6 bêta 7 Mysql 3.23.49 I try to access to Mysql via a client programm nammed MysqlFront. I have defined an user like this : Username : blank Host : .domain_name.ch Password : blank When I try to connect to MySQL, this error message come : MySQL - Error Connection failed: 2013 - Lost connection to MySQL server during query But I try to put into /etc/hosts IP mypc.domain_name.ch mypc and I retry to connect to MySQL via MySQLFront, and all is OK. I will not declare all of the PC into /etc/hosts. Is there a solution ? I browse the MySQL Documentation but I didn't find something. Thank you for your help and excuse for my poor English. David Rayroud - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: more about using sets
Luc Foisy wrote:
What exactly is wrong about using ENUM's?
Changing them will kill you unless you're _very_ careful.
Using them is usually unnecessary as you could've used an ID value
pointing to another table of values. That table can then be added to
with no risk to your existing queries. As a contrived example:
Employee
--
ID ... primary key
Gender enum('male','female')
vs.
GenderID tinyint
Gender
--
ID tiny int ... primary key
Name varchar(10)
INSERT into Gender(Name) values ('male'),('female');
Later, you might need:
INSERT into Gender(Name) values ('unknown');
--
Michael T. Babcock
C.T.O., FibreSpeed Ltd. SQL
http://www.fibrespeed.net/~mbabcock
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
[mysql crashes with Memory access error]
Description: When accessing this mysql version from a WinXP client (running in a VMWare shel) the server crashes reproducable. How-To-Repeat: Access to server with DBTools 1.0.x from WinXP client. Fix: ? Submitter-Id: submitter ID Originator:Hans-Joerg Wolff Organization: MySQL support: [none] Synopsis: Memory access error with DBTools access Severity: Priority: Category: mysql Class: Release: mysql-3.23.52 (Source distribution) Server: /usr/bin/mysqladmin Ver 8.23 Distrib 3.23.54, for pc-linux on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.52-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 2 min 50 sec Threads: 1 Questions: 1 Slow queries: 0 Opens: 6 Flush tables: 1 Open tables: 0 Queries per second avg: 0.006 Environment: System: Linux bu01 2.4.20-SMP #2 SMP Mon Dec 16 03:45:36 CET 2002 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i486-suse-linux/3.2/specs Configured with: ../configure --enable-threads=posix --prefix=/usr --with-local-prefix=/usr/local --infodir=/usr/share/info --mandir=/usr/share/man --libdir=/usr/lib --enable-languages=c,c++,f77,objc,java,ada --enable-libgcj --with-gxx-include-dir=/usr/include/g++ --with-slibdir=/lib --with-system-zlib --enable-shared --enable-__cxa_atexit i486-suse-linux Thread model: posix gcc version 3.2 Compilation info: CC='gcc' CFLAGS='-O2 -march=i586 -mcpu=i686 -fmessage-length=0 -DPIC -fPIC' CXX='g++' CXXFLAGS='-O2 -march=i586 -mcpu=i686 -fmessage-length=0 -felide-constructors-fno-exceptions -fno-rtti -fPIC -DPIC' LDFLAGS='' LIBC: -rwxr-xr-x1 root root 1312470 2002-09-10 18:51 /lib/libc.so.6 -rw-r--r--1 root root 23159812 2002-09-09 18:40 /usr/lib/libc.a -rw-r--r--1 root root 178 2002-09-09 18:40 /usr/lib/libc.so lrwxrwxrwx1 root root 20 2002-09-26 18:44 /usr/lib/libc-client.so - libc-client.so.2001a -rwxr-xr-x1 root root 735696 2002-09-09 22:47 /usr/lib/libc-client.so.2001a Configure command: ./configure '--disable-shared' '--with-mysqld-ldflags=-static' '--with-client-ldflags=-static' '--without-berkeley-db' '--without-innodb' '--enable-assembler' '--enable-large-files' '--infodir=/usr/share/info' '--libdir=/usr/lib' '--libexecdir=/usr/sbin' '--localstatedir=/var/lib/mysql' '--mandir=/usr/share/man' '--prefix=/usr' '--sysconfdir=/etc' '--with-mysqld-user=mysql' '--without-debug' '--datadir=/usr/share' '--includedir=/usr/include' '--with-extra-charsets=complex' '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--with-libwrap' 'CFLAGS=-O2 -march=i586 -mcpu=i686 -fmessage-length=0 -DPIC -fPIC' 'CXXFLAGS=-O2 -march=i586 -mcpu=i686 -fmessage-length=0-felide-constructors -fno-exceptions -fno-rtti -fPIC -DPIC' - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Bug in MySql 3.23.53 ?
Hi,
Seems that mySql sometime write Querys to Log but does
not execute them. I found folling in the MySql Log-File.
Comments made by me are signed with -.
Maybe some one has a solution for that ?
# at 5636748
#021216 15:27:38 server id 1 Intvar
SET INSERT_ID = 189612;
# at 5636770
#021216 15:27:38 server id 1 Query thread_id=18485 exec_time=0 error_code=0
SET TIMESTAMP=1040048858;
insert into billing_iteminfo (`bid_id`, `user_id`, `order_id`, `item_x`, `item_id`,
`item_txt`, `item_quant`, `auction_profile`, `auction_seller`, `auction_name`,
`auction_itemid`, `bidder_alias`, `bidder_email`, `org_price`, `org_currency`,
`usr_price`, `usr_single`, `usr_currency`, `cust_price`, `cust_single`,
`cust_currency`) VALUES ('200375', '12345', '988-14230/100369DE', '1', '0',
'VALENTINO', '1', 'STANDARD', 'winner', 'Nowhere', '488', 'kefgdfggish',
'yoman@nowhere', '79', '~@', '79.00', '79.00', 'EUR', '79.00', '79.00', 'EUR');
- this is ok !!!
# at 5637404
#021216 15:27:38 server id 1 Query thread_id=18485 exec_time=0 error_code=0
SET TIMESTAMP=1040048858;
update bidder_bidinfo set chk_buy='1', order_id='992600446488-14230/100369DE',
order_dat=NOW() where user_id='100369' and bid_id='200375';
- this is ok !!!
# at 5637576
#021216 15:27:38 server id 1 Intvar
SET INSERT_ID = 189613;
# at 5637598
#021216 15:27:38 server id 1 Query thread_id=18485 exec_time=0 error_code=0
SET TIMESTAMP=1040048858;
insert into billing_iteminfo (`bid_id`, `user_id`, `order_id`, `item_x`, `item_id`,
`item_txt`, `item_quant`, `auction_profile`, `auction_seller`, `auction_name`,
`auction_itemid`, `bidder_alias`, `bidder_email`, `org_price`, `org_currency`,
`usr_price`, `usr_single`, `usr_currency`, `cust_price`, `cust_single`,
`cust_currency`) VALUES ('200377', '12345', '988-14230/100369DE', '1', '0',
'VALENTINO', '1', 'STANDARD', 'winner', 'Nowhere', '488', 'kefgdfggish',
'yoman@nowhere', '79', '~@', '79.00', '79.00', 'EUR', '79.00', '79.00', 'EUR');
- this data cannot be found in Database. Dataset 189613 ist still missing
- (AutoInc-Row: 189611, 189612, 189614, ...) !!!
# at 5638232
#021216 15:27:38 server id 1 Query thread_id=18485 exec_time=0 error_code=0
SET TIMESTAMP=1040048858;
update bidder_bidinfo set chk_buy='1', order_id='992600446488-14230/100369DE',
order_dat=NOW() where user_id='100369' and bid_id='200377';
- this is ok !!!
# at 5638404
#021216 15:27:38 server id 1 Intvar
Regards,
Bjoern Mueller
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MYSQL Overhead
In some of my tables I see overheard. What does it mean and does it mean I have to optimize it every time? I seem to see it everyday. SQL - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: RE: more about using sets
What exactly is wrong about using ENUM's?
Changing them will kill you unless you're _very_ careful.
Using them is usually unnecessary as you could've used an ID value
pointing to another table of values. That table can then be added to
with no risk to your existing queries. As a contrived example:
Employee
--
ID ... primary key
Gender enum('male','female')
vs.
GenderID tinyint
Gender
--
ID tiny int ... primary key
Name varchar(10)
INSERT into Gender(Name) values ('male'),('female');
Later, you might need:
INSERT into Gender(Name) values ('unknown');
Is not enum forced to any of the values used when created?
We use enum extensively for 'Y' 'N' values, sort of true false.
That way the values are forced to be one or the other. So any end user has to put one
of those
values in (if we allow it in that way, though we usually force them to use a checkbox).
And its a little more viewable/understandable than 0 or 1, for the end user.
I also was under the impression that JOINs did take a little extra time/resource
rather than a direct value from the same table. We have a number of tables that
have multiple true/false values (one with about 50), is not joining that table
to 50 different fields not a little expensive?
Maybe we are just doing it all wrong? If we never expect the
values to be different (EVER) then is it still wrong?
Bah, sql, mysql
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Large Like Queries
Have you thought about creating a fulltext index? Then you can search on any word in a field. MySQL 4 has a lot more options for fulltext search, but you can still do it in 3. On Monday, December 16, 2002, at 04:25 PM, Chris Stark wrote: SELECT my_id FROM my_table WHERE my_value LIKE '%ABCDEFG%' LIMIT 50; I know that in a query such as this, mysql does not utilize indexing, so I was just curious how some others have attempted to speed up this kind of thing up -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How can I duplicate a mysql template database?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Daevid -- ...and then Daevid Vincent said... % % You mentioned a %echo PRE.$sql./PREP; % in your first post; what do you see? % % I see exactly what I expected to see. Basically the contents of a db.sql % file read in that looks like this roughly: OK. That's good, and now we know, too :-) Of course, you're still using fake examples instead of showing actual output... ... % Etc.. Everything looks exactly like the .sql file does and the .sql file % works perfectly if I redirect it in via the command line or even via an % system() call. OK. BTW, if you can use a system call you should be able to use the mysqldump suggestion from another reply. % % That's good, and we'll assume for the moment that your script doesn't % error through to die but instead makes a successful connection. % % Yes. All that stuff works great. Good. % % By the way, I see you quoting around your variables like %print This is . $var . here ; % % Yes, its' for readability in HomeSite. It color codes things, and that's % a nice way to see it proper. How interesting. Thanks for the info! % % How does it fail? Give us more detail. % % With the error I posted earlier: % % 1064: You have an error in your SQL syntax near '; % CREATE TABLE Departments ( %DeptID int(10) unsigned NOT NULL auto_increment,' at line 4 Ah -- but you didn't post the resultant SQL statement that glued all of this together so that we can see what line 4 is! % % It's always on the second table. Order is irrelevant. % Notice the ; that it chokes on. That only happens when I separate the % CREATE commands of course. But there is no other way to deliminate them. My guess is that Joseph's suggestion that you can only do single commands in a php mysql() command is probably right. % ... % No, that doesn't make sense. It shouldn't matter how you create and % populate the variable, as long as the end result content is the same. % Since it works when you lay it out manually, it seems very % probable that your filling it out in steps has some problems. % % I think I'm right ;-) Could be :-) % % Try if for yourself and see what I mean. Just try to create a db and two % tables and you'll see it choke. Actually, while I do both php and mysql, I haven't yet done mysql from php -- and so I'd be a lousy debugging source for that :-) % % d HTH HAND mysql query, :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://www.justpickone.org/davidtg/Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE9/zJLGb7uCXufRwARArwuAKCtNoZX4597UW6Usg4GQU6DeUXx9ACdExlw HZdcv7WV2BGJ66YvnWa1it0= =n2o+ -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: more about using sets
Luc Foisy wrote:
We use enum extensively for 'Y' 'N' values, sort of true false.
That way the values are forced to be one or the other. So any end user has to put one of those values in (if we allow it in that way, though we usually force them to use a checkbox). And its a little more viewable/understandable than 0 or 1, for the end user.
True enough, and I use enums for two-valued options myself quite often
enum('false','true') for example, makes a good replacement for bool.
However, you must remember to always do them in the same order and not
alter it to enum('true','false') by accident and end up flipping all the
data in your table.
I also was under the impression that JOINs did take a little extra time/resource rather than a direct value from the same table.
They do, but I've found it negligable so far. The joined table with
only 3 or 4 values in it should sit in memory forever, and the index
would be as big as the table if you bothered :).
We have a number of tables that have multiple true/false values (one with about 50), is not joining that table to 50 different fields not a little expensive?
Again, YMMV and I do this too. I'm just saying that for most things
that people enum (colours of vehicles, brands of product, shipping
company, ...) it just shouldn't be done. Its unfortunately familiar to
C programmers who go I know how to use that (I'm a C programmer
myself) and JOINs don't come naturally to.
Maybe we are just doing it all wrong? If we never expect the values to be different (EVER) then is it still wrong?
According the the SQL specs, its not wrong at all, of course. Its just
IMHO (and a few others', I'd bet) that you should avoid it unless its
obviously ok. Remember to document somewhere that you always use
('false','true') or vice versa so that if data needs to be rebuilt,
everyone knows which value 0 stands for and which one 1 stands for.
From C experience, I always put false first.
--
Michael T. Babcock
C.T.O., FibreSpeed Ltd.
http://www.fibrespeed.net/~mbabcock
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How indexes work?
I have some questions on how an index is built and used. I have a very simple table like this: char_col char(10) not null Index (char_col) If I have 1000 records in this table, how big will the index become? The data-size would be 10 000 right? How does an index look like and how can it speed up searches? From what I can think, the index on the rows in the table above would look like this: name address name phone There must be something more, like a pointer of some sort and a more efficient way of storing the indexed stuff. Any thoughts? // Michelle sql, query __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: more about using sets
From: David T-G [mailto:[EMAIL PROTECTED]] ...I still have to figure out how to make sure that our credit card types and skill levels don't get corrupted (MC, MasterCard, mastercard, ...), but I guess that gets enforced in the software interface, right? Isn't the card type a piece of derived data? You should never have to enter it directly; it's computable from (the first digit(s) of) the card number. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
JOIN based query problem (little help needed)
Hello list. Please help me to refine a JOIN based query which I am not able to correct. This the scenario (simplified). Three tables: Products - | id | description | - | 01 | bread| | 02 | milk | | 03 | coffee | - Purchases - | prod_id | quantities | - | 01| 10 | | 02| 5 | | 03| 3 | - Sellings - | prod_id | quantities | - | 01| 3 | | 01| 1 | | 02| 1 | | 02| 1 | - This the query which I could figure out: SELECT products.*, SUM(purchases.quantity) AS purchases, SUM(sellings.quantity) AS sellings, SUM(purchases.quantity)-SUM(sellings.quantity) AS inventory FROM products LEFT JOIN purchases ON products.id=purchases.prod_id LEFT JOIN sellings ON products.id=sellings.prod_id GROUP BY products.id ORDER BY products.id The query should return: - | prod_id | description | purchases | sellings | inventory | - |01 | bread|10 | 4 | 6 | |02 | milk |5 | 2 | 3 | |03 | coffee |3 | 0 | 3 | - This is what the query actually returns: - | prod_id | description | purchases | sellings | inventory | - |01 | bread|20 | 4 |16 | |02 | milk |10 | 2 | 8 | |03 | coffee |3 | 0 | 3 | - Thank you very much for your help. Gigi Here is the database dump if you wish to reproduce the scenario: # phpMyAdmin MySQL-Dump # version 2.3.2 # http://www.phpmyadmin.net/ (download page) # # Host: localhost # Generato il: 17 Dic, 2002 at 04:45 PM # Versione MySQL: 3.23.53 # Versione PHP: 4.2.3 # Database : `inventory` # # # Struttura della tabella `products` # CREATE TABLE products ( id int(11) NOT NULL auto_increment, description varchar(64) NOT NULL default '', PRIMARY KEY (id), KEY id (id) ) TYPE=MyISAM; # # Dump dei dati per la tabella `products` # INSERT INTO products VALUES (1, 'bread'); INSERT INTO products VALUES (2, 'milk'); INSERT INTO products VALUES (3, 'coffee'); # # # Struttura della tabella `purchases` # CREATE TABLE purchases ( prod_id int(11) NOT NULL default '0', quantity int(11) NOT NULL default '0', KEY prod_id (prod_id) ) TYPE=MyISAM; # # Dump dei dati per la tabella `purchases` # INSERT INTO purchases VALUES (1, 10); INSERT INTO purchases VALUES (2, 5); INSERT INTO purchases VALUES (3, 3); # # # Struttura della tabella `sellings` # CREATE TABLE sellings ( prod_id int(11) NOT NULL default '0', quantity int(11) NOT NULL default '0', KEY prod_id (prod_id) ) TYPE=MyISAM; # # Dump dei dati per la tabella `sellings` # INSERT INTO sellings VALUES (1, 3); INSERT INTO sellings VALUES (1, 1); INSERT INTO sellings VALUES (2, 1); INSERT INTO sellings VALUES (2, 1); - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: ALTER a auto_increment column
On Tuesday 17 December 2002 03:09, Lopez David E-r9374c wrote: Problem is type SMALLINT needs to be MEDIUMINT (MyISAM). column: id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT My solution is to use the following ALTER statement: ALTER TABLE messages CHANGE id id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT My question is: what happens to the sequence of numbers in this primary key? The manual states that if you don't change the AUTO_INCREMENT column, the sequence number will not be affected. It also says that if you drop a AUTO_INCREMENT column and then add another AUTO_INCREMENT column, MySQL will resequence the new column. Well I'm kinda in-between these two extremes. I want to increase the width but keep the sequence. As usual, this is a primary key which will affect other tables (foreign keys). The total rows affected is 14 million. If you wil use CHANGE or MODIFY, sequence number will not be affected, too. BTW I expected the rows would be around 50. It's now near 60k. So much for my foresight. -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
re: Re: ODBC query freezes server
On Tuesday 17 December 2002 09:24, B F wrote: I tried your pass-through idea previously and it did not work. Today I even typed my query directly into the mysql shell (and bypassing MS-Access altogether) and my MySQL server still hung! So we can rule out problems with Access of MyODBC... What is your query? Check with EXPLAIN if indexes are used. I suppose my query is just too resource-intensive for the server (lots of joins), so I must ask again: is there a way to prevent one query from consuming too many resources and hanging the server? Once again all replies are much appreciated! -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: more about using sets
Anderson, Alan R wrote:
Isn't the card type a piece of derived data? You should never have to enter it directly; it's computable from (the first digit(s) of) the card number.
Also note, the type of card isn't a SET option at all (it would be an
ENUM) and if its used as a foreign-key relation to a table of card
types, the card type verification (how to check the digits, etc.) could
be stored in the table:
CCTypes
---
ID ... primary key
Name { entries: mastercard, visa, amex, diners club, ... }
VerifyMethod { ... }
--
Michael T. Babcock
C.T.O., FibreSpeed Ltd. ... sql
http://www.fibrespeed.net/~mbabcock
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Access to MySQL
There are many tools. Use a gui. mysql front (free), premium soft (my pick but has a 29 day trial). There are also many small window applications to do this. Do a search at one of the famous downloads. +client +mysql +gui -Original Message- From: Charles Mabbott [mailto:[EMAIL PROTECTED]] Sent: Monday, December 16, 2002 1:45 PM To: [EMAIL PROTECTED]; '1Mysql' Subject: RE: Access to MySQL Yves, I am always open for cleaner method, but what I did was in MySQL created a table 'member' for data. In Access exported as .txt make sure tab sequential. Back in MySql I made database active that had 'member' And did a: mysql load data infile c:\\mysql\\tmp.txt into member; it actually went in quite cleanly. I am sure there are probably better ways, I am always willing to learn. But this is what worked for me today. Regards, Chuck -Original Message- From: Yves Arsenault [mailto:[EMAIL PROTECTED]] Sent: Monday, December 16, 2002 12:21 PM To: 1Mysql Subject: Access to MySQL Hello, Is there a small program that would transfer the contents of an Access database to MySQL? TIA, Yves Arsenault Carrefour Infotech 5,promenade Acadian Charlottetown, IPE C1C 1M2 [EMAIL PROTECTED] (902)368-1895 ext.242 ICQ #117650823 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Error 2013 from remote tcp connection
Hello list, I have been running MySQL 3.23.51 on a 4.6 FreeBSD box for some time with no problem. Recently, a client requested the ability to connect from remote. Until now, I only allow connections from localhost and no TCP port open. I have been going through the docs trying to figure out how to get tcp working, and/or ssh. I can ssh into the box no problem and use my ssh client to create a socket local:3306 to remote:3306. I also restarted MySQLd to allow tcp: ./mysql-server.sh start 20503 p0 I 0:00.01 /bin/sh /usr/local/bin/safe_mysqld --user=mysql --port=3306 20521 p0 S 0:00.03 /usr/local/libexec/mysqld --basedir=/usr/local --datadir=/var/db/mysql --user=mysq (screen chops off) mysql client from command line of server connects fine at localhost. php applications use mysql server fine via user/pass at localhost. still cant connect to server from remote using same user/pass. the user has perms to allow connects from my remote ip (host). Connection Failed: 2013 lost connection to MySQL server during query. This problem is when I try the tunnell and also when I try to connect directly to the box. For now, if I could just get tcp to accept connections from a remote ip without ssh would be a nice start. Can anyone help? I have read the docs and still cant get it to work. Any ideas? Thanks kris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
remote tcp connection help
Hello list, I have been running MySQL 3.23.51 on a 4.6 FreeBSD box for some time with no problem. Recently, a client requested the ability to connect from remote. Until now, I only allow connections from localhost and no TCP port open. I have been going through the docs trying to figure out how to get tcp working, and/or ssh. I can ssh into the box no problem and use my ssh client to create a socket local:3306 to remote:3306. I also restarted MySQLd to allow tcp: ./mysql-server.sh start 20503 p0 I 0:00.01 /bin/sh /usr/local/bin/safe_mysqld --user=mysql --port=3306 20521 p0 S 0:00.03 /usr/local/libexec/mysqld --basedir=/usr/local --datadir=/var/db/mysql --user=mysq (screen chops off) mysql client from command line of server connects fine at localhost. php applications use mysql server fine via user/pass at localhost. still cant connect to server from remote using same user/pass. the user has perms to allow connects from my remote ip (host). Connection Failed: 2013 lost connection to MySQL server during query. This problem is when I try the tunnell and also when I try to connect directly to the box. For now, if I could just get tcp to accept connections from a remote ip without ssh would be a nice start. Can anyone help? I have read the docs and still cant get it to work. Any ideas? Thanks kris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php . - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Switch rows and columns
Hi everyone, this might be a newbie question: is there a way to switch all rows and columns in the output of an select statement? I am using DBIx::XHMTL_Table to display all the contents of a table on a website. The problem is that the table has about 50 columns, but every query result displays only one row. So I`d like to display the query result as a vertical table If anybody knows a better way to do it, I`d really appreciate it Thanks for your help in advance. Best regards, Sven - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: No descending index ?
One trick I have used in the past in other databases is to create your own descending index. If your field is say fieldA, when you enter a row into the table take the value of fieldA and subtract from 0, then put that in fieldB. Index fieldB and you now can order rows descending. - Steve Yates - Can't I have just a little bit of peril? ~ Taglines by Taglinator - www.srtware.com ~ sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
remote tcp connection failure
Hello list, I have been running MySQL 3.23.51 on a 4.6 FreeBSD box for some time with no problem. Recently, a client requested the ability to connect from remote. Until now, I only allow connections from localhost and no TCP port open. I have been going through the docs trying to figure out how to get tcp working, and/or ssh. I can ssh into the box no problem and use my ssh client to create a socket local:3306 to remote:3306. I also restarted MySQLd to allow tcp: ./mysql-server.sh start 20503 p0 I 0:00.01 /bin/sh /usr/local/bin/safe_mysqld --user=mysql --port=3306 20521 p0 S 0:00.03 /usr/local/libexec/mysqld --basedir=/usr/local --datadir=/var/db/mysql --user=mysq (screen chops off) mysql client from command line of server connects fine at localhost. php applications use mysql server fine via user/pass at localhost. still cant connect to server from remote using same user/pass. the user has perms to allow connects from my remote ip (host). Connection Failed: 2013 lost connection to MySQL server during query. This problem is when I try the tunnell AND ALSO when I try to connect directly to the box from a remote ip via TCP. My username/pass has permissions to allow tcp connect from my remote host IP. For now, if I could just get tcp to accept connections from a remote ip without ssh would be a nice start. Can anyone help? I have read the docs and still cant get it to work. Any ideas? Thanks kris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Assertion failed in buf0buf.ic
I am receiving an 'Assertion failed' error in my log and I am not really sure how to continue. What I was trying to do was start the MySQLd (v 3.23.53) with support for innodb (it is compiled in) on a FreeBSD 4.7-stable server. I used the settings in my-large.cnf as a template. The server in question is Dual P3-866, 1GB memory. If Ive included too much, or too little information, please let me know. Below is the relevant portion of that logfile. error log: 021214 15:09:32 mysqld started 021214 15:09:34 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 43892 InnoDB: Doing recovery: scanned up to log sequence number 0 43892 InnoDB: Error: trying to access a stray pointer 9ac3bff8 InnoDB: buf pool start is at ac48000, number of pages 16384 021214 15:09:34 InnoDB: Assertion failure in thread 137764864 in file ../include/buf0buf.ic line 284 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked agaist is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail key_buffer_size=268431360 record_buffer=1044480 sort_buffer=1048568 max_used_connections=0 max_connections=100 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 466539 K bytes of memory Hope that's ok, if not, decrease some variables in the equation 021214 15:09:34 mysqld ended Thanks! - Chris Kulish Systems Engineer ING Advisors Network Ph. (515) 698-7583 Fx. (515) 698-3583 ... if you must mount the gallows, give a jest to the crowd, a coin to the hangman, and make the drop with a smile on your lips. --Birgitte - - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Mysql - Upgrading from 3.23.53 to 3.23.54
I installed binary version (source distribution)... not with rpm - Original Message - From: Egor Egorov [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, December 17, 2002 2:02 PM Subject: re: Mysql - Upgrading from 3.23.53 to 3.23.54 On Monday 16 December 2002 16:24, Santiago Alba wrote: I have installed MySQL 2.23.53 release and I want to upgrade to MySQL 2.23.54... how can I do this? Have i got to reinstall? Is there any script? As usual ;) Seriously speaking - depends on the way have you installed it. -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Mysql - Upgrading from 3.23.53 to 3.23.54
Sorry, but I don´t understand you... I have no idea, if you don't mind you could be more expecific... Thanks a lot for your answer. - Original Message - From: Ireneusz Piasecki [EMAIL PROTECTED] To: Egor Egorov [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, December 17, 2002 2:20 PM Subject: Re: Mysql - Upgrading from 3.23.53 to 3.23.54 Hi. You need automake ver. 1.5 don't use 1.5d (i did it and spent couple of hours to discover, why source rpm 3.23.54a don't rebuild ) autoconf is 2.52 libtool is 1.4.3 Regards. - Original Message - From: Egor Egorov [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, December 17, 2002 2:02 PM Subject: re: Mysql - Upgrading from 3.23.53 to 3.23.54 On Monday 16 December 2002 16:24, Santiago Alba wrote: I have installed MySQL 2.23.53 release and I want to upgrade to MySQL 2.23.54... how can I do this? Have i got to reinstall? Is there any script? As usual ;) Seriously speaking - depends on the way have you installed it. -- 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 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --r-e-k-l-a-m-a- Masz dosc placenia prowizji bankowi ? mBank - zaloz konto http://epieniadze.onet.pl/mbank - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Incorrect information in file with mysql_install_db after clean install
Hello, I am having problems with mysql_install_db. I am running a bleeding-edge Gentoo system (gcc 3.2.1 and glibc 2.3.1, both with some compatibility patches. also running unpatched kernel 2.5.52). MySQL compiles with no errors or problems, but mysql_install_db will not run correctly. Its output is: Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables ERROR: 1033 Incorrect information in file: './mysql/db.frm' ERROR: 1033 Incorrect information in file: './mysql/db.frm' ERROR: 1033 Incorrect information in file: './mysql/db.frm' ERROR: 1033 Incorrect information in file: './mysql/db.frm' ERROR: 1033 Incorrect information in file: './mysql/db.frm' ERROR: 1033 Incorrect information in file: './mysql/db.frm' ERROR: 1033 Incorrect information in file: './mysql/db.frm' ERROR: 1033 Incorrect information in file: './mysql/db.frm' ERROR: 1033 Incorrect information in file: './mysql/db.frm' ERROR: 1033 Incorrect information in file: './mysql/db.frm' ERROR: 1033 Incorrect information in file: './mysql/db.frm' ERROR: 1033 Incorrect information in file: './mysql/db.frm' ERROR: 1033 Incorrect information in file: './mysql/db.frm' ERROR: 1033 Incorrect information in file: './mysql/db.frm' 021217 12:09:26 /usr/sbin/mysqld: Shutdown Complete [password reminder, license info, etc] My configure options are the typical Gentoo options, although nobody on the Gentoo forums seems to be having the problem that I am: ./configure --prefix=/usr --host=i686-pc-linux-gnu --mandir=/usr/share/man --infodir=/usr/share/info --datadir=/usr/share --sysconfdir=/etc --localstatedir=/var/lib --libexecdir=/usr/sbin --sysconfdir=/etc/mysql --localstatedir=/var/lib/mysql --with-raid --with-low-memory --enable-assembler --with-charset=latin1 --with-mysqld-user=mysql --with-extra-charsets=all --enable-thread-safe-client --with-client-ldflags=-lstdc++ --with-unix-socket-path=/var/run/mysqld/mysqld.sock --with-berkeley-db=./bdb --with-readline --enable-shared --enable-static --with-libwrap --with-innodb --with-vio --with-openssl --without-debug I have tried dropping the following options, with no success: --with-low-memory, --with-berkeley-db, --with-innodb, --with-vio, --with-openssl My CFLAGS and CXXFLAGS for the build are as follows: CFLAGS=-march=athlon-xp -O3 -pipe -fomit-frame-pointer CXXFLAGS=-march=athlon-xp -O3 -pipe -fomit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti and I have also tried CFLAGS=-O3 CXXFLAGS=-O3 -felide-constructors -fno-exceptions -fno-rtti I have tried versions 3.23.52, 3.23.53, 3.23.54a, and 4.0.5-beta. For kicks, I also tried one of the builds while booted into a 2.4 kernel but had the same problem. There is no previous build of MySQL installed, nor are there any databases in the datadir. I am using ext3 partitions, but booting them as ext2 didn't change anything. I don't use NFS or anything like that. From what I can track down, I think the error is being triggered in file sql/table.cc in function openfrm. I can track down the specific conditionals in the function that are being triggered, if that would be helpful. I am at a loss. Any help would be very much appreciated. Thanks! Chun _ STOP MORE SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: NOW()-TIMESTAMP does not return accurate results
At 23:39 -0800 12/16/02, Troy Kruthoff wrote: Description: Invalid reporting of date calc How-To-Repeat: note: SESSIONTS is TIMESTAMP type SELECT (NOW()-SESSIONTS) FROM WEBSESSIONS; What leads you to expect that this should yield any useful result? +---+ | (NOW()-SESSIONTS) | +---+ | 1261 | +---+ 1 row in set (0.00 sec) mysql SELECT (UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(SESSIONTS)) FROM WEBSESSIONS; +---+ | (UNIX_TIMESTAMP(NOW())-UNIX_TIMESTAMP(SESSIONTS)) | +---+ | 748 | +---+ 1 row in set (0.00 sec) Fix: Cast fields as UNIX_TIMESTAMP to get valid seconds between to periods. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: more about using sets
Paul DuBois wrote:
I just tried this script:
[ ... ]
I don't see anything getting flipped.
Wow, I'm impressed :-). I've not actually tried this myself (as it has
been a known bug in at least other db software). Good to know that
MySQL has no such problem.
Now testing:
create table testenum (
val enum('false','true')
);
insert into testenum values ('true'),('true'),('false');
alter table testenum change val val enum('unknown','true','false');
select val from testenum;
true
true
false
*is impressed* ... data consistency is good (remarks about it being a
problem with MySQL withdrawn).
--
Michael T. Babcock
C.T.O., FibreSpeed Ltd.
http://www.fibrespeed.net/~mbabcock
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: remote tcp connection failure
i shoved it up my ass and things are now running great. Thanks for your invaluable assistance. Now if anyone on this list can be more helpful, I might just have an orgasm, as well as a fix to my 'problem'. Where is Paul DuBois when you need him? MY friend Jason says to look for him on here. For those SERIOUS about helping others, see below for my initial message that prompted the shove it up your ass solution. Kris comcast.net wrote: yea i have an idea shove it up your ass - Original Message - From: Kristopher Yates [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, December 17, 2002 9:07 AM Subject: remote tcp connection failure Hello list, I have been running MySQL 3.23.51 on a 4.6 FreeBSD box for some time with no problem. Recently, a client requested the ability to connect from remote. Until now, I only allow connections from localhost and no TCP port open. I have been going through the docs trying to figure out how to get tcp working, and/or ssh. I can ssh into the box no problem and use my ssh client to create a socket local:3306 to remote:3306. I also restarted MySQLd to allow tcp: ./mysql-server.sh start 20503 p0 I 0:00.01 /bin/sh /usr/local/bin/safe_mysqld --user=mysql --port=3306 20521 p0 S 0:00.03 /usr/local/libexec/mysqld --basedir=/usr/local --datadir=/var/db/mysql --user=mysq (screen chops off) mysql client from command line of server connects fine at localhost. php applications use mysql server fine via user/pass at localhost. still cant connect to server from remote using same user/pass. the user has perms to allow connects from my remote ip (host). Connection Failed: 2013 lost connection to MySQL server during query. This problem is when I try the tunnell AND ALSO when I try to connect directly to the box from a remote ip via TCP. My username/pass has permissions to allow tcp connect from my remote host IP. For now, if I could just get tcp to accept connections from a remote ip without ssh would be a nice start. Can anyone help? I have read the docs and still cant get it to work. Any ideas? Thanks kris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php . - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
replace(1) weirdness
as rejected by the spam filter: MySQL, query, blah blah. hello, i'm encountering some unexpected behavior in replace 3.23. when i pass it many replace pairs (only about twenty), its behavior becomes unpredictable. many times it does nothing at all, blatantly ignoring matches. other times it works fine. has anyone else had this experience with it? thanks, aaron. /\ | Aaron Brick (415) 206 - 4685 | | [EMAIL PROTECTED] Room 3501C, SFGH | Programmer / Analyst, Functional Genomics Core Sandler Center for Basic Research in Asthma Lung Biology Center, Department of Medicine San Francisco General Hospital Medical Center University of California at San Francisco - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: remote tcp connection failure
It sounds like you need to edit your hosts.allow file to allow from the IP you want to connect from. add either mysqld : ###.###.###.### : allow or mysqld : all : allow I'm not a sysadmin though, so you might want to do a search on the proper way to do this. -Original Message- From: Kristopher Yates [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 17, 2002 9:08 AM To: [EMAIL PROTECTED] Subject: remote tcp connection failure Hello list, I have been running MySQL 3.23.51 on a 4.6 FreeBSD box for some time with no problem. Recently, a client requested the ability to connect from remote. Until now, I only allow connections from localhost and no TCP port open. I have been going through the docs trying to figure out how to get tcp working, and/or ssh. I can ssh into the box no problem and use my ssh client to create a socket local:3306 to remote:3306. I also restarted MySQLd to allow tcp: ./mysql-server.sh start 20503 p0 I 0:00.01 /bin/sh /usr/local/bin/safe_mysqld --user=mysql --port=3306 20521 p0 S 0:00.03 /usr/local/libexec/mysqld --basedir=/usr/local --datadir=/var/db/mysql --user=mysq (screen chops off) mysql client from command line of server connects fine at localhost. php applications use mysql server fine via user/pass at localhost. still cant connect to server from remote using same user/pass. the user has perms to allow connects from my remote ip (host). Connection Failed: 2013 lost connection to MySQL server during query. This problem is when I try the tunnell AND ALSO when I try to connect directly to the box from a remote ip via TCP. My username/pass has permissions to allow tcp connect from my remote host IP. For now, if I could just get tcp to accept connections from a remote ip without ssh would be a nice start. Can anyone help? I have read the docs and still cant get it to work. Any ideas? Thanks kris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Primary key question
Hi, Does MySQL automatically create index on primary key ? If not how can I create an index on a primary key ? Thanks for any help. Patrice Serrand - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL 3.23.x preformance on MacOS 10.2
Hello all, I have checked the mailing list and the documentation but have been unable to find any information regarding the level of support for MySQL on the MacOS 10.2 platform. After moving a production database from a linux to MacOS 10.2 for development I have noticed that on my MacOS machine there is a significant performance hit on the MySQL server. Perhaps this is just hardware related but it seems as though the performance should be at least comparable on the following machines. Intel Pentium III 933 MHz Mandrake Linux v8.2 No windowing system running 512 MB RAM Query takes about 12 seconds. Apple G4 1GHz MacOS X 10.2 No windowing system running 512 MB RAM Query Takes about 25 seconds. I have noticed that on linux the mysqld runs as many processes and on MacOS 10.2 it runs as a single process. Is this an architectural decision? or have i configured the server incorrectly? Thank you, Christophe Banal - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL security vulnerabilites question
Hi, all, sql, query. Greetings. I got the following information. Does any one of you know whether it will impact my MySQL db server or not: Version 3.23.53 with InnoDB on Mac OS Darwin Kernel Version 6.2. If it does, what's the solution? Thank you, Jannie Qu === -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 17, 2002 8:57 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: [CLA-2002:555] Conectiva Linux Security Announcement - MySQL -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 - -- CONECTIVA LINUX SECURITY ANNOUNCEMENT - -- PACKAGE : MySQL SUMMARY : Several Vulnerabilities DATE : 2002-12-17 11:51:00 ID: CLA-2002:555 RELEVANT RELEASES : 6.0, 7.0, 8 - - DESCRIPTION MySQL is a very popular SQL database, distributed under the GNU-GPL license. Stefan Esser from e-matters[1] discovered several vulnerabilities in the MySQL code that affect both the server and the client library (libmysql) of MySQL. The server vulnerabilities can be exploited to crash the MySQL server, bypass password restrictions or even execute arbitrary code with the privileges of the user running the server process. The library ones consist in an arbitrary size heap overflow and a memory addressing problem that can be both exploited to crash or execute arbitrary code in programs linked against libmysql. More details about each vulnerability can be found in the e-matters security advisory[2]. The Common Vulnerabilities and Exposures project (cve.mitre.org) is tracking these issues with the names CAN-2002-1373, CAN-2002-1374, CAN-2002-1375 and CAN-2002-1376. SOLUTION We recommend that all MySQL users upgrade their packages as soon as possible. IMPORTANT: after the upgrade the mysql service must be restarted manually. In order to do that, run the following command as root: # /sbin/service mysql restart It is also recomended to restart all programs linked against libmysql. A list of such programs in execution can be obtained with the following command: # /usr/sbin/lsof | grep libmysql REFERENCES: 1.http://www.e-matters.de/ 2.http://security.e-matters.de/advisories/042002.html 3.http://cve.mitre.org/cgi-bin/cvename.cgi?name=CAN-2002-1373 4.http://cve.mitre.org/cgi-bin/cvename.cgi?name=CAN-2002-1374 5.http://cve.mitre.org/cgi-bin/cvename.cgi?name=CAN-2002-1375 6.http://cve.mitre.org/cgi-bin/cvename.cgi?name=CAN-2002-1376 UPDATED PACKAGES ftp://atualizacoes.conectiva.com.br/6.0/RPMS/MySQL-3.23.36-14U60_3cl.i386.rpm ftp://atualizacoes.conectiva.com.br/6.0/RPMS/MySQL-bench-3.23.36-14U60_3cl.i386.rpm ftp://atualizacoes.conectiva.com.br/6.0/RPMS/MySQL-client-3.23.36-14U60_3cl.i386.rpm ftp://atualizacoes.conectiva.com.br/6.0/RPMS/MySQL-devel-3.23.36-14U60_3cl.i386.rpm ftp://atualizacoes.conectiva.com.br/6.0/RPMS/MySQL-devel-static-3.23.36-14U60_3cl.i386.rpm ftp://atualizacoes.conectiva.com.br/6.0/RPMS/MySQL-doc-3.23.36-14U60_3cl.i386.rpm ftp://atualizacoes.conectiva.com.br/6.0/SRPMS/MySQL-3.23.36-14U60_3cl.src.rpm ftp://atualizacoes.conectiva.com.br/7.0/RPMS/MySQL-3.23.36-14U70_3cl.i386.rpm ftp://atualizacoes.conectiva.com.br/7.0/RPMS/MySQL-bench-3.23.36-14U70_3cl.i386.rpm ftp://atualizacoes.conectiva.com.br/7.0/RPMS/MySQL-client-3.23.36-14U70_3cl.i386.rpm ftp://atualizacoes.conectiva.com.br/7.0/RPMS/MySQL-devel-3.23.36-14U70_3cl.i386.rpm ftp://atualizacoes.conectiva.com.br/7.0/RPMS/MySQL-devel-static-3.23.36-14U70_3cl.i386.rpm ftp://atualizacoes.conectiva.com.br/7.0/RPMS/MySQL-doc-3.23.36-14U70_3cl.i386.rpm ftp://atualizacoes.conectiva.com.br/7.0/SRPMS/MySQL-3.23.36-14U70_3cl.src.rpm ftp://atualizacoes.conectiva.com.br/8/RPMS/MySQL-3.23.46-4U80_2cl.i386.rpm ftp://atualizacoes.conectiva.com.br/8/RPMS/MySQL-bench-3.23.46-4U80_2cl.i386.rpm ftp://atualizacoes.conectiva.com.br/8/RPMS/MySQL-client-3.23.46-4U80_2cl.i386.rpm ftp://atualizacoes.conectiva.com.br/8/RPMS/MySQL-devel-3.23.46-4U80_2cl.i386.rpm ftp://atualizacoes.conectiva.com.br/8/RPMS/MySQL-devel-static-3.23.46-4U80_2cl.i386.rpm ftp://atualizacoes.conectiva.com.br/8/RPMS/MySQL-doc-3.23.46-4U80_2cl.i386.rpm ftp://atualizacoes.conectiva.com.br/8/SRPMS/MySQL-3.23.46-4U80_2cl.src.rpm ADDITIONAL INSTRUCTIONS Users of Conectiva Linux version 6.0 or higher may use apt to perform upgrades of RPM packages: - run: apt-get update - after that, execute: apt-get upgrade Detailed instructions reagarding the use of apt and upgrade examples can be found at http://distro.conectiva.com.br/atualizacoes/#apt?idioma=en - - All packages are signed with Conectiva's GPG key. The key and instructions
RE: Access to MySQL
Thanks to all who replied! I was just looking at MySQL Front. Thanks all, Yves Arsenault Carrefour Infotech 5, Acadian Dr. Charlottetown, PEI C1C 1M2 [EMAIL PROTECTED] (902)368-1895 ext.242 -Original Message- From: Grant Cooper [mailto:[EMAIL PROTECTED]] Sent: December 17, 2002 12:24 PM To: 'Charles Mabbott'; [EMAIL PROTECTED]; '1Mysql' Subject: RE: Access to MySQL There are many tools. Use a gui. mysql front (free), premium soft (my pick but has a 29 day trial). There are also many small window applications to do this. Do a search at one of the famous downloads. +client +mysql +gui -Original Message- From: Charles Mabbott [mailto:[EMAIL PROTECTED]] Sent: Monday, December 16, 2002 1:45 PM To: [EMAIL PROTECTED]; '1Mysql' Subject: RE: Access to MySQL Yves, I am always open for cleaner method, but what I did was in MySQL created a table 'member' for data. In Access exported as .txt make sure tab sequential. Back in MySql I made database active that had 'member' And did a: mysql load data infile c:\\mysql\\tmp.txt into member; it actually went in quite cleanly. I am sure there are probably better ways, I am always willing to learn. But this is what worked for me today. Regards, Chuck -Original Message- From: Yves Arsenault [mailto:[EMAIL PROTECTED]] Sent: Monday, December 16, 2002 12:21 PM To: 1Mysql Subject: Access to MySQL Hello, Is there a small program that would transfer the contents of an Access database to MySQL? TIA, Yves Arsenault Carrefour Infotech 5,promenade Acadian Charlottetown, IPE C1C 1M2 [EMAIL PROTECTED] (902)368-1895 ext.242 ICQ #117650823 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: replace(1) weirdness
Please shove this up your ass - Original Message - From: Aaron Brick [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, December 17, 2002 10:05 AM Subject: replace(1) weirdness as rejected by the spam filter: MySQL, query, blah blah. hello, i'm encountering some unexpected behavior in replace 3.23. when i pass it many replace pairs (only about twenty), its behavior becomes unpredictable. many times it does nothing at all, blatantly ignoring matches. other times it works fine. has anyone else had this experience with it? thanks, aaron. /\ | Aaron Brick (415) 206 - 4685 | | [EMAIL PROTECTED] Room 3501C, SFGH | Programmer / Analyst, Functional Genomics Core Sandler Center for Basic Research in Asthma Lung Biology Center, Department of Medicine San Francisco General Hospital Medical Center University of California at San Francisco - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: replace(1) weirdness
your problem being? perhaps you fail to realize that replace(1) is part of the mysql distribution. aaron. so said [EMAIL PROTECTED] in 1.6K bytes at Tue, Dec 17, 2002: Please shove this up your ass - Original Message - From: Aaron Brick [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, December 17, 2002 10:05 AM Subject: replace(1) weirdness as rejected by the spam filter: MySQL, query, blah blah. hello, i'm encountering some unexpected behavior in replace 3.23. when i pass it many replace pairs (only about twenty), its behavior becomes unpredictable. many times it does nothing at all, blatantly ignoring matches. other times it works fine. has anyone else had this experience with it? thanks, aaron. /\ | Aaron Brick (415) 206 - 4685 | | [EMAIL PROTECTED] Room 3501C, SFGH | Programmer / Analyst, Functional Genomics Core Sandler Center for Basic Research in Asthma Lung Biology Center, Department of Medicine San Francisco General Hospital Medical Center University of California at San Francisco - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php /\ | Aaron Brick (415) 206 - 4685 | | [EMAIL PROTECTED] Room 3501C, SFGH | Programmer / Analyst, Functional Genomics Core Sandler Center for Basic Research in Asthma Lung Biology Center, Department of Medicine San Francisco General Hospital Medical Center University of California at San Francisco - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
JOIN + GROUP BY question
Hi, I have two tables: one holding bids for an auction (table bids) and one holding user data who placed the bids (users). I would like to get the highest bid, the user who placed the bid and the number of bids placed, so I use the following query: SELECT MAX(bids.amount) AS amount, COUNT(bids.itemid) AS bidcount, bids.bidderid, users.username FROM bids LEFT JOIN users ON bids.bidderid=users.userid WHERE bids.itemid = 71580 AND users.userid IS NOT NULL GROUP BY bids.itemid; What I get is not totally OK: amount is OK, COUNT is also OK, but the user (users.username) I get is not the one who placed the highest bid, but someone in the middle. How come? Am I misunderstanding something about GROUP BY on a joined table? THX, - Csongor - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL security vulnerabilites question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Tue, 17 Dec 2002, Jannie Qu wrote: I got the following information. Does any one of you know whether it will impact my MySQL db server or not: Version 3.23.53 with InnoDB on Mac OS Darwin Kernel Version 6.2. If it does, what's the solution? Yes, an unpatched MySQL 3.23.53 is vulnerable to the mentioned security problems. We provide updated binaries for Mac OS X 10.2 on our download web pages: http://www.mysql.com/downloads/mysql-3.23.html Bye, LenZ - -- For technical support contracts, visit https://order.mysql.com/?ref=mlgr __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mr. Lenz Grimmer [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Production Engineer /_/ /_/\_, /___/\___\_\___/ Hamburg, Germany ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.0 (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQE9/4SISVDhKrJykfIRAhacAJ9F2DS04VyE3Pk4NyaFd03JteZD5QCfd4WU QKts7z3+AZ/TkXK+vaK4VcI= =bQUF -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Upgrading MySQL/Solaris 2.7
I want to upgrade my MySQL version (3.23.51) because of the recently revealed exploit; the only binary dist on mysql.com for solaris 2.7 is 3.23.53; will there be a problem compiling from source on this platform or should I wait for the binaries to be updated? There is a .54 release for 2.8 and 2.9 but not for 2.7. Or does this exploit not affect Solaris? Thanks John - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Upgrading MySQL/Solaris 2.7
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, 17 Dec 2002, John P wrote: I want to upgrade my MySQL version (3.23.51) because of the recently revealed exploit; the only binary dist on mysql.com for solaris 2.7 is 3.23.53; will there be a problem compiling from source on this platform or should I wait for the binaries to be updated? There is a .54 release for 2.8 and 2.9 but not for 2.7. Or does this exploit not affect Solaris? Unfortunately it does affect all platforms. Sadly, our Solaris 2.7 build host has died (hardware failure) and we are currently unable to provide a 3.23.54 binary for Solaris 2.7 - sorry about that. However, I've already received a hint from a user who is using our Solaris 2.8 binary on a 2.7 system - you might want to try that first before doing a compile by yourself. Bye, LenZ - -- For technical support contracts, visit https://order.mysql.com/?ref=mlgr __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mr. Lenz Grimmer [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Production Engineer /_/ /_/\_, /___/\___\_\___/ Hamburg, Germany ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.0 (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQE9/4ijSVDhKrJykfIRAsOqAJ46tYXvOiNnT7I8wqiVEYToO9WrTQCeNzPs AQzcmaregampBpI8ff/B+Rs= =FVJq -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: replace(1) weirdness
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, 17 Dec 2002, Aaron Brick wrote: i'm encountering some unexpected behavior in replace 3.23. when i pass it many replace pairs (only about twenty), its behavior becomes unpredictable. many times it does nothing at all, blatantly ignoring matches. other times it works fine. has anyone else had this experience with it? We're not aware of such a behaviour - could you try to create a reproducable test case? Otherwise I fear it's impossible for us to do anything about it... Bye, LenZ - -- For technical support contracts, visit https://order.mysql.com/?ref=mlgr __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mr. Lenz Grimmer [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Production Engineer /_/ /_/\_, /___/\___\_\___/ Hamburg, Germany ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.0 (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQE9/4j1SVDhKrJykfIRAlNAAJ4yaqg1LhTSSoLshnUJOJ17Hf6a8gCdH5lW Fw67pzQNzixn+hlXwjfPscI= =WEXI -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL and PHP question
Hi, Why is it that I can not use two mysql queries in the same PHP file. I have tried to do this several times trying to put my site together and have had nothing but problems. The recent one is this: I use a SELECT statement to get some info from a table, then I modify that info and use an UPDATE statement to update another table. The update does not work (all variables used by PHP contain the proper values as does the info selected from the table). I get no errors as it appears the update worked, but the table was not updated. I have also found that I can not do two select statements in the same PHP file, as the second one will not contain any data . The query does not fail, there is just no data. Any info is appreciated. TIA - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Tomcat 4.1.12 and maybe mysql
Same message I posted at JGuru and on the JDJ Mailing list, but posting here just incase it is mysql related and someone can help On RedHat 7.0 RedHat 7.3 with Java 1.4.0_03 When page is hit fairly quickly I get the following error: 2002-12-17 14:56:37 StandardWrapperValve[jsp]: Servlet.service() for servlet jsp threw exception org.apache.jasper.JasperException: Socket closed at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:248) at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:289) at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:240) at javax.servlet.http.HttpServlet.service(HttpServlet.java:853) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:260) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995) at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2396) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643) at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:170) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:172) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174) at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643) at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480) at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995) at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:223) at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:405) at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:380) at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:508) at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:533) at java.lang.Thread.run(Thread.java:536) - Root Cause - java.net.SocketException: Socket closed at java.net.SocketOutputStream.socketWrite0(Native Method) at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:92) at java.net.SocketOutputStream.write(SocketOutputStream.java:126) at org.apache.coyote.http11.InternalOutputBuffer$OutputStreamOutputBuffer.doWrite(InternalOutputBuffer.java:652) at org.apache.coyote.http11.filters.ChunkedOutputFilter.doWrite(ChunkedOutputFilter.java:166) at org.apache.coyote.http11.InternalOutputBuffer.doWrite(InternalOutputBuffer.java:523) at org.apache.coyote.Response.doWrite(Response.java:513) at org.apache.coyote.tomcat4.OutputBuffer.realWriteBytes(OutputBuffer.java:380) at org.apache.tomcat.util.buf.ByteChunk.flushBuffer(ByteChunk.java:360) at org.apache.coyote.tomcat4.OutputBuffer.flush(OutputBuffer.java:341) at org.apache.coyote.tomcat4.CoyoteResponse.flushBuffer(CoyoteResponse.java:554) at org.apache.coyote.tomcat4.CoyoteResponseFacade.flushBuffer(CoyoteResponseFacade.java:227) at org.apache.jasper.runtime.JspWriterImpl.flush(JspWriterImpl.java:211) at org.apache.jasper.runtime.JspRuntimeLibrary.include(JspRuntimeLibrary.java:808) at org.apache.jsp.index_jsp._jspService(index_jsp.java:75) at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:136) at javax.servlet.http.HttpServlet.service(HttpServlet.java:853) at
Re: MySQL and PHP question
Why is it that I can not use two mysql queries in the same PHP file. You can, I assure you. Any info is appreciated. Perhaps if you could provide your code? W/o it there is no way we can determine where the problem might be coming from. Chris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql-max-3.23.54a binary DOES NOT WORK! GLIBC MISSING!
I tried to use mysql-max-3.23.54a but it isn't statically linked against GBLIB 2.2 Please also note the file size: mysql-3.23.54a-pc-linux-i686.tar.gz is 9831919 mysql-max-3.23.54a-pc-linux-i686.tar.gz is 6097043 At the startup it prints out: /lib/libc.so.6: version `GLIBC_2.2' not found Bye Nat -- Natalino Picone - [EMAIL PROTECTED] -- It's a horrible thing to be on top of the world and then to lose it and try to get it back. It's a whole lot harder the second time. -- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL and PHP question
On Wed, 18 Dec 2002 16:05:35 -0500, C. Reeve wrote: I can not use two mysql queries in the same PHP file Please post code snippets, this is possible. - Steve Yates - Burger Borg: We do it our way; yours is irrelevant. ~ Taglines by Taglinator - www.srtware.com ~ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Can not write to a file in /home/.....
I executed the following statement:
SELECT * INTO OUTFILE '/home/medic/outfile.txt' FROM fool;
I get the error meassge
Can't create/write to file '/home/medic/outfile.txt' (Errcode:13)
I followed the solutions from the manual A.3.3 Problems with File
Permissions
and still have a problem.
I created a my.cnf and when I put in the line:
set-varibale = UMASK=0777 in the {mysqld} section - the deamon will not
start.
I created a script as follows:
UMASK=0777
export UMASK
UMASK_DIR=0777
export UMASK_DIR
etc/bin/safe_mysqld
Can anyone help
sam,
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Assertion failed in buf0buf.ic
Chris, - Original Message - From: [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Tuesday, December 17, 2002 7:28 PM Subject: Assertion failed in buf0buf.ic I am receiving an 'Assertion failed' error in my log and I am not really sure how to continue. What I was trying to do was start the MySQLd (v 3.23.53) with support for innodb (it is compiled in) on a FreeBSD 4.7-stable server. I used the settings in my-large.cnf as a template. The server in question is Dual P3-866, 1GB memory. If Ive included too much, or too little information, please let me know. what did InnoDB print when you tried to create the InnoDB database? Please delete ibdata files, ib_logfiles, ib_arch_logfile and try again. ... Thanks! - Chris Kulish Systems Engineer ING Advisors Network Ph. (515) 698-7583 Fx. (515) 698-3583 Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB sql query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: innodb/mysql slow returning anything other than primary key
Walt, - Original Message - From: walt [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Tuesday, December 17, 2002 1:58 AM Subject: innodb/mysql slow returning anything other than primary key I've run into an interesting problem. I have a large innodb table (2274962 rows, 46 columns, 2 datafiles - 4.5GB total). When I run a query that uses a clustered_index in the where clause and the data I'm selecting is not the primary key of the table, it takes up to 2 1/2 minutes to return zero results (the result is correct). If I select the primary key, it takes 2 1/2 seconds to return zero results. For example: create table xray ( trans_id int not null, customer_id char(25) not null, customer_last_name char(25), UNIQUE INDEX trans_idx (trans_id), INDEX cus_id (customer_id ) ); type=INNODB; SELECT customer_last_name FROM XRAY WHERE customer_id = '12345'; This takes ~ 2 1/2 minutes to return 0 results. SELECT trans_id FROM XRAY WHERE customer_id = '12345'; This takes ~ 2 1/2 SECONDS. do you have a hanging transaction which prevents purge from removing delete-marked rows? What does SHOW INNODB STATUS say? What does EXPLAIN SELECT ... print for each query? What does SHOW TABLE STATUS FROM databasename LIKE 'tablename' say? For each query also do: run SHOW STATUS and SHOW INNODB STATUS, run the query once, and run SHOW STATUS and SHOW INNODB STATUS again. Send the outputs to me. How many customer ids are there in the table? If there are no matching rows, the query should return in 100 ms. From what I understand, clustered indexes contain the primary key as well as the indexed data and return that key as a pointer to the row where the data being selected is located. This explains why selecting the key is so quick, BUT if no values are found in the index that match the where clause, why is it taking so long? I've run explain and it's telling me the database is using the correct index. I've even added USE INDEX to sql query with no improvement. The machine is an AMD Athlon XP 2000 with 1GB of ram. Mysql version is 3.23.53 OS = Linux 2.4.18-SGI_XFS_1.1enterprise Thanks for any ideas or suggestions! -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 If it's not broketweak it Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: innodb/mysql slow returning anything other than primary key
Walt, you can also test 3.23.54. The following bug fix may be relevant here: MySQL/InnoDB-3.23.54, December 12, 2002 Fixed a bug: the InnoDB range estimator greatly exaggerated the size of a short index range if the paths to the endpoints of the range in the index tree happened to branch already in the root. This could cause unnecessary table scans in SQL queries. ... Regards, Heikki - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, December 18, 2002 1:33 AM Subject: Re: innodb/mysql slow returning anything other than primary key Walt, - Original Message - From: walt [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Tuesday, December 17, 2002 1:58 AM Subject: innodb/mysql slow returning anything other than primary key I've run into an interesting problem. I have a large innodb table (2274962 rows, 46 columns, 2 datafiles - 4.5GB total). When I run a query that uses a clustered_index in the where clause and the data I'm selecting is not the primary key of the table, it takes up to 2 1/2 minutes to return zero results (the result is correct). If I select the primary key, it takes 2 1/2 seconds to return zero results. For example: create table xray ( trans_id int not null, customer_id char(25) not null, customer_last_name char(25), UNIQUE INDEX trans_idx (trans_id), INDEX cus_id (customer_id ) ); type=INNODB; SELECT customer_last_name FROM XRAY WHERE customer_id = '12345'; This takes ~ 2 1/2 minutes to return 0 results. SELECT trans_id FROM XRAY WHERE customer_id = '12345'; This takes ~ 2 1/2 SECONDS. do you have a hanging transaction which prevents purge from removing delete-marked rows? What does SHOW INNODB STATUS say? What does EXPLAIN SELECT ... print for each query? What does SHOW TABLE STATUS FROM databasename LIKE 'tablename' say? For each query also do: run SHOW STATUS and SHOW INNODB STATUS, run the query once, and run SHOW STATUS and SHOW INNODB STATUS again. Send the outputs to me. How many customer ids are there in the table? If there are no matching rows, the query should return in 100 ms. From what I understand, clustered indexes contain the primary key as well as the indexed data and return that key as a pointer to the row where the data being selected is located. This explains why selecting the key is so quick, BUT if no values are found in the index that match the where clause, why is it taking so long? I've run explain and it's telling me the database is using the correct index. I've even added USE INDEX to sql query with no improvement. The machine is an AMD Athlon XP 2000 with 1GB of ram. Mysql version is 3.23.53 OS = Linux 2.4.18-SGI_XFS_1.1enterprise Thanks for any ideas or suggestions! -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 If it's not broketweak it Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select / Calculate question
I don't think this can be done in a single query, but what about: SELECT Reference.id1, Reference.id2, sum(Table1.number) FROM Table1, Reference WHERE Reference.id1=Table1.id1 GROUP BY Reference.id1 Which gives you: +--+--++ | id1 | id2 | sum(Table1.number) | +--+--++ |1 |1 | 60 | |2 |2 | 90 | +--+--++ or: SELECT Reference.id1, Reference.id2, Table2.name, sum(Table1.number) FROM Table1, Table2, Reference WHERE Reference.id1=Table1.id1 And Reference.id2=Table2.id2 and Table2.name='John' GROUP BY Reference.id1 +--+--+--++ | id1 | id2 | name | sum(Table1.number) | +--+--+--++ |1 |1 | JOhn | 60 | +--+--+--++ Then you'd have to do a separate UPDATE query (or queries) to get the sum into Table2. If Table2.id2 is a foreign key, though, you wouldn't even need the Reference table at all: select id1, sum(Table1.number) from Table1 group by id1 +--++ | id1 | sum(Table1.number) | +--++ |1 | 60 | |2 | 90 | +--++ ... and then the update, separately. -bill C. Reeve wrote: Hi, I have one table with an id1 column and a number column, these numbers correspond to another table with an id2 column and a name column and a total column. I also have a reference table with ties the two together (an id1 and id2 column). I want to be able to select all numbers in the first table that correspond to the name in the second column and add them together and place them in the total column of the second table, but whatever I have done has failed. Can anyone suggest a way of doing this? TIA Table1Table2Reference id1 number id2nametotalid1id2 1101John 6011 1202Mary 9022 130etc. etc. 240 250 etc. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL and PHP question
I can... Hard to know why without more info but a starting point would be to turn on the query log in MySQL and see what queries it is receiving. _ Fraser Stuart Logistics IT 77-85Phone: +61 2 9335 1235 Roberts Rd Mobile: +61 419 233 732 Greenacre NSW [EMAIL PROTECTED] Australia 2190 www.toll.com.au _ | -Original Message- | From: C. Reeve [mailto:[EMAIL PROTECTED]] | Sent: Thursday, 19 December 2002 8:06 AM | To: MySQL List | Subject: MySQL and PHP question | | | Hi, | | Why is it that I can not use two mysql queries in the same PHP | file. I have | tried to do this several times trying to put my site together and have had | nothing but problems. | | The recent one is this: | | I use a SELECT statement to get some info from a table, then I modify that | info and use an UPDATE statement to update another table. The update does | not work (all variables used by PHP contain the proper values as does the | info selected from the table). | | I get no errors as it appears the update worked, but the table was not | updated. | | I have also found that I can not do two select statements in the same PHP | file, as the second one will not contain any data . The query | does not fail, | there is just no data. | | Any info is appreciated. | | TIA | | | | | | - | Before posting, please check: |http://www.mysql.com/manual.php (the manual) |http://lists.mysql.com/ (the list archive) | | To request this thread, e-mail [EMAIL PROTECTED] | To unsubscribe, e-mail | [EMAIL PROTECTED] | Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php | | - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Can not write to a file in /home/.....
Did you also check directory permissions? What system are you using?
Chuck
http://68.43.100.7:81/aa8vs
==
Patriotism is the willingness to kill and
be killed for trivial reasons.
- Bertran Russell
-Original Message-
From: sam [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, December 17, 2002 5:51 PM
To: MySQL List
Subject: Re: Can not write to a file in /home/.
I executed the following statement:
SELECT * INTO OUTFILE '/home/medic/outfile.txt' FROM fool;
I get the error meassge
Can't create/write to file '/home/medic/outfile.txt' (Errcode:13)
I followed the solutions from the manual A.3.3 Problems with File
Permissions
and still have a problem.
I created a my.cnf and when I put in the line:
set-varibale = UMASK=0777 in the {mysqld} section - the deamon will not
start.
I created a script as follows:
UMASK=0777
export UMASK
UMASK_DIR=0777
export UMASK_DIR
etc/bin/safe_mysqld
Can anyone help
sam,
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Can MySQL handle 120 million records?
hi We are evaluating few databases for developing an application with following specs, 1. OS not very important. Leaning towards Linux 2. Currently the database has about 5 million records but it will grow to 120 million records. 3. The tables will have billing information for a telecom company. Nothing complex. 4. Back office staff will use the data in the database to create invoices to be sent to customers. This data is not connected to the live telecom system [e.g. switches etc]. We get the data every day from the telecom company. 5. Staff may perform queries on the database to get reports like busiest hour of the day etc etc. I don't see too many concurrent users using the system, however the system needs to be stable. 6. Need to create excel, pdf files from the data in the database. This I think has nothing to do with the database, however this is a requirement. 7. Needless to say, good security is a must which will also be built into the front end application. We are considering the following databases, 1. MYSQL 2. Postgres 3. Oracle 4. MSQL If MYSQL or Postgres can do the job I prefer not to spend the money on Oracle/MSQL. However, if Oracle/MSQL are required for getting good reports and scalability, so be it. We will use Oracle/MSQL. Any pointers/advice is appreciated -- -- B.G. Mahesh mailto:[EMAIL PROTECTED] http://www.indiainfo.com/ India's first ISO certified portal - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Can MySQL handle 120 million records?
On Wed, Dec 18, 2002 at 08:10:41AM +, B.G. Mahesh wrote: If MYSQL or Postgres can do the job I prefer not to spend the money on Oracle/MSQL. However, if Oracle/MSQL are required for getting good reports and scalability, so be it. We will use Oracle/MSQL. MySQL will have no problem with 120 million records, as long as you have decent hardware. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 2 days, processed 107,599,733 queries (426/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: remote tcp connection failure
yea, me too. but being second just isnt as good... but did you grant the user permissions on the database they want to connect too? can you connect to anything listening on 3306? htttp? when i connect from remote over tcp its mysql -h hostIP -p -u someuser somedatabase the firewall accepts the connect and NAT's it to the right machine inside Jim -Original Message- From: Kristopher Yates [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 17, 2002 10:04 AM To: comcast.net; [EMAIL PROTECTED] Subject: Re: remote tcp connection failure i shoved it up my ass and things are now running great. Thanks for your invaluable assistance. Now if anyone on this list can be more helpful, I might just have an orgasm, as well as a fix to my 'problem'. Where is Paul DuBois when you need him? MY friend Jason says to look for him on here. For those SERIOUS about helping others, see below for my initial message that prompted the shove it up your ass solution. Kris comcast.net wrote: yea i have an idea shove it up your ass - Original Message - From: Kristopher Yates [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, December 17, 2002 9:07 AM Subject: remote tcp connection failure Hello list, I have been running MySQL 3.23.51 on a 4.6 FreeBSD box for some time with no problem. Recently, a client requested the ability to connect from remote. Until now, I only allow connections from localhost and no TCP port open. I have been going through the docs trying to figure out how to get tcp working, and/or ssh. I can ssh into the box no problem and use my ssh client to create a socket local:3306 to remote:3306. I also restarted MySQLd to allow tcp: ./mysql-server.sh start 20503 p0 I 0:00.01 /bin/sh /usr/local/bin/safe_mysqld --user=mysql --port=3306 20521 p0 S 0:00.03 /usr/local/libexec/mysqld --basedir=/usr/local --datadir=/var/db/mysql --user=mysq (screen chops off) mysql client from command line of server connects fine at localhost. php applications use mysql server fine via user/pass at localhost. still cant connect to server from remote using same user/pass. the user has perms to allow connects from my remote ip (host). Connection Failed: 2013 lost connection to MySQL server during query. This problem is when I try the tunnell AND ALSO when I try to connect directly to the box from a remote ip via TCP. My username/pass has permissions to allow tcp connect from my remote host IP. For now, if I could just get tcp to accept connections from a remote ip without ssh would be a nice start. Can anyone help? I have read the docs and still cant get it to work. Any ideas? Thanks kris - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php . - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Can MySQL handle 120 million records?
+ On Wed, Dec 18, 2002 at 08:10:41AM +, B.G. Mahesh wrote: + + If MYSQL or Postgres can do the job I prefer not to spend the money on + Oracle/MSQL. However, if Oracle/MSQL are required for getting good + reports and scalability, so be it. We will use Oracle/MSQL. + + MySQL will have no problem with 120 million records, as long as you + have decent hardware. hi Thanks for the quick response. My question may not be very valid..but is there a upper limit on the number of records it can handle? Hardware wise, I guess if I have atleast 512MB RAM, P4 Intel motherboard I should be fine. Isn't it? -- -- B.G. Mahesh mailto:[EMAIL PROTECTED] http://www.indiainfo.com/ India's first ISO certified portal - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql dreamweaver
hello, i have access to mysql db i created, using a client app even on a remote pc (adding tables, fields, values) but when i try to connect with dreamweaver i get an unknown error, or unexpected. what do i need to do to us dreamweaver on my development machine to access an mysql db on my server? i have the pages hosted on my server but when i develop i use my dev machine, newbie charles r - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
table myisam and roll back?
Hi, May I know that if it is posibble to roll back the sql query automatic if the sql query get error by using myisam table type and not using innodb type? Thank for help. __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: table myisam and roll back?
CN YEONG wrote: Hi, May I know that if it is posibble to roll back the sql query automatic if the sql query get error by using myisam table type and not using innodb type? Thank for help. No. You need to use a transactional table handler, like InnoDB. -- Daniel Kasak IT Developer * NUS Consulting Group* Level 18, 168 Walker Street North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: www.nusconsulting.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: table myisam and roll back?
At 19:45 -0800 12/17/02, CN YEONG wrote: Hi, May I know that if it is posibble to roll back the sql query automatic if the sql query get error by using myisam table type and not using innodb type? No. MyISAM isn't a transaction-safe table type. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL and PHP question
I've since deleted the code and did it another way. The code however went
something like this. This is just a mock up (there is more being selected
etc and there are some joins, but the syntax is exact).
So what happens is the select statement with $user1 in it gets the proper
values (the first one in the example), the other one doesn't, but if I
change $user1 and $user2 around the second select will get the values.
So on the surface it sounds like a problem with $user2, but I have run
extensive tests and $user2 gets the proper values (they both come from a
form).
In another project I am doing, I tried a similar feat with totally different
values and got the same results. This time I was trying to do one SELECT and
one UPDATE.
If anyone has any ideas it would be appreciated. Also, if there is a better
way to do the following this would also be appreciated.
$query select number from table1 where userid=$user1;
$result = mysql_query($query) or die(Query failed);
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
$num1 = $line['number'];
}
$query select number from table2 where userid=$user2;
$result = mysql_query($query) or die(Query failed);
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
$num2 = $line['number'];
}
$total = $num1 + $num2;
mysql_query(UPDATE table3 SET total = '$total' WHERE userid=$user);
TIA
- Original Message -
From: Steve Yates [EMAIL PROTECTED]
To: MySQL List [EMAIL PROTECTED]
Sent: Tuesday, December 17, 2002 5:12 PM
Subject: Re: MySQL and PHP question
On Wed, 18 Dec 2002 16:05:35 -0500, C. Reeve wrote:
I can not use two mysql queries in the same PHP file
Please post code snippets, this is possible.
- Steve Yates
- Burger Borg: We do it our way; yours is irrelevant.
~ Taglines by Taglinator - www.srtware.com ~
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem with Query based on HTML form values
Hello, I am attempting to search a table based on information passed from a submitted form. The form contains details as follows: (radio buttons) Condition: O new O used Type: O powerO sail (textbox) Make: [ ] etc. etc. etc. ( it's a long list!) If I write a query ie. Select from boat where condition like '$condition' or type like '$type' or make like '$make' etc.. I get a search result that includes boats with any of the criteria that match one of the like comparisons, ie.returns all new boats + all sail boats + all boats of type X which makes sense What I really want is a result that is limited to a group of boats that individually match the submitted criteria but excludes the boats outside the setie. returns only boats which are new, sail, type X boats. If anyone can help me with sage advice on how to go about achieving this end, I would be highly grateful. I am stuck and may be missing something simple, or maybe there is more to what I am attempting than a simple query. Either way, pointers, tips, sample queries would be helpful and appreciated. Kris O. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Can MySQL handle 120 million records?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 BG -- ...and then B.G. Mahesh said... % ... % % Thanks for the quick response. My question may not be very valid..but is % there a upper limit on the number of records it can handle? I'll leave this to the experts. I'm almost certain there must be, though it's probably something like 4 billion or some such. % % Hardware wise, I guess if I have atleast 512MB RAM, P4 Intel motherboard I should % be fine. Isn't it? There was actually a fair bit of discussion about this (including the merits of various chips, their capabilities, and their speeds) on this list a bit back, and it's come up other times before; see the list archives for details. Basically it comes down to - figure out where your bottleneck will lie - RAM? disk I/O? CPU power? floating point math? 'net bandwidth? - build a system that caters to those bottlenecks You haven't said how large the records are, how many people will need to hit it at once, how much math will be involved, and what sort of peak load it will have to handle (it might sit basically idle for three and a half weeks and then be slammed generating invoices), but my loose guess is that you should get a dual-PIII or dual-Athlon box with 512M - 1024M of RAM and some good SCSI disks you can RAID. It won't be a $500 desktop, but it probably shouldn't cost you more than $2k to build a quite nice box (but don't forget to build a second for redundancy, perhaps as a slave server, since this is business and time is money). HTH HAND Happy Holidays mysql query, :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://www.justpickone.org/davidtg/Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE9//n/Gb7uCXufRwARAoZFAJ0W6SxXCR8pXD0bT4aS54lqlp1WiACgmMJk GbUxlpjbdzQ7kkEk8OOHgD4= =YzNx -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mysql command not found
I just installed mysql but I get the command not found when I type mysql. I am in the bin directory. Anyone know what is going on here? Thanks Gary - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Can not write to a file in /home/.....
chown -R mysql:mysql /home/medic/
and make sure there is no outfile.txt already in that
dir.
--- sam [EMAIL PROTECTED] wrote:
I executed the following statement:
SELECT * INTO OUTFILE '/home/medic/outfile.txt' FROM
fool;
I get the error meassge
Can't create/write to file
'/home/medic/outfile.txt' (Errcode:13)
I followed the solutions from the manual A.3.3
Problems with File
Permissions
and still have a problem.
I created a my.cnf and when I put in the line:
set-varibale = UMASK=0777 in the {mysqld} section -
the deamon will not
start.
I created a script as follows:
UMASK=0777
export UMASK
UMASK_DIR=0777
export UMASK_DIR
etc/bin/safe_mysqld
Can anyone help
sam,
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list
archive)
To request this thread, e-mail
[EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try:
http://lists.mysql.com/php/unsubscribe.php
__
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem with Query based on HTML form values
Hello, You're getting all new boats + all sail boats + all boats of type x because you're using ORs. If you only want records that match all the criteria, use ANDs instead. SELECT * FROM boat WHERE condition='$condition' AND type='$type' Or you might try SELECT * FROM boat WHERE (condition='new' OR condition='used') AND type='sail' You probably don't need to use LIKE since the form fields will always supply you with the same values. -bill web-dev wrote: Hello, I am attempting to search a table based on information passed from a submitted form. The form contains details as follows: (radio buttons) Condition: O new O used Type: O powerO sail (textbox) Make: [ ] etc. etc. etc. ( it's a long list!) If I write a query ie. Select from boat where condition like '$condition' or type like '$type' or make like '$make' etc.. I get a search result that includes boats with any of the criteria that match one of the like comparisons, ie.returns all new boats + all sail boats + all boats of type X which makes sense What I really want is a result that is limited to a group of boats that individually match the submitted criteria but excludes the boats outside the setie. returns only boats which are new, sail, type X boats. If anyone can help me with sage advice on how to go about achieving this end, I would be highly grateful. I am stuck and may be missing something simple, or maybe there is more to what I am attempting than a simple query. Either way, pointers, tips, sample queries would be helpful and appreciated. Kris O. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Mysql command not found
do this at the prompt... echo $PATH whereis mysql and compare the results. when in the bin directory type ./mysql as root... without ./ the shell wont find it... Jim -Original Message- From: Gary Hostetler [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 17, 2002 8:33 PM To: [EMAIL PROTECTED] Subject: Mysql command not found I just installed mysql but I get the command not found when I type mysql. I am in the bin directory. Anyone know what is going on here? Thanks Gary - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Can MySQL handle 120 million records?
I am not sure. Does anyone know any real examples of mysql handling huge database and still perform well? I am having problems with the performance with the MySQL Left join recently. A big table (about 2.5 million records) left join a small table (about 350K records) takes generally 2 mins to finish. I check the explain and primary key index on the small table was indeed used for the joining. My system is Redhat Linux 7.3 with 4 GB memory. I also tried replacing the default my.cnf with my-huge.cnf. It didn't help at all. Another thing, with some linux system, there is a size limit for file. MySQL seems to store each of its table as single file. You need to choose a file system without that limit. Qunfeng Dong --- B.G. Mahesh [EMAIL PROTECTED] wrote: hi We are evaluating few databases for developing an application with following specs, 1.OS not very important. Leaning towards Linux 2.Currently the database has about 5 million records but it will grow to 120 million records. 3.The tables will have billing information for a telecom company. Nothing complex. 4.Back office staff will use the data in the database to create invoices to be sent to customers. This data is not connected to the live telecom system [e.g. switches etc]. We get the data every day from the telecom company. 5.Staff may perform queries on the database to get reports like busiest hour of the day etc etc. I don't see too many concurrent users using the system, however the system needs to be stable. 6.Need to create excel, pdf files from the data in the database. This I think has nothing to do with the database, however this is a requirement. 7.Needless to say, good security is a must which will also be built into the front end application. We are considering the following databases, 1.MYSQL 2.Postgres 3.Oracle 4.MSQL If MYSQL or Postgres can do the job I prefer not to spend the money on Oracle/MSQL. However, if Oracle/MSQL are required for getting good reports and scalability, so be it. We will use Oracle/MSQL. Any pointers/advice is appreciated -- -- B.G. Mahesh mailto:[EMAIL PROTECTED] http://www.indiainfo.com/ India's first ISO certified portal - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Can MySQL handle 120 million records?
I've read limits are based on the filesize your OS can handle, the HDD size, memory, how fast your RISC or CISC processors are...and how the stars are aligned... i think 4 billion records will need some horses pullin... 8 x 2.4 XEON/2 at least. I've run simple queries on 80 million records and it takes minutes with a gig of RAM and 800 pentium III. each minute in front of a computer is like dog years... 1 = 7 Jim -Original Message- From: David T-G [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 17, 2002 8:31 PM To: mysql users Cc: B.G. Mahesh Subject: Re: Can MySQL handle 120 million records? -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 BG -- ...and then B.G. Mahesh said... % ... % % Thanks for the quick response. My question may not be very valid..but is % there a upper limit on the number of records it can handle? I'll leave this to the experts. I'm almost certain there must be, though it's probably something like 4 billion or some such. % % Hardware wise, I guess if I have atleast 512MB RAM, P4 Intel motherboard I should % be fine. Isn't it? There was actually a fair bit of discussion about this (including the merits of various chips, their capabilities, and their speeds) on this list a bit back, and it's come up other times before; see the list archives for details. Basically it comes down to - figure out where your bottleneck will lie - RAM? disk I/O? CPU power? floating point math? 'net bandwidth? - build a system that caters to those bottlenecks You haven't said how large the records are, how many people will need to hit it at once, how much math will be involved, and what sort of peak load it will have to handle (it might sit basically idle for three and a half weeks and then be slammed generating invoices), but my loose guess is that you should get a dual-PIII or dual-Athlon box with 512M - 1024M of RAM and some good SCSI disks you can RAID. It won't be a $500 desktop, but it probably shouldn't cost you more than $2k to build a quite nice box (but don't forget to build a second for redundancy, perhaps as a slave server, since this is business and time is money). HTH HAND Happy Holidays mysql query, :-D - -- David T-G * There is too much animal courage in (play) [EMAIL PROTECTED] * society and not sufficient moral courage. (work) [EMAIL PROTECTED] -- Mary Baker Eddy, Science and Health http://www.justpickone.org/davidtg/Shpx gur Pbzzhavpngvbaf Qrprapl Npg! -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.7 (FreeBSD) iD8DBQE9//n/Gb7uCXufRwARAoZFAJ0W6SxXCR8pXD0bT4aS54lqlp1WiACgmMJk GbUxlpjbdzQ7kkEk8OOHgD4= =YzNx -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: A Query problem
Hi!
I have an enigma that I just can't seem to resolve.
==
What is Hoped For:
I have 3 variables (called F1, F2 and F3) which will contain either an
integers (a pointer to a feeback item in another table, actually) and a
database of questions, each having an F1, F2 and F3, any one or more of
which can contain an integer or 0 (for null).
What I hope to do is do a comparison like this:
$myquery=MYSQL_QUERY(select count(uid) from TABLENAME where ( (F1='$F1')
|| (F2='$F1') || (F3='$F1') || (F1='$F2') || (F2='$F2') || (F3='$F2') ||
(F1='$F3') || (F2='$F3') || (F3='$F3') ));
In other words I wish to find out if one or more of three variables that may
contain an integer, matches any questions with feedback items F1, F2 and F3
(integers all). The variable F1 can match F1, F2 or F3 in the database, so
all comparisons must be tried.
==
The Problem:
Again, there are three fields in a table, F1, F2, and F3. They are numeric.
There are three variables passed by a php program, and I want to know if any
one of F1, F2 or F3 matches any one of F1, F2 or F3 in the table. Again,
since the variable F1 can match F1, F2 or F3 in the database, I want to do
all comparisons.
So using PHP I do this:
//set one variable to an integer (in the program being written, this may
occur naturally)
$F1='' $F2=2; $F3='';
//test to see if any of F1, F2 or F3 are set to some integer (otherwise it
is set to nothing at all)
$F1index=is_numeric($F1); //if F1 is numeric set F1index to 1
$F2index=is_numeric($F2); //if F2 is numeric set F2index to 1
$F3index=is_numeric($F3); //if F3 is numeric set F2index to 1
//Set any null values to some specific value (part of my efforts to resolve
the problem below)
if ($F1index 1){$F1='WWW';}
if ($F2index 1){$F2='WWW';}
if ($F3index 1){$F3='WWW';}
//The situation here is that the variable F1 is WWW and F2=2 and F3 is WWW.
In the database table there are 2 rows in which F1 is 0, F2=3 and F3=0.
$data=MYSQL_QUERY(select count(F1) from TABLENAME where (F1='$F1') ||
(F1='$F2') || (F1='$F3') );
//In the database, F1=0, F2=3 and F3=0. therefore, the above query results
in NO match, which is the case. Perfect.
BUT (and here is the rub) if I do this:
$data=MYSQL_QUERY(select count(uid) from TABLENAME where ( (F1='$F1') ||
(F2='$F1') || (F3='$F1') || (F1='$F2') || (F2='$F2') || (F3='$F2') ||
(F1='$F3') || (F2='$F3') || (F3='$F3') ));
//BOTH records in the table match successfully. I thought that none ought
to match as variable F1, F2 and F3 are all NOT 3.
//wondering what is going on I tried a scaled-down, simpler query
$data=MYSQL_QUERY(select count(F1) from QQwowen where (F1='$F1') ||
(F1='$F2') || (F1='$F3') || (F2='GOOOBAAA') );
//I put something ridiculous ('Gooobaaa') to compare to F2 (F1=0, F2=3 and
F3=0 in the table) and
this query matches ALL records in the table.
I'm certain that I'm missing something fundamental here. Can anyone explain
to me why this is happening?
Thanks very much!
Cheers!
-warren
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql command not found
At 23:33 -0500 12/17/02, Gary Hostetler wrote: I just installed mysql but I get the command not found when I type mysql. I am in the bin directory. Anyone know what is going on here? Does it work in that directory if you type: ./mysql If it does, then that's your clue. :-) Thanks Gary - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Can MySQL handle 120 million records?
rOn Tue, Dec 17, 2002 at 08:43:38PM -0800, Qunfeng Dong wrote: I am not sure. Does anyone know any real examples of mysql handling huge database and still perform well? Our largest table is about 340 million rows now. It grows by roughly 100,000 records per business day. Performance is quite good so far. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 2 days, processed 110,127,603 queries (425/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL security flaws uncovered
A good question posted to another list.. forwarded message follows Several vulnerabilities have been found in the MySQL database system, a light database package commonly used in Linux environments but which runs also on Microsoft platforms, HP-Unix, Mac OS and more. http://zdnet.com.com/2100-1104-977958.html So why no mention on the MySQL.COM site? That rather bugs me. In contrast, sites for products like Apache or Bind are very clear about current/past security issues. Is MySQL.COM the wrong place? -- Michael Bacarella | Netgraft Corp | 545 Eighth Ave #401 Systems Analysis | New York, NY 10018 Technical Support | 212 946-1038 | 917 670-6982 Managed Services | http://netgraft.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql command not found
When I type ./mysql I get the following: dyld: ./mysql Undefined symbols: ./mysql undefined reference to _BC expected to be defined in /usr/lib/libSystem.B.dylib ./mysql undefined reference to _PC expected to be defined in /usr/lib/libSystem.B.dylib ./mysql undefined reference to _UP expected to be defined in /usr/lib/libSystem.B.dylib Trace/BPT trap At least I'm getting some action with it, but I'm not sure what that means. Thanks Gary On 12/18/02 12:03 AM, Paul DuBois [EMAIL PROTECTED] wrote: At 23:33 -0500 12/17/02, Gary Hostetler wrote: I just installed mysql but I get the command not found when I type mysql. I am in the bin directory. Anyone know what is going on here? Does it work in that directory if you type: ./mysql If it does, then that's your clue. :-) Thanks Gary - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
