Re: MySQL Connector/ODBC 5.2.6 has been released
no binaries for AIX? -- saqib http://hivemined.net On Wed, Oct 9, 2013 at 3:02 PM, Kent Boortz kent.boo...@oracle.com wrote: Dear MySQL users, MySQL Connector/ODBC 5.2.6, a new version of the ODBC driver for the MySQL database management system, has been released. The available downloads include both a Unicode driver and an ANSI driver based on the same modern codebase. Please select driver type you need based on the type of your application - Unicode or ANSI. Server-side prepared statements are enabled by default. It is suitable for use with any MySQL version since 4.1 (It will not work with 4.0 or earlier releases.) The release is now available in source and binary form for a number of platforms from our download pages at https://urldefense.proofpoint.com/v1/url?u=http://dev.mysql.com/downloads/connector/odbc/5.2.htmlk=2a4Akkj3oY%2FOkjwft1MTMw%3D%3D%0Ar=87QPkuwxTJwqBO%2Bh4edD4j3tpM%2BFzlMhKOz%2B9QNZPGo%3D%0Am=L%2FgKYklRPKzhryVewvSQf1uRAbXq8OAWOmVW6e%2Fr%2Bg4%3D%0As=9074990ebf9e3277c9367db7b7881d214e8cf4ff3ca73847e2cab5b90b167d9c MySQL Connector/ODBC 5.2.6 will also be available for downloads via the Oracle Software Delivery Cloud in approximately 1-2 weeks. For information on installing, please see the documentation at https://urldefense.proofpoint.com/v1/url?u=http://dev.mysql.com/doc/refman/5.6/en/connector-odbc-installation.htmlk=2a4Akkj3oY%2FOkjwft1MTMw%3D%3D%0Ar=87QPkuwxTJwqBO%2Bh4edD4j3tpM%2BFzlMhKOz%2B9QNZPGo%3D%0Am=L%2FgKYklRPKzhryVewvSQf1uRAbXq8OAWOmVW6e%2Fr%2Bg4%3D%0As=544722c0f324655dcf7be180c365423e7cbdb91c9de221f2ef29cbd0ef2ed2dc Enjoy! The MySQL Connectors team at Oracle == Changes in MySQL Connector/ODBC 5.2.6 Functionality Added or Changed * Added a lock in the ENV structure against simultaneous accesses to an environment handle's connection list, in order to avoid memory access issues that might occur when multiple threads share the same environment handle. (Bug #17240611, Bug #69864) Bugs Fixed * Memory leaks occurred in SQLPrepare() and SQLExecDirect() when SQLBindParamater was used on the SQL statements. This fix frees previous results of mysql_stmt_result_metadata() in order to prevent the memory leaks. (Bug #17400483, Bug #70113) * The exit condition for a for loop in stringutil.c is changed to avoid a possible out-of-bounds error and the associated reports by Valgrind. (Bug #17397596, Bug #64105) * Microsoft Visual Studio 2010 crashed when reading rows from any table in Server Explorer with connections to DSNs made by Connector/ODBC. (Bug #17304031, Bug #69950) * When SQL TIME data was converted to the C data type of TIMESTAMP, if the value of the hour field is greater than 24, the date fields did not get set to the current date and the fraction field for seconds did not get set to zero as required by the ODBC specifications. (Bug #17016839, Bug #69545) * When trying to create a linked server in Micrsoft SQL Server 2008 to a MySQL server set up with Connector/ODBC as a DSN, the Microsoft SQL Server (if it is a 64-bit version) crashed or the linked server suffered a catastrophic failure (if a 32-bit version of the Microsoft SQL Server is used). (Bug #16604346, Bug #63386) * When reading TIME column data using the SQLGetData method into the SQL_C_TYPE_TIME datatype, errors occured when the time string contained more than 6-digits (e.g., 120:53:44). (Bug #16176981, Bug #67793) * When called after SQLPrepare, the funciton SQLMoreResults returned SQL_ERROR, instead of SQL_NO_DATA as required by the ODBC specification. (Bug #16101282, Bug #67920) * Connector/ODBC used the ; character instead of a null byte (\0) to terminate keyword-value pairs in attribute strings. (Bug #15940689, Bug #66548) * When using the Unicode-enabled version of the Connector/ODBC driver to connect to any MySQL server of version 5.5.2 or older, all statements failed with the error message .Server does not support 4-byte encoded UTF8 characters.. (Bug #14838690, Bug #67428) * The configured values of a DSN's parameters overrode the values provided through the connection string, instead of the other way round. (Bug #11760574, Bug #52996) Built against the MySQL server 5.6.14 client library. -- Kent Boortz, Release Staff engineer Oracle, The MySQL Team Mobile: +46 76 77 69 049 -- MySQL General Mailing List For list archives: https://urldefense.proofpoint.com/v1/url?u=http://lists.mysql.com/mysqlk=2a4Akkj3oY%2FOkjwft1MTMw%3D%3D%0Ar=87QPkuwxTJwqBO%2Bh4edD4j3tpM%2BFzlMhKOz%2B9QNZPGo%3D%0Am=L%2FgKYklRPKzhryVewvSQf1uRAbXq8OAWOmVW6e%2Fr%2Bg4%3D%0As=0406d8c5fc71760575ea672f27147b6cd35a3d2164f94c0f6e57396cc16f1d48 To unsubscribe: https://urldefense.proofpoint.com/v1/url?u=http://lists.mysql.com
Connecting to MySQL Community Edition from Informatica Powercenter 9.5.1 running on AIX
Is it possible to MySQL Community Edition (as target) from Informatica Powercenter 9.5.1 running on AIX? If so, what is ODBC driver should we use?
using a subquery/self-join to recursively retrieve a tree
Hello, I have following simplistic DB representing a hierarchy: ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | uid| int(10) | NO | PRI | 0 | | | name | char(80) | YES | | NULL| | | mail | char(80) | YES | | NULL| | | manageruid | int(10) | YES | | NULL| | ++--+--+-+-+---+ How can I do some recursion to get the UIDs of all the employees reporting up to a manager, regardless of how deep the tree is. I can do this usindg LDAP and/or PHP, but not sure how to do it as a mysql query. Any thoughts? Thanks saqib http://www.capital-punishment.us -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to create a unicode capable table??
Hello, Any thoughts on this? Thanks. On Tue, Nov 25, 2008 at 8:14 PM, Ali, Saqib [EMAIL PROTECTED] wrote: What Charset and Collation should I use while creating a mysql table such that it can take data from unicode SQL Server DB table? Thanks saqib http://doctrina.wordpress.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to create a unicode capable table??
What Charset and Collation should I use while creating a mysql table such that it can take data from unicode SQL Server DB table? Thanks saqib http://doctrina.wordpress.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Importing MS SQL exported CSV files adds spaces
I exported a large data set from from Microsoft SQL server in CSV format. However whenever I try to import that data to a a mySQL server running on Linux, it adds a space between each character in each field. Essentially: Saqib Ali becomes S a q i b A l i I have tried to use the dos2unix cmd on linux, but that didn't help either. I am using the following SQL to import the data: LOAD DATA LOCAL INFILE '/home/saqib/data.csv' INTO TABLE apps FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\n'; Any thoughts? saqib http://doctrina.wordpress.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Importing MS SQL exported CSV files adds spaces
try OPTIONALLY ENCLOSED BY ' ' already tried that. no help :( saqib http://doctrina.wordpress.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What to do if a table is supposed to get 3GB data per day?
wow 30GB is a lot of data. Do let us know what kind of hardware / OS you are using. In the past I have worked with larger tables then these, but I was using Objectivity DB running on UltraSPARC 64bit architecture. -- Saqib Ali, CISSP, ISSAP http://www.full-disk-encryption.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is your favorite GUI tool for creating and querying tables in MySQL?
sqlyog is pretty nice: http://www.webyog.com/ saqib http://www.full-disk-encryption.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is your favorite GUI tool for creating and querying tables in MySQL?
and also http://fabforce.net/dbdesigner4/ sorry for 2 seperate emails. saqib http://www.full-disk-encryption.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Retaining last primary key value
try the DELETE with the WHERE clause e.g. DELETE FROM table_name WHERE 1=1 saqib http://www.full-disk-encryption.net On 12/8/06, Adam Zerlin [EMAIL PROTECTED] wrote: Hello, When I run a DELETE FROM table_name, my primary key field is reset back to 1. Is there any way for it to not do this? Preferably, if there were 1000 records in table_name, and I ran DELETE FROM table_name, that the primary key field would start at 1001. This is an InnoDB table if that helps. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Saqib Ali, CISSP, ISSAP http://www.full-disk-encryption.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
OT: LAMP appliance for non-profit use
Hello All, I know this is little bit off-topic but I think users of this group can give good advice on this topic. We have a in-house built application using MySQL + PHP + Apache. It is essentially a elaborate collection of online forms that will be used for managing school scholarship in the 3rd world countries. The student/teachers will either come to the office or fill out the forms online. We have identified few pilot location and would like to deploy this application. However due to lack of IT support at the location we have decided to use a pre-configured appliance which support the LAMP stack. All we want to do is directly ship the appliance to the location, and remotely install our app. We want the appliance to require minimal maintenance and administration, and have a auto-update feature to get security patches etc. I had previously worked on a similar project and used Sun Cobalt 550 servers. But now I think Sun has stopped making them and you can only buy them refurbished and the auto-update features are severely limited. Any other suggestion??? We want the appliance to cost us $1,000 / unit. Thanks saqib http://www.full-disk-encryption.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best practices for deleting and restoring records - moving vs flagging
Hello All, What are best practices for deleting records in a DB. We need the ability to restore the records. Two obvious choices are: 1) Flag them deleted or undeleted 2) Move the deleted records to seperate table for deleted records. We have a complex schema. However the the records that need to be deleted and restored reside in 2 different tables (Table1 and Table2). Table2 uses the primary key of the Table1 as the Foriegn key. The Primary key for Table1 is auto-generated. This make the restoring with the same primary key impossible, if we move deleted data to a different table. However if we just flag the record as deleted the restoring is quite easy. Any thoughts/ideas ? -- In Peace, Saqib Ali http://www.xml-dev.com/blog/ Consensus is good, but informed dictatorship is better. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query Speed in Terminal vs. PHP
Is index defined on all of your tables? Saqib Ali - http://validate.sf.net (X)HTML / DocBook Validator and Transformer On Tue, 2 Mar 2004, Chris Fowler wrote: I have a query that is admittedly inefficient in that it is doing multiple OR clauses and joining multiple tables. However, the query runs at an acceptable speed if I am in a terminal session and run the query directly in the terminal. On the other hand, when PHP performs the same query for use in a page, it takes 5-7 minutes for the query to run (the same query running in the terminal takes about 3-5 seconds). I can do a show processlist when the php page runs the query and I see Copying to tmp table in the State column of the show processlist results. Does anyone have any tips on why it is so much slower when run from php vs. the terminal. -- 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: Database-design
you need to normalize your database structure, and create an ER diagram based on that. On Tue, 11 Nov 2003, Meli Meli wrote: I have a table with following structure: Id first quarter second quarter third quarter last quarter year month week day On an entry not all fields of the four quarter fields are covered with values. Following combinations are possible: first quarter | second quarter | third quarter | last quarter xnullnull null null x null null null null xnull null nullnullx x x null null x null xnull x null null x null x xnull null x nullx null null x x x x xnull x x null x x null x x null xx x x xx x The table will receive many thousands of entrys. Would it be better to divide the table in to 15 small tables in order to not register fields with null values? Thanks for helping Regards Martin - Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Lost Password
you can always reset the mysql root passwd http://www.mysql.com/doc/en/Resetting_permissions.html Saqib Ali - http://validate.sf.net --- XHTML/HTML/DocBook Validator On Tue, 4 Nov 2003, Carol Andrejak wrote: Hello Listers, I have a problem in that I cannot find my user id and password to log into mysql as administrator. I can never remember passwords so I write them all down in a small book however when I reinstalled mysql early last month, evidently I neglected to write down the new user id and password in my book. I haven't touched telnet since then. So now I can't log in to create a new user or do anything else. sigh Is it possible to sit down in front of the server logged in as root and find the file that has this information and have a look at it? Maybe if I can see the user names it'll jog my memory as to the password. I can try a couple to see if any work. If this is not possible, what recourse do I have? I don't want to mess with the database and start over and I seriously don't want to have to do anything to mysql as it took me two weeks to get it to work with the DBI perl module. Now that it's working, I'm terrified to touch it in case it breaks. Thanks for any guidance. I promise this time to not forget to write it down. Carol Andrejak Webmaster Delaware State University Grossley Hall Rm. 1 302-857-7045 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: phpmyadmin screw up
seems like you set the mysql root passwd which is the right thing to do. now should connect as follows: # /usr/local/mysql/bin/mysql -u root -p Saqib Ali - http://validate.sf.net --- XHTML/HTML/DocBook Validator On Tue, 4 Nov 2003, joffrey leevy wrote: Hi all: I was trying to use phpmyadmin for the first time and messed up when trying to give the [EMAIL PROTECTED] a password. I did that because phpmyadmin told me something about lax security using root with no password. Anyhow something went wrong. Now I can't do anything with phpadmin or mysql client. The message is - Error 1045: Access Denied for User: (Using Password:No). Grateful for anyone's help. Thanks __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- 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: Is MySQL Relational? (was: Foreigner keys in MySQL?)
a question... is any DB that complies to the Relational Algebra a Relational DB??? Saqib Ali - http://validate.sourceforge.net --- XHTML/HTML/DocBook Validator On Tue, 28 Oct 2003 [EMAIL PROTECTED] wrote: I tend to agree with the past two posts that mysql is in fact relational, and would like to add that if we are going to make blanket statements that something IS or IS NOT that we qualify our responses. personlly i would like to know why Martijn views it as being a non relational db, without argument. If you just say its so, why am is supposed to take that statement over those that provide a full email of text like bluejack or John that provided additional information via a link. Just saying No, to a question like that does not lend to learning only to confusion. sorry about the mini rant jeff bluejack [EMAIL PROTECTED]To: [EMAIL PROTECTED] om cc: Subject: Is MySQL Relational? (was: Foreigner keys in MySQL?) 10/28/2003 11:38 AM 10/28/03 8:11:16 AM, Martijn Tonies [EMAIL PROTECTED] wrote: If you are trying to determine whether MySQL is a relational database, the answer is yes. Ehm... the answer is no. It's getting better, that's for sure. MySQL may or may not conform to some standard or another, and it may or may not perform the tasks that you, personally, want it to, but in fact it *is* a relational database, as opposed to a flat file or an object database. I recommended the questioner evaluate her needs against MySQL's available feature set, because if you are used to Oracle (or some other robust, commercial relational database) you may be surprised at some of the differences or absences in MySQL. But the questioner made it seem as though she was really just trying to get a feel for the basics scope of MySQL, and in that context, yes, MySQL is designed to a implement a relational database model as opposed to some other fundamental type of database. So, what's your laundry list of things MySQL should do? --bluejack -- 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: Is MySQL Relational? (was: Foreigner keys in MySQL?)
so funtions that can be performed by relation algebra are a subset of the functions performed by a relational database? however isn't it required for a relational database to perform all the functions of relation algebra? Saqib Ali - http://validate.sourceforge.net --- XHTML/HTML/DocBook Validator On Tue, 28 Oct 2003, Jon Frisby wrote: No. Codd's rules defining what is a relational database are more specific than relational algebra can express. Essentially the requirements to be a relational database are a superset of the operations defined by relational algebra. -JF -Original Message- From: Saqib Ali [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 28, 2003 8:47 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Is MySQL Relational? (was: Foreigner keys in MySQL?) a question... is any DB that complies to the Relational Algebra a Relational DB??? Saqib Ali - http://validate.sourceforge.net --- XHTML/HTML/DocBook Validator On Tue, 28 Oct 2003 [EMAIL PROTECTED] wrote: I tend to agree with the past two posts that mysql is in fact relational, and would like to add that if we are going to make blanket statements that something IS or IS NOT that we qualify our responses. personlly i would like to know why Martijn views it as being a non relational db, without argument. If you just say its so, why am is supposed to take that statement over those that provide a full email of text like bluejack or John that provided additional information via a link. Just saying No, to a question like that does not lend to learning only to confusion. sorry about the mini rant jeff bluejack [EMAIL PROTECTED]To: [EMAIL PROTECTED] om cc: Subject: Is MySQL Relational? (was: Foreigner keys in MySQL?) 10/28/2003 11:38 AM 10/28/03 8:11:16 AM, Martijn Tonies [EMAIL PROTECTED] wrote: If you are trying to determine whether MySQL is a relational database, the answer is yes. Ehm... the answer is no. It's getting better, that's for sure. MySQL may or may not conform to some standard or another, and it may or may not perform the tasks that you, personally, want it to, but in fact it *is* a relational database, as opposed to a flat file or an object database. I recommended the questioner evaluate her needs against MySQL's available feature set, because if you are used to Oracle (or some other robust, commercial relational database) you may be surprised at some of the differences or absences in MySQL. But the questioner made it seem as though she was really just trying to get a feel for the basics scope of MySQL, and in that context, yes, MySQL is designed to a implement a relational database model as opposed to some other fundamental type of database. So, what's your laundry list of things MySQL should do? --bluejack -- 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/mysql? [EMAIL PROTECTED] -- 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]
corrupted passwd
Hello All, I think the passwd on my installation of mysql of mysql-standard-4.0.14-pc-linux-i686 is corrupted. I can't login with the root account. I was able to do so till last week. I trying to follow the instructions on resetting the passwd, but I get the following err msg. # ./mysqld --skip-grant-tables Fatal error: Please read Security section of the manual to find out how to run mysqld as root! Any ideas? Saqib Ali - http://www.xml-dev.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help optimizing query, awfully slow on only 20000 records
Are your tables indexed? http://www.mysql.com/doc/en/MySQL_indexes.html Saqib Ali - http://www.xml-dev.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: multiple ORs
If your data is properly Normalized, then this is the best way. Saqib Ali - http://www.xml-dev.com On Fri, 18 Jul 2003, Chris Edwards wrote: Hi This has probably come up before... Using version 3.23.54 I have multiple OR logic in the where clause. where id = 1 or id = 2 or id = 3 The list is finite, at about 20 ids. I know i can say where != if the the OR count is more than half the list items Is there any other way to provide optimization for this? Thanks. -- Chris Edwards Web Application Developer Outer Banks Internet, Inc. 252-441-6698 [EMAIL PROTECTED] http://www.OuterBanksInternet.com - Original Message - From: James [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, July 18, 2003 2:07 PM Subject: Replication Problem First off, this is the first time I have done replication in MySQL so the problem very well may be my fault. I run a simple master / slave setup that Replicates one database. Replication works for a day or two then for reasons unknown to me I see this on show slave status, Last_error: error 'Server shutdown in progress'... but Uptime on the Master and Slave both show dates that are older than when the error occurred. This problem has been occurring for ~ 2 weeks. 3 days ago I moved the Slave into the same rack as the master so they would be on the same subnet/switch, but the problem still exists. I could implement a perl script to just check the replication status, and take necessary action to restart the slave if the SQL thread is down, but I was wondering if a more elegant solution existed. Thanks James ([EMAIL PROTECTED]) [(none)] show slave status\G *** 1. row *** Master_Host: Replaced Master_User: Replaced Master_Port: Replaced Connect_retry: 60 Master_Log_File: MASTER-bin.010 Read_Master_Log_Pos: 131845183 Relay_Log_File: SLAVE001-relay-bin.004 Relay_Log_Pos: 257067861 Relay_Master_Log_File: MASTER-bin.010 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_do_db: MASTER Replicate_ignore_db: Last_errno: 1053 Last_error: error 'Server shutdown in progress' on query 'UPDATE LOW_PRIORITY thread SET views=views+1 WHERE threadid='154039'' Skip_counter: 0 Exec_master_log_pos: 108378574 Relay_log_space: 280534474 1 row in set (0.00 sec) ([EMAIL PROTECTED]) [(none)] select now(); +-+ | now() | +-+ | 2003-07-18 10:11:27 | +-+ 1 row in set (0.02 sec) ([EMAIL PROTECTED]) [(none)] start slave; Query OK, 0 rows affected (36.25 sec) ([EMAIL PROTECTED]) [(none)] status; -- mysql Ver 12.18 Distrib 4.0.12, for unknown-freebsd4.8 (i386) Connection id: 5239 Current database: Current user: [EMAIL PROTECTED] SSL:Not in use Current pager: /usr/bin/less Using outfile: '' Server version: 4.0.13 Protocol version: 10 Connection: Localhost via UNIX socket Client characterset:latin1 Server characterset:latin1 UNIX socket:/tmp/replication.sock Uptime: 3 days 15 hours 26 min 43 sec Then on my master. ([EMAIL PROTECTED]) [(none)] status; -- mysql Ver 12.20 Distrib 4.0.13, for unknown-freebsd4.8 (i386) Connection id: 71764 Current database: Current user: [EMAIL PROTECTED] SSL:Not in use Current pager: more Using outfile: '' Server version: 4.0.13-log Protocol version: 10 Connection: Localhost via UNIX socket Client characterset:latin1 Server characterset:latin1 UNIX socket:/tmp/mysql.sock Uptime: 16 days 19 hours 7 min 47 sec Threads: 92 Questions: 53552863 Slow queries: 698 Opens: 83017 Flush tables: 1 Open tables: 64 Queries per second avg: 36.901 -- ([EMAIL PROTECTED]) [(none)] select now(); +-+ | now() | +-+ | 2003-07-18 10:16:26 | +-+ 1 row in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- 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: multiple ORs
Paul, Does this optimize the query? I thought both of the queries will have the same relational algebra representation. Am I wrong? Thanks. Saqib Ali - http://www.xml-dev.com On Fri, 18 Jul 2003, Paul DuBois wrote: At 15:42 -0400 7/18/03, Chris Edwards wrote: Hi This has probably come up before... Using version 3.23.54 I have multiple OR logic in the where clause. where id = 1 or id = 2 or id = 3 The list is finite, at about 20 ids. I know i can say where != if the the OR count is more than half the list items Is there any other way to provide optimization for this? WHERE id IN(1,2,3,...) NULL cannot be in the value list. Thanks. -- Chris Edwards Web Application Developer Outer Banks Internet, Inc. 252-441-6698 [EMAIL PROTECTED] http://www.OuterBanksInternet.com - Original Message - From: James [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, July 18, 2003 2:07 PM Subject: Replication Problem First off, this is the first time I have done replication in MySQL so the problem very well may be my fault. I run a simple master / slave setup that Replicates one database. Replication works for a day or two then for reasons unknown to me I see this on show slave status, Last_error: error 'Server shutdown in progress'... but Uptime on the Master and Slave both show dates that are older than when the error occurred. This problem has been occurring for ~ 2 weeks. 3 days ago I moved the Slave into the same rack as the master so they would be on the same subnet/switch, but the problem still exists. I could implement a perl script to just check the replication status, and take necessary action to restart the slave if the SQL thread is down, but I was wondering if a more elegant solution existed. Thanks James ([EMAIL PROTECTED]) [(none)] show slave status\G *** 1. row *** Master_Host: Replaced Master_User: Replaced Master_Port: Replaced Connect_retry: 60 Master_Log_File: MASTER-bin.010 Read_Master_Log_Pos: 131845183 Relay_Log_File: SLAVE001-relay-bin.004 Relay_Log_Pos: 257067861 Relay_Master_Log_File: MASTER-bin.010 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_do_db: MASTER Replicate_ignore_db: Last_errno: 1053 Last_error: error 'Server shutdown in progress' on query 'UPDATE LOW_PRIORITY thread SET views=views+1 WHERE threadid='154039'' Skip_counter: 0 Exec_master_log_pos: 108378574 Relay_log_space: 280534474 1 row in set (0.00 sec) ([EMAIL PROTECTED]) [(none)] select now(); +-+ | now() | +-+ | 2003-07-18 10:11:27 | +-+ 1 row in set (0.02 sec) ([EMAIL PROTECTED]) [(none)] start slave; Query OK, 0 rows affected (36.25 sec) ([EMAIL PROTECTED]) [(none)] status; -- mysql Ver 12.18 Distrib 4.0.12, for unknown-freebsd4.8 (i386) Connection id: 5239 Current database: Current user: [EMAIL PROTECTED] SSL:Not in use Current pager: /usr/bin/less Using outfile: '' Server version: 4.0.13 Protocol version: 10 Connection: Localhost via UNIX socket Client characterset:latin1 Server characterset:latin1 UNIX socket:/tmp/replication.sock Uptime: 3 days 15 hours 26 min 43 sec Then on my master. ([EMAIL PROTECTED]) [(none)] status; -- mysql Ver 12.20 Distrib 4.0.13, for unknown-freebsd4.8 (i386) Connection id: 71764 Current database: Current user: [EMAIL PROTECTED] SSL:Not in use Current pager: more Using outfile: '' Server version: 4.0.13-log Protocol version: 10 Connection: Localhost via UNIX socket Client characterset:latin1 Server characterset:latin1 UNIX socket:/tmp/mysql.sock Uptime: 16 days 19 hours 7 min 47 sec Threads: 92 Questions: 53552863 Slow queries: 698 Opens: 83017 Flush tables: 1 Open tables: 64 Queries per second avg: 36.901 -- ([EMAIL PROTECTED]) [(none)] select now(); +-+ | now() | +-+ | 2003-07-18 10:16:26 | +-+ 1 row in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, Senior
Re: Sun Cobalt Web Server RTX
For my Cobalt server, I just d/l th mysql build from the Cobalt website (which is now @ sun.com) . And it worx just fine. Saqib Ali - http://www.xml-dev.com On Thu, 17 Jul 2003, Clint S. Jones wrote: What type of MySql can I install on a cobalt web server??? Please help... not sure... Clint S. Jones Computer Teks, Inc. President 1520 East Sunrise Blvd. Suite: 202 Fort Lauderdale, FL 33304 (954) 465-5516 (954) 767-6150 fax [EMAIL PROTECTED] www.webingenuity.net http://www.webingenuity.net/ www.computerteks.net http://www.computerteks.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Good Database Philosophy Book?
David, If you want to learn about Databases and normalization and Relational Algebra, a really good book is: Fundamentals of Database Systems, by Elmasri and Navathe and Understanding Relational Database Query Languages by SW Dietrich I have studied the first book, cover to cover, and I still use it for reference. It is the best books for understanding Relational Algebra and Normalization. The second book was written by my Professor Dr. Dietrich and is also a very good book. Saqib Ali - http://www.xml-dev.com On Wed, 9 Jul 2003, David Thompson wrote: Looking for a book to help me develop a philosophy for building databases (particularly on MySQL). And then taking them from concept to construction. Something like Start by asking which queries you will be performing, then define all data needed for each of those queries, then progress to normalize this data, etc. Any Ideasthanks... Regards, David Thompson President Provoix Corporation - Business Web Solutions www.provoix.com --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.497 / Virus Database: 296 - Release Date: 7/4/2003 -- 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]
NAS vs SAN for data directory
Hello All, I am trying to decide which storage I should use for the Data Directory. My application has lots of 'SELECT's (80%) and fewer UPDATEs/INSERTs(20 %). I have the following choices of Data storage 1) Xiotech SAN (66 Mhz FCAL) 2) NAS 3) Internal SCSI 3 RAID 5 Will I achieve any better performance using one storage vs the other storage? In the past (for about 2 years), I have I used SAN with 33Mhz FCAL, and the performance has been quite exceptional. And I have encountered no problems. Any suggestions? Thanks Saqib Ali http://www.xml-dev.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best Pratices for mySQL Backups in Enterprise
We are in the process of implementing enterprise wide (20,000+ users) application that will use mySQL as the Database engine. I was wondering if the slashdot readers can provide me some details about best practices / experiences for Backing Up and Restoring mySQL Databases. I am planning to setup a cron job, to lock the tables, use msqlhotcopy and then unlock the tables. Is that a good backup strategy? Is there a ready-made perl script that I can use? Is there a commercial solution for Backups? What other things do I need to keep in mind? What do other people do in production use, where the DB is frequently? I have already read mySQL: The definitive guide to to using, programming and administring mySQL 4, but did not find ay good information about backing up and restoring, in the 24x7 operation for Data Center use. Any ideas will highly appreciated. Thanks Saqib Ali -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Best Pratices for mySQL Backups in Enterprise
If your tables are InnoDB, you could look at InnoDB HotBackup: http://www.innodb.com/hotbackup.html No I m not using InnoDB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best Pratices for mySQL Backups in Enterprise
mysqlhotcopy does your locking for you. So running ./mysqlhostcopy dbase /path/to/backup/dir is perfectly safe while database operations (selct, insert, update etc) are being performed? --- Saqib Ali http://www.xml-dev.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
optimizing left join query
Hello All, I have the following 2 tables and LEFT join query as follows: mysql describe user_lic; +++--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +++--+-+-+---+ | license_id | bigint(20) | | PRI | 0 | | | user_id| bigint(20) | | PRI | 0 | | | location | char(30) | YES | | NULL| | +++--+-+-+---+ mysql describe user_info; +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | uid | int(10) unsigned | YES | | NULL| | | location | char(30) | YES | | NULL| | | mail | char(100)| YES | | NULL| | +--+--+--+-+-+---+ query = SELECT * FROM user_lic LEFT JOIN user_info ON user_lic.user_id=user_info.uid WHERE user_info.uid is NULL AND (user_lic.location = 'Rochester, US') When I run this query on a 'user_lic' table with 1000+ rows, it takes about 20+ mins to complete the query. However a similar query on a Oracle DB takes couple of minutes. I am using a high-end Sun Server, connected to a Xiotech SAN, using Fiber Optics. I dont think the Hardware is the bottle neck. I am wondering how I can optimize the Query/MySQL DB to make this query go faster. In Peace, Saqib Ali I fear, if I rebel against my Lord, the retribution of an Awful Day (The Day of Resurrection) Al-Quran 6:15 http://docbook.sc-icc.org - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: optimizing left join query
Diana, Thanks for the suggestion. I completely forgot to add the index/primary key. Now it works just fine :) Thanks again. In Peace, Saqib Ali I fear, if I rebel against my Lord, the retribution of an Awful Day (The Day of Resurrection) Al-Quran 6:15 http://docbook.sc-icc.org |+--- || Diana Soares | || [EMAIL PROTECTED]| || p.pt| || No Phone Info| || Available| || | || 02/11/2003 | || 08:59 AM | || | |+--- | | | | To: [EMAIL PROTECTED] | | cc: [EMAIL PROTECTED] [EMAIL PROTECTED] | | Subject: Re: optimizing left join query | | Your user_info table is not indexed... (user_info.uid should have an index) Check the manual: http://www.mysql.com/doc/en/MySQL_indexes.html On Tue, 2003-02-11 at 16:16, [EMAIL PROTECTED] wrote: Hello All, I have the following 2 tables and LEFT join query as follows: mysql describe user_lic; +++--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +++--+-+-+---+ | license_id | bigint(20) | | PRI | 0 | | | user_id| bigint(20) | | PRI | 0 | | | location | char(30) | YES | | NULL| | +++--+-+-+---+ mysql describe user_info; +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | uid | int(10) unsigned | YES | | NULL| | | location | char(30) | YES | | NULL| | | mail | char(100)| YES | | NULL| | +--+--+--+-+-+---+ query = SELECT * FROM user_lic LEFT JOIN user_info ON user_lic.user_id=user_info.uid WHERE user_info.uid is NULL AND (user_lic.location = 'Rochester, US') When I run this query on a 'user_lic' table with 1000+ rows, it takes about 20+ mins to complete the query. However a similar query on a Oracle DB takes couple of minutes. I am using a high-end Sun Server, connected to a Xiotech SAN, using Fiber Optics. I dont think the Hardware is the bottle neck. I am wondering how I can optimize the Query/MySQL DB to make this query go faster. In Peace, Saqib Ali I fear, if I rebel against my Lord, the retribution of an Awful Day (The Day of Resurrection) Al-Quran 6:15 http://docbook.sc-icc.org -- Diana Soares - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Not able to compile mysql-test
Hi, I have been working with mySQL and Apache for years on linux servers. But recently I purchased Solaris machine, and I am have problems compiling mySQL on it. I have - SunOS machine1 5.8 Generic_108528-10 sun4u sparc SUNW,Sun-Blade-100 - with gcc 2.95.3 20010315 And I am getting the following errors when I run the make command. And I am not using any weird option with configure. I m just using --prefix=/usr/local/mysql Making all in tests make[2]: Entering directory `/download/mysql-3.23.43/tests' make[2]: Nothing to be done for `all'. make[2]: Leaving directory `/download/mysql-3.23.43/tests' Making all in man make[2]: Entering directory `/download/mysql-3.23.43/man' make[2]: Nothing to be done for `all'. make[2]: Leaving directory `/download/mysql-3.23.43/man' Making all in sql-bench make[2]: Entering directory `/download/mysql-3.23.43/sql-bench' make[2]: Nothing to be done for `all'. make[2]: Leaving directory `/download/mysql-3.23.43/sql-bench' Making all in mysql-test make[2]: Entering directory `/download/mysql-3.23.43/mysql-test' make[2]: *** No rule to make target `all'. Stop. make[2]: Leaving directory `/download/mysql-3.23.43/mysql-test' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/download/mysql-3.23.43' make: *** [all-recursive-am] Error 2 Any help would be highly appreciated. Thanks Saqib Ali - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
load data local infile problems
Hi, I am creating a database for a university project. I say this because obviously certain things are not done the professional way due to time constraints. I've created a database, and entered the data into files. This is done because the teacher needs to see the files used for data entry. I then issue a command like: load data local infile Products.data into table Products; Occasionally I will get the response: Query ok, 29 rows affected (0.00 sec) Records: 29 deleted: 0 Skipped: 0 warnings: 0 But the vast majority of the time I get the response: Query ok, 0 rows affected (0.00 sec) Records: 29 deleted: 0 Skipped: 29 warnings: 0 I cannot seem to find any consistancy in MySQL's behaviour. This is an extremely annoying problem! If anyone out there can help then please do contact me. Thanks in advance. Regards, Saqib Shaikh Email: [EMAIL PROTECTED] Web site: http://www.saqibshaikh.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
loading data into a table and 1-many relations
Hi, I'm having a problem as follows: In my database (which just needs to work rather than be of commercial quality) I have an orders table. Each order contains an order number and a reference to ordered_items. Ordered_items table contains things like the name of the product and the price. Now obviously the orders-ordered_items link is a 1-to-many relationship since in any one order I should be able to order 5 different items. At the moment I am using: load data local infile orders.txt into table orders; to load the data. However, using this method I don't know how to enter say 10 ordered_item IDs into the tab-separated table contained in orders.txt. I'm sure that people on this list must make similar databases every day, and some basic help would be much appreciated. Regards, Saqib Shaikh - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
currency: which data type?
Hi, I'm interested to know what data type people consider suitable for storing currency. In a table in my database for a university assignment, i have: create table Products( ... price double(4,2) not null, ...); However it has been suggested to me that double(4,2) is inappropriate and that decimal or numeric would be more appropriate. I only used double(4,2) because this is what is used in the MySQL tutorial (I think it's chapter 3 of the manual). Any thoughts on this matter appreciated. Regards, Saqib Shaikh Email: [EMAIL PROTECTED] Web site: http://www.saqibshaikh.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: how to get a script to run/work
Hi, If you type mysql at the prompt you must interactively type commands at the mysql prompt and type quit to exit. However I think it's generally better in cases like yours to run in batch mode. Type: mysql pet.mysql. This will read the script from the file and execute it. If you want the output to be redirected to a file also add pets.out. In batch mode by default a concise format is used, but if you want tables to be drawn around the output like in interactive mode use the -t switch. Finally if you want the output to contain your typed commands use the -vvv switch. So you end up with: c:\mysql\scripts mysql -t -vvv pets.mysql pets.out I have only started using MySQL and only run it on a stand-alone system. If you are designing a database for a web site etc then you may need to provide extra parameters like the hostname. Hope this helps. Regards, Saqib Shaikh Email: [EMAIL PROTECTED] Web site: http://www.saqibshaikh.com/ - Original Message - From: Barbara Ferrell [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, November 11, 2001 3:47 AM Subject: how to get a script to run/work i want to convert my access database to mysql. and then it will run on a linux server if i ever figure out how to run these darn scripts though i downloaded mysql for windows 98. i have tried for a week to run these scripts and even reinstalled mysql and even tried it on another computer with nt4.0. this is where i saved mysql and the title of the script i want to run. C:\mysql\scripts\pet.sql i am sitting at the mysql prompt and i have the server/daemon running in the background. Please exactly what do i type after the mysql prompt?? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Create Table
Hi, For a university project I've recently started to learn to use MySQL. Obviously one of the first things I did, having read numerous tutorials was to create a database and attempt to add some tables. Unfortunately I have spent the past two weeks trying to do this presumably straight forward task and am sure that I am missing something. The problem occurs due to my wishing to place too many keywords after any attribute. If I create a simple table like: create table Products( ID int(7) unsigned not null unique zerofill default '000' auto_increment, primary key(ID)); I get an error message similar to: Error 1064 at line 3: You have an error in your SQL syntax near omitted text at line 2. After two weeks of changing combinations of keywords and deleting some and inserting others I finally found that the following command works: create table Products( ID int(7) unsigned zerofill default '000' not null unique auto_increment, primary key(ID)); So does anyone have any idea why the order of these keywords matters? In all the references on MySQL or SQL there is no mention of any specific order. While I've now got my first table working I'm still having difficulties getting other tables to work. Hopefully the answer to this question may shed some light on my difficulties. Thanks very much in advance. Regards, Saqib Shaikh Email: [EMAIL PROTECTED] Web site: http://www.saqibshaikh.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php