Synchronising SQL Server and MySQL
I need to synchronise an SQL Server 2000 back end with a MYSQL database on a different box (windows). Can anyone point me in the right direction. I have found a number of resources on synchronising 2 sql server databases but I cant find anything on SQLServer and MySQL. Thanks in Advance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
support for Prepared statement
Hi All, I am using MySQL 4.0.13 and using Connector/j jdbc drivers. What i am looking for is the support for Stored Procedures. Following is what Connector/J documentation says. PreparedStatements are implemented by the driver, as MySQL does not have a prepared statement feature. Because of this, the driver does not implement getParameterMetaData() or getMetaData() as it would require the driver to have a complete SQL parser in the client. I wrote a program to check for Stored Procdures is running fine. Now what i want to knwo is whether MySQL server is supporting Stored Procedures or not? REgards Deepak Saini **Disclaimer Information contained in this E-MAIL being proprietary to Wipro Limited is 'privileged' and 'confidential' and intended for use only by the individual or entity to which it is addressed. You are notified that any use, copying or dissemination of the information contained in the E-MAIL in any manner whatsoever is strictly prohibited. ***
--with-mysqld-user=mysql: not found configure error on 4.0.13
Hi, My configure/compilation script is: -- #!/usr/bin/ksh D=mysql-4.0.13 #PORT=3306 PORT=3406 H=/usr/local/mysql-4-test CC=cc -pthread export CC CXX=cxx -pthread -O export CXX cd $D ./configure \ --prefix=$H --with-mysqld-user=mysql\ --with-tcp-port=$PORT\ --with-unix-socket-path=$H/var/mysql.sock\ --with-named-thread-libs=-lpthread -lmach -lexc -lc\ --enable-thread-safe-client gmake -- I get this error in the output of the configure, but it goes on and compiles with user as root instead of mysql as I specified in my script. This worked fine under 3.x. -- ./do4[14]: --with-mysqld-user=mysql: not found cd libmysql; gmake link_sources gmake[1]: Entering directory `/src/net/db/mysql/mysql-4.0.13/libmysql' -- This is just part of the output from my 'do4' script. Thanks, Douglas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data types and the C API
Thanks Paul. After looking a little more closely at the documentation I figured this out. The example there looks like this: num_fields = mysql_num_fields(result); while((row = mysql_fetch_row(result))) { unsigned long *lengths; lengths = mysql_fetch_lengths(result); for(i = 0; i num_fields; i++) { printf(%.*s] , (int) lengths[i], row[i] ? row[i] : NULL); } printf(\n); } While this does not explicitely say that all columns are returned as null-terminated strings, it is implied in the printf statement. Thanks for you quick reply. Andrew At 20:37 -0400 6/18/03, Andrew Pierce wrote: I am trying to learn to use the C API for MySQL and have a question. I have a table that has a primary key defined as int(11). I don't know what data type this translates to in C. I thought maybe an unsigned int or an unsigned long. I have this little loop that cycles through the results of a query and displays the first two columns, the first being the int(11) and the second a varchar. Here is the code: result = mysql_use_result(mysql); while((row = mysql_fetch_row(result))) { printf(%lu %s\n, row[0], row[1]); } The first column is printed as garbage while the second column shows the data correctly. INT in MySQL is a 4-byte data type, but that's on the server side. The result that you're observing is that all values are returned to the *client* as strings. So you can either print the string using %s, or convert it to a C int and use %ld (you don't say that your MySQL type is UNSIGNED, so I'm assuming %ld rather than %lu). The MySQL 4.1 client/server protocol has some features that allow you to get back values in binary form without the conversion to string, but you're probably not using that. Help? Thanks. Andrew -- 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]
ssh problem with mysql_real_connect - repost
I think I need a MySQL code guru... I am using the 4.0.12 client library on a Win2k host to connect to a 4.0.13 linux server. If I connect directly to the server using mysql_real_connect, everything runs fine. However, I want to use SSH tunneling. So I set up a SSHv2 tunneling connection from localhost:3306 to server:3306. If I use the command line, i.e. mysql -u foo -pbar -h localhost, I can connect to the remote server via SSH. The same is, if I use tools like SQLyog. But in my program which call the mysql C API, the program freezes. I enable the dbug, here is the output. The program hangs at the last line... Any ideas how to avoid this problem? Thanks in advance, Tobias my_init | my_win_init | my_win_init | exit: home: '(null)' my_init libmysql_init libmysql_init my_malloc | my: Size: 544 MyFlags: 48 | exit: ptr: 4d144c8 my_malloc mysql_real_connect | enter: host: 127.0.0.1 db: rssvertrieb user: rss | info: Server name: '127.0.0.1'. Named Pipe: MySQL | error: host: '127.0.0.1' socket: '' named_pipe: 0 have_tcpip: 1 | info: Server name: '127.0.0.1'. TCP sock: 3306 | vio_new | | enter: sd=1140 | | my_malloc | | | my: Size: 84 MyFlags: 16 | | | exit: ptr: 4d124b0 | | my_malloc | | vio_reset | | | enter: type=1 sd=1140 localhost=0 | | vio_reset | vio_new | my_net_init | | my_malloc | | | my: Size: 8199 MyFlags: 16 | | | exit: ptr: 4d14720 | | my_malloc | | vio_fastsend | | | exit: 0 | | vio_fastsend | my_net_init | vio_keepalive | | enter: sd=1140, set_keep_alive=1 | vio_keepalive | vio_is_blocking | | exit: 1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Synchronising SQL Server and MySQL
If you are planning to synchronise the data from one to another, that should not be rocket science. However, if you want both database servers to update the data, you're in for something rather heavy. In the easy case, I would probably get the replication feature to output all the database updates, and then write my little parser that would transform dates and other peculiarities from the format of the master to the format of the slave. In the hard case, you will need to do some heavy lifting in order to control concurrency. That is, you will need to make sure databases A and B are identical even when both database servers write data at the same time. It is to the best of my knowledge feasible, but it is definitely not easy to do, and even harder to do without sacrificing performance. Anyhow, that's my 2c. best regards, Jeppe -Original Message- From: Paul [mailto:[EMAIL PROTECTED] Sent: 19. juni 2003 11:31 To: [EMAIL PROTECTED] Subject: Synchronising SQL Server and MySQL I need to synchronise an SQL Server 2000 back end with a MYSQL database on a different box (windows). Can anyone point me in the right direction. I have found a number of resources on synchronising 2 sql server databases but I cant find anything on SQLServer and MySQL. Thanks in Advance -- 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: I don'get binlogs to log properly
[EMAIL PROTECTED] wrote: so there I am, mysql server version 3.23.49-8.4 OS linux debian woody box type i386 I've set up binlogs in my.cnf I do get the binlog files created and rotated this with flush logs as master reset. I have the checkpoint and log_pos table set up. what I do not get is that the binlog files do get filled up with some data when I create tables insert in them delete in them or do whatever data manipulation whatsoever. If I've got you right, binary log files are created, but _all_ of them are empty. Right? Show me entries from my.cnf related to the binary logs. -- 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: where do I find the error log file?
Pushpinder Singh Garcha [EMAIL PROTECTED] wrote: I need to find out why the mysqld is not starting up ? Can someone please throw some light to the location of this file on a Mac? By default in the MySQL data dir. -- 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]
DB_ARCH_ABS: DB_NOTFOUND
Hi I m new to mysql Does any body know why fokowing error occurs Error: bdb: log_archive: DB_ARCH_ABS: DB_NOTFOUND: No matching key/data pair found Pleas send me all reason of this error and also tel me how i can increase the connections which are 100 by default Regards M Ejaz Haider Net Admin IQRA Univeristy _ STOP MORE SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=features/junkmail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: I don'get binlogs to log properly
Victoria Reznichenko writes: [EMAIL PROTECTED] wrote: so there I am, mysql server version 3.23.49-8.4 OS linux debian woody box type i386 I've set up binlogs in my.cnf I do get the binlog files created and rotated this with flush logs as master reset. I have the checkpoint and log_pos table set up. what I do not get is that the binlog files do get filled up with some data when I create tables insert in them delete in them or do whatever data manipulation whatsoever. If I've got you right, binary log files are created, but _all_ of them are empty. Right? Show me entries from my.cnf related to the binary logs. here we go (I was beginning to think nobody cares about backup with mysql) - # The following can be used as easy to replay backup logs or for replication server-id = 1 #log-update = /var/log/mysql/binlogs/update log-bin = /var/log/mysql/binlogs/mysql-bin binlog-do-db= test mysql #binlog-ignore-db = include_database_name #skip-grant-tables # set-variable=max_binlog_size=1048576 set-variable=max_binlog_cache_size =1048576 -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Solaris SPARC installation instructions
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 18 Jun 2003, Douglas Kramer wrote: The download bundle for MySQL Solaris SPARC 32-bit includes instructions for installing Windows and Linux versions, but apparently not Solaris SPARC versions. (There are notes, but not instructions) Can anyone please point me to installation instructions for Solaris SPARC? Take a look at this page: http://www.mysql.com/doc/en/Installing_binary.html Bye, LenZ - -- For technical support contracts, visit https://order.mysql.com/?ref=mlgr __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mr. Lenz Grimmer [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Production Engineer /_/ /_/\_, /___/\___\_\___/ Hamburg, Germany ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2-rc1-SuSE (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQE+8bgeSVDhKrJykfIRAl+pAJoD6GRpmQGPLUP1KhoEUkAvSH7S0ACfZq3R i/iwSAtecgtKbIF/Dv9KK6Q= =8ZBP -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question
Jake Johnson wrote: This is one quick way to get the newest records of a group if you are grouping by the sku and stock. select stock, sku, qty from table where concat(dt_tm,stock,sku) IN ( select concat(max(dt_tm), stock, sku) from table group by stock, sku ) Another approach (also assuming a current version of MySQL which supports subselects) is: SELECT stock, sku, qty FROM table t1 WHERE dt_tim = ( SELECT max(dt_tm) FROM table t2 WHERE t1.stock = t2.stock AND t1.sku = t2.sku ) This should be more reliable than Jake's solution, which will have problems with some data values. To illustrate the possible problem, run the above query on the following data: dt_tm stocksku qty dt1 A BB 1 dt1 ABB 2 Jake's query will return a single row instead of two rows. (Sorry, Jake, I don't mean to put you on the spot!) Bruce Feist I have a table with stock-status transactions like ... 2003-06-17 06:00 stockA SKU1 QTY 98 2003-06-16 06:10 stockA SKU1 QTY 101 2003-06-15 04:59 stockA SKU1 QTY 111 - the time for updating the transaction - each specific stock - each specific SKU / partnumber - quantity in stock at time of transaction The SQL issue - are there some way in SQL I can SELECT only latest transaction for each stock/SKU no matter date of update, or do I have to read thrue all transactions and select in the program ??? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Mailing list.
Hello I signed up for this about 2 days ago. And now I can't get out of the list. Could you guys help me out here? where do I unsubscribe? Jason - Original Message - From: Dathan Vance Pattishall [EMAIL PROTECTED] To: 'Grégoire Dubois' [EMAIL PROTECTED]; 'Mysql' [EMAIL PROTECTED] Sent: Wednesday, June 18, 2003 7:29 PM Subject: RE: Can we crypt passwords on MySQL Search for PASSWORD @ www.mysql.com It's a really convenient function. ---Original Message- --From: Grégoire Dubois [mailto:[EMAIL PROTECTED] --Sent: Wednesday, June 18, 2003 5:07 PM --To: 'Mysql' --Subject: Can we crypt passwords on MySQL -- --Hi all, -- --Is it possible to crypt the passwords on MySQL. If yes, how does it --work, and how is it to be implemented. --Any link would be great. -- --Thank you. --Grégoire Dubois. -- -- -- -- --MySQL General Mailing List --For list archives: http://lists.mysql.com/mysql --To unsubscribe: --http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Mailing list.
Was it really that bad :D Click on the link below and you should be unsubscribed http://lists.mysql.com/[EMAIL PROTECTED] -Original Message- From: Jason [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 3:40 PM To: Dathan Vance Pattishall; 'Grégoire Dubois'; 'Mysql' Subject: MySQL Mailing list. Hello I signed up for this about 2 days ago. And now I can't get out of the list. Could you guys help me out here? where do I unsubscribe? Jason - Original Message - From: Dathan Vance Pattishall [EMAIL PROTECTED] To: 'Grégoire Dubois' [EMAIL PROTECTED]; 'Mysql' [EMAIL PROTECTED] Sent: Wednesday, June 18, 2003 7:29 PM Subject: RE: Can we crypt passwords on MySQL Search for PASSWORD @ www.mysql.com It's a really convenient function. ---Original Message- --From: Grégoire Dubois [mailto:[EMAIL PROTECTED] --Sent: Wednesday, June 18, 2003 5:07 PM --To: 'Mysql' --Subject: Can we crypt passwords on MySQL -- --Hi all, -- --Is it possible to crypt the passwords on MySQL. If yes, how does it --work, and how is it to be implemented. Any link would be great. -- --Thank you. --Grégoire Dubois. -- -- -- -- --MySQL General Mailing List --For list archives: http://lists.mysql.com/mysql --To unsubscribe: --http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] All information contained in this email is confidential and may be used by the intended recipient only. All information contained in this email is confidential and may be used by the intended recipient only. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't drop a database
Hello. I am running MySQL database on Sun Solaris 9. # mysql -V mysql Ver 11.18 Distrib 3.23.54, for sun-solaris2.8 (sparc) I am having a problem dropping a database. All the tables in the database have been dropped successfully, but the database does not want to go away. To be more specific, when I execute: drop database clone_updater; query runs without errors, but database is still there. mysql show databases; +--+ | Database| +--+ | clone_updater| | mysql | +--+ 51 rows in set (0.01 sec) mysql drop database clone_updater; Query OK, 0 rows affected (0.00 sec) mysql show databases; +--+ | Database| +--+ | clone_updater| | mysql | +--+ 51 rows in set (0.01 sec) Restarting mysql had no effect on this issue. Can anyone please help? Thank you in advance, Leo. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
unknow SQL Error!
Hi People, Please, how are the error whit my sql? I receive this message when I execute it in phpMyAdmin 2.5.0: My sql host server is the portlandsql. Erro comando SQL : CREATE TABLE nuke_contactbook( uid int( 11 ) DEFAULT '0' NOT NULL , contactid int( 11 ) NOT NULL AUTO_INCREMENT , firstname varchar( 50 ) , lastname varchar( 50 ) , email varchar( 255 ) , company varchar( 255 ) , homeaddress varchar( 255 ) , city varchar( 80 ) , homephone varchar( 255 ) , workphone varchar( 255 ) , homepage varchar( 255 ) , IM varchar( 255 ) , events text, reminders int( 11 ) , notes text, PRIMARY KEY ( contactid ) , KEY contactid( contactid ) , KEY uid( uid ) ) Mensagens do MySQL : You have an error in your SQL syntax near 'events text, reminders int( 11 ) , notes text, PRIMARY KEY ( contactid ) , K' at line 13 Thanks very much.Marcelo---Outgoing mail is certified Virus Free.Checked by AVG anti-virus system (http://www.grisoft.com).Version: 6.0.491 / Virus Database: 290 - Release Date: 18/6/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert hang problem on Solaris 8
It looks like my MYI file was seriously corrupted, beyond myisamchk being able to do a thing with it. Has anyone seen this before on Solaris 8? Fortunately the data was trashable but when this hits production it wont always be. Thanks.. Randy On Wed, 2003-06-18 at 21:46, Nils Valentin wrote: Hi Randy, I am not sure if this is working, but did you try the slow query log ?? Why I am not sure is because I believe the result is logged AFTER the request is complete (which never happens in your case if I understood correctly)? Best regards Nils Valentin Tokyo/Japan 2003 6 18 23:12Randy Locklair : Hey there everyone. I'm having a problem on a MySQL installation (vsn 4.0.12-standard) on Solaris 8. I've got a few tables that get inserted into maybe 1000-1500 times a day. It seems to be pretty random, but I've seen three or four times that the database just hangs on the insert. SHOW PROCESSLIST shows this for the offending process: | 77 | cpms | localhost | cpms | Query | 8076 | update | INSERT INTO cpms_orders (order_id, add_timestamp) VALUES ('20030618-3', now()) | I've still got it running, if anyone has any suggestions for a way to get more debug data. The mysql software was built with gcc 3.2.2, again, on solaris 8. If anyone's got any suggestions let me know! Thanks! Unfortunately I'm gonna have to restart this sooner than later. :/ -Randy Swiss American Securities Inc. MMS sasiny.com made the following annotations on 06/18/03 10:04:42 --- --- [ALERT] -- Access Manager: DISCLAIMER: This e-mail contains proprietary and confidential information some or all of which may be legally privileged. It is intended only for the stated addressee(s) and access to it by any other person authorized. If you are not the intended recipient and an addressing or transmission error has misdirected this e-mail, please notify the author IMMEDIATELY, by replying to this e-mail, then delete this message and all copies from all locations in your system. You should not use, disseminate, disclose, distribute, copy, print, or rely on this e-mail: to do so may be unlawful. Swiss American Securities Inc. (SASI) and its affiliates reserve the right to monitor all e-mail communications through their networks. ***Please note that this message may contain preliminary information regarding transactions that have been executed for your account and are subject to final confirmation. === === -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils Swiss American Securities Inc. MMS sasiny.com made the following annotations on 06/19/03 10:12:20 -- [ALERT] -- Access Manager: DISCLAIMER: This e-mail contains proprietary and confidential information some or all of which may be legally privileged. It is intended only for the stated addressee(s) and access to it by any other person authorized. If you are not the intended recipient and an addressing or transmission error has misdirected this e-mail, please notify the author IMMEDIATELY, by replying to this e-mail, then delete this message and all copies from all locations in your system. You should not use, disseminate, disclose, distribute, copy, print, or rely on this e-mail: to do so may be unlawful. Swiss American Securities Inc. (SASI) and its affiliates reserve the right to monitor all e-mail communications through their networks. ***Please note that this message may contain preliminary information regarding transactions that have been executed for your account and are subject to final confirmation. = -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question
Nice approach Bruce, but I too won't have any problems with your case because I am grouping by sku and stock in the sub-query. Regards, Jake Johnson [EMAIL PROTECTED] -- Plutoid - http://www.plutoid.com Shop Plutoid for the best prices on Rims and Car Audio Products On Thu, 19 Jun 2003, Bruce Feist wrote: Jake Johnson wrote: This is one quick way to get the newest records of a group if you are grouping by the sku and stock. select stock, sku, qty from table where concat(dt_tm,stock,sku) IN ( select concat(max(dt_tm), stock, sku) from table group by stock, sku ) Another approach (also assuming a current version of MySQL which supports subselects) is: SELECT stock, sku, qty FROM table t1 WHERE dt_tim = ( SELECT max(dt_tm) FROM table t2 WHERE t1.stock = t2.stock AND t1.sku = t2.sku ) This should be more reliable than Jake's solution, which will have problems with some data values. To illustrate the possible problem, run the above query on the following data: dt_tm stocksku qty dt1 A BB 1 dt1 ABB 2 Jake's query will return a single row instead of two rows. (Sorry, Jake, I don't mean to put you on the spot!) Bruce Feist I have a table with stock-status transactions like ... 2003-06-17 06:00 stockA SKU1 QTY 98 2003-06-16 06:10 stockA SKU1 QTY 101 2003-06-15 04:59 stockA SKU1 QTY 111 - the time for updating the transaction - each specific stock - each specific SKU / partnumber - quantity in stock at time of transaction The SQL issue - are there some way in SQL I can SELECT only latest transaction for each stock/SKU no matter date of update, or do I have to read thrue all transactions and select in the program ??? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How do we convert a well-structured XML file to its corresponding tables in a MySQL Database ?
How do we convert a well-structured XML file to its corresponding tables in a MySQL Database ? And How do we normalize this well-structured XML file prior to conversion ? This message and any attachments are confidential and intended solely for the addressee. Any unauthorized use, alteration or dissemination is prohibited. Lawyers Access Web (Pty) Ltd. accepts no liability whatsoever for any loss, whether it be direct, indirect or consequential, arising from information made available and actions resulting there from.
Re: unknow SQL Error!
--Original Message- cut CREATE TABLE nuke_contactbook( cut workphone varchar( 255 ) , homepage varchar( 255 ) , IM varchar( 255 ) , events text, reminders int( 11 ) , notes text, PRIMARY KEY ( contactid ) , KEY contactid( contactid ) , KEY uid( uid ) You seem to be trying to create contactid key twice? ) Mensagens do MySQL : You have an error in your SQL syntax near 'events text, reminders int( 11 ) , notes text, PRIMARY KEY ( contactid ) , K' at line 13 Thanks very much.Marcelo---Outgoing mail is certified Virus Free.Checked by AVG anti-virus system (http://www.grisoft.com).Version: 6.0.491 / Virus Database: 290 - Release Date: 18/6/2003 Terry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is There An Automated Way To Convert A Well-structured XML File T o Its Corresponding Tables In A MySQL Database ?
How do we convert a well-structured XML file to its corresponding tables in a MySQL Database ? And How do we normalize this well-structured XML file prior to conversion ? This message and any attachments are confidential and intended solely for the addressee. Any unauthorized use, alteration or dissemination is prohibited. Lawyers Access Web (Pty) Ltd. accepts no liability whatsoever for any loss, whether it be direct, indirect or consequential, arising from information made available and actions resulting there from.
RE: Is There An Automated Way To Convert A Well-structured XML File T o Its Corresponding Tables In A MySQL Database ?
You need only ask once. This article by Paul Dubois may help :http://www.kitebird.com/articles/mysql-xml.html This Google search resulted in hundreds of resources: http://www.google.com/search?hl=enedition=caq=convert+xml+to+mysqlbtn meta%3Dsearch%3Dsearch=Search+the+Web Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Eldrid Rensburg [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 8:18 AM To: [EMAIL PROTECTED] Subject: Is There An Automated Way To Convert A Well-structured XML File T o Its Corresponding Tables In A MySQL Database ? Importance: High How do we convert a well-structured XML file to its corresponding tables in a MySQL Database ? And How do we normalize this well-structured XML file prior to conversion ? This message and any attachments are confidential and intended solely for the addressee. Any unauthorized use, alteration or dissemination is prohibited. Lawyers Access Web (Pty) Ltd. accepts no liability whatsoever for any loss, whether it be direct, indirect or consequential, arising from information made available and actions resulting there from. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
wierd sort query, how do you do it? (sort by ip proximity guess)
currently i have a table with an ip coloumn in text 123.45.67.89 though it shouldn't be a big deal to convert it to binary, but was wondering if there is an easy way to sort by closeness to a given ip address? ie records that come out sorted as same class sub-c same class c same class c same class b same class a other addresses other addresses the only thing i can think of at this point is a really long order by if same c,if same b,if same a -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Odd thing
We're trying to set up some scripts that will run mysqldump to create export files for each table in a database that can be downloaded to a test server and reloaded. What's weird is that the files are created with the ? character as the trailing character in the file name and we can't open them to do anything with the files. 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]
Additional info.
The server (our development test server) is running Red Hat 8.0 and Mysql 4.1. The remote test server to which we're trying to transfer data is running Red Hat 7.3 and Mysql 4.013. 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: Odd thing
[snip] We're trying to set up some scripts that will run mysqldump to create export files for each table in a database that can be downloaded to a test server and reloaded. What's weird is that the files are created with the ? character as the trailing character in the file name and we can't open them to do anything with the files. [/snip] How are your creating them? Script language? Jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Odd thing
Just creating shell scripts with one line creating each file. This is our first attempt to see how working with the smaller files performs while transferring data between sites. -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 9:43 AM To: Christensen, Dave; [EMAIL PROTECTED] Cc: Dickey, Dallas Subject: RE: Odd thing [snip] We're trying to set up some scripts that will run mysqldump to create export files for each table in a database that can be downloaded to a test server and reloaded. What's weird is that the files are created with the ? character as the trailing character in the file name and we can't open them to do anything with the files. [/snip] How are your creating them? Script language? Jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL question
Jake Johnson wrote: Nice approach Bruce, but I too won't have any problems with your case because I am grouping by sku and stock in the sub-query. You're right; you do avoid the problem with the specific sample data I gave you. Sorry about that! But, there are still potential problems because of the comparison of a single concatenated value instead of the separate SKU and STOCK values. In practice, they might not arise because of the formatting of the date/time field. Your subquery does yield, in effect, a good table of maximum date/times for each SKU/STOCK combination. But, you can get a false hit in it with the comparison if a dt_tm value isn't maximum for some stock, but there are other values dt_tm1 and stock1 such that concat(dt_tm,stock) = concat(dt_tm1, stock1) where dt_tm1 *is* maximum for stock1. That was very abstract; I'll supply some more data, with bogus date/time values to illustrate my point. dt_tm, stock, sku, qty A, BB, C, 1 AB, BB, C, 2 -- note that AB is the max(dt_tm) for stock=BB,SKU=C; A is not the max AB, B, C, 3-- note that AB is the max(dt_tm) for stock=B,SKU=C Subselect yields AB, BB, C and AB, B, C; concatenated they are ABBBC and ABBC Select compares ABBC to the above and it matches, so BB, C, 1 is returned (incorrectly) Select compares ABBBC to the above and it matches, so BB, C, 2 is returned Select compares ABBC to the above and it matches, so B, C, 3 is returned Bruce Feist On Thu, 19 Jun 2003, Bruce Feist wrote: Jake Johnson wrote: This is one quick way to get the newest records of a group if you are grouping by the sku and stock. select stock, sku, qty from table where concat(dt_tm,stock,sku) IN ( select concat(max(dt_tm), stock, sku) from table group by stock, sku ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: wierd sort query, how do you do it? (sort by ip proximity guess)
Hi, If you convert the IP address into integers, then do an xor (exclusive or), you could call the resultant value the approximate 'distance' between the values. (think of the IP address in binary, any identical bits will result in 0, any differing will be 1. This means that the most significant '1' will be the first dissimilar bit between the two IP addresses. This is really what you are after. However, including any lesser significant bits may well be OK for your application.) OK, so how in SQL You need to create, on the fly ipAddress1 ^ ipAddress2 (order does not matter) where they are both integers (mysql will use 64bit integer arithmetic). You just need to ORDER BY this value. Regards Matthew -Original Message- From: Ray [mailto:[EMAIL PROTECTED] Sent: 19 June 2003 15:33 To: [EMAIL PROTECTED] Subject: wierd sort query, how do you do it? (sort by ip proximity guess) currently i have a table with an ip coloumn in text 123.45.67.89 though it shouldn't be a big deal to convert it to binary, but was wondering if there is an easy way to sort by closeness to a given ip address? ie records that come out sorted as same class sub-c same class c same class c same class b same class a other addresses other addresses the only thing i can think of at this point is a really long order by if same c,if same b,if same 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]
RE: Odd thing
[snip] Just creating shell scripts with one line creating each file. This is our first attempt to see how working with the smaller files performs while transferring data between sites. [/snip] It sounds as if (based on past experience) that the filename is being created with an additional character at the end, such as a newline character or carriage return character. Since the character is invisible you see a question mark. Can we see a snip of your shell script? Jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Odd thing
Hi, are you using MSDOS/Windows anywhere here - it might be a trailing ^M which Windows uses before ^J as the end of line character. Regards Matthew -Original Message- From: Christensen, Dave [mailto:[EMAIL PROTECTED] Sent: 19 June 2003 15:50 To: 'Jay Blanchard'; '[EMAIL PROTECTED]' Cc: Dickey, Dallas Subject: RE: Odd thing Just creating shell scripts with one line creating each file. This is our first attempt to see how working with the smaller files performs while transferring data between sites. -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 9:43 AM To: Christensen, Dave; [EMAIL PROTECTED] Cc: Dickey, Dallas Subject: RE: Odd thing [snip] We're trying to set up some scripts that will run mysqldump to create export files for each table in a database that can be downloaded to a test server and reloaded. What's weird is that the files are created with the ? character as the trailing character in the file name and we can't open them to do anything with the files. [/snip] How are your creating them? Script language? Jay -- 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: Odd thing
mysqldump AgentAdminDB activitylookup -u UserName -pPassword //var/data/mysql/activitylookup.sql mysqldump AgentAdminDB agencies -u UserName -pPassword //var/data/mysql/agencies.sql mysqldump AgentAdminDB agentdocuments -u UserName -pPassword //var/data/mysql/agentdocuments.sql mysqldump AgentAdminDB agentdownlink -u UserName -pPassword //var/data/mysql/agentdownlink.sql mysqldump AgentAdminDB agentnotes -u UserName -pPassword //var/data/mysql/agentnotes.sql mysqldump AgentAdminDB agentpages -u UserName -pPassword //var/data/mysql/agentpages.sql -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 9:52 AM To: Christensen, Dave; [EMAIL PROTECTED] Cc: Dickey, Dallas Subject: RE: Odd thing [snip] Just creating shell scripts with one line creating each file. This is our first attempt to see how working with the smaller files performs while transferring data between sites. [/snip] It sounds as if (based on past experience) that the filename is being created with an additional character at the end, such as a newline character or carriage return character. Since the character is invisible you see a question mark. Can we see a snip of your shell script? Jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Odd thing
Delphi is being used to create a file with a mysqldump command per table -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 9:43 AM To: Christensen, Dave; [EMAIL PROTECTED] Cc: Dickey, Dallas Subject: RE: Odd thing [snip] We're trying to set up some scripts that will run mysqldump to create export files for each table in a database that can be downloaded to a test server and reloaded. What's weird is that the files are created with the ? character as the trailing character in the file name and we can't open them to do anything with the files. [/snip] How are your creating them? Script language? Jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
suggestions - server options/mysql variables
Hi! Does anyone know any mysql options I can change in the my.ini file or mysql variables to increase the speed of select statements? About database: -uses only select statements for queries -no transactions -if database becomes corrupt in anyway, not a big deal I can recreate it -it is a small database, I do not need recovery, raid or any other backup mechanism -queries generally return very little data I will test any suggestions people have for me. Thanks Eric _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Odd thing
[snip] mysqldump AgentAdminDB activitylookup -u UserName -pPassword //var/data/mysql/activitylookup.sql mysqldump AgentAdminDB agencies -u UserName -pPassword //var/data/mysql/agencies.sql mysqldump AgentAdminDB agentdocuments -u UserName -pPassword //var/data/mysql/agentdocuments.sql mysqldump AgentAdminDB agentdownlink -u UserName -pPassword //var/data/mysql/agentdownlink.sql mysqldump AgentAdminDB agentnotes -u UserName -pPassword //var/data/mysql/agentnotes.sql mysqldump AgentAdminDB agentpages -u UserName -pPassword //var/data/mysql/agentpages.sql [/snip] Can we see the rest of the script? Jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: suggestions - server options/mysql variables
If you have queries that are only selects, in small tables, with few rows, you should have very fast performance as is. Are your queries properly indexed? What hardware are you running on? What do these queries look like? Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Derick Smith [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 9:08 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: suggestions - server options/mysql variables Hi! Does anyone know any mysql options I can change in the my.ini file or mysql variables to increase the speed of select statements? About database: -uses only select statements for queries -no transactions -if database becomes corrupt in anyway, not a big deal I can recreate it -it is a small database, I do not need recovery, raid or any other backup mechanism -queries generally return very little data I will test any suggestions people have for me. Thanks Eric _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Odd thing
Yes, we'd be creating the file on a Windows box, then transferring the file to either a Samba share or using FTP to the LINUX machine. -Original Message- From: Matthew Smith [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 9:54 AM To: Christensen, Dave; 'Jay Blanchard'; [EMAIL PROTECTED] Cc: Dickey, Dallas Subject: RE: Odd thing Hi, are you using MSDOS/Windows anywhere here - it might be a trailing ^M which Windows uses before ^J as the end of line character. Regards Matthew -Original Message- From: Christensen, Dave [mailto:[EMAIL PROTECTED] Sent: 19 June 2003 15:50 To: 'Jay Blanchard'; '[EMAIL PROTECTED]' Cc: Dickey, Dallas Subject: RE: Odd thing Just creating shell scripts with one line creating each file. This is our first attempt to see how working with the smaller files performs while transferring data between sites. -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 9:43 AM To: Christensen, Dave; [EMAIL PROTECTED] Cc: Dickey, Dallas Subject: RE: Odd thing [snip] We're trying to set up some scripts that will run mysqldump to create export files for each table in a database that can be downloaded to a test server and reloaded. What's weird is that the files are created with the ? character as the trailing character in the file name and we can't open them to do anything with the files. [/snip] How are your creating them? Script language? Jay -- 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]
selecting PRIMARY KEY when there is no unique value
Hi, In a docuement such as Invoice Form, we have a header and a couple of records for the detail. In header table, Invoice# can be the PRIMARY KEY but in detail table, Invoice# is not unique. I think there are two solutions to choose a Primary Key (in MyISAM type) : 1) Adding an id field ( auto_increment ) and choose it as PRIMARY KEY in this case we have to add another index on Invoice# for making relation with the header table 2) There is another field in detail table with timestamp type for keeping the last change on the record. I want to select ( Invoice# + myTimestamp ) for PRIMARY KEY. in this case I don't need to add a new fields ( id ) and another index ( on Invoice# ) to the table. which one do you prefer and usually use? thank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: selecting PRIMARY KEY when there is no unique value
When I last did an invoice-type project, he had the header table with an invoice# as PRIMARY KEY, then we had an invoice_line table with a rowid PRIMARY KEY as auto-increment and a invoice# with a non-unique key INDEX. This worked fine for our purposes, so I would probably go with option #1 I guess. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Mojtaba Faridzad [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 9:16 AM To: [EMAIL PROTECTED] Subject: selecting PRIMARY KEY when there is no unique value Hi, In a docuement such as Invoice Form, we have a header and a couple of records for the detail. In header table, Invoice# can be the PRIMARY KEY but in detail table, Invoice# is not unique. I think there are two solutions to choose a Primary Key (in MyISAM type) : 1) Adding an id field ( auto_increment ) and choose it as PRIMARY KEY in this case we have to add another index on Invoice# for making relation with the header table 2) There is another field in detail table with timestamp type for keeping the last change on the record. I want to select ( Invoice# + myTimestamp ) for PRIMARY KEY. in this case I don't need to add a new fields ( id ) and another index ( on Invoice# ) to the table. which one do you prefer and usually use? thank -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: wierd sort query, how do you do it? (sort by ip proximity guess)
great! that was the trick i was looking for. thank you. i had the feeling i was doing it the hard way :-) On Thursday 19 June 2003 09:51, you wrote: Hi, If you convert the IP address into integers, then do an xor (exclusive or), you could call the resultant value the approximate 'distance' between the values. (think of the IP address in binary, any identical bits will result in 0, any differing will be 1. This means that the most significant '1' will be the first dissimilar bit between the two IP addresses. This is really what you are after. However, including any lesser significant bits may well be OK for your application.) OK, so how in SQL You need to create, on the fly ipAddress1 ^ ipAddress2 (order does not matter) where they are both integers (mysql will use 64bit integer arithmetic). You just need to ORDER BY this value. Regards Matthew -Original Message- From: Ray [mailto:[EMAIL PROTECTED] Sent: 19 June 2003 15:33 To: [EMAIL PROTECTED] Subject: wierd sort query, how do you do it? (sort by ip proximity guess) currently i have a table with an ip coloumn in text 123.45.67.89 though it shouldn't be a big deal to convert it to binary, but was wondering if there is an easy way to sort by closeness to a given ip address? ie records that come out sorted as same class sub-c same class c same class c same class b same class a other addresses other addresses the only thing i can think of at this point is a really long order by if same c,if same b,if same a -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: selecting PRIMARY KEY when there is no unique value
Mojtaba Faridzad wrote: In a docuement such as Invoice Form, we have a header and a couple of records for the detail. In header table, Invoice# can be the PRIMARY KEY but in detail table, Invoice# is not unique. I think there are two solutions to choose a Primary Key (in MyISAM type) : 1) Adding an id field ( auto_increment ) and choose it as PRIMARY KEY in this case we have to add another index on Invoice# for making relation with the header table 2) There is another field in detail table with timestamp type for keeping the last change on the record. I want to select ( Invoice# + myTimestamp ) for PRIMARY KEY. in this case I don't need to add a new fields ( id ) and another index ( on Invoice# ) to the table. which one do you prefer and usually use? My rule of thumb is to use a composite key such as you suggest in (2) in cases where the row doesn't really represent a distinct business entity, but is only used as part of a more complex one. Your example of multiple details within an invoice form falls into this category. If the row is likely to be used on its own, I'm more likely to introduce a new field as a key, in order to make it more convenient to access it directly. In other words, the trade-off is in simplicity of database design (use the existing fields) versus simplicity and efficiency in doing single-row look-ups. The latter is only significant if you expect to be retrieving the row on its own. Bruce Feist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: wierd sort query, how do you do it? (sort by ip proximity guess)
hehe, guess there is 1 problem with this solution. mysql doesn't do xor. but found a ref saying a XOR b is mathematically equal to (a AND (NOT b)) OR ((NOT a) and b) On Thursday 19 June 2003 09:51, Matthew Smith wrote: Hi, If you convert the IP address into integers, then do an xor (exclusive or), you could call the resultant value the approximate 'distance' between the values. (think of the IP address in binary, any identical bits will result in 0, any differing will be 1. This means that the most significant '1' will be the first dissimilar bit between the two IP addresses. This is really what you are after. However, including any lesser significant bits may well be OK for your application.) OK, so how in SQL You need to create, on the fly ipAddress1 ^ ipAddress2 (order does not matter) where they are both integers (mysql will use 64bit integer arithmetic). You just need to ORDER BY this value. Regards Matthew -Original Message- From: Ray [mailto:[EMAIL PROTECTED] Sent: 19 June 2003 15:33 To: [EMAIL PROTECTED] Subject: wierd sort query, how do you do it? (sort by ip proximity guess) currently i have a table with an ip coloumn in text 123.45.67.89 though it shouldn't be a big deal to convert it to binary, but was wondering if there is an easy way to sort by closeness to a given ip address? ie records that come out sorted as same class sub-c same class c same class c same class b same class a other addresses other addresses the only thing i can think of at this point is a really long order by if same c,if same b,if same a -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: selecting PRIMARY KEY when there is no unique value
Mojtaba Faridzad wrote: Hi, In a docuement such as Invoice Form, we have a header and a couple of records for the detail. In header table, Invoice# can be the PRIMARY KEY but in detail table, Invoice# is not unique. I think there are two solutions to choose a Primary Key (in MyISAM type) : 1) Adding an id field ( auto_increment ) and choose it as PRIMARY KEY in this case we have to add another index on Invoice# for making relation with the header table 2) There is another field in detail table with timestamp type for keeping the last change on the record. I want to select ( Invoice# + myTimestamp ) for PRIMARY KEY. in this case I don't need to add a new fields ( id ) and another index ( on Invoice# ) to the table. which one do you prefer and usually use? I always use a id field with auto increment. It helps for normalization, and makes the code I use to deal with information very generic, grated I've abstracted the code to the point that it has no clue what it's doing, it just gets it done. In my case, I know that the foreign key is always one column and I can short cut the lookup to create the joins, it's it's an index, it's a foreign key, it's this table and index. If the foreign key's index could be anything then It's it's an index, it's a foreign key, it's this table and index, the index are these columns and the code to generate the join is 'interesting'. The other issue is that while your timestamp should be unique when combined with an invoice by whatever rules your dealing with, there's nothing that says it will be in the real world (the one where crazy things happen). By having the id field I never, ever deal with it myself, MySQL always puts the number in there for me and I know it's going to be unique unless MySQL does something it should not do. The id field just takes the guesswork, mess and headaches out of the code (well not *all* of them, but enough) and with the size of disk space these days the extra space isn't much. -- Michael Conlen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't drop a database
Hi I really don't have an answer but would question whether MySQL will allow zero db's with it's own grant tables. Usually there is a test db, at least after an install. Why not try adding a database and then try to drop clone_updater??. Mike - Original Message - From: Leo Genyuk [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: June 19, 2003 10:06 AM Subject: Can't drop a database Hello. I am running MySQL database on Sun Solaris 9. # mysql -V mysql Ver 11.18 Distrib 3.23.54, for sun-solaris2.8 (sparc) I am having a problem dropping a database. All the tables in the database have been dropped successfully, but the database does not want to go away. To be more specific, when I execute: drop database clone_updater; query runs without errors, but database is still there. mysql show databases; +--+ | Database| +--+ | clone_updater| | mysql | +--+ 51 rows in set (0.01 sec) mysql drop database clone_updater; Query OK, 0 rows affected (0.00 sec) mysql show databases; +--+ | Database| +--+ | clone_updater| | mysql | +--+ 51 rows in set (0.01 sec) Restarting mysql had no effect on this issue. Can anyone please help? Thank you in advance, Leo. -- 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: Odd thing
I've also tried creating the script manually (script follows) and all that's being created is a text header with nothing else in the file. At least in these instances we can use the text files. Just be nice if they were valid... :-) --- Here's the script mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB MissedPages -uroot -ppano4577 MissedPages.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB UCS_APP_INFO -uroot -ppano4577 UCS_APP_INFO.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB UCS_AUDIT -uroot -ppano4577 UCS_AUDIT.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB UCS_COMPS -uroot -ppano4577 UCS_COMPS.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB UCS_FORMS -uroot -ppano4577 UCS_FORMS.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB UCS_HISTORY -uroot -ppano4577 UCS_HISTORY.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB UCS_LOGIN_TRACE -uroot -ppano4577 UCS_LOGIN_TRACE.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB UCS_PACCESS -uroot -ppano4577 UCS_PACCESS.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB UCS_PROFILES -uroot -ppano4577 UCS_PROFILES.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB UCS_UACCESS -uroot -ppano4577 UCS_UACCESS.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB UCS_USERINFO -uroot -ppano4577 UCS_USERINFO.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB UCS_USERS -uroot -ppano4577 UCS_USERS.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB activitylookup -uroot -ppano4577 activitylookup.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB agencies -uroot -ppano4577 agencies.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB agentdocuments -uroot -ppano4577 agentdocuments.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB agentdownlink -uroot -ppano4577 agentdownlink.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB agentnotes -uroot -ppano4577 agentnotes.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB agentpages -uroot -ppano4577 agentpages.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB agents -uroot -ppano4577 agents.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB agentsdba -uroot -ppano4577 agentsdba.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB airlinelookup -uroot -ppano4577 airlinelookup.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB airportlookup -uroot -ppano4577 airportlookup.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB appointments -uroot -ppano4577 appointments.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB appointmentstatuslookup -uroot -ppano4577 appointmentstatuslookup.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB appointmenttypelookup -uroot -ppano4577 appointmenttypelookup.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB appointmentuplink -uroot -ppano4577 appointmentuplink.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB appversioninfo -uroot -ppano4577 appversioninfo.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB callstatuslookup -uroot -ppano4577 callstatuslookup.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB carriercontacts -uroot -ppano4577 carriercontacts.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB carriernotes -uroot -ppano4577 carriernotes.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB carriers -uroot -ppano4577 carriers.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB contactnotes -uroot -ppano4577 contactnotes.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB contacts -uroot -ppano4577 contacts.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB diary -uroot -ppano4577 diary.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB documenttypelookup -uroot -ppano4577 documenttypelookup.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB followupactionlookup -uroot -ppano4577
Help me!!!
Hi, I need some link to a spanish mysql list, would you ...?
Re: MySQL Dual Master Replication
On Thu, Jun 19, 2003 at 05:04:06PM +1000, Solomon Sokolovsky wrote: So then no one is the master?? Please confirm! Both are just slaves??? They're both masters and both slaves. -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo MySQL Geek Desk: (408) 349-7878 Cell: (408) 685-5936 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: selecting PRIMARY KEY when there is no unique value
Thanks a lot. after all of these helpful responses, I will add a new field for Primary key. Thanks again - Original Message - From: Michael Conlen [EMAIL PROTECTED] To: Mojtaba Faridzad [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, June 19, 2003 11:43 AM Subject: Re: selecting PRIMARY KEY when there is no unique value Mojtaba Faridzad wrote: Hi, In a docuement such as Invoice Form, we have a header and a couple of records for the detail. In header table, Invoice# can be the PRIMARY KEY but in detail table, Invoice# is not unique. I think there are two solutions to choose a Primary Key (in MyISAM type) : 1) Adding an id field ( auto_increment ) and choose it as PRIMARY KEY in this case we have to add another index on Invoice# for making relation with the header table 2) There is another field in detail table with timestamp type for keeping the last change on the record. I want to select ( Invoice# + myTimestamp ) for PRIMARY KEY. in this case I don't need to add a new fields ( id ) and another index ( on Invoice# ) to the table. which one do you prefer and usually use? I always use a id field with auto increment. It helps for normalization, and makes the code I use to deal with information very generic, grated I've abstracted the code to the point that it has no clue what it's doing, it just gets it done. In my case, I know that the foreign key is always one column and I can short cut the lookup to create the joins, it's it's an index, it's a foreign key, it's this table and index. If the foreign key's index could be anything then It's it's an index, it's a foreign key, it's this table and index, the index are these columns and the code to generate the join is 'interesting'. The other issue is that while your timestamp should be unique when combined with an invoice by whatever rules your dealing with, there's nothing that says it will be in the real world (the one where crazy things happen). By having the id field I never, ever deal with it myself, MySQL always puts the number in there for me and I know it's going to be unique unless MySQL does something it should not do. The id field just takes the guesswork, mess and headaches out of the code (well not *all* of them, but enough) and with the size of disk space these days the extra space isn't much. -- Michael Conlen -- 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: Help me!!!
mailto:[EMAIL PROTECTED] This is what I found at lists.mysql.com Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Luis Enrique Bauzá Peña [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 10:00 AM To: Lista Mysql Inglés Subject: Help me!!! Hi, I need some link to a spanish mysql list, would you ...? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help me!!!
Luis Enrique Bauzá Peña, [EMAIL PROTECTED] David M Friscia friscia.rootsweb.com http://friscia.rootsweb.com [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -Original Message- From: Luis Enrique Bauzá Peña [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 9:00 AM To: Lista Mysql Inglés Subject: Help me!!! Hi, I need some link to a spanish mysql list, would you ...? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help me!!!
mailto:[EMAIL PROTECTED] - Original Message - From: Luis Enrique Bauzá Peña [EMAIL PROTECTED] To: Lista Mysql Inglés [EMAIL PROTECTED] Sent: Thursday, June 19, 2003 11:59 AM Subject: Help me!!! Hi, I need some link to a spanish mysql list, would you ...? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help me!!!
Hola, Puedes encontrar una lista Mysql Espanol a http://lists.mysql.com/list.php?list=mysql-es#b . Yo pienso que ellos pueden ayudarte ma's que nosotros podemos. You can find a spanish MySQL list at http://lists.mysql.com/list.php?list=mysql-es#b . I think they can help you more than we can. -- Michael Conlen Luis Enrique Bauzá Peña wrote: Hi, I need some link to a spanish mysql list, would you ...? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can we crypt passwords on MySQL
At a certain time, now past [Jun.18.2003-11:55:18PM +0200], [EMAIL PROTECTED] spake thusly: Hi all, Is it possible to crypt the passwords on MySQL. If yes, how does it work, and how is it to be implemented. Any link would be great. Passwords from/for what? Are these MySQL user passwords?...or passwords from an application where the userinfo is stored in MySQL? The recommended functions are different for each. If you use GRANT statements for adding MySQL users, it removes the need to use PASSWORD(). http://www.mysql.com/doc/en/GRANT.html http://www.mysql.com/doc/en/Passwords.html If you're talking about a app that looks to MySQL for user credentials use either md5() or sha1(). http://www.mysql.com/doc/en/Miscellaneous_functions.html ~elh -- Eric L. Howard e l h @ o u t r e a c h n e t w o r k s . c o m www.OutreachNetworks.com313.297.9900 JabberID: [EMAIL PROTECTED] Advocate of the Theocratic Rule -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: worst result in killing an optimize table query
On Thu, Jun 19, 2003 at 02:24:52AM +0530, gamin wrote: Hi, What would happen to my_table (~70MB of indexes) and its indexes if someone kills a an optimize table query - 'OPTIMIZE TABLE my_table' . Im wondering if i should provide a cancel button in my application during the optimization period. It is generally more user friendly to provide one, unless cancelling means having to repair the table or some similiar problem. MySQL will discard the tempporary files and leave your data unchanged. At least that's what I've seen happen. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 16 days, processed 531,850,131 queries (375/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: selecting PRIMARY KEY when there is no unique value
Hi Paul, (good to hear from you!) We use (invoice number+date) number as the primary key for the header and (invoice number+body item number+date) in the body of the invoice. We do not allow the same invoice number to be reused on the same day. However they can eventually rollover (ours is a multi location systems with a certain sequence reserved for each location). Body item number a generated sequence. I agree with paul TIMESTAMP is not a cool thing to use. Generating the sequence (there are many methods of doing this, see the MySQL Cookbook by Paul Dubois for some examples including SQL variables or an autoincrement key, program variables, etc) gives a natural and consistant order to the invoice. I hope this helps, Ken Don't use 2). There is no guarantee a TIMESTAMP value will be unique among records with the same invoice number. That means invoice number + timestamp wil not form a PRIMARY KEY. I'd use 1), possibly combining the invoice number with the item id as a composite key. -- 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: Odd thing
When I run this script file, I receive errors like this: [EMAIL PROTECTED] work]# ./BackupByTable.bat mysqldump: Got error: 1146: Table 'AgentAdminDB.MissedPages' doesn't exist when doing LOCK TABLES -Original Message- From: Christensen, Dave [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 10:58 AM To: 'Jay Blanchard'; Dickey, Dallas; Christensen, Dave; '[EMAIL PROTECTED]' Subject: RE: Odd thing I've also tried creating the script manually (script follows) and all that's being created is a text header with nothing else in the file. At least in these instances we can use the text files. Just be nice if they were valid... :-) --- Here's the script mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB MissedPages -uroot -ppano4577 MissedPages.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB UCS_APP_INFO -uroot -ppano4577 UCS_APP_INFO.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB UCS_AUDIT -uroot -ppano4577 UCS_AUDIT.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB UCS_COMPS -uroot -ppano4577 UCS_COMPS.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB UCS_FORMS -uroot -ppano4577 UCS_FORMS.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB UCS_HISTORY -uroot -ppano4577 UCS_HISTORY.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB UCS_LOGIN_TRACE -uroot -ppano4577 UCS_LOGIN_TRACE.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB UCS_PACCESS -uroot -ppano4577 UCS_PACCESS.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB UCS_PROFILES -uroot -ppano4577 UCS_PROFILES.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB UCS_UACCESS -uroot -ppano4577 UCS_UACCESS.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB UCS_USERINFO -uroot -ppano4577 UCS_USERINFO.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB UCS_USERS -uroot -ppano4577 UCS_USERS.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB activitylookup -uroot -ppano4577 activitylookup.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB agencies -uroot -ppano4577 agencies.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB agentdocuments -uroot -ppano4577 agentdocuments.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB agentdownlink -uroot -ppano4577 agentdownlink.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB agentnotes -uroot -ppano4577 agentnotes.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB agentpages -uroot -ppano4577 agentpages.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB agents -uroot -ppano4577 agents.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB agentsdba -uroot -ppano4577 agentsdba.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB airlinelookup -uroot -ppano4577 airlinelookup.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB airportlookup -uroot -ppano4577 airportlookup.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB appointments -uroot -ppano4577 appointments.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB appointmentstatuslookup -uroot -ppano4577 appointmentstatuslookup.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB appointmenttypelookup -uroot -ppano4577 appointmenttypelookup.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB appointmentuplink -uroot -ppano4577 appointmentuplink.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB appversioninfo -uroot -ppano4577 appversioninfo.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB callstatuslookup -uroot -ppano4577 callstatuslookup.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB carriercontacts -uroot -ppano4577 carriercontacts.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB carriernotes -uroot -ppano4577 carriernotes.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB carriers -uroot -ppano4577 carriers.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB contactnotes -uroot -ppano4577 contactnotes.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB contacts -uroot
RE: Odd thing
Oops! Sorry. Deleted that 'temp' table and didn't remove it from my script file. -Original Message- From: Christensen, Dave [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 11:34 AM To: 'Jay Blanchard'; Dickey, Dallas; '[EMAIL PROTECTED]' Subject: RE: Odd thing Importance: High When I run this script file, I receive errors like this: [EMAIL PROTECTED] work]# ./BackupByTable.bat mysqldump: Got error: 1146: Table 'AgentAdminDB.MissedPages' doesn't exist when doing LOCK TABLES -Original Message- From: Christensen, Dave [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 10:58 AM To: 'Jay Blanchard'; Dickey, Dallas; Christensen, Dave; '[EMAIL PROTECTED]' Subject: RE: Odd thing I've also tried creating the script manually (script follows) and all that's being created is a text header with nothing else in the file. At least in these instances we can use the text files. Just be nice if they were valid... :-) --- Here's the script mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB MissedPages -uroot -ppano4577 MissedPages.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB UCS_APP_INFO -uroot -ppano4577 UCS_APP_INFO.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB UCS_AUDIT -uroot -ppano4577 UCS_AUDIT.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB UCS_COMPS -uroot -ppano4577 UCS_COMPS.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB UCS_FORMS -uroot -ppano4577 UCS_FORMS.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB UCS_HISTORY -uroot -ppano4577 UCS_HISTORY.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB UCS_LOGIN_TRACE -uroot -ppano4577 UCS_LOGIN_TRACE.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB UCS_PACCESS -uroot -ppano4577 UCS_PACCESS.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB UCS_PROFILES -uroot -ppano4577 UCS_PROFILES.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB UCS_UACCESS -uroot -ppano4577 UCS_UACCESS.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB UCS_USERINFO -uroot -ppano4577 UCS_USERINFO.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB UCS_USERS -uroot -ppano4577 UCS_USERS.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB activitylookup -uroot -ppano4577 activitylookup.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB agencies -uroot -ppano4577 agencies.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB agentdocuments -uroot -ppano4577 agentdocuments.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB agentdownlink -uroot -ppano4577 agentdownlink.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB agentnotes -uroot -ppano4577 agentnotes.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB agentpages -uroot -ppano4577 agentpages.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB agents -uroot -ppano4577 agents.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB agentsdba -uroot -ppano4577 agentsdba.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB airlinelookup -uroot -ppano4577 airlinelookup.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB airportlookup -uroot -ppano4577 airportlookup.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB appointments -uroot -ppano4577 appointments.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB appointmentstatuslookup -uroot -ppano4577 appointmentstatuslookup.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB appointmenttypelookup -uroot -ppano4577 appointmenttypelookup.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB appointmentuplink -uroot -ppano4577 appointmentuplink.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB appversioninfo -uroot -ppano4577 appversioninfo.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB callstatuslookup -uroot -ppano4577 callstatuslookup.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB carriercontacts -uroot -ppano4577 carriercontacts.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB carriernotes -uroot -ppano4577 carriernotes.sql mysqldump --add-drop-table --compatible=mysql323
Re: wierd sort query, how do you do it? (sort by ip proximity guess)
Remember that this is a binary XOR, not a logical XOR. Mysql does have a binary XOR operator, the '^' operator. So 'ipAddress1 ^ ipAddress2' is the binary XOR between the two values. The binary AND is the '' operator and the binary OR is the '|'. Just don't confuse them with the AND operator and/or the OR operator. Got all that? =) Nick Elliott - Original Message - From: Ray [EMAIL PROTECTED] To: Matthew Smith [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, June 19, 2003 11:40 AM Subject: Re: wierd sort query, how do you do it? (sort by ip proximity guess) hehe, guess there is 1 problem with this solution. mysql doesn't do xor. but found a ref saying a XOR b is mathematically equal to (a AND (NOT b)) OR ((NOT a) and b) On Thursday 19 June 2003 09:51, Matthew Smith wrote: Hi, If you convert the IP address into integers, then do an xor (exclusive or), you could call the resultant value the approximate 'distance' between the values. (think of the IP address in binary, any identical bits will result in 0, any differing will be 1. This means that the most significant '1' will be the first dissimilar bit between the two IP addresses. This is really what you are after. However, including any lesser significant bits may well be OK for your application.) OK, so how in SQL You need to create, on the fly ipAddress1 ^ ipAddress2 (order does not matter) where they are both integers (mysql will use 64bit integer arithmetic). You just need to ORDER BY this value. Regards Matthew -Original Message- From: Ray [mailto:[EMAIL PROTECTED] Sent: 19 June 2003 15:33 To: [EMAIL PROTECTED] Subject: wierd sort query, how do you do it? (sort by ip proximity guess) currently i have a table with an ip coloumn in text 123.45.67.89 though it shouldn't be a big deal to convert it to binary, but was wondering if there is an easy way to sort by closeness to a given ip address? ie records that come out sorted as same class sub-c same class c same class c same class b same class a other addresses other addresses the only thing i can think of at this point is a really long order by if same c,if same b,if same 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]
Re: Odd thing
Christensen, Dave wrote: Oops! Sorry. Deleted that 'temp' table and didn't remove it from my script file. -Original Message- From: Christensen, Dave [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 11:34 AM To: 'Jay Blanchard'; Dickey, Dallas; '[EMAIL PROTECTED]' Subject: RE: Odd thing Importance: High When I run this script file, I receive errors like this: [EMAIL PROTECTED] work]# ./BackupByTable.bat mysqldump: Got error: 1146: Table 'AgentAdminDB.MissedPages' doesn't exist when doing LOCK TABLES -Original Message- From: Christensen, Dave [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 10:58 AM To: 'Jay Blanchard'; Dickey, Dallas; Christensen, Dave; '[EMAIL PROTECTED]' Subject: RE: Odd thing I've also tried creating the script manually (script follows) and all that's being created is a text header with nothing else in the file. At least in these instances we can use the text files. Just be nice if they were valid... :-) --- Here's the script mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB MissedPages -uroot -ppano4577 MissedPages.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB UCS_APP_INFO -uroot -ppano4577 UCS_APP_INFO.sql You have options after the tables. All options should precede the database and tables. mysqldump --add-drop-table --compatible-mysql323 --disable-keys -uroot -ppano4577 AgentAdminDB MissedPages MissedPages.sql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Access denied for user: '@localhost' to database 'mysql'
I ran a command which was part of the installation process for some php accounting software. update user set host='%' where host='localhost' and user='root'; Figured it just let root log in from anywhere. Dunno if that is what caused the problem or not. But now root can't access anything in mysql anymore. When I do \s it shows the user as '[EMAIL PROTECTED]'. But when I try to look at tables in mysql it thinks the user is only '@localhost'. The error I get is: Access denied for user: '@localhost' to database 'mysql' Anyone know wassup? Thanks. Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: suggestions - server options/mysql variables
Hi! All the select statements have indexes on appropriate fields. Mysql appears to run fast, I was just wondering if there was anyway to make it faster. It is currently running on Windows, I have also ported it to HP-UX 11. The queries are of the type : SELECT * FROM textl WHERE parsetext = and filter = GROUP BY code. I think the query code is optimized, I was wondering is there were any settings I could change in mysql to speed it up? or any other tricks to increase speed? I tried setting set-variable = key_buffer_size=64M, but that did not appear to make a big difference. Thanks Eric From: Mike Hillyer [EMAIL PROTECTED] To: Derick Smith [EMAIL PROTECTED], [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: RE: suggestions - server options/mysql variables Date: Thu, 19 Jun 2003 09:13:33 -0600 If you have queries that are only selects, in small tables, with few rows, you should have very fast performance as is. Are your queries properly indexed? What hardware are you running on? What do these queries look like? Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Derick Smith [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 9:08 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: suggestions - server options/mysql variables Hi! Does anyone know any mysql options I can change in the my.ini file or mysql variables to increase the speed of select statements? About database: -uses only select statements for queries -no transactions -if database becomes corrupt in anyway, not a big deal I can recreate it -it is a small database, I do not need recovery, raid or any other backup mechanism -queries generally return very little data I will test any suggestions people have for me. Thanks Eric _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
C API Query Semantics
Using the C API I have written a program that calls mysql_query() on a table with values I know to be in the table. It returns fine and I then call mysql_store_result. The problem I am having is figuring out how to dump the contents of that query to the screen (printf for example). I have tried mysql_fetch_rows and a few others. What am trying to do is see the data on the terminal once the c program executes. Any suggestions? Sean Mac Millan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mac osx
Hi, I have installed mysql-standard 4.0.13 for Mac OSX10.2. I cannot get the mysql application to work-the files are white and cannot be launched. Any ideas what to do? Regards JB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
C API - mysql_free_result
Stats: MySQL 4.0.13, under Windows98, using the C API with lcc-win32. Is there any way to determine if mysql_free_result() has been called on a result set pointer? Occasionally I recycle the pointer (use it, free it, use it again, free it again, etc.) and if my code calls mysql_free_result() on a result set pointer that was already freed, an exception is raised and my code gracefully crashes out. -- Adam Lawrence Sustaining Engineering Tectrol Inc. -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL compilation
Hello, Compiled MySQL with ./configure --prefix=/usr/local/mysql \ --localstatedir=/usr/db/mysql \ CFLAGS=-O3 \ CXX=gcc \ CXXFLAGS=-O3 -felide-constructors -fno-exceptions -fno-rtti --enable-assembler \ --with-openssl gmake gmake install Forgot to include --with-innodb \ Can I get around it by appropriate settings in my.cnf or do I have to recompile? Thanks -- Joe --
Re: suggestions - server options/mysql variables
We don't know anything about you structures, your data, or your indicies. You should show them along with the output of EXPLAIN on your query. Derick Smith wrote: Hi! All the select statements have indexes on appropriate fields. Mysql appears to run fast, I was just wondering if there was anyway to make it faster. It is currently running on Windows, I have also ported it to HP-UX 11. The queries are of the type : SELECT * FROM textl WHERE parsetext = and filter = GROUP BY code. I think the query code is optimized, I was wondering is there were any settings I could change in mysql to speed it up? or any other tricks to increase speed? I tried setting set-variable = key_buffer_size=64M, but that did not appear to make a big difference. Thanks Eric From: Mike Hillyer [EMAIL PROTECTED] To: Derick Smith [EMAIL PROTECTED], [EMAIL PROTECTED],[EMAIL PROTECTED] Subject: RE: suggestions - server options/mysql variables Date: Thu, 19 Jun 2003 09:13:33 -0600 If you have queries that are only selects, in small tables, with few rows, you should have very fast performance as is. Are your queries properly indexed? What hardware are you running on? What do these queries look like? Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Derick Smith [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 9:08 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: suggestions - server options/mysql variables Hi! Does anyone know any mysql options I can change in the my.ini file or mysql variables to increase the speed of select statements? About database: -uses only select statements for queries -no transactions -if database becomes corrupt in anyway, not a big deal I can recreate it -it is a small database, I do not need recovery, raid or any other backup mechanism -queries generally return very little data I will test any suggestions people have for me. Thanks Eric _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: C API Query Semantics
Look at the source for the mysql client. It is a perfect example. Sean Macmillan wrote: Using the C API I have written a program that calls mysql_query() on a table with values I know to be in the table. It returns fine and I then call mysql_store_result. The problem I am having is figuring out how to dump the contents of that query to the screen (printf for example). I have tried mysql_fetch_rows and a few others. What am trying to do is see the data on the terminal once the c program executes. Any suggestions? Sean Mac Millan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: C API - mysql_free_result
At 13:36 -0400 6/19/03, Adam Lawrence wrote: Stats: MySQL 4.0.13, under Windows98, using the C API with lcc-win32. Is there any way to determine if mysql_free_result() has been called on a result set pointer? There's no API function for that, no. Occasionally I recycle the pointer (use it, free it, use it again, free it again, etc.) and if my code calls mysql_free_result() on a result set pointer that was already freed, an exception is raised and my code gracefully crashes out. -- Adam Lawrence Sustaining Engineering Tectrol Inc. -- -- 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: Can we crypt passwords on MySQL
Technically MD5/SHA1 are hashing algorithms and shouldn't be confused with encryption algorithms like DES, 3DES or AES. From the looks of the document link you gave us it seems that AES_ENCRYT is the way to go if you want to encrypt the data. So the command would look something like this: INSERT INTO t VALUES (1,AES_ENCRYPT(text,password)); So are you saying that the XXX_ENCRYPT function isn't to be used? -Original Message- From: Jocelyn Fournier [mailto:[EMAIL PROTECTED] Sent: Thursday, June 26, 2003 3:41 PM To: [EMAIL PROTECTED] Cc: 'Mysql' Subject: Re: Can we crypt passwords on MySQL It's not recommended because in MySQL 4.1, it returns a different result each time PASSWORD() is used, with the same input string. For MD5/SHA1 function, take a look here : http://www.mysql.com/doc/en/Miscellaneous_functions.html Jocelyn - Original Message - From: development [EMAIL PROTECTED] To: 'Jocelyn Fournier' [EMAIL PROTECTED] Cc: 'Mysql' [EMAIL PROTECTED] Sent: Thursday, June 19, 2003 12:38 AM Subject: RE: Can we crypt passwords on MySQL Jocelyn Why is that not recommended ? Do you have any links for how to encrypt the password with MD5 ? Freddie -Original Message- From: Jocelyn Fournier [mailto:[EMAIL PROTECTED] Sent: Freitag, 27. Juni 2003 00:19 To: [EMAIL PROTECTED]; Grégoire Dubois Cc: 'Mysql' Hi, Using PASSWORD function to crypt password is not recommended, it's mainly used for internal mysql password encryption. Prefer using MD5/SHA1 functions to encrypt a password. Regards, Jocelyn - Original Message - From: [EMAIL PROTECTED] To: Grégoire Dubois [EMAIL PROTECTED] Cc: 'Mysql' [EMAIL PROTECTED] Sent: Thursday, June 19, 2003 12:14 AM Subject: RE: Can we crypt passwords on MySQL There is a PASSWORD('your_clear_text_password_here') function you can use wherever you define a new password. See the manual for more. Lian -Original Message- From: Grégoire Dubois [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 12:55 AM Cc: 'Mysql' Subject: Can we crypt passwords on MySQL Hi all, Is it possible to crypt the passwords on MySQL. If yes, how does it work, and how is it to be implemented. Any link would be great. Thank you. Grégoire Dubois. -- 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] pc.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do we convert a well-structured XML file to its corresponding tables in a MySQL Database ?
Eldrid Rensburg wrote: How do we convert a well-structured XML file to its corresponding tables in a MySQL Database ? With your own Perl/Python/C code ... And how do we normalize this well-structured XML file prior to conversion ? By basically tracking how deep you are in the nesting. In Python, you build a mutli-layer dictionary of the XML file and then do something like: def deconstruct(xmldata, parentdata): for item in xmldata: if item.has_subitems(): deconstruct(item, xmldata) query = INSERT INTO %s VALUES (...) % (xmldata.name, ...) -- Michael T. Babcock C.T.O., FibreSpeed Ltd. http://www.fibrespeed.net/~mbabcock -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Post-installation Testing
Hi guys I have just installed MySQL 4.0.13-standard on Mac OS X 10.2.6 for the first time. While doing post-installation testing I got access denied error. e.g Shell ./bin/mysqladmin -u root shutdown ./bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)' Similarly I got an error when I did this test Shell ./bin/mysqlshow mysql ./bin/mysqlshow: Access denied for user: '@localhost' to database 'mysql' Before these tests, I had used following commands to setup password /usr/local/mysql/bin/mysqladmin -u root password password /usr/local/mysql/bin/mysqladmin -u root -h `hostname` password password Thanks for the help Mazhar -- Instructional Technology Database Developer Electronic Instructional Services Georgia College State University 478-445-2520
mysql 4.0.13 and show databases topic
Hello, I have installed mysql 4.0.13 but suddenly all users can see names of all databases on the server in phpmyadmin (show databases command afaik) Is there something to avoid it ??? Sincerely, Roman -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Odd thing
On 19-Jun-2003 Christensen, Dave wrote: We're trying to set up some scripts that will run mysqldump to create export files for each table in a database that can be downloaded to a test server and reloaded. What's weird is that the files are created with the ? character as the trailing character in the file name and we can't open them to do anything with the files. Probably you have a unprintable character in your script. For reference, here's what I use for backup: --- #!/bin/sh # dwr dump database(s) each day DUMP=$HOME/archive/db DBS=auth gl ar ap inv DIR=`date +%a` cd $DUMP mkdir -p $DIR rm -f Today ln -sf $DIR Today cd $DIR for K in $DBS do TBLS=`mysql -N -e show tables $K` for I in $TBLS do mysqldump -e -q --add-drop-table $K $I $K.$I.sql rm -rf $K.$I.sql.gz gzip $K.$I.sql done done Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AIX 4.3.x Binary of MySQL
I downloaded MySQL AIX 4.3.x and was looking to see if it would run on an as/400. The as/400 ( iSeries ) has an AIX runtime library support (PASE), when I try to execute mysqld I get: Dependent module libnsl.a(shr.o) could not be loaded. Does MySQL actually use this library ? If not is there a possibility to get a binay without this dependency ? -Thanks Steve More __ Do you Yahoo!? SBC Yahoo! DSL - Now only $29.95 per month! http://sbc.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Same Problem about starting MySQL on a Mac
Hello Everyone, I have been running MySQL on my Mac for almost a year now. suddenly I am unable to start the mysqld, I keep getting mysqld ended error. - [psg:/usr/local/mysql] psgarcha% sudo ./bin/mysqld_safe [5] 703 [psg:/usr/local/mysql] psgarcha% Starting mysqld daemon with databases from /usr/local/mysql/data 030618 11:50:09 mysqld ended [5]Done sudo ./bin/mysqld_safe - Can u tell me how/ where to view the error logs on MAC OS JAGUAR...? I have MySQL installed on my Win XP Pro Laptop as well and I am able to view the error log files within the 'data' dir. However when it comes to my Mac G4 , I am at a loss to understand why this happens ? [psg:/usr/local/mysql] psgarcha% ls -la total 10352 drwxr-xr-x 22 root wheel 748 Apr 4 11:38 . drwxr-xr-x 5 root wheel 170 Apr 4 11:38 .. -rw-r--r-- 1 root wheel19106 Mar 15 11:17 COPYING -rw-r--r-- 1 root wheel28003 Mar 15 11:17 COPYING.LIB -rw-r--r-- 1 root wheel 126466 Mar 15 10:47 ChangeLog -rw-r--r-- 1 root wheel 6811 Mar 15 11:17 INSTALL-BINARY -rw-r--r-- 1 root wheel 1937 Mar 15 10:47 README drwxr-xr-x 46 root wheel 1564 Apr 4 11:38 bin -rwxr-xr-x 1 root wheel 773 Mar 15 11:29 configure drwxr-x--- 13 mysql wheel 442 Jun 18 11:50 data drwxr-xr-x 51 root wheel 1734 Apr 4 11:38 include drwxr-xr-x 8 root wheel 272 Apr 4 11:38 lib drwxr-xr-x 3 root wheel 102 Apr 4 11:38 man -rw-r--r-- 1 root wheel 2676944 Mar 15 11:16 manual.html -rw-r--r-- 1 root wheel 2329252 Mar 15 11:16 manual.txt -rw-r--r-- 1 root wheel98233 Mar 15 11:16 manual_toc.html drwxr-xr-x 9 root wheel 306 Apr 4 11:38 mysql-test drwxr-xr-x 3 root wheel 102 Apr 4 11:38 scripts drwxr-xr-x 3 root wheel 102 Apr 4 11:38 share drwxr-xr-x 33 root wheel 1122 Apr 4 11:38 sql-bench drwxr-xr-x 11 root wheel 374 Apr 4 11:38 support-files drwxr-xr-x 21 root wheel 714 Apr 4 11:38 tests When I try to access the Data Dir within mysql, i get a permission denied error, Thanks --Pushpinder
Re: selecting PRIMARY KEY when there is no unique value
On 19-Jun-2003 Mojtaba Faridzad wrote: Please don't hi-jack threads. Hi, In a docuement such as Invoice Form, we have a header and a couple of records for the detail. In header table, Invoice# can be the PRIMARY KEY but in detail table, Invoice# is not unique. I think there are two solutions to choose a Primary Key (in MyISAM type) : 1) Adding an id field ( auto_increment ) and choose it as PRIMARY KEY in this case we have to add another index on Invoice# for making relation with the header table 2) There is another field in detail table with timestamp type for keeping the last change on the record. I want to select ( Invoice# + myTimestamp ) for PRIMARY KEY. in this case I don't need to add a new fields ( id ) and another index ( on Invoice# ) to the table. which one do you prefer and usually use? Number one (with a composite key). table invoice_line ( idinv int unsigned not null, // invoice number line tinyint unsigned auto_increment,// line number idprod, qty, uom, price, cogs, // product shipped ... primary key(idinv, line) ) Regards, -- Don Read [EMAIL PROTECTED] -- It's always darkest before the dawn. So if you are going to steal the neighbor's newspaper, that's the time to do it. (53kr33t w0rdz: sql table query) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Post-installation Testing
On Thu, Jun 19, 2003 at 01:57:25PM -0400, Mazhar Malik wrote: Hi guys I have just installed MySQL 4.0.13-standard on Mac OS X 10.2.6 for the first time. While doing post-installation testing I got access denied error. e.g Shell ./bin/mysqladmin -u root shutdown ./bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: '[EMAIL PROTECTED]' (Using password: NO)' run: ./bin/mysqladmin -u root -p shutdown It will prompt you for the password you used during setup. Similarly I got an error when I did this test Shell ./bin/mysqlshow mysql ./bin/mysqlshow: Access denied for user: '@localhost' to database 'mysql' ./bin/mysqlshow -u root -p mysql You must provide a username to connect to the mysql server with. The '-p' option says to prompt for and use a password in order to connect. Before these tests, I had used following commands to setup password /usr/local/mysql/bin/mysqladmin -u root password password /usr/local/mysql/bin/mysqladmin -u root -h `hostname` password password -- Greg Klaus -=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=-=*=- Nec Mors, Nec Requies. Carpe Noctum! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: C API - mysql_free_result
After I use mysql_free_result(), I immediately set the resultset to NULL. -Original Message- From: Adam Lawrence [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 11:36 AM To: [EMAIL PROTECTED] Subject: C API - mysql_free_result Stats: MySQL 4.0.13, under Windows98, using the C API with lcc-win32. Is there any way to determine if mysql_free_result() has been called on a result set pointer? Occasionally I recycle the pointer (use it, free it, use it again, free it again, etc.) and if my code calls mysql_free_result() on a result set pointer that was already freed, an exception is raised and my code gracefully crashes out. -- Adam Lawrence Sustaining Engineering Tectrol Inc. -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySql C API
Can anybody please list known Forums with regard to MySql C API programming and development, Interfacing with multi-language programming, COBOL, PHP, GDK+, XForms, etc... Help for newbies to C API, techniques, sample codes, creating wrappers, list of great reference books (ones that are not padded too much with irrelevant topics to API, etc... etc... etc... We need more free online resources, documentation, and real world usage for the API! I'm sure others would also be interested and appreciate an index of resources ;-) Ronald Arenas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie trying to make root password
Hi, I'm installing and using mySQL for the first time, and it shows. I've downloaded and installed mysql-standard-4.0.13.pkg on my OS X box (bw G3, OS X 10.2.6) I'm following the directions given in the Readme: If you installed MySQL for the first time, *please remember to set a password for the MySQL root user!* This is done with the following two commands: /usr/local/mysql/bin/mysqladmin -u root password password /usr/local/mysql/bin/mysqladmin -u root -h `hostname` password password The first command, /usr/local/mysql/bin/mysqladmin -u root password password, worked fine. The second command gives me errors. I have questions about it, too, if you don't mind answering them. 1. What does this command do? It looks like it defines the root password a second time while specifying the host machine. Why do we do that? 2. Here's what I tried. Please tell me what I should /really/ be doing. I initially typed the command as shown: /usr/local/mysql/bin/mysqladmin -u root -h `hostname` password password with password replaced by the root password I used in the first command. Doing that gave me this error: bash-2.05a$ /usr/local/mysql/bin/mysqladmin -u root -h `hostname` password MYPASSWORD /usr/local/mysql/bin/mysqladmin: connect to server at 'Ungoliant.local.' failed error: 'Host '10.0.0.14' is not allowed to connect to this MySQL server' Next I tried this: bash-2.05a$ /usr/local/mysql/bin/mysqladmin -u root -h `Ungoliant.local' password MYPASSWORD You can see that I got a caret (greater-than-symbol) prompt which I did NOT know what to do with. (Ungoliant is the name of my machine.) Now when I try bash-2.05a$ /usr/local/mysql/bin/mysqladmin -u root -h '10.0.0.14' password MYPASSWORD or try again: bash-2.05a$ /usr/local/mysql/bin/mysqladmin -u root -h 'Ungoliant.local.' password MYPASSWORD I get the same connect to server at... error. Please advise? I'd wanted to start out right before I moved on. -- Michael Greisman [EMAIL PROTECTED] Web Developer Scanalytics, Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Odd thing
Gerald, That may be a preference, but the scripts typically run on the LINUX command line just fine in the form that we've entered. We've been entering them in that manner for months with no operational issues. Thanks for the input though. Have a great day! Dave -Original Message- From: gerald_clark [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 12:19 PM To: Christensen, Dave Cc: '[EMAIL PROTECTED]' Subject: Re: Odd thing Christensen, Dave wrote: Oops! Sorry. Deleted that 'temp' table and didn't remove it from my script file. -Original Message- From: Christensen, Dave [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 11:34 AM To: 'Jay Blanchard'; Dickey, Dallas; '[EMAIL PROTECTED]' Subject: RE: Odd thing Importance: High When I run this script file, I receive errors like this: [EMAIL PROTECTED] work]# ./BackupByTable.bat mysqldump: Got error: 1146: Table 'AgentAdminDB.MissedPages' doesn't exist when doing LOCK TABLES -Original Message- From: Christensen, Dave [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 10:58 AM To: 'Jay Blanchard'; Dickey, Dallas; Christensen, Dave; '[EMAIL PROTECTED]' Subject: RE: Odd thing I've also tried creating the script manually (script follows) and all that's being created is a text header with nothing else in the file. At least in these instances we can use the text files. Just be nice if they were valid... :-) --- Here's the script mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB MissedPages -uroot -ppano4577 MissedPages.sql mysqldump --add-drop-table --compatible=mysql323 --disable-keys AgentAdminDB UCS_APP_INFO -uroot -ppano4577 UCS_APP_INFO.sql You have options after the tables. All options should precede the database and tables. mysqldump --add-drop-table --compatible-mysql323 --disable-keys -uroot -ppano4577 AgentAdminDB MissedPages MissedPages.sql -- 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: Complex SQL involving 10 checkboxes
OK, I've done this. Problem occurs when a user selects 1 and 9. The SQL statement I have reads like so: if (isset($HTTP_GET_VARS['check00'])) { $age00 = '00'; $s_age00 = penpals.agegroup = $age00 AND; } Problem is the AND statement. Some times the user will pick checkbox 1 and 5 but not 2,3,4,6, 7, 8, and 9. And then again there will be times when a user only uses one check box needing the AND statement on the end, but at other times the user will select more than one needing an OR statement between the two checkboxes. Make sense? -- Original Message --- From: Mike Hillyer [EMAIL PROTECTED] To: vernon [EMAIL PROTECTED], Sent: Thu, 19 Jun 2003 12:57:20 -0600 Subject: RE: Complex SQL involving 10 checkboxes Well, if one checkbox represents ages 1-5, another represents 6-9, and another represents 10-15, then you have the checkboxes add where clauses. Here is a pseudocode/basic example: *START WITH A SELECT* SQL = SELECT * FROM TABLE WHERE *BUILD THE WHERE CLAUSES* IF check1 THEN SQL = SQL age BETWEEN 1 AND 5 AND IF check2 THEN SQL = SQL age BETWEEN 6 AND 9 AND IF check3 THEN SQL = SQL age BETWEEN 10 AND 15 AND *TRIM THE UNUSED AND STATEMENT SQL = left(sql, len(SQL) - 3) Add some GROUP and ORDER and LIMIT clauses as needed and send the resulting string to the server. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: vernon [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 12:53 PM To: [EMAIL PROTECTED] Subject: Complex SQL involving 10 checkboxes I'm trying to setup a SQL statement that involes using 10 different checkboxes. The checkboxes repersent age groups that I need to forward to an SQL statement. What would be the easiest way to do this? As I think this through I'm thinking I have to check it 1 and 2 are checked and not 3-9 and so forth which can drive a person to drink espically when I have other SQL statemenst I need to add to it as well. Any ideas on the best way to do this? I have this whole thing I was going to post to the list but figured I start out with the basic question first. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] --- End of Original Message --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Complex SQL involving 10 checkboxes
Well first of all, I am obviously having an off day for using AND at all in my example. Why not this? if (isset($HTTP_GET_VARS['check00'])) { $SQL .= penpals.agegroup = '00' OR; } This is probably a question for the PHP general mailing list as it is more a code question. Essentially you just build the query in chunks. By the nature of checkboxes you probably just need Ors, not ANDs. You may need to rip the trailing OR, maybe replacing it with an AND if you need to add some other condition on the end that needs an AND. Unless of course someone can belong to more than one agegroup. If so you better post what the table looks like. Regards, Mike Hillyer -Original Message- From: vernon [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 1:25 PM To: [EMAIL PROTECTED] Subject: RE: Complex SQL involving 10 checkboxes OK, I've done this. Problem occurs when a user selects 1 and 9. The SQL statement I have reads like so: if (isset($HTTP_GET_VARS['check00'])) { $age00 = '00'; $s_age00 = penpals.agegroup = $age00 AND; } Problem is the AND statement. Some times the user will pick checkbox 1 and 5 but not 2,3,4,6, 7, 8, and 9. And then again there will be times when a user only uses one check box needing the AND statement on the end, but at other times the user will select more than one needing an OR statement between the two checkboxes. Make sense? -- Original Message --- From: Mike Hillyer [EMAIL PROTECTED] To: vernon [EMAIL PROTECTED], Sent: Thu, 19 Jun 2003 12:57:20 -0600 Subject: RE: Complex SQL involving 10 checkboxes Well, if one checkbox represents ages 1-5, another represents 6-9, and another represents 10-15, then you have the checkboxes add where clauses. Here is a pseudocode/basic example: *START WITH A SELECT* SQL = SELECT * FROM TABLE WHERE *BUILD THE WHERE CLAUSES* IF check1 THEN SQL = SQL age BETWEEN 1 AND 5 AND IF check2 THEN SQL = SQL age BETWEEN 6 AND 9 AND IF check3 THEN SQL = SQL age BETWEEN 10 AND 15 AND *TRIM THE UNUSED AND STATEMENT SQL = left(sql, len(SQL) - 3) Add some GROUP and ORDER and LIMIT clauses as needed and send the resulting string to the server. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: vernon [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 12:53 PM To: [EMAIL PROTECTED] Subject: Complex SQL involving 10 checkboxes I'm trying to setup a SQL statement that involes using 10 different checkboxes. The checkboxes repersent age groups that I need to forward to an SQL statement. What would be the easiest way to do this? As I think this through I'm thinking I have to check it 1 and 2 are checked and not 3-9 and so forth which can drive a person to drink espically when I have other SQL statemenst I need to add to it as well. Any ideas on the best way to do this? I have this whole thing I was going to post to the list but figured I start out with the basic question first. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] --- End of Original Message --- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Complex SQL involving 10 checkboxes
When I have these kind of settings/testing, I make one text-field and set a specific code ex. age 0-5 [age00-05] age 6-10 [age06-10] and so on - then you reduce the SQL to always read that specific tablefield, and leave the complexity to the program instead. best regards Peter - Original Message - From: Mike Hillyer [EMAIL PROTECTED] To: vernon [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, June 19, 2003 8:57 PM Subject: RE: Complex SQL involving 10 checkboxes Well, if one checkbox represents ages 1-5, another represents 6-9, and another represents 10-15, then you have the checkboxes add where clauses. Here is a pseudocode/basic example: *START WITH A SELECT* SQL = SELECT * FROM TABLE WHERE *BUILD THE WHERE CLAUSES* IF check1 THEN SQL = SQL age BETWEEN 1 AND 5 AND IF check2 THEN SQL = SQL age BETWEEN 6 AND 9 AND IF check3 THEN SQL = SQL age BETWEEN 10 AND 15 AND *TRIM THE UNUSED AND STATEMENT SQL = left(sql, len(SQL) - 3) Add some GROUP and ORDER and LIMIT clauses as needed and send the resulting string to the server. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: vernon [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 12:53 PM To: [EMAIL PROTECTED] Subject: Complex SQL involving 10 checkboxes I'm trying to setup a SQL statement that involes using 10 different checkboxes. The checkboxes repersent age groups that I need to forward to an SQL statement. What would be the easiest way to do this? As I think this through I'm thinking I have to check it 1 and 2 are checked and not 3-9 and so forth which can drive a person to drink espically when I have other SQL statemenst I need to add to it as well. Any ideas on the best way to do this? I have this whole thing I was going to post to the list but figured I start out with the basic question first. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [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: Complex SQL involving 10 checkboxes
Why not have each one look like this: if (isset($HTTP_GET_VARS['check00'])) { $SQL .= penpals.agegroup = '00' OR; } Then after you go through them all, strip the last two characters (the trailing OR) and then attach and AND That way it will accommodate any checks, and because you rip the last or and replace it with an and, you are always Ok for what follows. -Original Message- From: vernon [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 1:49 PM To: Mike Hillyer Subject: RE: Complex SQL involving 10 checkboxes if (isset($HTTP_GET_VARS['check00'])) { $SQL .= penpals.agegroup = '00' OR; } Because there is a statement after these that neends to have the AND statement. This is probably a question for the PHP general mailing list as it is more a code question. There I always get them telling me to come here, SQL issue. :( Essentially you just build the query in chunks. By the nature of checkboxes you probably just need Ors, not ANDs. You may need to rip the trailing OR, maybe replacing it with an AND if you need to add some other condition on the end that needs an AND. Unless of course someone can belong to more than one agegroup. If so you better post what the table looks like. It's not that they belong to more than one age group they may be looking for more than one age group, which is the case (it's a dating site). The statement immidiately after words MUST have the AND statement. My only problem is with the checkboxes. Maybe they select only one in which case it needs and AND if they select more than one it needs an OR and AND on the last one. Make sense? V -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fastest DB engine
How do I unscribe from this list? Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
linux and mysql socket
I installed mysql manually on red hat 9. It worked fine couple days but from yesterday i am getting a weird error when i tried to run the mysql. the error statement is Error 2002: Can't connect to local MySql server through socket '/tmp/mysql.sock'(2). I am new user of Linux and I am unable to figure out why is this happening. Please help me. thanks in advance. Kamran -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to UNSUBSCRIBE
I swear we went over this today... for anybody still unsure... give a look at the bottom of any email you recieve from the list and it will say 'To unsubscribe' on the last line... just click the link It will probably be at the end of this message even.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Distributed/Fault Tolerant DB operation... possible?
Hi guys, I've been doing some digging around and found some information about master/slave database duplication, but it always sees to focus on increasing query performance by spreading the db out. My situation is that there's a database which must absolutely, guaranteedly be operational 24x7x365 always forever. It must survive and still be operational through power failures, machine locks, and any other manner of scheduled or unscheduled downtime short of a bomb dropping on the co-lo. This would be relatively easy to do if the system was purely read-only: I'd simply duplicate my data across numerous machines and pull queries from them, perhaps on the other side of a load balancer to make no one machine have to be too painfully hit. However, this system is write-heavy (at least 50%, with periods of time reaching 80% or more). Therefore, I need to be able to do a store to one of the servers, and have that store propogate to the other machines (with appropriate software design to compensate for propogation delays and insert-order neutrality). Has anyone done this with two (or more, if possible!) machines? Is it possible to do at the present time? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mac osx
There is no built-in graphically interface for running MySQL. It's all command line, meaning you need to use your terminal. You absolutely must read something on how to use MySQL. There are a lot of good books out there, I used MySQL by Paul DuBois (who is also on this list). MySQL is a database engine, which kind of works like FileMaker Server if you are familiar with that. On Thursday, June 19, 2003, at 02:14 PM, 4mula design wrote: Hi, I have installed mysql-standard 4.0.13 for Mac OSX10.2. I cannot get the mysql application to work-the files are white and cannot be launched. Any ideas what to do? Regards JB -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: linux and mysql socket
Your client is configured to connect via UNIX sock on localhost. It thinks the sock is on /tmp when it's probably /var/lib/mysql Take a look at /etc/my.cnf and make the change there or at the command line. ---Original Message- --From: azamka [mailto:[EMAIL PROTECTED] --Sent: Thursday, June 19, 2003 1:07 PM --To: [EMAIL PROTECTED] --Subject: linux and mysql socket -- --I installed mysql manually on red hat 9. It worked fine couple days but --from --yesterday i am getting a weird error when i tried to run the mysql. the --error --statement is Error 2002: Can't connect to local MySql server through --socket --'/tmp/mysql.sock'(2). -- --I am new user of Linux and I am unable to figure out why is this --happening. --Please help me. -- --thanks in advance. --Kamran -- -- -- --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: Distributed/Fault Tolerant DB operation... possible?
---Original Message--- -- Has anyone done this with two (or more, if possible!) machines? Is it --possible to do at the present time? Your going to need to have a chain master setup. This is what I propose. 1) Application(s) must know about all the master / slaves (or hide it behind a VIP 2) Configure the Master and Sub-Master to log it's slave queries. 3) If the Master goes down then the sub master will maintain all the writes vice versa. 4) Have the binary logs located on a shared drive or mirror it across the network. This will allow slave to connect to new masters in the event the original master goes down. 5) All sub masters must use the same binary log file and respect the rotation. This is related to 4. 6) software needs to be written to manage this all Problems with this. 1) Primary key violations on Master to Sub-Master 2) Lots of gaps and assumptions. This is generally the approach I have taken. One day I will write a white paper up on it and publish my ugly code that manages failover etc. -- -- --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]
SQL query question
Hello all, I am a totally newby to MYSQL but here i go. I want to create an ASP page that shows an image (screendump of game) a game name a game description and an url. I created a database with the following table: filename, urldesc, desc Now i have to create an SQL query that will put this information in a webpage: this is what i got. SELECT ' img border=0 src=ftp://cc278355-a.groni1.gr.home.nl/c64/games/' filename '.gif /p td a href=ftp://cc278355-a.groni1.gr.home.nl/c64/games/' filename '.zip ' urldesc '/a td p ' desc ' /p td ' FROM table1; The zip file for the download and the gif file for the image have the same filename exept the extention. It nearly works but it won't show all the text in the description. Any ideas? _ Chatten met je online vrienden via MSN Messenger. http://messenger.msn.nl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SQL query question
Well, from what limited info I have, it looks like your image tag is not closed properly. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Rolf C [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 2:57 PM To: [EMAIL PROTECTED] Subject: SQL query question Hello all, I am a totally newby to MYSQL but here i go. I want to create an ASP page that shows an image (screendump of game) a game name a game description and an url. I created a database with the following table: filename, urldesc, desc Now i have to create an SQL query that will put this information in a webpage: this is what i got. SELECT ' img border=0 src=ftp://cc278355-a.groni1.gr.home.nl/c64/games/' filename '.gif /p td a href=ftp://cc278355-a.groni1.gr.home.nl/c64/games/' filename '.zip ' urldesc '/a td p ' desc ' /p td ' FROM table1; The zip file for the download and the gif file for the image have the same filename exept the extention. It nearly works but it won't show all the text in the description. Any ideas? _ Chatten met je online vrienden via MSN Messenger. http://messenger.msn.nl/ -- 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: Distributed/Fault Tolerant DB operation... possible?
First get an acceptable outtage rate. Your only going to get so many nines, and your budget depends on how many. The system will fail at some point, no matter what, even if it's only for a few seconds. That's reality. Figure out what kinds of failures you can tolerate based on how many 9's you get and what kinds you have to design around. From there you can figure out a budget. 99.999% uptime is 5 minutes and 15 seconds per year of total downtime. 99.99% is 52.56 minutes and so on. At some point something will happen, and I've never seen anyone offer more than 5 9's, and IBM charges a lot for that. Then, figure out everything that could cause an outtage, figure out how to work around them and give them a budget. Watch how many 9's come off that requirement. If you have to use MySQL I'd ditch PC hardware and go with some nice Sun kit if you haven't already, or maybe a IBM mainframe. Sun's Ex8xx line should let you do just about anything without taking it down (like change the memory while it's running). Then I'd get a bunch of them. Then I'd recode the application to handle the multiple writes to multiple servers and keep everything atomic, then test the hell out of it. There's a lot of issues to consider in there, and you probably want someone with a graduate degree in computer science to look over the design for you. (anything this critical and I get someone smarter than me to double check my designs and implementations). It may be best to just build it in to the driver so the apps are consistent. On the other hand, if you have all this money, look at some of the comerical solutions. This is probably heresy on this list, but hey, it's about the best solution for the needs right? Sybase or DB2 would be my first choices depending on the hardware platform (Sun or Mainframe). The systems are setup to handle failover of the master server. I know for Sun you want to be looking at Sun Clustering technology, a nice SAN and a couple of nice servers. You write to one server, but when it fails the backup server starts accepting the write operations as if it were the master. There's a general rule with software engineering that says if you can buy 80% of what you want, your better off doing that than trying to engineer 100% Think about the networking. two datapaths everywhere there's one. Two switches, two NIC cards for each interface, each going to a different switch. Depending on where your clients are you need to look at your datacenter. Is your database server feeding data to clients outside your building? If so you probably want a few servers in a few different datacenters. At least something like one on the east coast and one on the west coast in the US, or the equivelent in your country, both of whom have different uplinks to the Internet. Get portable IP addresses and do your own BGP. That way if a WAN link fails the IP addresses will show up on the other WAN link even though it's from a different provider. This is just a quick run down of immediate issues in a 24x7x365, it's not exhaustive. Think about every cable, every cord, every component, from a processor to a memory chip and think about what happens when you pull it out or unplug it, then make it redundant. -- Michael Conlen Rick Franchuk wrote: Hi guys, I've been doing some digging around and found some information about master/slave database duplication, but it always sees to focus on increasing query performance by spreading the db out. My situation is that there's a database which must absolutely, guaranteedly be operational 24x7x365 always forever. It must survive and still be operational through power failures, machine locks, and any other manner of scheduled or unscheduled downtime short of a bomb dropping on the co-lo. This would be relatively easy to do if the system was purely read-only: I'd simply duplicate my data across numerous machines and pull queries from them, perhaps on the other side of a load balancer to make no one machine have to be too painfully hit. However, this system is write-heavy (at least 50%, with periods of time reaching 80% or more). Therefore, I need to be able to do a store to one of the servers, and have that store propogate to the other machines (with appropriate software design to compensate for propogation delays and insert-order neutrality). Has anyone done this with two (or more, if possible!) machines? Is it possible to do at the present time? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What's wrong with this query?
Why isn't the key being used in the c (certificate) table? SELECT DISTINCT a.uid, a.company, a.firstname, a.lastname FROM master_info a, logins lsl, logins lc, certificate c WHERE a.uid = lsl.uid AND lc.parent = lsl.uid AND lc.uid = c.uid AND c.void 1 AND c.status IN ('CP', 'MC', 'AIC', 'RP', 'VQ', 'NQ', 'LQ', 'RQ', 'NG', 'EP', 'AA', 'AD' ) AND lsl.active = 1 AND lsl.void = 0 ORDER BY company, uid EXPLAIN: +---+++-+-+---+---+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+++-+-+---+---+-+ | c | ALL| uid,status | NULL|NULL | NULL | 11552 | where used; Using temporary; Using filesort | | lc| eq_ref | PRIMARY,uid,parent | PRIMARY | 10 | c.uid | 1 | | | lsl | eq_ref | PRIMARY,uid| PRIMARY | 10 | lc.parent | 1 | where used | | a | eq_ref | PRIMARY| PRIMARY | 10 | lsl.uid | 1 | | +---+++-+-+---+---+-+ mysql show index from certificate; +-++-+--+-+---+-+--++-+ | Table | Non_unique | Key_name| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +-++-+--+-+---+-+--++-+ +-++-+--+-+---+-+--++-+ | certificate | 0 | PRIMARY |1 | invoice_num | A| 11552 | NULL | NULL | | | certificate | 0 | cert_num|1 | cert_num| A|NULL | NULL | NULL | | | certificate | 0 | cert_num|2 | cert_order | A| 11552 | NULL | NULL | | | certificate | 1 | uid |1 | uid | A|NULL | NULL | NULL | | | certificate | 1 | status |1 | status | A|NULL | NULL | NULL | | | certificate | 1 | invoice_num |1 | invoice_num | A|NULL | NULL | NULL | | | certificate | 1 | invoice_num |2 | status | A|NULL | NULL | NULL | | | certificate | 1 | x1 |1 | uid | A|NULL | NULL | NULL | | | certificate | 1 | x1 |2 | status | A|NULL | NULL | NULL | | | certificate | 1 | x1 |3 | void| A|NULL | NULL | NULL | | +-++-+--+-+---+-+--++-+ 10 rows in set (0.00 sec) mysql show index from logins; +++--+--+-+---+-+--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +++--+--+-+---+-+--++-+ | logins | 0 | PRIMARY |1 | uid | A | 9091 | NULL | NULL | | | logins | 1 | uid |1 | uid | A | NULL | NULL | NULL | | | logins | 1 | parent |1 | parent | A | NULL | NULL | NULL | | | logins | 1 | type |1 | type| A | NULL | NULL | NULL | | | logins | 1 | level|1 | level | A | NULL | NULL | NULL | | +++--+--+-+---+-+--++-+ 5 rows in set (0.01 sec) mysql show index from master_info; +-++---+--+-+---+-+--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation| Cardinality | Sub_part | Packed | Comment | +-++---+--+-+---+-+--++-+ | master_info | 0 | PRIMARY |1 | uid | A|9078 | NULL | NULL | | | master_info | 1 | slbco_idx |1 | slbco_id| A|NULL | NULL | NULL
Re: mac osx
There are some good Mac clients to access MySQL. I personally use CocoaMySQL for much of my day to day activity. You can find them all on versiontracker.org. YourSQL looks okay too. I agree though, the fancy interface, should you choose to use one of the above clients, is much more useful if you know some basics about how MySQL and Databases in general work. (Not assuming you don't, you may.) You'll need to use Terminal anyway to start the MySQL daemon. Marc Liyanage has some good documentation on MySQL on OS X. http://www.entropy.ch/software/macosx/mysql/ I followed his instructions and it works beautifully on my machine. On Thursday, June 19, 2003, at 03:31 PM, Brent Baisley wrote: There is no built-in graphically interface for running MySQL. It's all command line, meaning you need to use your terminal. You absolutely must read something on how to use MySQL. There are a lot of good books out there, I used MySQL by Paul DuBois (who is also on this list). MySQL is a database engine, which kind of works like FileMaker Server if you are familiar with that. On Thursday, June 19, 2003, at 02:14 PM, 4mula design wrote: Hi, I have installed mysql-standard 4.0.13 for Mac OSX10.2. I cannot get the mysql application to work-the files are white and cannot be launched. Any ideas what to do? Regards JB -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Kerberos or Ldap authentication
Hello All, I am evaluating MySQL as a DB backend for project I am working on. I have used MySQL in the past and I think it is an excellent product and would be perfect for my current environment. HOWEVER !! One of the major requirements for my project is that the DB backed support Kerberos or LDAP authentication. Does MySQL support Kerberos or LDAP authentication and if so can someone point me to documentation on how to set it up. Thanks, Anwar Abdus-Samad [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What's wrong with this query?
On 19 Jun 2003 at 16:18, Chris Boget wrote: Why isn't the key being used in the c (certificate) table? SELECT DISTINCT a.uid, a.company, a.firstname, a.lastname FROM master_info a, logins lsl, logins lc, certificate c WHERE a.uid = lsl.uid AND lc.parent = lsl.uid AND lc.uid = c.uid AND c.void 1 AND c.status IN ('CP', 'MC', 'AIC', 'RP', 'VQ', 'NQ', 'LQ', 'RQ', 'NG', 'EP', 'AA', 'AD' ) AND lsl.active = 1 AND lsl.void = 0 ORDER BY company, uid How do you think the key should be used? You have a key on status, but you're asking for a wide range of status values. Presumably MySQL thinks (probably correctly) that using the index to find a range of status values from 'AA' to 'VQ' is no faster than doing a full table scan. I could be missing something, but if so it would help if you could explain how you expect the key to be used. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: linux and mysql socket
If it truly ran fine for a few days and then you couldn't run the client, you may have another problem. If you rebooted the box, mysqld may not have automatically started. So, all of the sudden, the client can't connect to the socket since it is not there as the server is not running. You can check the my.cnf file until your are blue in the face, but, if the server isn't running, then the socket file isn't anywhere to be found, no matter what is in my.cnf. By the way, I did this one to myself too! Ken Hylton -Original Message- From: Dathan Vance Pattishall [mailto:[EMAIL PROTECTED] Sent: Thursday, June 19, 2003 3:45 PM To: 'azamka'; [EMAIL PROTECTED] Subject: RE: linux and mysql socket Your client is configured to connect via UNIX sock on localhost. It thinks the sock is on /tmp when it's probably /var/lib/mysql Take a look at /etc/my.cnf and make the change there or at the command line. ---Original Message- --From: azamka [mailto:[EMAIL PROTECTED] --Sent: Thursday, June 19, 2003 1:07 PM --To: [EMAIL PROTECTED] --Subject: linux and mysql socket -- --I installed mysql manually on red hat 9. It worked fine couple days but --from --yesterday i am getting a weird error when i tried to run the mysql. the --error --statement is Error 2002: Can't connect to local MySql server through --socket --'/tmp/mysql.sock'(2). -- --I am new user of Linux and I am unable to figure out why is this --happening. --Please help me. -- --thanks in advance. --Kamran -- -- -- --MySQL General Mailing List --For list archives: http://lists.mysql.com/mysql --To unsubscribe: --http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]