Re: how would mysqld restart affect dynamically set global variables?
On Wed, March 14, 2007 9:35, Bing Du said: Hi, We're running mysql 4.1.20. If I understand the manual correctly, I can change max_connections while mysqld is running without restart mysqld to make the change take effect. But what if mysqld restarts later in some other situations, like machine reboot, would my (global) change on max_connections remain? I'm just very clear when to make dynamic changes and when is better to put changes in my.cnf. Please advise. Thanks in advance, Bing Put the changes in dynamically. If they work change the my.cnf to make them survive the next time mysql is restarted (for example on a reboot). --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ENCODE() and DECODE()
On Mon, March 12, 2007 8:04, Neil Tompkins said: I've been researching the best method to store credit card numbers in a database which are saved encrypted. My version of mysql is 3.23 therefore I think the only function I could use is ENCODE() and DECODE(). I've tried these functions and they appear to work as I want. I've a couple of questions though, can I use varchar when saving the data and are these functions suitable for my requirements ? Thanks, Neil use 'Bin' versions of fields since the encoded data may be binary. Bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best Practice: Timezones and web development
On Tue, March 6, 2007 9:40, Marcus Bointon said: On 6 Mar 2007, at 17:12, David T. Ashley wrote: Best practice is that all times maintained in a database (or anywhere on the server) are UTC, and are only converted to local timezone and/or adjusted to daylight savings time as required to display data for a specific user. Exactly right. Now, as far as the best way to implement the two paragraphs above (especially with DST), I have not a clue. I do this using the date extension that was updated in PHP 5.1. I store the string representation of the time zone, for example 'Europe/ London', and set that as the time environment whenever a session is started using: http://www.php.net/manual/en/function.date-default- timezone-set.php After that it all just magically works - whenever you call date() and friends, it's all corrected for the time zone. A slightly harder question is how to get the user's time zone in the first place. You can take a wild guess according to their IP, but it could well be wrong. Next you could use Javascript to find out the local time and get an offset, but then you have no way of getting DST info, and it doesn't tell you where they really are. Finally, you can just ask - I've made the time zone a user preference, and most systems I've seen do the same. Marcus -- Marcus Bointon Synchromedia Limited: Creators of http://www.smartmessages.net/ [EMAIL PROTECTED] | http://www.synchromedia.co.uk/ Added problem. What if their computer clock is way off. Before I figured out session cookies I had cookies disappearing on random computers because the computer clock was a couple of days off. FWIW. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Daylight Savings Time Patch - easy check
On Wed, February 28, 2007 14:10, Ryan Stille said:
I am on 4.1.20-1.
Maybe your OS isn't patched?
Try this: SELECT @@global.time_zone;
Won't help if you are on debian which is still on 4.0.
If you get back SYSTEM, then MySQL is looking to the OS for timezone
data. And its only loaded when MySQL starts, so if you haven't
restarted MySQL since you patched your OS, you need to do that.
-Ryan
Jerry Schwartz wrote:
What version of MySQL are you using? I'm running 4.1.21, and that check
doesn't work even after I've updated (I think) the time zone tables.
I should probably eyeball the output of mysql_tzinfo_to_sql.
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
-Original Message-
From: Ryan Stille [mailto:[EMAIL PROTECTED]
Sent: Saturday, February 24, 2007 4:28 PM
To: mysql@lists.mysql.com
Subject: Re: MySQL Daylight Savings Time Patch - easy check
Ryan Stille wrote:
Paul DuBois wrote:
At 4:40 PM -0600 2/20/07, Ryan Stille wrote:
Is there an easy way to test to see if MySQL already has
the proper
tables loaded?
-Ryan
Yes, reload them. :-) After that, they're current! ...
After digging around on the net for a while I found an easy
way to tell
if your MySQL installation is ready for the new daylight savings time.
SELECT UNIX_TIMESTAMP('2007-03-11 02:00:00'),
UNIX_TIMESTAMP('2007-03-11 03:00:00');
This should return the same value, even though you are feeding it
different times, because this is when the 1 hr change occurs.
I get the
correct result on both of my machines. On one of them I've run the
suggested |mysql_tzinfo_to_sql command, on the other, the time zone
tables are completely empty!
Any wisdom on these time zone tables - are they ever used, should I
populate them or not?
-Ryan
|
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Fwd: RE: [PART 2/2] InnoDB - Different EXPLAINs for same query]
Please post to the list not to me personnally. Original Message Subject: RE: [PART 2/2] InnoDB - Different EXPLAINs for same query From: John Anderson [EMAIL PROTECTED] Date:Thu, January 18, 2007 10:24 To: William R. Mussatto [EMAIL PROTECTED] -- I optimized every table after I first imported the data. The tables were probably in use, off and on for testing, for about a week after the optimize table was ran on every table before I noticed this problem. I'm not saying the problem didn't exist within that week, I'm just saying I didn't notice it ;) . Another thing. Does the query optimizer keep any sort of statistics and use them to make decisions for future queries on the same table? If so, then that could be the problem because we have certain fields, containing only numbers, but were previously setup as varchars for some unknown reason. I changed them all to int types but some queries in obscure parts of our applications are still querying this field as if it were a character field, using LIKE, etc. I'm slowly but sure tracking those down and fixing them, I'm just curious if that could have anything to do with this strange behavior. Thanks, John A. -Original Message- From: William R. Mussatto [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 17, 2007 5:17 PM To: mysql@lists.mysql.com Subject: Re: [PART 2/2] InnoDB - Different EXPLAINs for same query Just a thought, did you try running Optimize Table from the MySQL Administrator. I'm thinking that when you restarted it re-examined the table statistics and was able to pick a better index. On Wed, January 17, 2007 14:31, John Anderson said: mysql SELECT SQL_NO_CACHE COUNT(rb.subscription_id) as recurring_cc_count, - SUM(rb.grace_price) as recurring_cc, - COUNT(sb.subscription_id) as single_cc_count, - SUM(sb.initial_amt) as single_cc - FROM customerdetail a - LEFT JOIN recurringbilling rb - ON a.subscription_id = rb.subscription_id - LEFT JOIN singlebilling sb - ON a.subscription_id = sb.subscription_id - LEFT JOIN (global.subscriptionExchangeRate as ser INNER JOIN global.currencyCodes as cur) - ON (a.subscription_id = ser.subscriptionId AND ser.billedCurrencyCode = cur.currencyCode) - WHERE client_accnum = '12345' - AND a.trans_timestamp - BETWEEN '2007010800' AND '20070108235959'; ++--+-+---+ | recurring_cc_count | recurring_cc | single_cc_count | single_cc | ++--+-+---+ | 4 | 119.80 | 0 | NULL | ++--+-+---+ 1 row in set (0.40 sec) mysql explain SELECT SQL_NO_CACHE COUNT(rb.subscription_id) as recurring_cc_count, - SUM(rb.grace_price) as recurring_cc, - COUNT(sb.subscription_id) as single_cc_count, - SUM(sb.initial_amt) as single_cc - FROM customerdetail a - LEFT JOIN recurringbilling rb - ON a.subscription_id = rb.subscription_id - LEFT JOIN singlebilling sb - ON a.subscription_id = sb.subscription_id - LEFT JOIN (global.subscriptionExchangeRate as ser INNER JOIN global.currencyCodes as cur) - ON (a.subscription_id = ser.subscriptionId AND ser.billedCurrencyCode = cur.currencyCode) - WHERE client_accnum = '12345' - AND a.trans_timestamp - BETWEEN '2007010800' AND '20070108235959'; ++-+---++--- -+-+-+-- -+--+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra| ++-+---++--- -+-+-+-- -+--+--+ | 1 | SIMPLE | a | range | client_idx,trans_idx,accno_trans_idx,accnumactive,accsubactive | accno_trans_idx | 7 | NULL |4 | Using where; Using index | | 1 | SIMPLE | rb| eq_ref | PRIMARY | PRIMARY | 8 | company.a.subscription_id |1 | | | 1 | SIMPLE | sb| eq_ref | PRIMARY | PRIMARY | 8 | company.a.subscription_id |1 | | | 1 | SIMPLE | ser | ref| PRIMARY,billedCurrencyCode | PRIMARY | 8 | company.a.subscription_id |1 | | | 1 | SIMPLE | cur | eq_ref | PRIMARY | PRIMARY | 2 | global.ser.billedCurrencyCode |1 | Using index
Re: [PART 2/2] InnoDB - Different EXPLAINs for same query
for the customerdetail table which has much better cardinality (almost 1 key per row). The obvious workaround is use FORCE INDEX(accno_trans_idx) in the query for now, but I'm beginning to think this may be a bug. Has anyone else had any similar issues? I haven't found anything like this in the bug database though. If anyone doesn't see anything blatantly wrong with my setup, I'll submit this as a bug. Further information: Here is how MySQL is configured, and the my.cnf I'm using. CC=gcc -m64 CXX=g++ -m64 \ ./configure --prefix=/usr \ --sbindir=/usr/sbin \ --libexecdir=/usr/sbin \ --infodir=/usr/share/man \ --mandir=/usr/share/info \ --libdir=/usr/lib64 \ --enable-shared \ --enable-static \ --enable-thread-safe-client \ --enable-local-infile \ --with-extra-charsets=all \ --with-gnu-ld \ --with-pthread \ --with-unix-socket-path=/tmp/mysql.sock \ --with-mysqld-user=mysql \ --without-debug \ --with-openssl=/usr \ --with-big-tables \ --with-archive-storage-engine \ --with-csv-storage-engine \ --with-blackhole-storage-engine \ --with-federated-storage-engine \ --with-berkeley-db \ --with-berkeley-includes=/usr/include \ --with-berkeley-libs=/usr/lib64 \ --without-extra-tools \ --with-mysqlmanager=no \ --with-ndbcluster \ --without-geometry -- [mysqld] #Directories datadir=/var/db/mysql socket=/tmp/mysql.sock log-error=/var/log/mysql/mysql.log pid-file=/var/run/mysqld/mysqld.pid tmpdir=/tmp #Replication server-id=127 #log-bin replicate-ignore-db=mysql #log-slave-updates #Network max_connections=1024 max_allowed_packet=1024M net_buffer_length=16k #Files open_files_limit=8192 # Anything higher needs corresponding ulimit entry #Buffers join_buffer_size=128M key_buffer_size=512M key_buffer=512M large_pages max_heap_table_size=1024M myisam_sort_buffer_size=256M read_buffer_size=64M read_buffer=64M query_cache_size=32M query_cache_type=1 record_buffer=512 sort_buffer=512M table_cache=512 thread_cache=4M thread_stack=512K thread_cache_size=300 thread_concurrency=16 tmp_table_size=1G #innodb innodb-table-locks=off transaction_isolation=REPEATABLE-READ innodb_buffer_pool_size=1024M innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=0 innodb_log_file_size=128M innodb_additional_mem_pool_size=32M innodb_thread_concurrency=16 innodb_commit_concurrency=4 innodb_flush_method=O_DIRECT innodb_open_files=8192 innodb_sync_spin_loops=32 innodb_thread_sleep_delay=1000 innodb_autoextend_increment=1024M innodb_file_per_table=TRUE [client] socket=/tmp/mysql.sock John Anderson --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OT: LAMP appliance for non-profit use
Have you looked at ubuntu distribution of linux. I think they have a 'school' variant which might be close to what you need. Also, the people there might be able to give you more help. Good luck. On Thu, December 7, 2006 9:39, Saqib Ali said: 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
test email
Sorry, but I haven't gotten any emails from the list and I'm trying to determine if there is a blockages. Sorry to disturb the list. --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Alter Table Add Column - How Long to update
On Thu, October 19, 2006 18:24, Ow Mun Heng said: Just curious to know, I tried to update a table with ~1.7 million rows (~1G in size) and the update took close to 15-20 minutes before it says it's done. Is this kind of speed expected? I don't really understand how the alter table add column is done, but when I look at the show processlist I see that it says the state is copying into tmp table Does the alter table mean that MySQL has to copy the Entire table, row-by-row into a temporary table, and add in the additional column (or 2)?? I'm using InnoDB by the way Basic process for any change which modifies the structure of the table is to create a temporary table with the new structure, copy the information from the old table table to the new one (modifying as needed to match the new structure), drop the old table and rename the new table to the old tables name. So the time might be realistic. It depends on the hardware you are using and what else is going on on the system. Hope this helps. --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple JOIN on three tables
On Wed, October 18, 2006 12:46, spacemarc said: Hi I have 3 tables with the same fields. I would want to find the data that they are comprised in the time interval: SELECT a.*, b.*, c.* FROM tab1 a, tab2 b, tab3 c WHERE a.date between '-MM-DD' and '-MM-DD' OR b.date between '-MM-DD' and '-MM-DD' OR c.date between '-MM-DD' and '-MM-DD' ORDER BY a.date DESC But this query returns all the fields duplicated. Where it is mistaken? -- http://www.spacemarc.it Well that's what you asked it to do, if you look closely at your query. How are table a, b, and c related to each other? Do you want all the information from each of the tables (which are NOT related to each other) between the specified dates? If that is the case you are looking at a UNION rather than a strait JOIN. --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple JOIN on three tables
On Wed, October 18, 2006 13:21, spacemarc said: ok, instead I use (SELECT * FROM tab1 WHERE mydate between 'the-date1' and 'the-date2' ) UNION (SELECT * FROM tab2 WHERE mydate between 'the-date1' and 'the-date2' ) etc But if I wanted to use a join I can make it however or not? -- http://www.spacemarc.it A JOIN implies that the tables are related, that is, a column (or columns) in table a has the same value as a column (or columns) in table b. Thats what they mean when they call it a RELATIONAL database. If the tables don't have such a relationship you are asking for the UNION of the separate information from table a, where it meets its WHERE conditions, and the information from table b where it meets its WHERE conditions. The fact that both where conditions are the same doesn't matter. Hope this helps. --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple JOIN on three tables
On Wed, October 18, 2006 13:37, spacemarc said: 2006/10/18, Peter Brawley [EMAIL PROTECTED]: You can join on any row(s) you like. What are you trying to acccomplish? I simply want to select the records from my three tables that are comprised in time interval. Now, the first table comprises the records until to 2004 year; the second table unitl 2005 and the third table until 2006. If a user select a date (example) between 2005 and 2006 I want to select the data from two tables -- http://www.spacemarc.it Then it is really a UNION. I hope you have the date field as an index otherwise you are looking at a table scan which is always slow. --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RE: How to rewrite query
Would it not be best to have the field with the fewest repeats (i.e., the closest to unique) first, or is that what you meant. Bill On Tue, October 17, 2006 10:12, Jerry Schwartz said: I didn't think of that (combinations). You are probably right. Due to my background, I tend not to think a lot about multi-column indices. I would think that you want field with the most possible values first, then the next, etc. Is that what you were thinking? Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 17, 2006 12:05 PM To: Jerry Schwartz Cc: mos; mysql@lists.mysql.com Subject: Re: RE: How to rewrite query I agree that individual fields have relatively few possible values - hopefully, when those are combined in a multi-column index, he will have a greater number of unique combinations, gaining more out of the index. That's why I suggested putting stype and Is_id as the first two fields in the index (though I guess I did not mention that!). stype had 6 values, Is_id had 5, so he may have up to about 30 combinations as the first two fields, which should be enough to help a lot. Dan On 10/17/06, Jerry Schwartz [EMAIL PROTECTED] wrote: I would think that with so few possible values for all but the ip field, indexing the other fields would accomplish nothing. In fact, I'd be surprised if the optimizer didn't realize that and do a sequential read anyways. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Tuesday, October 17, 2006 10:46 AM To: mysql@lists.mysql.com Subject: Re: How to rewrite query At 08:34 AM 10/17/2006, you wrote: Hello, For the Radius server we're using MySQL cluster and the following query looks too slow: select ip from ipaddr where pool='INTERNET' and stype='S' and ls_id=3 and allocated is null limit 1; Table ipaddr is small (~6MB, 38000 records). Fields in WHERE clause have few values and no indexes: - pool: 2 distinct values; - stype: 6 distinct values; - ls_id: 5 distinct values; - allocated is null for ~3 of records. Table type is NDB. If I change it to MEMORY everything starts to fly. Of course there are a lot of updates to ipaddr table too. For every select there are 3 updates. But updates are of type update something where ip=ipaddr and ipaddr is unique key. What can cause slowdown in NDB case? Table is small and is in memory (5.0 cluster). Maybe I can rewrite it in some better form for such case? MySQL setting are basically default. I did not find something in documentation about improving performance of NDB engine tables. Maybe increase read_buffer_size which is currently the default 128k? Server has 4GB of memory and runs x86_64 version of CentOS4 Linux. Thanks, Mindaugas Mindaugas, If your queries are always using those fields, why not create a single compound index on those fields? This shouldn't slow down inserts that much, and if they do, you could always use delayed inserts. Mike -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Windows Server Configuration
Just noticed that you said partitions. I am assuming that you meat multiple drives in a raid array. Bill David Lazo said: Thanx again. For the time being, we will keep 4 drives with Dan's suggestion. OS and MySQL running from there. On 8/25/06 11:03 AM, Dan Buettner [EMAIL PROTECTED] wrote: James, with just 4 drives, you can set up one big RAID 10 disk (usually called a logical disk, with Dell PERCs I think it's a container), and then partition it for your different needs. If you have 4 73 GB disks, you probably have around 135 GB formatted capacity with RAID 10; I'd do something like this for my own MySQL server in that situation: 20 GB C partition for OS and software binaries 10 GB D partition for MySQL temp space 20-40 GB E partition for MySQL binary logs (if you're using them) remainder F partiition for MySQL data directory Your needs will vary depending on whether this server does only MySQL or other serving as well, how big your databases are, whether you want to keep binary logs for some period of time, and how large those binary logs are. I agree with David's response that you want redundancy for the OS as well. Drives fail, plain and simple. The single best thing you can do with servers is plan for hardware failure. Having your data on redundant disks is great, but if your OS is on a single drive, when (not if, when) that one fails, your data is redundant but still unavailable. You may pay a small performance penalty having the OS on the same physical drives with your MySQL, but I'd make that sacrifice for the redundancy, no question. On the other hand if you want to add a couple of drives and make a separate RAID 1 pair for the OS, go for it. Best, Dan On 8/25/06, JamesDR [EMAIL PROTECTED] wrote: -- 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: file i/o operations...
A couple of comments: - Simultaneous connections can be increased, but at some point the user than runs the mysqld process will run out of file handles it can allocate (each table takes 2 or 3). - If we are talking about a database server and test server being the same box then what are you trying to test. Once you exceed the number of processors on the box, the OS will just queue up the various processes and that will be the limit of scalablity. Unless you overlap real I/O with computation there is not much gain beyond a certain point. When you run out of memory for processes, its page to disk time (not a pleasent site). Not sure what you are testing here. BTW: please expain the 'black hole table'. Jut my $0.1 worth. Bill Brent Baisley said: Just getting that number of processes running I think would be a challenge. A setup I recently worked on runs a few hundred processes per box, and that kind of maxes out the CPU. Approach 1, been there, done that. Too messy. Approach 2, considered it, but you may end up with processes that never connect. You would need a queueing/scheduling mechanism. Essentially you would be trying to do what an OS does, manage resources to make sure every process gets it's turn. Approach 3, what we currently use. The processes connect to the db, does a bulk insert and then disconnects. We decided to limit each process to blocks of 100. Inserting a single record at a time will quickly degrade. This setup actually moved the bottleneck from the database to the processes doing their job. When each process starts, it inserts a record into a table and gets it's id. The process then handles the autoincrement value. The unique id for each record is then the process id plus the increment value. To really scale, you may want to look into the black hole table format. Essentially it's a black hole, nothing is saved so there really isn't much overhead. But you set it up to be replicated and a replication log is generated. An easy setup would be to have multiple tables on a master server, each table replicating a black hole table from another server. Then create a merge table encompassing the multiple tables for easy querying. This is the next idea we are pursueing, so it may or may not work. - Original Message - From: bruce [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, August 25, 2006 1:12 PM Subject: file i/o operations... hi... i'm trying to determine which is the better way/approach to go. should an app do a great deal of file i/o, or should it do a great deal of read/writes to a mysql db... my test app will create a number of spawned child processes, 1000's of simultaneous processes, and each child process will create data. the data will ultimately need to be inserted into a db. Approach 1 --- if i have each child app write to a file, i'm going to have a serious hit on the disk, for the file i/o, but i'm pretty sure Centos/RH could handle it. (although, to be honest, i don't know if there's a limit to the number of simultaneous file descriptors that the OS allows to be open at the same time.) i'm assuming that the number is multiples of magnitudes more than the number of simultaneous connections i can have with a db i could then have a process/app collect the information from each output file, writing the information to the db, and deleting the output files as required. Approach 2 -- i could have each child app write to a local db, with each child app, waiting to get the next open db connection. this is limited, as i'd run into the max connection limit for the db. i'd also have to implement a process to get the information from the local db, to the master db. .. Approach 3 --- i could have each child app write directly to the db.. the problem with this approach is that the db has a max regarding the number of simultaneous connections, based on system resources. this would be the cleanest solution.. so... anybody have any thoughts/comments as to how one can essentially accept 1000's-1's of simultaneous hits with an app... i've been trying to find out if there's any kind of distributed parent/child/tiered kind of app, where information/data is more or less collected and received at the node level... does anyone know of a way to create a distributed kind of db app, where i can enter information into a db on a given server, and the information is essentially pulled into the master server from the child server... thanks -bruce -- 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:
Re: 1 to many relationships
Gerald L. Clark said: Brian E Boothe wrote: hey guys ; it's not Customers Per issue it's the other way around issues per customer i enter in Customer 1 and then down the road i wanna add a issue associated with that customer, so goto that customer and Click Add issue the a form Comes up and i add the issue and Click Add issue, i need that issue to associate with that customer, every Customer Refrences the same issue table Then it is many to many. Many customers are associated with many issues. i have Customers /Issues as tables, -- Gerald L. Clark Supplier Systems Corporation I beg to differ. It would be a many to many if several customers were related to the same issue. If each customer can have one or more issues, but no issue can 'belong' to more than one customer then its 1 to many and you can put the customer ID in the issues table. If several customers can have the same issue (row) then you will need an intermediate table which has two columns: customerID and issueID. --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LIMIT Question
Have your where clause select the customer, sort result by autoincrement column DESC limit 50 Only problem is the order would be the inverse (i.e., last in first displayed) from your goal. Only way I can thing to go around this would be to select into a temp table and then sort that into the desired sequence. Just a thougth. Dirk Bremer said: Dan, That might be close. The rows are inserted with an auto-increment primary key, but I have no ready way of knowing what the latest 50-IDs are. There are also various date columns, but I won't readily know the dates in this scenario. The goal of the query, which currently returns all of the results, is to find all entries for a given customer regardless of when they occurred. I would like no more than the last 50 rows inserted for this customer (this could be based upon the auto-increment value) and would prefer to have them ordered within the 50-possible results in the order they were inserted, from lowest-ID to the highest-ID. This will prevent the query from showing possible hundreds of results. There are multiple customers in the table. Dirk Bremer - Senior Systems Engineer - ESS/AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2503 [EMAIL PROTECTED] www.nisc.coop -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 28, 2006 15:28 To: Dirk Bremer Cc: mysql@lists.mysql.com Subject: Re: LIMIT Question Depends what you mean by last - you could show the 50 with the latest datestamps by ending your query with something like: ORDER BY datestampcolumn DESC LIMIT 50; or the 50 with the highest ID numbers, same thing: ORDER BY id DESC LIMIT 50; only real problem there is then they're sorted highest to lowest, but it is still the last 50. Dan On 6/28/06, Dirk Bremer [EMAIL PROTECTED] wrote: Is there a way to use a LIMIT clause to show the last X amount of rows or a way to emulate this behavior? For example, a table has somewhere between 1000 and 2000 rows, but you just want to see the last 50. These last 50 might be the most recent entries, for example. Can this be done in single query? Dirk Bremer - Senior Systems Engineer - ESS/AMS - NISC Lake St. Louis MO - USA Central Time Zone 636-755-2652 fax 636-755-2503 [EMAIL PROTECTED] www.nisc.coop -- --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Connection Pooling
romyd misc said: Hi Everyone, I'm developing an application using C# .NET and mysql as database. It's a multithreaded application, we open a mysql database connection at the very beginning when the application is started and all the database requests use the same connection. But under stress or when more than one request try to access database, i get object reference errors. I don't get this error when frequency of database calls is low. Does it sounds like i need to implement connection pooling? I tried to lookup online, but couldn't find any help under mysql documentation. Can someone help me setting up mysql connection pooling with C#.NET. Thanks in advance, Romy Your comment about one connection for all of the threads disturbs me. Your application will have to ensure that each thread is finished with the connection and returns it to the pool. Two threads cannot, at the same time, use the same connection. Say thread A had performed a select which returned 2000 row resultset. Until that thread had read in all 2000 rows, they would still be in the connection. If thread B tried to use the same connection and asked for a different result set when thread A went back for the rest of its results where would they be? When you put stress on your application this is more likely to happen. What a pool does is allow your threads to formally release their connections back to the pool when they are done with them and re-aquire them later without the full overhead to going all the way back to the server to open a connection. Instead you go to some intermediate point where a set of threads are already avaiable (in Apache its at the child level and in java its at the container level). Bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: New User Setting up MYSQL
You might want to look get the mysql admin tool. It will call the query browser if needed (so don't get rid of that), but its what you use to supervise the server (set up users etc.) Hope this helps. Chuck Wildeman said: Hi, I am very new to mysql. In the past I was using Access. I don't have any other experience setting up databases. I bought a book on MYSQL and downloaded the installation file to one of our server and then installed it using most of the defaults. I am now at my XP Pro laptop and want to access the database and start doing things such as importing table from access etc. I wasn't exactly sure what to use for a front end so I choose MYSQL Query browser. I am having trouble connecting to the newly created database. MYSQL Query Browser comes up with a screen that asks for a couple of things. Under stored connection I put in OCRPDC which is the name we use for the server I put this on. For stored host I put in the IP address of this server. I left the port as 3306 and arranged for this port to be open during the installation. Under username I have tried both root and cwildeman. I thought during the installation it asked for a user ID and this is what I plugged in. For a password I have used both my normal windows password which I use for many things and our administrator password which I thought I used during the installation. I wasn't sure what to use to Default Schema so I just put in test. I keep getting a connection error number 2003 stating I can't connect to MYSQL server. Is there something that someone can suggest? Thanks, Chuck --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table keys
fbsd_user said: create table members ( logon_idvarchar(15) NOT NULL, email_addr varchar(30) NOT NULL, member_type char(1), email_verified char(1), logon_pwvarchar(15), date_added date, last_login timestamp, count_of_logons INT, first_name varchar(30), last_name varchar(30), primary key login_id (login_id), UNIQUE INDEX email_addr (email_addr)); --- When doing a insert row, if the logon_id value is all ready in the table I get a dup id msg. This is fine and what I want to happen. But when inserting a row with a unique logon_id value that has a email_addr that is already used by some other logon_id, mysql allows the insert. This is not the action I want. I need to be able to do lookup by logon_id or by email_addr and retrieve the row. I can do that now, but if 2 logon_id's have the same email address I get both rows. I need the email address to be unique across all rows. How can I change this table definition so email_addr is unique across all rows of the table? I read the manual about 'unique index' options, but still don't comprehend what the manual says. Also as you can see I do not select a engine type, is there some engine type better suited and or faster for the way I am trying to use the keys? Thanks for your advice and help. Close but you need to specify that the index is UNIQUE (see changes above) --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LVM-snapshot + mysqldump -- is this a reasonable backup
George Herson said: James G. Sack (jim) wrote: On Mon, 2005-09-19 at 13:14 -0400, George Herson wrote: James G. Sack (jim) wrote: On Mon, 2005-09-19 at 12:15 -0400, George Herson wrote: Dear Jim, Re: your post at http://lists.mysql.com/mysql/189058, why bother creating the mysqldump if you already have the snapshot? Why not just backup the snapshot? [...] The output of mysqldump *is* the backup. If the db goes away, it can be restored with (something like) mysql dumpfile.2005-09-01. The dump operation is run periodically, and some number of back versions can be kept around (or offloaded) for archival value. Yes, but can't you also save your snapshot instead, then, when/if you want, restore it, 4. mount the snapshot 5. load a second database server daemon accessing the db within the snapshot (with a suitable alternate my.cnf file) 6. perform mysqldump operation on the snapshot-db ?? .. George, LVM snapshots are generally intended to be short-lived -- snip ..jim Jim, I didn't word my question quite right because I was only guessing at what a LVM snapshot was. Moreover, what you're saying is all correct. However, I was not suggesting that the snapshot be kept around once the backup is made. Let's go to article What is a Logical Volume Manager (LVM) snapshot and how do I use it? http://kbase.redhat.com/faq/dml_fetch.pl?CompanyID=842ContentID=4097FaqID=3640word=What%20is%20a%20Logical%20Volume%20Managerfaq_template=http://kbase.redhat.com/faq/searchfaq.shtmtopic=80back_refr=http://kbase.redhat.com/faq/topicname=AS/ES/WS%20BasicsId=Instance=Shared= in the RedHat k'base http://www.redhat.com/apps/support/knowledgebase/. It says After performing the backup of the snapshot partition we release the snapshot. This implies, at least to me, that one doesn't need a 2nd database server or to do a mysqldump (your steps 5-7). Instead, we just tar cv /mnt/ops/dbbackup (to use the article's example name for the mounted snapshot), save the tape, and dispense with the snapshot. Wouldn't that work? MySQL keeps its data in files already, so why is it necessary to mysqldump it? Are you only trying to avoid having to also backup the mysqld version that wrote the data files to ensure that these can be read later? George The LVM snapshot will hold the state of the database as it is written to the disk. However, the database engine may have to write several things to the disk to ensure consistency (e.g, main table and index). If you take the snapshot between these two operations the database will be in an inconsistent state. Mysqldump locks the tables before performing a dump to prevent this. --- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Difference between Blob and varchar binary
I was storing some 8 bit information in a varchar binary field (encrypted stuff) and I think its getting corrupted. I thought they were (except for size) interchangable? I'm running debian GNU Linux and haven't moved to sarge so I'm still on 3.23.x but will be moving shortly. --- William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Difference between Blob and varchar binary
Gleb Paharenko said: Hello. In my opinion, one of the causes of the problem can be the processing of trailing spaces in varbinary fields. See: http://dev.mysql.com/doc/mysql/en/binary-varbinary.html Thanks, you may be right since the results are too short. William R. Mussatto [EMAIL PROTECTED] wrote: I was storing some 8 bit information in a varchar binary field (encrypted stuff) and I think its getting corrupted. I thought they were (except for size) interchangable? I'm running debian GNU Linux and haven't moved to sarge so I'm still on 3.23.x but will be moving shortly. --- William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sharing a Database Between Websites
David Blomstrom said: I discovered by accident that I can link any website on my reseller account to one database. That would be far more convenient than working with six separate databases, and it would also cut down on file size overall, since there are certain tables that I share between websites. But my host warned me that a big database could increase query time and make it harder to update. I want to understand exactly how this works. The problem would be large tables rather than large database. Traversing a table is what costs time. There would be a slight increase in the Operating System finding the files associated with each table, but this is a factor only with very large numbers of tables not 100. This all assumes MyISAM table type. I don't know about the others since they do share a single file. Suppose I have two database tables and one website that's linked to both those tables. Suppose it takes one second to query those tables. Now if I add 100 tables, but my website still queries just two of them, will a query still take one second? Or will those additional tables slow things down, even though my website doesn't even make any reference to them? And will those extra websites make it take increasingly longer to add additional tables or modify existing tables? I would think my local database ought to be a good guide. I have over 100 tables in the same database on my computer, and things seem to work just fine. Of course, I realize things take longer online. My webpages do run a little slow, but I think that's because of some sloppiness in designing my database tables; that's something I'll just have to refine as I learn more about MySQL. Thanks. __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- 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 pooling problem
Eric Bergen said: Apache 1.3 with php (not sure about tomcat) is a pre forked daemon. Any connection pooling numbers will be per child. Try setting your connection numbers per child (for most php apps this is 1) and let us know what happens. I'm guessing it will create as many connections as there are apache children. For more info see the fork() man page. -Eric Actually the connection is on per worker process. Tomcat response to apache's children's requests, so it would be, at worst, one connnection per tomcat worker, which should be a smaller number than apache children/threads. There are methods for tomcat workers to also share a connection pool, but I've never used them. For my needs dropping and creating connections has been better (shared servers). I'd look at the tomcat connection pooling information on the apache site as a place to start rather than the web-sever. Hopefully you have configured tomcat to just handle the .jsp page not ALL PAGES. On Mon, 7 Mar 2005 17:04:47 -0500, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Scott Purcell [EMAIL PROTECTED] wrote on 03/07/2005 04:59:27 PM: Thanks Sean, I am using standalone Apache/Tomcat 4.1.31 mysql \s -- mysql Ver 12.21 Distrib 4.0.15, for Win95/Win98 (i32) Connection id: 184 SSL:Not in use Server version: 4.0.15-max-debug Protocol version: 10 Connection: localhost via TCP/IP Client characterset:latin1 Server characterset:latin1 TCP port: 3306 -- mysql I am using Java to code with and I am using a driver called: org.gjt.mm.mysql.Driver My connection class is too large to submit to the list, or I would submit it. I guess what I would like to be able to do is create some type of object that I could use to do pooling that is maybe inside the container? I hope this helps, Scott It would be happy to try to help if I worked in or on either of those platforms (Apache + Java). I am Win32(IIS), ASP (VBScript/JavaScript) using MyODBC as my connection library. Sorry but I can't help in any way shape or form. I have CC-ed the description of your operating and development environments to the list to see if anyone offers to help. Maybe next time! Shawn Green Database Administrator Unimin Corporation - Spruce Pine -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, March 07, 2005 3:48 PM To: Scott Purcell Cc: mysql@lists.mysql.com Subject: Re: database pooling problem Scott Purcell [EMAIL PROTECTED] wrote on 03/07/2005 04:29:18 PM: Hello, I am working with a traditional database pooling class basically putting connection objects into a Vector. When I run the code from a main, it appears solid. I have run 10s of thousands of back to back queries and all is good. Problem is when I connect it to a web-app, and reboot the webserver a couple of times, I get a message from the driver stating Too many connection and I am done. Upon thinking about this, I figured my traditional Vector of connections, is getting created over and over, each time someone bumps the web server with a new class, etc. So I start out with 10 connections, then another 10 and another 10 and eventually I guess the driver has no more connections to give? Anyway, this list is possibly the wrong, list, but I would hope there are some developers here that may lend a hand or link. So I am not happy with what I have created nor the way it works on the web server. Does anyone have any ideas how I should handle this? in a web-based environment. Thanks, Scott No matter which list you submit to, you need to provide more details. Technically what you are asking is a little off-topic (more about making a pool work with a web server than just making the connection, right?) But if you bring the question to the list, someone _may_ be able to pitch in and help. Now exactly WHO can help (and to what degree they can help) completely depends on WHICH web server you use (there are dozens, you know...), HOW you initialize your pool (again many methods available), HOW you take and return connections from your pool (ditto), and which languages are in use in this entire process (again dozens of options). Just telling us that you use a web-server did not provide nearly enough information as there are literally thousands of combinations of ways to do what you described. Please tell whichever list you submit to: What OS you are using Which web server you are using What languages you are using And you will need to supply some code examples, too. The list members will need to be able to see your code so that they can see what is wrong with it. Best of luck, Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- Eric
RE: insert data
Gerald Preston said:
Michael,
This is the actual code except for the :
my $dbh = DBI-connect( 'DBI:mysql:database=club', '', '', {
PrintError = 0 } ) or die $DBI::errstr;
my $sql = insert into wolfies( group_name, me, daily, item, unit,
qty,
amount, tax, total )
values( ?, ?, ?, ?,?,?,
?,
?, ? ) ;
my $sth = $dbh-prepare( $sql ) or die $dbh-errstr if $dbh-err;
$sth-execute( $group_name, $me, $daily, $item, $unit, $qty, $amount,
$tax, $total ) or warn Cannot execute FIRST Statement!!\n$DBI::errstr;
Question? When I created the database club, is there anything I needed
to do concerning permissions or anything?
I am lost here. I have been writing code on a SUN Oracle systems for
over five years.
Regards,
Jerry
Did you 'grant' user access to all the tables in database club?
-Original Message-
From: Michael Stassen [mailto:[EMAIL PROTECTED]
Sent: Monday, February 28, 2005 9:29 AM
To: Gerald Preston
Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com
Subject: Re: insert data
From perldoc DBD::mysql
use DBI;
$dsn = DBI:mysql:database=$database;host=$hostname;port=$port;
$dbh = DBI-connect($dsn, $user, $password);
So it's not a syntax problem. Even if it were, we should detect the
error long before calling prepare or execute.
Perl is quite clearly telling you what is wrong. Originally, you got
Can't call method prepare on an undefined value.
for the line
my $sth = $dbh-prepare( $sql );
which means that $dbh is undefined at the time of the call to prepare.
Now, you are getting
Can't call method execute on an un undefined value
for the line
$sth-execute( $group_name, $me, $daily, $item, $unit, $qty, $amount,
$tax, $total ) or warn Cannot execute FIRST
Statement!!\n$DBI::errstr;
which means that $sth is undefined at the time of the call to execute.
Are you showing us select lines of your code, rather than the actual
code? My best guess right now is that you haven't taken into account
that my is a scoping operator in perl, and in the lines you haven't
showed us, the variables in question ($dbh or $sth) go out of scope.
Michael
John Doe wrote:
Am Sonntag, 27. Februar 2005 22.19 schrieb Gerald Preston:
Hi Gerald
The object used:
my $dbh=DBI-connect( 'DBI:mysql:database=club', 'xxx, 'x', {
PrintError = 0} ) or die $DBI::errstr;
I didn't see this part in your first post :-)
Hmm... I've never seen a '=' in the first argument passed to
DBI-connect...
Here's an functional example I'm using:
my $db ='database';
my $host ='hostname';
my $port ='1234';
$dbh=DBI-connect(DBI:mysql:$db:$host:$port,
'a_username',
'a_password',
{RaiseError=1,
AutoCommit=1})
or die $0: $DBI::errstr; }
So, try using club instead of database=club, and a hostname too.
greetings joe
[nothing new below]
-Original Message-
From: John Doe [mailto:[EMAIL PROTECTED]
Sent: Sunday, February 27, 2005 6:37 AM
To: mysql@lists.mysql.com
Subject: Re: insert data
Hi Gerald
I am trying to insert data for the first time using MySQL. In Oracle
I used the following:
# my $sql = insert into bar( group_name, me, daily, item, unit,
qty, amount, tax, total )
# values( ?, ?, ?, ?,?,?,
?, ?, ? ) ;
my $sth = $dbh-prepare( $sql );
die $dbh-errstr if $dbh-err;
$sth-execute( $group_name, $me, $daily, $item, $unit, $qty,
$amount,
$tax, $total ) || die Cannot execute FIRST
Statement!!\n$DBI::errstr;
I keep getting Can't call method prepare on an un undefined value.
All the name listed are correct by looking at MySQLAdmin1.3\4.
Apart from David Logan's answer:
You have to create the $dbh object first (man DBI); the undefined
value in
the error message refers to that.
HTH
joe
--
William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: moving DB from one box to another.
Ian Sales said: Randy Paries wrote: The problem is I get this wonderfully verbose :-( error message :::ERROR at line 362701 - MySQL can't parse something on line 362701 of your dump file. One possibility is an index named with a keyword, which 3.23.x appears to allow but 4.0.x doedsn't. Edit the dump file and see. - ian Or it could be that a table name is now a reserved word. DIV in my case -- William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql client that does export and import
Duhaime Johanne said: Hello I have looked at MySql browser and MySqlAdministration and then in the mysql lists but I could not find what I was looking for. A developper is working with one of the many databasees we have. We want to allow him to import and export data from this database throught a window client (mysql in on a unix machine). Can someone suggest something? Thank you in advance Johanne Duhaime IRCM Is he at a fixed IP. If so, create a username @ that IP with proper permissions. He can install myodbc on the windows box and then use access. The firewall's (if any) will have to be adjusted so that traffic on the correct port is allowed from only that IP. Just a thought. -- William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ok now connector/J doesn't work.
William R. Mussatto said:
Mark Matthews said:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
William R. Mussatto wrote:
Keith Ivey said:
William R. Mussatto wrote:
I've been googling for 1/2 hr w/o any answers. sorry if I've missed
the obvious.
Problem. Fresh install of mysql 4.7.1, AS perl 5.8
DBI and DBD-Mysql via ppm.
Also new connector/J version
Client does not support authnticaiton protocol
What version of MySQL were you using previously? If it was 4.1.0 or
earlier, then this
might be useful reading:
http://dev.mysql.com/doc/mysql/en/Password_hashing.html
--
Keith Ivey [EMAIL PROTECTED]
Washington, DC
Thanks, I'll check it out. I did the development under linux using
3.23.xx and perl 5.6 Basically debian stable.
From the article it looks like I'll have to fall back to a 4.0
version.
I was hoping, but using a clean install on the windows box to avoid
these issues.
I'll try SET PASSWORD FOR 'some_user'@'some_host' =
OLD_PASSWORD('mypass');
William,
You'll need a copy of DBD::MySQL that's linked with libmysql from 4.1
to get around this issue (and use the new server-side prepared
statements as well). Patrick Galbraith (a MySQL engineer) has pushed
the required changes into the CVS repo of DBD::MySQL, however that
hasn't been released yet as a binary by the DBD::MySQL maintainer
(DBD::MySQL version 2.9015)
Thanks that worked, now to see what happends when I try w/connector/J
As long as you're using a recent version of Connector/J (3.0.15 or
3.1.4), it'll work fine.
Regards,
-Mark
Tried it and now I get connection timed out after three tries. I'm
running the testbed using the tomcat buried in netbeans. Cut and pasted
the example from the on line docs. Put the jar in the WEB-INF/lib
dirctory. Compiles fine.. I'm using the in line driver registration
rather than modifying the
web.xml file.
Tested user on the same box with command line client using the
--host=localhost and it connected fine (had run grant all for the
'username'@'localhost' earlier. Any thoughts?
Of course I've been using your old mm version with 3.23.x (to match our
debian install).
With 4.7.1 both the old mm connector and the current connector/J both
time out. Anythink I should check. And no. there is no local firewll
installed on the box.
Follow up w/specific error reports:
SQLException: Server connection failure during transaction.
Attempted reconnect 3 times. Giving up.
SQLState: 08001
VendorError: 0
--
William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [PHP-DB] Upgrading mySQL
Warning!! Make sure the php module will handle the new password scheme in 4.1. DBD::mysql from AS will not. There are work arounds. I'm having to fall back to 4.0 because I can't get .jsp to work with 4.1 database (no connection). GH said: What I have been asking for... is that the upgrade does not tell me *HOW* to install this upgrade. WHich file I should download etc I do not see this under the UPGRADE section... That is what I have been asking Also, it says on the manual (section 2.10) It is a good idea to rebuild and reinstall the Perl DBD::mysql module whenever you install a new release of MySQL. The same applies to other MySQL interfaces as well, such as the PHP mysql extension and the Python MySQLdb module. However I do not know how to do this and was asking how to for PHP 4. On Mon, 22 Nov 2004 15:55:25 +0100, Ingo Strüwing [EMAIL PROTECTED] wrote: Hi GH, apart of the contents of the mentioned documents and hints, there is nothing else. Just start the new server in place of the old server with the same options unless told otherwise in the mentioned documents. Am Mo, den 15.11.2004 schrieb GH um 15:06: when I go to both 2.10 Upgrading MySQL (http://dev.mysql.com/doc/mysql/en/Upgrade.html) and 2.10.2 Upgrading from Version 4.0 to 4.1 (http://dev.mysql.com/doc/mysql/en/Upgrading-from-4.0.html) it does not tell me how to upgrade it only tells me about important changes and recomendation. I am looking for a guide of what commands I need to use and so forth. As I said in the inital mailing I made, I am a newbie to Linux. It also says that It is a good idea to rebuild and reinstall the Perl DBD::mysql module whenever you install a new release of MySQL. The same applies to other MySQL interfaces as well, such as the PHP mysql extension and the Python MySQLdb module. [But now how to] Regards, Ingo -- Ingo Strüwing, Senior Software Developer MySQL AB, www.mysql.com Office: +49 30 43672407 Are you MySQL certified? 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: Ok now connector/J doesn't work.
Hassan Schroeder said: William R. Mussatto wrote: Tried it and now I get connection timed out after three tries. I'm running the testbed using the tomcat buried in netbeans. Frankly, the whole concept of running a server from within an IDE sounds so sketchy to me -- but I'm definitely not an IDE kinda guy :-) Have you thought of trying this from a standalone Tomcat? Wouldn't take long to install and test... FWIW! -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com Dropped back to 4.0.22 server and it works. I guess I shouldn't have jumped into 4.1 so soon. Buried in netbeans is a tomcat 4.06 in my installation, has all the directories and so forth. The idea was that you could plub in someone else's JSP Container and test with that. I am now breathing a lot easier. Still some errors but I'm going to bring over a clean copy of the stuff and try to see what's going on. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Ok now connector/J doesn't work.
Mark Matthews said: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 William R. Mussatto wrote: Tried it and now I get connection timed out after three tries. I'm running the testbed using the tomcat buried in netbeans. Cut and pasted the example from the on line docs. Put the jar in the WEB-INF/lib dirctory. Compiles fine.. I'm using the in line driver registration rather than modifying the web.xml file. Tested user on the same box with command line client using the --host=localhost and it connected fine (had run grant all for the 'username'@'localhost' earlier. Any thoughts? Of course I've been using your old mm version with 3.23.x (to match our debian install). With 4.7.1 both the old mm connector and the current connector/J both time out. Anythink I should check. And no. there is no local firewll installed on the box. Follow up w/specific error reports: SQLException: Server connection failure during transaction. Attempted reconnect 3 times. Giving up. SQLState: 08001 VendorError: 0 What is the error message with the _new_ version of Connector/J (it should have more information in it, as the code that throws the exception you give looks like this: throw new SQLException( Server connection failure during transaction. Due to underlying exception: ' + connectionException + '. + (this.paranoid ? : Util.stackTraceToString( connectionException)) + \nAttempted reconnect + this.maxReconnects + times. Giving up., SQLError.SQL_STATE_UNABLE_TO_CONNECT_TO_DATASOURCE); ) That 'underlying exception' bit will be important for anyone who is trying to figure out why you're having problems! -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Client Connectivity Office: +1 708 332 0507 www.mysql.com Dropped back to server version 4.0.22 and it works (at least to the extent of getting normal jsp errors rather than connection errors). I'll try to look at it later, but I've lost about a week w/4.1 connectivity issues so I have to get what works out. ? I thought I was using the latest current stable version of connector/J. ? Do you want me to try it with the development version? Bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Ok now connector/J doesn't work.
Mark Matthews said:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
William R. Mussatto wrote:
Keith Ivey said:
William R. Mussatto wrote:
I've been googling for 1/2 hr w/o any answers. sorry if I've missed
the obvious.
Problem. Fresh install of mysql 4.7.1, AS perl 5.8
DBI and DBD-Mysql via ppm.
Also new connector/J version
Client does not support authnticaiton protocol
What version of MySQL were you using previously? If it was 4.1.0 or
earlier, then this
might be useful reading:
http://dev.mysql.com/doc/mysql/en/Password_hashing.html
--
Keith Ivey [EMAIL PROTECTED]
Washington, DC
Thanks, I'll check it out. I did the development under linux using
3.23.xx and perl 5.6 Basically debian stable.
From the article it looks like I'll have to fall back to a 4.0
version.
I was hoping, but using a clean install on the windows box to avoid
these issues.
I'll try SET PASSWORD FOR 'some_user'@'some_host' =
OLD_PASSWORD('mypass');
William,
You'll need a copy of DBD::MySQL that's linked with libmysql from 4.1 to
get around this issue (and use the new server-side prepared statements
as well). Patrick Galbraith (a MySQL engineer) has pushed the required
changes into the CVS repo of DBD::MySQL, however that hasn't been
released yet as a binary by the DBD::MySQL maintainer (DBD::MySQL
version 2.9015)
Thanks that worked, now to see what happends when I try w/connector/J
As long as you're using a recent version of Connector/J (3.0.15 or
3.1.4), it'll work fine.
Regards,
-Mark
Tried it and now I get connection timed out after three tries. I'm
running the testbed using the tomcat buried in netbeans. Cut and pasted
the example from the on line docs. Put the jar in the WEB-INF/lib
dirctory. Compiles fine.. I'm using the in line driver registration
rather than modifying the
web.xml file.
Tested user on the same box with command line client using the
--host=localhost and it connected fine (had run grant all for the
'username'@'localhost' earlier. Any thoughts?
Of course I've been using your old mm version with 3.23.x (to match our
debian install).
With 4.7.1 both the old mm connector and the current connector/J both time
out. Anythink I should check. And no. there is no local firewll installed
on the box.
--
William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What changed in 4.1.7 to break DBD::Mysql
Michael Stassen said: Rudy Lippan wrote: On Mon, 15 Nov 2004, Patrick Galbraith wrote: William, I'm not sure about this, because it involves windows/active state, and those ppm packages used for active state perl are pre-compiled to run. I'll talk to the current maintainer about this and see if he knows about how active state packages their DBD ppm, or if he's heard of this issue (I haven't before) Interesting. When this comes up on the linux/bsd/unix side of things, I just tell people to either recompile against the latest mysql client libraries, or set the mysql server to accept the older passwords. But now with active state, it probably gets a little more complicated. My guess is that active state is packaging DBD::mysql with an older version of the mysql libraries, and I don't know if the new client libraries will talk to older versions of MySQL (Can anyone confirm this)?, but if it is the case that the newer mysql client libraries can't talk to the older versions of mysql, then active state would have to release two version of DBD::mysql, one compiled to talk 4.1.7 and one to talk to everthing else. Or activestate just says that you have to use the old passwords with 4.1.7 (Oh, and what about the shutdown changes?) Rudy New clients can talk to old servers. This is documented in the manual http://dev.mysql.com/doc/mysql/en/Password_hashing.html. Michael Yes I know NOW. Its a problem with DBD being an OLD client. I also asked the question from the other direction on the DBI maillist and I don't know if the DBD::mysql maintainer was aware of the implications. He was focusing on stored procedures (a 5.x option I thougth). A warning should go into the Perl interface section of the manual (~~Temporarily you will have to use OLD_PASSWORD until DBD::mysql is updated~~ or some such). Once you know what the problem is you can look in the correct section of the manual, but until then I didn't know where to look. But it was Monday. Bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql-4.1.7 and client library - Client does not support authentication protocol requested by server; consider upgrading MySQL client
They are successful to install mysql-4.1.7 on o.s. solaris 8 thanks to
the suggestions of Gleb Paharenko. Now when use webmin (last version
1.170) o phpmyadmin (last version -2.6.0-pl2) I have this problem:
DBI connect failed : Client does not support authentication protocol
requested by server; consider upgrading MySQL client
From shell it works all the solution to the problem exists? Or it
is better install the version 4.0.21?
Thank's
Alessio
I had a similar problem with DBI/DBD. The solution I used was to create
the passwords using OLD_PASSWORD('plaintextPassword') function
Look at http://dev.mysql.com/doc/mysql/en/Password_hashing.html for the
details as well as work arounds. When they update DBD::mysql this should
go away.
--
William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
What changed in 4.1.7 to break DBD::Mysql
I've been googling for 1/2 hr w/o any answers. sorry if I've missed the
obvious.
Problem. Fresh install of mysql 4.7.1, AS perl 5.8
DBI and DBD-Mysql via ppm.
Client does not support authnticaiton protocol
DBI connect('cjuhsdinfo','{username}',...) failed: Client does not support
authentication protocol requested by server; consider upgrading MySQL
client at stdconfig.
ph line 2
DBI::__ANON__[C:/Perl/site/lib/DBI.pm:660]('undef', 'undef')
called at C
:/Perl/site/lib/DBI.pm line 662
DBI::connect('DBI', 'dbi:mysql:cjuhsdinfo', '{username}',
'{password}') called
at stdconfig.ph line 2
Any thoughts on how far I need to fall back to get around this?
--
William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What changed in 4.1.7 to break DBD::Mysql
Keith Ivey said:
William R. Mussatto wrote:
I've been googling for 1/2 hr w/o any answers. sorry if I've missed
the obvious.
Problem. Fresh install of mysql 4.7.1, AS perl 5.8
DBI and DBD-Mysql via ppm.
Client does not support authnticaiton protocol
What version of MySQL were you using previously? If it was 4.1.0 or
earlier, then this
might be useful reading:
http://dev.mysql.com/doc/mysql/en/Password_hashing.html
--
Keith Ivey [EMAIL PROTECTED]
Washington, DC
Thanks, I'll check it out. I did the development under linux using
3.23.xx and perl 5.6 Basically debian stable.
From the article it looks like I'll have to fall back to a 4.0 version.
I was hoping, but using a clean install on the windows box to avoid these
issues.
I'll try SET PASSWORD FOR 'some_user'@'some_host' = OLD_PASSWORD('mypass');
Thanks that worked, now to see what happends when I try w/connector/J
--
William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What changed in 4.1.7 to break DBD::Mysql
Mark Matthews said:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
William R. Mussatto wrote:
Keith Ivey said:
William R. Mussatto wrote:
I've been googling for 1/2 hr w/o any answers. sorry if I've missed
the obvious.
Problem. Fresh install of mysql 4.7.1, AS perl 5.8
DBI and DBD-Mysql via ppm.
Client does not support authnticaiton protocol
What version of MySQL were you using previously? If it was 4.1.0 or
earlier, then this
might be useful reading:
http://dev.mysql.com/doc/mysql/en/Password_hashing.html
--
Keith Ivey [EMAIL PROTECTED]
Washington, DC
Thanks, I'll check it out. I did the development under linux using
3.23.xx and perl 5.6 Basically debian stable.
From the article it looks like I'll have to fall back to a 4.0
version.
I was hoping, but using a clean install on the windows box to avoid
these issues.
I'll try SET PASSWORD FOR 'some_user'@'some_host' =
OLD_PASSWORD('mypass');
William,
You'll need a copy of DBD::MySQL that's linked with libmysql from 4.1 to
get around this issue (and use the new server-side prepared statements
as well). Patrick Galbraith (a MySQL engineer) has pushed the required
changes into the CVS repo of DBD::MySQL, however that hasn't been
released yet as a binary by the DBD::MySQL maintainer (DBD::MySQL
version 2.9015)
Thanks that worked, now to see what happends when I try w/connector/J
As long as you're using a recent version of Connector/J (3.0.15 or
3.1.4), it'll work fine.
Regards,
-Mark
- --
Mark Matthews
MySQL AB, Software Development Manager - Client Connectivity
Office: +1 708 332 0507
www.mysql.com
Thanks, I figured I just hit it at the wrong time (4.1.7 out but DBD not
yet updated). the connector/j just came down from mysql.com so it should
be current. I do most of my production work on debian linux but wanted to
give this client the current stuff.
--
William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What changed in 4.1.7 to break DBD::Mysql
Patrick Galbraith said:
William,
What was the error that you had specifically? Did you use a precompiled
version of DBD::mysql like a RPM, or did you build it yourself? I don't
know if this is a DBD version issue that you are experiencing, because
nothing was changed in authentication protocol. The main thing that
I've coded into the newer version of DBD::mysql is support for prepared
statements.
regards,
Patrick
The DBD::mysql is from Active State's repository via the ppm auto install
process. The version they supply supports only the older short passwords.
Since I missed the change in 4.1.x to longer passwords I was unable to
connect to the newly installed mysql using the DBI interface. I'm not
sure of clean way other than what I did (force the use of old_password's.
I have other boxes which run older mysql which have to support the old
interfaces. The only think I can think to do (assuming you can't ask
which version is supported) is to try the long and then the short version
of the passwords, or to allow the default behavior of the DBI- connect
function to be altered by a parameter.
On Nov 15, 2004, at 5:14 PM, William R. Mussatto wrote:
Mark Matthews said:
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
William R. Mussatto wrote:
Keith Ivey said:
William R. Mussatto wrote:
I've been googling for 1/2 hr w/o any answers. sorry if I've
missed
the obvious.
Problem. Fresh install of mysql 4.7.1, AS perl 5.8
DBI and DBD-Mysql via ppm.
Client does not support authnticaiton protocol
What version of MySQL were you using previously? If it was 4.1.0
or earlier, then this
might be useful reading:
http://dev.mysql.com/doc/mysql/en/Password_hashing.html
--
Keith Ivey [EMAIL PROTECTED]
Washington, DC
Thanks, I'll check it out. I did the development under linux using
3.23.xx and perl 5.6 Basically debian stable.
From the article it looks like I'll have to fall back to a 4.0
version.
I was hoping, but using a clean install on the windows box to avoid
these issues.
I'll try SET PASSWORD FOR 'some_user'@'some_host' =
OLD_PASSWORD('mypass');
William,
You'll need a copy of DBD::MySQL that's linked with libmysql from 4.1
to
get around this issue (and use the new server-side prepared
statements as well). Patrick Galbraith (a MySQL engineer) has pushed
the required changes into the CVS repo of DBD::MySQL, however that
hasn't been released yet as a binary by the DBD::MySQL maintainer
(DBD::MySQL version 2.9015)
Thanks that worked, now to see what happends when I try
w/connector/J
As long as you're using a recent version of Connector/J (3.0.15 or
3.1.4), it'll work fine.
Regards,
-Mark
- --
Mark Matthews
MySQL AB, Software Development Manager - Client Connectivity
Office: +1 708 332 0507
www.mysql.com
Thanks, I figured I just hit it at the wrong time (4.1.7 out but DBD
not
yet updated). the connector/j just came down from mysql.com so it
should
be current. I do most of my production work on debian linux but
wanted to
give this client the current stuff.
--
William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061
Patrick Galbraith Senior Software Developer
[EMAIL PROTECTED] http://www.mysql.com
Whatever action a great man performs, common men follow. Whatever
standards he sets by exemplary acts, all the world pursues --
Bhagavad Gita
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0 and concat
A bit of a warning, if the fields are DATETIME rather than DATE, add the appropriate hours:minutes:seconds to the test WHERE datetimefield '1999-02-01 23:59:59'. When comparing DATETIME fields with date strings '00:00:00' is assumed and that can cause problems if one forgets that. Have you considered NOT comparing dates as strings but rather as date values? That will avoid the use of CONCAT() completely. SELECT * FROM sampletable WHERE datefield = '1999-01-12' and datefield '1999-02-01' This example query will get all of the records from sampletable that were entered after January 11th and before February 1st. It will also be **very** fast if the column datefield is indexed. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Alfredo Cole [EMAIL PROTECTED] wrote on 10/11/2004 09:43:14 AM: Hi: I have a problem in that all statements that include concat execute very slowly. For instance, if I have three fields in string format that represent a year, month and day, and want to issue a select like: select * from cxcmanpag where contact (year,month,day)=stringYear+stringMonth+stringDay (simplified), then it will take a long time, againts a table with only around 100,00 records. If I rewrite the statement to read: select * from cxcmanpag where year=stringYear and month=stringMonth and day=stringDay, it will execute considerable faster, but will not produce the same results. I have looked in the manual, and also read High Performance MySQL from Zawodny and Balling, and MySQL from Paul Dubois, but none of them seem to address this issue. Can somebody point me to a URL or book that I should be reading to improve, this, or how to avoid using concat altogether? Thank you. -- Alfredo J. Cole Grupo ACyC www.acyc.com - www.clshonduras.com - SolCom -- William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bad quot;too many connectionsquot; error (os x)
Michael Winston said: On Sep 1, 2004, at 9:10 AM, V. M. Brasseur wrote: Michael Winston wrote: Hi- We've been running into a pretty serious problem for the past several versions of mysql 4.0 running on OS X (both client and server). Every once in a while we wake up to find the too many connections error coming up. There really aren't too many connections (we have our max set to 99) - it's the type of message that appears when a wrong password is used too many times (and I'm 100% sure this isn't happening). Now, the problem is that once this message starts appearing we can't even connect with mysqladmin as root. That extra connection that mysql promises doesn't exist. The only way we can shut down mysql is to perform a 'kill -9' (then restart the server and repair all the tables). And we can't reproduce this problem at will. This is driving us nuts. Before I report this as a bug I wanted to know if anyone else has seen something like this or has any suggestions of how to narrow down the problem. Thanks! Michael We've run into this problem ourselves, also using 4.0 but on a 64bit AIX. The problem we found was that some queries were firing off threads which never ended. These threads blocked other threads, which blocked other threads... A logjam resulted with all connections ended up being used by the offending threads. The fix was to *ahem* fix our queries so they'd close their database connections once they were complete. You may wish to do a code inspection and verify that every open connection has a matching close. Hmmm. All of our connections are coming from php-generated web pages. PHP automatically closes the connection at the end of the script. Unless I completely misunderstand how this stuff works. Plus, this problem only happens once every few weeks. If some of our queries are causing this, I would expect the problem to occur more often. I'll look into this, though. Thanks, Michael Actually php may be using connection pooling which will NOT close off the connections. It can be configured either way in the apache php configuration file. I use squirrelmail, which is a php based webmail system with MySQL holdin configuration information and those connections never close, they just go inactive. There is one connection for each apache child. Since apache doesn't use the same child the answer the query each time, on a busy server you may end up with quite a few connecitons. Just a thougth. -- William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ANNC: MySQL Administrator 1.0.11
Alfredo Kengi Kojima said: MySQL Administrator 1.0.11 has been released. MySQL Administrator is a GUI management console for MySQL, with support for tasks such as managing users, configuring MySQL, performing backups, editing table definitions etc. More information at: http://www.mysql.com/products/administrator/ You can download sources and binaries for Windows and Linux from: http://dev.mysql.com/downloads/administrator/ This release contains a few bug fixed relating to user administration and the table editor. -- Alfredo Kojima, GUI Developer MySQL AB, www.mysql.com Buenos Aires, Argentina You should warn people that it doen't support 3.x series I believe. Since that is all that is in the Debian std. release this is an issue. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with Mysql 4.0.18 + Debian
Thanks for the two responses.
William Mussatto said:
Are you running mod_perl?
Yes I am, with Perl 5.8.3.
Victor Pendleton said:
What does mysql show processlist look like?
Here is what it looks like currently, but the system is not in its
unresponsive phase right now. I can't force it to go all wonky on me,
it will probably be tomorrow before the process count explodes again.
++-+---+-+-+--+---+--+
| Id | User| Host | db | Command | Time | State | Info
|
++-+---+-+-+--+---+--+
| 8 | citidel | localhost | citidel | Sleep | 0| | NULL
|
| 71 | citidel | localhost | citidel | Sleep | 2192 | | NULL
|
| 72 | citidel | localhost | citidel | Sleep | 2141 | | NULL
|
| 78 | citidel | localhost | citidel | Sleep | 1503 | | NULL
|
| 79 | citidel | localhost | citidel | Sleep | 1503 | | NULL
|
| 87 | citidel | localhost | citidel | Sleep | 741 | | NULL
|
| 88 | citidel | localhost | citidel | Sleep | 730 | | NULL
|
| 89 | citidel | localhost | citidel | Sleep | 607 | | NULL
|
| 95 | citidel | localhost | citidel | Query | 0| NULL | show
processlist |
++-+---+-+-+--+---+-
Ryan Richardson said:
-Original Message-
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: 8/2/04 9:16 AM
Subject: Problem with Mysql 4.0.18 + Debian
Hello:
I posted this before but I have not gotten a response. I have a
Debian
(woody) server running a good sized database (7.2GB of db files),
Mysql 4.0.18. I am running Apache 1.3.29 + perl and using mysql as
the backend. In my.cnf, I have max_connections=300.
Here's the problem. I had the site up several days, with everything
running perfectly. Ordinarily there would be about 11 mysql processes
running. However, after a few days of running smoothly, the number of
mysql processes increases to over 170, and the site crashes. Formerly
I'd
get errors like
DBI connect('yada yada, ...) failed: Too many connections at DB.pm
line 25
However once I set max_connections to 300 (default is 90), mysql will
still accept connections, but it is still way too slow to be usable,
so the website becomes unreachable.
I've read on this list that people running MySQL w/ FreeBSD can have
similar sounding problems. I am wondering if there is a connection.
I know that the site is getting virtually no traffic, so the problem
is not that it is being overloaded. I have tried this scenario at
least a dozen times, and the same thing always happens.
Ryan
While I am not a mod_perl expert it does create a connection for each
child. I'm not clear on the pooling of these connections but you might
check the number of apache children next time it goes un-responsive. Do
each of your perl scripts include an explicit ' $dbh-disconnect;'? That
may be needed to return things to the pool. You might also make sure that
the $dbh is 'my'ed and that its scope is less than the entire program.
For example putting it inside of set of { } and ensuring that the program
flow falls out the bottom so the $dbh goes out of scope. I run in a
multi-virtual host environment so mod_perl is conter productive for us so
my experience is limited. I'd look at mod_perl and $dbh connection
pooling on google. I'd be interested in what you find. We have a php
mail system and I've had to increase the number of connections because of
its connection pooling.
--
William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB TableSpace Question
Jeff Mathis said: my understanding is that the datafiles are created when the server initializes, and this this is the designed and expected behavior. Most other database products use a similar model. Your scenario cannot happen. You specify how many innodb data files and how large in your config file. when the server starts, it allocates all the space you requested. if the server cannot find the space at startup, you get an error. if during an import the file size is exceeded, you get an error and the import stops. you cannot overrun your disk. jeff [EMAIL PROTECTED] wrote: I agree with David. If there is no present way to recover unused InnoDB tablespace, then we (as a community) seriously need to create a tool to do just that. How have we gone so long without it? I always assumed it was possible (I guess I have been just lucky enough to not need to do it yet) What if, during the course of a major data import, I try something that creates a working table that expands my datafile to fill my available disk space. I might have made a logical error or not. Regardless of why it filled up, without the ability to reclaim that room, an entire server could be royally scr***d (assuming a server that supports a mix of InnoDB and other table types). Please tell me there is something other than a dump-delete-import that can be used to shrink InnoDB tablespaces. Shawn Green Database Administrator Unimin Corporation - Spruce Pine David Seltzer [EMAIL PROTECTED] wrote on 08/03/2004 12:42:03 PM: Thanks Marc, Is there really no way to reclaim unused space in an InnoDB table space? If not, why is this not considered a tremendous limitation? -Dave Seltzer -Original Message- From: Marc Slemko [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 03, 2004 12:31 PM To: David Seltzer Cc: [EMAIL PROTECTED] Subject: Re: InnoDB TableSpace Question On Tue, 3 Aug 2004 10:07:25 -0400 , David Seltzer [EMAIL PROTECTED] wrote: Hi all, I've been searching the archives mysql documentation for a while and I can't seem to find an answer to my question - Is there a way to force InnoDB to shrink its filesize? I just dropped a 7GB table, but it hasn't freed up the disk space and I need it back. From what I've been reading, a restart will cause this to happen, but I'm in a production environment, and I'm afraid that InnoDB will take its sweet time while my users are holding their breath. Does anyone have any experience with this? No, a restart will not shrink it. Currently the only option I can think of is to do a dump and restore, using mysqldump (since innodb hot backup just copies the data file, it won't be of any use in shrinking it). A number of products allow the extants to be added onto automatically when the initial assignment is exceeded. I thought one of the newer versions of MySQL did that as well but I don't know if its in a stable release yet. -- William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Problem with Mysql 4.0.18 + Debian
Victor Pendleton said:
What does mysql show processlist look like?
-Original Message-
From: [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: 8/2/04 9:16 AM
Subject: Problem with Mysql 4.0.18 + Debian
Hello:
I posted this before but I have not gotten a response. I have a
Debian
(woody) server running a good sized database (7.2GB of db files), Mysql
4.0.18. I am running Apache 1.3.29 + perl and using mysql as the
backend. In my.cnf, I have max_connections=300.
Here's the problem. I had the site up several days, with everything
running perfectly. Ordinarily there would be about 11 mysql processes
running. However, after a few days of running smoothly, the number of
mysql processes increases to over 170, and the site crashes. Formerly
I'd
get errors like
DBI connect('yada yada, ...) failed: Too many connections at DB.pm line
25
However once I set max_connections to 300 (default is 90), mysql will
still accept connections, but it is still way too slow to be usable, so
the website becomes unreachable.
I've read on this list that people running MySQL w/ FreeBSD can have
similar sounding problems. I am wondering if there is a connection.
I know that the site is getting virtually no traffic, so the problem is
not that it is being overloaded. I have tried this scenario at least a
dozen times, and the same thing always happens.
Ryan
Are you running mod_perl?
--
William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with Mysql 4.0.18 + Apache
Hello:
I posted this before but I have made some changes since then (not that
they helped much). I have a Debian (woody) server running a good sized
database (7.2GB of db files), Mysql 4.0.18. I am running Apache 1.3.29
+ perl and using mysql as the backend. In my.cnf, I set
max_connections=300.
Here's the problem. I had the site up for weeks, with everything
running perfectly. Ordinarily there would be about 11 mysql processes
running (according to ps). However, when I switched my domain name over
to this server, immediately the number of mysql processes increases to
over 150, and the site crashes. Formerly I'd get errors like
DBI connect('yada yada, ...) failed: Too many connections at DB.pm line
25
However once I set max_connections to 300 (default is 90), mysql will
still accept connections, but it is still way too slow to be usable, so
the website becomes unreachable.
I know that the site is getting virtually no traffic, so the problem is
not that it is being overloaded. I have tried this scenario at least a
dozen times, and the same thing always happens. Is this a known issue
with Apache + mysql 4.0.18?
Ryan
Are you using mod_perl by any chance? You might check to make sure that
you are calling 'disconnect' at the end of each call. With mod_perl
connections may stay open. I see a similar problem with php. Also,
mod_perl will cause the children to be larger.
--
William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Where are BLOBs / TEXTs stored?
for ISAM storage or internals. Ed -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, June 17, 2004 9:00 AM To: [EMAIL PROTECTED] Cc: emierzwa; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Where are BLOBs / TEXTs stored? [EMAIL PROTECTED] wrote on 17/06/2004 15:35:36: I am curious about this, too. However, I don't think that you answer the original question. Are BLOBs stored as separate files, one file per object? Are they combined into a single large BLOB file? are they aggregated into several medium sized files? Answering where are they stored on the disk may be a better response as the docs state that they are not stored in the MyISAM table itself but as separate objects (which means what, exactly?) This is not how I read the section of the manual. Normally, a database row is a single Object within the MyISAM file, whcih contains many Objects. Successive numeric fields will be stored in adjacent words of the MyISAM file in exactly the order you see them when you do a SELECT *. If you want to access this record, then only one disk seek is needed to fetch it. However, because large BLOBs are rarely involved in searches, rather than creating a single huge record with the BLOB embedded in it, the BLOB is stored elsewhere *in the same .myd file*, with only a pointer to the position of the blob within the file. The upside of this is that for searches not involving the BLOB field, and after the indexes have been exausted, only the relatively small non-BLOB needs to be read and checked. The downside is that if the search involves the BLOB field, or if the BLOB field needsw to be fetched, then a second disk access is required, reducing performance. That is how I understand it: if anybody knows better, feel free to correct me - one learns by ones mistakes. Alec There are certain performance advantages if the rows in a table are fixed length. If blobs/text fields are moved to a separate table then the varchar fields can be stored as char. Also, you will no have to clean up the database after large numbers of deletes/updates/inserts because the space in the file will not become fragmented. -- William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DBI and last_insert_id()
Garth Webb said:
You might have better luck with this on the [EMAIL PROTECTED] list,
re: why this doesn't work. This works for me though:
$pk = $dbh-{mysql_insertid};
On Mon, 2004-06-14 at 21:42, Daniel Kasak wrote:
Hi all.
I'm writing an app in Perl / Gtk2, and I'm having trouble with DBI's
last_insert_id() function.
I have so far:
eval {
$sth-execute (@bind_values) || die $dbh-errstr;
};
# If the above failed, there will be something in the special
variable $@
if ($@) {
# Dialog explaining error...
my $dialog = msgbox(
$prospects-get_widget(Prospects),
Error updating recordset!,
Database Server says:\n .
$dbh-errstr, 1
);
$dialog-run;
$dialog-destroy;
warn Error updating recordset:[EMAIL PROTECTED] . $@
.
\n\n;
return 0;
}
$pk = $dbh-last_insert_id();
The statement executes successfully, and the data is inserted. However
the above line that fetches the last_insert_id value from MySQL
always returns undef. The table has an auto_increment column. What's
going on?
Dan
($pk) = $dbh-selectrow_array('SELECT LAST_INSERT_ID()');
BTW this also works in java.
--
William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data into 2 tables and set id
J S said: Hi, I need some help please! I have 60GB of proxy logs to parse and load into a mysql database. I've written a parsing script but I'm stuck now on how to load the data in. I have a database called PROXY_LOG with 2 tables: USER_TABLE user_id date_time url_id size and URL_TABLE url_id url category The data values I have for each record are: user_id date_time size url category for example: u752359 2004-04-02 12:33:04 3403 http://www.mysql.com/index.html business The problem is I'm not sure how to load the data into the 2 tables and set the url_id. Could anyone point me in the right direction please? Many thanks, js. _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger What language did you use to do the parsing. If it was perl I'd recommend looking at using perl's DBI interface and doing it progromaticaly. Do you have any idea of how many different url's you have. Basic steps: Get record, check to see if url is in database, if it is get the url_id. if not insert it and get the generated url_id. insert the user record using the url_id you now have. repeat until you run out of records. Not elegent but it will get the job done. Note look into documentation on how to get the new url_id after you do an insert. Its in the DBD::mysql for perl. -- William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: load data into 2 tables and set id
J S said:
Hi,
I need some help please! I have 60GB of proxy logs to parse and load
into a mysql database. I've written a parsing script but I'm stuck
now on how to load the data in.
I have a database called PROXY_LOG with 2 tables:
USER_TABLE
user_id date_time url_id size
and
URL_TABLE
url_id url category
The data values I have for each record are:
user_id date_time size url category
for example:
u752359 2004-04-02 12:33:04 3403
http://www.mysql.com/index.html
business
The problem is I'm not sure how to load the data into the 2 tables
and set the url_id. Could anyone point me in the right direction
please?
Many thanks,
js.
_
It's fast, it's easy and it's free. Get MSN Messenger today!
http://www.msn.co.uk/messenger
What language did you use to do the parsing. If it was perl I'd
recommend looking at using perl's DBI interface and doing it
progromaticaly. Do you have any idea of how many different url's you
have.
Basic steps:
Get record,
check to see if url is in database, if it is get the url_id.
if not insert it and get the generated url_id.
insert the user record using the url_id you now have.
repeat until you run out of records.
Not elegent but it will get the job done. Note look into documentation
on how to get the new url_id after you do an insert. Its in the
DBD::mysql for perl.
Thanks for your reply William. I am using a perl script. If I have to
insert these records one by one it's going to be really slow isn't it?
Maybe the quickest way is to parse the logs twice i.e. fill the
URL_TABLE first using your procedure above, then on the second run,
create a LOAD file for USER_TABLE?
js.
How will you get the information for the url-id's? I can see splitting
the logs and using a load file for the url_table (if you can eliminate
duplicates). You can save some time, if you can build a perl hash with
the
$url{urlvalue} = url_id. Test that and only do inserts if you need to.
Hash look up is faster than db query, but you will have to have the hash
in memory. You can use the hash to prepare the USER_TABLE and then load
infile that.
Just thought, url is going to have to be a unique key? You can speed up
the initial inserts by inserting without that key (using the perl hash to
avoid collisions) and then altering table to add the key in. However,
question comes back to do you have enough memory for the hash in perl?
Notice also, that you don't have a rowID equivalent in the USER_TABLE
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unable to Programatically Create DB Connection
Scott D. Spiegler said: I am trying to programatically connect to my DB, but I am not sure what the connection string should be. I am using this statement: conn = DriverManager.getConnection(jdbc:mysql://localhost/test?user=scottpassword=cuatro); I am getting this exception message: database_test.DBConnector SQLException: No suitable driver SQLState: 08001 VendorError: 0 Any idea as to what the correct, connection string might be? Thanks, Scott = We don't see things as they are, we see things as we are. --Anais Nin Ok, we are still stuck bck on the old mm.mysql series of drivers and its java 1.3.1 but I think you can just subsitute the current stuff 1st you must 'register the driver' then you need a slightly different connection setup. Class.forName(org.gjt.mm.mysql.Driver).newInstance(); // we register the driver, use the new driver string here this.conn = DriverManager.getConnection(this.DBUrl,this.dbuser,this.dbpasswd); where DBUrl is a string like jdbc:mysql://localhost:3306/+this.dbname; and dbuser and dbpasswd are the strings containing the username and password. -- William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 PS. we'd update the driver, but is a shared server environment and we have lots of jsp contects and they all have to change at once.. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and NPTL
Sasha Pachev said: Steve Meyers wrote: Has anyone else experienced this bug? http://bugs.mysql.com/bug.php?id=868 We've been seeing this problem on several of our servers (see the last comment to the bug). MySQL just hangs occasionally, it happens about 3-4 times per month. We have 13 database servers, so that unfortunately increases our odds. I just wondered how many other people have seen it, and if the suggested export LD_ASSUME_KERNEL=2.2.5; mysqld_safe has worked for anyone. In order to turn that on, I will need to take our site down completely, which is (of course) not desirable. Steve: I have not heard much good about NPTL threads when used with MySQL at least :-) If LD_ASSUME_KERNEL trick does not do the job, I would recommend compiling a virgin 2.4 kernel from kernel.org and putting it on all of your servers. That might actually be a good idea anyway even if the trick does work. In three years of doing MySQL support I have learned this simple formula: RH kernel from 7.0 and newer + MySQL + high load = highly probable instability -- Sasha Pachev Create online surveys at http://www.surveyz.com/ Any particular reason to use 2.4.x vs 2.6.x from kernel for base? -- William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backups with version 4.1
Mauricio Pellegrini said:
Hi I'm using, Mysql version 4.1.1 with InnoDB under SuSE linux 8.2
I don't know if this is the right place to ask. If not please point me
in the right direction.
I'm performing nightly backups of the datadir. So my backups include a
database and the Mysql databases themselves ( also I think there are
somo innodb files, I think.
For this porpouse I'm using tar and then gzip.
So every backup leaves .tar.gz file containing the whole datadir.
My questions are:
1- Are these backups usable in case I need to restore the datadir ?
2- As they are Full backups, I'm starting to think in doing some kind of
incremental backups in order to use less space for storage, is that
possible ?
Thank you
Mauricio
Databases in general, hold some information internally and the files
themselves may be in an inconsistent state. You can of course, stop the
database engine, run the backup and then restore it. This will ensure
that the files are consistent. However, I prefer to dump each of the
databases and then backup that. Since I have many smallish databases this
seems to have minimal impact. Since all my databases use myisam tables I
can 'cheat' and use the following script:
echo -n 'Dumping at '
date
cd ../mysql
for d in `find -type d -maxdepth 1 -mindepth 1 | sed 's/^\.\///'`
do
mysqldump -u root --password={your mysql root password here}
--add-drop-table -l $d ../mysqlback/$d.dmp
done
Obviously for a linux setup.
--
William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Website
Robert A. Rosenberg said: This is a re-submission of a 4/21 reply that was bounded by the list. At 09:14 -0400 on 04/21/2004, Lou Olsten wrote about Re: MySQL Website: I have not been able to access the mysql.com server for about a day and a half now from my office. From home, it's fine. There have been rare occasions in the past when our provider had dropped (or very slow) connectivity with certain nodes on the Internet. My understanding there is limited, but I know that there are really only a handful of actual back-bone providers out there for the 'Net, and if a main provider has problems with one of those points, it can take down (or slow) access to vast geographical areas. I believe that's what's going on with our provider at present. However, getting them to troubleshoot it is another matter altogether. It usually starts with Did you restart your modem? and degrades from there. I can get to every other site that I normally visit without problems. Still no MySQL as of 9:14am EST. Lou If you suspect congestion of this type, the simplest way to checj is to issue a traceroute mysql.com command from terminal (Unix/Linux/MacOS X/etc) or command prompt (Windows). Here is an example of the result. traceroute to mysql.com (66.35.250.190), 30 hops max, 40 byte packets 1 10.93.64.1 (10.93.64.1) 10.328 ms 10.437 ms 8.338 ms 2 dstswr1-vlan2.rh.mhwhnj.cv.net (67.83.247.161) 21.551 ms 8.186 ms 9.024 ms 3 ool-4353f781.dyn.optonline.net (67.83.247.129) 19.871 ms 18.958 ms 10.331 ms 4 r1-srp13-0.wan.prnynj.cv.net (65.19.112.17) 9.319 ms 10.649 ms 9.271 ms 5 r1-srp5-0.in.nycmny83.cv.net (65.19.96.53) 13.937 ms 11.543 ms 11.162 ms 6 dcr1-so-5-2-0.newyork.savvis.net (206.24.207.25) 12.278 ms 11.309 ms 12.097 ms 7 dcr2-loopback.santaclara.savvis.net (208.172.146.100) 78.67 ms 79.595 ms 79.875 ms 8 bhr1-pos-0-0.santaclarasc8.savvis.net (208.172.156.198) 80.005 ms 79.479 ms 80.273 ms 9 csr1-ve240.santaclarasc8.savvis.net (66.35.194.34) 80.498 ms 81.079 ms 79.937 ms 10 66.35.212.174 (66.35.212.174) 82.225 ms 111.405 ms 82.783 ms 11 mysql.com (66.35.250.190) 80.431 ms 80.987 ms 80.576 ms Note: under windows its tracert not traceroute. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: files stored in fields
adrian Greeman said: Please excuse a very simple inquiry from a near beginner If I wish to store a complete Word file or similar (Open Office perhaps) or an archive file - is that possible and what kind of field do I need for it? How do you insert a file into a table if so? I know that you can store pictures - what field do I need for those and again how to insert? Regards Adrian Yes you would need to use a 'blob' type field. However, please consider writing the information to the file system and then storing the address on the file system in the database. Last time I checked fixed length database records process much faster. What computer language are you using to do the work. That would help in answering the rest of the question. -- William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DateDiff function in SqlServer ... How do it in MySql ?
Michael Stassen said: Gabriel Alessandria wrote: I am a user of Microsoft Sql Server and use very much the function Datediff (interval, fecha1, fecha2) to extract differences between two dates, in years, days, months, hours, etc I am a beginner with MySql and i can't see the way to do this function .. How can I extract for example difference of hours between two given dates .. someone help me please ? Thanks in advance Not quite sure what it means to get the difference between two dates in months or hours, but mysql does have a DATEDIFF function: DATEDIFF(expr,expr2) DATEDIFF() returns the number of days between the start date expr and the end date expr2. expr and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation. For more date functions, see the manual: http://www.mysql.com/doc/en/Date_and_time_functions.html Michael For finergrain accuracy: (UNIX_TIMESTAMP(TIStop)-UNIX_TIMESTAMP(TIStart))/60 This gets difference in minutes. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB, mysqldump/mysql timeout dropping table (disaster recovery)
How about if you dumped to a compressor and stored the result? Steve Williams said: Hi, The problem with doing a myqldump to a file (via cron) is that at some point it will hit the filesize limitiations. By streaming it over the network, that problem is avoided on both ends of the pipe. The idea of doing the scp of the mysql data directory is not a bad one, but would require the shutting down of the database (production). The shutting down of the disaster recovery one isn't a problem... I may resort to it, but I'd prefer to just figure out which timeout is causing the problem. Thanks, Steve Williams -Original Message- From: dan orlic [mailto:[EMAIL PROTECTED] Sent: Friday, February 27, 2004 3:52 PM To: Steve Williams Cc: [EMAIL PROTECTED] Subject: Re: InnoDB, mysqldump/mysql timeout dropping table (disaster recovery) perhaps you would get a better response from doing scp... which runs over ssh... or doing the mysqldump in a cron job, so it will already be complete for transport by ssh. I still think scp is the more proper way to go. dan orlic Steve Williams wrote: Hi, We have a (pre-existing) disaster recovery/backup script that uses = mysqldump, ssh, mysql to backup an existing database. One of the tables = is rather large (1 Gig or so), and the time that it takes to DROP = TABLE on an already loaded recover server causes a timeout. I have = confirmed tested by creating an empty database on the recovery server = the mysqldump loads fine. The second time I run it, it gets a timeout = error. The basic logic is: mysqldump ... somedatabase | ssh [EMAIL PROTECTED] mysql ... This technique is because only the SSH port is open to the recovery = host. mysqldump: Error 2013: Lost connection to MySQL server during query when = dumping table 'Item' at row: 1539 real 3:10.4 user0.0 sys 0.0 I just do not know which timeout is causing the problem. mysql show variables like '%timeout%' - ; +--+---+ | Variable_name| Value | +--+---+ | connect_timeout | 5 | | delayed_insert_timeout | 300 | | innodb_lock_wait_timeout | 50| | interactive_timeout | 28800 | | net_read_timeout | 30| | net_write_timeout| 60| | slave_net_timeout| 3600 | | wait_timeout | 28800 | +--+---+ 8 rows in set (0.06 sec) =09 Or is it a timeout associated with mysqldump?? Can anyone shed some light?? Thanks, Steve Williams -- 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: order by: more that one field
Mike Mapsnac said: Have some questions about Order By: Can a mysql query be order by more than one field? If this two queries will give different result or not? #1 select * from t1 Order by id, username; #2 select * from t2 Order by id _ Choose now from 4 levels of MSN Hotmail Extra Storage - no more account overload! http://click.atdmt.com/AVE/go/onm00200362ave/direct/01/ yes. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld hangs with no CPU activity...
Ware Adams said: Paul Stearns wrote: As reported under the subject Random Database Slowdowns... on the win32 list, our database still hangs on an average of 1-2 times per day. I can find no error messages or logs associated to the problem. It affects both IIS ADO connections as well as local connections from tools such as mysqlcc, mysqladmin and command line tools such as mysql. I see no CPU activity associated with the hangs. I cannot stop and restart the service, but most of the time a reboot will resolve the problem. Sometimes the problem will reoccur within a few minutes of a reboot, other times it takes hours. I can't swear it's the same problem, but we had very similar symptoms some time ago (version was around 4.0.8 or so). This was also on Mac OS X Server 10.2.x. Seemingly random queries would just not finish. They were queries we had run before and could even run at the same time from a different client. But this process would just not finish. It would sit in show processlist forever. If it was the only query running the mysqld cpu load would drop to around zero. There was no i/o activity if it was the only query running. Any temp files associated with the query wouldn't grow. Sometimes it was a small query, sometimes a big one. If you tried to kill the process from within the command line client or mysqladmin it would show up as killed in the process list but never die. Issuing mysqladmin shutdown wouldn't shut down the mysqld server b/c it couldn't kill off the queries either. Even kill -9 mysqld pid would hang the machine. The only solution was rebooting while mysqld was running b/c we couldn't shut it down. We tried moving to InnoDB and got the same situation (and show innodb status\G revealed no work was being done within InnoDB). We tried swapping RAM, swapping hard drives, changing drive formats (HFS+ to UFS), installing Yellow Dog Linux on the machine, etc I was pretty certain it was just a MySQL on the Mac issue, but then I pulled the drives and RAM and put them in an identical Mac. Same drives, same RAM, same data, same OS, same MySQL...the problem disappeared. We sent the machine back to Apple (it was new) and they replaced the mother board. That machine would show random errors even in just desktop use, so though the solution seems onerous we really didn't have much choice. If you can, I'd try running the queries on a different machine. Good luck, Ware If moving to new hardware solved the problem, it was the hardware. Had similar problem with Linux kernel compilation a few years ago. Drove us crazy for a while since on that process failed. Turned out bad cache chip on motherboard. Moral: if you can't trust the hardware, all bets are off. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ISP and users
Donald Henson said: On Fri, 2004-01-16 at 17:05, Bryan Koschmann - GKT wrote: Hello, I run an ISP where our web customers have access to the MySQL server. When they want a database, they request it through me and I add it. I was just curious if this is the common way it is done, or if there is a safe way they can add their own? Thanks, Bryan That's the way my ISP does it. Of course, that doesn't make it right. :-) Don Henson I too run an ISP and normally run it that way. We have one customer who can use a web page to create databases so they meet certain criteria, but giving direct create and destroy rights to our customers is a bit of a security hole IMHO. And yes the web page is on a secured server. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What does this Mean
Chris L. White said: C:\MySQL\binmysql show ERROR 1045 (28000): Access denied for user: 'ODBC'@'localhost' (Using password: YES) C:\MySQL\binmysql show -u root ERROR 1045 (28000): Access denied for user: 'root'@'localhost' (Using password: YES) Your musql setup requires a password for root and you didn't include the -p so that mysql client will ask you for the password on the next line. C:\MySQL\binmysql show -u root -p Enter password: ** ERROR 1045 (28000): Access denied for user: 'root'@'localhost' (Using password: YES) Did you enter the password which was configured or mysqld that you are runnning? C:\MySQL\binmysql show -u administrator -p Enter password: ** ERROR 1045 (28000): Access denied for user: 'administrator'@'localhost' (Using p assword: YES) Administrator is windows OS equivalent to *nix 'root' but mysql expects its all powerful user to be called 'root' NOT administrator. C:\MySQL\bin Ah you are on widows.. on the right of the bottom bar (the one which can be hidden) is an icon which looks like a traffic signal(should have its green light on). Click on it and then click on 'Show Me' in the popup Go to the my.ini Setup tab and look for the root password. Also here is my.ini file below: [mysqld] # set basedir to your installation path basedir=C:/mysql # set datadir to the location of your data directory datadir=C:/mysql/data # Example mysql config file. # Copy this file to c:\my.cnf to set global options # # One can use all long options that the program supports. # Run the program with --help to get a list of available options # This will be passed to all mysql clients [client] password=xx port=3306 socket=MySQL # Here is entries for some specific programs # The following values assume you have at least 32M ram # The MySQL server [mysqld] port=3306 socket=MySQL skip-locking set-variable = key_buffer=16M set-variable = max_allowed_packet=1M set-variable = table_cache=64 set-variable = sort_buffer=512K set-variable = net_buffer_length=8K set-variable = myisam_sort_buffer_size=8M server-id = 1 # Uncomment the following if you want to log updates log-bin # Uncomment the following rows if you move the MySQL distribution to another # location # basedir = c:/mysql/ # datadir = c:/SQLData # Uncomment the following if you are NOT using BDB tables skip-bdb # Uncomment the following if you are using BDB tables #set-variable = bdb_cache_size=4M #set-variable = bdb_max_lock=1 # Uncomment the following if you are using Innobase tables #innodb_data_file_path = ibdata1:400M #innodb_data_home_dir = c:\ibdata #innodb_log_group_home_dir = c:\iblogs #innodb_log_arch_dir = c:\iblogs #set-variable = innodb_mirrored_log_groups=1 #set-variable = innodb_log_files_in_group=3 #set-variable = innodb_log_file_size=5M #set-variable = innodb_log_buffer_size=8M #innodb_flush_log_at_trx_commit=1 #innodb_log_archive=0 #set-variable = innodb_buffer_pool_size=16M #set-variable = innodb_additional_mem_pool_size=2M #set-variable = innodb_file_io_threads=4 #set-variable = innodb_lock_wait_timeout=50 [mysqldump] quick set-variable = max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL safe-updates [isamchk] set-variable = key_buffer=20M set-variable = sort_buffer=20M set-variable = read_buffer=2M set-variable = write_buffer=2M [myisamchk] set-variable = key_buffer=20M set-variable = sort_buffer=20M set-variable = read_buffer=2M set-variable = write_buffer=2M [mysqlhotcopy] interactive-timeout [WinMySQLAdmin] Server=C:/mysql/bin/mysqld-nt.exe user=administrator password= QueryInterval=5 Chris L. White Network Administrator Coe-Truman Technologies, Inc. Email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: What Does This Mean
Chris L. White said: You know what is even more screwed up now. I did this command and when it prompted me for the password I just hit enter and wallah look what I got. But I am sure there is other stuff messed up now: SET YOUR ROOT PASSWORD it its blank it will behave as you describe. Sorry for the shouting but this is a safety issue. C:\MySQL\binmysql -u root -p Enter password: ** ERROR 1045 (28000): Access denied for user: 'root'@'localhost' (Using password: YES) C:\MySQL\binmysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 to server version: 5.0.0-alpha-max-debug-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql Chris L. White Network Administrator Coe-Truman Technologies, Inc. Email: [EMAIL PROTECTED] William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select lock - How reliable?
Hassan Schroeder said: Roger Baklund wrote: Maybe I don't understand connection pooling, then. Isn't its purpose to allow a client -- say, a webapp running in Tomcat -- to multiplex requests for *multiple* end users over a single connection? Almost... except for the 'single connection' part. It will maintain a pool of connections, thus the name 'pooling'. Of course -- I was trying to simplify for the purpose of discussion... A sensible connection pooling implementation would do something like this: - accept a client connection - if a free database connection is in the pool: - return it to the user - otherwise if the pool is full: - return error message to user too many connections, retry later - otherwise - create a new database connection - add it to the pool - return it to the user Once a user has got a connection, he keeps it for the entire session, that usually means untill the program ends or the connection is explicitly closed. OK, but the user here is the servlet container (Tomcat), *not* the human end user. There may be other ways to implement connection pooling, but I would say an implementation allowing separate statements from separate users intermingeled on the same connection was... well, at least special, if not broken. Besides LAST_INSERT_ID(), user variables would also be broken in such a system. As would temporary tables, which is why I stopped using them for request-specific data :-) Related to this, does each process in the query log match to a single active connection? Or is that connection-pool-implementation dependent? -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. On a related note. You should always close the connection before ending the jsp, not depend on the garbage collector to do it for you. In the case of a connection pool setup this will return the connection to the pool and in the case of a direct connection setup, release the connection to mysql. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sleeping Processes
. - Byte Magazine Given infinite time, 100 monkeys could type out the complete works of Shakespeare. Win 98 source code? Eight monkeys, five minutes. -- NullGrey I have one php application with php and mysql where php is imbedded in the apache children and when connection pooling is on, there is a connection for each apache child process. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OT: MySQL amp; NAT
gerald_clark said: If you are going out throught NAT to the mysql server, there should be no problem. If you are trying to come into the server through NAT you will need port forwarding. adburne wrote: Someone can make nat with mysql? I can do it with apache without problems, but mysql client freeze trying to connect. Thanx, Alejandro. You might also remember that unless you limit the outgoing connection to a specific NIC, there is no way that I know of to limit which stations inside the NAT can use the external mysql server, i.e., the 'host' address will be the public IP of the NAT firewall. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: max_user_connections problem after upgrading
Joe Lewis said: We're experiencing the same issues, but not neccesarily after an upgrade. We're using MySQL 4.0.12 and FreeBSD 4.7 Release. We're getting max'd connections only on specific users, and the show processlist is returning only the show processlist process. the results of netstat show absolutely nothing. What I think is happening is the connections are not properly getting closed. The users are allowed to connect after a flush user_resources is run. Is there a bug in the particular version of MySQL (4.0.12) where the user connections are not getting decremented when a connection is closed? Joe Henrik Skotth wrote: I'm mostly using mytop, and that's the way that I see that there are no (are almost no) connections when the server claims that it is above the connection limit... So I guess that there's something seriously wrong then... Any ideas what? -- Henrik [EMAIL PROTECTED] (Pete Harlan) skrev: What does show processlist say when the connections are maxed out? (You may have to leave a client logged in to reserve a slot so you can submit this query.) If it shows only a few connections, then there's something seriously wrong. If it shows a ton of idle connections, it should tell you which machines they are coming from and which users, and that should help you track down who's holding connections open. --Pete On Wed, Nov 12, 2003 at 01:15:54AM +0100, Henrik Skotth wrote: Hello! I have tested this now, and that isn't the case. Any other ideas? -- Henrik Michael McTernan skrev: Hi, Have you tried netstat -a on the box with the MySQL server? This command (Linux) will show what is connected to where, and will help you double check that there really aren't any open connections to the server. Thanks, Mike -Original Message- From: Henrik Skotth [mailto:[EMAIL PROTECTED] Sent: 10 November 2003 18:54 To: [EMAIL PROTECTED] Subject: Re: max_user_connections problem after upgrading That's new for 4.0? Hadn't noticed it... Anyway, it's set to 0 so there is no limit to exceed. Also, we aren't getting the error messages ALL the time, they start to appear after a day or two and gets more and more frequent untill I restart mysql. Any other ideas? -- Henrik gerald_clark skrev: Are you sure you are net exceeding the setting for maximum connections per hour for that user? Henrik Skotth wrote: Hi! What I meant was that even if there are currently only two user connections being used, and the limit is 300, we still get the already more than max_user_connections error... -- Henrik gerald_clark skrev: Henrik Skotth wrote: Hello all, We recently upgraded to 4.0, it went very well and the performance gains have been great. But now the server has started to act strangely. Every few days, the server starts to refuse connections, saying that there is already more than max_user_connections, but there is really only one or two active connections and our max_user_connections is 300. I have to take down and restart the server to solve the problem, and it keeps happening over and over again every few days... Am I the only one having this problem? Any suggestions? Regards, -- Henrik Skotth, Hogwarts.nu Are there 298 or 299 inactive connections? If so, why are they not being closed? Is the user running show processlist allowed to see all processes (e.g. 'root')? William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: aes encryption bug
Paul DuBois said:
At 15:29 -0800 11/6/03, Herb Rubin wrote:
Paul,
I did try VARCHAR(16) BINARY and it still failed to INSERT in a NOT
NULL column.
The encrypted string seems to be equivalent to the NULL value even
though it visually looks like some kind of data.
Herb
Okay, I investigated this further and I believe I know the cause of the
problem. The solution is to use a TINYBLOB NOT NULL rather than
a VARCHAR(16) NOT NULL.
Here's what's going on:
- The particular encrypted value you calculate ends with 0x20. That is,
the same value as a space character.
- Trailing spaces are trimmed from values stored in VARCHAR columns. -
That means when you retrieve the value, it's 15 bytes long, and is no
longer a legal encrypted value.
- AES_DECRYPT() returns NULL for illegal encrypted values.
Can you instead use CHAR(16) instead of VARCHAR(16)? No, because
the trailing space would still be trimmed *when the value is retrieved*,
and you'd still get NULL from AES_DECRYPT().
Use a TINYBLOB instead. Trailing spaces won't be trimmed when the value
is stored, or when it is retrieved. AES_DECRYPT() will work.
In general, you shouldn't try to use CHAR/VARCHAR for encrypted values
or other forms of binary data. Use BLOB columns instead.
Please reply to the list, not to me directly, so that others can
follow this discussion. Thanks.
At 14:54 -0800 11/6/03, Herb Rubin wrote:
Paul,
Yes, I get the same, now try and decrypt it, it will turn out to be
NULL. So, you cannot insert this into a NOT NULL column. It will
reject it.
Your message (below) appears to be reporting a problem with
AES_ENCRYPT(). It states that you get a NULL return value from that
function.
You now appear to be saying something else.
I don't understand what problem you're trying to report.
My result from decryption:
mysql select AES_ENCRYPT('551850040',
'0bf251c9aaf007deaf1143ca1492b561');
+--+ |
AES_ENCRYPT('551850040', '0bf251c9aaf007deaf1143ca1492b561') |
+--+ |
k\ÎúVÀà ÿxû÷Ò |
+--+ 1
row in set (0.00 sec)
mysql select
AES_DECRYPT(AES_ENCRYPT('551850040',
'0bf251c9aaf007deaf1143ca1492b561'),
- '0bf251c9aaf007deaf1143ca1492b561');
+-
--+
| AES_DECRYPT(AES_ENCRYPT('551850040',
'0bf251c9aaf007deaf1143ca1492b561'),
'0bf251c9aaf007deaf1143ca1492b561') |
+-
--+
| 551850040
|
+-
--+
1 row in set (0.07 sec)
Are you reporting that you get NULL only in the context of inserting
and retrieving the value from the id column?
If so, please try using TINYBLOB rather than VARCHAR and see what
happens.
we are on 4.0.14
Herb
At 14:03 -0800 11/6/03, Herb Rubin wrote:
Hi,
I am trying to use aes_encrypt and I get a NULL value with a
specific string:
INSERT INTO test SET
`id` = AES_ENCRYPT('551850040',
'0bf251c9aaf007deaf1143ca1492b561');
my field 'id' is VARCHAR(16) NOT NULL
If I change the value or the encryption string it works. But this
combination turns out to be null and it refuses to insert.
With MySQL 4.0.14, 4.0.16, and 4.1.1, I get:
mysql select AES_ENCRYPT('551850040',
'0bf251c9aaf007deaf1143ca1492b561');
+--+
| AES_ENCRYPT('551850040', '0bf251c9aaf007deaf1143ca1492b561') |
+--+
|
k\ÎúVÀà ÿxû÷Ò |
+--+
What version are you using?
Help!
Herb
--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
So a char(16) binary would work fine as well?
William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Multiuser Programming
Chris said: I'm not familiar with that function in MS SQL, and you're a bit unclear, but it looks like you might be able to use temporary tables. http://www.mysql.com/doc/en/CREATE_TABLE.html Temporary tables are on a per-connection basis, and are unique per connection (each connection can have a temporary table that won't conflict with others). Temporary tables are dropped as soon as the connection is dropped. Chris -Original Message- From: William IT [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 29, 2003 5:01 PM To: [EMAIL PROTECTED] Subject: Multiuser Programming I am doing transfer from MS SQL. In MS SQL while I connect as USER1 my table will be create as User1.TableName. This will avoid conlict with other user. Now, how do I do that in Mysql? Or, maybe there is such system in Mysql. Is there any Mysql reference talk about this matter? I thought this list wanted bottom posting? Sorry if I've got it backwards. How does connection pooling figure into this? I would assume a drop table command would be needed since from MySQL's point of view, the connection never gets dropped. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What are the effects of key_buffer on a dedicated slave [also]
Jeremy Zawodny said: On Tue, Sep 30, 2003 at 01:11:23PM -0700, Dathan Vance Pattishall wrote: ---Original Message- --From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] --Sent: Tuesday, September 30, 2003 11:51 AM --To: Dathan Vance Pattishall --Cc: [EMAIL PROTECTED] --Subject: Re: What are the effects of key_buffer on a dedicated slave --[also] -- --On Tue, Sep 30, 2003 at 11:36:30AM -0700, Dathan Vance Pattishall wrote: -- -- Yes, I use a custom mytop (sent my patches in to you). In fact I'm -- making a signed java applet to simulate mytop, just to be fancy ;) as -- well as not having to ssh into a central box that can reach all my -- servers. -- --Really? Which patch? Have I integrated it yet? :-) Patch Contained SLAVE / Master Positions as well as which databases are ignored or slaved, and a full list of possible keys Command (E). I can resend another patch; I've made some changes since. Please do, it sounds useful. I'd like to roll it into version 1.5 before I release it. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 16 days, processed 590,020,855 queries (404/sec. avg) Have you tried mytop with debian and 3.23.49 -- version in Debian stable? we keep getting core dumps. Does it have to be run as root? Sorry if OT William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB / Linux
Marvin Wright said: Hi, I'm in the process of setting up a new database server that will run on redhat linux. The machine will be dual processor with 4GB ram and about 16GB disk. The machine is going to be used purely with InnoDB tables and will have a few very large tables acting as cache data. The amount of data I want to store will be between 2 and 4 GB to start with but might grow larger. I've been reading alot on how to set up InnoDB and have come across the 2GB limit problem. There is actually 2 problems here. 1. From reading many articles Linux may or may not support files larger than 2GB. 2. There is a problem with glibc that a process may become unstable if a process allocates more than 2GB. The 1st one isn't a problem, I can have 2 data files of 2GB, but I would like to overcome this issue. The second is where I'm stuck on, the InnoDB configuration page gives a nice formula that you should use so that you can calculate how much memory you should use. It gives an example configuration but this exceeds the 2GB limit even with only 200 concurrent connections. I really need to get the connections to something like 1000 without going over the limit. What configuration can be used and how can this be achieved ? Additionally I have read that each linux thread has a stack of 2MB, this is taken into account in the formula, this can be changed as I understand by changing a #define somewhere and recompiling the kernel and then recompiling the mysql server. Any input would be greatly appreciated. Best Regards, Marvin Wright Depends on: your version of Linux, File system and processor. I believe that the basic 2 GB limit is gone in Linux 2.4 with ext2 or ext3 file system. You may have to turn on some flags or define an estimated table size to cause mysql to use large enough pointers, but I thought that InnoDB could use multiple extants each of which could be up to 2 GB. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: last_insert_id()
R.Dobson said: Hi, Is it possible to obtain the last_insert_id() for a particular column in a particular table? eg, say i wanted to obtain the last insert id of a column called id in table reference, something along the lines of: last_insert_id(reference.id) The reason I ask is because I want to initially insert values into two tables and then insert values into a third using the last_insert_id() from the first two tables. Obviously, the last_insert_id from the first insert is replaced by the last_insert_id from the second insert. This is all done in a perl script and I could store the first last_insert_id in a variable but I thought their might be a more elegant way round it? tia Rich last_insert_id is connection specific and contains the value of the auto-increment column for the last record you inserted using that connection. The sequence is - do insert of record with auto-increment column - get value of last_insterted_id (i.e., the value asigned to the auto-increment column to that record). There is only one column so there is no need for figuring out which column. If you do two inserts you must get the last_inserted_id BETWEEN the inserts. If the second table also has a auto-increment column you will have to get its value after that insert. Hope this helps. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How much user LOAD can Mysql bear???
Tariq Murtaza said: Thanks Fortuno, Adam Actually, I was thinking about concurrent users that mysql can handle, provided with reasonable hardware. Looking for comments / suggestions. Regards, Tariq Fortuno, Adam wrote: Tariq, Check the list's history - this is actually a frequent question. The answer everyone is about to give you is generic. MySQL's ability is havily based on the hardware and OS it runs on. Therefore, a multi-processor machine with lots of memory can handle more than a single processor workstation with 256 MB of RAM. The faster the machine the faster MySQL... etc. Be more specific, whats the hardware and software. How much information are you storing in the DB? Any replication? Regards, Adam -Original Message- From: Tariq Murtaza [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 02, 2003 8:40 AM To: [EMAIL PROTECTED] Subject: How much user LOAD can Mysql bear??? Hi All! How much user LOAD can Mysql bear before die. Regards, Tariq You haven't changed the question. The number of concurrent users is the same as the number of connections...if they are TRUELY concurrent. That is is they all hit the submit button at the same time. You set the number on connections. Each connection takes up a bit of memory, even if idle (php and mod_perl hold open connections. .jsp may or may not. Your response time (how long is acceptable) will depend on you actual database and questions you ask it as well as the hardware. It might help if you told us what hardware you are planning to use or what kinds of questions or environment it will support. Then the list can get more specific. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Too many hours - brain not working.....
Mike Morton said: Hey y'all - I have been at this too long today - this is driving me nuts! Table: Date (y-m-d),amount (decimal),dealercode I am looking for a query that will give me the sum of each of the distinct dealercodes for a specific date range. Something to the effect of: Select (distinct dealercode),sum(amount) as total where date between 'this' and 'that' The trouble that I am running into is that I cannot seem to get that done in one query - splitting it up into 2 is easy enough - do the distinct dealercode, then loop through those to do the sum between date query - but ideally I would like to do it in one query. If this is possible to do - how? TIA! -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * Indeed, it would not be an exaggeration to describe the history of the computer industry for the past decade as a massive effort to keep up with Apple. - Byte Magazine Given infinite time, 100 monkeys could type out the complete works of Shakespeare. Win 98 source code? Eight monkeys, five minutes. -- NullGrey Welcome to 'Group By' Table: Date (y-m-d),amount (decimal),dealercode select sum(amount),dealercode from table where date 'date1' and date 'date2' group by dealercode William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Atomicity of a SELECT/UPDATE
Andy Jackman said:
Hi,
I want to get any one of a number of rows that matches some criteria and
update it so that it is marked as 'mine'. How can I do this safely?
Given something like this table:
create table tbl_new (
t_value varchar(16) primary key not null,
dt_used datetime,
l_pid int(9)
);
I want to get any t_value WHERE dt_used is null and then set dt_used to
prevent anyone else getting the same t_value.
If I use locking (but I'm using C and I don't see any locking functions)
(nor a START TRANSACTION?) I could:
lock the tables,
select t_value from tbl_new where dt_used is null;
update tbl_new set dt_used = now() where t_value = 'whatever';
unlock the tables;
Without locking I could do something like this: (assume my-pid is unique
between all users of this application at any one moment)
while (1)
{
select t_value from tbl_new where dt_used is null;
update tbl_new set dt_used = now(), l_pid = my-pid where t_value =
'whatever' and l_pid is null;
select l_pid from tbl_new where t_value = 'whatever';
if (l_pid == my-pid)
break;
// Else someone grabbed that record before us, go round and do it again
}
This sounds long winded to me. Anyone got a better suggestion?
Thanks,
Andy.
You almost have it. Look up 'Lock Tables' in the manual.
Unlike most database engines, mysql allows various types of tables. The
defaults is myisam which does not support transactions; however, InnoDB
does. Please see docs for details.
William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Q] about AUTO_INCREMENT...
Write a Friend said: When using AUTO_INCREMENT, is there a way to set the starting value. Thanks, Carlos Alter Table TABLENAME AUTO_INCREMENT=1 where TABLENAME is your table. Of course this resets it so the next use of auto increment will generate a 1 for the table. I use is right after deleting the contents of the table and any related tables. (Its for a conversion program and I delete everything if the conversion fails). William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Java API to mySQL
Hello I want to know if there is java API to mySQL like its available for C and C++. Bye Kalika SMS using the Yahoo! Messenger;Download latest version. Look for Connector/J on the MySql site. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I display images from a mySQL Database in a web page?
I think there is a way to insert binary image data in your html, but is there any particular reason you need to do that? First you would need to encode it in some ascii equivalent (check w3c.org, I think) ...and it would increase the size of your html page while rendering the browser's image cache useless... This will both increase the page's load time and your server's bandwidth requirements, as well as possibly being a pita to implement :-/ I would suggest you store the image filenames in the table, and paste that into your html... -Partap On 7/15/03 1:25 PM, Dan Anderson [EMAIL PROTECTED] wrote: I have created a BLOB field to store images. Is there any way to embed them within HTML with something like: image start: jpeg /image Thanks in advance, Dan blob datatype. But again why do this. use a file system and put the file's address in the database. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: !!! NEWBIW !!! how to start? !!! NEWBIE !!!
Hello everyone, Im extremaly newbie with using MySQL under Linux. Im using: mysql Ver 11.18 Distrib 3.23.51, for slackware-linux-gnu (i386) - ( btw - should i uprgade this or its enough to learn ? ) My question is: How to start mysql deamon? When i type: mysqld then apear: ERROR 2002: Can't connect to local MySQL server through socket '/var/run/mysql/mysql.sock' (2) Whats wrong? Its a default installation I have a little experience with MySQL under MS Windows ( run mysqld then open MySQLadmin and thats it ), but i wish to use database under Linux. If any one can help then o would be in debt forever. -- Best regards, mailto:[EMAIL PROTECTED] Did you try typing 'ps aux | grep mysqld' ? You should get three lines (plus sometimes the grep line) if the server is running. Normally you will start a safe_mysqld rather than mysqld Alternatively you would start it with mysqladmin William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT TOP
What's wrong with the following query? The application used to use Access via ODBC, now running MySQL 3.23.xx, MyISAM table, accessed via MyODBC 3.51.01. // Return last 20 searches SELECT TOP 20 h.historyid, h.searchstring, h.serverid, s.shortname FROM history h INNER JOIN servers s ON h.serverid = s.serverid WHERE h.employeeid = 9 ORDER BY h.historyid DESC Works fine once I remove the TOP 20 from the query. If this isn't supported, is there an equivalent? Thanks, Jim remove TOP 20 from front, add 'limit 20' to back. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql - Dual Xeon or Dual Opteron
On Monday 07 July 2003 15:18, Florian Weimer wrote: Konstantin Yotov [EMAIL PROTECTED] writes: 2x1GHz Intel, 1GB RAM, 40GB WD 7200 8MB cache. We are going to uprade our server but I'am wondering between new Opteron (1.4GHz)and Xeon (2.4). Can't you get a machine for testing before you buy it? Xeon processors aren't necessarily a significant win over Pentium 3s, even with the noticeable difference in clock speed. But they are capable of more than 2-way... Get yourself a quad Xeon-board, and start with two CPUs, adding more as you go along... -- Andreas D. Landmark / noXtension You mean they have solved the problem of having to used matched CPU's. Used to be that you had to throw out the old CPU's and get all new. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date and time
Hi, can someone explain me the avantage of using date and time, and also can i set time + XX minutes?? Thanx Anthony Short answer is: You can use the date and time functions and formats. There is one or just use '+' or '-' See chapter 6 in the ref. manual. If you need both date and time use the datetime field type since you will simplify sorting and conditionals. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie SELECT problem
Hello everyone, I have the following select statement SELECT DISTINCT sessionID, userID, date, time FROM sti_tracking WHERE userID = 999 What I want is to have only records with the userID of 99 and where the sessionID is distinct (meaning only on of each session id). Neither sessionID nor userID are keys or unique. Obviously this isn't working. Can someone suggest how this should be done? Tim Winters Creative Development Manager Sampling Technologies Incorporated Had a similar experience, and I've been doing it long enough to know better. 'DISTINCT' would work only if date and time returned the same values. Are '999' and '99' supposed to be the same? Let me see if I can rephrase what you are looking for: a. For user '999' give me the information where there is only one record with a given SessionID? b. For user '999' for each sessionID give me the unique Date and Time values. c. something else entirely. Also, are you running this in a procedureal language (e.g., perl, java)? This will give us other options. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie SELECT problem
Tim:
Assuming that in your ealier posting the 99 was supposed to be 999, then
the solution given by Mike Hillyer is excellent and should work.
However, when I read your new posting, I seem to get confused. The
scenario sounds totally different - excuse me - from the earlier one and
would therefore need a different solution. You might help us by giving
sample data.
Or is this what you mean by But I don't want duplicate session numbers
(one is enough)? == In a single session (sessionID) user 999 (userID
999) may visit 3 pages. This results in three inserts being made into
table sti_tracking all having same sessionID and userID. Correct? When
retrieving you do not want to retrieve all these three records. Correct?
You just want one of the records. Which one? The first, second or third
because they each probably have a different time and pageName (even
date!!). If you did not want the date, time and pageName then the
solution is simple SELECT DISTINCT userID, sessionID FROM sti_tracking
WHERE userID = 999.
If you do not care which of the entries (3 in my example) is returned
and you still want the date, time and pageName (my guess is the first
will be returned), then you need to generate all the distinct userID and
sessionID pairs using the above SQL. Then for each pair (use a loop)
run SELECT userID, sessionID, date, time, pageName FROM sti_tracking
WHERE userID = {provide from loop} AND sessionID = {provide from loop}
LIMIT 1.
Peter Aganyo
Tim Winters wrote:
Hello,
Very sorry to everyone about the confusing message. I should have read
it over again before pressing send.
First of all I'm looking for userID 999. A typo in the message not in
the code.
The table is set up like this.
Table name sti_tracking
hitID (primary key) (autonumber)
userID
sessionID
date
time
pageName
What it's for is a simple page tracing counter for a FLash site. Each
time a section is accessed a new row is written in the table.
userID identifies the user. So if the user comes to the site today and
comes back again tomorrow the userID will be maintained.
sessionID identifies 1 visit to the site. During 1 visit a user may
view many sections within the site but as long as he doesn't close the
browser the session number remains the same. Date and time will always
be different (as will the hitID obviously).
So what I want to be able to do is single out a user (999) and retrieve
all the sessions he was involved in. But I don't want duplicate
session numbers (one is enough).
Make any more sense?
Tim Winters
Creative Development Manager
Sampling Technologies Incorporated
--snip--
While I was trying to figure an elegant solution to this I noticed that
you have a separate date and time field. Is there a reason for this. It
would be easier to get single row for each sessionID if they were one
field. Otherwise I think you will have to go with the method Peter
proposed above.
William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie SELECT problem
Tim:
Assuming that in your ealier posting the 99 was supposed to be 999,
then
the solution given by Mike Hillyer is excellent and should work.
However, when I read your new posting, I seem to get confused. The
scenario sounds totally different - excuse me - from the earlier one
and
would therefore need a different solution. You might help us by
giving
sample data.
Or is this what you mean by But I don't want duplicate session
numbers (one is enough)? == In a single session (sessionID) user 999
(userID 999) may visit 3 pages. This results in three inserts being
made into table sti_tracking all having same sessionID and userID.
Correct? When retrieving you do not want to retrieve all these three
records. Correct?
You just want one of the records. Which one? The first, second or
third because they each probably have a different time and pageName
(even
date!!). If you did not want the date, time and pageName then the
solution is simple SELECT DISTINCT userID, sessionID FROM sti_tracking
WHERE userID = 999.
If you do not care which of the entries (3 in my example) is returned
and you still want the date, time and pageName (my guess is the first
will be returned), then you need to generate all the distinct userID
and
sessionID pairs using the above SQL. Then for each pair (use a loop)
run SELECT userID, sessionID, date, time, pageName FROM sti_tracking
WHERE userID = {provide from loop} AND sessionID = {provide from
loop} LIMIT 1.
Peter Aganyo
Tim Winters wrote:
Hello,
Very sorry to everyone about the confusing message. I should have
read
it over again before pressing send.
First of all I'm looking for userID 999. A typo in the message not in
the code.
The table is set up like this.
Table name sti_tracking
hitID (primary key) (autonumber)
userID
sessionID
date
time
pageName
What it's for is a simple page tracing counter for a FLash site. Each
time a section is accessed a new row is written in the table.
userID identifies the user. So if the user comes to the site today and
comes back again tomorrow the userID will be maintained.
sessionID identifies 1 visit to the site. During 1 visit a user may
view many sections within the site but as long as he doesn't close
the browser the session number remains the same. Date and time will
always be different (as will the hitID obviously).
So what I want to be able to do is single out a user (999) and
retrieve
all the sessions he was involved in. But I don't want duplicate
session numbers (one is enough).
Make any more sense?
Tim Winters
Creative Development Manager
Sampling Technologies Incorporated
--snip--
While I was trying to figure an elegant solution to this I noticed that
you have a separate date and time field. Is there a reason for this.
It would be easier to get single row for each sessionID if they were one
field. Otherwise I think you will have to go with the method Peter
proposed above.
--Somedays I just need more tea.. ok how about this:
select sessionID,max(concat(idate,' ',itime)) from test group by sessionID
Note I thought date and time were reserved so I substituted..
William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upgrade from mm to Connector/J under tomcat
I've googled, but haven't got a hit, so please excuse if this has been covered many times before. System: linux, (debian for what it matters) tomcat 4.0 java 1.3.1 mysql 2.23.x Curently we are using the older org.gjt.mm.mysql.Driver drivers. We support many different sites which use .jsp pages and bean. If we move the org.gjt.mm.mysql.Driver drivers into the local lib directories WEB-INF/lib/.. and compile the beans with the driver. Can we upgrade one context at a time to Connector/J or must we shut down the server and upgrade them all at the same time? Anyone done this? William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: RAID hardware suggestions/experience
I have heard good thing about 3Ware, but I would suggest looking at the 8500-4 in combination with Western Digital's Raptor drive (http://www.tomshardware.com/storage/20030501/index.html). The Raptor is a 10,000 RPM SATA drive which, combined with the 8500-4 SATA Raid card should give excellent performance at a great price. I would also look at Opteron based servers if you are looking for performance for a good price. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: David Griffiths [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 17, 2003 10:59 AM To: [EMAIL PROTECTED] Subject: Re: RAID hardware suggestions/experience Anyone had any experience with 3Ware 7500-4 IDE RAID or the Promise SX-6000 IDE RAID cards? Specifically for Linux. Heard bad things about Promise, good about 3Ware. David - Original Message - From: Patrick Shoaf [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, June 17, 2003 9:40 AM Subject: Re: RAID hardware suggestions/experience I am using 4 120G IDE Drives with an Adaptec IDE RAID Controller on RedHat Linux providing 240G of RAID 5 storage. While not quite as fast as SCSI, I have found this to work very well. You should be able to pickup a nice dual processor XENON 2.4Ghz system w/1G Ram and IDE RAID loaded with RedHat Linux ES for around $4,000. At 12:25 PM 6/17/2003, you wrote: Hi there, Our databank with all tables and idices is about 130GB big. The biggest limitations we encounter are on the I/O side. Therefore we are willing to update our data storage system to a RAID system (RAID 0+1, RAID 5, or RAID 10). Has anyone experience with such RAID systems? What should we buy? From whom should we buy (We are located in New York City)? Do you have any experience you want to share? Thank you very much for your help and support! Bernd Patrick J. Shoaf, Systems Engineer mailto:[EMAIL PROTECTED][EMAIL PROTECTED] Midmon Internet Services, LLC 100 Third Street Charleroi, PA 15022 http://www.midmon.com Phone: 724-483-2400 ext. 105 or888-638-6963 Fax: 724-489-4386 A significant question remains for SATA: basic drive reliability. Related to that is length of time drive will remain available. A dirty secret of RAID is that when a drive goes it must be replaced you must replace it with the same drive (please..please tell me I'm wrong). So, unless you have a spare in the back you will end up replacing 3 drives (assuming Raid 5). That may be why the WD model has such low capacity compared with the normal IDE drives. Just my 2 cents worth. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Java/JDBC - Connection refused on Linux
I can't access MySQL using Java on Linux! It's killing my project. I
can access that very MySQL from PHP just fine. I have the same
database running on Windows so I can develop what I need to. Just
can't run anything for our users!!!
I don't think it's the firewall because I get the same error when I run
locally on the box.
Anyone face this before? I need HELP.
How are you trying to connect? You need a JDBC connector. You must
register the connection.. The current connector is Connector/J (see
www.mysql.com)
Basically you need to have it in the class path of java compiler (i.e, the
user who runs javac either explicitly or tomcat for .jsp pages. Its
similar to having to get pear going in php.
If you can be more specific the list can be of more help. I'm currently
using an earlier version of the driver so you will have to subsititute
connector/J equivalent:
for a bean:
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.DriverManager;
import java.sql.Statement;
this.DBUrl = jdbc:mysql://localhost:3306/+dbname;
try {
Class.forName(org.gjt.mm.mysql.Driver).newInstance();
this.conn =
DriverManager.getConnection(this.DBUrl,this.dbuser,this.dbpasswd);
}
catch (Exception e) {
e.printStackTrace();
}
Hope this helps..
William R. Mussatto, Senior Systems Engineer
Ph. 909-920-9154 ext. 27
FAX. 909-608-7061
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Which version do I install?
I am used to using P3 and P4 machines with the x86 download. I just got myself a Dual Xeon 2.60 Ghz machine. Does this still use the x86 download, or is there a better binary to use (ie IA64)? Any other tips for someone new to the Xeon chip family with linux would be nice too. -- This is a x86 (32 bit) chip. It may or may not have 'hyperthreading' where it can act as two processors. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Connector/J
Hi, I am using MySQL Connector/J JDBC Driver to support the=20 ResultSet.CONCUR_UPDATABLE. I am using J2sdk 1.4 Tomcat 4.1.1 with = MySQL: I am using a jsp file to use the Driver. After running the JSP file = following Error has been generated.=20 org.apache.jasper.JasperException: = org.gjt.mm.mysql.Connection.createStatement(II)Ljava/sql/Statement; at = org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.jav= a:248) at = org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:295) .. Thanks for your help. eff. Usman Somehow you are still using the older org.gjt.mm.mysql drivers not connector/J. When I asked many moons ago I was told that they could not both be used at the same time by Tomcat even in separate contexts, but YMMV. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why does auto increment not take into account deleted rows?
I have one column as an auto increment for adding numbers: 1 2 3 4 5 If i delete row 3 then add a new row and view the results i get: 1 2 4 5 6 As you can see 3 has been deleted and its now added 6! Is this normal?. How can i get it to always display numbers in this column in sequence. TIA I'm assuming you inserted a new record after deleting record 3 and are wondering why it didn't reuse '3'. Yes. Auto-increment columns are frequently used to link tables in a relationship. If you reused '3' the associated records would think the originial record was still there not that a new one had been substituted. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join problem
O. I've got a headache trying to understand joins. I'm definitely NOT a database guru. Why in the world doesn't this work? SELECT dacspriv_name FROM dacspriv WHERE dacspriv_id not in (SELECT dacspriv_id FROM dacs_access JOIN users ON dacs_access.user_id=users.user_id WHERE users.username='sator') Susan Ator Online Services Engineer National Public Radio Distribution Division [EMAIL PROTECTED] Short answer is mysql does not do sub-selects (i.e., a select inside of a select). The join part is not this issue. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Join problem
Short answer is mysql does not do sub-selects (i.e., a select inside of a select). The join part is not this issue. Wouldn't this depend on the version... I thought the newest versions, 4.x+, supported sub-selects. Ryan __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com I sit corrected, 4.1x but its alpha from the manual: Subqueries are supported in MySQL version 4.1. I run a production IPP so we run debian with is very far BACK from the bleeding edge. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Join problem
Well, I'm running 3.23.54 on Red Hat 7.3. Given this, how in the world do I accomplish the following: I have these tables: dacspriv - with dacspriv_id,dacspriv_name,short_name users - with user_id,username dacs_access - with dacsaccess_id,dacspriv_id,user_id I need to be able to return a list of dacspriv.short_name where user.user_id IS NOT in dacs_access but ONLY for that user_id (I have over 1700 users with multiple mappings in dacs_access). susan -Original Message- From: Ryan McDougall [mailto:[EMAIL PROTECTED] Sent: Friday, May 30, 2003 12:11 PM To: mysql Subject: Re: Join problem Short answer is mysql does not do sub-selects (i.e., a select inside of a select). The join part is not this issue. Wouldn't this depend on the version... I thought the newest versions, 4.x+, supported sub-selects. Ryan Ok let's see: select dacspriv_name,short_name from dacspriv,users LEFT JOIN dacs_access on dacs_access.user_id = users.user_id where dacs_access.user_id is NULL and users.user_id = WHATEVER The key is dacs_access.user_id is NULL While I haven't tried it with your data, I've used this in the past. For speed recommend user_id's in all tables be indexed. Obviously replace 'WHATEVER' with the userID value. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Migrating to Connector/J
We run Tomcat4. Currently we are using the older mm- drivers. Is there anyway to move to connector /J one context at a time rather than all at once? William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Storing Images in MySQL
I assumed to store the data in mysql you did something like uuencode and inserted that data into the db? Could the decode method be different on redhat 7.3? -Original Message- From: vernon [mailto:[EMAIL PROTECTED] Sent: Friday, March 28, 2003 2:19 PM To: [EMAIL PROTECTED] Subject: Storing Images in MySQL Hey all, I'm using phpNewsAds and am wanting to store images in MySQL. I have this working on my Red Hat 7.2 Box and am moving to a new server running Red Hat 7.3 and of course, after moving the database the images are not being dispalyed. The database, however, is working. I'm assuming that maybe something in MySWL needs to me turned on and I have no idea what that is. Anyone have any ideas? Thanks. Why not just store it in a binary field? That said, if you are going to use any programming language, why not store the pointer in the database and the image on the disk? Only case where this would be a problem would be if the numbers of images were so large that the file look up mechanism of the file system began to be a factor. In cases like that I use a tree of subdirectories tied to something like ISBM number. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Slow Inserts
I've tried it both as fixed (char) and variable (varchar). Interestingly when I set is as char when building the table, MySQL changes it to varchar sometimes (but not always). Here's a structure dump: CREATE TABLE soldierMain ( id int(20) NOT NULL auto_increment, timeadded varchar(14) NOT NULL default '', lastupdate timestamp(14) NOT NULL, name varchar(50) default NULL, email varchar(40) NOT NULL default '', status tinyint(1) NOT NULL default '0', PRIMARY KEY (id) ) TYPE=MyISAM; Here's what it looked like when I ran the import: CREATE TABLE soldierMain ( id int(20) NOT NULL auto_increment, timeadded varchar(14) NOT NULL default '', lastupdate timestamp(14) NOT NULL, name char(50) default NULL, email char(40) NOT NULL default '', status tinyint(1) NOT NULL default '0', PRIMARY KEY (id) ) TYPE=MyISAM; Incidentally - I waited a long time to post my own issue to this list and I'm quite pleased by the responsiveness and ideas I'm getting. Thanks to all that are consider the issues I'm having. Dan -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Thursday, March 27, 2003 4:40 PM To: 'Dan Wright'; [EMAIL PROTECTED] Subject: RE: Slow Inserts What does the table DDL look like. Is the table a fixed or dynamic format? -Original Message- From: Dan Wright [mailto:[EMAIL PROTECTED] Sent: Thursday, March 27, 2003 11:08 AM To: [EMAIL PROTECTED] Subject: Slow Inserts I've been having trouble with some large tables getting what seems to be corrupted. Here's the situation: I have several tables that have 3 million to as much as 7 million records. I have a process that I run against those tables that pulls out a record based on specific criteria (select id,name from table where name !='' and status=0) does something in Perl and then changes the record it just pulled to a status of 9. So basically - every time I run this process, every records is scanned and many (90%) are changed to the status of 9. Well - on a newly imported list, it screams through that and I can get upwards of 3 million per second. Each time I run the process, it gets slower, however. I've used myisamchk and optimize table and neither seem to have any affect on the performance. The only thing that seems to work is mysqldumping the whole table, dropping the table and reimporting the table. I've read up on the site and have found a lot about what could be causing this and have tried many things. Now that I've found what's wrong and how to fix it, I'm happy, but I'd rather not have to dump and reimport. I'm running MySQL 4.0.11 on RHL 7.2. I'm using the huge my.cnf file with some minor tweaks. The tables I'm speaking of have no indexes in it. They had them, but I dumped them and that gave me a huge insert performance gain, but I'm still seeing slowdowns the more I run the process on the file. Thanks in advance, Dan If you have a varchar, text, or blob type field then MySQL silently converts all character fields longer than one character to varchar. This may be why you see it sometimes converting the fields. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign keys and being FIRST index
http://www.mysql.com/doc/en/SEC457.html states that there must be an index where the foreign key and the referenced key are listed as the FIRST columns. Will this restriction be lifted soon? It is incredibly frustrating. I don't see why they have to be indexes, and more importantly, I don't see why they have to be FIRST! Ugh. Major performance hit would be a guess. Otherwise the database would have to do a table scan. Think about how it would find the related record. I think is a requirement of db2 as well William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 - 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: Temporary Tables
At 23:19 + 3/4/03, Mamatha Balasubramanian wrote: Thank you once again. I have a web-interface that does search on a given text and I would have a script that creates a temporary table. So according to you, in my script, I just to need create a temporary table and not have to worry about another client using the same web interface (and thereby using the same program). Can you please elaborate a little more on this? Sure. You are incorrect. :-) That is, you're making an assumption that cannot necessarily be made. If you can guarantee that the web script will establish a new connection, and the connection will terminate when the script ends, you can indeed do what you describe above. But you *cannot* do that if you're running your script in an environment that uses persistent connections that may be used by successive instances of the script. PHP persistent connections fall into this class, for example. Several requests might be served by the same instance of the web server process, and you don't know that one request won't be getting the connection used by a previous request. In that case, the connection won't have closed, and the TEMPORARY table won't have disappeared. You can guard against this by issuing this query before creating the TEMPORARY table: DROP TABLE IF EXISTS tbl_name Thanks, Mamatha Probably even better to have the Drop Table at the end of the script that used it. From: Paul DuBois [EMAIL PROTECTED] To: Mamatha Balasubramanian [EMAIL PROTECTED],[EMAIL PROTECTED] Subject: Re: Temporary Tables Date: Tue, 4 Mar 2003 17:06:30 -0600 At 23:00 + 3/4/03, Mamatha Balasubramanian wrote: Hi, I would like to know how MySQL handles multiple temporary tables? 1. Can multiple temporary tables be created at the same time? 2. If so, how does MySQL differentiate them - do we need to explicitly give them different names inorder to identify them or does MySQL provide a timestamp (or use some other means) to identify the tables? You can create multiple temporary tables, but they must have different names. A TEMPORARY table can have the same name as a non-TEMPORARY table. The non-TEMPORARY table is hidden to the client that creates the TEMPORARY table as long as the TEMPORARY table exists. A second TEMPORARY table with the same name cannot be created. This is on a connection-specific basis. Two clients each can create a TEMPORARY table with the same name. Only the table created by a given client is visible to that client. I use MySQL 4.0.7 on Red Hat. Thanks, Mamatha William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL or PostgreSQL
Since you're posting on a MySQL list, you could probably expect some biased responses. Could you post more about what you need to use a database for, i.e., what are your needs, wants, what kind of data are you handling, connection rates, serving platform, code base, etc. Here are a few URLs I was able to google up for your specific question: http://www.webtechniques.com/archives/2001/09/jepson/ http://librenix.com/?inode=1266 http://phd.pp.ru/Software/SQL/PostgreSQL-vs-MySQL.html These link seems to be a bit dated. I know they contain obsolete information on mysql. -Original Message- From: InfoSysNCA [mailto:[EMAIL PROTECTED] Sent: Friday, February 28, 2003 11:24 AM To: [EMAIL PROTECTED] Subject: MySQL or PostgreSQL Hi! I've just started working in Linux, but I'd like to know which database is better to use, MySQL or PostgreSQL. Which one would be better in the long run? -- Regards, Neil - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: count rows
Here's a nice MySQL simple returning records query question! I want to return a staement saying 'number of records shave been found' for your selection. so presumably this is a simple row count based on the the returning recordset? Andrew Either with PHP or C you can use mysql_num_rows() In perl:$numRows = $sth-rows; Note the following warning from the DBI man page: Generally, you can only rely on a row count after a non-SELECT execute (for some specific operations like UPDATE and DELETE), or after fetching all the rows of a SELECT statement. Bye! William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 - 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
