ON DELETE CASCADE ON UPDATE CASCADE
HI I'm not sure whether it's a bug or my configuration problem?? I have upgraded mysql from 3.23.53 to 4.0.12 so that the on update cascade will work! then the I got mysql crash when I did the update. Any idea?? mysql CREATE TABLE software ( - softwareID CHAR(20) NOT NULL, - softwareName CHAR(100), - softwareVers CHAR(20), - installedDate DATE, - softwareSource BLOB, - PRIMARY KEY (softwareID) - ) TYPE=INNODB; Query OK, 0 rows affected (0.04 sec) mysql CREATE TABLE software_machineOSs ( - softwareID CHAR(20) NOT NULL, - osName CHAR(20) NOT NULL, - osRevision CHAR(20), - INDEX (softwareID), - FOREIGN KEY (softwareID) REFERENCES software (softwareID) ON DELETE CASCADE ON UPDATE CASCADE, - PRIMARY KEY (softwareID, osName) - ) TYPE=INNODB; Query OK, 0 rows affected (0.00 sec) mysql insert into software (softwareID,softwareName,softwareVers) values (test-1.1, test, 1.1); Query OK, 1 row affected (0.00 sec) mysql insert into software_machineOSs (softwareID,osName,osRevision) values (test-1.1, win, 2002); Query OK, 1 row affected (0.00 sec) mysql select * from software; ++--+--+---++ | softwareID | softwareName | softwareVers | installedDate | softwareSource | ++--+--+---++ | test-1.1 | test | 1.1 | NULL | NULL | ++--+--+---++ 1 row in set (0.00 sec) mysql select * from software_machineOSs; ++++ | softwareID | osName | osRevision | ++++ | test-1.1 | win| 2002 | ++++ 1 row in set (0.00 sec) mysql update software set softwareID=test-1.6 where softwareID=test-1.1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql select * from software; ++--+--+---++ | softwareID | softwareName | softwareVers | installedDate | softwareSource | ++--+--+---++ | test-1.6 | test | 1.1 | NULL | NULL | ++--+--+---++ 1 row in set (0.00 sec) mysql select * from software_machineOSs; ERROR 2013: Lost connection to MySQL server during query mysql ** mungah.cs.mu.OZ.AU.err mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=16777216 read_buffer_size=131072 sort_buffer_size=524280 max_used_connections=1 max_connections=100 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 80383 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. 030611 13:59:39 mysqld restarted 030611 13:59:39 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 137838052 InnoDB: Doing recovery: scanned up to log sequence number 0 137838052 InnoDB: Last MySQL binlog file position 0 2391, file name ./mungah-bin.043 030611 13:59:39 InnoDB: Flushing modified pages from the buffer pool... 030611 13:59:39 InnoDB: Started /usr/pkg/libexec/mysqld: ready for connections. Version: '4.0.12-log' socket: '/var/mysql/mysql.sock' port: 3306 *** mysqlbinlog mungah-bin.043 update software set softwareID=test-1.6 where softwareID=test-1.1; # at 2391 #030611 13:59:39 server id 1 log_pos 2391 Stop ### When I don't have the column softwareSource ( BLOB) it works fine!!! --- mysql CREATE TABLE software ( - softwareID CHAR(20) NOT NULL, - softwareName CHAR(100), - softwareVers CHAR(20), - installedDate DATE, - PRIMARY KEY (softwareID) - ) TYPE=INNODB; Query OK, 0 rows affected (0.01 sec) mysql CREATE TABLE software_machineOSs ( - softwareID CHAR(20) NOT NULL, - osName CHAR(20) NOT NULL, - osRevision CHAR(20), INDEX (softwareID), FOREIGN KEY (softwareID) REFERENCES software (softwareID) ON
Re: Unicode(ucs2) setup question
Hi Nils, I have no ucs2.xml in the /usr/local/share/mysql/charsets/ . but I was looking for about ucs2 in the /usr/local/share/mysql/charsets/Index.xml . charset name=ucs2 familyUnicode/family descriptionUCS-2 Unicode/description collation name=ucs2id=35 flag=primary/ /charset What should I do? Japanese: Nils Valentin wrote: Hi Yuji Error 2 means the file doesnt exist. (here: '/usr/local/share/mysql/charsets/ucs2.xml'). Please make sure the file is in the foldr specified and not misspelled. Best regards Nils Valentin Tokyo/Japan Japanese: 2003 6 11 14:44Yuji Sato : I'd like to use ucs2 character set for MySQL. My enviroment is shown below: OS FreeBSD 4.8 DB MySQL4.1.0-alpha A) configure option: --with-charset=ucs2 \ --with-extra-chasets=all \ define to /etc/my.cnf by ucs2. [mysqld] default-character-set=ucs2 Then, It was does not work. # ./mysql-server.sh start # tail /var/db/mysql/MyDB.err 030610 21:24:08 mysqld started /usr/local/libexec/mysqld: File '/usr/local/share/mysql/charsets/ucs2.xml' not found (Errcode: 2) /usr/local/libexec/mysqld: Character set 'ucs2' is not a compiled character set and is not specified in the '/usr/local/share/mysql/charsets/Index.xml' file 030610 21:24:08 Aborting 030610 21:24:08 /usr/local/libexec/mysqld: Shutdown Complete 030610 21:24:08 mysqld ended B) configure option: --with-extra-chasets=all \ Of course,It was working. Then, I created a table u1,and modify to character set by manual operation. mysqlALTER TABLE u1 MODIFY b CHAR(20) CHARACTER SET ucs2; ERROR 1115: Unknown character set: 'ucs2' Regards, Yujis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql + dispersion
Hello I need some information about it - access to remote data base - implementation X/Open DTB some links ... ?? thanks a lot mec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unicode(ucs2) setup question
Hi Yuji, I have never heard of a character set thats named ucs2 This are the character sets which came with 4.1 alpha: baby-bumble-bee:/usr/share/mysql/charsets # ls . READMEcp1250.xml cp1257.xml cp866.xml hebrew.xml koi8r.xml latin2.xml macce.xml .. armscii8.xml cp1251.xml cp850.xml dec8.xml hp8.xml koi8u.xml latin5.xml macroman.xml Index.xml ascii.xml cp1256.xml cp852.xml greek.xml keybcs2.xml latin1.xml latin7.xml swe7.xml I guess that this is a misconfguration. I am not sure were you want to go. Did you compile it yourself ? Do you want to create your own character set ? My guess is that you perhaps wanted to use the new UTF-8 charcater set. Is that right ? Best regards Nils Valentin Tokyo/Japan 2003 6 11 15:04Yuji Sato : Hi Nils, I have no ucs2.xml in the /usr/local/share/mysql/charsets/ . but I was looking for about ucs2 in the /usr/local/share/mysql/charsets/Index.xml . charset name=ucs2 familyUnicode/family descriptionUCS-2 Unicode/description collation name=ucs2id=35 flag=primary/ /charset What should I do? Japanese: Nils Valentin wrote: Hi Yuji Error 2 means the file doesnt exist. (here: '/usr/local/share/mysql/charsets/ucs2.xml'). Please make sure the file is in the foldr specified and not misspelled. Best regards Nils Valentin Tokyo/Japan Japanese: 2003 6 11 14:44Yuji Sato : I'd like to use ucs2 character set for MySQL. My enviroment is shown below: OS FreeBSD 4.8 DB MySQL4.1.0-alpha A) configure option: --with-charset=ucs2 \ --with-extra-chasets=all \ define to /etc/my.cnf by ucs2. [mysqld] default-character-set=ucs2 Then, It was does not work. # ./mysql-server.sh start # tail /var/db/mysql/MyDB.err 030610 21:24:08 mysqld started /usr/local/libexec/mysqld: File '/usr/local/share/mysql/charsets/ucs2.xml' not found (Errcode: 2) /usr/local/libexec/mysqld: Character set 'ucs2' is not a compiled character set and is not specified in the '/usr/local/share/mysql/charsets/Index.xml' file 030610 21:24:08 Aborting 030610 21:24:08 /usr/local/libexec/mysqld: Shutdown Complete 030610 21:24:08 mysqld ended B) configure option: --with-extra-chasets=all \ Of course,It was working. Then, I created a table u1,and modify to character set by manual operation. mysqlALTER TABLE u1 MODIFY b CHAR(20) CHARACTER SET ucs2; ERROR 1115: Unknown character set: 'ucs2' Regards, Yujis -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unicode(ucs2) setup question
I'd like to use ucs2 character set for MySQL. You do understand that Unicode support is not yet complete in MySQL, including the current alpha? Also, I would expect UTF-8 to be in better condition that UTF-16. -- Joel Rees [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unicode(ucs2) setup question
Hi Nils, I have to test to unicode(utf8 ucs2). but It was frontend client enviroment matter(native supported by Unicode-16). I was compiled by my self. Then I tried uft8 and it was successful. but ucs2 was doesn't work by the same step. Thank you. Yujis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AES 256-Bit Encryption
Does anyone know where to find the 256-Bit patch for AES encryption? Thanks...=) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unicode(ucs2) setup question
In wich version of Windows do you have compiled mysql? I have Win XP and I need a mysql version that works with UTF-I. I am waiting for the development of MySQL 4.1 beta, but it is too late for me. It is posible if you publish your compiled version? (I suppose that is for windows). Or if you send to the list? I am making a trilingual web site (spanish, japanese and english) and I really need UTF-8. Thanks. - Original Message - From: Yuji Sato [EMAIL PROTECTED] To: Nils Valentin [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 8:40 AM Subject: Re: Unicode(ucs2) setup question Hi Nils, I have to test to unicode(utf8 ucs2). but It was frontend client enviroment matter(native supported by Unicode-16). I was compiled by my self. Then I tried uft8 and it was successful. but ucs2 was doesn't work by the same step. Thank you. Yujis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unicode(ucs2) setup question
Hi Juan, I understood Yuji is using OS FreeBSD 4.8 DB MySQL4.1.0-alpha Best regards Nils Valentin Tokyo/Japan 2003 6 11 16:54Juan Sebastian Alarcon : In wich version of Windows do you have compiled mysql? I have Win XP and I need a mysql version that works with UTF-I. I am waiting for the development of MySQL 4.1 beta, but it is too late for me. It is posible if you publish your compiled version? (I suppose that is for windows). Or if you send to the list? I am making a trilingual web site (spanish, japanese and english) and I really need UTF-8. Thanks. - Original Message - From: Yuji Sato [EMAIL PROTECTED] To: Nils Valentin [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 8:40 AM Subject: Re: Unicode(ucs2) setup question Hi Nils, I have to test to unicode(utf8 ucs2). but It was frontend client enviroment matter(native supported by Unicode-16). I was compiled by my self. Then I tried uft8 and it was successful. but ucs2 was doesn't work by the same step. Thank you. Yujis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
efficiency join
Hi, I was wondering is it efficient to use join with 7 tables, and also is it possible if we don't yet have any records in the table to check the efficiency of the table? Thanx Anthony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlhotcopy generates a segmentation fault
On Wed, 2003-06-11 at 01:55, Danny Cron wrote:
I have three boxes. Mysqlhotcopy works on box1, but it generates a
segmentation fault on box2 and box3. I want it to work on all boxes.
I am prepared to upgrade them all to the level of box3 (but
mysqlhotcopy doesn't work with that configuration). Does anyone know
of a solution?
Hi,
I have had the same problem running mysqlhotcopy on the MySQL
installed with Woody. I think there is some problem with the
client library while it tries to parse the my.cnf file causing
a segmentation fault. I'm not sure if the problem is inside DBI
or the MySQL libs.
A quick solution is to remove the
mysql_read_default_group=mysqlhotcopy clause from the connection
string:
my $dbh =
DBI-connect(dbi:mysql:$dsn;mysql_read_default_group=mysqlhotcopy,
$opt{user}, $opt{password},
{
RaiseError = 1,
PrintError = 0,
AutoCommit = 1,
});
becomes:
my $dbh = DBI-connect(dbi:mysql:$dsn,
$opt{user}, $opt{password},
{
RaiseError = 1,
PrintError = 0,
AutoCommit = 1,
});
This loses the ability to use dedicated mysqlhotcopy sections
in your config file, but at least gets around the segfault.
regards,
Martin
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: tables size in mysql database
I got these : | Name | Type | Row_format | Rows| Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_ti me | Update_time | Check_time | Create_options | Comment I guess Data_length shows the size in bytes of the table. if it isn't then what is the field for that? Jeremy Zawodny wrote: On Wed, Jun 11, 2003 at 10:08:47AM +0500, Sohail Hasan wrote: Hi there, How can I see per table size of all tables in a particular database. SHOW TABLE STATUS -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
not allowed to connect to this mysql server
Hi I am using a .Net project to connect to a mysql server. I have a winXP (IIS installed) machine as a network client to our mysql server hold on a linux machine. I have mydobc installed. I made a connection in .Net. But when I try to connect to mysql I get an error message saying: 'My ip' is not allowed to connect to this mysql server What can I do? Sinan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error running mysql test
Dear all I had installed mysql. Things comes right till i tried to execute perl run-all-tests i got the following error /usr/local/bin/perl: relocation error: /usr/local/lib/perl5/site_perl/5.8.0/i686-linux/auto/DBD/mysql/mysql.so: undefined symbol: mysql_init Any ideas? hamid
Re: not allowed to connect to this mysql server
[EMAIL PROTECTED] writes: Hi I am using a .Net project to connect to a mysql server. I have a winXP (IIS installed) machine as a network client to our mysql server hold on a linux machine. I have mydobc installed. I made a connection in .Net. But when I try to connect to mysql I get an error message saying: 'My ip' is not allowed to connect to this mysql server What can I do? Check out this list: http://www.mysql.com/doc/en/Access_denied.html Most likely the answer is: If you get the following error when you try to connect from a different host than the one on which the MySQL server is running, then there is no row in the user table that matches that host: Host ... is not allowed to connect to this MySQL server You can fix this by using the command-line tool mysql (on the server host!) to add a row to the user, db, or host table for the user/hostname combination from which you are trying to connect and then execute mysqladmin flush-privileges. If you are not running MySQL Version 3.22 and you don't know the IP number or hostname of the machine from which you are connecting, you should put an entry with '%' as the Host column value in the user table and restart mysqld with the --log option on the server machine. After trying to connect from the client machine, the information in the MySQL log will indicate how you really did connect. (Then replace the '%' in the user table entry with the actual hostname that shows up in the log. Otherwise, you'll have a system that is insecure.) Another reason for this error on Linux is that you are using a binary MySQL version that is compiled with a different glibc version than the one you are using. In this case you should either upgrade your OS/glibc or download the source MySQL version and compile this yourself. A source RPM is normally trivial to compile and install, so this isn't a big problem. -- Per Andreas Buer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: tables size in mysql database
http://www.mysql.com/doc/en/SHOW_TABLE_STATUS.html Searching is usually faster than waiting for the list to respond, too. Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Sohail Hasan [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, 11 June, 2003 16:33 Subject: Re: tables size in mysql database I got these : | Name | Type | Row_format | Rows| Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_ti me | Update_time | Check_time | Create_options | Comment I guess Data_length shows the size in bytes of the table. if it isn't then what is the field for that? Jeremy Zawodny wrote: On Wed, Jun 11, 2003 at 10:08:47AM +0500, Sohail Hasan wrote: Hi there, How can I see per table size of all tables in a particular database. SHOW TABLE STATUS -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql windows passwd probleme
I've been having the same problem with one of my systems, also on Windows XP, but using 4.0.12. Perhaps it's time for a bug report... Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Jonas Geiregat [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, 10 June, 2003 20:58 Subject: mysql windows passwd probleme I install mysql4.0.13 on windowsXP by using the installation wizard. Next I start mysql using NET START mysql, then I try to change my root password 'mysqladmin -u root password foobarke. Try to login with the new root passwd I get error access denied. But I can login as root using nothing as passwd. Then I try using the grant option : GRANT ALL ON *.* TO root IDENTIFIED BY foobarke; logout relogin can't login with foobarke as passwd but I can with nothing as passwd for the root user ? By nothing I mean nothing like . Even if I create a new user : GRANT ALL ON *.* TO user IDENTIFIED BY foobarke; I can't login with the user using foobarke as passwd. But I can with nothing as passwd. How can this be ?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL server 4.1 getColumns ()
Hello Mark,
Thanks for your help...
Since I have received your mail, I have made a few more tests. I took
the part of the web application and made a short one.
The result is that I can repeat the problem with the databases that are
generated by the web application, but if I use the database test
generated by the installation, I can't reproduce the problem.
If I use 'describe CLIENT;' in mysql client, I can see the structure of
the table and no exception message is displayed.
The web application generates the database and the tables using String
queries that are executed in statements.
Is there a way to see what goes wrong in a database ?
I join you the part of the source code I use to repeat the problem and
the two databases.
NGADMIN_DB is the database where I have the following exception on the
table CLIENT.
Result :
Exception caught.
java.lang.StringIndexOutOfBoundsException: String index out of range: -1
java.lang.StringIndexOutOfBoundsException: String index out of range: -1
at java.lang.String.substring(String.java:1474)
at
com.mysql.jdbc.DatabaseMetaData.getColumns(DatabaseMetaData.java:877)
at mysqltest.init(mysqltest.java:33)
at mysqltest.main(mysqltest.java:61)
test is the database where there is no problem (table : pet).
Result :
name name
name owner
name species
name sex
name birth
name death
NGADMIN_DB.tar.gz
Description: GNU Zip compressed data
test.tar.gz
Description: GNU Zip compressed data
the source code :
/*
* Java sample program - mysqltest.java
*/
import java.io.*;
import java.sql.*;
public class mysqltest
{
Connection conn; // holds database connection
Statement stmt; // holds SQL statement
// the table where the problem exists
String _host = goupix.bookmark.fr ;
String _database = NGADMIN_DB ;
String _user = usertest ;
String _password = ;
String _table = CLIENT ;
// the table where there is no problem
/*
String _host = goupix.bookmark.fr ;
String _database = test ;
String _user = usertest ;
String _password = ;
String _table = pet ;
*/
public mysqltest() throws ClassNotFoundException,
FileNotFoundException, IOException,
SQLException
{
// load database interface
Class.forName(com.mysql.jdbc.Driver);
// connect to the database
conn = DriverManager.getConnection(jdbc:mysql://+ _host + / +
_database, _user, _password);
DatabaseMetaData dmd = conn.getMetaData() ;
ResultSet resultSet = dmd.getColumns( null, null, _table, null ) ;
while ( resultSet.next() )
{
String name = resultSet.getString( 4 ) ;
System.out.println ( name + name ) ;
}
resultSet.close() ;
conn.close();
}
public static void main(String args[])
{
try {
mysqltest test = new mysqltest();
} catch(Exception exc)
{
System.err.println(Exception caught.\n + exc);
exc.printStackTrace();
}
}
}
Thierry Boucheny
Thierry Boucheny wrote:
Hello,
I am working on a web application using tomcat 4.1 j2sdk 1.4.1 on
Red
Hat 7.2. This app get the following error in a request to a
database on
MySql server 4.1.
java.lang.StringIndexOutOfBoundsException: String index out of
range: -1
at java.lang.String.substring(String.java:1474)
at
com.mysql.jdbc.DatabaseMetaData.getColumns(DatabaseMetaData.java:877)
I use Jconnector 3.0.8 as the JDBC driver.
I have seen that others had this problem but I could not see if it
was
solved or not.
Thanks for any help.
Thierry Boucheny
Hi!
Do you have a repeatable test case I could use? The JDBC driver runs
through the JDBC compliance test as well as some internal regression
and
unit tests which actually call this method in DatabaseMetadata, and it
doesn't fail, so it is probably something specific to your particular
usage.
Thanks!
-Mark
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: MySQL server 4.1 getColumns ()
Hello again, One more thing... There is no problem with MySql server 4.0.xx... Regards. Thierry Boucheny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql and clustering
They're completely different clustering solution. What, exactly, are you trying to acheive? LVS is primarily for load balancing and possibly fail-over. OpenMosix is a different beast entirely. Jeremy Thanks for the reply Jeremy. What i need to build are a mysql master server with two nodes both active at the same time. Don't know how this is called in english but we call it an HA active-to-active; while standard HA clustering is considered active-to-passive because only one node at time is working while the other one it will come up only if the first one is falling. I'm not a DBA and i know very little on setting up DB servers, but for what i have understand from the online documentation, if i use a slave server with db replication, when the master fails, the slave can only manage queries and not commits; I've also read that db replication fo the master servers will be supported starting at v.5.0 of mysql. Now, how i can achieve the active-to-active task? I've tought to put up two nodes with a shared disk-array where the db stands, and to share the processes between the two nodes using openmosix. What do you (and the others) think about that? Any advice is really welcome! :) Thanks in advance for any reply! Roberto -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: not allowed to connect to this mysql server
Hi, First you must add grants to the user which you want to connect to the MySQL server.In documentation (4.3.5 Adding New Users to MySQL) you will find how you can achieved this. Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 2:41 PM Subject: not allowed to connect to this mysql server Hi I am using a .Net project to connect to a mysql server. I have a winXP (IIS installed) machine as a network client to our mysql server hold on a linux machine. I have mydobc installed. I made a connection in .Net. But when I try to connect to mysql I get an error message saying: 'My ip' is not allowed to connect to this mysql server What can I do? Sinan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Hot backup in mysql
Hi all I'm new to this part of mysql. I've never done somme kind of backup/dump with mysql I read that for myIsam I can Just copy the frm, idx form the database directory But I've to manage hot backup with a database mixed with InnoDB tables and MyIsam. I searched for a tutorial / a software or any other kinda of information But there is something I don't understand: Mysql HOt Backup is a non-free solution Does it works onyl with InnoDB or also with MyIsam Can Someone suggest me some way to follow ? TIA Alx -- alx [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT question
I have a question on how to substitute a subselect in mysql. For example lets say i have 3 tables Cars, Options and CarOptions Cars consists of: uid, make Options consists of: uid, option CarOption consists of: uid, Caruid, Optionsuid I want to select all Cars that have ALL of requested options: if there were only few options (say 2) it would be easy: (lets ignore the options table and assume we know the needed uids from Options) SELECT Cars.* FROM Cars JOIN CarOptions as CO1 ON Cars.uid = CO1.Caruid JOIN CarOptions as CO2 ON Cars.uid = CO2.Caruid WHERE CO1.Optionuid = 1 AND CO2.Optionuid = 2 Now the problem is what if there are dozens of Options (so each car can have none or 20 options each) You see the problem? I can easily keep on building JOIN statements for each option they requested but that could end up with a JOIN per Option that is requested Having no limit on Options the SELECT statement can be HUGE and adding additional tables into equation gets crazy (like tables carengines, carweel, cardealer)... So the queston is: Is there a better way of doing this? I am sure there is a limit on how many joins can be in the SELECT... Also, I tested it with the following data: 3 entries in the Cars table, 20 entries in Option table and 10 entries in the CarOption table Wrote the SELECT asking for Every possible option (all 20) and the time it took to execute it was devistating (about 2 seconds) (longer than if i manualy did select from CarOption table for every needed Option for each car). Please help, thank you
mySQL GUIs
hi! i am looking for a good mySQL gui for windows i have used mySQL front, which was an excellent free program, but i did not handle relations and diagrams. also the program is no longer being developed i have now found the prog mySQL tools (http://www.mysqltools.com/) and mySQL explorer that works almost as enterprise manager for MS SQL server. it even creates database diagrams with relations as in enterprise manager! only backdraw is that this program is not free, but it is the best i have ever seen so far is there anyone out there that knows of a program that is freeware/shareware and is good (like mySQL tools)? /rewdboy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Doing a count of a count in mysql
Hi everybody, I do a query like select count(*) from SPECTRUM where SPECTRUM.REVIEW_FLAG =true group by MOLECULE_ID which gives back a lot of figures, mainly 1 and 2, perhaps 3. What I actually want is the count how many 1s and 2s (and 3s ...) are in this result set. What would be the best way to do ? I found 3 possible solutions, all not working or not good :-) -Subqueries, probably best, but not possible in MySQL currently. -Doing a loop over the first result set in my Java code. Possible, but slow. -I tried a nested count query like: select count(count(*)) from SPECTRUM where SPECTRUM.REVIEW_FLAG =true group by count(*),MOLECULE_ID; but this seems not possible. Anybody any idea ? Thanks a lot Stefan -- Stefan Kuhn M. A. Cologne University BioInformatics Center (http://www.cubic.uni-koeln.de) Zlpicher Str. 47, 50674 Cologne Tel: +49(0)221-470-7428 Fax: +49 (0) 221-470-5092 My public PGP key is available at http://pgp.mit.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mySQL GUIs
Why not try MySQL Control Center? It's MySQL AB's own project. Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Rodolphe Toots [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, 11 June, 2003 07:16 Subject: mySQL GUIs hi! i am looking for a good mySQL gui for windows i have used mySQL front, which was an excellent free program, but i did not handle relations and diagrams. also the program is no longer being developed i have now found the prog mySQL tools (http://www.mysqltools.com/) and mySQL explorer that works almost as enterprise manager for MS SQL server. it even creates database diagrams with relations as in enterprise manager! only backdraw is that this program is not free, but it is the best i have ever seen so far is there anyone out there that knows of a program that is freeware/shareware and is good (like mySQL tools)? /rewdboy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
same query different result on slightly different db
Dear all, i'm sorry if this kind of question was already posted, i'm a newbie and i hope you will find anyway the time to answer me. I've installed mysql version 4.0.12-nt on my Windows XP Professional notebook and mysql version 4.0.12-standard on a Solaris 8 machine. Some antefacts: I need a very very small database to keep track of a set of discussion topics, a set of users and the subscriptions of the users to the discussion topics. This is my database (a poor designed one, i know): -- MySQL dump 9.07 -- -- Host: localhostDatabase: buddyfinder - -- Server version4.0.12-nt -- -- Current Database: buddyfinder -- CREATE DATABASE /*!32312 IF NOT EXISTS*/ buddyfinder; USE buddyfinder; -- -- Table structure for table 'subscriptions' -- CREATE TABLE subscriptions ( SipUri varchar(80) NOT NULL default '', TopicName varchar(40) NOT NULL default '', Alias varchar(40) default NULL, PRIMARY KEY (SipUri,TopicName), KEY SipUri_ind (SipUri), KEY TopicName_ind (TopicName), FOREIGN KEY (`SipUri`) REFERENCES `users` (`SipUri`) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (`TopicName`) REFERENCES `topics` (`TopicName`) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE=InnoDB; -- -- Table structure for table 'topics' -- CREATE TABLE topics ( TopicName varchar(40) NOT NULL default '0', TopicType smallint(6) NOT NULL default '0', PRIMARY KEY (TopicName) ) TYPE=InnoDB; -- -- Table structure for table 'users' -- CREATE TABLE users ( SipUri varchar(80) NOT NULL default '', PRIMARY KEY (SipUri) ) TYPE=InnoDB; what i'd need is to present the user a list of the topics that he's not yet subscribed. I read the MySQL manual and I found that NOT IN and the nested SELECTs are not supported in this version, so I ended up to use temporary tables. These are the queries that i use: CREATE TEMPORARY TABLE user03 (TopicName varchar(40)); INSERT INTO user03 SELECT TopicName FROM subscriptions WHERE SipUri='[EMAIL PROTECTED]'; SELECT topics.TopicName FROM topics,user03 WHERE topics.TopicName != user03.TopicName; They are working fine on XP, the result is the list of topics at which user03 isn't subscribed yet. But on Solaris, with the same database, the result is a list of all the topics, repeated a certain number of times. I'm stucked on this, is it possible that the problem is the 4.0.12-standard version? Should I use the 4.0.12-max version? Or am i missing something? Thank you in advance for any help you'd like to give me, all the best Giovanna -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Doing a count of a count in mysql
On Wednesday, June 11, 2003, at 07:23 AM, Stefan Kuhn wrote: do a query like select count(*) from SPECTRUM where SPECTRUM.REVIEW_FLAG =true group by MOLECULE_ID which gives back a lot of figures, mainly 1 and 2, perhaps 3. What I actually want is the count how many 1s and 2s (and 3s ...) are in this result set. What would be the best way to do ? I found 3 possible solutions, all not working or not good :-) -Subqueries, probably best, but not possible in MySQL currently. -Doing a loop over the first result set in my Java code. Possible, but slow. -I tried a nested count query like: select count(count(*)) from SPECTRUM where SPECTRUM.REVIEW_FLAG =true group by count(*),MOLECULE_ID; but this seems not possible. If the number of possible counts were small enough, you could group by the count column, and use HAVING to limit the result set to a particular count. Repeat that for each count, and join the statements with UNION. For more than a few possible counts, though, I'd probably use a temporary table, and do a second select from that. ___/ / __/ / / Ed Leafe http://leafe.com/ http://opentech.leafe.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Hot backup in mysql
InnoDB Hot Backup is non-free and works only with InnoDB. mysqlhotcopy is free and works with MyISAM tables but not InnoDB. mysqldump is free and works with both. The easiest thing to do is use mysqldump...you'll get text files that contain create and insert statements allowing you to restore you tables in MySQL. It's easy b/c it works with both major table types. The issue is that it's much slower then mysqlhotcopy (and presumably InnoDB Hot Backup). --Ware alx wrote: Hi all I'm new to this part of mysql. I've never done somme kind of backup/dump with mysql I read that for myIsam I can Just copy the frm, idx form the database directory But I've to manage hot backup with a database mixed with InnoDB tables and MyIsam. I searched for a tutorial / a software or any other kinda of information But there is something I don't understand: Mysql HOt Backup is a non-free solution Does it works onyl with InnoDB or also with MyIsam Can Someone suggest me some way to follow ? TIA Alx -- alx [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Can't insert data from Apache/PHP
[snip] Sorry, but I am a bit behind on MySQL digest, but isn't turning Register Globals 'on' for php a security risk? [/snip] We recently had this discussion on the PHP-General list and the upshot is that having register_globals 'on' is only a security risk if the code is sloppy. PHP allows this as the variables are not strongly typed and most developer fail to do any checking of variables to make sure that they contain what they want them to contain. Turning register_globals 'off' does not take care of that. It just adds another layer of abstraction to certain vaariables (such as GET, POST, etc) which is still vulnerable if the developer does not do a good job of taking care with his or her variables. HTH! Jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: not allowed to connect to this mysql server
[EMAIL PROTECTED] wrote: I am using a .Net project to connect to a mysql server. I have a winXP (IIS installed) machine as a network client to our mysql server hold on a linux machine. I have mydobc installed. I made a connection in .Net. But when I try to connect to mysql I get an error message saying: 'My ip' is not allowed to connect to this mysql server What can I do? Give user privileges to connect from needed host: http://www.mysql.com/doc/en/GRANT.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: forming foreign keys
Nils Valentin [EMAIL PROTECTED] wrote: I believe that the User column is indexed (please see below): show index from user; +---++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+---+-+--++--++-+ | user | 0 | PRIMARY |1 | Host| A | 15 | NULL | NULL | | BTREE | | | user | 0 | PRIMARY |2 | User| A | 15 | NULL | NULL | | BTREE | | +---++--+--+-+---+-+--++--++-+ Any comments appreciated. If you have multi-column index, 'User' must be the first part of the index. In your case User is the second part of index. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to make Cascade Update?
William IT [EMAIL PROTECTED] wrote: I just migeate from MS SQL to MySQL but have problem on Cascade Update. Suppose I have BOOK and LIST_TYPE tables like this: BOOK, field: Book_Name, Book_Type LIST_TYPE, field: Book_Type If I already define Cascade Update between BOOK and LIST_TYPE, then when I update the content of Book_Type (from table LIST_TYPE): the Book_Type of BOOK automatically change too. How to perform Cascade Update in MySQL? Create the FOREIGN KEY CONSTRAINTS and specify ON UPDATE CASCADE: http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: data path
unplug [EMAIL PROTECTED] wrote: By default, data dir is located at /var/lib/mysql. I have run mysql for serveral month with data in that path. Now, I want to move all database data to another path, say /var2/lib/mysql. What is the suitable purpose to do it? Is it only shutdown mysql and move all data file from /var/lib/mysql to /var2/lib/mysql and startup again? Any experience to share? You must specify path to the new data dir in my.cnf or with --datadir option of mysqld and then start the server. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: forming foreign keys
Hi Victoria, I thought so, but also if I try somthing similar like: ALTER TABLE db ADD FOREIGN KEY (Host) REFERENCES user (Host); I get the same error. BTW: Can I change the order of the keys ? Best regards Nils Valentin 2003 6 11 17:17Victoria Reznichenko : Nils Valentin [EMAIL PROTECTED] wrote: I believe that the User column is indexed (please see below): show index from user; +---++--+--+-+--- +-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+--- +-+--++--++-+ | user | 0 | PRIMARY |1 | Host| A | | 15 | NULL | NULL | | BTREE | | | user | 0 | PRIMARY |2 | User| A | | 15 | NULL | NULL | | BTREE | | +---++--+--+-+--- +-+--++--++-+ Any comments appreciated. If you have multi-column index, 'User' must be the first part of the index. In your case User is the second part of index. -- 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 -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't insert data from Apache/PHP
On Tuesday 10 June 2003 22:49, CM Miller wrote: globals back to ON. Sorry, but I am a bit behind on MySQL digest, but isn't turning Register Globals 'on' for php a security risk? Yes. He needs to take a look at the documentation on the PHP website. I also sent instructions on how to make things work by reading the $_POST and $_GET and $_SERVER variables. I also have a couple of code snippets to remove other potential offending characters from input. Curtis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: forming foreign keys
Hi Victoria, Thank you for the reply. I understand now that it must be the first part of the primary index in both related tables. But what I still dont understand is the following: How do I create several foreign keys in a single table (f.e a link table) which relates f.e to 10 other tables ? Best regards Nils Valentin Tokyo/Japan 2003 6 11 21:20Nils Valentin : Hi Victoria, I thought so, but also if I try somthing similar like: ALTER TABLE db ADD FOREIGN KEY (Host) REFERENCES user (Host); I get the same error. BTW: Can I change the order of the keys ? Best regards Nils Valentin 2003 6 11 17:17Victoria Reznichenko : Nils Valentin [EMAIL PROTECTED] wrote: I believe that the User column is indexed (please see below): show index from user; +---++--+--+-+- -- +-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | | Collation Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+- -- +-+--++--++-+ | user | 0 | PRIMARY |1 | Host| A 15 | NULL | NULL | | BTREE | | | user | 0 | PRIMARY |2 | User| A 15 | NULL | NULL | | BTREE | | +---++--+--+-+- -- +-+--++--++-+ Any comments appreciated. If you have multi-column index, 'User' must be the first part of the index. In your case User is the second part of index. -- 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 -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Join with no matches on other table
Hello, I have two tables, other having information of cottages and other including information when each cottage is booked. The table structures are following: Cottages: - code - name - equipment Reservations: - cottagecode - begindate - enddate I'm looking for a query structure that I can use to find for example cottages that are free on 15. - 16. July. Any help? Thank you, Ville Mattila Ikaalinen, Finland -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Static Table
Hi, I read the document about Static table and Dynamic table. When we have VARCHAR, BLOB, and TEXT, default is Dynamic. But if we don't have BLOB, we can force MySQL to create static table. Now my question: There is a table with more than 15 records and about 130 fields and there are some fields with text type. This table is very handy and everyday is being changed and used in queries. Speed is more important than size for us. What type do you recommend for this table? Static or Dynamic? if static, what should I do with text fields? Should I leave them in this table or create another related table and move the text fields to it? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Copy database to another Server
William IT wrote: I do mysqlamin shutdown and copy all /var/lib/mysql file from another MySQL server. And then run: chmod -R 660 * Try 770 Mysql needs to be able to scan the directories to find tables. chown -R mysql:mysql * shutdown now -r But I can't logon to Mysql. What's wrong? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Printing table descriptions
Hi! I need to print the description of all tables in my database. The problem is, there are about 200 of them, and the only way I know to do this is send each of the screens to a file, an then print the file. Isn't there any easier way, like printing directly from mysql or at least saving all descriptions to the file at once?? Thanks in advance Fernando
Re: Join with no matches on other table
On Wed, 2003-06-11 at 13:48, Ville Mattila wrote: Hello, I have two tables, other having information of cottages and other including information when each cottage is booked. The table structures are following: Cottages: - code - name - equipment Reservations: - cottagecode - begindate - enddate I'm looking for a query structure that I can use to find for example cottages that are free on 15. - 16. July. Any help? Hi, Something like: select c.* from cottage c left join reservations r on r.cottagecode = c.code and begindate = '2003-07-16' and enddate = '2003-07-15' where r.cottagecode is null regards, Martin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Printing table descriptions
On Wed, 2003-06-11 at 14:29, Fernando Gerent wrote: Hi! I need to print the description of all tables in my database. The problem is, there are about 200 of them, and the only way I know to do this is send each of the screens to a file, an then print the file. Isn't there any easier way, like printing directly from mysql or at least saving all descriptions to the file at once?? Hi, Try: mysqldump -d -u user -p database-name regards, Martin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
deadlock problem?
dear all! I am running mysql 4.0.13 (Linux-X86) my innodb table accasionally comes to a deadlock, when i run some sql such a delete from tb_memfee where no='206681'. if it come to a deadlock, i cannot update any column of the row - where no is '206681' in the table tb_memfee. how can i solve the problem? please help. thank you. # the following is a result of show innodb status when i met a deadlock. mysql show innodb status; = 030611 9:43:50 INNODB MONITOR OUTPUT = Per second averages calculated from the last 25 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 124502, signal count 123284 Mutex spin waits 2765914, rounds 10463114, OS waits 34375 RW-shared spins 144868, OS waits 71456; RW-excl spins 6603, OS waits 4664 TRANSACTIONS Trx id counter 0 714450041 Purge done for trx's n:o 0 713888592 undo n:o 0 0 Total number of lock structs in row lock hash table 33 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 714447966, not started, process no 12337, OS thread id 794675 MySQL thread id 12217, query id 1209966 db1 10.10.7.1 dbdba ... cut cut cut ... ---TRANSACTION 0 714440674, not started, process no 17431, OS thread id 90124 MySQL thread id 10, query id 1195297 db2 10.10.7.2 dbdba ---TRANSACTION 0 714450019, ACTIVE 18 sec, process no 17756, OS thread id 229405 starting index read LOCK WAIT 2 lock struct(s), heap size 320 MySQL thread id 65, query id 1214253 db2 10.10.7.2 web updating delete from tb_memfee where no='206681' --- TRX HAS BEEN WAITING 18 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 170373 n bits 152 table pay/tb_memfee index PRIMARY trx id 0 71445001 9 lock_mode X waiting Record lock, heap no 38 RECORD: info bits 32 0: len 10; hex 32303030303036363831; asc 206681;; 1: -- ---TRANSACTION 0 713900169, ACTIVE 50231 sec, process no 17432, OS thread id 98317 19 lock struct(s), heap size 2496, undo log entries 13 MySQL thread id 9, query id 1213960 db2 10.10.7.2 web Trx read view will not see trx with id = 0 713900170, sees 0 713875499 ---TRANSACTION 0 713875499, ACTIVE 52484 sec, process no 17530, OS thread id 155668 22 lock struct(s), heap size 2496, undo log entries 13 MySQL thread id 27, query id 1214277 db2 10.10.7.2 web Trx read view will not see trx with id = 0 713888581, sees 0 713875612 FILE I/O I/O thread 0 state: waiting for i/o request I/O thread 1 state: waiting for i/o request I/O thread 2 state: waiting for i/o request I/O thread 3 state: waiting for i/o request Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 1078482 OS file reads, 229960 OS file writes, 113777 OS fsyncs 0.16 reads/s, 16384 avg bytes/read, 2.96 writes/s, 1.12 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf for space 0: size 1, free list len 4286, seg size 4288, 36392 inserts, 36392 merged recs, 26899 merges Hash table size 1593833, used cells 99888, node heap has 102 buffer(s) 0.44 hash searches/s, 3.92 non-hash searches/s --- LOG --- Log sequence number 5 3656343850 Log flushed up to 5 3656343850 Last checkpoint at 5 3656343850 0 pending log writes, 0 pending chkp writes 83440 log i/o's done, 0.36 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 458854006; in additional pool allocated 2259968 Buffer pool size 24576 Free buffers 2 Database pages 24472 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 2974610, created 1792, written 148676 0.16 reads/s, 0.00 creates/s, 2.60 writes/s Buffer pool hit rate 993 / 1000 -- ROW OPERATIONS -- 0 queries inside InnoDB, 0 queries in queue Main thread process no 17421, state: sleeping Number of rows inserted 69660, updated 13094, deleted 50050, read 223213315 0.12 inserts/s, 0.12 updates/s, 0.00 deletes/s, 6.64 reads/s END OF INNODB MONITOR OUTPUT 1 row in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie Question
Hello, I'm sure there's an answer somewhere but the 900+ page manual is a bit overwhelming. I have MySQL 4.1 running on one machine and I'd like to access using the client software I alerady have on another machine which was built against 3.23.33. Access is via command line client (mysql), PHP and C-API - all obviously built against the same client library. I gather from the manual (brief note at top of page 222) that 4.1 has a different way of handling passwords which is why a mysql connection failed with error 1249 about authentication protocols. Is there any way round this? I really don't want to have two separate sets of client stuff around and I'm not ready to switch a production system to 4.1 [Host environment is Solaris 2.7/2.8] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Printing table descriptions
Hi Gerald, How about like this ? mysql \P lp; PAGER set to lp; mysql SHOW INNODB STATUS ; lp is the printer queue which you have probably setup. Exchange lp with your printer spooler name. I am not sure about printing several tables. How about making a script or batch file (in case you want to print them regularly) ? ;-) Best regards Nils Valentin Tokyo/Japan 2003 6 11 22:29Fernando Gerent : Hi! I need to print the description of all tables in my database. The problem is, there are about 200 of them, and the only way I know to do this is send each of the screens to a file, an then print the file. Isn't there any easier way, like printing directly from mysql or at least saving all descriptions to the file at once?? Thanks in advance Fernando -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
underscore character?
I'm getting an error with create table, appears that field names cannot have the underscore character. Is this correct? It would be quite fatal for us, as we need some sort of delimeter character, so we can group variables by instrument. You have an error in your SQL syntax near 'A2DEE1_RWO FLOAT(1), -- --Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Printing table descriptions
Martin, my compliment. Excellent answer. I completely forgot about the -d option for mysqldump. Best regards Nils Valentin Tokyo/Japan 2003 6 11 22:32Martin Waite : On Wed, 2003-06-11 at 14:29, Fernando Gerent wrote: Hi! I need to print the description of all tables in my database. The problem is, there are about 200 of them, and the only way I know to do this is send each of the screens to a file, an then print the file. Isn't there any easier way, like printing directly from mysql or at least saving all descriptions to the file at once?? Hi, Try: mysqldump -d -u user -p database-name regards, Martin -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: underscore character?
The underscore is a valid character. Maybe it's the FLOAT(1) that's causing a problem. -Original Message- From: Chris Webster [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 10, 2003 9:09 PM To: [EMAIL PROTECTED] Subject: underscore character? I'm getting an error with create table, appears that field names cannot have the underscore character. Is this correct? It would be quite fatal for us, as we need some sort of delimeter character, so we can group variables by instrument. You have an error in your SQL syntax near 'A2DEE1_RWO FLOAT(1), -- --Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: deadlock problem?
Hi! It is not a deadlock. These two transactions have been dangling uncommitted for 14 hours: ---TRANSACTION 0 713900169, ACTIVE 50231 sec, process no 17432, OS thread id 98317 19 lock struct(s), heap size 2496, undo log entries 13 MySQL thread id 9, query id 1213960 db2 10.10.7.2 web Trx read view will not see trx with id = 0 713900170, sees 0 713875499 ---TRANSACTION 0 713875499, ACTIVE 52484 sec, process no 17530, OS thread id 155668 22 lock struct(s), heap size 2496, undo log entries 13 MySQL thread id 27, query id 1214277 db2 10.10.7.2 web Trx read view will not see trx with id = 0 713888581, sees 0 713875612 Remember to commit your transactions. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ - Original Message - From: [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Wednesday, June 11, 2003 4:42 PM Subject: deadlock problem? dear all! I am running mysql 4.0.13 (Linux-X86) my innodb table accasionally comes to a deadlock, when i run some sql such a delete from tb_memfee where no='206681'. if it come to a deadlock, i cannot update any column of the row - where no is '206681' in the table tb_memfee. how can i solve the problem? please help. thank you. # the following is a result of show innodb status when i met a deadlock. mysql show innodb status; = 030611 9:43:50 INNODB MONITOR OUTPUT = Per second averages calculated from the last 25 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 124502, signal count 123284 Mutex spin waits 2765914, rounds 10463114, OS waits 34375 RW-shared spins 144868, OS waits 71456; RW-excl spins 6603, OS waits 4664 TRANSACTIONS Trx id counter 0 714450041 Purge done for trx's n:o 0 713888592 undo n:o 0 0 Total number of lock structs in row lock hash table 33 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 714447966, not started, process no 12337, OS thread id 794675 MySQL thread id 12217, query id 1209966 db1 10.10.7.1 dbdba ... cut cut cut ... ---TRANSACTION 0 714440674, not started, process no 17431, OS thread id 90124 MySQL thread id 10, query id 1195297 db2 10.10.7.2 dbdba ---TRANSACTION 0 714450019, ACTIVE 18 sec, process no 17756, OS thread id 229405 starting index read LOCK WAIT 2 lock struct(s), heap size 320 MySQL thread id 65, query id 1214253 db2 10.10.7.2 web updating delete from tb_memfee where no='206681' --- TRX HAS BEEN WAITING 18 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 170373 n bits 152 table pay/tb_memfee index PRIMARY trx id 0 71445001 9 lock_mode X waiting Record lock, heap no 38 RECORD: info bits 32 0: len 10; hex 32303030303036363831; asc 206681;; 1: -- ---TRANSACTION 0 713900169, ACTIVE 50231 sec, process no 17432, OS thread id 98317 19 lock struct(s), heap size 2496, undo log entries 13 MySQL thread id 9, query id 1213960 db2 10.10.7.2 web Trx read view will not see trx with id = 0 713900170, sees 0 713875499 ---TRANSACTION 0 713875499, ACTIVE 52484 sec, process no 17530, OS thread id 155668 22 lock struct(s), heap size 2496, undo log entries 13 MySQL thread id 27, query id 1214277 db2 10.10.7.2 web Trx read view will not see trx with id = 0 713888581, sees 0 713875612 FILE I/O I/O thread 0 state: waiting for i/o request I/O thread 1 state: waiting for i/o request I/O thread 2 state: waiting for i/o request I/O thread 3 state: waiting for i/o request Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 1078482 OS file reads, 229960 OS file writes, 113777 OS fsyncs 0.16 reads/s, 16384 avg bytes/read, 2.96 writes/s, 1.12 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf for space 0: size 1, free list len 4286, seg size 4288, 36392 inserts, 36392 merged recs, 26899 merges Hash table size 1593833, used cells 99888, node heap has 102 buffer(s) 0.44 hash searches/s, 3.92 non-hash searches/s --- LOG --- Log sequence number 5 3656343850 Log flushed up to 5 3656343850 Last checkpoint at 5 3656343850 0 pending log writes, 0 pending chkp writes 83440 log i/o's done, 0.36 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 458854006; in additional pool allocated 2259968 Buffer pool size 24576 Free buffers 2 Database pages 24472 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 2974610, created 1792, written 148676 0.16 reads/s, 0.00 creates/s, 2.60 writes/s Buffer pool hit rate 993 / 1000
Re: ON DELETE CASCADE ON UPDATE CASCADE
Vinita, thank you for the bug report. The BLOB silently changes the CHAR(20) field to a VARCHAR(20) field in te parent table. But the field in the child stays CHAR(20). There is a bug in ON UPDATE CASCADE which forgets to pad the field in the child table with spaces. The child table becomes corrupt because a fixed length field is shorter than it should be! The fix will be in 4.0.14. Thank you, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL - Original Message - From: vinita Vigine Murugiah [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Wednesday, June 11, 2003 9:03 AM Subject: ON DELETE CASCADE ON UPDATE CASCADE HI I'm not sure whether it's a bug or my configuration problem?? I have upgraded mysql from 3.23.53 to 4.0.12 so that the on update cascade will work! then the I got mysql crash when I did the update. Any idea?? mysql CREATE TABLE software ( - softwareID CHAR(20) NOT NULL, - softwareName CHAR(100), - softwareVers CHAR(20), - installedDate DATE, - softwareSource BLOB, - PRIMARY KEY (softwareID) - ) TYPE=INNODB; Query OK, 0 rows affected (0.04 sec) mysql CREATE TABLE software_machineOSs ( - softwareID CHAR(20) NOT NULL, - osName CHAR(20) NOT NULL, - osRevision CHAR(20), - INDEX (softwareID), - FOREIGN KEY (softwareID) REFERENCES software (softwareID) ON DELETE CASCADE ON UPDATE CASCADE, - PRIMARY KEY (softwareID, osName) - ) TYPE=INNODB; Query OK, 0 rows affected (0.00 sec) mysql insert into software (softwareID,softwareName,softwareVers) values (test-1.1, test, 1.1); Query OK, 1 row affected (0.00 sec) mysql insert into software_machineOSs (softwareID,osName,osRevision) values (test-1.1, win, 2002); Query OK, 1 row affected (0.00 sec) mysql select * from software; ++--+--+---+ + | softwareID | softwareName | softwareVers | installedDate | softwareSource | ++--+--+---+ + | test-1.1 | test | 1.1 | NULL | NULL | ++--+--+---+ + 1 row in set (0.00 sec) mysql select * from software_machineOSs; ++++ | softwareID | osName | osRevision | ++++ | test-1.1 | win| 2002 | ++++ 1 row in set (0.00 sec) mysql update software set softwareID=test-1.6 where softwareID=test-1.1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql select * from software; ++--+--+---+ + | softwareID | softwareName | softwareVers | installedDate | softwareSource | ++--+--+---+ + | test-1.6 | test | 1.1 | NULL | NULL | ++--+--+---+ + 1 row in set (0.00 sec) mysql select * from software_machineOSs; ERROR 2013: Lost connection to MySQL server during query mysql ** mungah.cs.mu.OZ.AU.err mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=16777216 read_buffer_size=131072 sort_buffer_size=524280 max_used_connections=1 max_connections=100 threads_connected=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 80383 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. 030611 13:59:39 mysqld restarted 030611 13:59:39 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 137838052 InnoDB: Doing recovery: scanned up to log sequence number 0 137838052 InnoDB: Last MySQL binlog file position 0 2391, file name ./mungah-bin.043 030611 13:59:39 InnoDB: Flushing modified pages from the buffer pool... 030611 13:59:39 InnoDB: Started /usr/pkg/libexec/mysqld: ready for connections. Version: '4.0.12-log' socket: '/var/mysql/mysql.sock' port: 3306 *** mysqlbinlog mungah-bin.043 update software set softwareID=test-1.6 where softwareID=test-1.1; # at 2391 #030611 13:59:39
Re: need help
I have no idea. sorry. Sibananda Sahoo wrote: Thanks for your reply. Could you please tell me is it possible to achieve foreign key constraint in MySQL 3.23.56. Rgds, Sibananda --- Jeff Mathis [EMAIL PROTECTED] wrote: i'm not an expert by any means :) but, you do need indexes on both the primary key and the foreign key. you've got one on foo_id, but you also need on on foo_value. check the lists for more information. there's been plenty of discussion lately ... good luck Sibananda Sahoo wrote: Dear Sir I am using MySQL 3.23.56. I want to achieve foreign key constraints. But not able to achieve. Right now status of table is : MyISAM. So I tried to convert it to InnoDB in the following way but not able to convert. 1. ALTER TABLE mytableName Type=InnoDB; Then I tried to create a table create table foo ( foo_idint unsigned auto_increment, foo_value int, primary key(foo_id) ) type=innodb; For the above table it also shows the status as MyISAM. Could you pls suggest how can I change and to achieve foreign key constraints. Rgds, Sibananda __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Jeff Mathis, Ph.D.505-955-1434 The Prediction Company[EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6 http://www.predict.com Santa Fe, NM 87505 __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: need help
Do a show variables like '%inno%'; This should tell you if you have innodb enabled or not. If not you will need to configure your cnf file. -Original Message- From: Jeff Mathis [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 10:07 AM To: Sibananda Sahoo; [EMAIL PROTECTED] Subject: Re: need help I have no idea. sorry. Sibananda Sahoo wrote: Thanks for your reply. Could you please tell me is it possible to achieve foreign key constraint in MySQL 3.23.56. Rgds, Sibananda --- Jeff Mathis [EMAIL PROTECTED] wrote: i'm not an expert by any means :) but, you do need indexes on both the primary key and the foreign key. you've got one on foo_id, but you also need on on foo_value. check the lists for more information. there's been plenty of discussion lately ... good luck Sibananda Sahoo wrote: Dear Sir I am using MySQL 3.23.56. I want to achieve foreign key constraints. But not able to achieve. Right now status of table is : MyISAM. So I tried to convert it to InnoDB in the following way but not able to convert. 1. ALTER TABLE mytableName Type=InnoDB; Then I tried to create a table create table foo ( foo_idint unsigned auto_increment, foo_value int, primary key(foo_id) ) type=innodb; For the above table it also shows the status as MyISAM. Could you pls suggest how can I change and to achieve foreign key constraints. Rgds, Sibananda __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Jeff Mathis, Ph.D.505-955-1434 The Prediction Company[EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6 http://www.predict.com Santa Fe, NM 87505 __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Jeff Mathis, Ph.D. 505-955-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
my.cnf
I have been plowing through the mysql.com resources for something regarding the run-time configuration options as well as the my.cnf. In particular, I am trying to move the entire information store to a separate hard-drive. This line confuses me: --- Note that this is the directory that was specified at configuration time, not the one specified with --datadir when mysqld starts up! It is from: http://www.mysql.com/doc/en/Option_files.html DATADIR is the MySQL data directory (typically `/usr/local/mysql/data' for a binary installation or `/usr/local/var' for a source installation). Note that this is the directory that was specified at configuration time, not the one specified with --datadir when mysqld starts up! (--datadir has no effect on where the server looks for option files, because it looks for them before it processes any command-line arguments.) If anyone can explain that line, possibly point me to a good reference to the my.cnf and runtime options, I would be most appreciative. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: my.cnf
Are you wanting to physically relocate the myd, myi and frm files to a new location? If so specify datadir in the cnf file or --datadir on the command line for mysqld. -Original Message- From: Chris McKeever [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 10:35 AM To: [EMAIL PROTECTED] Subject: my.cnf I have been plowing through the mysql.com resources for something regarding the run-time configuration options as well as the my.cnf. In particular, I am trying to move the entire information store to a separate hard-drive. This line confuses me: --- Note that this is the directory that was specified at configuration time, not the one specified with --datadir when mysqld starts up! It is from: http://www.mysql.com/doc/en/Option_files.html DATADIR is the MySQL data directory (typically `/usr/local/mysql/data' for a binary installation or `/usr/local/var' for a source installation). Note that this is the directory that was specified at configuration time, not the one specified with --datadir when mysqld starts up! (--datadir has no effect on where the server looks for option files, because it looks for them before it processes any command-line arguments.) If anyone can explain that line, possibly point me to a good reference to the my.cnf and runtime options, I would be most appreciative. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Qwery help please
I have two tables; TESTtblRC ++++-+ | id | npanxx | RCname | RCstate | ++++-+ | 1 | 11 | ONE| CA | | 2 | 22 | ONE| CA | | 3 | 33 | ONE| CA | | 4 | 44 | TWO| CA | | 5 | 55 | TWO| CA | | 6 | 66 | THREE | CA | | 7 | 77 | FOUR | CA | | 8 | 88 | FOUR | CA | | 9 | 99 | FIVE | CA | ++++-+ TESTtblRCscope ++-+-+ | id | originating | terminating | ++-+-+ | 1 | ONE | TWO | | 2 | ONE | THREE | | 3 | TWO | ONE | | 4 | THREE | ONE | | 5 | FOUR| FIVE| | 6 | FIVE| FOUR| ++-+-+ Given an 'originating' I want to return a distinct list of 'RCname' (from 1st table) that does not match the originating or terminating in the second table or itself. So, given an originating of 'ONE' I should return Rcname's FOUR and FIVE (ONE is iteself, TWO and THREE are its terminatings) TIA! Jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mySql BUG
Hi, Try to do the following: *) Import the dump file I sent to you attached to this e-mail. There is a table called l5 and some data in the dump file. *) run the command: select conjuge.l5_nome from l5 as conjuge, l5 as parte where parte.l5_parte = 8 and parte.l5_seq = 0 and conjuge.l5_parte = parte.l5_parteconjuge order by conjuge.l5_seq desc limit 1; It shows the error 1105: Unknown error -- here is the bug!!! *) run the command: select conjuge.l5_nome from l5 as conjuge, l5 as parte where parte.l5_parte = 8 and parte.l5_seq = 0 and conjuge.l5_parte = parte.l5_parteconjuge group by conjuge.l5_seq having max(conjuge.l5_seq); It returns an empty set. It is what I would like had happend in the previous command. My System is Linux Debian Box kernel 2.4.18... with 768 of ram and MySQl 4.0.13 using InnoDB Tables !!! I have a dump of table with the bug ! i cant send file to the list... exists a FTP to put the file ? sql,query,innodb,hep please - ++ Dyego Souza do Carmo ++ Dep. Desenvolvimento - E S C R I B A I N F O R M A T I C A - The only stupid question is the unasked one (somewhere in Linux's HowTo) Linux registred user : #230601 --ICQ : 1647350 $ look into my eyes Phone : +55 041 296-2311 look: cannot open my eyes Fax : +55 041 296-6640 - Reply: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.1 and MyCC
I upgraded my test MySQL server yesterday to 4.1 and I'm now receiving an error when I try to connect using MyCC. The error is ERROR 1249: Client does not support authentification protocol requested by server. Consider upgrading MySQL client. I've tried to find a solution on the website, but either it's not mentioned there or my search skills need some refinement (probably the latter :-) Anyone have any idea what I have to do to solve this one? David Christensen Brokers International, Ltd. 1200 E Main St PO Box 160 Panora, IA 50216 (800) 362-1097 or (641) 755-2775 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Group by and Count query
What about SELECT COUNT(DISTINCT(host)) FROM TABLE_HOST WHERE path_a=; Mithun Karl J. Stubsjoen wrote: Hello, I am trying to count the number of unique records that exist in my database based on a given criteria. My table looks like this: TABLE_HOST id host path_a path_b The field host will have duplicates like: myway.com hisway.com hisway.com hisway.com someway.com someway.com yourway.com The given criteria is: only count the records where path_a is blank (path_a='') Here is the query I'd like to run (which fails - but is exactly what I need for a query): select sum(count(host)) from TABLE_HOST where path_a='' group by host This is the next query I tried... (nothing distinct about the result of a count) select distinct count(host) from TABLE_HOST where path_a='' So, is it possible to perform this query? Karl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mySql BUG
Dyego Souza do Carmo writes: Hi, Try to do the following: [skip] - ++ Dyego Souza do Carmo ++ Dep. Desenvolvimento - E S C R I B A I N F O R M A T I C A - The only stupid question is the unasked one (somewhere in Linux's HowTo) Linux registred user : #230601 --ICQ : 1647350 $ look into my eyes Phone : +55 041 296-2311 look: cannot open my eyes Fax : +55 041 296-6640 - Reply: [EMAIL PROTECTED] Hi! Your attachment was stripped off. Please upload it to: ftp://support.mysql.com:/pub/mysql/secret and let us know it's filename. -- Regards, -- For technical support contracts, go to https://order.mysql.com/?ref=msmi __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB /_/ /_/\_, /___/\___\_\___/ Fulltime Developer and Support Coordinator ___/ www.mysql.com Larnaca, Cyprus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: mySql BUG
Sinisa: Wednesday, June 11, 2003, 2:16:16 PM, você escreveu: ---[inicio]-- SM Hi! SM Your attachment was stripped off. SM Please upload it to: SM ftp://support.mysql.com:/pub/mysql/secret SM and let us know it's filename. The FileName is l5.zip ---[cortar]-- - ++ Dyego Souza do Carmo ++ Dep. Desenvolvimento - E S C R I B A I N F O R M A T I C A - The only stupid question is the unasked one (somewhere in Linux's HowTo) Linux registred user : #230601 --ICQ : 1647350 $ look into my eyes Phone : +55 041 296-2311 look: cannot open my eyes Fax : +55 041 296-6640 - Reply: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re[2]: mySql BUG
Dyego Souza do Carmo writes: Sinisa: Wednesday, June 11, 2003, 2:16:16 PM, você escreveu: ---[inicio]-- The FileName is l5.zip ---[cortar]-- - ++ Dyego Souza do Carmo ++ Dep. Desenvolvimento - E S C R I B A I N F O R M A T I C A - The only stupid question is the unasked one (somewhere in Linux's HowTo) Linux registred user : #230601 --ICQ : 1647350 $ look into my eyes Phone : +55 041 296-2311 look: cannot open my eyes Fax : +55 041 296-6640 - Reply: [EMAIL PROTECTED] Thanks. We shall take a look at it. -- Regards, -- For technical support contracts, go to https://order.mysql.com/?ref=msmi __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB /_/ /_/\_, /___/\___\_\___/ Fulltime Developer and Support Coordinator ___/ www.mysql.com Larnaca, Cyprus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Group by and Count query
snip Here is the query I'd like to run (which fails - but is exactly what I need for a query): select sum(count(host)) from TABLE_HOST where path_a='' group by host /snip well I think this one fails because count only ever returns one number (correct?). So I would think sum would take in a stream of numbers and then add them all together. Thus sum would fail, in my mind anyway. __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
left join
Which mysql´s version is able to make sub selects statament??? And left join? could you there give me some left join examples??? thanks a lot
Thousands of users? Newbie question
Hi Guys, I intend my online database to be used by 100 or so companies each with up to 100 employees. Each of these employees may add data as required. I don't want to assign one password for an entire company, in case one disgruntled ex-employee decides to post erroneous data. So presumably I need to issue each employee a username and password, then remove this when the employee leaves the company (or grant an admin for that company permission to do it). My questions are : How is the username and password issue best handled when so many potential users are involved? Should a username and password be added to the web form used to add data? or Should I just use .htaccess to grant usernames and passwords so no unauthorized user can access the submission form in the first place? Which is less resource hungry? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mySQL GUIs
Greeting I find SQLyog ( http://www.webyog.com ) the best of all. You will find lot of articles and tutorials on it on various MySQL/PHP site. It is comparable to any MySQL GUI in the market and is FREE!!! I think you should definately take a look at it. MyCCI dont like...coz its bloated and has lot of less features as compared to SQLyog. Karam --- Rodolphe Toots [EMAIL PROTECTED] wrote: hi! i am looking for a good mySQL gui for windows i have used mySQL front, which was an excellent free program, but i did not handle relations and diagrams. also the program is no longer being developed i have now found the prog mySQL tools (http://www.mysqltools.com/) and mySQL explorer that works almost as enterprise manager for MS SQL server. it even creates database diagrams with relations as in enterprise manager! only backdraw is that this program is not free, but it is the best i have ever seen so far is there anyone out there that knows of a program that is freeware/shareware and is good (like mySQL tools)? /rewdboy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 4.1 and MyCC
Don't know if I agree with that. I can connect via VPN to a test server at the home office running RH 8.0 and MySQL 4.1. I can also connect to a RH 7.3 machine running MySQL 4.0.12. BUT I can't connect to my local test server running Red Hat 7.3 and MySQL 4.1 without receiving the error message. -Original Message- From: Karam Chand [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 1:30 PM To: Christensen, Dave Subject: Re: MySQL 4.1 and MyCC MyCC does not support MySQL 4.1 coz the the library to connect to MySQL 4.1 has changed. Karam --- Christensen, Dave [EMAIL PROTECTED] wrote: I upgraded my test MySQL server yesterday to 4.1 and I'm now receiving an error when I try to connect using MyCC. The error is ERROR 1249: Client does not support authentification protocol requested by server. Consider upgrading MySQL client. I've tried to find a solution on the website, but either it's not mentioned there or my search skills need some refinement (probably the latter :-) Anyone have any idea what I have to do to solve this one? David Christensen Brokers International, Ltd. 1200 E Main St PO Box 160 Panora, IA 50216 (800) 362-1097 or (641) 755-2775 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
complie for hp-ux bug
I think I have encountered a bug in trying to install mysql 4.0.13 on hp-ux 11.0 when I try to install per sec 2.3 in your documentation I get the errors below. I believe the problem is identified ;in the fragment from config.log on the fourth line you will see a space between the u and the x in hpux11.00. I think this is why make can't find libz.sl which is in /usr/local/lib (I tried copying it to several other locations but it didn't help). Can you tell me what I can do to fix this? Thanks Dave Driscoll ddriscoll at triustc.com %%%TAIL OF OUTPUT from running make: *** Warning: This library needs some functionality provided by -lcrypt. *** I have the capability to make that library automatically link in when *** you link to this library. But I can only do this if you have a *** shared version of the library, which you do not appear to have. *** The inter-library dependencies that have been dropped here will be *** automatically added whenever a program is linked with this library *** or is declared to -dlopen it. /usr/ccs/bin/ld -b +h libmysqlclient.sl.12 +b /usr/local/lib/mysql -o .libs/libmysqlclient.sl.12.0 libmysql.lo password.lo manager.lo get_password.lo errmsg.lo my_init.lo my_static.lo my_malloc.lo my_realloc.lo my_create.lo my_delete.lo mf_tempfile.lo my_open.lo mf_casecnv.lo my_read.lo my_write.lo errors.lo my_error.lo my_getwd.lo my_div.lo mf_pack.lo my_messnc.lo mf_dirname.lo mf_fn_ext.lo mf_wcomp.lo typelib.lo safemalloc.lo my_alloc.lo mf_format.lo mf_path.lo mf_unixpath.lo my_fopen.lo my_symlink.lo my_fstream.lo mf_loadpath.lo my_pthread.lo my_thr_init.lo thr_mutex.lo mulalloc.lo string.lo default.lo my_compress.lo array.lo my_once.lo list.lo my_net.lo charset.lo hash.lo mf_iocache.lo mf_iocache2.lo my_seek.lo my_sleep.lo my_pread.lo mf_cache.lo my_vsnprintf.lo md5.lo my_getopt.lo my_gethostbyname.lo my_port.lo my_lib.lo strmov.lo strxmov.lo strxnmov.lo strnmov.lo strmake.lo strend.lo strnlen.lo strfill.lo is_prefix.lo int2str.lo str2int.lo strinstr.lo strcont.lo strcend.lo bcmp.lo bchange.lo bmove.lo bmove_upp.lo longlong2str.lo strtoull.lo strtoll.lo llstr.lo ctype.lo dbug.lo vio.lo viosocket.lo viossl.lo viosslfactories.lo net.lo -lz -lnsl -lm -lc /usr/ccs/bin/ld: Can't find library: z *** Error exit code 1 Stop. *** Error exit code 1 Stop. *** Error exit code 1 Stop. ###START FRAGMENT FROM config.log configure:11931: checking if libz with compress configure:11960: gcc -o conftest conftest.c -lz -lcrypt -lnsl -lm 5 /usr/local/lib/gcc-lib/hppa2.0n-hp-hpux11.00/3.2/../../../../hppa2.0n-hp-hpux11.00/bin/nm: /usr/local/lib/gcc-lib/hppa2.0n-hp-hpu x11.00/3.2/../../../libz.sl:no symbols configure:11963: $? = 0 configure:11966: test -s conftest configure:11969: $? = 0 configure:11979: result: yes ###END FRAGMENT FROM config.log# -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.1 character set documentation
Alexander Barkov and Peter Gulutzan have written up some documentation on the new character set support in MySQL 4.1, which has now been added to the online manual. You can read it here: http://www.mysql.com/doc/en/Charset.html Note that this documentation actually is ahead of the current release (4.1.0) because it is current for 4.1.1 and some things have changed since 4.1.0. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my.cnf
At 10:34 -0500 6/11/03, Chris McKeever wrote: I have been plowing through the mysql.com resources for something regarding the run-time configuration options as well as the my.cnf. In particular, I am trying to move the entire information store to a separate hard-drive. This line confuses me: --- Note that this is the directory that was specified at configuration time, not the one specified with --datadir when mysqld starts up! The directory that is specified at configuration time is the one that is hardwired into the server binary. You can relocate the data directory at server startup time with a --datadir option. But when looking for my.cnf files, the server will continue to look in the hardwired directory, if it exists. That's what that sentence means. (The server still looks in /etc/my.cnf; that doesn't change even if you use --datadir.) It is from: http://www.mysql.com/doc/en/Option_files.html DATADIR is the MySQL data directory (typically `/usr/local/mysql/data' for a binary installation or `/usr/local/var' for a source installation). Note that this is the directory that was specified at configuration time, not the one specified with --datadir when mysqld starts up! (--datadir has no effect on where the server looks for option files, because it looks for them before it processes any command-line arguments.) If anyone can explain that line, possibly point me to a good reference to the my.cnf and runtime options, I would be most appreciative. Thanks -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1 and MyCC
At 11:55 -0500 6/11/03, Christensen, Dave wrote: I upgraded my test MySQL server yesterday to 4.1 and I'm now receiving an error when I try to connect using MyCC. The error is ERROR 1249: Client does not support authentification protocol requested by server. Consider upgrading MySQL client. I've tried to find a solution on the website, but either it's not mentioned there or my search skills need some refinement (probably the latter :-) Anyone have any idea what I have to do to solve this one? Start the server with the --old-passwords option. Or put this in an option file: [mysqld] old-passwords That'll force the server to use the older password authentication mechanism. David Christensen Brokers International, Ltd. 1200 E Main St PO Box 160 Panora, IA 50216 (800) 362-1097 or (641) 755-2775 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mySQL GUIs
I'm a big fan of Scibit's Mascon -Original Message- From: Rodolphe Toots [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 5:17 AM To: [EMAIL PROTECTED] Subject: mySQL GUIs hi! i am looking for a good mySQL gui for windows i have used mySQL front, which was an excellent free program, but i did not handle relations and diagrams. also the program is no longer being developed i have now found the prog mySQL tools (http://www.mysqltools.com/) and mySQL explorer that works almost as enterprise manager for MS SQL server. it even creates database diagrams with relations as in enterprise manager! only backdraw is that this program is not free, but it is the best i have ever seen so far is there anyone out there that knows of a program that is freeware/shareware and is good (like mySQL tools)? /rewdboy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replicate
I don't really think this can be done, but I figured that I would ask anyways I want 3 mysql server running (A,B and C) I want only one table from A to replicate to B and I want all of B but the one table from A to replicate to C and I want C to replicate back to B -Michael This marks you as mine. -Emperor Jagang -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with MAX()
We are running v4.0.12. Consider the following: mysql SELECT MAX(assets) as assets - FROM do_deductibles - WHERE currency = 'usd'; ++ | assets | ++ | NULL | ++ 1 row in set (0.00 sec) mysql SELECT MAX(assets) as assets - FROM do_deductibles - WHERE currency = 'usd' AND - assets IS NOT NULL; +--+ | assets | +--+ | 1000 | +--+ 1 row in set (0.00 sec) mysql SELECT MAX(assets) as assets - FROM do_deductibles - WHERE currency = 'usd'; +--+ | assets | +--+ | 1000 | +--+ 1 row in set (0.00 sec) What's going on with the MAX() function? Why did it return NULL in the first query above. There were no adding/deleting data between the above queries and yet MySQL didn't pull the MAX from the table until I added the AND assets IS NOT NULL to the query. And yet after I ran that query, attempting to run the first query yields the proper results. Is this a bug with MySQL? thnx, Chris mysql,query,blah -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sub select
Hi, I've just downloaded the version 4.1. I tryed to run my first sub-select : SELECT curso.* FROM curso where curso.cdCurso IN (SELECT EstabMantemCurso.cdCurso FROM EstabMantemCurso) The following ERROR returned: [local] ERROR 1235: This version of MySQL doesn't yet support 'LIMIT IN/ALL/ANY/SOME subquery' The version is: 4.1.0-alpha-max-debug for Win95/Win98 Doesn't version 4.1 support yet sub-selects ? Thanks, Mauro. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
binary log
Is it possible to have more than one binary log per master or is it possible to start more than one thread to execute queries? I have 2 computers in a master and slave configuration. The slave is running without error but it can't keep up with the volume of updates being performed on the master. The slave is a dual processor box and only one cpu is being utilized because the slave is reading the master's binary log and running queries serially. The master and slave have several databases and it would be great if each database could run it's own thread to execute queries. It would stand a better chance of being able to keep up. I'm not expecting real-time replication but after 24 hours, the slave is already 5 hours behind. Sam Jumper Director of Data Acquisition eRealty.com 713-490-7512
timestamp
hello and thank you for your time, i have insert into table (column1, column2, create_dt) values (fname, lname, 'what do i put for create_dt?') does timestamp automatically insert the date without me specifying? thank you for your time again, tad -- Addison Ellis small independent publishing co. 114 B 29th Avenue North Nashville, TN 37203 (615) 321-1791 [EMAIL PROTECTED] [EMAIL PROTECTED] subsidiaries of small independent publishing co. [EMAIL PROTECTED] [EMAIL PROTECTED] addisonellis.com THIS E-MAIL AND ANY FILES TRANSMITTED WITH IT ARE CONFIDENTIAL AND ARE INTENDED SOLELY FOR THE INDIVIDUAL OR ENTITY TO WHOM THEY ARE ADDRESSED. IF YOU ARE NOT THE INTENDED RECIPIENT, PLEASE DO NOT READ, COPY OR RE-TRANSMIT THIS COMMUNICATION BUT DESTROY IT IMMEDIATELY. ANY UNAUTHORIZED DISSEMINATION, DISTRIBUTION OR COPYING OF THIS COMMUNICATION IS STRICTLY PROHIBITED. A -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Group by and Count query
You could do the following: select host,count(*) as counthost from TABLE_HOST group by 1 having counthost = 1; Which will list all the hosts with only 1 record.. Then get the numrows from that query to find uot how many there are.. Cheers, Andrew -Original Message- From: Mithun Bhattacharya [mailto:[EMAIL PROTECTED] Sent: Wednesday 11 June 2003 18:05 To: MySQL Mailing List Subject: Re: Group by and Count query What about SELECT COUNT(DISTINCT(host)) FROM TABLE_HOST WHERE path_a=; Mithun Karl J. Stubsjoen wrote: Hello, I am trying to count the number of unique records that exist in my database based on a given criteria. My table looks like this: TABLE_HOST id host path_a path_b The field host will have duplicates like: myway.com hisway.com hisway.com hisway.com someway.com someway.com yourway.com The given criteria is: only count the records where path_a is blank (path_a='') Here is the query I'd like to run (which fails - but is exactly what I need for a query): select sum(count(host)) from TABLE_HOST where path_a='' group by host This is the next query I tried... (nothing distinct about the result of a count) select distinct count(host) from TABLE_HOST where path_a='' So, is it possible to perform this query? Karl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: timestamp
yes, it automatically inserts the time. From the manual: 6.2.2.2 The DATETIME, DATE, and TIMESTAMP Types: ( http://www.mysql.com/doc/en/DATETIME.html ) The TIMESTAMP column type provides a type that you can use to automatically mark INSERT or UPDATE operations with the current date and time. If you have multiple TIMESTAMP columns, only the first one is updated automatically. Automatic updating of the first TIMESTAMP column occurs under any of the following conditions: a.. The column is not specified explicitly in an INSERT or LOAD DATA INFILE statement. b.. The column is not specified explicitly in an UPDATE statement and some other column changes value. (Note that an UPDATE that sets a column to the value it already has will not cause the TIMESTAMP column to be updated, because if you set a column to its current value, MySQL ignores the update for efficiency.) c.. You explicitly set the TIMESTAMP column to NULL. TIMESTAMP columns other than the first may also be set to the current date and time. Just set the column to NULL or to NOW(). - Original Message - From: Tad Ellis [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 10:57 PM Subject: timestamp hello and thank you for your time, i have insert into table (column1, column2, create_dt) values (fname, lname, 'what do i put for create_dt?') does timestamp automatically insert the date without me specifying? thank you for your time again, tad -- Addison Ellis small independent publishing co. 114 B 29th Avenue North Nashville, TN 37203 (615) 321-1791 [EMAIL PROTECTED] [EMAIL PROTECTED] subsidiaries of small independent publishing co. [EMAIL PROTECTED] [EMAIL PROTECTED] addisonellis.com THIS E-MAIL AND ANY FILES TRANSMITTED WITH IT ARE CONFIDENTIAL AND ARE INTENDED SOLELY FOR THE INDIVIDUAL OR ENTITY TO WHOM THEY ARE ADDRESSED. IF YOU ARE NOT THE INTENDED RECIPIENT, PLEASE DO NOT READ, COPY OR RE-TRANSMIT THIS COMMUNICATION BUT DESTROY IT IMMEDIATELY. ANY UNAUTHORIZED DISSEMINATION, DISTRIBUTION OR COPYING OF THIS COMMUNICATION IS STRICTLY PROHIBITED. A -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
timestamp solved - thanks
timestamp solved - thanks... -- Addison Ellis small independent publishing co. 114 B 29th Avenue North Nashville, TN 37203 (615) 321-1791 [EMAIL PROTECTED] [EMAIL PROTECTED] subsidiaries of small independent publishing co. [EMAIL PROTECTED] [EMAIL PROTECTED] addisonellis.com THIS E-MAIL AND ANY FILES TRANSMITTED WITH IT ARE CONFIDENTIAL AND ARE INTENDED SOLELY FOR THE INDIVIDUAL OR ENTITY TO WHOM THEY ARE ADDRESSED. IF YOU ARE NOT THE INTENDED RECIPIENT, PLEASE DO NOT READ, COPY OR RE-TRANSMIT THIS COMMUNICATION BUT DESTROY IT IMMEDIATELY. ANY UNAUTHORIZED DISSEMINATION, DISTRIBUTION OR COPYING OF THIS COMMUNICATION IS STRICTLY PROHIBITED. A -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: binary log
In order to help, could you post some info about both servers config (cpu speed, disk speed and OS as well as network capacity) - That would really help people understand the problem and find the bottleneck... Cheers, A -Original Message- From: Sam Jumper [mailto:[EMAIL PROTECTED] Sent: Wednesday 11 June 2003 21:30 To: [EMAIL PROTECTED] Subject: binary log Is it possible to have more than one binary log per master or is it possible to start more than one thread to execute queries? I have 2 computers in a master and slave configuration. The slave is running without error but it can't keep up with the volume of updates being performed on the master. The slave is a dual processor box and only one cpu is being utilized because the slave is reading the master's binary log and running queries serially. The master and slave have several databases and it would be great if each database could run it's own thread to execute queries. It would stand a better chance of being able to keep up. I'm not expecting real-time replication but after 24 hours, the slave is already 5 hours behind. Sam Jumper Director of Data Acquisition eRealty.com 713-490-7512 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1 and MyCC
Paul DuBois wrote: At 11:55 -0500 6/11/03, Christensen, Dave wrote: I upgraded my test MySQL server yesterday to 4.1 and I'm now receiving an error when I try to connect using MyCC. The error is ERROR 1249: Client does not support authentification protocol requested by server. Consider upgrading MySQL client. I've tried to find a solution on the website, but either it's not mentioned there or my search skills need some refinement (probably the latter :-) Anyone have any idea what I have to do to solve this one? Start the server with the --old-passwords option. Or put this in an option file: [mysqld] old-passwords Thanks - that also answers an earlier post of mine - although I think it should be old-password and if you've (as I did) set up 4.1 with new style passwords, you can't then just drop this into /etc/my.cnf without also changing all the passwords - the new and old schemes encrypt in a different way AFAIK. That'll force the server to use the older password authentication mechanism. David Christensen Brokers International, Ltd. 1200 E Main St PO Box 160 Panora, IA 50216 (800) 362-1097 or (641) 755-2775 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- From Peter Burden, [EMAIL PROTECTED] http://www.scit.wlv.ac.uk/~jphb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: binary log
On Wed, Jun 11, 2003 at 03:30:01PM -0500, Sam Jumper wrote: Is it possible to have more than one binary log per master or is it possible to start more than one thread to execute queries? I have 2 computers in a master and slave configuration. The slave is running without error but it can't keep up with the volume of updates being performed on the master. The slave is a dual processor box and only one cpu is being utilized because the slave is reading the master's binary log and running queries serially. The master and slave have several databases and it would be great if each database could run it's own thread to execute queries. It would stand a better chance of being able to keep up. I'm not expecting real-time replication but after 24 hours, the slave is already 5 hours behind. You could run separate MySQL instances for the various databases. That's the only way to get any parallelism about of it. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 8 days, processed 276,889,335 queries (371/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
configuration questions - newbie getting up-to-speed
Sorry from the low-level posts, but I am trying to get my grips around some simple set-up configuration. - What exactly does basedir do? It defauts to / which to me means it is at the 'base' of the filesystem. Why/when would you need to change it (since you would only be moving it up levels - or down depending how you look at it). - What does server-specific options mean (as in placing the my.cnf for server-specific)? Isnt mysql running on the server itself and the global my.cnf handles that? Or is this for something in regards to multiple daemons running? - Am I correct ot assume the MYSQL 4.0 default to using innodb data files? - Is there documentation (or the ability) to specifiy different data directories for different database systems. I found a nice howto that is pretty low-level: http://astro.temple.edu/~kumar/MySQLRepPro/Installing_MySQL.htm which got me going with a few questions, but the couple questions I can't figure out. Once again, if anyone can point to some other online howto's and basic administration, that would be great Thanks Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.1 and MyCC
At 23:18 +0100 6/11/03, Peter Burden wrote: Paul DuBois wrote: At 11:55 -0500 6/11/03, Christensen, Dave wrote: I upgraded my test MySQL server yesterday to 4.1 and I'm now receiving an error when I try to connect using MyCC. The error is ERROR 1249: Client does not support authentification protocol requested by server. Consider upgrading MySQL client. I've tried to find a solution on the website, but either it's not mentioned there or my search skills need some refinement (probably the latter :-) Anyone have any idea what I have to do to solve this one? Start the server with the --old-passwords option. Or put this in an option file: [mysqld] old-passwords Thanks - that also answers an earlier post of mine - although I think it should be old-password and if you've (as I did) set up 4.1 with new style passwords, you can't then just drop this into /etc/my.cnf without also changing all the passwords - the new and old schemes encrypt in a different way AFAIK. Actually, the option *is* --old-passwords (with an s), though probably the option processor will recognize both. Also, if you have set up 4.1 accounts with new passwords, you can still connect to those accounts, even with --old-passwords, but only when using 4.1 clients. That'll force the server to use the older password authentication mechanism. David Christensen Brokers International, Ltd. 1200 E Main St PO Box 160 Panora, IA 50216 (800) 362-1097 or (641) 755-2775 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: configuration questions - newbie getting up-to-speed
At 17:04 -0500 6/11/03, Chris McKeever wrote: Sorry from the low-level posts, but I am trying to get my grips around some simple set-up configuration. - What exactly does basedir do? It defauts to / which to me means it is at the 'base' of the filesystem. Why/when would you need to change it (since you would only be moving it up levels - or down depending how you look at it). It's the base (root) directory of your MySQL installation. If it's defaulting to / on your system, I'd guess that you have a system that doesn't place all the MySQL-related files under a single directory. Another typical value is /usr/local/mysql, or C:\mysql on Windows. I wouldn't worry about changing it if you have no particular reason to. - What does server-specific options mean (as in placing the my.cnf for server-specific)? Isnt mysql running on the server itself and the global my.cnf handles that? Or is this for something in regards to multiple daemons running? Yes, multiple servers. You can run several servers on a given machine (I have several dozen different ones on one of my systems), and you need to supply server-specific values for several of the options to keep them from interfering with each other. For example, the TCP/IP port they listen to must be different for each server. - Am I correct ot assume the MYSQL 4.0 default to using innodb data files? Yes. See: http://www.mysql.com/doc/en/Nutshell_4.0_features.html - Is there documentation (or the ability) to specifiy different data directories for different database systems. Huh? I found a nice howto that is pretty low-level: http://astro.temple.edu/~kumar/MySQLRepPro/Installing_MySQL.htm which got me going with a few questions, but the couple questions I can't figure out. Once again, if anyone can point to some other online howto's and basic administration, that would be great Thanks Chris -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: configuration questions - newbie getting up-to-speed
I also found out that mysqld --help provides a whole lot more of the information that I was looking for than just mysql --help! whoops -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 5:15 PM To: Chris McKeever; [EMAIL PROTECTED] Subject: Re: configuration questions - newbie getting up-to-speed At 17:04 -0500 6/11/03, Chris McKeever wrote: Sorry from the low-level posts, but I am trying to get my grips around some simple set-up configuration. - What exactly does basedir do? It defauts to / which to me means it is at the 'base' of the filesystem. Why/when would you need to change it (since you would only be moving it up levels - or down depending how you look at it). It's the base (root) directory of your MySQL installation. If it's defaulting to / on your system, I'd guess that you have a system that doesn't place all the MySQL-related files under a single directory. Another typical value is /usr/local/mysql, or C:\mysql on Windows. I wouldn't worry about changing it if you have no particular reason to. - What does server-specific options mean (as in placing the my.cnf for server-specific)? Isnt mysql running on the server itself and the global my.cnf handles that? Or is this for something in regards to multiple daemons running? Yes, multiple servers. You can run several servers on a given machine (I have several dozen different ones on one of my systems), and you need to supply server-specific values for several of the options to keep them from interfering with each other. For example, the TCP/IP port they listen to must be different for each server. - Am I correct ot assume the MYSQL 4.0 default to using innodb data files? Yes. See: http://www.mysql.com/doc/en/Nutshell_4.0_features.html - Is there documentation (or the ability) to specifiy different data directories for different database systems. Huh? I found a nice howto that is pretty low-level: http://astro.temple.edu/~kumar/MySQLRepPro/Installing_MySQL.htm which got me going with a few questions, but the couple questions I can't figure out. Once again, if anyone can point to some other online howto's and basic administration, that would be great Thanks Chris -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 4.1 and MyCC
Tried this and it didn't make any difference. Still getting the error when I try to connect to the server with MyCC. I CAN connect using the command line mysql program. -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 5:08 PM To: Peter Burden Cc: Christensen, Dave; '[EMAIL PROTECTED]' Subject: Re: MySQL 4.1 and MyCC At 23:18 +0100 6/11/03, Peter Burden wrote: Paul DuBois wrote: At 11:55 -0500 6/11/03, Christensen, Dave wrote: I upgraded my test MySQL server yesterday to 4.1 and I'm now receiving an error when I try to connect using MyCC. The error is ERROR 1249: Client does not support authentification protocol requested by server. Consider upgrading MySQL client. I've tried to find a solution on the website, but either it's not mentioned there or my search skills need some refinement (probably the latter :-) Anyone have any idea what I have to do to solve this one? Start the server with the --old-passwords option. Or put this in an option file: [mysqld] old-passwords Thanks - that also answers an earlier post of mine - although I think it should be old-password and if you've (as I did) set up 4.1 with new style passwords, you can't then just drop this into /etc/my.cnf without also changing all the passwords - the new and old schemes encrypt in a different way AFAIK. Actually, the option *is* --old-passwords (with an s), though probably the option processor will recognize both. Also, if you have set up 4.1 accounts with new passwords, you can still connect to those accounts, even with --old-passwords, but only when using 4.1 clients. That'll force the server to use the older password authentication mechanism. David Christensen Brokers International, Ltd. 1200 E Main St PO Box 160 Panora, IA 50216 (800) 362-1097 or (641) 755-2775 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 4.1 and MyCC
Okay, I've now added this using the 'old-passwords' syntax in my.cnf file. Still doesn't make any difference. Have no idea about how to change passwords from 'old' to 'new' -Original Message- From: Peter Burden [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 5:19 PM To: Paul DuBois Cc: Christensen, Dave; '[EMAIL PROTECTED]' Subject: Re: MySQL 4.1 and MyCC Paul DuBois wrote: At 11:55 -0500 6/11/03, Christensen, Dave wrote: I upgraded my test MySQL server yesterday to 4.1 and I'm now receiving an error when I try to connect using MyCC. The error is ERROR 1249: Client does not support authentification protocol requested by server. Consider upgrading MySQL client. I've tried to find a solution on the website, but either it's not mentioned there or my search skills need some refinement (probably the latter :-) Anyone have any idea what I have to do to solve this one? Start the server with the --old-passwords option. Or put this in an option file: [mysqld] old-passwords Thanks - that also answers an earlier post of mine - although I think it should be old-password and if you've (as I did) set up 4.1 with new style passwords, you can't then just drop this into /etc/my.cnf without also changing all the passwords - the new and old schemes encrypt in a different way AFAIK. That'll force the server to use the older password authentication mechanism. David Christensen Brokers International, Ltd. 1200 E Main St PO Box 160 Panora, IA 50216 (800) 362-1097 or (641) 755-2775 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- From Peter Burden, [EMAIL PROTECTED] http://www.scit.wlv.ac.uk/~jphb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 4.1 and MyCC
At 17:25 -0500 6/11/03, Christensen, Dave wrote: Tried this and it didn't make any difference. Still getting the error when I try to connect to the server with MyCC. I CAN connect using the command line mysql program. Are you using the 4.0 mysql or the 4.1 mysql? Are you trying to connect (with MySQLCC) to an account that has a short password or a long one? -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 5:08 PM To: Peter Burden Cc: Christensen, Dave; '[EMAIL PROTECTED]' Subject: Re: MySQL 4.1 and MyCC At 23:18 +0100 6/11/03, Peter Burden wrote: Paul DuBois wrote: At 11:55 -0500 6/11/03, Christensen, Dave wrote: I upgraded my test MySQL server yesterday to 4.1 and I'm now receiving an error when I try to connect using MyCC. The error is ERROR 1249: Client does not support authentification protocol requested by server. Consider upgrading MySQL client. I've tried to find a solution on the website, but either it's not mentioned there or my search skills need some refinement (probably the latter :-) Anyone have any idea what I have to do to solve this one? Start the server with the --old-passwords option. Or put this in an option file: [mysqld] old-passwords Thanks - that also answers an earlier post of mine - although I think it should be old-password and if you've (as I did) set up 4.1 with new style passwords, you can't then just drop this into /etc/my.cnf without also changing all the passwords - the new and old schemes encrypt in a different way AFAIK. Actually, the option *is* --old-passwords (with an s), though probably the option processor will recognize both. Also, if you have set up 4.1 accounts with new passwords, you can still connect to those accounts, even with --old-passwords, but only when using 4.1 clients. That'll force the server to use the older password authentication mechanism. David Christensen Brokers International, Ltd. 1200 E Main St PO Box 160 Panora, IA 50216 (800) 362-1097 or (641) 755-2775 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need help with an update
Here is the scenario. I was running 3.23.39 that came with BSD/OS 4.3 , All the databases were running active. I upgraded to BSD/OS 5.0 which has Mysql 3.23.49 and suddenly NO databases are seen. Everything is where is is supposed to be, but the mysqld is not seeing the DBs that were running with 3.23.39. What do I need to do to correct this?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Programmatically building table
Hello All: I'm working on an application that has to drop and then recreate tables programmatically and does not have 'file' privileges. i.e., I can't use 'load data infile'. On a smaller venue, I programmatically create a .sql script file that drops, then re-creates the table, and then writes a series of insert statements that line by line insert the values as imported from a tab-delimited text file. (Essentially the same as a 'dump') Then make a system call to mysql to load the files as in 'mysql --host=host --user=usr --password=pwd --database=db table.sql' Now that is sufficient for under a thousand records, but next I must rebuild a table of 50-60 thousand records and more with 70 fields or more. I would welcome any helpful hints as to optimise this, as well as pointers to docs or even thoughts on a different solution. TIA and best regards -- Tim Johnson [EMAIL PROTECTED] http://www.alaska-internet-solutions.com http://www.johnsons-web.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to import MySQL tables from Linux?
Instead of creating tables manually in MySQL, I would like to import them. I have done this under Oracle, with the following @a:\sqlfiles\foo.sql How do I import sql files into mysql using Linux? I have a directory under /root/sql thanks -Chris = GTFG GAIM ID: cmmiller1973 __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: configuration questions - newbie getting up-to-speed
Hi Chris, Not sure which OS you are using, but in case you use Linux you should try info mysql You will most probably find what you are after. Best regards Nils Valentin 2003 6 12 07:18Chris McKeever : I also found out that mysqld --help provides a whole lot more of the information that I was looking for than just mysql --help! whoops -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 5:15 PM To: Chris McKeever; [EMAIL PROTECTED] Subject: Re: configuration questions - newbie getting up-to-speed At 17:04 -0500 6/11/03, Chris McKeever wrote: Sorry from the low-level posts, but I am trying to get my grips around some simple set-up configuration. - What exactly does basedir do? It defauts to / which to me means it is at the 'base' of the filesystem. Why/when would you need to change it (since you would only be moving it up levels - or down depending how you look at it). It's the base (root) directory of your MySQL installation. If it's defaulting to / on your system, I'd guess that you have a system that doesn't place all the MySQL-related files under a single directory. Another typical value is /usr/local/mysql, or C:\mysql on Windows. I wouldn't worry about changing it if you have no particular reason to. - What does server-specific options mean (as in placing the my.cnf for server-specific)? Isnt mysql running on the server itself and the global my.cnf handles that? Or is this for something in regards to multiple daemons running? Yes, multiple servers. You can run several servers on a given machine (I have several dozen different ones on one of my systems), and you need to supply server-specific values for several of the options to keep them from interfering with each other. For example, the TCP/IP port they listen to must be different for each server. - Am I correct ot assume the MYSQL 4.0 default to using innodb data files? Yes. See: http://www.mysql.com/doc/en/Nutshell_4.0_features.html - Is there documentation (or the ability) to specifiy different data directories for different database systems. Huh? I found a nice howto that is pretty low-level: http://astro.temple.edu/~kumar/MySQLRepPro/Installing_MySQL.htm which got me going with a few questions, but the couple questions I can't figure out. Once again, if anyone can point to some other online howto's and basic administration, that would be great Thanks Chris -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table design suggestions?
I have to add group functionality to an existing database. The functionality required will be something like /etc/group in linux. I have a need to create and maintain a list of groups. Then, each group will have a list of members. I will need to be able to search by member name and get a list of what groups the member is in. Also, I will need to maintain the members in each group (add, delete members). I would appreciate any suggestions on how best to model this in MySQL. I've come up with two ideas and I'm not crazy about either. *** * The first idea is simple to create a table with two columns: *** groupNamevarchar, primary key groupMembers text (comma seperated list) - ex: groupOnemember1, member2, member3 groupTwo member2, member4 *** * The second idea is to create two tables. *** table groups groupName varchar primary key groupDescription text table groupRelations groupName groupMember - ex: groups table: groupOneFirst test group groupTwo Second test group groupRelations: groupOnemember1 groupOnemember3 groupTwomember2 groupTwomember3 Like I said before, I'm not very happy with either method. If anyone else has any better ideas they wouldn't mind sharing, I'd really appreciate it! Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
about replication
Hello, mysql, 2 Mysql 4.0.12 windows version with INNODB replciate sucessfully,both in myisam and innodb table. According to mysql manual(PDF),there is only one master database in the same time,but with many slave server. Master for query,and slave for update or insert operation. Therefore,while programming with JAVA tech,I must write 2 connection pool? one for query,other for update? That's not very proper.Coze the programe switch the connection frequently.It really slow down the web application performance. How do you slove this problem?Any idea appreciate. Best regards. MaFai [EMAIL PROTECTED] 2003-06-12 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table design suggestions?
On Wednesday 11 June 2003 08:47 pm, JJ wrote: I have to add group functionality to an existing database. The functionality required will be something like /etc/group in linux. How about 3 tables. Groups, Members, and Relationships. Table Group id int auto_increment name char Table Member id int auto_increment name char Table Relationship group_id int member_id int this makes queries like: select member.name from group,member,relationship where group.name='Group Foo' and relationship.group_id=group.id and relationship.member_id=member.id; select group.name from group,member,relationship where member.name='Joe Bar' and relationship.group_id=group.id and relationship.member_id=member.id; Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table design suggestions?
On Wed, 11 Jun 2003 22:17:09 -0230, JJ [EMAIL PROTECTED] wrote: [...] | *** | * The second idea is to create two tables. | *** | table groups | groupName varchar primary key | groupDescription text | | table groupRelations | groupName | groupMember | | - ex: | groups table: | groupOneFirst test group | groupTwo Second test group | | groupRelations: | groupOnemember1 | groupOnemember3 | groupTwomember2 | groupTwomember3 | | Like I said before, I'm not very happy with either method. If anyone else | has any better ideas they wouldn't mind sharing, I'd really appreciate it! This scheme will give you the most flexibility. There is no limit as to how many people can be in a group or how many groups a person can be in. You will want to create a joined unique key on the group_id and member_id. Have Fun! Michael -- Michael Brunson 504.200. [EMAIL PROTECTED]ICQ: 83163789 Data Center Manager -- www.zipa.com --Zipa, LLC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table design suggestions?
given this, how would you write a query to list all members that are part of group_id=1 AND group_id=2 AND ... group_id 20. without having to write 20 JOIN relationship r1 on r1.group_id =1 JOIN relationship r20 on r20.group_id =20) wich gets extremely slow with large ammounts of JOINS On Wednesday 11 June 2003 08:47 pm, JJ wrote: I have to add group functionality to an existing database. The functionality required will be something like /etc/group in linux. How about 3 tables. Groups, Members, and Relationships. Table Group id int auto_increment name char Table Member id int auto_increment name char Table Relationship group_id int member_id int this makes queries like: select member.name from group,member,relationship where group.name='Group Foo' and relationship.group_id=group.id and relationship.member_id=member.id; select group.name from group,member,relationship where member.name='Joe Bar' and relationship.group_id=group.id and relationship.member_id=member.id; Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question about insert failed.
Hello all, In version 3.23.X we had a script running: 1 - Create a temp table from imported cdf files. 2 - Select into the production table using criteria. 3 - Use production table. -- On a duplicate record, for some reason our Oracle folks think this is acceptable, the duplicate record was ignored and the (No 2.) query continued. Now with 4.0.13 the same script does the following: 1 - Create temp files from imported cdf files. 2 - Insert into production table from temp. **FAILS ON DUPLICATE RECORD** 3 - Use production data with limited data. Was this by design in the 3.x.x - 4.x.x or am I missing a cnf file setting that may make this go back to the way it was. NOTE: We are going to use replace, but we do have to answer for 'why did your system not work when ours does...' questions. Rob. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
