Re: MySQL and SSL
Michael Dykman wrote: could someone please tell me which versions of mysql support SSL connections for both clients and replication slaves? As far as I understand SSL was introduced in 4.0 and SSL-replication was introduced in 4.1.1. http://dev.mysql.com/doc/mysql/en/SSL_options.html http://dev.mysql.com/doc/mysql/en/Replication_Options.html We've started using replication over the Internet in 2001 using SSH-Tunnels (SSH-Port-Forwarding) which works fine, too. We haven't had any problems. regards Jan Kirchhoff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Large 30 GB Database
Hi All, I have been asked to provide a searchable electoral roll. This data comprises of first name, surname, address for every person over 18 in the UK. Approximately 30 GB The data is to be used to find people in a certain area. EG All John Smith in London. All Sarah Dean in St Johns Road, Manchester. Should I even attempt this using mysql? Has anyone played with this much data in mysql? Thanks Robert -- Robert Harrison Bsc MBSC CITP IT Manager Harrison Goddard Foote +44 113 2330110 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OT, but real: Hoax or legit
Richard, I received the same. I concluded from the typos that the message is a hoax. Regards, Heikki - Original Message - From: Richard Mixon (qwest) [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, July 28, 2004 7:16 AM Subject: OT, but real: Hoax or legit Is anyone else getting this? Is this for real or someone sending SPAM or viruses? Thanks - Richard -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 27, 2004 8:21 PM To: [EMAIL PROTECTED] Subject: IMPORTANT: Please Verify Your Message Hello [EMAIL PROTECTED] , [EMAIL PROTECTED] is currently protecting themselves from receiving junk mail using Spamcease Just this once, click the link below so I can receive your emails. You won't have to do this again. http://www.tgpwizards.com/spamcease2/verify.php?id=2355087 -- 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]
installation fails with max-4.1.3-beta-sgi but not 4.1.2-alpha
Description: The mysql_install_db script in mysql-max-4.1.3-beta-sgi-irix6.5-mips crashes with a bus error: Installing all prepared tables scripts/mysql_install_db[218]: 1352683 Bus error Installation of system tables failed! No files were present in ./data other than empty mysql and test directories. No log file was found. Changing directory ownerships (as per INSTALL-BINARY) and re-execution of mysql_install_db resulted in the same error. This was true with and without setting CAP_SCHED_MGT+epi (as recommended in the MySQL SGI notes). Running mysqld --skip-grant and mysqld --help also produced bus errors. In contrast, running mysql_install_db from a fresh install of mysql-standard-4.1.2-alpha-sgi-irix6.5-mips does NOT result in a crash. How-To-Repeat: Follow instructions from the INSTALL-BINARY file except that the groupadd and useradd tasks are performed manually for SGI. A crash will be produced from scripts/mysql_install_db --user=mysql. Fix: Unknown but it seems clear the main problem lies with the mysqld binary. Submitter-Id: submitter ID Originator: Wesley Schaal Organization: Uppsala University MySQL support: none Synopsis: installation fails with max-4.1.3-beta-sgi but not 4.1.2-alpha Severity: critical Priority: high Category: mysql Class: sw-bug Release:mysql-4.1.3-beta-max (Official MySQL-max binary) C compiler: C++ compiler: Environment: System: IRIX mach006 6.5 04091958 IP32 Some paths: /usr/sbin/perl /sbin/make /usr/freeware/bin/gmake /usr/freeware/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/freeware/lib/gcc-lib/mips-sgi-irix6.2/2.95.2/specs gcc version 2.95.2 19991024 (release) Compilation info: CC='cc' CFLAGS='-DBIG_TABLES -O3 -mp -mips4 -OPT:Olimit=0 -TARG:platform=IP27 -Xcpluscomm -I/usr/local/include -I/usr/include -I/usr/freeware/include' CXX='CC' CXXFLAGS='-DBIG_TABLES -O3 -mips4' LDFLAGS='-mp -W,rpath=/usr/local/lib -L/usr/freeware/lib32 -L/usr/lib32 -L/usr/freeware/lib32' ASFLAGS='' LIBC: -r-xr-xr-t1 root sys 242 Nov 28 2003 /lib/libc.so.1 lrwxrwxrwx1 root sys 19 Nov 10 1999 /usr/lib/libc.so - ../../lib/libc.so.1 lrwxrwxrwx1 root sys 19 Nov 10 1999 /usr/lib/libc.so.1 - ../../lib/libc.so.1 Configure command: ./configure '--prefix=/usr/local/mysql' '--localstatedir=/usr/local/mysql/data' '--libexecdir=/usr/local/mysql/bin' '--with-comment=Official MySQL-max binary' '--with-extra-charsets=complex' '--with-server-suffix=-max' '--enable-thread-safe-client' '--enable-local-infile' '--disable-shared' '--with-libedit' '--with-embedded-server' '--with-innodb' 'CC=cc' 'CFLAGS=-DBIG_TABLES -O3 -mp -mips4 -OPT:Olimit=0 -TARG:platform=IP27 -Xcpluscomm -I/usr/local/include -I/usr/include -I/usr/freeware/include' 'CXXFLAGS=-DBIG_TABLES -O3 -mips4' 'CXX=CC' 'LDFLAGS=-mp -W,rpath=/usr/local/lib -L/usr/freeware/lib32 -L/usr/lib32 -L/usr/freeware/lib32' -- # Wesley Schaal, PhD # Dept of Medicinal Chemistry (Org Pharm) # BMC B5:530b, Box 574, Uppsala University # SE-751 23 Uppsala, Sweden # Tel: +46-18-471-5013 Fax: +46-18-471-5010 # E-mail: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to show comments/annotations in MySQL client output
Michael Stassen wrote: Paul DuBois wrote: At 15:57 -0400 7/27/04, Michael Stassen wrote: First, I should point out that I've never used mysql on Windows. The manual makes no mention that I can see of system not being supported on the Windows mysql client. On the other hand, Windows is so different that I http://dev.mysql.com/doc/mysql/en/mysql_Commands.html says: The edit, nopager, pager, and system commands work only in Unix. Well, there you have it. I recommended that very page earlier in the thread, but somehow missed that line. Thanks, Paul. Michael Thanks to both of you. I know what my options are now. - Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SHOW TABLE STATUS in MySQL 4.1.latest different from 4.1.1 !?
Hi all, I just noticed that the MySQL 4.1.latest version handles a SHOW TABLE STATUS different from 4.1.1! Instead of a field Type that holds the table type, it's now Engine. Just a quick question: who makes up these changes in a minor minor (x.x.x) release What do they expect from third party developers? In short: this is a stupid change. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Large 30 GB Database
Should I even attempt this using mysql? Has anyone played with this much data in mysql? I've got two 100 gig databases in mysql, and slave replication on both of them, the only time I have a problem is table scans, that much data will be slow. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Indexed Database still slow
Ok, I will be the first to say that I am learning about indexes, however it is my understanding that if I have a database with MANY rows and I wish my queries to be faster I should index my database. With that being said, I have 2 tables in my database that are being queried with a single query using a UNION these 2 tables combined are about 9 Million records (yes I said million). My query which is below takes about 1 minute to run, now some people would say that this isn't long, however when the 2 tables were sub 5 million it only took a matter of about 20 seconds to run, so I figure I need an index. So I have created an index called Main within both tables and added 6 columns to that index, most of the columns that are used in my query. Sorry for the long background, but here is the problem, my query DID NOT speed up at all. It still takes right at 1 minute per query, so indexing didn't buy me anything as far as I can tell. Can someone tell me how the indexes are supposed to be done ( to ensure that I did it correctly) and tell me if they think that it should have sped up or if there is a more efficient way to do my query. ###QUERY HERE ### Select ALL PRTC_DIALUP.Id, PRTC_DIALUP.Date, PRTC_DIALUP.Time, PRTC_DIALUP.Record_Type, PRTC_DIALUP.Full_Name, PRTC_DIALUP.Framed_IP_Address from PRTC_DIALUP Where PRTC_DIALUP.Framed_IP_Address = 'someipaddress' AND (PRTC_DIALUP.Date = 'one-date-here' OR PRTC_DIALUP.Date = 'one-day-earlier' OR PRTC_DIALUP.Date = 'one-day-later') UNION Select ALL PRTC_DSL.Id, PRTC_DSL.Date, PRTC_DSL.Time, PRTC_DSL.Record_Type, PRTC_DSL.Full_Name, PRTC_DSL.Framed_IP_Address from PRTC_DSL Where PRTC_DSL.Framed_IP_Address = 'someipaddress' and (PRTC_DSL.Date = 'one-date-here' OR PRTC_DSL.Date = 'one-day-earlier' OR PRTC_DSL.Date = 'one-day-later') order by Full_Name, Time; ### END QUERY ### Chris Hood Investigator Verizon Global Security Operations Center Email: mailto:[EMAIL PROTECTED] [EMAIL PROTECTED] Desk: 972.399.5900 Verizon Proprietary NOTICE - This message and any attached files may contain information that is confidential and/or subject of legal privilege intended only for the use by the intended recipient. If you are not the intended recipient or the person responsible for delivering the message to the intended recipient, be advised that you have received this message in error and that any dissemination, copying or use of this message or attachment is strictly forbidden, as is the disclosure of the information therein. If you have received this message in error please notify the sender immediately and delete the message.
Re: OT, but real: Hoax or legit
Hi, I received the same. I concluded from the typos that the message is a hoax. I got two more today. And yeah I believe its a hoax. Of course the only way that we'd be sure is if said list manager were to pipe up here and state that with certainty. My guess is that its a harvester address thats on the list and thereby sending these msgs to anyone that's posted to the list, quite ingenious really. M i l e s +++ President Toolbox Architect MagicMiles Software (413) 374 - 5161 PO Box 414, Northampton, MA 01060 http://www.magicmiles.com/ [EMAIL PROTECTED] aim/yahoo/msn: magikmiles -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SHOW TABLE STATUS in MySQL 4.1.latest different from 4.1.1 !?
Hi Jocelyn, From the MySQL doc : The ENGINE and TYPE options specify the storage engine for the table. ENGINE was added in MySQL 4.0.18 (for 4.0) and 4.1.2 (for 4.1). It is the preferred option name as of those versions, and TYPE has become deprecated. TYPE will be supported throughout the 4.x series, but likely will be removed in MySQL 5.1. So I assume it makes sense to change it also for SHOW TABLE STATUS :) Well, in that case - the documentation fails to document the behaviour :-) The latest 4.1 beta does NOT have a Type column. Remove it in 5 or 5.1, fine - that's a major (and next major minor release). But not in a sub-minor release. A mistake then? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com I just noticed that the MySQL 4.1.latest version handles a SHOW TABLE STATUS different from 4.1.1! Instead of a field Type that holds the table type, it's now Engine. Just a quick question: who makes up these changes in a minor minor (x.x.x) release What do they expect from third party developers? In short: this is a stupid change. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.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]
binlog truncated in the middle of event
One of my slaves has decided to stop replicating every time I reset it, I get this 040728 8:46:46 Error reading packet from server: binlog truncated in the middle of event (server_errno=1236) 040728 8:46:46 Got fatal error 1236: 'binlog truncated in the middle of event' from master when reading data from binary log 040728 8:46:46 Slave I/O thread exiting, read up to log 'FINANCE-bin.185', position 419216838 All slaves share the same config, but this one refuses to work, the master server has plenty of drive space, I have made changes to the buffer sizes, I thought one of those could play into it anyone have a sugestion for a fix? both a 4.017 (found bugs in current version) master is on windows 2000, slave is on server 2003 From the main server.. +-++ | Variable_name | Value | +-++ | binlog_cache_size | 32768 | | bulk_insert_buffer_size | 536870912 | | delayed_queue_size | 1000 | | innodb_additional_mem_pool_size | 1048576| | innodb_buffer_pool_size | 8388608| | innodb_log_buffer_size | 1048576| | innodb_log_file_size| 5242880| | join_buffer_size| 536866816 | | key_buffer_size | 1572864000 | | max_binlog_cache_size | 4294967295 | | max_binlog_size | 1073741824 | | max_heap_table_size | 16777216 | | max_join_size | 4294967295 | | max_relay_log_size | 0 | | myisam_max_extra_sort_file_size | 268435456 | | myisam_max_sort_file_size | 2147483647 | | myisam_sort_buffer_size | 268435456 | | query_alloc_block_size | 8192 | | query_cache_size| 0 | | query_prealloc_size | 8192 | | range_alloc_block_size | 2048 | | read_buffer_size| 268431360 | | read_rnd_buffer_size| 262144 | | sort_buffer_size| 268435448 | | thread_cache_size | 8 | | tmp_table_size | 419430400 | | transaction_alloc_block_size| 8192 | | transaction_prealloc_size | 4096 | +-++ From the slave server.. +-++ | Variable_name | Value | +-++ | binlog_cache_size | 32768 | | bulk_insert_buffer_size | 8388608| | delayed_queue_size | 1000 | | innodb_additional_mem_pool_size | 1048576| | innodb_buffer_pool_size | 8388608| | innodb_log_buffer_size | 1048576| | innodb_log_file_size| 5242880| | join_buffer_size| 536866816 | | key_buffer_size | 1572864000 | | max_binlog_cache_size | 4294967295 | | max_binlog_size | 1073741824 | | max_heap_table_size | 16777216 | | max_join_size | 4294967295 | | max_relay_log_size | 0 | | myisam_max_extra_sort_file_size | 268435456 | | myisam_max_sort_file_size | 2147483647 | | myisam_sort_buffer_size | 268435456 | | query_alloc_block_size | 8192 | | query_cache_size| 0 | | query_prealloc_size | 8192 | | range_alloc_block_size | 2048 | | read_buffer_size| 314568704 | | read_rnd_buffer_size| 262144 | | sort_buffer_size| 268435448 | | thread_cache_size | 8 | | tmp_table_size | 33554432 | | transaction_alloc_block_size| 8192 | | transaction_prealloc_size | 4096 | +-++ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using function followed by order by function_name(table.field_name).....
I hope you have already tried just plain-old ORDER BY field_name in your query. What is wrong with the default order presented? Can you show an example of how the ORDER BY did not solve your problem along with an example of what it should have been for your situation? Thanks in advance, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Scott Fletcher [EMAIL PROTECTED] wrote on 07/27/2004 05:29:05 PM: I'm having a little bit of a trouble with the use of the SQL function... What I have here is a webpage that show the row of data, the web user get to click on the field to sort the row by the field name. So, when I use order by char(field_name), the data doesn't turned out right in alpha-numeric in ascending order... --snip-- Debug Test (WM Account) 1 ABC Company Riverknoll A C SDFONE'sdf SeaWater --snip-- I would like the first few letter of each row be in correct alpha-numeric order regardless of what character is there in the data, like (, ), ', ?, etc... Thanks, FletchSOD
Re: using max() on update
That error suggests that `status` exists in both tmpupdateme and dCopy (it
probably doesn't, but that's what it means). We should be able to cure
this by specifically referencing the column by adding its table's alias
before the field name. That changes the update statement to be :
update dCOPY d inner join tmpupdateme tum on tum.office = d.office and
tum.maxdate = d.dateposted set d.status = 1;
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Louie Miranda [EMAIL PROTECTED] wrote on 07/27/2004 10:17:01 PM:
Hi, i was able to try your suggestion. But error seems to show up when
updating already...
+---++--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+---++--+-+-+---+
| datacount | int(255) | | | 0 | |
| office| varchar(255) | | | | |
| filename | varchar(255) | | | | |
| status| enum('0','1') | | | 0 | |
| maxdate | varchar(10) binary | YES | | NULL| |
+---++--+-+-+---+
5 rows in set (0.00 sec)
mysql update dCOPY d inner join tmpupdateme tum on tum.office =
d.office and tum.maxdate = d.dateposted set status = 1;
ERROR 1052: Column: 'status' in field list is ambiguous
mysql
- Original Message -
From: [EMAIL PROTECTED] [EMAIL PROTECTED]
Date: Tue, 27 Jul 2004 09:17:44 -0400
Subject: Re: using max() on update
To: Louie Miranda [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Here's how I handle these situations. If I need all of the data from
the row that contains the maximum of an unindexed column:
SELECT @maxval := MAX(column_name) FROM tablename;
SELECT * FROM tablename WHERE column_name = @maxval;
For the same thing but for an indexed column
SELECT * FROM tablename ORDER BY column_name DESC LIMIT1;
To get all of the rows that contain the MAX() value of column2 for all
values of column1, I need to use a temporary table:
CREATE TEMPORARY TABLE tmpMax
SELECT column1, MAX(column2) as maxval
FROM tablename
GROUP BY column1;
SELECT *
FROM tablename t
INNER JOIN tmpMax tm
ON tm.column1 = t.column1
AND tm.column2 = t.column2;
Now for your case. You need to update all of the rows that have the
most recent dateposted for each office value.
CREATE TEMPORARY TABLE tmpUpdateMe
SELECT office, MAX(dateposted) as maxdate
FROM dCOPY
GROUP BY office;
UPDATE dCOPY d
INNER JOIN tmpUpdateMe tum
ON tum.office = d.office
AND tum.maxdate = d.dateposted
SET status = 1;
You should only need to wrap the 1 with quotes (like '1') if you are
trying to insert it into a character-based field. You do not need
quotes on any numeric value being assigned to a numeric column.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Louie Miranda [EMAIL PROTECTED] wrote on 07/27/2004 03:04:27 AM:
Just recently post a problem on how can i see all max(dateposted) on
all of my records by doing this..
select datacount,office,filename,status, max(dateposted) from dCOPY
group by office;
now, i was wondering if i can use max() on update to update all my
current records only..
i tried this:
mysql update dCOPY set status = '1' where max(dateposted);
ERROR : Invalid use of group function
mysql
But as you can see, it returns an error for an invalid group function.
--
Louie Miranda
http://www.axishift.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
Louie Miranda
http://www.axishift.com
Re: Indexed Database still slow
Your problem is that you created a single index. An index is just a presorted list of the data. The first column in the index is the most relevant, being sorted by that column first. If you have an index on State+County+Town, how would you quickly find a town? You can't if you don't know the State or County, because the data is sorted first by State, then by County, then by Town. You need to create a separate index for each column you are searching on. The only time you will create an single index containing multiple columns is if you will always be searching on the indexed fields in the order you declared them in your index (i.e. State+County). On Jul 28, 2004, at 8:25 AM, [EMAIL PROTECTED] wrote: Ok, I will be the first to say that I am learning about indexes, however it is my understanding that if I have a database with MANY rows and I wish my queries to be faster I should index my database. With that being said, I have 2 tables in my database that are being queried with a single query using a UNION these 2 tables combined are about 9 Million records (yes I said million). My query which is below takes about 1 minute to run, now some people would say that this isn't long, however when the 2 tables were sub 5 million it only took a matter of about 20 seconds to run, so I figure I need an index. So I have created an index called Main within both tables and added 6 columns to that index, most of the columns that are used in my query. Sorry for the long background, but here is the problem, my query DID NOT speed up at all. It still takes right at 1 minute per query, so indexing didn't buy me anything as far as I can tell. Can someone tell me how the indexes are supposed to be done ( to ensure that I did it correctly) and tell me if they think that it should have sped up or if there is a more efficient way to do my query. ###QUERY HERE ### Select ALL PRTC_DIALUP.Id, PRTC_DIALUP.Date, PRTC_DIALUP.Time, PRTC_DIALUP.Record_Type, PRTC_DIALUP.Full_Name, PRTC_DIALUP.Framed_IP_Address from PRTC_DIALUP Where PRTC_DIALUP.Framed_IP_Address = 'someipaddress' AND (PRTC_DIALUP.Date = 'one-date-here' OR PRTC_DIALUP.Date = 'one-day-earlier' OR PRTC_DIALUP.Date = 'one-day-later') UNION Select ALL PRTC_DSL.Id, PRTC_DSL.Date, PRTC_DSL.Time, PRTC_DSL.Record_Type, PRTC_DSL.Full_Name, PRTC_DSL.Framed_IP_Address from PRTC_DSL Where PRTC_DSL.Framed_IP_Address = 'someipaddress' and (PRTC_DSL.Date = 'one-date-here' OR PRTC_DSL.Date = 'one-day-earlier' OR PRTC_DSL.Date = 'one-day-later') order by Full_Name, Time; ### END QUERY ### Chris Hood Investigator Verizon Global Security Operations Center Email: mailto:[EMAIL PROTECTED] [EMAIL PROTECTED] Desk: 972.399.5900 Verizon Proprietary NOTICE - This message and any attached files may contain information that is confidential and/or subject of legal privilege intended only for the use by the intended recipient. If you are not the intended recipient or the person responsible for delivering the message to the intended recipient, be advised that you have received this message in error and that any dissemination, copying or use of this message or attachment is strictly forbidden, as is the disclosure of the information therein. If you have received this message in error please notify the sender immediately and delete the message. -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: binlog truncated in the middle of event
Update on this, I found that when the slave stops, all I have to do is start the slave and it's good again Here's what the log shows.. the only thing I did was start slave and it picked right back up 040728 9:25:13 Error reading packet from server: binlog truncated in the middle of event (server_errno=1236) 040728 9:25:13 Got fatal error 1236: 'binlog truncated in the middle of event' from master when reading data from binary log 040728 9:25:13 Slave I/O thread exiting, read up to log 'FINANCE-bin.186', position 171309530 040728 9:29:40 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'FINANCE-bin.186' at position 171309530 repeats removed 040728 9:29:56 Error reading packet from server: Lost connection to MySQL server during query (server_errno=2013) 040728 9:29:56 Slave I/O thread: Failed reading log event, reconnecting to retry, log 'FINANCE-bin.186' position 171309530 040728 9:29:58 Error reading packet from server: Lost connection to MySQL server during query (server_errno=2013) 040728 9:29:58 Slave I/O thread: Failed reading log event, reconnecting to retry, log 'FINANCE-bin.186' position 171309530 040728 9:30:26 Slave: load data infile on table 'e47wk_in' at log position 979433898 in log 'FINANCE-bin.185' produced 601027 warning(s). Default database: 'finance' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Indexed Database still slow
I think the UNION is the right way to handle this, in fact, I would be tempted to break it into 6 UNIONS... more on that later. You really should concentrate mostly on coverage for fields used in your WHERE clauses, in this case: Framed_IP_Address and Date. Additional fields can be used to get data straight from the index but the docs say that they must be numeric (not character based). So, in the case of this query, those additional fields just make your index larger which takes longer to search. Try a two-field index and just this part of your subquery: Select ALL PRTC_DIALUP.Id, PRTC_DIALUP.Date, PRTC_DIALUP.Time, PRTC_DIALUP.Record_Type, PRTC_DIALUP.Full_Name, PRTC_DIALUP.Framed_IP_Address from PRTC_DIALUP Where PRTC_DIALUP.Framed_IP_Address = 'someipaddress' AND PRTC_DIALUP.Date = 'one-date-here' If I am right, that should return somewhere 2 seconds. This means that a 6-way union would return in somewhere near or below 12 seconds. In this case each query is doing an exact match on an index and the 6 queries unioned together should take less time than your 2 3-way queries. I call them 3 way as each half has to check for one of 3 dates. Also, if you need to ORDER BY the results of the UNION, you need to enclose each participating query in parentheses and put the ORDER BY clause after the last query. I went ahead and expanded your 2-query UNION into a 6-query UNION to illustrate: ( Select ALL PRTC_DIALUP.Id, PRTC_DIALUP.Date, PRTC_DIALUP.Time, PRTC_DIALUP.Record_Type, PRTC_DIALUP.Full_Name, PRTC_DIALUP.Framed_IP_Address from PRTC_DIALUP Where PRTC_DIALUP.Framed_IP_Address = 'someipaddress' AND PRTC_DIALUP.Date = 'one-date-here' ) UNION ( Select ALL PRTC_DIALUP.Id, PRTC_DIALUP.Date, PRTC_DIALUP.Time, PRTC_DIALUP.Record_Type, PRTC_DIALUP.Full_Name, PRTC_DIALUP.Framed_IP_Address from PRTC_DIALUP Where PRTC_DIALUP.Framed_IP_Address = 'someipaddress' AND PRTC_DIALUP.Date = 'one-day-earlier' ) UNION ( Select ALL PRTC_DIALUP.Id, PRTC_DIALUP.Date, PRTC_DIALUP.Time, PRTC_DIALUP.Record_Type, PRTC_DIALUP.Full_Name, PRTC_DIALUP.Framed_IP_Address from PRTC_DIALUP Where PRTC_DIALUP.Framed_IP_Address = 'someipaddress' AND PRTC_DIALUP.Date = 'one-day-later' ) UNION ( Select ALL PRTC_DSL.Id, PRTC_DSL.Date, PRTC_DSL.Time, PRTC_DSL.Record_Type, PRTC_DSL.Full_Name, PRTC_DSL.Framed_IP_Address from PRTC_DSL Where PRTC_DSL.Framed_IP_Address = 'someipaddress' and PRTC_DSL.Date = 'one-date-here' ) UNION ( Select ALL PRTC_DSL.Id, PRTC_DSL.Date, PRTC_DSL.Time, PRTC_DSL.Record_Type, PRTC_DSL.Full_Name, PRTC_DSL.Framed_IP_Address from PRTC_DSL Where PRTC_DSL.Framed_IP_Address = 'someipaddress' and PRTC_DSL.Date = 'one-day-earlier' ) UNION ( Select ALL PRTC_DSL.Id, PRTC_DSL.Date, PRTC_DSL.Time, PRTC_DSL.Record_Type, PRTC_DSL.Full_Name, PRTC_DSL.Framed_IP_Address from PRTC_DSL Where PRTC_DSL.Framed_IP_Address = 'someipaddress' and PRTC_DSL.Date = 'one-day-later' ) ORDER BY Full_Name, Time; I agree that it will take some additional time to parse those 6 queries instead of just 2 but I believe that you won't be able to notice the difference. I would compare those 4 extra queries to the # of queries per second your system handles now to get a rough estimate of the additional overhead involved. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 07/28/2004 08:25:36 AM: Ok, I will be the first to say that I am learning about indexes, however it is my understanding that if I have a database with MANY rows and I wish my queries to be faster I should index my database. With that being said, I have 2 tables in my database that are being queried with a single query using a UNION these 2 tables combined are about 9 Million records (yes I said million). My query which is below takes about 1 minute to run, now some people would say that this isn't long, however when the 2 tables were sub 5 million it only took a matter of about 20 seconds to run, so I figure I need an index. So I have created an index called Main within both tables and added 6 columns to that index, most of the columns that are used in my query. Sorry for the long background, but here is the problem, my query DID NOT speed up at all. It still takes right at 1 minute per query, so indexing didn't buy me anything as far as I can tell. Can someone tell me how the indexes are supposed to be done ( to ensure that I did it correctly) and tell me if they think that it should have sped up or if there is a more efficient way to do my query. ###QUERY HERE ### Select ALL PRTC_DIALUP.Id, PRTC_DIALUP.Date, PRTC_DIALUP.Time, PRTC_DIALUP.Record_Type, PRTC_DIALUP.Full_Name, PRTC_DIALUP.Framed_IP_Address from PRTC_DIALUP Where PRTC_DIALUP.Framed_IP_Address = 'someipaddress' AND (PRTC_DIALUP.Date = 'one-date-here' OR PRTC_DIALUP.Date = 'one-day-earlier' OR PRTC_DIALUP.Date = 'one-day-later') UNION
RE: Using function followed by order by function_name(table.field_name).....
Let me quote a little bit from the docs (http://dev.mysql.com/doc/mysql/en/String_functions.html): CHAR() interprets the arguments as integers and returns a string consisting of the characters given by the code values of those integers. NULL values are skipped. mysql SELECT CHAR(77,121,83,81,'76'); - 'MySQL' mysql SELECT CHAR(77,77.3,'77.3'); - 'MMM' That means that when it processes each of the fields in your resultset, it converts that string into some other string and is sorting by the results of that transformation. Definitely NOT what you wanted. Please, let's try again. Please, show me the results of just a plain ORDER BY field_name query (without the CHAR() function) and explain to me what is wrong... Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Scott Fletcher [EMAIL PROTECTED] wrote on 07/28/2004 09:12:45 AM: The example of ORDER BY that didn?t solve the problem is ? --snip-- Debug Test (WM Account) 1 ABC Company Riverknoll A C SDFONE'sdf SeaWater --snip-- which should be ? --snip-- ABC Company Debug Test (WM Account) 1 Riverknoll A C SDFONE'sdf SeaWater --snip-- The word, ?Debug Test (WM Account) 1? came before the ?ABC Company? which should be the other way around?. This show that the ?ORDER BY CHAR(??)? doesn?t work. I just don?t see how to use a function that would sort the char in ascending order properly? Thanks, FletchSOD From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 28, 2004 7:58 AM To: Scott Fletcher Cc: [EMAIL PROTECTED] Subject: Re: Using function followed by order by function_name(table.field_name). I hope you have already tried just plain-old ORDER BY field_name in your query. What is wrong with the default order presented? Can you show an example of how the ORDER BY did not solve your problem along with an example of what it should have been for your situation? Thanks in advance, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Scott Fletcher [EMAIL PROTECTED] wrote on 07/27/2004 05:29:05 PM: I'm having a little bit of a trouble with the use of the SQL function... What I have here is a webpage that show the row of data, the web user get to click on the field to sort the row by the field name. So, when I use order by char(field_name), the data doesn't turned out right in alpha-numeric in ascending order... --snip-- Debug Test (WM Account) 1 ABC Company Riverknoll A C SDFONE'sdf SeaWater --snip-- I would like the first few letter of each row be in correct alpha-numeric order regardless of what character is there in the data, like (, ), ', ?, etc... Thanks, FletchSOD
Re: A possible bug
thanks, but some of the tables have to be in MyIsam format, and i cannot see a workaround for them. (actually I discovered that the integrity of the data exported is just fine, but it seems that the server hangs trying to close the file handle. - Original Message - From: Nickolai Nielsen [EMAIL PROTECTED] To: Leonardo Javier Belén [EMAIL PROTECTED] Sent: Wednesday, July 28, 2004 9:34 AM Subject: SV: A possible bug Hi Leonardo i had a similar problem, it was with InnoDB tables, so i changed these values: # Set buffer pool size to 50-80% of your computer's memory set-variable = innodb_buffer_pool_size=512M set-variable = innodb_additional_mem_pool_size=100M in the my.ini Nickolai -Oprindelig meddelelse- Fra: Leonardo Javier Belén Sendt: 27. juli 2004 22:04 Til: [EMAIL PROTECTED] Emne: A possible bug Hi all, I am using MySQL ver. 4.1.3-beta on Windows 2000 pro and I found that, whenever i try to export data using the into outfile clause of the select command the server hangs and i need to restart the service. Has anyone faced the same problem, and if it is, how have you resolve it? I think it is wrong because the same select string on MySQL ver. 4.0.20 works fine. Leonardo J. Belén. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQLcheck-scramble vulerability info
Our security guy ran across an exploit in all MySQL versions before the June releases. I am trying to find information on how to patch to fix this vulnerability but I cannot find anything on MySQL's site! It is kind of ridiculous. I searched for check_scramble, zero-length comparison and even vuneralibility (the latter only had 7 hits). Has anyone seen better information on how to patch 4.0.18 to fix this bug? Check out http://securitytracker.com/alerts/2004/Jul/1010645.html for more information. Ben Ricker -- Ben Ricker Web Administrator Mastercard International, Inc. 904 North Third (636) 722-4697 - CONFIDENTIALITY NOTICE This e-mail message and any attachments are only for the use of the intended recipient and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient, any disclosure, distribution or other use of this e-mail message or attachments is prohibited. If you have received this e-mail message in error, please delete and notify the sender immediately. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: daisy-chaining replication
thank you again Paul, I did miss that one altogether, but that does not appear to be the whole problem. having added 'log-slave-updates' to all my.cnf of all the servers (just to be paranoid) I found no difference in the results. I still get the same errors message upon 'CHANGE MASTER TO ...' and the error log is substantially the same. On Tue, 2004-07-27 at 14:59, Paul DuBois wrote: At 14:31 -0400 7/27/04, Michael Dykman wrote: Hello, I'm running a development build of 4.1.3 and trying to set up of chain of replication servers. The first level of replication works beautifully but every effort to establish slaves on that first slave have been met with failure. here is the setup (log-bin is enabled on all 3 servers): server_a: is set up as primary master: all clients connect to this server and all data changes are made there. server_b: is setup as client with a clean copy of the data (restarting server_a with 'skip-networking' to take the clean cut and gather the master status info, then restarting server_a with networking enabled again). do the change master thing on server_b and start the slave, in due course 'show slave status' shows that it has caught up with server_a and all data is there server_c: setup is a problem (though not necessarily 'the problem'). having restarted server_b with skip-networking and stopped the slave, i take a cut of the data and get the master status info. having put the data cut on server_c, executing CHANGE MASTER TO MASTER_HOST='server_b', MASTER_USER='replica_dude', MASTER_PASSWORD='secret', MASTER_LOG_FILE='server_b.01', MASTER_LOG_POS=79;log-slave-updates reports: ERROR 1201 at line 1: Could not initialize master info structure; more error messages can be found in the MySQL error log contents of the error log: ... Failed to open the relay log './server_b-relay-bin.01' (relay_log_pos 1011481184) 040727 11:40:34 Could not find target log during relay log initialization 040727 11:40:34 Failed to initialize the master info structure /usr/local/mysql/libexec/mysqld: ready for connections. Version: '4.1.3-beta-log' socket: '/tmp/mysql.sock' port: 3306 040727 11:41:26 Failed to open the relay log './server_b-relay-bin.01' (relay_log_pos 1011481184) 040727 11:41:26 Could not find target log during relay log initialization The file server_b-relay-bin.01 certainly is there and is permissioned mysql:mysql, as are all the data files. the only really spooky thing I can find going on is on server_b (slave to server_a, intended master to server_c). Having reenabled the slave and seeing it catch up to server_a (both 'show slave status' and empirical data tests confirm this) anther look at 'show master status' on server_b *still* reports: +-+--+--+--+ | File| Position | Binlog_Do_DB | Binlog_Ignore_DB | +-+--+--+--+ | server_b-bin.01 | 79 | | | +-+--+--+--+ it is not updating at all, even as replication data pours in. I have reinstalled server_b as a slave 3 times now using the data from server_a and no matter how much replication data server_b has gathered from server_a post-install, the position is always 79 (!). Can anyone lend me a clue? Did you start server b with --log-slave-updates so that it writes the events that it receives from its master to its own binary log? Just turning on binary logging on server b isn't enough. -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Indexed Database still slow
Brent, I humbly disagree with your analysis. I believe that it is well established in this list and other places (http://dev.mysql.com/doc/mysql/en/EXPLAIN.html) that the query engine only uses at most 1 index of the available indexes on any table involved in the query. It has also been shown that properly constructed multi-column indexes can significantly improve the performance of many queries (http://dev.mysql.com/doc/mysql/en/Query_Speed.html (and its sub-pages)) (http://dev.mysql.com/doc/mysql/en/Multiple-column_indexes.html). Based on this information, I dispute your assertion that by creating separate indexes on each column to be searched, christopher.l.hood will improve the performance of his query. I also dispute your statement that The only time you will create an single index containing multiple columns is if you will always be searching on the indexed fields in the order you declared them in your index as a multi-column index can be used to answer any query that involves just the first column, just the first and second columns, just the first, second, and third columns, etc. You do not need to search on every column of a multi-column index for it to be used to resolve a query. Also, under certain conditions (numeric columns), a multi-column index will also avoid the need for a direct table read to return data. This optimizes some queries enormously even though the additional columns may never appear in the WHERE clause of the query. (http://dev.mysql.com/doc/mysql/en/Where_optimisations.html) I do agree with you that a multi-column index will NOT aid to resolve any queries that search only on the second column, the second and third columns, etc. I also agree that if a query is searching for values listed first and third in a mutli-column query, that the index will be useful only for finding records containing the first value listed in the index. The third column value will have to be resolved by a seek of the resultset (after the index is applied). With greatest respect, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Brent Baisley [EMAIL PROTECTED] wrote on 07/28/2004 09:17:07 AM: Your problem is that you created a single index. An index is just a presorted list of the data. The first column in the index is the most relevant, being sorted by that column first. If you have an index on State+County+Town, how would you quickly find a town? You can't if you don't know the State or County, because the data is sorted first by State, then by County, then by Town. You need to create a separate index for each column you are searching on. The only time you will create an single index containing multiple columns is if you will always be searching on the indexed fields in the order you declared them in your index (i.e. State+County). On Jul 28, 2004, at 8:25 AM, [EMAIL PROTECTED] wrote: Ok, I will be the first to say that I am learning about indexes, however it is my understanding that if I have a database with MANY rows and I wish my queries to be faster I should index my database. With that being said, I have 2 tables in my database that are being queried with a single query using a UNION these 2 tables combined are about 9 Million records (yes I said million). My query which is below takes about 1 minute to run, now some people would say that this isn't long, however when the 2 tables were sub 5 million it only took a matter of about 20 seconds to run, so I figure I need an index. So I have created an index called Main within both tables and added 6 columns to that index, most of the columns that are used in my query. Sorry for the long background, but here is the problem, my query DID NOT speed up at all. It still takes right at 1 minute per query, so indexing didn't buy me anything as far as I can tell. Can someone tell me how the indexes are supposed to be done ( to ensure that I did it correctly) and tell me if they think that it should have sped up or if there is a more efficient way to do my query. ###QUERY HERE ### Select ALL PRTC_DIALUP.Id, PRTC_DIALUP.Date, PRTC_DIALUP.Time, PRTC_DIALUP.Record_Type, PRTC_DIALUP.Full_Name, PRTC_DIALUP.Framed_IP_Address from PRTC_DIALUP Where PRTC_DIALUP.Framed_IP_Address = 'someipaddress' AND (PRTC_DIALUP.Date = 'one-date-here' OR PRTC_DIALUP.Date = 'one-day-earlier' OR PRTC_DIALUP.Date = 'one-day-later') UNION Select ALL PRTC_DSL.Id, PRTC_DSL.Date, PRTC_DSL.Time, PRTC_DSL.Record_Type, PRTC_DSL.Full_Name, PRTC_DSL.Framed_IP_Address from PRTC_DSL Where PRTC_DSL.Framed_IP_Address = 'someipaddress' and (PRTC_DSL.Date = 'one-date-here' OR PRTC_DSL.Date = 'one-day-earlier' OR PRTC_DSL.Date = 'one-day-later') order by Full_Name, Time; ### END QUERY ### Chris Hood Investigator Verizon Global Security Operations Center Email: mailto:[EMAIL PROTECTED] [EMAIL PROTECTED] Desk:
Re: SHOW TABLE STATUS in MySQL 4.1.latest different from 4.1.1 !?
In the last episode (Jul 28), Martijn Tonies said: Hi Jocelyn, From the MySQL doc : The ENGINE and TYPE options specify the storage engine for the table. ENGINE was added in MySQL 4.0.18 (for 4.0) and 4.1.2 (for 4.1). It is the preferred option name as of those versions, and TYPE has become deprecated. TYPE will be supported throughout the 4.x series, but likely will be removed in MySQL 5.1. So I assume it makes sense to change it also for SHOW TABLE STATUS :) Well, in that case - the documentation fails to document the behaviour :-) The latest 4.1 beta does NOT have a Type column. Remove it in 5 or 5.1, fine - that's a major (and next major minor release). But not in a sub-minor release. Well, 4.1 is still in Beta, so I don't see any problem with changes like this before it goes Stable. Remember that MySQL 3.x went through 23 minor releases and changed a whole lot more :) -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Indexed Database still slow
In the last episode (Jul 28), [EMAIL PROTECTED] said:
I think the UNION is the right way to handle this, in fact, I would be
tempted to break it into 6 UNIONS... more on that later.
I think his 2 original unions should suffice. Unions are great for
overcoming mysql's one index per table limitation when you have ORs in
your query referring to different fields. You don't need to split up the
ORs within those WHERE clauses because they're all looking at the same
field. You could even tidy the query up a bit by using the IN clause:
Where PRTC_DIALUP.Framed_IP_Address = 'someipaddress'
AND PRTC_DIALUP.Date in ('one-date-here','one-day-earlier')
but that's just syntax changes. Mysql will process it the same way. Two
indexes, one on PRTC_DIALUP (Framed_IP_Address, Date) and the other on
PRTC_DSL (Framed_IP_Address, Date) should be all Christopher needs.
Also use the EXPLAIN command to find out exactly what MySQL decides to do
for the query. Chapter 7.2 of the mysql manual covers this:
http://dev.mysql.com/doc/mysql/en/Query_Speed.html
--
Dan Nelson
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Finding duplicate values in a column
This seems like it should be easy, and I'd be happy for a simple reference to where in the manual or in Paul's book I can find the answer. I am wanting to find basically the inverse of a SELECT DISTINCT operation. I have a table with a column labled date_created. I know that some records (about 30) were created at the exact same time (to the second) as another record. I would like to find the records that have a date_created value equal to another record. Is this possible in 3.23.54? Thanks in advance = Robert Reed 512-869-0063 home 512-818-2460 cell __ Do you Yahoo!? Yahoo! Mail is new and improved - Check it out! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Finding duplicate values in a column
From: Robert Reed [mailto:[EMAIL PROTECTED] This seems like it should be easy, and I'd be happy for a simple reference to where in the manual or in Paul's book I can find the answer. I am wanting to find basically the inverse of a SELECT DISTINCT operation. I have a table with a column labled date_created. I know that some records (about 30) were created at the exact same time (to the second) as another record. I would like to find the records that have a date_created value equal to another record. Is this possible in 3.23.54? Thanks in advance Try this: SELECT date_created, COUNT(*) AS num FROM tablename GROUP BY date_created HAVING num 1; HTH! -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Finding duplicate values in a column
From: Mike Johnson From: Robert Reed [mailto:[EMAIL PROTECTED] This seems like it should be easy, and I'd be happy for a simple reference to where in the manual or in Paul's book I can find the answer. I am wanting to find basically the inverse of a SELECT DISTINCT operation. I have a table with a column labled date_created. I know that some records (about 30) were created at the exact same time (to the second) as another record. I would like to find the records that have a date_created value equal to another record. Is this possible in 3.23.54? Thanks in advance Try this: SELECT date_created, COUNT(*) AS num FROM tablename GROUP BY date_created HAVING num 1; HTH! Oof. On re-reading this, I realized I wasn't entirely specific enough. What this'll return is all date_created values that have more than one record and a count of how many. The legwork after that is to select all the rows that have each of those date_created values, but that's not exactly a scalable solution. Come to think of it, I'm not entirely sure, off the top of my head, how to get the records themselves. Sorry for the confusion! -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SHOW TABLE STATUS in MySQL 4.1.latest different from 4.1.1 !?
At 14:36 +0200 7/28/04, Martijn Tonies wrote: Hi Jocelyn, From the MySQL doc : The ENGINE and TYPE options specify the storage engine for the table. ENGINE was added in MySQL 4.0.18 (for 4.0) and 4.1.2 (for 4.1). It is the preferred option name as of those versions, and TYPE has become deprecated. TYPE will be supported throughout the 4.x series, but likely will be removed in MySQL 5.1. So I assume it makes sense to change it also for SHOW TABLE STATUS :) Well, in that case - the documentation fails to document the behaviour :-) The latest 4.1 beta does NOT have a Type column. The current manual does document it: http://dev.mysql.com/doc/mysql/en/SHOW_TABLE_STATUS.html Remove it in 5 or 5.1, fine - that's a major (and next major minor release). But not in a sub-minor release. A mistake then? A decision you disagree with. It's hard to win on this kind of thing. If we don't make changes, people say development is too slow. If we do, development is said to be arbitrary. Anyway, it's documented now. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com I just noticed that the MySQL 4.1.latest version handles a SHOW TABLE STATUS different from 4.1.1! Instead of a field Type that holds the table type, it's now Engine. Just a quick question: who makes up these changes in a minor minor (x.x.x) release What do they expect from third party developers? In short: this is a stupid change. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.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] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: what os to use for mysql on amd64?
Just curious if I have got something wrong with my eyes or fingers: [EMAIL PROTECTED] mysql-max-4.0.20-unknown-linux-x86_64]# ldd bin/mysqld librt.so.1 = /lib64/tls/librt.so.1 (0x003c71f0) libdl.so.2 = /lib64/libdl.so.2 (0x003c7190) libpthread.so.0 = /lib64/tls/libpthread.so.0 (0x003c7210) libz.so.1 = /usr/lib64/libz.so.1 (0x003c71d0) libcrypt.so.1 = /lib64/libcrypt.so.1 (0x003c71b0) libnsl.so.1 = /lib64/libnsl.so.1 (0x003c7250) libm.so.6 = /lib64/tls/libm.so.6 (0x003c7170) libc.so.6 = /lib64/tls/libc.so.6 (0x003c7140) /lib64/ld-linux-x86-64.so.2 = /lib64/ld-linux-x86-64.so.2 (0x003c7120) and this (from a 32bit installation) neon:/usr/local/mysql# ldd bin/mysqld not a dynamic executable SODIUM is from mysql amd64 tarball. Did I download the wrong tarball or do I need to hack libc and link them by myself? mc. -Original Message- From: Egor Egorov [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 27, 2004 22:25 To: [EMAIL PROTECTED] Subject: Re: what os to use for mysql on amd64? bad corn [EMAIL PROTECTED] wrote: Recently our company has purchased a dual amd64 opteron machine for mysql server purpose. Whatever Linux you choose please better run MySQL officialy built binaries. Due to some known glibc/gcc issues the officially built binary performs better that custom build on Linux. Please note that the binaries are compiled statically so it doesn't really matter on which distro you are running them. The kernel matters, not the libraries. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: daisy-chaining replication
I forgot to mention in the previous post that the replay-log server_c failed to find (./server_b-relay-bin.05) does exist on server_b, (this is while setting up server_c as slave) but is only 4 bytes in length. 040728 5:15:13 Failed to open the relay log './server_b-relay-bin.05' (relay_log_pos 4) there is tons of extra space on all the machines in question and server_b and server_c are connected to each other through a private switch which is only used by them to prevent any collisions with the rest of the network. === thank you again Paul, I did miss that one altogether, but that does not appear to be the whole problem. having added 'log-slave-updates' to all my.cnf on each server (just to be paranoid) I found no difference in the results. I still get the same errors message upon 'CHANGE MASTER TO ...' and the error log is substantially the same. On Tue, 2004-07-27 at 14:59, Paul DuBois wrote: At 14:31 -0400 7/27/04, Michael Dykman wrote: Hello, I'm running a development build of 4.1.3 and trying to set up of chai thank you again Paul, I did miss that one altogether, but that does not appear to be the whole problem. having added 'log-slave-updates' to all my.cnf of all the servers (just to be paranoid) I found no difference in the results. I still get the same errors message upon 'CHANGE MASTER TO ...' and the error log is substantially the same. On Tue, 2004-07-27 at 14:59, Paul DuBois wrote: At 14:31 -0400 7/27/04, Michael Dykman wrote: Hello, I'm running a development build of 4.1.3 and trying to set up of chain of replication servers. The first level of replication works beautifully but every effort to establish slaves on that first slave have been met with failure. here is the setup (log-bin is enabled on all 3 servers): server_a: is set up as primary master: all clients connect to this server and all data changes are made there. server_b: is setup as client with a clean copy of the data (restarting server_a with 'skip-networking' to take the clean cut and gather the master status info, then restarting server_a with networking enabled again). do the change master thing on server_b and start the slave, in due course 'show slave status' shows that it has caught up with server_a and all data is there server_c: setup is a problem (though not necessarily 'the problem'). having restarted server_b with skip-networking and stopped the slave, i take a cut of the data and get the master status info. having put the data cut on server_c, executing CHANGE MASTER TO MASTER_HOST='server_b', MASTER_USER='replica_dude', MASTER_PASSWORD='secret', MASTER_LOG_FILE='server_b.01', MASTER_LOG_POS=79;log-slave-updates reports: ERROR 1201 at line 1: Could not initialize master info structure; more error messages can be found in the MySQL error log contents of the error log: ... Failed to open the relay log './server_b-relay-bin.01' (relay_log_pos 1011481184) 040727 11:40:34 Could not find target log during relay log initialization 040727 11:40:34 Failed to initialize the master info structure /usr/local/mysql/libexec/mysqld: ready for connections. Version: '4.1.3-beta-log' socket: '/tmp/mysql.sock' port: 3306 040727 11:41:26 Failed to open the relay log './server_b-relay-bin.01' (relay_log_pos 1011481184) 040727 11:41:26 Could not find target log during relay log initialization The file server_b-relay-bin.01 certainly is there and is permissioned mysql:mysql, as are all the data files. the only really spooky thing I can find going on is on server_b (slave to server_a, intended master to server_c). Having reenabled the slave and seeing it catch up to server_a (both 'show slave status' and empirical data tests confirm this) anther look at 'show master status' on server_b *still* reports: +-+--+--+--+ | File| Position | Binlog_Do_DB | Binlog_Ignore_DB | +-+--+--+--+ | server_b-bin.01 | 79 | | | +-+--+--+--+ it is not updating at all, even as replication data pours in. I have reinstalled server_b as a slave 3 times now using the data from server_a and no matter how much replication data server_b has gathered from server_a post-install, the position is always 79 (!). Can anyone lend me a clue? Did you start server b with --log-slave-updates so that it writes the events that it receives from its master to its own binary log? Just turning on binary logging on server b isn't enough. -- - michael dykman - [EMAIL PROTECTED] n of replication servers. The first level of replication works beautifully but every effort to
RE: Finding duplicate values in a column
No worries mate, that is exactly what I need, and the number I've got are 32 and when I subtract the ones more than a year old I've less than 10. I can do the legwork. :) Thanks. --- Mike Johnson [EMAIL PROTECTED] wrote: From: Mike Johnson From: Robert Reed [mailto:[EMAIL PROTECTED] This seems like it should be easy, and I'd be happy for a simple reference to where in the manual or in Paul's book I can find the answer. I am wanting to find basically the inverse of a SELECT DISTINCT operation. I have a table with a column labled date_created. I know that some records (about 30) were created at the exact same time (to the second) as another record. I would like to find the records that have a date_created value equal to another record. Is this possible in 3.23.54? Thanks in advance Try this: SELECT date_created, COUNT(*) AS num FROM tablename GROUP BY date_created HAVING num 1; HTH! Oof. On re-reading this, I realized I wasn't entirely specific enough. What this'll return is all date_created values that have more than one record and a count of how many. The legwork after that is to select all the rows that have each of those date_created values, but that's not exactly a scalable solution. Come to think of it, I'm not entirely sure, off the top of my head, how to get the records themselves. Sorry for the confusion! -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 = Robert Reed 512-869-0063 home 512-818-2460 cell __ Do you Yahoo!? Yahoo! Mail is new and improved - Check it out! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQLcheck-scramble vulerability info
Hi! On Jul 28, Ben Ricker wrote: Our security guy ran across an exploit in all MySQL versions before the June releases. I am trying to find information on how to patch to fix this vulnerability but I cannot find anything on MySQL's site! It is kind of ridiculous. I searched for check_scramble, zero-length comparison and even vuneralibility (the latter only had 7 hits). Has anyone seen better information on how to patch 4.0.18 to fix this bug? Check out http://securitytracker.com/alerts/2004/Jul/1010645.html for more information. Did you notice at the above url: Version(s): 4.1 prior to version 4.1.3; also version 5.0 4.0.18 is NOT vunerable. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
JOIN/WHERE and index confusion
Hi, all. I think I'm misunderstanding something basic about indexes. I have a SELECT like so: SELECT * FROM a LEFT JOIN b ON a.a_id = b.a_id JOIN c ON a.c_id = c.c_id JOIN d ON c.d_id = d.d_id JOIN e ON c.e_id = e.e_id WHERE a.timestamp BETWEEN 2004010100 AND 20040101235959 GROUP BY c.d_id, c.e_id All of the id fields are primary indexes. I've already created an index on a.timestamp, and that works all right. I tried creating an index on a for the SELECT: KEY `reporting` (`a_id`,`c_id`,`timestamp`) and an index on c for the GROUP BY: KEY `reporting` (`c_id`,`d_id`,`e_id`) But EXPLAIN shows that MySQL isn't even considering the key on a, and chooses the primary key on c over my index. Clearly I'm confused about how indexes are used in a JOIN/WHERE situation: can anyone enlighten me? Eamon Daly -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Using function followed by order by function_name(table.field_name).....
Scott,
I really don't understand your need to convert your string values to some
kind of numeric equivalent. When it comes to sorting strings it all
depends on the collation sequence for the characterset you are using.
For example, my STATUS; command tells me:
mysql Ver 14.3 Distrib 4.1.1a-alpha, for Win95/Win98 (i32)
Connection id: 1
Current database: test
Current user: [EMAIL PROTECTED]
SSL:Not in use
Using delimiter:;
Server version: 4.1.1a-alpha-nt-log
Protocol version: 10
Connection: localhost via TCP/IP
Client characterset:latin1_swedish_ci
Server characterset:latin1_swedish_ci
TCP port: 3306
Uptime: 2 hours 59 min 30 sec
Threads: 1 Questions: 19 Slow queries: 0 Opens: 12 Flush tables: 1
Open tables: 5 Queries per second avg: 0.002
(This is a development machine that has seen very little work yet today)
My Server charset is latin1_swedish_ci. From the source code for
MySQL, the ASCII collation order (sorting sequence) for that character set
is:
static uchar sort_order_latin1[] = {
0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15,
16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31,
32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47,
48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63,
64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79,
80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95,
96, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79,
80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90,123,124,125,126,127,
128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,
144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,
160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,
176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,
65, 65, 65, 65, 92, 91, 92, 67, 69, 69, 69, 69, 73, 73, 73, 73,
68, 78, 79, 79, 79, 79, 93,215,216, 85, 85, 85, 89, 89,222,223,
65, 65, 65, 65, 92, 91, 92, 67, 69, 69, 69, 69, 73, 73, 73, 73,
68, 78, 79, 79, 79, 79, 93,247,216, 85, 85, 85, 89, 89,222,255
};
You can see that the characters that represent the numbers 0 through 9
(positions 48 through 57) are sorted in order and before the capital
letters A through Z (65 through 90). The lower-case letters a
through z are assigned the same sort value as their capital counterparts
(positions 97 through 122). This means that ADAM and adam will sort
together (the collation order is case-insensitive, thus the ci at the
end of the name)
That also means that 00 apple will sort before 01 apple and that 1
cherry and 10 cherry will sort before 2 cherry (1 comes
*alphabetically* before 2). This is because the numbers do not exist as
separate entities within a string. To the CPU, all of the elements of a
string are viewed as numeric values (their ASCII or UNICODE values) that
get sorted according to the rules built into the characterset being used
(see table above).
So, please try to explain again why an ORDER BY fieldname (without any
sort of conversion function on your field) does not work as you want it
to.
Thank you for your patience with me,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Scott Fletcher [EMAIL PROTECTED] wrote on 07/28/2004 10:42:39 AM:
Okay, redo(ed) the scripts and eliminated all of the CHAR() part
from all of the SQL Syntax in the PHP scripts and it look better.
What functions can I use to substitute the INTEGER() and DOUBLE()
from DB2 to MySQL?. I?ll keep searching the MySQL but I don?t seem
to find a MySQL function that would do something similar to this.
Here I got by the ?ORDER BY field_name ASC? for the integer value?
--snip?
10
11
129
21
23
--snip?
Where 129 should be at the bottom in integer value. Similar concept
with the double or float value?
Thanks,
FletchSOD
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, July 28, 2004 8:56 AM
To: Scott Fletcher
Cc: [EMAIL PROTECTED]
Subject: RE: Using function followed by order by
function_name(table.field_name).
Let me quote a little bit from the docs (http://dev.mysql.
com/doc/mysql/en/String_functions.html):
CHAR() interprets the arguments as integers and returns a string
consisting of the characters given by the code values of those
integers. NULL values are skipped.
mysql SELECT CHAR(77,121,83,81,'76');
- 'MySQL'
mysql SELECT CHAR(77,77.3,'77.3');
- 'MMM'
That means that when it processes each of the fields in your
resultset, it converts that string into some other string and is
sorting by the results of that transformation. Definitely NOT what
you wanted. Please, let's try again. Please, show me the results of
just a plain ORDER BY field_name query (without the CHAR()
function) and explain to
Re: SHOW TABLE STATUS in MySQL 4.1.latest different from 4.1.1 !?
Paul, From the MySQL doc : The ENGINE and TYPE options specify the storage engine for the table. ENGINE was added in MySQL 4.0.18 (for 4.0) and 4.1.2 (for 4.1). It is the preferred option name as of those versions, and TYPE has become deprecated. TYPE will be supported throughout the 4.x series, but likely will be removed in MySQL 5.1. So I assume it makes sense to change it also for SHOW TABLE STATUS :) Well, in that case - the documentation fails to document the behaviour :-) The latest 4.1 beta does NOT have a Type column. The current manual does document it: http://dev.mysql.com/doc/mysql/en/SHOW_TABLE_STATUS.html It documents Engine instead of Type, but I cannot find the part about deprecating Type in favor of Engine. Remove it in 5 or 5.1, fine - that's a major (and next major minor release). But not in a sub-minor release. A mistake then? A decision you disagree with. A decision that has been taken way too lightly. If you expect third party developers to tune their applications to every minor sub-release, you will have a hard time gaining their thrust and expecting them to support the latest and the greatest. Would you expect us to stay away from 4.1 until it's become stable? It's hard to win on this kind of thing. If we don't make changes, people say development is too slow. If we do, development is said to be arbitrary. Anyway, it's documented now. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.20 and UTF-8?
I'm using UTF-8 in my newsboard, too, and I have no major problems with it. UTF-8 doesn't need to be handled as binary, I believe, since all characters should be in a range over the control characters. Anyone please correct me, if that's wrong... Sorting will not work as expected, special characters are represented as multiple other characters that might not be in the order you'd expect them to be. My search uses LIKE and I have no problems with it. The browser brings me already UTF-8 data and I cannot imagine why it should not be usable in LIKE queries. -- Yves Goergen [EMAIL PROTECTED] BlackBoard Internet Newsboard System -- blackboard.unclassified.de Free (GPL), easy to use and install, secure, innovative! (PHP+MySQL) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Finding duplicate values in a column
If you have an auto_increment or other unique recno you can do: select a.* from file a , file b where a.timefield = b.timefield and a.recno != b.recno Robert Reed wrote: No worries mate, that is exactly what I need, and the number I've got are 32 and when I subtract the ones more than a year old I've less than 10. I can do the legwork. :) Thanks. --- Mike Johnson [EMAIL PROTECTED] wrote: From: Mike Johnson From: Robert Reed [mailto:[EMAIL PROTECTED] This seems like it should be easy, and I'd be happy for a simple reference to where in the manual or in Paul's book I can find the answer. I am wanting to find basically the inverse of a SELECT DISTINCT operation. I have a table with a column labled date_created. I know that some records (about 30) were created at the exact same time (to the second) as another record. I would like to find the records that have a date_created value equal to another record. Is this possible in 3.23.54? Thanks in advance Try this: SELECT date_created, COUNT(*) AS num FROM tablename GROUP BY date_created HAVING num 1; HTH! Oof. On re-reading this, I realized I wasn't entirely specific enough. What this'll return is all date_created values that have more than one record and a count of how many. The legwork after that is to select all the rows that have each of those date_created values, but that's not exactly a scalable solution. Come to think of it, I'm not entirely sure, off the top of my head, how to get the records themselves. Sorry for the confusion! -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 = Robert Reed 512-869-0063 home 512-818-2460 cell __ Do you Yahoo!? Yahoo! Mail is new and improved - Check it out! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select all and group by question
Hi, I'm using MySQL 4.0.18 on Win2k Server. I have a table similar to the following: col1col2col3 A 1 this A 2 is A 3 a B 1 test B 2 table What I would like to get with one query is the following result: col1max(col2) col3 -- A 3 a B 2 table I've searched around, but haven't been able to come up with anything. This query doesn't give me the correct results: select col1, max(col2), col3 from table group by col1 Any suggestions? Thanks in advance, Matt
Re: Large 30 GB Database
i have one MySQL dB that is currently about 26Gb, properly indexed searches are very quick - hcir On Jul 28, 2004, at 4:23 AM, matt ryan wrote: Should I even attempt this using mysql? Has anyone played with this much data in mysql? I've got two 100 gig databases in mysql, and slave replication on both of them, the only time I have a problem is table scans, that much data will be slow. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select all and group by question
Without using a subquery, you have to do it in two steps. First you need to collect the MAX(col2) values for each col1 value: CREATE TEMPORARY TABLE tmpMaxes SELECT col1, max(col2) from test_table GROUP BY col1 Then you want to get the rows where the col2 value is the maximum for each col1 value (as we determined in the last query): SELECT tt.col1, tt.col2, tt.col3 FROM test_table tt INNER JOIN tmpMaxes tm ON tm.col1 = tt.col1 AND tm.col2 = tt.col2 Finally, you should always clean up after yourself: DROP TABLE tmpMaxes Make sense? Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 07/28/2004 12:29:26 PM: Hi, I'm using MySQL 4.0.18 on Win2k Server. I have a table similar to the following: col1col2col3 A 1 this A 2 is A 3 a B 1 test B 2 table What I would like to get with one query is the following result: col1max(col2) col3 -- A 3 a B 2 table I've searched around, but haven't been able to come up with anything. This query doesn't give me the correct results: select col1, max(col2), col3 from table group by col1 Any suggestions? Thanks in advance, Matt
mysql_safe just ends
So I'm a little confused here I've installed mysql-standard-4.0.20-pc-linux-i686.tar to /usr/local/mysql and then ran the scripts/mysql_install_db with seemingly no errors. After reading section 5.1 'the MySQL Server and Server Startup Scripts' I figured that cd /usr/local/mysql bin/mysqld_safe would simply work but no. I get [EMAIL PROTECTED] mysql]# bin/mysqld_safe [2] 11616 [EMAIL PROTECTED] mysql]# Starting mysqld daemon with databases from /var/lib/mysql 040728 10:50:16 mysqld ended [2]+ Donebin/mysqld_safe [EMAIL PROTECTED] mysql]# OK - so I thought well I'll just specify what I want. [EMAIL PROTECTED] mysql]# bin/mysqld_safe --ledir=/usr/local/mysql/bin --data=/usr/local/mysql/data --user=mysql [2] 11664 [EMAIL PROTECTED] mysql]# Starting mysqld daemon with databases from /var/lib/mysql 040728 10:52:34 mysqld ended [2]+ Donebin/mysqld_safe --ledir=/usr/local/mysql/bin --data=/usr/local/mysql/data --user=mysql So now in the end I'm confused. 1. Why does mysqld_safe simply start then immediately end? How do I start mysql? 2. Why does it go to /var/lib/mysql for the data even when I specify another location? For that matter where did it get that location? did mysql_install_db do that? Any help or direction would be greatly appreciated Cheers Cam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_safe just ends
Cam wrote: So I'm a little confused here I've installed mysql-standard-4.0.20-pc-linux-i686.tar to /usr/local/mysql and then ran the scripts/mysql_install_db with seemingly no errors. After reading section 5.1 'the MySQL Server and Server Startup Scripts' I figured that cd /usr/local/mysql bin/mysqld_safe would simply work but no. I get [EMAIL PROTECTED] mysql]# bin/mysqld_safe [2] 11616 [EMAIL PROTECTED] mysql]# Starting mysqld daemon with databases from /var/lib/mysql 040728 10:50:16 mysqld ended [2]+ Donebin/mysqld_safe [EMAIL PROTECTED] mysql]# snip I don't know why, but something at the back of my mind is shouting 'Check Permissions', I think you need to check the OS file permissions for the folder in which you have stored the data for mySql. If memory serves, part of the installation process is to change file permissions and /or groups and ownership - methinks this is where your problem lies -- Rory McKinley Nebula Solutions +27 21 555 3227 - office +27 21 551 0676 - fax +27 82 857 2391 - mobile www.nebula.co.za This e-mail is intended only for the person to whom it is addressed and may contain confidential information which may be legally privileged. Nebula Solutions accepts no liability for any loss, expense or damage arising from this e-mail and/or any attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: Using function followed by order by function_name(table.field_name).....
Scott,
I am glad that you understand what I meant but I still don't think you
understand what you can do with MySQL. You are killing your query
performance by trying to convert things on the fly. YOU DON'T NEED TO
DO THAT WITH MYSQL. Maybe you did in DB2 but not here.
So you can stop Googling for the documents, here is a link to the MySQL
manual that heads up the section on almost all of the functions in MySQL:
(http://dev.mysql.com/doc/mysql/en/Functions.html). May I suggest you look
at the Date_Format() function as an easier method to re-format your dates
for display.
Here's an example of what I mean. Imagine I have a table of appointments
that looks like:
CREATE TABLE appointment (
ID int not null auto_increment,
OwnerName varchar(20) not null,
MeetWithName varchar(20) not null,
Date datetime,
PRIMARY KEY(ID)
)
(YES, this is _not_ a normalized table design but it doesn't need to be
for this example) and I have populated it with the following data:
INSERT appointment (OwnerName, MeetWithName, Date)
VALUES ('Shawn','Dr. Bell', '2004-08-05 14:00:00')
,('Shawn', 'Dr. Bell','2004-08-12 11:00:00')
,('John','Mary','2004-07-31 12:15:00')
, ('John','Mary','2004-08-04 12:15:00')
, ('John','Mary','2004-08-11 11:15:00')
, ('John','Mary','2004-08-25 12:15:00')
, ('Scott', 'David','2004-08-01 09:00:00')
, ('Scott', 'James', '2004-08-07 15:00:00')
, ('Shawn', 'Scott', '2004-08-06')
, ('Scott', 'Anna','2004-08-22');
Here is the data as it sits in the table:
select * from appointment;
++---+--+-+
| ID | OwnerName | MeetWithName | Date|
++---+--+-+
| 1 | Shawn | Dr. Bell | 2004-08-05 14:00:00 |
| 2 | Shawn | Dr. Bell | 2004-08-12 11:00:00 |
| 3 | John | Mary | 2004-07-31 12:15:00 |
| 4 | John | Mary | 2004-08-04 12:15:00 |
| 5 | John | Mary | 2004-08-11 11:15:00 |
| 6 | John | Mary | 2004-08-25 12:15:00 |
| 7 | Scott | David| 2004-08-01 09:00:00 |
| 8 | Scott | James| 2004-08-07 15:00:00 |
| 9 | Shawn | Scott| 2004-08-06 00:00:00 |
| 10 | Scott | Anna | 2004-08-22 00:00:00 |
++---+--+-+
10 rows in set (0.00 sec)
Now, To sort this data different ways. Sorted by OwnerName:
SELECT * from appointment order by OwnerName;
++---+--+-+
| ID | OwnerName | MeetWithName | Date|
++---+--+-+
| 6 | John | Mary | 2004-08-25 12:15:00 |
| 3 | John | Mary | 2004-07-31 12:15:00 |
| 4 | John | Mary | 2004-08-04 12:15:00 |
| 5 | John | Mary | 2004-08-11 11:15:00 |
| 8 | Scott | James| 2004-08-07 15:00:00 |
| 7 | Scott | David| 2004-08-01 09:00:00 |
| 10 | Scott | Anna | 2004-08-22 00:00:00 |
| 2 | Shawn | Dr. Bell | 2004-08-12 11:00:00 |
| 9 | Shawn | Scott| 2004-08-06 00:00:00 |
| 1 | Shawn | Dr. Bell | 2004-08-05 14:00:00 |
++---+--+-+
10 rows in set (0.00 sec)
Sorted by OwnerName and Date:
SELECT * from appointment order by OwnerName, Date;
++---+--+-+
| ID | OwnerName | MeetWithName | Date|
++---+--+-+
| 3 | John | Mary | 2004-07-31 12:15:00 |
| 4 | John | Mary | 2004-08-04 12:15:00 |
| 5 | John | Mary | 2004-08-11 11:15:00 |
| 6 | John | Mary | 2004-08-25 12:15:00 |
| 7 | Scott | David| 2004-08-01 09:00:00 |
| 8 | Scott | James| 2004-08-07 15:00:00 |
| 10 | Scott | Anna | 2004-08-22 00:00:00 |
| 1 | Shawn | Dr. Bell | 2004-08-05 14:00:00 |
| 9 | Shawn | Scott| 2004-08-06 00:00:00 |
| 2 | Shawn | Dr. Bell | 2004-08-12 11:00:00 |
++---+--+-+
10 rows in set (0.00 sec)
Notice that now for each name, their appointments are in the correct date
order? Sorted by OwnerName, MeetWithName, then Date:
SELECT * from appointment order by OwnerName,MeetWithName,Date;
++---+--+-+
| ID | OwnerName | MeetWithName | Date|
++---+--+-+
| 3 | John | Mary | 2004-07-31 12:15:00 |
| 4 | John | Mary | 2004-08-04 12:15:00 |
| 5 | John | Mary | 2004-08-11 11:15:00 |
| 6 | John | Mary | 2004-08-25 12:15:00 |
| 10 | Scott | Anna | 2004-08-22 00:00:00 |
| 7 | Scott | David| 2004-08-01 09:00:00 |
| 8 | Scott | James| 2004-08-07
License info
Sorry, but I send an e-mail to sales two month ago but none replies me. The question is very simple: Can I buy 2 licenses and transfer them to an hardware reseller that will transfer them to the customer? Thank you. Santino Cusimano -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
TOP
Hi, Does anyone know how to do a TOP function in MySQL ? Thanks, -Kamal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: TOP
In MS SQL Server (T-SQL) you say SELECT TOP n In MySQL you use: SELECT LIMIT n (http://dev.mysql.com/doc/mysql/en/SELECT.html) There is no direct equivalent to SELECT TOP n PERCENT Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Kamal Ahmed [EMAIL PROTECTED] wrote on 07/28/2004 02:39:11 PM: Hi, Does anyone know how to do a TOP function in MySQL ? Thanks, -Kamal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: TOP
TOP is a microsoft SQL extension. MySQL uses the LIMIT clause. for instance, the following is a rather typical top 10 sql query: select some_column, sum(another_column) total from some_table group by some_column order by total desc LIMIT 10 On Wed, 28 Jul 2004 14:39:11 -0400, Kamal Ahmed [EMAIL PROTECTED] wrote: Hi, Does anyone know how to do a TOP function in MySQL ? Thanks, -Kamal. -- 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: Indexed Database still slow
YAHOOO Shawn Green you were absolutely right, I basically just took your 6 union query from below, modified it to add back my specific data and fixed my index to only include the 2 fields that I needed and VOILA` it worked like a champ. My query times went from 1:05 to 0.32 seconds, thanks for all your help. Thanks to every one in the community that helped me out with this and other questions your knowledge in invaluable and cannot be gained strictly thru books, I will continue to use this mailing list and will help where I can. Chris Hood Investigator Verizon Global Security Operations Center Email: [EMAIL PROTECTED] Desk: 972.399.5900 Verizon Proprietary NOTICE - This message and any attached files may contain information that is confidential and/or subject of legal privilege intended only for the use by the intended recipient. If you are not the intended recipient or the person responsible for delivering the message to the intended recipient, be advised that you have received this message in error and that any dissemination, copying or use of this message or attachment is strictly forbidden, as is the disclosure of the information therein. If you have received this message in error please notify the sender immediately and delete the message. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 28, 2004 8:41 AM To: Christopher L. Hood Cc: [EMAIL PROTECTED] Subject: Re: Indexed Database still slow I think the UNION is the right way to handle this, in fact, I would be tempted to break it into 6 UNIONS... more on that later. You really should concentrate mostly on coverage for fields used in your WHERE clauses, in this case: Framed_IP_Address and Date. Additional fields can be used to get data straight from the index but the docs say that they must be numeric (not character based). So, in the case of this query, those additional fields just make your index larger which takes longer to search. Try a two-field index and just this part of your subquery: Select ALL PRTC_DIALUP.Id, PRTC_DIALUP.Date, PRTC_DIALUP.Time, PRTC_DIALUP.Record_Type, PRTC_DIALUP.Full_Name, PRTC_DIALUP.Framed_IP_Address from PRTC_DIALUP Where PRTC_DIALUP.Framed_IP_Address = 'someipaddress' AND PRTC_DIALUP.Date = 'one-date-here' If I am right, that should return somewhere 2 seconds. This means that a 6-way union would return in somewhere near or below 12 seconds. In this case each query is doing an exact match on an index and the 6 queries unioned together should take less time than your 2 3-way queries. I call them 3 way as each half has to check for one of 3 dates. Also, if you need to ORDER BY the results of the UNION, you need to enclose each participating query in parentheses and put the ORDER BY clause after the last query. I went ahead and expanded your 2-query UNION into a 6-query UNION to illustrate: ( Select ALL PRTC_DIALUP.Id, PRTC_DIALUP.Date, PRTC_DIALUP.Time, PRTC_DIALUP.Record_Type, PRTC_DIALUP.Full_Name, PRTC_DIALUP.Framed_IP_Address from PRTC_DIALUP Where PRTC_DIALUP.Framed_IP_Address = 'someipaddress' AND PRTC_DIALUP.Date = 'one-date-here' ) UNION ( Select ALL PRTC_DIALUP.Id, PRTC_DIALUP.Date, PRTC_DIALUP.Time, PRTC_DIALUP.Record_Type, PRTC_DIALUP.Full_Name, PRTC_DIALUP.Framed_IP_Address from PRTC_DIALUP Where PRTC_DIALUP.Framed_IP_Address = 'someipaddress' AND PRTC_DIALUP.Date = 'one-day-earlier' ) UNION ( Select ALL PRTC_DIALUP.Id, PRTC_DIALUP.Date, PRTC_DIALUP.Time, PRTC_DIALUP.Record_Type, PRTC_DIALUP.Full_Name, PRTC_DIALUP.Framed_IP_Address from PRTC_DIALUP Where PRTC_DIALUP.Framed_IP_Address = 'someipaddress' AND PRTC_DIALUP.Date = 'one-day-later' ) UNION ( Select ALL PRTC_DSL.Id, PRTC_DSL.Date, PRTC_DSL.Time, PRTC_DSL.Record_Type, PRTC_DSL.Full_Name, PRTC_DSL.Framed_IP_Address from PRTC_DSL Where PRTC_DSL.Framed_IP_Address = 'someipaddress' and PRTC_DSL.Date = 'one-date-here' ) UNION ( Select ALL PRTC_DSL.Id, PRTC_DSL.Date, PRTC_DSL.Time, PRTC_DSL.Record_Type, PRTC_DSL.Full_Name, PRTC_DSL.Framed_IP_Address from PRTC_DSL Where PRTC_DSL.Framed_IP_Address = 'someipaddress' and PRTC_DSL.Date = 'one-day-earlier' ) UNION ( Select ALL PRTC_DSL.Id, PRTC_DSL.Date, PRTC_DSL.Time, PRTC_DSL.Record_Type, PRTC_DSL.Full_Name, PRTC_DSL.Framed_IP_Address from PRTC_DSL Where PRTC_DSL.Framed_IP_Address = 'someipaddress' and PRTC_DSL.Date = 'one-day-later' ) ORDER BY Full_Name, Time; I agree that it will take some additional time to parse those 6 queries instead of just 2 but I believe that you won't be able to notice the difference. I would compare those 4 extra queries to the # of queries per second your system handles now to get a rough estimate of the additional overhead involved. Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 07/28/2004 08:25:36 AM: Ok, I will be the first to say that I am
RE: mysql_safe just ends
Thanks Rory, I did go through (as per the manual)and set the permissions shell groupadd mysql shell useradd -g mysql mysql shell cd /usr/local shell gunzip /path/to/mysql-VERSION-OS.tar.gz | tar xvf - shell ln -s full-path-to-mysql-VERSION-OS mysql shell cd mysql shell scripts/mysql_install_db --user=mysql shell chown -R root . shell chown -R mysql data shell chgrp -R mysql . shell bin/mysqld_safe --user=mysql As well, afterward I checked the permissions on the directories and the data directory seems to have the right permissions. i.e. 'mysql' owns the data directory. Checking the 'bin' directory, all the files are group 'mysql' and all have 'rx' for group. This seems to make sense to me. Cheers Cam -Original Message- From: Rory McKinley [mailto:[EMAIL PROTECTED] Sent: July 28, 2004 11:55 AM To: [EMAIL PROTECTED] Cc: Cam Subject: Re: mysql_safe just ends Cam wrote: So I'm a little confused here I've installed mysql-standard-4.0.20-pc-linux-i686.tar to /usr/local/mysql and then ran the scripts/mysql_install_db with seemingly no errors. After reading section 5.1 'the MySQL Server and Server Startup Scripts' I figured that cd /usr/local/mysql bin/mysqld_safe would simply work but no. I get [EMAIL PROTECTED] mysql]# bin/mysqld_safe [2] 11616 [EMAIL PROTECTED] mysql]# Starting mysqld daemon with databases from /var/lib/mysql 040728 10:50:16 mysqld ended [2]+ Donebin/mysqld_safe [EMAIL PROTECTED] mysql]# snip I don't know why, but something at the back of my mind is shouting 'Check Permissions', I think you need to check the OS file permissions for the folder in which you have stored the data for mySql. If memory serves, part of the installation process is to change file permissions and /or groups and ownership - methinks this is where your problem lies -- Rory McKinley Nebula Solutions +27 21 555 3227 - office +27 21 551 0676 - fax +27 82 857 2391 - mobile www.nebula.co.za This e-mail is intended only for the person to whom it is addressed and may contain confidential information which may be legally privileged. Nebula Solutions accepts no liability for any loss, expense or damage arising from this e-mail and/or any attachments. -- 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]
Setting up MySQL on Raid Mirror
Hello all. I want to install MySQL on a RAID mirror drive using two ATA 125GB for the mirror. I know in a webserver config, it's best to put the boot OS on one drive and the shared serving folder on the RAID mirror drive. Under a MySQL server, if I install the OS and MySQL all on the mirror drive ( I can get rid of the standard 80gb drive the computer ships with) are there draw backs or problems with such a config??? (ex. speed) Thanks. Rick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL over Raid Mirror
Hello all. I want to install MySQL on a RAID mirror drive using two ATA 125GB for the mirror. I know in a webserver config, it's best to put the boot OS on one drive and the shared serving folder on the RAID mirror drive. Under a MySQL server, if I install the OS and MySQL all on the mirror drive ( I can get rid of the standard 80gb drive the computer ships with) are there draw backs or problems with such a config??? (ex. speed) My config is a Mac Dual Processor G4 1.25 Ghtz running 10.3 with MysQL 4.0.15. both drives are 120GB 7200RM IDE Hitachi Deskstar's. Thanks. Rick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Setting up MySQL on Raid Mirror
Rick Dwyer wrote: Hello all. I want to install MySQL on a RAID mirror drive using two ATA 125GB for the mirror. I know in a webserver config, it's best to put the boot OS on one drive and the shared serving folder on the RAID mirror drive. Under a MySQL server, if I install the OS and MySQL all on the mirror drive ( I can get rid of the standard 80gb drive the computer ships with) are there draw backs or problems with such a config??? (ex. speed) Thanks. Rick There are pro's and con's to either setup: - The more volumes you have (e.g. both an 80GB and a mirrored 125GB volume), the more opportunity you have to balance overall system IO and get the best performance. Mind you, that will take some tuning and understanding what portions of your system cause IO (e.g Operating system code, versus MySQL code, swap and actual read/write of the database data). - Putting your OS on a unmirrored volume is more risky than having it on the RAID 1 volume. Assuming its not a super-high performance situation, I would put everything on the RAID 1 volume. If its really high performance, you need an altogether different disk setup anyway. Hope this helps - Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Innodb assertion failure after binary backup-restore
Hi Sp,
The best advice I can give you is to implement a replication slave,
and perform hot backups using the innodb ibbackup tool from the slave
server. This ensures that you maintain high availability and
disaster recovery in case of catastrophic failure.
The ibbackup tool from innodb (http://www.innodb.com) works
very well even on large databases (currently we backup over
40 gigs/day).
Cheers,
Mark Steele
Implementation Director
CDT Inc.
-Original Message-
From: Sp.Raja [mailto:[EMAIL PROTECTED]
Sent: July 27, 2004 10:51 AM
To: Heikki Tuuri; [EMAIL PROTECTED]
Subject: Re: Innodb assertion failure after binary backup-restore
Thanks for your replies.
Now I have three ways to go
1. replication
2. innodb hot backup tool
3. Make sure that no one is writing in to the database and start
backup when modified db pages in BUFFER POOL AND MEMORY becomes zero
#FLUSH TABLES WITH READ LOCK
still_to_flush=1
while [ $still_to_flush != 0 ]
do
still_to_flush=`/usr/local/mysql/bin/mysql -e SHOW INNODB
STATUS\G | grep Modified db pages | awk '{print $4}'`
sleep 1
done
#UNLOCK TABLES
Do you think #3 will work?
Regards,
Sp.Raja
Original Message
From: Heikki Tuuri [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Date: Tue, Jul-27-2004 6:24 PM
Subject: Re: Innodb assertion failure after binary backup-restore
Hi!
sync will not help.
You can run SHOW INNODB STATUS\G to monitor when InnoDB has flushed
its buffer pool.
Best regards,
Heikki
Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up
MyISAM tables
http://www.innodb.com/order.php
Order MySQL support from http://www.mysql.com/support/index.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Setting up MySQL on Raid Mirror
Assuming its not a super-high performance situation, I would put everything on the RAID 1 volume. Richard, what would you define as super-high performance? This MySQL database server will serve as the backend for a Lasso/ OS X Apache webserver handling thousands of hits per day. Will installing OS and MySQL on the Mirrored Drive be within the scope of that type of activity? The database basically collects customer data. Thanks. Rick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
connection problems
Greetings, I have what I hope is an easy problem. I have installed mysql 4.0.20 and when I execute mysqladmin to set the root password using the -h flag my hostname is truncated, and I get a connection refused message. The hostname on this box is a fqdn like blah.1.2.3.net. My command line is mysqladmin -u root -h blah.1.2.3.net password 'test' mysqladmin -u root -h blah.1.2.3.net password test /path/to/mysql/bin/mysqladmin: connect to server at 'blah.1.2.3.net' failed error: 'Host 'blah' is not allowed to connect to this MySQL server' Can anyone shed any light on why the hostname is being truncated? Am I missing something obvious here? Thank you in advance, Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using SQL's JOIN to return all rows regardless of non-exist rows in other tables....
I'm trying to figure out how to get the table FUNDED_INFO to return all rows, even if there is no row(s) in the two other tables, STOCK CUSTOMERS. This one doesn't really work 'cause either one of the two tables, STOCK and CUSTOMERS doesn't have a row which would cause a row from FUNDED_INFO not to be returned at all... --snip-- SELECT FUNDED_INFO.TIMESTAMP, FUNDED_INFO.ACCT_NUMBER FROM FUNDED_INFO INNER JOIN STOCK ON FUNDED_INFO.ACCT_NUMBER = STOCK.ACCT_NUMBER INNER JOIN CUSTOMERS ON FUNDED_INFO.ACCT_NUMBER = CUSTOMERS.ACCT_NUMBER --snip-- So, I believe that a LEFT JOIN would be the answer, if so what would be the appropriate syntax exactly to reflect that?? Thanks, FletchSOD -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: connection problems
Can you check the host name again? You have a five segment address. -Original Message- From: Andrew Hall To: [EMAIL PROTECTED] Sent: 7/28/04 3:55 PM Subject: connection problems Greetings, I have what I hope is an easy problem. I have installed mysql 4.0.20 and when I execute mysqladmin to set the root password using the -h flag my hostname is truncated, and I get a connection refused message. The hostname on this box is a fqdn like blah.1.2.3.net. My command line is mysqladmin -u root -h blah.1.2.3.net password 'test' mysqladmin -u root -h blah.1.2.3.net password test /path/to/mysql/bin/mysqladmin: connect to server at 'blah.1.2.3.net' failed error: 'Host 'blah' is not allowed to connect to this MySQL server' Can anyone shed any light on why the hostname is being truncated? Am I missing something obvious here? Thank you in advance, Andrew -- 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]
where its not a letter
Hi Sure this is easy Im trying to create a simple select query but I want to return records unless a field contains a C or c so would it be something like where field 'c' or 'C' Regards John B -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: connection problems
Yes I have. The hostname of the box returned with 'hostname' is the fqdn and is in the format of blah.1.2.3.net. Drew On Wed, 2004-07-28 at 17:16, Victor Pendleton wrote: Can you check the host name again? You have a five segment address. -Original Message- From: Andrew Hall To: [EMAIL PROTECTED] Sent: 7/28/04 3:55 PM Subject: connection problems Greetings, I have what I hope is an easy problem. I have installed mysql 4.0.20 and when I execute mysqladmin to set the root password using the -h flag my hostname is truncated, and I get a connection refused message. The hostname on this box is a fqdn like blah.1.2.3.net. My command line is mysqladmin -u root -h blah.1.2.3.net password 'test' mysqladmin -u root -h blah.1.2.3.net password test /path/to/mysql/bin/mysqladmin: connect to server at 'blah.1.2.3.net' failed error: 'Host 'blah' is not allowed to connect to this MySQL server' Can anyone shed any light on why the hostname is being truncated? Am I missing something obvious here? Thank you in advance, Andrew -- 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: connection problems
My apologies I was reading an ip address. Have you tried logging in from the server itself? Since this is the initial install I think root needs to log in from the localhost. -Original Message- From: Andrew Hall To: Victor Pendleton Cc: '[EMAIL PROTECTED] ' Sent: 7/28/04 4:35 PM Subject: RE: connection problems Yes I have. The hostname of the box returned with 'hostname' is the fqdn and is in the format of blah.1.2.3.net. Drew On Wed, 2004-07-28 at 17:16, Victor Pendleton wrote: Can you check the host name again? You have a five segment address. -Original Message- From: Andrew Hall To: [EMAIL PROTECTED] Sent: 7/28/04 3:55 PM Subject: connection problems Greetings, I have what I hope is an easy problem. I have installed mysql 4.0.20 and when I execute mysqladmin to set the root password using the -h flag my hostname is truncated, and I get a connection refused message. The hostname on this box is a fqdn like blah.1.2.3.net. My command line is mysqladmin -u root -h blah.1.2.3.net password 'test' mysqladmin -u root -h blah.1.2.3.net password test /path/to/mysql/bin/mysqladmin: connect to server at 'blah.1.2.3.net' failed error: 'Host 'blah' is not allowed to connect to this MySQL server' Can anyone shed any light on why the hostname is being truncated? Am I missing something obvious here? Thank you in advance, Andrew -- 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: Setting up MySQL on Raid Mirror
Rick Dwyer wrote: Assuming its not a super-high performance situation, I would put everything on the RAID 1 volume. Richard, what would you define as super-high performance? This MySQL database server will serve as the backend for a Lasso/ OS X Apache webserver handling thousands of hits per day. Will installing OS and MySQL on the Mirrored Drive be within the scope of that type of activity? The database basically collects customer data. Thanks. Rick Rick, That's a little harder question. I'll go through a typical calculation, but you'll have to adjust it for your situation. 1) It sounds like the typical database transactions are not that intensive, basically storing info about a single customer. MySQL should handle this in fractions of a second - even with everything on the same harddrive - assuming you do not have lots of other conflicting disk-intensive processes. 2) Let's say that your thousands is 10,000 customer add/updates per day, evenly spread over a 10 hour day (36000 seconds). Obviously customers do not evenly spread the load across the day, but initially let's say they do. 3) If you divide 36000 seconds by 10,000 update requests you end up with 3.6 seconds per request. 4) Now the only think you have to factor in is how the load will actually be distributed. You have to supply this intelligence, based on your customer knowledge. For example, if your customers are all in a single time zone and 90% of the updates happen during lunchtime your hardware may not be up to it - 9,000 transactions in an hour. I know this does not give you the answer you were looking for, but hope it helps. Sizing new hardware for an existing applications is not too bad if, you can usually figure out how much more processor and disk IO you need to buy. But for a new application, you really need to run some load testing on comparable hardware before deployment. Basic load testing is not hard, but it does take time. Really sophisticated load testing is a whole discipline in itself, but it does not seem like you need this. - Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOIN/WHERE and index confusion
Okay, now I'm even /more/ confused. I whittled everything down like so: CREATE INDEX reporting_t ON a (timestamp); CREATE INDEX reporting_t_id ON a (timestamp, a_id); CREATE INDEX reporting_id_t ON a (a_id, timestamp); EXPLAIN SELECT * FROM a, b WHERE a.a_id = b.a_id AND a.timestamp BETWEEN 2004010100 AND 20040101235959 and it /still/ only uses reporting_t! What the heck am I missing? Eamon Daly - Original Message - From: Eamon Daly [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, July 28, 2004 10:58 AM Subject: JOIN/WHERE and index confusion Hi, all. I think I'm misunderstanding something basic about indexes. I have a SELECT like so: SELECT * FROM a LEFT JOIN b ON a.a_id = b.a_id JOIN c ON a.c_id = c.c_id JOIN d ON c.d_id = d.d_id JOIN e ON c.e_id = e.e_id WHERE a.timestamp BETWEEN 2004010100 AND 20040101235959 GROUP BY c.d_id, c.e_id All of the id fields are primary indexes. I've already created an index on a.timestamp, and that works all right. I tried creating an index on a for the SELECT: KEY `reporting` (`a_id`,`c_id`,`timestamp`) and an index on c for the GROUP BY: KEY `reporting` (`c_id`,`d_id`,`e_id`) But EXPLAIN shows that MySQL isn't even considering the key on a, and chooses the primary key on c over my index. Clearly I'm confused about how indexes are used in a JOIN/WHERE situation: can anyone enlighten me? Eamon Daly -- 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]
Installing MySQL Databases on RAM Drive
Our customers are running MySQL 4.0.17 on QNX 6.2.1. We are currently using a flash drive to store our database but we want to store it in a RAM disk to prolong the life of the drive. We want to install the database on the RAM disk on startup and save it to flash on shutdown. There are two databases that we are using. Neither is very big but one is updated very frequently and the other one is not. However, even though the databases are small, the ibdata1 file is 136M. Is there a way to prevent this file from growing too big? ...Stephen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: connection problems
This is a local connection. Thanks again! Drew On Wed, 2004-07-28 at 17:33, Victor Pendleton wrote: My apologies I was reading an ip address. Have you tried logging in from the server itself? Since this is the initial install I think root needs to log in from the localhost. -Original Message- From: Andrew Hall To: Victor Pendleton Cc: '[EMAIL PROTECTED] ' Sent: 7/28/04 4:35 PM Subject: RE: connection problems Yes I have. The hostname of the box returned with 'hostname' is the fqdn and is in the format of blah.1.2.3.net. Drew On Wed, 2004-07-28 at 17:16, Victor Pendleton wrote: Can you check the host name again? You have a five segment address. -Original Message- From: Andrew Hall To: [EMAIL PROTECTED] Sent: 7/28/04 3:55 PM Subject: connection problems Greetings, I have what I hope is an easy problem. I have installed mysql 4.0.20 and when I execute mysqladmin to set the root password using the -h flag my hostname is truncated, and I get a connection refused message. The hostname on this box is a fqdn like blah.1.2.3.net. My command line is mysqladmin -u root -h blah.1.2.3.net password 'test' mysqladmin -u root -h blah.1.2.3.net password test /path/to/mysql/bin/mysqladmin: connect to server at 'blah.1.2.3.net' failed error: 'Host 'blah' is not allowed to connect to this MySQL server' Can anyone shed any light on why the hostname is being truncated? Am I missing something obvious here? Thank you in advance, Andrew -- 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: connection problems
The problem was /etc/hosts, but not for localhost. My issue was that whomever setup /etc/hosts swapped the fields: Old busted: IP blah blah.1.2.3.net Working: IP blah.1.2.3.net blah Oh well...Thank for every one's time. Drew On Wed, 2004-07-28 at 17:33, Victor Pendleton wrote: My apologies I was reading an ip address. Have you tried logging in from the server itself? Since this is the initial install I think root needs to log in from the localhost. -Original Message- From: Andrew Hall To: Victor Pendleton Cc: '[EMAIL PROTECTED] ' Sent: 7/28/04 4:35 PM Subject: RE: connection problems Yes I have. The hostname of the box returned with 'hostname' is the fqdn and is in the format of blah.1.2.3.net. Drew On Wed, 2004-07-28 at 17:16, Victor Pendleton wrote: Can you check the host name again? You have a five segment address. -Original Message- From: Andrew Hall To: [EMAIL PROTECTED] Sent: 7/28/04 3:55 PM Subject: connection problems Greetings, I have what I hope is an easy problem. I have installed mysql 4.0.20 and when I execute mysqladmin to set the root password using the -h flag my hostname is truncated, and I get a connection refused message. The hostname on this box is a fqdn like blah.1.2.3.net. My command line is mysqladmin -u root -h blah.1.2.3.net password 'test' mysqladmin -u root -h blah.1.2.3.net password test /path/to/mysql/bin/mysqladmin: connect to server at 'blah.1.2.3.net' failed error: 'Host 'blah' is not allowed to connect to this MySQL server' Can anyone shed any light on why the hostname is being truncated? Am I missing something obvious here? Thank you in advance, Andrew -- 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]
4.0.17 to 4.1.3 connection problem
I have a mysql connection problem that I'm trying to understand. The three servers and the version of mysql they are running (all under Solaris9) are: db1 - 4.0.16 db2 - 4.0.17 db3 - 4.1.3 All three systems have the same mysql user and passwords setup. I'll use the mythical user xx with password yy to explain here. Connections with the mysql client (using -uxx -pyy) from system to system all work except this one on db2: mysql -hdb3 -uxx -pyy ERROR 1045: Access denied for user 'xx'@'db2' (using password: YES) The mysql.user table entry has host=% and user=xx, so it's not simply an issue of a system-specific entry allowing one and not the other. Since db1 has no problem getting to db3, I wouldn't expect db2 to struggle. This same problem occurs with all users, so it' is also not something specific to how this user is setup. Does anyone know why this would be happening? Is there something different in 4.0.17 (compared to 4.0.16) that prevents it from connecting to the 4.1.3 server? I don't see anything in the 4.0.17 change list specific to this. Thanks -keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.0.17 to 4.1.3 connection problem
Keith: I don't know if it's the same problem, but I recently had issues where I had a similar setup with only two MySQL servers, one 4.0.20, the other 4.1.3, same usernames/passwords on each. My solution, and I don't remember where in the manual I saw this (possibly the FAQ), was to do an update to the 4.1.3 mysql user table: UPDATE user SET Password = OLD_PASSWORD( Password ) WHERE ... fill in with username/host/whatever I believe this was because I was trying to connect to MySQL 4.1.x with a 4.0.x client. I don't know if that's your case as well. http://dev.mysql.com/doc/mysql/en/Old_client.html Wes On Jul 28, 2004, at 6:21 PM, Keith Thompson wrote: I have a mysql connection problem that I'm trying to understand. The three servers and the version of mysql they are running (all under Solaris9) are: db1 - 4.0.16 db2 - 4.0.17 db3 - 4.1.3 All three systems have the same mysql user and passwords setup. I'll use the mythical user xx with password yy to explain here. Connections with the mysql client (using -uxx -pyy) from system to system all work except this one on db2: mysql -hdb3 -uxx -pyy ERROR 1045: Access denied for user 'xx'@'db2' (using password: YES) The mysql.user table entry has host=% and user=xx, so it's not simply an issue of a system-specific entry allowing one and not the other. Since db1 has no problem getting to db3, I wouldn't expect db2 to struggle. This same problem occurs with all users, so it' is also not something specific to how this user is setup. Does anyone know why this would be happening? Is there something different in 4.0.17 (compared to 4.0.16) that prevents it from connecting to the 4.1.3 server? I don't see anything in the 4.0.17 change list specific to this. Thanks -keith -- 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]
AW: TOP
Check out the LIMIT function in the documentation -Ursprüngliche Nachricht- Von: Kamal Ahmed [mailto:[EMAIL PROTECTED] Gesendet: Mittwoch, 28. Juli 2004 20:39 An: [EMAIL PROTECTED] Betreff: TOP Hi, Does anyone know how to do a TOP function in MySQL ? Thanks, -Kamal. -- 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]
connection time is slow
Hello. I'm using MySQL 4.1.2 on Linux(Fedora Core 2) and Window 2003 Server. When connect from Linux to Windows, or from Window to Linux connection time is very slow. ( it takes about 6 seconds) But Linux to Linux or Windows to Windows Fast. Anybody has an Idea? Thank you for advanced answer! == () email : [EMAIL PROTECTED] tel : 02-798-6822 fax : 02-749-9632 Web : http://www.ufamily.co.kr 104 3 == , We win!
Re: where its not a letter
Hi, Try the following: WHERE not (field like '%c%' or field like'%C%') Cheers, On Wed, 28 Jul 2004 22:23:40 +0100, John Berman [EMAIL PROTECTED] wrote: Hi Sure this is easy Im trying to create a simple select query but I want to return records unless a field contains a C or c so would it be something like where field 'c' or 'C' Regards John B -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Tiago Serafim [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: where its not a letter
-- Original message from Tiago Serafim : -- Hi, Try the following: WHERE not (field like '%c%' or field like'%C%') Cheers, I think since LIKE is case insensitive, unless the keyword BINARY is present, and the parser would have to collapse the extraneous parentheses WHERE NOT LIKE '%c%' would be slightly more efficient. applicable manual page -- http://dev.mysql.com/doc/mysql/en/String_comparison_functions.html On Wed, 28 Jul 2004 22:23:40 +0100, John Berman wrote: Hi Sure this is easy Im trying to create a simple select query but I want to return records unless a field contains a C or c so would it be something like where field 'c' or 'C' Regards John B -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Tiago Serafim [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]
Permissions problem with MySQL 4.1.3
Hello, I'd like to ask for some help on a problem which have prevented my mysql server to run since I updated it from 4.0 to 4.1 Anyway, even if in the idea it's an update, in the facts it's a fresh install, from sources. Sources were configure'd with ./configure --prefix=/usr/local/mysql. After doing everything I could find on google, manual, mailing lists and stuff, I still have basicly the same problems : (a) Running mysql_install_db --user=mysql /dev/null prints a lot of errors, most of them are Errcode: 13 (Permission denied), and resulting Table 'xxx.yyy' doesn't exist messages. See Annex A for detailed output. (b) Running libexec/mysqld --console --user=mysql prints : InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name /usr/local/mysql/var/ibdata1 InnoDB: File operation call: 'create'. InnoDB: Cannot continue operation. Running both of these programs as root works fine, but I don't really want mysql to run as root, even if it's just for testing purposes. There are some points which seems really strange to me. First point is that mysql_install_db is able to create mysql and test directories in var without problem. Nothing changes if I chown -R mysql:mysql /usr/local/mysql/var Nothing changes if I chown -R mysql:mysql /usr/local/mysql Nothing changes if I chmod -R 777 /usr/local/mysql Nothing changes if these these elements are made altogether. The other strange point is that if the var directory isn't owned by mysql user, InnoDB complaints about not beeing able to create innodb.status. in var. Once var is owned by mysql, it can create the file without problem. I can't understand why it cannot create ibdata1 if it can create the other one. I've also tried to install_db as root, then run mysqld as root for one time in order to get inno files created properly. Switching back to mysqld --user=mysql isn't possible anyway. I'm open to any suggestion or help, and available to provide any further information as needed. Thanks in advance for your time. Elie `woe` BLETON APPENDIX A Output of /usr/local/mysql/bin/mysql_install_db --user=mysql ERROR: 1 Can't create/write to file '/usr/local/mysql/var/mysql/db.MYI' (Errcode: 13) ERROR: 1146 Table 'mysql.db' doesn't exist ERROR: 1146 Table 'mysql.db' doesn't exist ERROR: 1 Can't create/write to file '/usr/local/mysql/var/mysql/host.MYI' (Errcode: 13) ERROR: 1 Can't create/write to file '/usr/local/mysql/var/mysql/user.MYI' (Errcode: 13) ERROR: 1146 Table 'mysql.user' doesn't exist ERROR: 1146 Table 'mysql.user' doesn't exist ERROR: 1146 Table 'mysql.user' doesn't exist ERROR: 1146 Table 'mysql.user' doesn't exist ERROR: 1 Can't create/write to file '/usr/local/mysql/var/mysql/func.MYI' (Errcode: 13) ERROR: 1 Can't create/write to file '/usr/local/mysql/var/mysql/tables_priv.MYI' (Errcode: 13) ERROR: 1 Can't create/write to file '/usr/local/mysql/var/mysql/columns_priv.MYI' (Errcode: 13) ERROR: 1 Can't create/write to file '/usr/local/mysql/var/mysql/help_topic.MYI' (Errcode: 13) ERROR: 1 Can't create/write to file '/usr/local/mysql/var/mysql/help_category.MYI' (Errcode: 13) ERROR: 1 Can't create/write to file '/usr/local/mysql/var/mysql/help_relation.MYI' (Errcode: 13) ERROR: 1 Can't create/write to file '/usr/local/mysql/var/mysql/help_keyword.MYI' (Errcode: 13) ERROR: 1 Can't create/write to file '/usr/local/mysql/var/mysql/time_zone_name.MYI' (Errcode: 13) ERROR: 1 Can't create/write to file '/usr/local/mysql/var/mysql/time_zone.MYI' (Errcode: 13) ERROR: 1 Can't create/write to file '/usr/local/mysql/var/mysql/time_zone_transition.MYI' (Errcode: 13) ERROR: 1 Can't create/write to file '/usr/local/mysql/var/mysql/time_zone_transition_type.MYI' (Errcode: 13) ERROR: 1 Can't create/write to file '/usr/local/mysql/var/mysql/time_zone_leap_second.MYI' (Errcode: 13) ERROR: 1146 Table 'mysql.help_topic' doesn't exist ERROR: 1146 Table 'mysql.help_category' doesn't exist ERROR: 1146 Table 'mysql.help_keyword' doesn't exist ERROR: 1146 Table 'mysql.help_relation' doesn't exist ERROR: 1146 Table 'mysql.help_category' doesn't exist ERROR: 1146 Table 'mysql.help_category' doesn't exist ERROR: 1146 Table 'mysql.help_topic' doesn't exist ERROR: 1146 Table 'mysql.help_topic' doesn't exist ERROR: 1146 Table 'mysql.help_topic' doesn't exist ERROR: 1146 Table 'mysql.help_topic' doesn't exist ERROR: 1146 Table 'mysql.help_topic' doesn't exist ERROR: 1146 Table 'mysql.help_topic' doesn't exist ERROR: 1146 Table 'mysql.help_topic' doesn't exist ERROR: 1146 Table 'mysql.help_topic' doesn't exist ERROR: 1146 Table 'mysql.help_topic' doesn't exist ERROR: 1146 Table 'mysql.help_topic' doesn't exist ERROR: 1146 Table 'mysql.help_topic' doesn't exist ERROR: 1146 Table 'mysql.help_topic' doesn't exist ERROR: 1146 Table 'mysql.help_topic' doesn't exist ERROR: 1146 Table
Re: connection time is slow
Hello Jungsu, What are the tools you're using to connect? Any client applications? Fred. Heo, Jungsu wrote: Hello. I'm using MySQL 4.1.2 on Linux(Fedora Core 2) and Window 2003 Server. When connect from Linux to Windows, or from Window to Linux connection time is very slow. ( it takes about 6 seconds) But Linux to Linux or Windows to Windows Fast. Anybody has an Idea? Thank you for advanced answer! == (ÁÖ)À¯ºñÄõÅͽº Æйи® °³¹ßÆÀ Çã Á¤ ¼ö ÁÖÀÓ email : [EMAIL PROTECTED] tel : 02-798-6822 fax : 02-749-9632 Web : http://www.ufamily.co.kr ¼¿ï½Ã ¿ë»ê±¸ ³²¿µµ¿ 104 û¼Ûºôµù 3Ãþ == ¿ï ÂÄ, ¿ï ¾¥ We win! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: where its not a letter
Hello, It should select * from table where field not like '%c%'; or to be safe; select * from table where field not like 'c%' and field not like '%c' and field not like '%c%'; Fred. Tiago Serafim wrote: Hi, Try the following: WHERE not (field like '%c%' or field like'%C%') Cheers, On Wed, 28 Jul 2004 22:23:40 +0100, John Berman [EMAIL PROTECTED] wrote: Hi Sure this is easy Im trying to create a simple select query but I want to return records unless a field contains a C or c so would it be something like where field 'c' or 'C' Regards John B -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: where its not a letter
I think since LIKE is case insensitive, unless the keyword BINARY is present, and the parser would have to collapse the extraneous parentheses WHERE NOT LIKE '%c%' would be slightly more efficient. applicable manual page -- http://dev.mysql.com/doc/mysql/en/String_comparison_functions.html You´re right, I forgot it thank you :j -- Tiago Serafim [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
slave should not stop
Is there any way within mysql to have the slaves not stop replicating on an error. For some reason, my application is trying to insert duplicate keys. This fails and the slaves stop replicating from the master. Ideally, the command that failed would just be skipped automatically and the whole system keeps going. If there's no way to fix this problem, has anyone come up with a workaround, like some sort of clever script that will keep things replicating? Thanks, Jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: connection time is slow
Hello. Sorry for lack of information. Any client applications or programming langueges do like that. PHP mysql_connect() function , MySQL's mysql command tool, etc. - Original Message - From: Frederick R. Doncillo [EMAIL PROTECTED] To: Heo, Jungsu [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, July 29, 2004 11:14 AM Subject: Re: connection time is slow Hello Jungsu, What are the tools you're using to connect? Any client applications? Fred. Heo, Jungsu wrote: Hello. I'm using MySQL 4.1.2 on Linux(Fedora Core 2) and Window 2003 Server. When connect from Linux to Windows, or from Window to Linux connection time is very slow. ( it takes about 6 seconds) But Linux to Linux or Windows to Windows Fast. Anybody has an Idea? Thank you for advanced answer! == () email : [EMAIL PROTECTED] tel : 02-798-6822 fax : 02-749-9632 Web : http://www.ufamily.co.kr 104 3 == , We win! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: where its not a letter
At 1:58 + 7/29/04, [EMAIL PROTECTED] wrote: -- Original message from Tiago Serafim : -- Hi, Try the following: WHERE not (field like '%c%' or field like'%C%') Cheers, I think since LIKE is case insensitive, unless the keyword BINARY is present, Close, but not quite. LIKE is case insensitive unless an operand is a binary string. BINARY happens to cause an operand to be a binary string. If an operand is already a binary string, LIKE will be case sensitive even in the absence of BINARY. and the parser would have to collapse the extraneous parentheses WHERE NOT LIKE '%c%' would be slightly more efficient. applicable manual page -- http://dev.mysql.com/doc/mysql/en/String_comparison_functions.html On Wed, 28 Jul 2004 22:23:40 +0100, John Berman wrote: Hi Sure this is easy Im trying to create a simple select query but I want to return records unless a field contains a C or c so would it be something like where field 'c' or 'C' -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_safe just ends
Cameron, Take a look in the data directory for a .err file. This error log will almost certainly contain the reason why Mysql failed to load and will make fixing it much easier. Richard On Wed, 28 Jul 2004 13:15:34 -0600, Cameron Roe [EMAIL PROTECTED] wrote: Thanks Rory, I did go through (as per the manual)and set the permissions shell groupadd mysql shell useradd -g mysql mysql shell cd /usr/local shell gunzip /path/to/mysql-VERSION-OS.tar.gz | tar xvf - shell ln -s full-path-to-mysql-VERSION-OS mysql shell cd mysql shell scripts/mysql_install_db --user=mysql shell chown -R root . shell chown -R mysql data shell chgrp -R mysql . shell bin/mysqld_safe --user=mysql As well, afterward I checked the permissions on the directories and the data directory seems to have the right permissions. i.e. 'mysql' owns the data directory. Checking the 'bin' directory, all the files are group 'mysql' and all have 'rx' for group. This seems to make sense to me. Cheers Cam -Original Message- From: Rory McKinley [mailto:[EMAIL PROTECTED] Sent: July 28, 2004 11:55 AM To: [EMAIL PROTECTED] Cc: Cam Subject: Re: mysql_safe just ends Cam wrote: So I'm a little confused here I've installed mysql-standard-4.0.20-pc-linux-i686.tar to /usr/local/mysql and then ran the scripts/mysql_install_db with seemingly no errors. After reading section 5.1 'the MySQL Server and Server Startup Scripts' I figured that cd /usr/local/mysql bin/mysqld_safe would simply work but no. I get [EMAIL PROTECTED] mysql]# bin/mysqld_safe [2] 11616 [EMAIL PROTECTED] mysql]# Starting mysqld daemon with databases from /var/lib/mysql 040728 10:50:16 mysqld ended [2]+ Donebin/mysqld_safe [EMAIL PROTECTED] mysql]# snip I don't know why, but something at the back of my mind is shouting 'Check Permissions', I think you need to check the OS file permissions for the folder in which you have stored the data for mySql. If memory serves, part of the installation process is to change file permissions and /or groups and ownership - methinks this is where your problem lies -- Rory McKinley Nebula Solutions +27 21 555 3227 - office +27 21 551 0676 - fax +27 82 857 2391 - mobile www.nebula.co.za This e-mail is intended only for the person to whom it is addressed and may contain confidential information which may be legally privileged. Nebula Solutions accepts no liability for any loss, expense or damage arising from this e-mail and/or any attachments. -- 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_fix_privilege_tables
I have upgraded MySQL from version 3.32 to 4.0 and am trying to upgrade the privilege tables by running the subject script. Regardless of what I set in the bindir= line of the script file I get errors from lines 100, 115, 132, and others that either %bindir%/mysql: No such file or directory or %bindir%/mysql: is a directory. The mysql database is located in the directory /var/lib/mysql on my Redhat 9 system. I did unintall the 3.32 version before installing the 4.0 and MySQL runs fine other than this issue. What should be in the bindir= option of the script? Thanks, Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb assertion failure after binary backup-restore
Hi!
- Original Message -
From: Sp.Raja [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Tuesday, July 27, 2004 5:52 PM
Subject: Re: Innodb assertion failure after binary backup-restore
Thanks for your replies.
Now I have three ways to go
1. replication=20
2. innodb hot backup tool
3. Make sure that no one is writing in to the database and start
backup=
when modified db pages in BUFFER POOL AND MEMORY becomes zero
#FLUSH TABLES WITH READ LOCK
still_to_flush=3D1=20=20
while [ $still_to_flush !=3D 0 ]
do
still_to_flush=3D`/usr/local/mysql/bin/mysql -e SHOW INNODB
STATUS=
\G | grep Modified db pages | awk '{print $4}'`
sleep 1
done
#UNLOCK TABLES
Do you think #3 will work?
not in the general case, since purge or the insert buffer merge may still be
running. You must wait that the status of the InnoDB main thread is 'Waiting
for server activity'. Then, if you are sure that clients are not doing
anything (calling COMMIT, for example), then probably all the buffer pool
data has been flushed to files, nothing is being written to the ib_logfiles.
I am not absolutely sure about this, I would need to check the code in
log0log.c and srv0srv.c. You can then copy the data files and ib_logfiles
as-is. To be safe, best that you check also the modification times of the
files after copying them. Check that they did not change while you were
copying.
Regards,
Sp.Raja
Best regards,
Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php
Order MySQL technical support from https://order.mysql.com/
Original Message
From: Heikki Tuuri [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Date: Tue, Jul-27-2004 6:24 PM
Subject: Re: Innodb assertion failure after binary backup-restore
=20
Hi!
=20
sync will not help.
=20
You can run SHOW INNODB STATUS\G to monitor when InnoDB has flushed its
buffer pool.
=20
Best regards,
=20
Heikki
Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up=20
MyISAM
tables
http://www.innodb.com/order.php
=20
Order MySQL support from http://www.mysql.com/support/index.html
=20
--
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]
