efficient query for: it's your birthday today
I have date of birth stored in a DATETIME column and need to find for which persons a certain date is their birthday. I've tried so far: DATE_FORMAT (col, '%m%d') = '1107'= 0.2001 sec col LIKE '%-11-07%'= 0.1643 sec col RLIKE '-11-07'= 0.1702 sec Are there faster alternatives for MySQL 4.0.21 ? Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using IN() clause
On Wed, 17 Nov 2004 21:02:27 -0800 (PST), Mitul Bhammar [EMAIL PROTECTED] wrote: The query is running fine for now. I wanted to know how MySQL interprets and executes this query and can it have problems in future?? Read about MySQL's query optimization here: http://dev.mysql.com/doc/mysql/en/Query_Speed.html The Benchmark() function could also be userful for you. Regards Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server Won't Start Next record offset is nonsensical
Clif,
I did not know that the mailing list censors email addresses :).
It is heikki dot tuuri at innodb dot com.
All spammers and viruses already know my email address. That is why there is
no harm showing it in public.
Regards,
Heikki
- Original Message -
From: Clif Smith [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Wednesday, November 17, 2004 10:28 PM
Subject: Re: Server Won't Start Next record offset is nonsensical
Cliff,
your OS or hardware has probably corrupted the ibdata file.
Ouch!
Next record offset is nonsensical 28769 in record at offset 7022
Before writing an index page to the file, InnoDB checks that offsets
are sensible ( 16 kB).
InnoDB: rec address 407b1b6e, first buffer frame 401c
InnoDB: buffer pool high end 409c, buf fix count 1
041116 17:17:10 InnoDB: Page dump in ascii and hex (16384 bytes):
len 16384; hex
large snip
;InnoDB: End of page dump
74 041116 17:17:10 InnoDB: Page checksum 3244520732,
prior-to-4.0.14-form checksum 1495873249
InnoDB: stored checksum 4145305205, prior-to-4.0.14-form stored
checksum 0
Hmm... the 'old checksum' at offsets 16 kB - 8 ... 16 kB - 4 is zero.
That is probably file corruption.
InnoDB: Resetting space id's in the doublewrite buffer
if (mach_read_from_4(doublewrite +
TRX_SYS_DOUBLEWRITE_SPACE_ID_STORED)
!= TRX_SYS_DOUBLEWRITE_SPACE_ID_STORED_N) {
/* We are upgrading from a version 4.1.x to a version
where
multiple tablespaces are supported. We must reset the
space id
field in the pages in the doublewrite buffer because
starting
from this version the space id is stored to
FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID. */
trx_doublewrite_must_reset_space_ids = TRUE;
fprintf(stderr,
InnoDB: Resetting space id's in the doublewrite buffer\n);
} else {
trx_sys_multiple_tablespace_format = TRUE;
}
The printout looks like you tried a downgrade and upgrade of MySQL to
resolve the crash? Is that true? Which 4.1.x version you are running?
Yes, initially I had thought that there was an issue with the
installation itself, so I upgraded to v4.1.7-0.
Please send the FULL .err log to me [EMAIL PROTECTED] for
more detailed analysis. Do not cut anything off.
Unfortunately the mailing list stripped your domain off. Please send an
email to spam at cjs226.com and I'll send you the err log.
Your lsn is only 300 MB. High-end users of InnoDB on a stable Linux
computer can easily reach 300 GB without any corruption.
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: Clif Smith [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Wednesday, November 17, 2004 4:53 AM
Subject: Server Won't Start Next record offset is nonsensical
Everything was fine...I haven't installed anything lately, etc. I've
got a Fedora FC1 system running MySQL v4. I noticed my db exports
failing this morning. The db wasn't running and now won't startup. I'm
googling but... Here's what's in the log:
41116 17:17:09 mysqld started
041116 17:17:09 [Warning] Asked for 196608 thread stack, but got 126976
041116 17:17:09 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Resetting space id's in the doublewrite buffer
041116 17:17:10 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 296311265.
041116 17:17:10 InnoDB: Starting an apply batch of log records to the
database...
InnoDB: Progress in percents: 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 InnoDB:
Next record offset is nonsensical 28769 in record at offset 7022
InnoDB: rec address 407b1b6e, first buffer frame 401c
InnoDB: buffer pool high end 409c, buf fix count 1
041116 17:17:10 InnoDB: Page dump in ascii and hex (16384 bytes):
len 16384; hex
large snip
;InnoDB: End of page dump
74 041116 17:17:10 InnoDB: Page checksum 3244520732,
prior-to-4.0.14-form checksum 1495873249
InnoDB: stored checksum 4145305205, prior-to-4.0.14-form stored
checksum 0
InnoDB: Page lsn 0 296329762, low 4 bytes of lsn at page end 296329762
InnoDB: Page number (if stored to page already) 6570,
InnoDB: space id (if created with = MySQL-4.1.1 and stored already) 0
InnoDB: Page may be an index page where index id is 0 38
041116 17:17:10InnoDB: Assertion failure in thread 12292 in file
../include/page0page.ic line 494
InnoDB: Failing assertion: 0
InnoDB: We intentionally generate a memory
Re: copy data only from one table to another table
On Wed, 17 Nov 2004 18:37:37 -0700, Jim McAtee [EMAIL PROTECTED] wrote: How would this be done if table_2 already exists? It has an auto_increment field as PK and I want to take all the rows from table_1 and dump them into table_2. The records being copied from table_1 can get new primary keys as there are no foreign key relationships to maintain. I suppose the following (provided the table structure of the both tables are exactly the same): first, make a backup ;) then, delete the primary key column of the table to be imported (for correct new primary key values later) create a table dump. do NOT make use of extended inserts, and be sure not to include the table creation commands. only the data itself is needed. Then import the data into the second table and be happy :) Bye Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Row level security requirements, can I still use MySQL?
Hello, I am in the position where I need row level user access, this is crucial in my current project. I know this has been discussed before and the answer has been use views when they become availble. But views would still allow the root user access to the complete table, wouldnt it? I would like to lock rows to certain user and not let anyone else see them, not even the root user. I have been thinking about using heap tables or trying to supply each user/group with their own dynamically created tables. But I always come to the conclusion that I am hacking away at something I do not fully understand and that I cannot guaranty that the end result will have the security I claim. Is this possible in MySQL? Does anyone know if it cab be performed with other RDBMS? Regards /Jonas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql NTFS
Hi! In the thread Re: mysqld-nt error 23. Urgent pls you stated that the file system is corrupt. Thus, the error 995 might result from that. There are two or three 995 reports from this year. I have suspected a bug in some disk driver in Windows, but maybe they are caused by file system corruption. From now on I know to instruct users to run CHKDSK if they encounter OS error number 995. Thank you, Heikki - Original Message - From: A Z [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, November 17, 2004 2:01 PM Subject: Mysql NTFS MySQL 4.0.14 Are there any known issues with MySQL (above version) and NTFS file system with 120 GB of hard-disk and Win 2000 pro. We are facing a problem of mysqld-nt crashing intermittently, throwing error numbers 995, 32. any help is appreciated. regards ___ Win a castle for NYE with your mates and Yahoo! Messenger http://uk.messenger.yahoo.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: Row level security requirements, can I still use MySQL?
Hi What about encrypting the data using a password that is specific to the user. That way only those that know the password for that row can access it. Locking is really to stop two users editing the same record at the same time, rather than controlling access. HTH Peter -Original Message- From: Jonas Ladenfors [mailto:[EMAIL PROTECTED] Sent: 18 November 2004 09:46 To: Mysql (E-mail) Subject: Row level security requirements, can I still use MySQL? Hello, I am in the position where I need row level user access, this is crucial in my current project. I know this has been discussed before and the answer has been use views when they become availble. But views would still allow the root user access to the complete table, wouldnt it? I would like to lock rows to certain user and not let anyone else see them, not even the root user. I have been thinking about using heap tables or trying to supply each user/group with their own dynamically created tables. But I always come to the conclusion that I am hacking away at something I do not fully understand and that I cannot guaranty that the end result will have the security I claim. Is this possible in MySQL? Does anyone know if it cab be performed with other RDBMS? Regards /Jonas -- 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]
www.partypagesstore.co.uk - supporting children in need
Dear Party Expert Just a quick email to let you know that PartyPages are now launching its new online store today. Its also Children in need this week so we thought we would support a great cause and donate £1 from every transaction that you make to them. Whether you are hosting a party, wedding, anniversary or going to one you need that special gift. Have a look through our New PartyPages store. With astonishingly low prices you will be amazed. Everything is of the highest quality and wont be found in your normal high street stores. Our Promise to you is if you can not find it here, then we will find it for you. Email us at [EMAIL PROTECTED] and tell us what you want. If you know of any quality products that we should stock then let us know. Visit www.partypagesstore.co.uk or via our link on the www.partypages.co.uk site Yours faithfully PartyPages Team Email: [EMAIL PROTECTED] PS. Remember £1 for every transaction goes to Children in Need so get spending.. This email was sent to:[EMAIL PROTECTED] ©2002-2003 Total Plus Management Ltd. All Rights Reserved. Disclaims any proprietary interest in copyrights, trademarks and names other than its own. All trademarks on this page belong to their respective owners. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Row level security requirements, can I still use MySQL?
Yeah you are correct locking is something else I actually meant was restricted access. If I understand you correctly I would then encrypt all information in the table I was interested in restricting access to. But if two or more users was to share a row in the table they would need a shared key? and then several user collaborations would result in a lot of different keys. I have actually been thinking about this solution earlier, my problem with it is where to store the different keys that are needed. Forcing the user to manually keep track of 5 - 10 keys is to much to hope for sadly;) What I have been thinking about is some low-level way where you as an administrator can control users and groups and place restrictions on each row by tagging the row in some way? Or the user could tag his rows in someway. Is this how other RDBMS enforce access restrictions? Regards /Jonas -Original Message- From: Peter Lovatt [mailto:[EMAIL PROTECTED] Sent: den 18 november 2004 11:03 To: Jonas Ladenfors; Mysql (E-mail) Subject: RE: Row level security requirements, can I still use MySQL? Hi What about encrypting the data using a password that is specific to the user. That way only those that know the password for that row can access it. Locking is really to stop two users editing the same record at the same time, rather than controlling access. HTH Peter -Original Message- From: Jonas Ladenfors [mailto:[EMAIL PROTECTED] Sent: 18 November 2004 09:46 To: Mysql (E-mail) Subject: Row level security requirements, can I still use MySQL? Hello, I am in the position where I need row level user access, this is crucial in my current project. I know this has been discussed before and the answer has been use views when they become availble. But views would still allow the root user access to the complete table, wouldnt it? I would like to lock rows to certain user and not let anyone else see them, not even the root user. I have been thinking about using heap tables or trying to supply each user/group with their own dynamically created tables. But I always come to the conclusion that I am hacking away at something I do not fully understand and that I cannot guaranty that the end result will have the security I claim. Is this possible in MySQL? Does anyone know if it cab be performed with other RDBMS? Regards /Jonas -- 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: Row level security requirements, can I still use MySQL?
Hi I use a system based on linux securuty model create groups - this will define access to the data, so you need to group the data - and encrypt data the group can access using the password belonging to that group. make users members of any number of groups, as required. Users can then access any data they are untitled to, but cannot read data encrytped with a password they do not have access to. You will need to use software (php, C++, asp, whatever) to manage the user/group system. HTH Peter -Original Message- From: Jonas Ladenfors [mailto:[EMAIL PROTECTED] Sent: 18 November 2004 10:19 To: 'Peter Lovatt'; 'Mysql (E-mail)' Subject: RE: Row level security requirements, can I still use MySQL? Yeah you are correct locking is something else I actually meant was restricted access. If I understand you correctly I would then encrypt all information in the table I was interested in restricting access to. But if two or more users was to share a row in the table they would need a shared key? and then several user collaborations would result in a lot of different keys. I have actually been thinking about this solution earlier, my problem with it is where to store the different keys that are needed. Forcing the user to manually keep track of 5 - 10 keys is to much to hope for sadly;) What I have been thinking about is some low-level way where you as an administrator can control users and groups and place restrictions on each row by tagging the row in some way? Or the user could tag his rows in someway. Is this how other RDBMS enforce access restrictions? Regards /Jonas -Original Message- From: Peter Lovatt [mailto:[EMAIL PROTECTED] Sent: den 18 november 2004 11:03 To: Jonas Ladenfors; Mysql (E-mail) Subject: RE: Row level security requirements, can I still use MySQL? Hi What about encrypting the data using a password that is specific to the user. That way only those that know the password for that row can access it. Locking is really to stop two users editing the same record at the same time, rather than controlling access. HTH Peter -Original Message- From: Jonas Ladenfors [mailto:[EMAIL PROTECTED] Sent: 18 November 2004 09:46 To: Mysql (E-mail) Subject: Row level security requirements, can I still use MySQL? Hello, I am in the position where I need row level user access, this is crucial in my current project. I know this has been discussed before and the answer has been use views when they become availble. But views would still allow the root user access to the complete table, wouldnt it? I would like to lock rows to certain user and not let anyone else see them, not even the root user. I have been thinking about using heap tables or trying to supply each user/group with their own dynamically created tables. But I always come to the conclusion that I am hacking away at something I do not fully understand and that I cannot guaranty that the end result will have the security I claim. Is this possible in MySQL? Does anyone know if it cab be performed with other RDBMS? Regards /Jonas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Row level security requirements, can I still use MySQL?
Ok, jupp if I could use groups each group could have a shared key. How do you create groups and then add users to them in MySQL? Are you refering to the Linux systems user and groups? This idea should work but I am not familiar with how groups work in mysql. I need to be able to audit logs on a per user level, is this possible in this solution? /Jonas Hi I use a system based on linux securuty model create groups - this will define access to the data, so you need to group the data - and encrypt data the group can access using the password belonging to that group. make users members of any number of groups, as required. Users can then access any data they are untitled to, but cannot read data encrytped with a password they do not have access to. You will need to use software (php, C++, asp, whatever) to manage the user/group system. HTH Peter -Original Message- From: Jonas Ladenfors [mailto:[EMAIL PROTECTED] Sent: 18 November 2004 10:19 To: 'Peter Lovatt'; 'Mysql (E-mail)' Subject: RE: Row level security requirements, can I still use MySQL? Yeah you are correct locking is something else I actually meant was restricted access. If I understand you correctly I would then encrypt all information in the table I was interested in restricting access to. But if two or more users was to share a row in the table they would need a shared key? and then several user collaborations would result in a lot of different keys. I have actually been thinking about this solution earlier, my problem with it is where to store the different keys that are needed. Forcing the user to manually keep track of 5 - 10 keys is to much to hope for sadly;) What I have been thinking about is some low-level way where you as an administrator can control users and groups and place restrictions on each row by tagging the row in some way? Or the user could tag his rows in someway. Is this how other RDBMS enforce access restrictions? Regards /Jonas -Original Message- From: Peter Lovatt [mailto:[EMAIL PROTECTED] Sent: den 18 november 2004 11:03 To: Jonas Ladenfors; Mysql (E-mail) Subject: RE: Row level security requirements, can I still use MySQL? Hi What about encrypting the data using a password that is specific to the user. That way only those that know the password for that row can access it. Locking is really to stop two users editing the same record at the same time, rather than controlling access. HTH Peter -Original Message- From: Jonas Ladenfors [mailto:[EMAIL PROTECTED] Sent: 18 November 2004 09:46 To: Mysql (E-mail) Subject: Row level security requirements, can I still use MySQL? Hello, I am in the position where I need row level user access, this is crucial in my current project. I know this has been discussed before and the answer has been use views when they become availble. But views would still allow the root user access to the complete table, wouldnt it? I would like to lock rows to certain user and not let anyone else see them, not even the root user. I have been thinking about using heap tables or trying to supply each user/group with their own dynamically created tables. But I always come to the conclusion that I am hacking away at something I do not fully understand and that I cannot guaranty that the end result will have the security I claim. Is this possible in MySQL? Does anyone know if it cab be performed with other RDBMS? Regards /Jonas -- 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]
Reg Table spaces and Rollback segments in MySQL 4.0.21
Hi ALL, We are using MySQL 4.0.21 with InnoDB. For creating the tablespace mentioned as innodb_data_file_path = ibdata1:10M:autoextend in my.cnf file. Here, is there a facility to know the table space name?. Shall we create multiple table spaces like the above in MySQL 4.0.21 and assign different tables to different table spaces?. Actually if we set autocommit=0, we are able to do the perform rollback and commit and is working as expected in different sessions. I think rollback segments are creating internally. Is there any facility to mention the rollback segment size?. Shall we create our own rollback segments in MYSQL 4.0.21? PS: Whenever Oracle Database is created Rollback segments were created in the System tablespace. Also, have the option of creating more Rollback segments in the non system tablespace. Do we have the same facility in MySQL 4.0.21? Please advise us for the solution. Thanks, Narasimha Confidentiality Notice The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain confidential or privileged information. If you are not the intended recipient, please notify the sender at Wipro or [EMAIL PROTECTED] immediately and destroy all copies of this message and any attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Row level security requirements, can I still use MySQL?
Hi, I think Peter's suggesting you implement that security model in your application, rather than in the database server. Apart from probably not supporting such a security model, it's easier to audit user activity if you are controlling the security model. Cheers, Luke Venediger. On Thu, 18 Nov 2004 11:46:15 +0100, Jonas Ladenfors [EMAIL PROTECTED] wrote: Ok, jupp if I could use groups each group could have a shared key. How do you create groups and then add users to them in MySQL? Are you refering to the Linux systems user and groups? This idea should work but I am not familiar with how groups work in mysql. I need to be able to audit logs on a per user level, is this possible in this solution? /Jonas Hi I use a system based on linux securuty model create groups - this will define access to the data, so you need to group the data - and encrypt data the group can access using the password belonging to that group. make users members of any number of groups, as required. Users can then access any data they are untitled to, but cannot read data encrytped with a password they do not have access to. You will need to use software (php, C++, asp, whatever) to manage the user/group system. HTH Peter -Original Message- From: Jonas Ladenfors [mailto:[EMAIL PROTECTED] Sent: 18 November 2004 10:19 To: 'Peter Lovatt'; 'Mysql (E-mail)' Subject: RE: Row level security requirements, can I still use MySQL? Yeah you are correct locking is something else I actually meant was restricted access. If I understand you correctly I would then encrypt all information in the table I was interested in restricting access to. But if two or more users was to share a row in the table they would need a shared key? and then several user collaborations would result in a lot of different keys. I have actually been thinking about this solution earlier, my problem with it is where to store the different keys that are needed. Forcing the user to manually keep track of 5 - 10 keys is to much to hope for sadly;) What I have been thinking about is some low-level way where you as an administrator can control users and groups and place restrictions on each row by tagging the row in some way? Or the user could tag his rows in someway. Is this how other RDBMS enforce access restrictions? Regards /Jonas -Original Message- From: Peter Lovatt [mailto:[EMAIL PROTECTED] Sent: den 18 november 2004 11:03 To: Jonas Ladenfors; Mysql (E-mail) Subject: RE: Row level security requirements, can I still use MySQL? Hi What about encrypting the data using a password that is specific to the user. That way only those that know the password for that row can access it. Locking is really to stop two users editing the same record at the same time, rather than controlling access. HTH Peter -Original Message- From: Jonas Ladenfors [mailto:[EMAIL PROTECTED] Sent: 18 November 2004 09:46 To: Mysql (E-mail) Subject: Row level security requirements, can I still use MySQL? Hello, I am in the position where I need row level user access, this is crucial in my current project. I know this has been discussed before and the answer has been use views when they become availble. But views would still allow the root user access to the complete table, wouldnt it? I would like to lock rows to certain user and not let anyone else see them, not even the root user. I have been thinking about using heap tables or trying to supply each user/group with their own dynamically created tables. But I always come to the conclusion that I am hacking away at something I do not fully understand and that I cannot guaranty that the end result will have the security I claim. Is this possible in MySQL? Does anyone know if it cab be performed with other RDBMS? Regards /Jonas -- 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] -- Get Firefox Browser! Reclaim the web. http://getfirefox.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Row level security requirements, can I still use MySQL?
On Thu, 18 Nov 2004 10:45:37 +0100, Jonas Ladenfors wrote: Hello, I am in the position where I need row level user access, this is crucial in my current project. I know this has been discussed before and the answer has been use views when they become availble. But views would still allow the root user access to the complete table, wouldnt it? I would like to lock rows to certain user and not let anyone else see them, not even the root user. The only way to do that is client side encryption. Otherwise a sufficiently privileged user can still see the data. (Even if it is just by sniffing the network traffic or attaching a custom debugger to the running process.) Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Row level security requirements, can I still use MySQL?
Well, This is a road I would like to avoid. We have several different applications fetching data from our MySQL system and constructing a application security layer is both a lot of work and have to be cusomized for some of the different applications. Some applications is not constructed by me and might not be applicable to this solution. Regards /Jonas -Original Message- From: Luke Venediger [mailto:[EMAIL PROTECTED] Sent: den 18 november 2004 12:35 To: Jonas Ladenfors Cc: Peter Lovatt; [EMAIL PROTECTED] Subject: Re: Row level security requirements, can I still use MySQL? Hi, I think Peter's suggesting you implement that security model in your application, rather than in the database server. Apart from probably not supporting such a security model, it's easier to audit user activity if you are controlling the security model. Cheers, Luke Venediger. On Thu, 18 Nov 2004 11:46:15 +0100, Jonas Ladenfors [EMAIL PROTECTED] wrote: Ok, jupp if I could use groups each group could have a shared key. How do you create groups and then add users to them in MySQL? Are you refering to the Linux systems user and groups? This idea should work but I am not familiar with how groups work in mysql. I need to be able to audit logs on a per user level, is this possible in this solution? /Jonas Hi I use a system based on linux securuty model create groups - this will define access to the data, so you need to group the data - and encrypt data the group can access using the password belonging to that group. make users members of any number of groups, as required. Users can then access any data they are untitled to, but cannot read data encrytped with a password they do not have access to. You will need to use software (php, C++, asp, whatever) to manage the user/group system. HTH Peter -Original Message- From: Jonas Ladenfors [mailto:[EMAIL PROTECTED] Sent: 18 November 2004 10:19 To: 'Peter Lovatt'; 'Mysql (E-mail)' Subject: RE: Row level security requirements, can I still use MySQL? Yeah you are correct locking is something else I actually meant was restricted access. If I understand you correctly I would then encrypt all information in the table I was interested in restricting access to. But if two or more users was to share a row in the table they would need a shared key? and then several user collaborations would result in a lot of different keys. I have actually been thinking about this solution earlier, my problem with it is where to store the different keys that are needed. Forcing the user to manually keep track of 5 - 10 keys is to much to hope for sadly;) What I have been thinking about is some low-level way where you as an administrator can control users and groups and place restrictions on each row by tagging the row in some way? Or the user could tag his rows in someway. Is this how other RDBMS enforce access restrictions? Regards /Jonas -Original Message- From: Peter Lovatt [mailto:[EMAIL PROTECTED] Sent: den 18 november 2004 11:03 To: Jonas Ladenfors; Mysql (E-mail) Subject: RE: Row level security requirements, can I still use MySQL? Hi What about encrypting the data using a password that is specific to the user. That way only those that know the password for that row can access it. Locking is really to stop two users editing the same record at the same time, rather than controlling access. HTH Peter -Original Message- From: Jonas Ladenfors [mailto:[EMAIL PROTECTED] Sent: 18 November 2004 09:46 To: Mysql (E-mail) Subject: Row level security requirements, can I still use MySQL? Hello, I am in the position where I need row level user access, this is crucial in my current project. I know this has been discussed before and the answer has been use views when they become availble. But views would still allow the root user access to the complete table, wouldnt it? I would like to lock rows to certain user and not let anyone else see them, not even the root user. I have been thinking about using heap tables or trying to supply each user/group with their own dynamically created tables. But I always come to the conclusion that I am hacking away at something I do not fully understand and that I cannot guaranty that the end result will have the security I claim. Is this possible in MySQL? Does anyone know if it cab be performed with other RDBMS? Regards /Jonas -- 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
RE: Row level security requirements, can I still use MySQL?
Yup, you are absolutly correct but my application runs in a closed enviroment and our average users does not have sniffing/debugging knowledge. So this might be something I could live without. BUT this is something which touches on what I said before. That creating a good security system is a very complex thing. There is always something you forget;). It is therefore I would like to apply an already existing solution. This would hopefully minimize the potential bugs or security flaws. Anyway I was given a link by Mark Leith (thanks!) on Oracle row level access that seems interesting. Here it is (not MySQL but the mind-work might be interesting). http://www.securityfocus.com/infocus/1743 Regards /Jonas -Original Message- From: Jochem van Dieten [mailto:[EMAIL PROTECTED] Sent: den 18 november 2004 12:48 To: Jonas Ladenfors Cc: Mysql (E-mail) Subject: Re: Row level security requirements, can I still use MySQL? On Thu, 18 Nov 2004 10:45:37 +0100, Jonas Ladenfors wrote: Hello, I am in the position where I need row level user access, this is crucial in my current project. I know this has been discussed before and the answer has been use views when they become availble. But views would still allow the root user access to the complete table, wouldnt it? I would like to lock rows to certain user and not let anyone else see them, not even the root user. The only way to do that is client side encryption. Otherwise a sufficiently privileged user can still see the data. (Even if it is just by sniffing the network traffic or attaching a custom debugger to the running process.) Jochem -- 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: Row level security requirements, can I still use MySQL?
On Thu, 18 Nov 2004 13:07:11 +0100, Jonas Ladenfors wrote: Anyway I was given a link by Mark Leith (thanks!) on Oracle row level access that seems interesting. Here it is (not MySQL but the mind-work might be interesting). http://www.securityfocus.com/infocus/1743 It does not meet your initial requirement that there would be no root user with the ability to access the data. But if that is OK most databases have it one way or another, usually through a view + procedure. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MicroOLAP Database Designer for MySQL 1.4 is released
Greetings All, The new version 1.4 of microOLAP Database Designer for MySQL has been released. Since now it supports the new features of MySQL 5.x: stored procedures and functions. What's new: [+] Added support of MySQL 5.x stored procedures and functions. [+] Added reverse engineering of stored procedures and functions. [+] Added stored procedures and functions manager that allows to change parameters of stored routines with easy. [+] Support of before and after object create scripts was added. These scripts allow executing of custom SQL statements before and after generation of tables and functions. [+] Ability to create multiple objects of the same type was added. [+] Ability to set the number of diagram sheets for printing was added. [+] Ability to add headers and footers to diagram pages was added. [-] Column Editor: setting of integer fields size is corrected. [-] Fixed bug occured on the Create Report tool launching. [-] The table comments weren't reverse engineered and generated. [-] Fixed bugs in the print preview. Direct download link: http://microolap.com/dba/mysql/designer/mymdd.zip How to install: Unzip and install it over the old version. How to extend trial period: E-mail us at [EMAIL PROTECTED], and we'll send you temporary registration key if 30-days fully functional trial is not enough for you for any reason. What about educational discounts: E-mail us at [EMAIL PROTECTED], and we'll provide you with the 50% discount coupon code. -- Best regards, Edward Smirnov microOLAP Technologies LTD mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Static library for MySQL C API
Hello. See: http://dev.mysql.com/doc/mysql/en/Windows_client_compiling.html Karam Chand [EMAIL PROTECTED] wrote: Hello, Is it possible to get static library for MySQL C API() instead of libmysql.dll? Regards, Karam __ Do you Yahoo!? Meet the all-new My Yahoo! - Try it today! http://my.yahoo.com -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Row level security requirements, can I still use MySQL?
Hi yes that is a loophole you could encrypt the passwords using a password held in the software so SELECT DECODE(g.datapasword ,system pass supplied by software) FROM `User` u, `Groups` g, `Groups_user_link` l WHERE u.userID = users ID here AND u.userID = l.userID AND l.groupID = g.groupID someone with root access to the database and access to the software sourcecode would still be able to gain access, but it is an extra layer of security. Peter -Original Message- From: Jonas Ladenfors [mailto:[EMAIL PROTECTED] Sent: 18 November 2004 12:36 To: 'Peter Lovatt' Subject: RE: Row level security requirements, can I still use MySQL? Ok, This is a nice solution but it still lacks a thing I would like to avoid. Any user with global access for example root would have access to the groups table and could easily se which password was allocated to each group and in that way bypass the security system? Or have I misunderstod your solution. In any case, thanks alot for wasting som brain-time on this ;) Regards /Jonas -Original Message- From: Peter Lovatt [mailto:[EMAIL PROTECTED] Sent: den 18 november 2004 12:13 To: Jonas Ladenfors Subject: RE: Row level security requirements, can I still use MySQL? The groups system would work in software I would suggest in mysql create tables `User` userID username password `Groups` groupID data_password `Groups_user_link` userID groupID In software log user in select data (group) to view SELECT g.datapasword FROM `User` u, `Groups` g, `Groups_user_link` l WHERE u.userID = users ID here AND u.userID = l.userID AND l.groupID = g.groupID which will return the password for the data group the software will then retrieve the data using the password HTH Peter -Original Message- From: Jonas Ladenfors [mailto:[EMAIL PROTECTED] Sent: 18 November 2004 10:46 To: 'Peter Lovatt'; [EMAIL PROTECTED] Subject: RE: Row level security requirements, can I still use MySQL? Ok, jupp if I could use groups each group could have a shared key. How do you create groups and then add users to them in MySQL? Are you refering to the Linux systems user and groups? This idea should work but I am not familiar with how groups work in mysql. I need to be able to audit logs on a per user level, is this possible in this solution? /Jonas Hi I use a system based on linux securuty model create groups - this will define access to the data, so you need to group the data - and encrypt data the group can access using the password belonging to that group. make users members of any number of groups, as required. Users can then access any data they are untitled to, but cannot read data encrytped with a password they do not have access to. You will need to use software (php, C++, asp, whatever) to manage the user/group system. HTH Peter -Original Message- From: Jonas Ladenfors [mailto:[EMAIL PROTECTED] Sent: 18 November 2004 10:19 To: 'Peter Lovatt'; 'Mysql (E-mail)' Subject: RE: Row level security requirements, can I still use MySQL? Yeah you are correct locking is something else I actually meant was restricted access. If I understand you correctly I would then encrypt all information in the table I was interested in restricting access to. But if two or more users was to share a row in the table they would need a shared key? and then several user collaborations would result in a lot of different keys. I have actually been thinking about this solution earlier, my problem with it is where to store the different keys that are needed. Forcing the user to manually keep track of 5 - 10 keys is to much to hope for sadly;) What I have been thinking about is some low-level way where you as an administrator can control users and groups and place restrictions on each row by tagging the row in some way? Or the user could tag his rows in someway. Is this how other RDBMS enforce access restrictions? Regards /Jonas -Original Message- From: Peter Lovatt [mailto:[EMAIL PROTECTED] Sent: den 18 november 2004 11:03 To: Jonas Ladenfors; Mysql (E-mail) Subject: RE: Row level security requirements, can I still use MySQL? Hi What about encrypting the data using a password that is specific to the user. That way only those that know the password for that row can access it. Locking is really to stop two users editing the same record at the same time, rather than controlling access. HTH Peter -Original Message- From: Jonas Ladenfors [mailto:[EMAIL PROTECTED] Sent: 18 November 2004 09:46 To: Mysql (E-mail) Subject: Row level security requirements, can I still use MySQL? Hello, I am in the position where I need row level user
RE: copy data only from one table to another table
[snip] How do I copy all data only from one table into another table? Both tables are in the same database. I have phpMyAdmin and it suppossedly does this, but it is not working, and there are no error messages. [/snip] INSERT INTO `table2` (`columnnames`) SELECT `columnnames` FROM `table1` -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ANN: Database Workbench 2.6.0 released
Ladies, gentlemen, Upscene Productions is happy to announce the next version of the popular database development tool: Database Workbench 2.6.0 has been released today! Based on user feedback, there are numerous bugfixes, new functionality and enhancements. Download a trial at: http://www.upscene.com Features and fixes: http://www.upscene.com/news/20041118.htm Database Workbench supports: - Borland InterBase ( v4.x - v7.x ) - Firebird ( v1.x ) - MS SQL Server/MSDE ( v6.5, 7, 2000, MSDE 1 2 ) - MySQL 4, 4.1 If you experience any problems with this new version, don't hestitate and either go to the website and send a support email or email directly to [EMAIL PROTECTED] New Features - InterBase 7.5 Temporary Tables support - MSSQL: allow FKs to be created to Unique Indices - MySQL: added ability to register additional databases at a server to which you don't have access with the user name you used to register the server - Added Copy as RTF to Code Editors - Added Multi-Level Clipboard Tool - GUID support for ODBC in DataPump Enhancements - Misc GUI improvements - IB/Fb: Confirm automatically adjust registration dialect when registered with the wrong dialect - MSSQL: more error messages before saving constraints - MSSQL: creating a database now allows you to enter your own logical filename besides physical names - MySQL/IB/Fb: more error messages before saving Procedures - Syntax highlighter supports system functions as a seperate item to be highlighted. Check the Preferences for this. - Preferences setting for empty line is separator in SQL Editor - SQL Editor: Options drop down button - TDG: - emptying tables in backwards order - improved error handling and reporting - Added Select All/Deselect All buttons to Schema Compare and Schema Migration Wizards/Dialogs Fixes - Failing to connect to a server when expanding a node in the EM now doesn't spawn multiple error dialogs - interface not supported error when modifying an object while having the DB Navigator tree, depending objects openen that included that object - Describe Companion: no longer loses selection when auto-refreshing details - Hopefully an AV fixed upon closing the SQL Editor - Possible AV fixed when disconnecting from a database - Fixed error when disconnecting from db when there are disconnect errors: EM would list the db as connected - Schema Compare: - fixed AV issue when collecting script - fixed not including indices/triggers/constraints for new tables when collecting scripts/showing compare results - some minor GUI issues - ignore case attribute was ignored when searching for columns - Domain set when migrating single column to MS SQL wasn't set - IB/Fb: fixed an error in the debugger wrt numeric constants - IB/Fb: fixed a bug with the debugger and BLOBs - IB/Fb: check constraints could show up as triggers in the depending objects for older databases - IB/Fb: using the Recompute index selectivity on a Indices list in a table would give an error - MSSQL: fixed a problem with attaching databases that moved locations between detach/attach - MSSQL: fixed a problem with attaching databases on server v7 - MSSQL: fixed possible error when retrieving PK information - Starting the ML-Clipboard without anything onthe clipboard would get you a list index out of bounds (0) - AV fixed when right-clicking the source grid in the DataPump without a connection - AV fixed when dropping a PK after selected a certain node - DataPump now allows dragging by image instead of text only - FetchAll flag on a per-database engine basis - Using the Visual Query Builder in the View Editor adds text without overwriting previous text - No more black rectangle (Windows XP) when connecting to a database - Database Navigator: Count rows SQL didn't include owner name - Statement Recorder: kept using TERMINATOR for IB/Fb instead of current database engine (eg: MSSQL) - DataPump: better auto-map name matching, matches including owner for MS SQL Thank you for your support, Martijn Tonies Database Workbench - the database developer tool for professionals 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: Static library for MySQL C API
Thanks. We haev to compile it with mysqlclient.lib I just the binary distribution of MySQL 4.1.7 for Windows. The size of mysqlclient.lib is 1.5MB whereas libmysql.dll is 1.04MB and libmysql.lib is around 33K. Why is the difference in size? Regards, Karam --- Gleb Paharenko [EMAIL PROTECTED] wrote: Hello. See: http://dev.mysql.com/doc/mysql/en/Windows_client_compiling.html Karam Chand [EMAIL PROTECTED] wrote: Hello, Is it possible to get static library for MySQL C API() instead of libmysql.dll? Regards, Karam __ Do you Yahoo!? Meet the all-new My Yahoo! - Try it today! http://my.yahoo.com -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Meet the all-new My Yahoo! - Try it today! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Column restrictions in 4.1.x from 3.23.52
Whilst we're using 4.1.4 for most of our development, on e of our developers who works remotely has been using 3.23.52 for his development. In trying to migrate his database onto 4.1.4 (or 4.1.7) we appear to have hit an introduce restriction in 4.1.x. It appears that the length of the column names has a direct relationship to the number of columns allowed in a table. In the 3.23.52 database we have 1200+ columns in a table. With the same length name in 4.1.4 we can get about 200. If we restrict the name to 4 characters we can get approx 2600 columns. Is this a bug or and undocumented feature change? We're using MyIASM tables. Kevin Cowley RD Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. **
Re: Row level security requirements, can I still use MySQL?
Hi! On Nov 18, Jonas Ladenfors wrote: Hello, I am in the position where I need row level user access, this is crucial in my current project. I know this has been discussed before and the answer has been use views when they become availble. But views would still allow the root user access to the complete table, wouldnt it? I would like to lock rows to certain user and not let anyone else see them, not even the root user. In MySQL there's no concept of root user. If you make sure that no user on the system has select privileges on the underlying tables, than nobody will be able to select from them. 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]
Re: copy data only from one table to another table
- Original Message - From: Jay Blanchard [EMAIL PROTECTED] To: Chip Wiegand [EMAIL PROTECTED]; MySQL List [EMAIL PROTECTED] Sent: Thursday, November 18, 2004 8:10 AM Subject: RE: copy data only from one table to another table [snip] How do I copy all data only from one table into another table? Both tables are in the same database. I have phpMyAdmin and it suppossedly does this, but it is not working, and there are no error messages. [/snip] INSERT INTO `table2` (`columnnames`) SELECT `columnnames` FROM `table1` If this is valid SQL surely grave accents are not? -Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Row level security requirements, can I still use MySQL?
Yeah, What i am referring to is rather the Global access options in MySQL. I have one root user with full global access for administration purposes. An option would be to remove the root user and let every user including the root user have access on table level instead of global access. But I fear that this would complicate administration. Each new table would need to be assign access to the root user. A better solution in my perspective is that the tables that need to be accessable on a user level should be marked as excluded from the global access option. But your suggestion of removing the root user would solve my problem but increase administration?. /jonas -Original Message- From: Sergei Golubchik [mailto:[EMAIL PROTECTED] Sent: den 18 november 2004 14:29 To: Jonas Ladenfors Cc: Mysql (E-mail) Subject: Re: Row level security requirements, can I still use MySQL? Hi! On Nov 18, Jonas Ladenfors wrote: Hello, I am in the position where I need row level user access, this is crucial in my current project. I know this has been discussed before and the answer has been use views when they become availble. But views would still allow the root user access to the complete table, wouldnt it? I would like to lock rows to certain user and not let anyone else see them, not even the root user. In MySQL there's no concept of root user. If you make sure that no user on the system has select privileges on the underlying tables, than nobody will be able to select from them. 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
2 ways Replication in MySQL
Hi, I need to expand the database to 3 different locations. We have 3 servers in 3 cities. So far the database has been in one city and 2 others have been linked to it and worked. But sometimes for a day or more a city lost the connection to the master database and the users could not work. Now I am going to change the method to have a copy of database on each location. Each location should be able to change the data also. All tables are myISAM. I am thinking about 2 ways Replication in MySQL but as MySQL document recommended not to do it because there is no guarantee that we won't have any problem (slow connection or losing connection in a period of time). Have you ever had this kind of situation? How did you solve it? Have you ever found any problem in your solution? Thanks in advance for you advice! Mojtaba -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: copy data only from one table to another table
[snip] INSERT INTO `table2` (`columnnames`) SELECT `columnnames` FROM `table1` If this is valid SQL surely grave accents are not? [/snip] Actually? MySQL supports the use of grave accents around table and column names. I use them here for emphasis. In certain cases, with older versions of MySQL, I encourage our developers to always use them. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 2 ways Replication in MySQL
Mojtaba Faridzad [EMAIL PROTECTED] wrote on 18/11/2004 13:59:21: Hi, I need to expand the database to 3 different locations. We have 3 servers in 3 cities. So far the database has been in one city and 2 others have been linked to it and worked. But sometimes for a day or more a city lost the connection to the master database and the users could not work. Now I am going to change the method to have a copy of database on each location. Each location should be able to change the data also. All tables are myISAM. I am thinking about 2 ways Replication in MySQL but as MySQL document recommended not to do it because there is no guarantee that we won't have any problem (slow connection or losing connection in a period of time). Have you ever had this kind of situation? How did you solve it? Have you ever found any problem in your solution? What you are attempting to do is inherently difficult, and I don't think any DBMS has solved it. What do you expect to happen if the links between cities are down, and the *same* row in the database is updated differently by different users? Even when the link is up, you have the possibility of a race condition if users in different places update records within a narrow window. The closest we got to this was having a master database in one place and read-only slaves in another. UPDATE commands were always sent to the master copy, and could not be done when the link was down. SELECTs were sent to the local slave and could therefore continue when the link was down. At the application level, we pipelined a few necessary but uncomplicated updates to be done when the link returned. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 2 ways Replication in MySQL
I worked on a military system that went further than this, but again required a proprietary application to perform the updates. Databases were either slave, master, of standalone. Any update transaction was logged to a file. If the master was available then If we are the master we apply the update and sent a replication update to all other databases. If we are the slave we send the transaction to the master and wait for its response. If the master was unavailable then we store the transaction until the master becomes available. At this point we apply any pending updates from the master. We then send our updates to the master an wait for a response. If the master detects a conflict between slave updates and pre applied updates then it refuses the update and sends a conflict message back to the originating slave, where it is up to the user to resolve manually. Standalone is a special version of master. It applies updates locally but remembers what the last update it received from the master was. When it is reconnected to the master the user must manually resolve conflicts and determine which, if any, of the updates should be applied to the master. When I left 14 months into the project we had the basic replication engine working but none of the conflict resolution stuff. Kevin Cowley RD Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk The closest we got to this was having a master database in one place and read-only slaves in another. UPDATE commands were always sent to the master copy, and could not be done when the link was down. SELECTs were sent to the local slave and could therefore continue when the link was down. At the application level, we pipelined a few necessary but uncomplicated updates to be done when the link returned. Alec .com/[EMAIL PROTECTED] ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 2 ways Replication in MySQL
Thanks Alec, how is a bank system implemented? do they have just one master sever and all the other servers are slaves? you solution is not bad and I should think more about it. it's close to one of my solutions: I should convert the database to InnoDB. when a user in location A needs to update or add a new record, the program in background should lock the record in all 3 locations. if it is successful, then user in A can change the data and program should update all 3 locations. if updating has any problem, send rollback command to the other locations. I should have a commands waiting list. if update command could get through but rollback could not, after connection backed, send it to the server. then I should think about how to solve dead lock (if location A send lock to B, and C and before release the lock, connection gone. then B and C should not be locked forever : anyways, it's complicated. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, November 18, 2004 9:34 AM Subject: Re: 2 ways Replication in MySQL Mojtaba Faridzad [EMAIL PROTECTED] wrote on 18/11/2004 13:59:21: Hi, I need to expand the database to 3 different locations. We have 3 servers in 3 cities. So far the database has been in one city and 2 others have been linked to it and worked. But sometimes for a day or more a city lost the connection to the master database and the users could not work. Now I am going to change the method to have a copy of database on each location. Each location should be able to change the data also. All tables are myISAM. I am thinking about 2 ways Replication in MySQL but as MySQL document recommended not to do it because there is no guarantee that we won't have any problem (slow connection or losing connection in a period of time). Have you ever had this kind of situation? How did you solve it? Have you ever found any problem in your solution? What you are attempting to do is inherently difficult, and I don't think any DBMS has solved it. What do you expect to happen if the links between cities are down, and the *same* row in the database is updated differently by different users? Even when the link is up, you have the possibility of a race condition if users in different places update records within a narrow window. The closest we got to this was having a master database in one place and read-only slaves in another. UPDATE commands were always sent to the master copy, and could not be done when the link was down. SELECTs were sent to the local slave and could therefore continue when the link was down. At the application level, we pipelined a few necessary but uncomplicated updates to be done when the link returned. Alec -- 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: copy data only from one table to another table
- Original Message - From: Jay Blanchard [EMAIL PROTECTED] To: Jonathan Mangin [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, November 18, 2004 9:19 AM Subject: RE: copy data only from one table to another table [snip] INSERT INTO `table2` (`columnnames`) SELECT `columnnames` FROM `table1` If this is valid SQL surely grave accents are not? [/snip] Actually? MySQL supports the use of grave accents around table and column names. I use them here for emphasis. In certain cases, with older versions of MySQL, I encourage our developers to always use them. Well, never mind then. I thought only apostrophes were valid. -Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: copy data only from one table to another table
[snip] [snip] INSERT INTO `table2` (`columnnames`) SELECT `columnnames` FROM `table1` If this is valid SQL surely grave accents are not? [/snip] Actually? MySQL supports the use of grave accents around table and column names. I use them here for emphasis. In certain cases, with older versions of MySQL, I encourage our developers to always use them. Well, never mind then. I thought only apostrophes were valid. [/snip] You would use apostrophes around the data itself. Consider this... INSERT INTO `table2` (`columnA2`, `columnB2`) SELECT `columnA1`, `columnB1` FROM `table1` WHERE `columnA1` = 'this data' AND `columnB1` = 'that data' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: efficient query for: it's your birthday today
Really what you are trying to do is search on month + day, not a date. For special dates (birthday, anniversary, etc), I always store the year separately. Especially since some people don't really want you to know how old they are. Without breaking the date up into it's separate parts, you can't use an index, so you will always do a full table scan. Your searches will get slower as you add more records. I don't know how many records you are searching on in your example, but if you have a lot, the difference is pretty minimal and may be due to slightly different loads on the computer. Although the DATE_FORMAT one has the extra overhead of formating every single record to do the comparison. On Nov 18, 2004, at 4:28 AM, Jigal van Hemert wrote: I have date of birth stored in a DATETIME column and need to find for which persons a certain date is their birthday. I've tried so far: DATE_FORMAT (col, '%m%d') = '1107'= 0.2001 sec col LIKE '%-11-07%'= 0.1643 sec col RLIKE '-11-07'= 0.1702 sec Are there faster alternatives for MySQL 4.0.21 ? Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- 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: Row level security requirements, can I still use MySQL?
At 03:45 AM 11/18/2004, you wrote: Hello, I am in the position where I need row level user access, this is crucial in my current project. I know this has been discussed before and the answer has been use views when they become availble. But views would still allow the root user access to the complete table, wouldnt it? I would like to lock rows to certain user and not let anyone else see them, not even the root user. I have been thinking about using heap tables or trying to supply each user/group with their own dynamically created tables. But I always come to the conclusion that I am hacking away at something I do not fully understand and that I cannot guaranty that the end result will have the security I claim. Is this possible in MySQL? Does anyone know if it cab be performed with other RDBMS? How many users do you have? If fewer than 10, why not just create 10 different tables and put a different user password on each? How are they accessing this information? If it is through a program that you've written, then all you need to do is wrap an AND clause around each Where clause that gets constructed. Example: Select * from table1 where date = '2004-01-01' becomes Select * from table1 where (date = '2004-01-01') and useridcol = LoginId So you add ( ) around the existing where clause, and add AND useridcol = LoginId to the end of each of these WHERE clauses. Each of these user tables has a UserIdCol which can be an ENUM (or SET if more than 1 user can see it) and LoginId is the user id that was used to log into your program with. All of the database passwords are handled by your program and you don't give out any MySQL passwords to your users. This prevents the users from accessing the database unless it is through your program. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: efficient query for: it's your birthday today
[snip] Really what you are trying to do is search on month + day, not a date. For special dates (birthday, anniversary, etc), I always store the year separately. Especially since some people don't really want you to know how old they are. Without breaking the date up into it's separate parts, you can't use an index, so you will always do a full table scan. Your searches will get slower as you add more records. I don't know how many records you are searching on in your example, but if you have a lot, the difference is pretty minimal and may be due to slightly different loads on the computer. Although the DATE_FORMAT one has the extra overhead of formating every single record to do the comparison. [/snip] So if I index a date field (given -mm-dd) and then SELECT `userNames` FROM `userTable` WHERE SUBSTRING(`userBirthDate`, 6, 5) = SUBSTRING(NOW(), 6, 5) it is still very fast. I have a table with several thousand records in it and I get back ... 30 rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: copy data only from one table to another table
A semi-generic solution: You have tableA with columns (col1, col2, col3, ..., colN) where col1 is an auto_increment column. You have tableB that looks just like tableA except for some additional columns (extra1, extra2, col1, col2, col3, ..., colN, extra3, extra4). TableB is intended to act as an archive table for several tables of identically organized information. for tableB the extra columns contain: extra1 - the name of the table where these rows are coming from extra2 - a new auto_increment id extra3 - a datetime column to show when the rows were copied. extra4 - initially null (BACKGROUND: This example is based on a situation that was once involved with. Each tableA contained billing information for different categories of clientel. The application they were designing was not responding quickly enough with one consolidated table of data so the decision was made to split it by category into separate tables. That decision improved their performance but created an administrative load they had not anticipated and they hired me to help work it out.) Now if you want to copy records from tableA to tableB you write an INSERT...SELECT... statement that would look like this: INSERT tableB (extra1, extra3, col1, col2, col3, ..., colN) SELECT 'tableA', NOW(), col1, col2, col3, ..., colN FROM tableA WHERE (some appropriate conditions)... Each value (column or literal) in the SELECT clause lines up one-to-one with each column listed in the INSERT () clause. A more trivial solution exists when the tables are virtually identical except you want the moved records to be autonumbered when they get to the new table. In this situation tableB has identical columns to tableA. For both tables col1 is an auto_increment column. To move records from tableA to tableB so that they get autonumbered by tableB you need to write a statement like this INSERT tableB (col2, col3, col4, ..., colN) SELECT col2, col3, col4, ..., colN FROM tableA WHERE ...( some appropriate conditions)... In both examples, we had to declare ALL of the source columns/values and the destination columns (cannot use *) because we needed to specifically exclude the auto_increment columns from the effects of the statement. Does that make sense or just make it worse? Shawn Green Database Administrator Unimin Corporation - Spruce Pine Jim McAtee [EMAIL PROTECTED] wrote on 11/17/2004 08:37:37 PM: - Original Message - From: Daniel Kasak [EMAIL PROTECTED] To: Chip Wiegand [EMAIL PROTECTED]; MySQL List [EMAIL PROTECTED] Sent: Wednesday, November 17, 2004 5:04 PM Subject: Re: copy data only from one table to another table Chip Wiegand wrote: How do I copy all data only from one table into another table? Both tables are in the same database. I have phpMyAdmin and it suppossedly does this, but it is not working, and there are no error messages. Thanks, -- Chip create table table_2 select * from table_1 How would this be done if table_2 already exists? It has an auto_increment field as PK and I want to take all the rows from table_1 and dump them into table_2. The records being copied from table_1 can get new primary keys as there are no foreign key relationships to maintain. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 2 ways Replication in MySQL
Thanks Kevin! that's a good method. but in this method we should not have any AUTO_INCREMENT field in tables and a master table should take care of giving a unique key. because at first we should add a record to a table then mysql gives the key. if it's a slave the master is off, then we will be in trouble (two slaves needs to add to the same table when master is off). in this case slaves cannot add any record if they don't have connection. unless I change the key and add the location to the key also (that's a big change and program should be changed also). on the other hand usually primary key is not a field that is visible for the user. then program can change the primary key if there are duplicated in master table and fix all relational tables. anyways, I am wondering how ORACLE can handle this kind of situtaion. - Original Message - From: Kevin Cowley [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, November 18, 2004 9:56 AM Subject: RE: 2 ways Replication in MySQL I worked on a military system that went further than this, but again required a proprietary application to perform the updates. Databases were either slave, master, of standalone. Any update transaction was logged to a file. If the master was available then If we are the master we apply the update and sent a replication update to all other databases. If we are the slave we send the transaction to the master and wait for its response. If the master was unavailable then we store the transaction until the master becomes available. At this point we apply any pending updates from the master. We then send our updates to the master an wait for a response. If the master detects a conflict between slave updates and pre applied updates then it refuses the update and sends a conflict message back to the originating slave, where it is up to the user to resolve manually. Standalone is a special version of master. It applies updates locally but remembers what the last update it received from the master was. When it is reconnected to the master the user must manually resolve conflicts and determine which, if any, of the updates should be applied to the master. When I left 14 months into the project we had the basic replication engine working but none of the conflict resolution stuff. Kevin Cowley RD Tel: 0118 902 9099 (direct line) Email: [EMAIL PROTECTED] Web: http://www.alchemetrics.co.uk The closest we got to this was having a master database in one place and read-only slaves in another. UPDATE commands were always sent to the master copy, and could not be done when the link was down. SELECTs were sent to the local slave and could therefore continue when the link was down. At the application level, we pipelined a few necessary but uncomplicated updates to be done when the link returned. Alec .com/[EMAIL PROTECTED] ** ALCHEMETRICS LIMITED (ALCHEMETRICS) Mulberry Park, Fishponds Road, Wokingham, Berkshire, RG41 2GX Tel: +44 (0) 118 902 9000Fax: +44 (0) 118 902 9001 This e-mail is confidential and is intended for the use of the addressee only. If you are not the intended recipient, you are hereby notified that you must not use, copy, disclose, otherwise disseminate or take any action based on this e-mail or any information herein. If you receive this transmission in error, please notify the sender immediately by reply e-mail or by using the contact details above and then delete this e-mail. Please note that e-mail may be susceptible to data corruption, interception and unauthorised amendment. Alchemetrics does not accept any liability for any such corruption, interception, amendment or the consequences thereof. ** -- 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: efficient query for: it's your birthday today
- Original Message - From: Jigal van Hemert [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, November 18, 2004 4:28 AM Subject: efficient query for: it's your birthday today I have date of birth stored in a DATETIME column and need to find for which persons a certain date is their birthday. I've tried so far: DATE_FORMAT (col, '%m%d') = '1107'= 0.2001 sec col LIKE '%-11-07%'= 0.1643 sec col RLIKE '-11-07'= 0.1702 sec Are there faster alternatives for MySQL 4.0.21 ? I don't know if it is more efficient but I would write this query as follows, simply because it is easier to understand what it is doing when you look at it: select [whatever columns you want] from mytable where month(birthdate) = 11 and dayofmonth(birthdate) = 7; Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question: Marking records
I'm not even sure what this would be called, but maybe someone (or more then one) can give me some pointers and where to learn how this is down: Someone does a search on my system: 1- I need a way to mark (somewhere) that the record came up in a search. i.e. 700 records were returned in a search, each one needs to a) be marked that it came up in a search b) that number needs to be incremented 2- Step further, out of those 700 records, user chooses to view details on 30 of them - those records now need to be marked as viewed, again , incrementing everytime they are viewed is needed. Sorry, if this is another lame question. I am not looking for the code, just some ideas how these things get implemented. Using 4.0.22 Thank you. Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: efficient query for: it's your birthday today
From: Brent Baisley [EMAIL PROTECTED] Without breaking the date up into it's separate parts, you can't use an index, so you will always do a full table scan. Your searches will get slower as you add more records. That was what I feared; I was just hoping that MySQL wouldn't treat the DATETIME column type as a variation of a string or an integer (with a set of functions to extract various parts of the datetime), but as a type with a special kind of indexing, etc. Searching for month + date or other parts of a datetime is pretty common and it would be useful to be able to do these kind of operations without storing the same data in more than one place. I don't know how many records you are searching on in your example, but if you have a lot, the difference is pretty minimal and may be due to slightly different loads on the computer. Although the DATE_FORMAT one has the extra overhead of formating every single record to do the comparison. The tests were made on a slow test server with about 10,000 records (if queries run fast on this server they'll be blazingly fast on the production machines ;-) ) Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Question: Marking records
[snip] 1- I need a way to mark (somewhere) that the record came up in a search. i.e. 700 records were returned in a search, each one needs to a) be marked that it came up in a search b) that number needs to be incremented 2- Step further, out of those 700 records, user chooses to view details on 30 of them - those records now need to be marked as viewed, again , incrementing everytime they are viewed is needed. [/snip] Additional tables to hold status information is in order. You would query those tables for their current counts and update as required. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: efficient query for: it's your birthday today
From: Jay Blanchard [EMAIL PROTECTED] So if I index a date field (given -mm-dd) and then SELECT `userNames` FROM `userTable` WHERE SUBSTRING(`userBirthDate`, 6, 5) = SUBSTRING(NOW(), 6, 5) it is still very fast. I have a table with several thousand records in it and I get back ... 30 rows in set (0.00 sec) on my test machine this variation takes approx. 0.18 seconds... Have you tried: SELECT SQL_NO_CACHE `userNames` FROM `userTable` WHERE SUBSTRING(`userBirthDate`, 6, 5) = SUBSTRING(NOW(), 6, 5) To prevent the use of the query cache? Otherwise you will get very fast results, just because the query is not executed at all, but the result is returned from the cache... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: **[SPAM]** Re: efficient query for: it's your birthday today
[snip] From: Jay Blanchard [EMAIL PROTECTED] So if I index a date field (given -mm-dd) and then SELECT `userNames` FROM `userTable` WHERE SUBSTRING(`userBirthDate`, 6, 5) = SUBSTRING(NOW(), 6, 5) it is still very fast. I have a table with several thousand records in it and I get back ... 30 rows in set (0.00 sec) on my test machine this variation takes approx. 0.18 seconds... Have you tried: SELECT SQL_NO_CACHE `userNames` FROM `userTable` WHERE SUBSTRING(`userBirthDate`, 6, 5) = SUBSTRING(NOW(), 6, 5) To prevent the use of the query cache? Otherwise you will get very fast results, just because the query is not executed at all, but the result is returned from the cache... [/snip] That first result was a non-cached query on a dual processor server. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server Won't Start Next record offset is nonsensical
Clif,
I have analyzed some of the hex dumps now:
First there was corruption in the buffer pool:
041001 21:44:51 InnoDB: Started
/usr/sbin/mysqld: ready for connections.
Version: '4.0.21-standard' socket: '/var/lib/mysql/mysql.sock' port: 3306
Official MySQL RPM
InnoDB: Next record offset is nonsensical 21360 in record at offset 10289
...
22b1
230c
2263
23ba
24a2
250e
0074
The hex addresses of the last records in the record chain look otherwise ok,
but note the odd one: 0x2263. If it were 0x2363, it would be fine.
Apparently, one bit has changed in memory.
After that, the ibdata file got corrupted. These are different pages:
041106 23:33:03 InnoDB: Page checksum 1633315220, prior-to-4.0.14-form
checksum 4288906249
InnoDB: stored checksum 505188979, prior-to-4.0.14-form stored checksum
4288906249
InnoDB: Page lsn 0 222902106, low 4 bytes of lsn at page end 222902106
InnoDB: Page may be an index page where index id is 0 36
InnoDB: (index `PRIMARY` of table `perfparse/perfdata_service_bin`)
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 6452.
041116 13:11:39 InnoDB: Page checksum 2281460337, prior-to-4.0.14-form
checksum 3049742901
InnoDB: stored checksum 853267344, prior-to-4.0.14-form stored checksum
3049742901
InnoDB: Page lsn 0 35147564, low 4 bytes of lsn at page end 35147564
Page checksum errors are almost certainly caused by a hardware fault or an
OS bug in file I/O. Since you suddenly get so many of them, I would suspect
a hardware fault.
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
- Alkuperäinen viesti -
Lähettäjä: Clif Smith [EMAIL PROTECTED]
Vastaanottaja: [EMAIL PROTECTED]
Lähetetty: Thursday, November 18, 2004 4:31 AM
Aihe: Re: Server Won't Start Next record offset is nonsensical
Yes, initially I had thought that there was an issue with the installation
itself,
so I upgraded to v4.1.7-0. I've attached the log.
Thanks, Clif
Cliff,
your OS or hardware has probably corrupted the ibdata file.
Next record offset is nonsensical 28769 in record at offset 7022
Before writing an index page to the file, InnoDB checks that offsets
are sensible ( 16 kB).
InnoDB: rec address 407b1b6e, first buffer frame 401c
InnoDB: buffer pool high end 409c, buf fix count 1
041116 17:17:10 InnoDB: Page dump in ascii and hex (16384 bytes):
len 16384; hex
large snip
;InnoDB: End of page dump
74 041116 17:17:10 InnoDB: Page checksum 3244520732,
prior-to-4.0.14-form checksum 1495873249
InnoDB: stored checksum 4145305205, prior-to-4.0.14-form stored
checksum 0
Hmm... the 'old checksum' at offsets 16 kB - 8 ... 16 kB - 4 is zero.
That is probably file corruption.
InnoDB: Resetting space id's in the doublewrite buffer
if (mach_read_from_4(doublewrite +
TRX_SYS_DOUBLEWRITE_SPACE_ID_STORED)
!= TRX_SYS_DOUBLEWRITE_SPACE_ID_STORED_N) {
/* We are upgrading from a version 4.1.x to a version
where
multiple tablespaces are supported. We must reset the
space id
field in the pages in the doublewrite buffer because
starting
from this version the space id is stored to
FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID. */
trx_doublewrite_must_reset_space_ids = TRUE;
fprintf(stderr,
InnoDB: Resetting space id's in the doublewrite buffer\n);
} else {
trx_sys_multiple_tablespace_format = TRUE;
}
The printout looks like you tried a downgrade and upgrade of MySQL to
resolve the crash? Is that true? Which 4.1.x version you are running?
Yes, initially I had thought that there was an issue with the
installation itself, so I upgraded to v4.1.7-0.
Please send the FULL .err log to me [EMAIL PROTECTED] for
more detailed analysis. Do not cut anything off.
Your lsn is only 300 MB. High-end users of InnoDB on a stable Linux
computer can easily reach 300 GB without any corruption.
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: Clif Smith [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Wednesday, November 17, 2004 4:53 AM
Subject: Server Won't Start Next record offset is nonsensical
Everything was fine...I haven't installed anything lately, etc. I've
got a Fedora FC1 system running MySQL v4. I noticed my db exports
failing this morning. The db wasn't running and now won't startup. I'm
googling but... Here's what's in the log:
41116 17:17:09 mysqld started
041116 17:17:09 [Warning] Asked for 196608 thread stack, but got 126976
041116 17:17:09 InnoDB:
Re: efficient query for: it's your birthday today
From: Rhino [EMAIL PROTECTED] I don't know if it is more efficient but I would write this query as follows, simply because it is easier to understand what it is doing when you look at it: select [whatever columns you want] from mytable where month(birthdate) = 11 and dayofmonth(birthdate) = 7; Sorry, same speed (approx 0.18 sec). This seems to be the speed at which a full tablescan can be performed on this recordset. I guess we have to make some adjustments before the table can grow to 500,000+ records... Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Question: Marking records
--- Jay Blanchard [EMAIL PROTECTED] wrote: [snip] 1- I need a way to mark (somewhere) that the record came up in a search. i.e. 700 records were returned in a search, each one needs to a) be marked that it came up in a search b) that number needs to be incremented 2- Step further, out of those 700 records, user chooses to view details on 30 of them - those records now need to be marked as viewed, again , incrementing everytime they are viewed is needed. [/snip] Additional tables to hold status information is in order. You would query those tables for their current counts and update as required. I'm thinking this through. In other words, when a record is returned in a search , and insert statement makes an entry into another table? This will involve scripting as well as sql statements ? Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Question: Marking records
[snip] I'm thinking this through. In other words, when a record is returned in a search , and insert statement makes an entry into another table? This will involve scripting as well as sql statements ? [/snip] Well, you could use triggers and stored procedures, but yeyou are going to have to do something to add the marks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: efficient query for: it's your birthday today
You don't have to replicate the data, and you shouldn't. Since you will be searching on parts of the data, you may want to store it in parts. Then you can create indexes to merge the data for searching. For instance, you use three fields for storage: year, month, day. The you can create two compound indexes, one on year+month+day and one on just month+day. Searching will then be very fast, regardless of whether you are searching on the full date or just the month and day. Now, if your table is only 10K records, that's pretty small and you probably won't see any performance difference using indexes since the table may be in cache. If the table is not going to get a lot bigger (i.e. 50K+ records), it may be easier just to make sure you have plenty of RAM in the machine. On Nov 18, 2004, at 11:14 AM, Jigal van Hemert wrote: From: Brent Baisley [EMAIL PROTECTED] Without breaking the date up into it's separate parts, you can't use an index, so you will always do a full table scan. Your searches will get slower as you add more records. That was what I feared; I was just hoping that MySQL wouldn't treat the DATETIME column type as a variation of a string or an integer (with a set of functions to extract various parts of the datetime), but as a type with a special kind of indexing, etc. Searching for month + date or other parts of a datetime is pretty common and it would be useful to be able to do these kind of operations without storing the same data in more than one place. I don't know how many records you are searching on in your example, but if you have a lot, the difference is pretty minimal and may be due to slightly different loads on the computer. Although the DATE_FORMAT one has the extra overhead of formating every single record to do the comparison. The tests were made on a slow test server with about 10,000 records (if queries run fast on this server they'll be blazingly fast on the production machines ;-) ) Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- 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: Question: Marking records
It depends on whether you are tracking info for individual users or just on a global basis. On a global basis, you just need to run an update query: UPDATE dbTable SET searchCount=searchCount+1 WHERE queryparams The default value for searchCount would be 0, if the record never came up in a search the searchCount is 0. I wouldn't just track viewed or not, you might as well track how many times it's been viewed (viewCount). It will give more data without much added cost. Now, if you want to keep separate logs for each user, you need to create a log table. The log table would have the following fields: relatedRecordID, userID, searchCount, viewCount When the user does a search, you first update the searchCount in the log table, something like: UPDATE logTable,mainTable SET searchCount=searchCount+1 WHERE queryparams AND logTable.relatedRecordID=mainTable.ID AND logTable.userID=### Then you add log entries, using INSERT SELECT, for records that the user has searched on for the first time. Something like: INSERT INTO logTable (relatedRecordID, userID, searchCount) SELECT mainTable.ID,userID,1 FROM mainTable WHERE queryparams Updating the log entry to track viewed, is of course just a simple update. UPDATE logTable SET viewCount=viewCount+1 WHERE relatedRecordID=### AND userID=### On Nov 18, 2004, at 11:14 AM, Stuart Felenstein wrote: I'm not even sure what this would be called, but maybe someone (or more then one) can give me some pointers and where to learn how this is down: Someone does a search on my system: 1- I need a way to mark (somewhere) that the record came up in a search. i.e. 700 records were returned in a search, each one needs to a) be marked that it came up in a search b) that number needs to be incremented 2- Step further, out of those 700 records, user chooses to view details on 30 of them - those records now need to be marked as viewed, again , incrementing everytime they are viewed is needed. Sorry, if this is another lame question. I am not looking for the code, just some ideas how these things get implemented. Using 4.0.22 Thank you. Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- 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]
error starting mysql 4.1.7 cannot create/write to /root/tmp
Hello I have just installed mysql 4.1.7 on Mandrake 10.0 (final). All went smoothly. However, when I issue the following command: /usr/local/mysql/bin/mysqld_safe --user=mysql I get the error: Can't create/write to file /root/tmp/ibLmfg7w (Errcode: 13) When I examine the error file in /usr/local/mysql/var it says it cannto read /root and it cannot write to /root/tmp If I give others (beside root) r permission to /root and rw permissions to /root/tmp, it start up fine. I have looked as startup options for the mysqld_safe as well as the /etc/my.cnf file but have not found anyway to alter where it wants to write its temp files. Can anyone please help? Thanks, Gail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question: Marking records
--- Brent Baisley [EMAIL PROTECTED] wrote: It depends on whether you are tracking info for individual users Individual users (lucky me!) Now, if you want to keep separate logs for each user, you need to create a log table. The log table would have the following fields: relatedRecordID, userID, searchCount, viewCount I just ran a search on Log Tables. I'm coming to the conclusion they are just tables , myisam or innodb. No different , except log is how they are used ? Is this a correct assumption ? Stuart -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: efficient query for: it's your birthday today
How about adding another column that stores the day of year for the birthday. You could then index on this column and your query would be for 11/7 would be doycol=DAYOFYEAR(2004-11-07) or doycol=312 E Jigal van Hemert [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I have date of birth stored in a DATETIME column and need to find for which persons a certain date is their birthday. I've tried so far: DATE_FORMAT (col, '%m%d') = '1107'= 0.2001 sec col LIKE '%-11-07%'= 0.1643 sec col RLIKE '-11-07'= 0.1702 sec Are there faster alternatives for MySQL 4.0.21 ? Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb data file grew beyond the specified max size in config
Hi all I converted my 6GB MyISAM database to Innodb using ALTER TABLE table TYPE = INNODB. There was no errors in the err log and database is working ok. But after stopping mysql server It refuse to start again complaining: 041118 16:55:45 mysqld started 041118 16:55:45 [Warning] Asked for 1048576 thread stack, but got 126976 InnoDB: Error: auto-extending data file /data/mysql_4.1_ibdata/ibdata1 is of a different size InnoDB: 779008 pages (rounded down to MB) than specified in the .cnf file: InnoDB: initial 32000 pages, max 128000 (relevant if non-zero) pages! InnoDB: Could not open or create data files. InnoDB: If you tried to add new data files, and it failed here, InnoDB: you should now edit innodb_data_file_path in my.cnf back InnoDB: to what it was, and remove the new ibdata files InnoDB created InnoDB: in this failed attempt. InnoDB only wrote those files full of InnoDB: zeros, but did not yet use them in any way. But be careful: do not InnoDB: remove old data files which contain your precious data! 041118 16:55:45 [ERROR] Can't init databases 041118 16:55:45 [ERROR] Aborting 041118 16:55:45 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete 041118 16:55:45 mysqld ended The server enlarged the initial tablespace file and made it 12GB regardless my settings of 2000M max for one ibdata file. It should split them into 2G pieces , correct? Is this a bug or I am missing something? I am running Mysql 4.1.7 on Linux Here is my config file: [mysqld] port= 3307 socket = /usr/local/mysql/mysql.sock pid-file= /usr/local/mysql/mysql.pid datadir = /data/mysql_4.1_data skip-locking key_buffer_size = 64M max_allowed_packet = 2M table_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 16M net_buffer_length = 2M thread_stack = 1M max_connections = 100 query_cache_type = 1 maximum-query_cache_size = 24M thread_cache = 8 thread_concurrency = 2 server-id = 1 innodb_data_home_dir = /data/mysql_4.1_ibdata/ innodb_data_file_path = ibdata1:500M:autoextend:max:2000M innodb_log_group_home_dir = /data/mysql_4.1_iblog/ innodb_log_arch_dir = /data/mysql_4.1_iblog/ innodb_buffer_pool_size = 64M innodb_additional_mem_pool_size = 8M innodb_log_file_size = 16M innodb_log_buffer_size = 4M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 -- Hristo Chernev --- ÁÅÇÏËÀÒÅÍ ëè÷åí ÔÀÊÑ íîìåð çà âñåêè àáîíàò íà Mail.bg http://mail.bg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: efficient query for: it's your birthday today
Good idea! But, that method will fail for dates past Feb 29th on leap
years.
MYSQLselect dayofyear('1999-03-01'), Dayofyear('2000-03-01');
+-+-+
| dayofyear('1999-03-01') | Dayofyear('2000-03-01') |
+-+-+
| 60 | 61 |
+-+-+
Sorry!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Eric McGrane [EMAIL PROTECTED] wrote on 11/18/2004 11:29:20 AM:
How about adding another column that stores the day of year for the
birthday. You could then index on this column and your query would be
for
11/7 would be
doycol=DAYOFYEAR(2004-11-07)
or
doycol=312
E
Jigal van Hemert [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
I have date of birth stored in a DATETIME column and need to find for
which
persons a certain date is their birthday.
I've tried so far:
DATE_FORMAT (col, '%m%d') = '1107'= 0.2001 sec
col LIKE '%-11-07%'= 0.1643 sec
col RLIKE '-11-07'= 0.1702 sec
Are there faster alternatives for MySQL 4.0.21 ?
Regards, Jigal.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
error starting mysql 4.1.7 cannot create/write to /root/tmp
Hello I have just installed mysql 4.1.7 on Mandrake 10.0 (final). All went smoothly. However, when I issue the following command: /usr/local/mysql/bin/mysqld_safe --user=mysql I get the error: Can't create/write to file /root/tmp/ibLmfg7w (Errcode: 13) When I examine the error file in /usr/local/mysql/var it says it cannto read /root and it cannot write to /root/tmp If I give others (beside root) r permission to /root and rw permissions to /root/tmp, it start up fine. I have looked as startup options for the mysqld_safe as well as the /etc/my.cnf file but have not found anyway to alter where it wants to write its temp files. Can anyone please help? Thanks, Gail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: efficient query for: it's your birthday today
Yup, very good point. I am the one who is sorry. My bad.
E
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 18, 2004 1:06 PM
To: McGrane, Eric
Cc: [EMAIL PROTECTED]
Subject: Re: efficient query for: it's your birthday today
Good idea! But, that method will fail for dates past Feb 29th on leap
years.
MYSQLselect dayofyear('1999-03-01'), Dayofyear('2000-03-01');
+-+-+
| dayofyear('1999-03-01') | Dayofyear('2000-03-01') |
+-+-+
| 60 | 61 |
+-+-+
Sorry!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Eric McGrane [EMAIL PROTECTED] wrote on 11/18/2004 11:29:20 AM:
How about adding another column that stores the day of year for the
birthday. You could then index on this column and your query would be
for
11/7 would be
doycol=DAYOFYEAR(2004-11-07)
or
doycol=312
E
Jigal van Hemert [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
I have date of birth stored in a DATETIME column and need to find
for
which
persons a certain date is their birthday.
I've tried so far:
DATE_FORMAT (col, '%m%d') = '1107'= 0.2001 sec
col LIKE '%-11-07%'= 0.1643 sec
col RLIKE '-11-07'= 0.1702 sec
Are there faster alternatives for MySQL 4.0.21 ?
Regards, Jigal.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
RE: using IN()
Although parentUserId is indexed, the fact that you're using an IN list turns the query into a range. IN lists are fast but at certain levels such as yours it is not. The reasons are listed below: The query parser must allocate memory for every string in the list and convert it into an int. So your using prob a few megs of memory to parse the IN list. Next the query optimizer notices that the IN list is very large, thus for efficiency it must perform passes or a range across the binary tree. If this range covers more then 30% of your table, the optimizer will determine that a full table scan is faster and will not use the index. To improve throughput: Put the 60K ints into a temporary table. Next join against this temporary table. This join forces the optimizer to translate the range into an eq_ref-one of the fastest type joins. DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: Mitul Bhammar [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 17, 2004 9:22 PM To: [EMAIL PROTECTED] Subject: using IN() I've a bunch of IDs fetched(around 60,000) from a DB. I'm using these IDs to fetch data from another DB having a related fields in its tables. I'm using IN clause for it. i.e. for e.g. SELECT * FROM site_users WHERE parentUserId IN (1,2,3,4) Again here parentUserId is Indexed. The query is running fine for now. I wanted to know how MySQL interprets and executes this query and can it have problems in future if number exceeds 60,000?? __ Do you Yahoo!? The all-new My Yahoo! - Get yours free! http://my.yahoo.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: innodb data file grew beyond the specified max size in config
When ever you use INNODB it must create a table space, something like a virtual file space or system for the data, that sits on top of the OS filesystem. By default it has allocated a certain size, change the value of innodb_data_file_path = ibdata1:500M:autoextend:max:2000M 500M back to the original size. The data will grow as large as the filesystem will allow it since the data is autoextended. Innodb datafiles contain the index and the data in one file for 4.0.x. This means that data will be the size of the columns + the indexes + some padding per row. DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: Hristo Chernev [mailto:[EMAIL PROTECTED] Sent: Thursday, November 18, 2004 9:14 AM To: [EMAIL PROTECTED] Subject: innodb data file grew beyond the specified max size in config Hi all I converted my 6GB MyISAM database to Innodb using ALTER TABLE table TYPE = INNODB. There was no errors in the err log and database is working ok. But after stopping mysql server It refuse to start again complaining: 041118 16:55:45 mysqld started 041118 16:55:45 [Warning] Asked for 1048576 thread stack, but got 126976 InnoDB: Error: auto-extending data file /data/mysql_4.1_ibdata/ibdata1 is of a different size InnoDB: 779008 pages (rounded down to MB) than specified in the .cnf file: InnoDB: initial 32000 pages, max 128000 (relevant if non-zero) pages! InnoDB: Could not open or create data files. InnoDB: If you tried to add new data files, and it failed here, InnoDB: you should now edit innodb_data_file_path in my.cnf back InnoDB: to what it was, and remove the new ibdata files InnoDB created InnoDB: in this failed attempt. InnoDB only wrote those files full of InnoDB: zeros, but did not yet use them in any way. But be careful: do not InnoDB: remove old data files which contain your precious data! 041118 16:55:45 [ERROR] Can't init databases 041118 16:55:45 [ERROR] Aborting 041118 16:55:45 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete 041118 16:55:45 mysqld ended The server enlarged the initial tablespace file and made it 12GB regardless my settings of 2000M max for one ibdata file. It should split them into 2G pieces , correct? Is this a bug or I am missing something? I am running Mysql 4.1.7 on Linux Here is my config file: [mysqld] port= 3307 socket = /usr/local/mysql/mysql.sock pid-file= /usr/local/mysql/mysql.pid datadir = /data/mysql_4.1_data skip-locking key_buffer_size = 64M max_allowed_packet = 2M table_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 16M net_buffer_length = 2M thread_stack = 1M max_connections = 100 query_cache_type = 1 maximum-query_cache_size = 24M thread_cache = 8 thread_concurrency = 2 server-id = 1 innodb_data_home_dir = /data/mysql_4.1_ibdata/ innodb_data_file_path = ibdata1:500M:autoextend:max:2000M innodb_log_group_home_dir = /data/mysql_4.1_iblog/ innodb_log_arch_dir = /data/mysql_4.1_iblog/ innodb_buffer_pool_size = 64M innodb_additional_mem_pool_size = 8M innodb_log_file_size = 16M innodb_log_buffer_size = 4M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 -- Hristo Chernev --- ÁÅÇÏËÀÒÅÍ ëè÷åí ÔÀÊÑ íîìåð çà âñåêè àáîíàò íà Mail.bg http://mail.bg -- 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: error starting mysql 4.1.7 cannot create/write to /root/tmp
perror 13 System error: 13 = Permission denied The mysql user cannot read the /root filesystem. DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: Gail Lange [mailto:[EMAIL PROTECTED] Sent: Thursday, November 18, 2004 10:23 AM To: [EMAIL PROTECTED] Subject: error starting mysql 4.1.7 cannot create/write to /root/tmp Hello I have just installed mysql 4.1.7 on Mandrake 10.0 (final). All went smoothly. However, when I issue the following command: /usr/local/mysql/bin/mysqld_safe --user=mysql I get the error: Can't create/write to file /root/tmp/ibLmfg7w (Errcode: 13) When I examine the error file in /usr/local/mysql/var it says it cannto read /root and it cannot write to /root/tmp If I give others (beside root) r permission to /root and rw permissions to /root/tmp, it start up fine. I have looked as startup options for the mysqld_safe as well as the /etc/my.cnf file but have not found anyway to alter where it wants to write its temp files. Can anyone please help? Thanks, Gail -- 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]
Optimizing MySQL
Hello, I was wondering if a more knowledgeable person could help me out with my configuration and let me know how I could further optimize MySQL. Here's the hardware on my dedicated server: Processor #1 Vendor: GenuineIntel Processor #1 Name: Intel(R) Pentium(R) 4 CPU 2.40GHz Processor #1 speed: 2400.152 MHz Processor #1 cache size: 1024 KB Memory 512 MB Here's what I get for the command SHOW STATUS: Aborted_clients247 Aborted_connects483 Bytes_received 531539854 Bytes_sent 503095410 Connections 450758 Created_tmp_disk_tables 14768 Created_tmp_tables 269520 Created_tmp_files 3 Delayed_insert_threads 0 Delayed_writes 0 Delayed_errors 0 Flush_commands 1 Handler_commit 0 Handler_delete 27837 Handler_read_first 670529 Handler_read_key 285579436 Handler_read_next 394084433 Handler_read_prev 680815 Handler_read_rnd5230552 Handler_read_rnd_next 58229817 Handler_rollback 0 Handler_update 384098 Handler_write 77442968 Key_blocks_used 50333 Key_read_requests 1081940322 Key_reads 45598 Key_write_requests 66458416 Key_writes 41372551 Max_used_connections 154 Not_flushed_key_blocks 0 Not_flushed_delayed_rows 0 Open_tables256 Open_files 323 Open_streams 0 Opened_tables 1022 Questions 30428972 Qcache_queries_in_cache0 Qcache_inserts0 Qcache_hits 0 Qcache_lowmem_prunes0 Qcache_not_cached 0 Qcache_free_memory 0 Qcache_free_blocks 0 Qcache_total_blocks 0 Rpl_status NULL Select_full_join 268 Select_full_range_join0 Select_range 66211 Select_range_check 0 Select_scan 151459 Slave_open_temp_tables 0 Slave_running OFF Slow_launch_threads 2 Slow_queries 15783 Sort_merge_passes 0 Sort_range 476962 Sort_rows 5241809 Sort_scan 283556 Table_locks_immediate31443397 Table_locks_waited 20243 Threads_cached 4 Threads_created 2423 Threads_connected5 Threads_running 1 Uptime 771502 Here are the complete contents of my my.cnf file [mysqld] skip-locking set-variable = key_buffer=256M set-variable = max_allowed_packet=1M set-variable = table_cache=256 set-variable = sort_buffer=1M set-variable = record_buffer=1M set-variable = myisam_sort_buffer_size=64M set-variable = thread_cache=8 set-variable = max_connections=500 set-variable = ft_min_word_len=1 log-slow-queries=/var/log/slow-queries.log set-variable = long_query_time=1 safe-show-database Thanks a lot for your help! http://www.hdtv-info.org http://www.entertainment-news.org http://www.political-news.org
Re: Question: Marking records
Sorry, logTable is just an arbitrary name I came up with. You can name the table anything you want, like SearchViewTracking. On Nov 18, 2004, at 12:26 PM, Stuart Felenstein wrote: --- Brent Baisley [EMAIL PROTECTED] wrote: It depends on whether you are tracking info for individual users Individual users (lucky me!) Now, if you want to keep separate logs for each user, you need to create a log table. The log table would have the following fields: relatedRecordID, userID, searchCount, viewCount I just ran a search on Log Tables. I'm coming to the conclusion they are just tables , myisam or innodb. No different , except log is how they are used ? Is this a correct assumption ? Stuart -- 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: 4.1.7 serious problems
It can be even simpler than that, if you stay withing the same branch. Just replace mysqld and share/english/errmsg.sys ( or share/your langauge/errmsg.sys if you want them in your native language) with the files from the new version. This is for the source, binary or both? Sorry to ask stupid questions, but I do have read 3 MySQL books (including yours), and went through the manual, and I've never really seen a section about updating MySQL. It does not matter. mysqld is the only file in the server that really does the job - mysqld_safe is just a wrapper and if it does change from version to version at all, using the old version does not hurt. mysqld does need to see the error messages file on startup and the error messages file gets changed sometimes from version to version. mysqld does know how many error messages it is supposed to have, and will not start unless the error messages file has the expected number. Otherwise, if you are within the same version, mysqld will be able to deal with all the files from the old one. Hmmm, ok. Do you think I should file a bug? Lenz is already looking at it. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: long update query does not replicate correctly (cont.)
Przemyslaw Popielarski wrote: Sasha Pachev [EMAIL PROTECTED] wrote: Check if you have any replication restricting rules on the slave. There might be a bug that incorrectly flags a query to be excluded. If that is the case, then try to re-write the rules to see if you can get around the bug. Yes. I have recently add the following line to the slave's my.cnf file: replicate-wild-do-table=abe_exp.% There are no more replication rules in that file. Both tables included in the multitable update were in this database (abe_exp). So this is a bug. I suggest then that you file a bug report at http://bugs.mysql.com -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Solaris 10 performance improvements??
Shankar, Thanks for the input. I understand the route you suggest, but it doesn't get at the heart of my issue. The info I'm interested in isn't really about my particular app performance. I'm looking for issues in regards to how the MySQL code executes against Solaris vs. Linux. I have read in the past that roots of certain performance issues on Solaris/MySQL had been identified (such as different thread models and thread creation time: Solaris v. Linux, etc...). The kind of input I'm looking for is: Does anyone know if Solaris 10 has made any changes to the things that were identified to be dragging MySQL on Solaris? Since I'm also studying freeBSD 5.3, I would also like to know if anyone has ideas on this as well. IOW, has freeBSD 5.3 fixed/changed things that in previous versions were a cause of slowness in MySQL. thanks, Jon Jon: The main reason MySQL did not do as well on Solaris as it did on Linux in the past was that Linux cached more aggressively, and MyISAM tables heavily depend of filesystem cache. I do know know if Solaris 10 solved that problem. I would recommend that you install Solaris 10 and do a benchmark. You may need to do some special tuning of Solaris to make it cache better. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: copy data only from one table to another table
- Original Message - From: [EMAIL PROTECTED] To: Jim McAtee [EMAIL PROTECTED] Cc: MySQL List [EMAIL PROTECTED] Sent: Thursday, November 18, 2004 8:28 AM Subject: Re: copy data only from one table to another table A semi-generic solution: You have tableA with columns (col1, col2, col3, ..., colN) where col1 is an auto_increment column. You have tableB that looks just like tableA except for some additional columns (extra1, extra2, col1, col2, col3, ..., colN, extra3, extra4). TableB is intended to act as an archive table for several tables of identically organized information. for tableB the extra columns contain: extra1 - the name of the table where these rows are coming from extra2 - a new auto_increment id extra3 - a datetime column to show when the rows were copied. extra4 - initially null (BACKGROUND: This example is based on a situation that was once involved with. Each tableA contained billing information for different categories of clientel. The application they were designing was not responding quickly enough with one consolidated table of data so the decision was made to split it by category into separate tables. That decision improved their performance but created an administrative load they had not anticipated and they hired me to help work it out.) Now if you want to copy records from tableA to tableB you write an INSERT...SELECT... statement that would look like this: INSERT tableB (extra1, extra3, col1, col2, col3, ..., colN) SELECT 'tableA', NOW(), col1, col2, col3, ..., colN FROM tableA WHERE (some appropriate conditions)... Each value (column or literal) in the SELECT clause lines up one-to-one with each column listed in the INSERT () clause. A more trivial solution exists when the tables are virtually identical except you want the moved records to be autonumbered when they get to the new table. In this situation tableB has identical columns to tableA. For both tables col1 is an auto_increment column. To move records from tableA to tableB so that they get autonumbered by tableB you need to write a statement like this INSERT tableB (col2, col3, col4, ..., colN) SELECT col2, col3, col4, ..., colN FROM tableA WHERE ...( some appropriate conditions)... In both examples, we had to declare ALL of the source columns/values and the destination columns (cannot use *) because we needed to specifically exclude the auto_increment columns from the effects of the statement. Does that make sense or just make it worse? That's exactly what I needed. Thank you for the detailed explanation. It makes perfect sense. Jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb data file grew beyond the specified max size in config
Hristo, thank you for the bug report. I broke the :autoextend:max:2000M feature when I added multiple tablespaces in 4.1.1. The bug is fixed in 4.1.8. Unfortunately, InnoDB does not automatically add 2000 MB files if you specify the max. You have to add additional ibdata files manually, as explained in tne manual. 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 .. Hi all I converted my 6GB MyISAM database to Innodb using ALTER TABLE table TYPE = INNODB. There was no errors in the err log and database is working ok. But after stopping mysql server It refuse to start again complaining: 041118 16:55:45 mysqld started 041118 16:55:45 [Warning] Asked for 1048576 thread stack, but got 126976 InnoDB: Error: auto-extending data file /data/mysql_4.1_ibdata/ibdata1 is of a different size InnoDB: 779008 pages (rounded down to MB) than specified in the .cnf file: InnoDB: initial 32000 pages, max 128000 (relevant if non-zero) pages! InnoDB: Could not open or create data files. InnoDB: If you tried to add new data files, and it failed here, InnoDB: you should now edit innodb_data_file_path in my.cnf back InnoDB: to what it was, and remove the new ibdata files InnoDB created InnoDB: in this failed attempt. InnoDB only wrote those files full of InnoDB: zeros, but did not yet use them in any way. But be careful: do not InnoDB: remove old data files which contain your precious data! 041118 16:55:45 [ERROR] Can't init databases 041118 16:55:45 [ERROR] Aborting 041118 16:55:45 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete 041118 16:55:45 mysqld ended The server enlarged the initial tablespace file and made it 12GB regardless my settings of 2000M max for one ibdata file. It should split them into 2G pieces , correct? Is this a bug or I am missing something? I am running Mysql 4.1.7 on Linux Here is my config file: [mysqld] port= 3307 socket = /usr/local/mysql/mysql.sock pid-file= /usr/local/mysql/mysql.pid datadir = /data/mysql_4.1_data skip-locking key_buffer_size = 64M max_allowed_packet = 2M table_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 16M net_buffer_length = 2M thread_stack = 1M max_connections = 100 query_cache_type = 1 maximum-query_cache_size = 24M thread_cache = 8 thread_concurrency = 2 server-id = 1 innodb_data_home_dir = /data/mysql_4.1_ibdata/ innodb_data_file_path = ibdata1:500M:autoextend:max:2000M innodb_log_group_home_dir = /data/mysql_4.1_iblog/ innodb_log_arch_dir = /data/mysql_4.1_iblog/ innodb_buffer_pool_size = 64M innodb_additional_mem_pool_size = 8M innodb_log_file_size = 16M innodb_log_buffer_size = 4M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 -- Hristo Chernev --- ÁÅÇÏËÀÒÅÍ ëè÷åí ÔÀÊÑ íîìåð çà âñåêè àáîíàò íà Mail.bg http://mail.bg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1.7 serious problems
Sasha Pachev wrote: It can be even simpler than that, if you stay withing the same branch. Just replace mysqld and share/english/errmsg.sys ( or share/your langauge/errmsg.sys if you want them in your native language) with the files from the new version. This is for the source, binary or both? Sorry to ask stupid questions, but I do have read 3 MySQL books (including yours), and went through the manual, and I've never really seen a section about updating MySQL. It does not matter. mysqld is the only file in the server that really does the job - mysqld_safe is just a wrapper and if it does change from version to version at all, using the old version does not hurt. mysqld does need to see the error messages file on startup and the error messages file gets changed sometimes from version to version. mysqld does know how many error messages it is supposed to have, and will not start unless the error messages file has the expected number. Otherwise, if you are within the same version, mysqld will be able to deal with all the files from the old one. So all I have to do, using the binary, is to take the mysqld file and replace my old one (from 4.1.3 to 4.1.7, for example). And for the source, I compile in another directory, then take the mysqld file and replace my old one? Is that documented somewhere? Hmmm, ok. Do you think I should file a bug? Lenz is already looking at it. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Optimizing MySQL
Look at Created_tmp_disk_tables 14768 Created_tmp_tables 269520 Created_tmp_files 3 Increase tmp_table_size = 64M: it's used to stop going to disk and some internal mysql operations. Handler_read_rnd_next 58229817 Your tables are not index properly, your doing a lot of table scans. Your biggest perf. Gain will come from changing your schema and or optimizing your queries. DVP Dathan Vance Pattishall http://www.friendster.com -Original Message- From: Shaun [mailto:[EMAIL PROTECTED] Sent: Thursday, November 18, 2004 10:56 AM To: [EMAIL PROTECTED] Subject: Optimizing MySQL Hello, I was wondering if a more knowledgeable person could help me out with my configuration and let me know how I could further optimize MySQL. Here's the hardware on my dedicated server: Processor #1 Vendor: GenuineIntel Processor #1 Name: Intel(R) Pentium(R) 4 CPU 2.40GHz Processor #1 speed: 2400.152 MHz Processor #1 cache size: 1024 KB Memory 512 MB Here's what I get for the command SHOW STATUS: Aborted_clients247 Aborted_connects483 Bytes_received 531539854 Bytes_sent 503095410 Connections 450758 Created_tmp_disk_tables 14768 Created_tmp_tables 269520 Created_tmp_files 3 Delayed_insert_threads 0 Delayed_writes 0 Delayed_errors 0 Flush_commands 1 Handler_commit 0 Handler_delete 27837 Handler_read_first 670529 Handler_read_key 285579436 Handler_read_next 394084433 Handler_read_prev 680815 Handler_read_rnd5230552 Handler_read_rnd_next 58229817 Handler_rollback 0 Handler_update 384098 Handler_write 77442968 Key_blocks_used 50333 Key_read_requests 1081940322 Key_reads 45598 Key_write_requests 66458416 Key_writes 41372551 Max_used_connections 154 Not_flushed_key_blocks 0 Not_flushed_delayed_rows 0 Open_tables256 Open_files 323 Open_streams 0 Opened_tables 1022 Questions 30428972 Qcache_queries_in_cache0 Qcache_inserts0 Qcache_hits 0 Qcache_lowmem_prunes0 Qcache_not_cached 0 Qcache_free_memory 0 Qcache_free_blocks 0 Qcache_total_blocks 0 Rpl_status NULL Select_full_join 268 Select_full_range_join0 Select_range 66211 Select_range_check 0 Select_scan 151459 Slave_open_temp_tables 0 Slave_running OFF Slow_launch_threads 2 Slow_queries 15783 Sort_merge_passes 0 Sort_range 476962 Sort_rows 5241809 Sort_scan 283556 Table_locks_immediate31443397 Table_locks_waited 20243 Threads_cached 4 Threads_created 2423 Threads_connected5 Threads_running 1 Uptime 771502 Here are the complete contents of my my.cnf file [mysqld] skip-locking set-variable = key_buffer=256M set-variable = max_allowed_packet=1M set-variable = table_cache=256 set-variable = sort_buffer=1M set-variable = record_buffer=1M set-variable = myisam_sort_buffer_size=64M set-variable = thread_cache=8 set-variable = max_connections=500 set-variable = ft_min_word_len=1 log-slow-queries=/var/log/slow-queries.log set-variable = long_query_time=1 safe-show-database Thanks a lot for your help! http://www.hdtv-info.org http://www.entertainment-news.org http://www.political-news.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Possible bug in 4.1.7
Hello, I have a query that works fine on 4.0.20 but doesn't work in 4.1.7. I want to search all the rows of table AULE that don't have a record in table OCCUPAZIONI so the query is: select AUL_ID, OCC_ID from AULE left join OCCUPAZIONI on OCC_ID_AUL=AUL_ID and OCC_DATA='2004-11-10' where OCC_ID is null; After some tests I find that an index changes the results. This is a sql command file that reproduce the problem on my Linux Fedora core 1 with MySql 4.1.7 : mysql select version(); ++ | version() | ++ | 4.1.7-standard | ++ 1 row in set (0.00 sec) ==CUT DROP DATABASE IF EXISTS bug; create database bug; use bug; CREATE TABLE AULE ( AUL_ID int(11) NOT NULL auto_increment, PRIMARY KEY (AUL_ID) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; CREATE TABLE OCCUPAZIONI ( OCC_ID int(11) NOT NULL auto_increment, OCC_ID_AUL int(11) NOT NULL, OCC_DATA date, PRIMARY KEY (OCC_ID) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO AULE VALUES (1); INSERT INTO AULE VALUES (2); INSERT INTO AULE VALUES (3); INSERT INTO OCCUPAZIONI VALUES (1, 1, '2004-11-10'); select Before index; select AUL_ID, OCC_ID from AULE left join OCCUPAZIONI on OCC_ID_AUL=AUL_ID and OCC_DATA='2004-11-10' where OCC_ID is null; alter table OCCUPAZIONI add KEY OCC_ID_AUL (OCC_ID_AUL); select After Index; select AUL_ID, OCC_ID from AULE left join OCCUPAZIONI on OCC_ID_AUL=AUL_ID and OCC_DATA='2004-11-10' where OCC_ID is null; ==CUT Results: [EMAIL PROTECTED] bugs]# mysql v.sql Before index Before index AUL_ID OCC_ID 2 NULL 3 NULL After Index After Index [EMAIL PROTECTED] bugs]# Is it a bug? Santino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimizing MySQL
Shaun wrote: Hello, I was wondering if a more knowledgeable person could help me out with my configuration and let me know how I could further optimize MySQL. Here's the hardware on my dedicated server: Processor #1 Vendor: GenuineIntel Processor #1 Name: Intel(R) Pentium(R) 4 CPU 2.40GHz Processor #1 speed: 2400.152 MHz Processor #1 cache size: 1024 KB Memory 512 MB Here's what I get for the command SHOW STATUS: Shaun: Once every 5 seconds on average you run a select that does a full scan which on average examines about 350-400 rows. Once every 2 seconds or so you have a query that creates a temporary table. Enable log-long-format and try to figure out what those queries are. See if you can optimize the ones that are scanning a lot of rows by adding a key, or by convincing your developers to re-write them. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: efficient query for: it's your birthday today
Store month*100+day
1999-03-01 -- 301
you can also say it's your birthday today when 02/29 doesn't exists!
Santino
At 13:05 -0500 18-11-2004, [EMAIL PROTECTED] wrote:
Good idea! But, that method will fail for dates past Feb 29th on leap
years.
MYSQLselect dayofyear('1999-03-01'), Dayofyear('2000-03-01');
+-+-+
| dayofyear('1999-03-01') | Dayofyear('2000-03-01') |
+-+-+
| 60 | 61 |
+-+-+
Sorry!
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Eric McGrane [EMAIL PROTECTED] wrote on 11/18/2004 11:29:20 AM:
How about adding another column that stores the day of year for the
birthday. You could then index on this column and your query would be
for
11/7 would be
doycol=DAYOFYEAR(2004-11-07)
or
doycol=312
E
Jigal van Hemert [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
I have date of birth stored in a DATETIME column and need to find for
which
persons a certain date is their birthday.
I've tried so far:
DATE_FORMAT (col, '%m%d') = '1107'= 0.2001 sec
col LIKE '%-11-07%'= 0.1643 sec
col RLIKE '-11-07'= 0.1702 sec
Are there faster alternatives for MySQL 4.0.21 ?
Regards, Jigal.
--
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: Optimizing MySQL
Thanks for your help Dathan, I will make the required changes. I just have one other problem. I'm not sure what queries don't use an index. I've attmepted to turn on the slow-queries-log, but nothing ever shows up in the file. Here's what I have in the my.cnf [mysqld] skip-locking set-variable = key_buffer=256M set-variable = max_allowed_packet=1M set-variable = table_cache=256 set-variable = sort_buffer=1M set-variable = record_buffer=1M set-variable = myisam_sort_buffer_size=64M set-variable = thread_cache=8 set-variable = max_connections=500 set-variable = ft_min_word_len=1 long_query_time=1 log-long-format log-slow-queries = /var/log/slow.log Do you have any idea why my slow query log isn't working? Do I have something wrong in my syntax? I'm using MySQL 4.0. Thanks for your help, I really appreciate it. http://www.hdtv-info.org http://www.entertainment-news.org http://www.political-news.org Look at Created_tmp_disk_tables 14768 Created_tmp_tables 269520 Created_tmp_files 3 Increase tmp_table_size = 64M: it's used to stop going to disk and some internal mysql operations. Handler_read_rnd_next 58229817 Your tables are not index properly, your doing a lot of table scans. Your biggest perf. Gain will come from changing your schema and or optimizing your queries. DVP Dathan Vance Pattishall http://www.friendster.com Hello, I was wondering if a more knowledgeable person could help me out with my configuration and let me know how I could further optimize MySQL. Here's the hardware on my dedicated server: Processor #1 Vendor: GenuineIntel Processor #1 Name: Intel(R) Pentium(R) 4 CPU 2.40GHz Processor #1 speed: 2400.152 MHz Processor #1 cache size: 1024 KB Memory 512 MB Here's what I get for the command SHOW STATUS: Aborted_clients247 Aborted_connects483 Bytes_received 531539854 Bytes_sent 503095410 Connections 450758 Created_tmp_disk_tables 14768 Created_tmp_tables 269520 Created_tmp_files 3 Delayed_insert_threads 0 Delayed_writes 0 Delayed_errors 0 Flush_commands 1 Handler_commit 0 Handler_delete 27837 Handler_read_first 670529 Handler_read_key 285579436 Handler_read_next 394084433 Handler_read_prev 680815 Handler_read_rnd5230552 Handler_read_rnd_next 58229817 Handler_rollback 0 Handler_update 384098 Handler_write 77442968 Key_blocks_used 50333 Key_read_requests 1081940322 Key_reads 45598 Key_write_requests 66458416 Key_writes 41372551 Max_used_connections 154 Not_flushed_key_blocks 0 Not_flushed_delayed_rows 0 Open_tables256 Open_files 323 Open_streams 0 Opened_tables 1022 Questions 30428972 Qcache_queries_in_cache0 Qcache_inserts0 Qcache_hits 0 Qcache_lowmem_prunes0 Qcache_not_cached 0 Qcache_free_memory 0 Qcache_free_blocks 0 Qcache_total_blocks 0 Rpl_status NULL Select_full_join 268 Select_full_range_join0 Select_range 66211 Select_range_check 0 Select_scan 151459 Slave_open_temp_tables 0 Slave_running OFF Slow_launch_threads 2 Slow_queries 15783 Sort_merge_passes 0 Sort_range 476962 Sort_rows 5241809 Sort_scan 283556 Table_locks_immediate31443397 Table_locks_waited 20243 Threads_cached 4 Threads_created 2423 Threads_connected5 Threads_running 1 Uptime 771502 Here are the complete contents of my my.cnf file [mysqld] skip-locking set-variable = key_buffer=256M set-variable = max_allowed_packet=1M set-variable = table_cache=256 set-variable = sort_buffer=1M set-variable = record_buffer=1M set-variable = myisam_sort_buffer_size=64M set-variable = thread_cache=8 set-variable = max_connections=500 set-variable = ft_min_word_len=1 log-slow-queries=/var/log/slow-queries.log set-variable = long_query_time=1 safe-show-database Thanks a lot for
Re: Optimizing MySQL
Shaun wrote: Thanks for your help Dathan, I will make the required changes. I just have one other problem. I'm not sure what queries don't use an index. I've attmepted to turn on the slow-queries-log, but nothing ever shows up in the file. Here's what I have in the my.cnf [mysqld] skip-locking set-variable = key_buffer=256M set-variable = max_allowed_packet=1M set-variable = table_cache=256 set-variable = sort_buffer=1M set-variable = record_buffer=1M set-variable = myisam_sort_buffer_size=64M set-variable = thread_cache=8 set-variable = max_connections=500 set-variable = ft_min_word_len=1 long_query_time=1 log-long-format log-slow-queries = /var/log/slow.log Do you have any idea why my slow query log isn't working? Can the mysql user write to this file? --log-long-format should show you queries that don't use an index. Do I have something wrong in my syntax? I'm using MySQL 4.0. Thanks for your help, I really appreciate it. http://www.hdtv-info.org http://www.entertainment-news.org http://www.political-news.org Look at Created_tmp_disk_tables 14768 Created_tmp_tables 269520 Created_tmp_files 3 Increase tmp_table_size = 64M: it's used to stop going to disk and some internal mysql operations. Handler_read_rnd_next 58229817 Your tables are not index properly, your doing a lot of table scans. Your biggest perf. Gain will come from changing your schema and or optimizing your queries. DVP Dathan Vance Pattishall http://www.friendster.com Hello, I was wondering if a more knowledgeable person could help me out with my configuration and let me know how I could further optimize MySQL. Here's the hardware on my dedicated server: Processor #1 Vendor: GenuineIntel Processor #1 Name: Intel(R) Pentium(R) 4 CPU 2.40GHz Processor #1 speed: 2400.152 MHz Processor #1 cache size: 1024 KB Memory 512 MB Here's what I get for the command SHOW STATUS: Aborted_clients247 Aborted_connects483 Bytes_received 531539854 Bytes_sent 503095410 Connections 450758 Created_tmp_disk_tables 14768 Created_tmp_tables 269520 Created_tmp_files 3 Delayed_insert_threads 0 Delayed_writes 0 Delayed_errors 0 Flush_commands 1 Handler_commit 0 Handler_delete 27837 Handler_read_first 670529 Handler_read_key 285579436 Handler_read_next 394084433 Handler_read_prev 680815 Handler_read_rnd5230552 Handler_read_rnd_next 58229817 Handler_rollback 0 Handler_update 384098 Handler_write 77442968 Key_blocks_used 50333 Key_read_requests 1081940322 Key_reads 45598 Key_write_requests 66458416 Key_writes 41372551 Max_used_connections 154 Not_flushed_key_blocks 0 Not_flushed_delayed_rows 0 Open_tables256 Open_files 323 Open_streams 0 Opened_tables 1022 Questions 30428972 Qcache_queries_in_cache0 Qcache_inserts0 Qcache_hits 0 Qcache_lowmem_prunes0 Qcache_not_cached 0 Qcache_free_memory 0 Qcache_free_blocks 0 Qcache_total_blocks 0 Rpl_status NULL Select_full_join 268 Select_full_range_join0 Select_range 66211 Select_range_check 0 Select_scan 151459 Slave_open_temp_tables 0 Slave_running OFF Slow_launch_threads 2 Slow_queries 15783 Sort_merge_passes 0 Sort_range 476962 Sort_rows 5241809 Sort_scan 283556 Table_locks_immediate31443397 Table_locks_waited 20243 Threads_cached 4 Threads_created 2423 Threads_connected5 Threads_running 1 Uptime 771502 Here are the complete contents of my my.cnf file [mysqld] skip-locking set-variable = key_buffer=256M set-variable = max_allowed_packet=1M set-variable = table_cache=256 set-variable = sort_buffer=1M set-variable = record_buffer=1M set-variable = myisam_sort_buffer_size=64M set-variable = thread_cache=8 set-variable = max_connections=500 set-variable = ft_min_word_len=1 log-slow-queries=/var/log/slow-queries.log set-variable = long_query_time=1 safe-show-database Thanks a lot for your help! http://www.hdtv-info.org http://www.entertainment-news.org http://www.political-news.org -- MySQL
Re: 4.1.7 serious problems
So all I have to do, using the binary, is to take the mysqld file and replace my old one (from 4.1.3 to 4.1.7, for example). And for the source, I compile in another directory, then take the mysqld file and replace my old one? Is that documented somewhere? Do not forget to replace errmsg.sys with the newer version. This method does not seem to be documented anywhere, but I have successfully used it since early 3.23. Here is another trick you can do if you want to benchmark a certain mysqld binary on some random system where you have an account: scp sql/mysqld sql/share/english/errmsg.sys host: ssh host mkdir mysql-data ./mysqld --skip-grant --skip-net --datadir=`pwd`/mysql-data --language=`pwd` --socket=`pwd`/mysql-data/mysql-test.sock you can now connect on socket `pwd`/mysql-data/mysql-test.sock and issue queries. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimizing MySQL
Thanks, I changed the permissions, restarted, and everything is working great now. Thanks a lot. Shaun wrote: Thanks for your help Dathan, I will make the required changes. I just have one other problem. I'm not sure what queries don't use an index. I've attmepted to turn on the slow-queries-log, but nothing ever shows up in the file. Here's what I have in the my.cnf [mysqld] skip-locking set-variable = key_buffer=256M set-variable = max_allowed_packet=1M set-variable = table_cache=256 set-variable = sort_buffer=1M set-variable = record_buffer=1M set-variable = myisam_sort_buffer_size=64M set-variable = thread_cache=8 set-variable = max_connections=500 set-variable = ft_min_word_len=1 long_query_time=1 log-long-format log-slow-queries = /var/log/slow.log Do you have any idea why my slow query log isn't working? Can the mysql user write to this file? --log-long-format should show you queries that don't use an index. Do I have something wrong in my syntax? I'm using MySQL 4.0. Thanks for your help, I really appreciate it. http://www.hdtv-info.org http://www.entertainment-news.org http://www.political-news.org Look at Created_tmp_disk_tables 14768 Created_tmp_tables 269520 Created_tmp_files 3 Increase tmp_table_size = 64M: it's used to stop going to disk and some internal mysql operations. Handler_read_rnd_next 58229817 Your tables are not index properly, your doing a lot of table scans. Your biggest perf. Gain will come from changing your schema and or optimizing your queries. DVP Dathan Vance Pattishall http://www.friendster.com Hello, I was wondering if a more knowledgeable person could help me out with my configuration and let me know how I could further optimize MySQL. Here's the hardware on my dedicated server: Processor #1 Vendor: GenuineIntel Processor #1 Name: Intel(R) Pentium(R) 4 CPU 2.40GHz Processor #1 speed: 2400.152 MHz Processor #1 cache size: 1024 KB Memory 512 MB Here's what I get for the command SHOW STATUS: Aborted_clients247 Aborted_connects483 Bytes_received 531539854 Bytes_sent 503095410 Connections 450758 Created_tmp_disk_tables 14768 Created_tmp_tables 269520 Created_tmp_files 3 Delayed_insert_threads 0 Delayed_writes 0 Delayed_errors 0 Flush_commands 1 Handler_commit 0 Handler_delete 27837 Handler_read_first 670529 Handler_read_key 285579436 Handler_read_next 394084433 Handler_read_prev 680815 Handler_read_rnd5230552 Handler_read_rnd_next 58229817 Handler_rollback 0 Handler_update 384098 Handler_write 77442968 Key_blocks_used 50333 Key_read_requests 1081940322 Key_reads 45598 Key_write_requests 66458416 Key_writes 41372551 Max_used_connections 154 Not_flushed_key_blocks 0 Not_flushed_delayed_rows 0 Open_tables256 Open_files 323 Open_streams 0 Opened_tables 1022 Questions 30428972 Qcache_queries_in_cache0 Qcache_inserts0 Qcache_hits 0 Qcache_lowmem_prunes0 Qcache_not_cached 0 Qcache_free_memory 0 Qcache_free_blocks 0 Qcache_total_blocks 0 Rpl_status NULL Select_full_join 268 Select_full_range_join0 Select_range 66211 Select_range_check 0 Select_scan 151459 Slave_open_temp_tables 0 Slave_running OFF Slow_launch_threads 2 Slow_queries 15783 Sort_merge_passes 0 Sort_range 476962 Sort_rows 5241809 Sort_scan 283556 Table_locks_immediate31443397 Table_locks_waited 20243 Threads_cached 4 Threads_created 2423 Threads_connected5 Threads_running 1 Uptime 771502 Here are the complete contents of my my.cnf file [mysqld] skip-locking set-variable = key_buffer=256M set-variable = max_allowed_packet=1M set-variable = table_cache=256 set-variable = sort_buffer=1M set-variable = record_buffer=1M set-variable = myisam_sort_buffer_size=64M
Auto-Increment Starting Point? (Multimaster Replication Question)
When you set a field to auto-increment, can you tell it where to start? I'm trying to set up multimaster replication, but I'm worried about auto-increment collisions. Q: If server A starts auto-incrementing at 0, and server B starts auto-incrementing at some point higher than the maximum number of records, would that allow replication without auto-increment collisions? Q2: Assuming you can tell it where to start auto-incrementing, what happens after the following sequence: 1. Johnny inserts record 1000 at server A. 2. Server A receives record number 5000 from server B via replication. 3. Mary needs to insert a new record. Does server A number the new record 1001 or 5001? -- Eric Robinson
Unique index on two fields
Can someone explain how I can make a combination of two fields be a unique index. For example, in my table I have an OrderID field and a LineItems field. Individually the fields are not unique but when combined they are. I'd like to create a unique index of the two together but not individually. Thanks for the help.
RE: Auto-Increment Starting Point? (Multimaster Replication Question)
5001 Peter -Original Message- From: Robinson, Eric [mailto:[EMAIL PROTECTED] Sent: 18 November 2004 21:35 To: [EMAIL PROTECTED] Subject: Auto-Increment Starting Point? (Multimaster Replication Question) When you set a field to auto-increment, can you tell it where to start? I'm trying to set up multimaster replication, but I'm worried about auto-increment collisions. Q: If server A starts auto-incrementing at 0, and server B starts auto-incrementing at some point higher than the maximum number of records, would that allow replication without auto-increment collisions? Q2: Assuming you can tell it where to start auto-incrementing, what happens after the following sequence: 1. Johnny inserts record 1000 at server A. 2. Server A receives record number 5000 from server B via replication. 3. Mary needs to insert a new record. Does server A number the new record 1001 or 5001? -- Eric Robinson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
need help optimize query
Dear list, i have some small tables but for some reason the mysql took very long to find the results. my query looks like below and mysql'e explain is attached for better format. Thanks for your help! select teu.name, eca.owner_id, ece.value fromtyped_enterprise_unit teu, e_contact_association eca, e_contact_entry ece where teu.unit_id=eca.owner_id and eca.entry_id=ece.entry_id and eca.type_id=68 and (teu.type_path like '%/66/%' or teu.type_id=66) and eca.owner_id 45 order by eca.owner_id limit 50; mysql select teu.name, eca.owner_id, ece.value - - fromtyped_enterprise_unit teu, - e_contact_association eca, - e_contact_entry ece - - where teu.unit_id=eca.owner_id and eca.entry_id=ece.entry_id and - eca.type_id=68 and (teu.type_path like '%/66/%' or teu.type_id=66) - - order by eca.owner_id limit 22; +---+--++ | name | owner_id | value | +---+--++ | Asian Book One| 45 | [EMAIL PROTECTED]| +---+--++ 22 rows in set (4.97 sec) mysql explain select teu.name, eca.owner_id, ece.value - - fromtyped_enterprise_unit teu, - e_contact_association eca, - e_contact_entry ece - - where teu.unit_id=eca.owner_id and eca.entry_id=ece.entry_id and - eca.type_id=68 and (teu.type_path like '%/66/%' or teu.type_id=66) - - order by eca.owner_id limit 22; ++-++--+---+--+-+--+--+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-++--+---+--+-+--+--+-+ | 1 | PRIMARY | eca| ALL | NULL | NULL | NULL| NULL | 2712 | Using where; Using temporary; Using filesort | | 1 | PRIMARY | ece| ALL | NULL | NULL | NULL| NULL | 2669 | Using where | | 1 | PRIMARY | derived2 | ALL | NULL | NULL | NULL| NULL | 1440 | Using where | | 2 | DERIVED | tp | ALL | NULL | NULL | NULL| NULL | 100 | | | 2 | DERIVED | eu | ALL | NULL | NULL | NULL| NULL | 1444 | Using where | ++-++--+---+--+-+--+--+-+ 5 rows in set (0.37 sec) No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.289 / Virus Database: 265.3.1 - Release Date: 11/15/2004 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unique index on two fields
ALTER TABLE tablename ADD UNIQUE (Column1, Column2); Best Regards, Andrew Can someone explain how I can make a combination of two fields be a unique index. For example, in my table I have an OrderID field and a LineItems field. Individually the fields are not unique but when combined they are. I'd like to create a unique index of the two together but not individually. Thanks for the help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
2 questions about Replication
Hello, i have 2 questions about Replication. 1) I managed to make one slave and one master. The replication works fine and it updates only one table from one database (cause of the replicate-do-table=db.table1). The first question is can I tell mysql that table1 from master to be named table2 on slave? I already have another table1 and I dont want to overwrite it. 2) As far as I read I can have as many slaves as I want and one master. Can I have the opposite? Imagine the follow scenario: I have 3 Shops with 3 Database (same structure) and they all contain a table CUSTOMERS. I want to put all those customers from 3 shops to a central database. So, I was thinking to inverse the master/slave logic and backup each shop to the slave computer. Thats why I need to change the name of table. Because i want to backup customers of first shop to CUSTOMERS1, customers of second shop to CUSTOMERS2 etc etc. Your comments are welcomed. Thanks
Re: Poor performance for executing SELECT COUNT() for 50 000 records
Julian, maybe the secondary index tree LOGIN_NAME is so small that it fits completely in the buffer pool? Then there is no file I/O, and the scan is faster. For this reason, COUNT(secondary_index_column) can be faster than COUNT(*), which is performed on the PRIMARY, clustered index. 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: Julian Legeny [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, November 18, 2004 5:22 PM Subject: Poor performance for executing SELECT COUNT() for 50 000 records Hello, I'm using MySQL 4.0.20a. I have a question about index performance for sql query SELECT COUNT(*) or SELECT COUNT(ID) or SELECT COUNT(LOGIN_NAME) for huge data (50.000 records). I have table BF_USER: CREATE TABLE BF_USER ( ID INTEGER NOT NULL AUTO_INCREMENT, DOMAIN_ID INTEGER NOT NULL, FIRST_NAME VARCHAR(50) NOT NULL, LAST_NAME VARCHAR(50) NOT NULL, EMAIL VARCHAR(50) NOT NULL, LOGIN_NAME VARCHAR(30) NOT NULL, PASSWORD VARCHAR(30) NOT NULL, MODIFICATION_DATE TIMESTAMP NOT NULL, CREATION_DATE TIMESTAMP NOT NULL DEFAULT 0, CONSTRAINT BF_USR_PK PRIMARY KEY (ID), CONSTRAINT BF_USR_LOGIN_UQ UNIQUE (LOGIN_NAME), CONSTRAINT BF_USR_EMAIL_UQ UNIQUE (EMAIL), INDEX IND_USER_DOMAIN_ID (DOMAIN_ID), CONSTRAINT BF_USR_FK FOREIGN KEY (DOMAIN_ID) REFERENCES BF_DOMAIN (ID) ON DELETE CASCADE ) TYPE=INNODB; And index on FK column DOMAIN_ID and unique column LOGIN_NAME: CREATE INDEX LST_LOGIN_NAME ON BF_USER (DOMAIN_ID, LOGIN_NAME); After creating this table and index, there will be created following indexes: PRIMARY - index on key ID (automatically created) LOGIN_NAME - unique index on column LOGIN_NAME (automatically created) EMAIL - unique index on column EMAIL (automatically created) LST_LOGIN_NAME - combined index on columns DOMAIN_ID and LOGIN_NAME. (created manually) I fill table BF_USER by 50.000 records, there are some differencies between time durations for executing SELECT COUNT() command. I have try 3 types of COUNT(): 1.) SELECT COUNT(*) FROM BF_USER; - - I have execute this command 3-times (on 50.000 items) and here are time durations of this select: Query 1 elapsed time (seconds) - SQL query: 1,281 Query 1 elapsed time (seconds) - SQL query: 1,407 Query 1 elapsed time (seconds) - SQL query: 1,375 When I EXPLAIN this query, i can see: type : Index possible_keys : null key : PRIMARY key_len : 4 extra : Using index - it means, there is used PRIMARY index, but I thing, there is poor performance for this query. 2.) SELECT COUNT(ID) FROM BF_USER; -- - I have execute this command 3-times (on 50.000 items) and here are time durations of this select: Query 1 elapsed time (seconds) - SQL query: 1,313 Query 1 elapsed time (seconds) - SQL query: 1,484 Query 1 elapsed time (seconds) - SQL query: 1,343 When I EXPLAIN this query, i can see: type : Index possible_keys : null key : PRIMARY key_len : 4 extra : Using index - it means, there is used PRIMARY index, but I thing, there is poor performance for this query. It is the same as SELECT COUNT(*) 3.) SELECT COUNT(LOGIN_NAME) FROM BF_USER; -- - I have execute this command 3-times (on 50.000 items) and here are time durations of this select: Query 1 elapsed time (seconds) - SQL query: 0,437 Query 1 elapsed time (seconds) - SQL query: 0,063 Query 1 elapsed time (seconds) - SQL query: 0,062 When I EXPLAIN this query, i can see: type : Index possible_keys : null key : LOGIN_NAME key_len : 30 extra : Using index - it means, there is used LOGIN_NAME index, and also performance is MUCH BETTER as for count(*)/count(id). First time it takes more time but each following is very fast. My question is, WHY there is better performance for executing COUNT(LOGIN_NAME) as for COUNT(ID). Both commands are used indexes I don't understand why index for ID is too slow. Is there a possibility speed up SELECT COUNT(*) ? Thank you for your advice, with best regards, Julian Legeny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL
Re: Reg Table spaces and Rollback segments in MySQL 4.0.21
Lakshmi, - Original Message - From: [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, November 18, 2004 1:07 PM Subject: Reg Table spaces and Rollback segments in MySQL 4.0.21 Hi ALL, We are using MySQL 4.0.21 with InnoDB. For creating the tablespace mentioned as innodb_data_file_path =3D ibdata1:10M:autoextend in my.cnf file. Here, is there a facility to know the table space name?. Shall we create multiple table spaces like the above in MySQL 4.0.21 and assign different tables to different table spaces?. in MySQL-4.0, there is just one tablespace. It consists of the ibdata files, that are thought of as catenated. Actually if we set autocommit=3D0, we are able to do the perform rollback and commit and is working as expected in different sessions. I think rollback segments are creating internally. Is there any facility to mention the rollback segment size?. Shall we create our own rollback segments in MYSQL 4.0.21? No need to create them. InnoDB creates the 'rollback segment' automatically in the ibdata files. PS: Whenever Oracle Database is created Rollback segments were created in the System tablespace. Also, have the option of creating more Rollback segments in the non system tablespace. Do we have the same facility in MySQL 4.0.21? In Oracle, rollback segments can become a bottleneck, but not in InnoDB. No need to create more of them. Please advise us for the solution. Thanks, Narasimha please address these general MySQL questions to [EMAIL PROTECTED] 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/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Indexing...
Can anyone tell me why this makes sense? I have a SELECT which uses an indexed datetime field called Start with a BETWEEN range. If I select on this with no LIMIT, it does a full scan of the 9391282 records in the DB (key=NULL). However, if I do a limit of any value LESS than the number of records in the DB, it uses the index (key=Start). If I do a LIMIT of any value greater than the number of records in the DB it again doesn't use the index (key=NULL). This is on MySQL 4.0.18. Am I missing a bug fix in a more recent version of MySQL? mysql SHOW CREATE TABLE BillingLog; | BillingLog |CREATE TABLE `BillingLog` ( `CallID` bigint(20) unsigned NOT NULL default '0', `ChargeType` char(5) NOT NULL default '', `DNIS` char(10) NOT NULL default '', `Application` char(15) NOT NULL default '', `Duration` int(11) NOT NULL default '0', `VoiceSystem` char(8) NOT NULL default '', `Mailbox` char(15) NOT NULL default '', `PhoneNumber` char(20) NOT NULL default '', `InfoDigs` char(2) NOT NULL default '', `BillingRate` char(5) NOT NULL default '', `TimeStamp` timestamp(14) NOT NULL, `Sequence` bigint(20) NOT NULL auto_increment, `Start` datetime NOT NULL default '-00-00 00:00:00', `End` datetime NOT NULL default '-00-00 00:00:00', `Provider` char(5) NOT NULL default '', `ProviderCost` double(15,3) NOT NULL default '0.000', `BillProvider` char(5) NOT NULL default '', `CustomerPrice` double(15,3) NOT NULL default '0.000', PRIMARY KEY (`Sequence`), UNIQUE KEY `Sequence` (`Sequence`), KEY `Start` (`Start`), KEY `Application` (`Application`,`Start`), KEY `CallID` (`CallID`) ) TYPE=MyISAM | mysql EXPLAIN SELECT DATE_FORMAT(Start, '%m/%d/%Y %H:%i:%S'), Duration, PhoneNumber, DNIS, Provider, Sequence, ProviderCost, CustomerPrice, CallID FROM BillingLog WHERE Start BETWEEN '2004100100' AND '2004103100'; ++--+---+--+-+--+-+-+ | table | type | possible_keys | key | key_len | ref | rows| Extra | ++--+---+--+-+--+-+-+ | BillingLog | ALL | Start | NULL |NULL | NULL | 9391282 | Using where | ++--+---+--+-+--+-+-+ 1 row in set (0.00 sec) mysql EXPLAIN SELECT - DATE_FORMAT(Start, '%m/%d/%Y %H:%i:%S'), - Duration, - PhoneNumber, - DNIS, - Provider, - Sequence, - ProviderCost, - CustomerPrice, - CallID - FROM - BillingLog - WHERE - Start BETWEEN '2004100100' AND '2004103100' - ; ++--+---+--+-+--+-+-+ | table | type | possible_keys | key | key_len | ref | rows| Extra | ++--+---+--+-+--+-+-+ | BillingLog | ALL | Start | NULL |NULL | NULL | 9391289 | Using where | ++--+---+--+-+--+-+-+ 1 row in set (0.00 sec) mysql EXPLAIN SELECT - DATE_FORMAT(Start, '%m/%d/%Y %H:%i:%S'), - Duration, - PhoneNumber, - DNIS, - Provider, - Sequence, - ProviderCost, - CustomerPrice, - CallID - FROM - BillingLog - WHERE - Start BETWEEN '2004100100' AND '2004103100' - LIMIT 1000 - ; ++---+---+---+-+--+-+-+ | table | type | possible_keys | key | key_len | ref | rows| Extra | ++---+---+---+-+--+-+-+ | BillingLog | range | Start | Start | 8 | NULL | 2061426 | Using where | ++---+---+---+-+--+-+-+ 1 row in set (0.00 sec) mysql EXPLAIN SELECT - DATE_FORMAT(Start, '%m/%d/%Y %H:%i:%S'), - Duration, - PhoneNumber, - DNIS, - Provider, - Sequence, - ProviderCost, - CustomerPrice, - CallID - FROM - BillingLog - WHERE - Start BETWEEN '2004100100' AND '2004103100' - LIMIT 1000 - ; ++--+---+--+-+--+-+-+ | table | type | possible_keys | key | key_len | ref | rows| Extra | ++--+---+--+-+--+-+-+ | BillingLog | ALL | Start | NULL |NULL | NULL | 9391313 | Using where | ++--+---+--+-+--+-+-+ 1 row in set (0.01
Spatial and InnoDB: WHEN?
There have been a couple of threads about the lack of spatial support with InnoDB tables. What is the timeline for its implementation? Paul? thanks in advance, Chuck
Re: Spatial and InnoDB: WHEN?
At 21:02 -0800 11/18/04, Chuck Han wrote: There have been a couple of threads about the lack of spatial support with InnoDB tables. What is the timeline for its implementation? Paul? Dunno. -- 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]
detect rows that has zero values
Im trying not to list the rows that has zero values. this is my current select statement: select id,country_city from rates_ocean order by country_city The fields that i have to detect are: fieldvalue1 fieldvalue2 fieldvalue3 how can i do this on mysql, ex: select country from rates (if fieldvalue1,fieldvalue2,fieldvalue3 = 0 (do not display)) order by country; -- 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]
