Replication doesn't work under heavy load
Hi,
I just noticed that replication on my servers failed when they were
under heavy load. I have 1 master and 2 slaves.
For example, I have table for all incoming messages.
message_inbox | CREATE TABLE `message_inbox` (
`member_id` mediumint(8) unsigned NOT NULL default '0',
`message_id` int(10) unsigned NOT NULL default '0',
`new` enum('y','n','replied') NOT NULL default 'y',
`datetime` datetime default NULL,
KEY `idx_1` (`member_id`,`new`),
KEY `idx_2` (`member_id`,`datetime`)
) TYPE=InnoDB
This table has about 2789678 rows.
I have a cron running this every night.
DELETE message_inbox FROM message_inbox LEFT JOIN message ON message.id
= message_inbox.message_id WHERE message.id IS NULL
Message table also has about the same amount of rows.
I notice that when the cron is running, the changes made to this table
don't replicate properly to slave DB. Anyone has same experience? I
checked 'show slave status' but no Last_error or Last_errno was found.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Order by 2 date fields
Hello, I have two date fields (1095689105) in mysql. One is Creation date and other is Modify date. If news is not modified its value is 0. How can I sort my news so modify date is more important (if exists) than creation date? TNX -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Order by 2 date fields
Hello, I have two date fields (1095689105) in mysql. One is Creation date and other is Modify date. If news is not modified its value is 0. How can I sort my news so modify date is more important (if exists) than creation date? What about an ORDER BY with a CASE statement that uses the CreationDate if ModifyDate = 0 and ModifyDate if it's 0. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Order by 2 date fields
On Mon, 4 Oct 2004 11:03:13 +0200, Martijn Tonies [EMAIL PROTECTED] wrote: Hello, I have two date fields (1095689105) in mysql. One is Creation date and other is Modify date. If news is not modified its value is 0. How can I sort my news so modify date is more important (if exists) than creation date? What about an ORDER BY with a CASE statement that uses the CreationDate if ModifyDate = 0 and ModifyDate if it's 0. With regards, Or use: ORDER BY if(ModifyDate = 0,1, ModifyDate), CreationDate Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Doubt in Isolation level
Hi, I use MySql version 4.0.18. We do a lot of bulk inserts and there is no need for transaction integrity. Currently the isolation level of the database is REPEATABLE-READ. Iam planning to change that to READ UNCOMMITTED as I dont have any transactions. Will this improve DB performance ? Is there any side effects for this?. Thanks in advance for your suggestions. --Yoge
Need help with mysql search engine
Hi, I currently have a table with 2 text fields, both of which are marked as fulltext. I'm using the full-text boolean search method with fairly good results. My problems however are: 1. I am on a shared host provider so I can't tweak the full-text search options such as stop words or minimum query length. 2. I would like to be able to search for words as short as 2 characters, but I'm limited by the default mysql configuration to 4 characters. 3. I'd like to get around the stopwords/stop characters. A lot of people search for 802.11g , but that search always comes up empty because I think the . character is messing up my searches. 4. Since I'm using the full-text search with a small amount of data, I would like all the results to be returned, even if they keywords are in more or less than 50% of the rows. 5. I really like the +, -, and for searching in full-text boolean mode, so I'd like to preserve that functionality. Since I'm on a shared host provider and can't really optimize the mysql configuration, is there any suggestions to help me improve my search results? Thanks, Grant __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order rows before applying GROUP BY / DISTINCT
What you are looking for is the MAX() of the Time column (most recent entry) select user, host, max(time) as time, count(user) as visits from userlog where user!='' group by user, host order by time desc limit 10; The issue you ran into is caused by an non-standard SQL extension created by MySQL. Its behavior is documented here: http://dev.mysql.com/doc/mysql/en/GROUP-BY-hidden-fields.html SOAPBOX ON IMHO, this extension of the SQL spec has caused more problems than it has helped. I believe a better extension would have been to add a new GROUP BY predicate like FIRST() or LAST() so that the behavior would be explicit and controllable. It is even documented that the value returned is potentially random Do not use this feature if the columns you omit from the GROUP BY part are not unique in the group! You will get unpredictable results. I know it's too late to take this behavior out of the older versions of MySQL but is there any way to prevent its inclusion in future versions? Are there other options out there to fix this non-deterministic behavior? I do not believe that educating the community to _avoid_ a feature is a viable option. If it were, I do not think that we would need to explain this strange behavior to new users as often as we do. SOAPBOX OFF Shawn Green Database Administrator Unimin Corporation - Spruce Pine Remi Mikalsen [EMAIL PROTECTED] wrote on 10/01/2004 06:41:32 PM: Hello. I'm having a problem where I seem to need to order a table before applying group by (or distinct?) in my query. Here is a simplified table structure example: ID USER HOST TIME ID = Primary Key I would like to do the following in ONE query, if possible: I am looking to retrieve the LAST time 10 UNIQUE users were registered in the table (user+host+time). These users should be the last 10 people to be inserted into the table (each user can appear various times in the table, like in a log). At the same time, I would like to retrieve the TOTAL NUMBER of times each of these users appear in the table, but this is not very important. This was the query I adopted until noticing it had a severe problem: select user, host, time, count(user) as times from userlog where user!='' group by user order by time desc limit 10; The problem is that the TIME associated with each person isn't the LAST TIME a registry was done for the user. This makes me think that I might need to order the TIME column before doing the GROUP BY, but I do no know how (and it might not even be the solution to the problem!). I do not know if I managed to express myself very well, but if anyone is willing to help, I would of course clarify things if necessary. Remi Mikalsen E-Mail: [EMAIL PROTECTED] URL: http://www.iMikalsen.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication doesn't work under heavy load
On Monday 04 October 2004 01:18 am, Batara Kesuma wrote: I have a cron running this every night. DELETE message_inbox FROM message_inbox LEFT JOIN message ON message.id = message_inbox.message_id WHERE message.id IS NULL Message table also has about the same amount of rows. I notice that when the cron is running, the changes made to this table don't replicate properly to slave DB. Anyone has same experience? I checked 'show slave status' but no Last_error or Last_errno was found. Define properly? Jeff pgpLp7xvfln8d.pgp Description: PGP signature
Re: foreign key problem
Whenever you get an INNODB error, you can get more details by running a SHOW INNODB STATUS. A foreign key means that a value must exist in one table before it can be used as a value in another table. That's probably why you couldn't add a record to Table2 before you had a value in Table1. The child table of a foreign key is also required to have an index on the column that is referencing the foreign value. That means that you had to declare either a Key(memberID), a UNIQUE(memberID), or a PRIMARY KEY(memberID) before memberID could participate in a FK relationship. As to why you cannot drop the foreign key from Table2, you will have to look at the error's details in the SHOW INNODB STATUS results. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Stuart Felenstein [EMAIL PROTECTED] wrote on 10/03/2004 10:17:36 AM: I think I may have discovered one of my issues, is memberID in Table2 was primary key. Should not have been. As far as the error messages in removing key, I'm still unsure. Stuart --- Stuart Felenstein [EMAIL PROTECTED] wrote: Two tables: Table1 [innodb] userID. addtlfields. Table2 [innodb] memberID. addtlfields. I created a foreign key in Table2 for memberID to userID set to no action on both delete and update. I get this error when I try to add a record to table 2:Cannot add or update a child row: a foreign key constraint fails I try and remove the foreign key and it won't let me: 1025-Error on rename of './mydatabase/table to './mydatabase/#sql2-695b-c4ac0'(errno: 152) I think can probably forgoe the foreign key , but what's with all the error messages ? Thank you, Stuart -- 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: filed that does not contain text between symbols
You will need the RLIKE comparator. http://dev.mysql.com/doc/mysql/en/String_comparison_functions.html http://dev.mysql.com/doc/mysql/en/Regexp.html I haven't tested this but this is pretty close to what you will need WHERE page_body RLIKE 'img *' and page_body not RLIKE 'img *alt=*' you will have to experiment a bit to get the correct expression. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Bob Ramsey [EMAIL PROTECTED] wrote on 10/03/2004 07:45:22 AM: Hi, I have some web pages in a database and I want to check to make sure that all of the images have alt tags in them. So what I need to do is ask something like this in psuedocode: select page_name from web_pages where page_body does not contain 'alt=' between 'img' and ''; But I just can't figure out the right syntax. Any ideas? Thanks, bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Reg Backup
Thank you very much. One more question i.e Is there any command to take the Backup in MySQL 4.0 (windows) classic which does not support innodb. I know 2 commands like mysqldump and mysqlhotcopy. mysqlhotcopy seems to support myism but only in linux and netware os. But i need it in windows. Will mysqldump supports the backup in windows operatiog system with out using innodb?. If not any alternatives are there and how to do that?. Please explain me in detail asap. Thanks, Narasimha -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Fri 10/1/2004 6:22 PM To: Lakshmi NarasimhaRao (WT01 - TELECOM SOLUTIONS) Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: views/cursors in mysql 4.0 Yes. Best of luck with your conversion! Shawn Green Database Administrator Unimin Corporation - Spruce Pine [EMAIL PROTECTED] wrote on 09/30/2004 05:25:22 PM: Hi, Thank you very much for your reply. So for the cursors result set C API's will be suitable ..right?. 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. 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.
Re: Looking for null values.
Are you just looking for any column is null or that all columns are null or that specific columns are null or . what exactly do you need to determine? Shawn Green Database Administrator Unimin Corporation - Spruce Pine Scott Hamm [EMAIL PROTECTED] wrote on 10/02/2004 11:21:58 AM: How do I use SQL to look for null values in ANY column? SELECT * FROM QA WHERE * = null; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Looking for null values.
I'm trying to find a null value anywhere in QA table. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, October 04, 2004 9:46 AM To: Scott Hamm Cc: [EMAIL PROTECTED] Subject: Re: Looking for null values. Are you just looking for any column is null or that all columns are null or that specific columns are null or . what exactly do you need to determine? Shawn Green Database Administrator Unimin Corporation - Spruce Pine Scott Hamm [EMAIL PROTECTED] wrote on 10/02/2004 11:21:58 AM: How do I use SQL to look for null values in ANY column? SELECT * FROM QA WHERE * = null; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Looking for null values.
I think what will work for your case is the CONCAT() function
(http://dev.mysql.com/doc/mysql/en/String_functions.html). It will return
NULL if any field in it is null.
SELECT *
FROM qa
WHERE concat(field1, field2, field3,..., fieldn) is null
It won't break any speed records but it will find each record where any of
the fields {field1...fieldn} is null.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Scott Hamm [EMAIL PROTECTED] wrote on 10/04/2004 09:52:37 AM:
I'm trying to find a null value anywhere in QA table.
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Monday, October 04, 2004 9:46 AM
To: Scott Hamm
Cc: [EMAIL PROTECTED]
Subject: Re: Looking for null values.
Are you just looking for any column is null or that all columns are null
or
that specific columns are null or . what exactly do you need to
determine?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Scott Hamm [EMAIL PROTECTED] wrote on 10/02/2004 11:21:58 AM:
How do I use SQL to look for null values in ANY column?
SELECT * FROM QA WHERE * = null;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order rows before applying GROUP BY / DISTINCT
Thank you for the answer! However, it doesn't seem to solve my problem. Running the query without modifications repeated users (because of the group by user, HOST). I removed the HOST, and ran the query over again. Now it returned unique users, but it still didn't return the LAST login of them many logins for each user. Currently I am using a new implementation with two queries and PHP in between. I won't write all the code here, just explain the basics. 1. A query gets the total number of logins for each user 2. PHP retrieves the logins and orders them with a perfect hash - I create an array with indexes correspondig to usernames - the values in the array are the numbers of logins corresponding to each username - this way I have practically no overhead when accessing the array for example: $users['a_username'] = 10; /* 10 logins by user a_username */ 3. Another query gets all logins ordered by the time column, last first 4. PHP reads the results. When a user is found that has more than 0 logins in the $users array, I print the user and the amount of logins, and set the value in the $users array to 0. This guarantees only printing a user once, and only printing the last login due to ordering by the time field. But this also has obvious drawbacks as to performance. I may have to retrieve 10.000 rows from the userlog table to get 10 unique users, or I might never have 10 unique users in the table, even with 1.000.000.000 records! I just never know. I also have to make two queries, which gives a small overhead. Finally, using scripting to interprete intermediate results is much slower than MySQL. All in all, this just doesn't seem to be a very scaleable solution. Remi Mikalsen E-Mail: [EMAIL PROTECTED] URL:http://www.iMikalsen.com On 4 Oct 2004 at 9:14, [EMAIL PROTECTED] wrote: What you are looking for is the MAX() of the Time column (most recent entry) select user, host, max(time) as time, count(user) as visits from userlog where user!='' group by user, host order by time desc limit 10; The issue you ran into is caused by an non-standard SQL extension created by MySQL. Its behavior is documented here: http://dev.mysql.com/doc/mysql/en/GROUP-BY-hidden-fields.html SOAPBOX ON IMHO, this extension of the SQL spec has caused more problems than it has helped. I believe a better extension would have been to add a new GROUP BY predicate like FIRST() or LAST() so that the behavior would be explicit and controllable. It is even documented that the value returned is potentially random Do not use this feature if the columns you omit from the GROUP BY part are not unique in the group! You will get unpredictable results. I know it's too late to take this behavior out of the older versions of MySQL but is there any way to prevent its inclusion in future versions? Are there other options out there to fix this non-deterministic behavior? I do not believe that educating the community to _avoid_ a feature is a viable option. If it were, I do not think that we would need to explain this strange behavior to new users as often as we do. SOAPBOX OFF Shawn Green Database Administrator Unimin Corporation - Spruce Pine Remi Mikalsen [EMAIL PROTECTED] wrote on 10/01/2004 06:41:32 PM: Hello. I'm having a problem where I seem to need to order a table before applying group by (or distinct?) in my query. Here is a simplified table structure example: ID USER HOST TIME ID = Primary Key I would like to do the following in ONE query, if possible: I am looking to retrieve the LAST time 10 UNIQUE users were registered in the table (user+host+time). These users should be the last 10 people to be inserted into the table (each user can appear various times in the table, like in a log). At the same time, I would like to retrieve the TOTAL NUMBER of times each of these users appear in the table, but this is not very important. This was the query I adopted until noticing it had a severe problem: select user, host, time, count(user) as times from userlog where user!='' group by user order by time desc limit 10; The problem is that the TIME associated with each person isn't the LAST TIME a registry was done for the user. This makes me think that I might need to order the TIME column before doing the GROUP BY, but I do no know how (and it might not even be the solution to the problem!). I do not know if I managed to express myself very well, but if anyone is willing to help, I would of course clarify things if necessary. Remi Mikalsen E-Mail: [EMAIL PROTECTED] URL: http://www.iMikalsen.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
Re: Order rows before applying GROUP BY / DISTINCT
I would be very surprised if this query fails as you say it does. select user, max(time) as most_recent, count(user) as visits from userlog where user!='' group by user order by most_recent desc limit 10; This represents the last 10 users to sign in, when they signed in last, and many times they have signed in so far. I may not know everything but unless you have some problem with the data so that your datetime field time is not acting the same for all visitors, this should work. Please describe why this data is wrong for your question. It is entirely likely I misunderstood what you wanted to find in your data. That would make my suggestions wrong (my apologies if so!!) Shawn Green Database Administrator Unimin Corporation - Spruce Pine Remi Mikalsen [EMAIL PROTECTED] wrote on 10/04/2004 10:11:09 AM: Thank you for the answer! However, it doesn't seem to solve my problem. Running the query without modifications repeated users (because of the group by user, HOST). I removed the HOST, and ran the query over again. Now it returned unique users, but it still didn't return the LAST login of them many logins for each user. Currently I am using a new implementation with two queries and PHP in between. I won't write all the code here, just explain the basics. 1. A query gets the total number of logins for each user 2. PHP retrieves the logins and orders them with a perfect hash - I create an array with indexes correspondig to usernames - the values in the array are the numbers of logins corresponding to each username - this way I have practically no overhead when accessing the array for example: $users['a_username'] = 10; /* 10 logins by user a_username */ 3. Another query gets all logins ordered by the time column, last first 4. PHP reads the results. When a user is found that has more than 0 logins in the $users array, I print the user and the amount of logins, and set the value in the $users array to 0. This guarantees only printing a user once, and only printing the last login due to ordering by the time field. But this also has obvious drawbacks as to performance. I may have to retrieve 10.000 rows from the userlog table to get 10 unique users, or I might never have 10 unique users in the table, even with 1.000.000.000 records! I just never know. I also have to make two queries, which gives a small overhead. Finally, using scripting to interprete intermediate results is much slower than MySQL. All in all, this just doesn't seem to be a very scaleable solution. Remi Mikalsen E-Mail: [EMAIL PROTECTED] URL: http://www.iMikalsen.com On 4 Oct 2004 at 9:14, [EMAIL PROTECTED] wrote: What you are looking for is the MAX() of the Time column (most recent entry) select user, host, max(time) as time, count(user) as visits from userlog where user!='' group by user, host order by time desc limit 10; The issue you ran into is caused by an non-standard SQL extension created by MySQL. Its behavior is documented here: http://dev.mysql.com/doc/mysql/en/GROUP-BY-hidden-fields.html SOAPBOX ON IMHO, this extension of the SQL spec has caused more problems than it has helped. I believe a better extension would have been to add a new GROUP BY predicate like FIRST() or LAST() so that the behavior would be explicit and controllable. It is even documented that the value returned is potentially random Do not use this feature if the columns you omit from the GROUP BY part are not unique in the group! You will get unpredictable results. I know it's too late to take this behavior out of the older versions of MySQL but is there any way to prevent its inclusion in future versions? Are there other options out there to fix this non-deterministic behavior? I do not believe that educating the community to _avoid_ a feature is a viable option. If it were, I do not think that we would need to explain this strange behavior to new users as often as we do. SOAPBOX OFF Shawn Green Database Administrator Unimin Corporation - Spruce Pine Remi Mikalsen [EMAIL PROTECTED] wrote on 10/01/2004 06:41:32 PM: Hello. I'm having a problem where I seem to need to order a table before applying group by (or distinct?) in my query. Here is a simplified table structure example: ID USER HOST TIME ID = Primary Key I would like to do the following in ONE query, if possible: I am looking to retrieve the LAST time 10 UNIQUE users were registered in the table (user+host+time). These users should be the last 10 people to be inserted into the table (each user can appear various times in the table, like in a log). At the same time, I would like to retrieve the TOTAL NUMBER of times each of these users appear in the table, but this is not very important.
inner join, character data type
Hi! I am trying to do an inner join of two tables using a char(3) key. The field I am joining by contains integers with sometimes leading zeros. The inner join works when the field is a full 3 character field, with for example 566, but when it starts with a 0, it does not work anymore. Why is this? I would appreciate some help, thanx. /Camilla __ This e-mail has been scanned for all viruses by Excedo MailScreen, powered by MessageLabs. Mailscreen stops both known and unkown viruses, visit: http://www.mailscreen.se __ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Backslash \ in query
Why does selecting backslash give me an error? select \ gives me... #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '\' at line 1 select / returns / select \\ returns \ What's the deal w/ backslash? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query optimization question
Query optimization question I am selecting from a single table but it has a lot of rows and it has a very involved calculation. What I really want to do is is FIRST restrict the number of rows so that the big calculation is only performed on the ones that are within 3 degrees. Using 4.0.20 A sample query is given here: The application interpolates variable values such as 44.6 into the query string, so from mysql's point of view they are constants, right? And the explain doc says it optimizes constants, but it is looking at all the rows and I see why. SELECT city, state, country, latitude, longitude, IF(latitude REGEXP '[0-9]+$' AND longitude REGEXP'[0-9]+$', ROUND(DEGREES(ACOS((SIN(RADIANS(44.6))) * (SIN(RADIANS(latitude))) + (COS(RADIANS(44.6))) * (COS(RADIANS(latitude))) * (COS(RADIANS(-123.28 -longitude) * 111),) as distance FROM londata WHERE ABS(44.6-latitude) = 3.0 AND ABS(-123.28-longitude) = 3.0 ORDER BY distance; I guess I can't do a subselect with my version... If I could what would it look like? Something like below? (I might be able to talk the powers that be into an upgrade.) And if I can't is it more horrible to manually create a temporary table and perform the calculations on it than it is to just do what I am doing? SELECT city, state, country, latitude, longitude, IF(latitude REGEXP '[0-9]+$' AND longitude REGEXP'[0-9]+$', ROUND(DEGREES(ACOS((SIN(RADIANS(44.6))) * (SIN(RADIANS(latitude))) + (COS(RADIANS(44.6))) * (COS(RADIANS(latitude))) * (COS(RADIANS(-123.28 -longitude) * 111),) as distance FROM (SELECT * FROM londata WHERE ABS(44.6-latitude) = 3.0 AND ABS(-123.28-longitude) = 3.0) as sublon ORDER BY distance; Thanks. GT -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: **[SPAM]** Backslash \ in query
[snip] What's the deal w/ backslash? [/snip] It is an escape character. In order to get things with backslashes, as you have deduced, you SELECT \\ Do you have a column '\'? If so, why? Backslashes are often used in this way... SELECT businessName FROM table WHERE businessName = 'Doc\'s'; Because the apostrophe in Doc's would cause the field to be read as 'Doc', but escaping the apostrophe allows it to be searched properly. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backslash \ in query
At 11:35 -0400 10/4/04, Jeff Burgoon wrote: Why does selecting backslash give me an error? select \ gives me... #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '\' at line 1 select / returns / select \\ returns \ What's the deal w/ backslash? It's an escape character in strings: http://dev.mysql.com/doc/mysql/en/String_syntax.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: inner join, character data type
Camilla Brodén wrote: Hi! I am trying to do an inner join of two tables using a char(3) key. The field I am joining by contains integers with sometimes leading zeros. If it is a char(3) field it contains characters, not integers. The inner join works when the field is a full 3 character field, with for example 566, but when it starts with a 0, it does not work anymore. Why is this? If you are joining a CHAR(3) field to a INT(3) field, the INT(3) becomes a CHAR(3)' for the join. '013' is not equal to '13'. Try casting the CHAR(3) to an int. I would appreciate some help, thanx. /Camilla __ This e-mail has been scanned for all viruses by Excedo MailScreen, powered by MessageLabs. Mailscreen stops both known and unkown viruses, visit: http://www.mailscreen.se __ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backslash \ in query
Jeff Burgoon wrote: Why does selecting backslash give me an error? select \ gives me... #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '\' at line 1 select / returns / select \\ returns \ What's the deal w/ backslash? backslash is used to signify that the following character is part of the data, not a special character. It is used to include the double quotes in the string. Ex: select Mary said \You can quote me on that.\ See the special characters section of the manual. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query optimization question
Gerald Taylor wrote: Query optimization question I am selecting from a single table but it has a lot of rows and it has a very involved calculation. What I really want to do is is FIRST restrict the number of rows so that the big calculation is only performed on the ones that are within 3 degrees. Using 4.0.20 A sample query is given here: The application interpolates variable values such as 44.6 into the query string, so from mysql's point of view they are constants, right? And the explain doc says it optimizes constants, but it is looking at all the rows and I see why. SELECT city, state, country, latitude, longitude, IF(latitude REGEXP '[0-9]+$' AND longitude REGEXP'[0-9]+$', ROUND(DEGREES(ACOS((SIN(RADIANS(44.6))) * (SIN(RADIANS(latitude))) + (COS(RADIANS(44.6))) * (COS(RADIANS(latitude))) * (COS(RADIANS(-123.28 -longitude) * 111),) as distance FROM londata WHERE ABS(44.6-latitude) = 3.0 AND ABS(-123.28-longitude) = 3.0 ORDER BY distance; I guess I can't do a subselect with my version... If I could what would it look like? Something like below? (I might be able to talk the powers that be into an upgrade.) And if I can't is it more horrible to manually create a temporary table and perform the calculations on it than it is to just do what I am doing? SELECT city, state, country, latitude, longitude, IF(latitude REGEXP '[0-9]+$' AND longitude REGEXP'[0-9]+$', ROUND(DEGREES(ACOS((SIN(RADIANS(44.6))) * (SIN(RADIANS(latitude))) + (COS(RADIANS(44.6))) * (COS(RADIANS(latitude))) * (COS(RADIANS(-123.28 -longitude) * 111),) as distance FROM (SELECT * FROM londata WHERE ABS(44.6-latitude) = 3.0 AND ABS(-123.28-longitude) = 3.0) as sublon ORDER BY distance; Thanks. GT A Subselect won't help. You still have to scan the whole table to the calculation for the where clause. Pre calculate your min and max lat. and lon. WHERE latitude = @maxlat and latitude = @minlat and longitude = @maxlon and longitude = @minlon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: field that does not contain text between symbols - solved
Thanks for the replies. This appears to be the right answer: where page_body regexp '.*img .*.*' and page_body not regexp '.*img .* alt= .*.*'; bob == Bob Ramsey SYSTEMS ADMINISTRATION AND SYSTEMS PROGRAMMING III MA, Management of Information Systems 2004 MA, English Literature, 1992 ph: 1(319)335-9956 187 Boyd Law Building fax: 1(319)335-9019 University of Iowa College of Law mailto:[EMAIL PROTECTED]Iowa City, IA 52242-1113 For Hardware and Software questions, call 5-9124 == -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query optimization question
When you don't have subselects, you have two options: temporary tables or JOINed queries.In your case, I think the temporary table is the better way to go. I would also eliminate the ABS() check so that I can compare values directly against the index. I know the math is correct your way but this way you are comparing values directly against the column which means that indexes can come into play. SET @targetLat = 44.6, @targetLon = -123.8, @Delta = 3 CREATE TEMPORARY TABLE tmpDeltaData SELECT city, state, country, latitude, longitude FROM Londata WHERE latitude BETWEEN (@[EMAIL PROTECTED]) AND (@targetLat + @Delta) AND longitude BETWEEN (@targetLon - @Delta) AND (@targetLon + @Delta) SELECT city, state, country, latitude, longitude, IF(latitude REGEXP '[0-9\\.]+$' AND longitude REGEXP'[0-9\\.]+$', ROUND(DEGREES(ACOS((SIN(RADIANS(@targetLat))) * (SIN(RADIANS(latitude))) + (COS(RADIANS(@targetLat))) * (COS(RADIANS(latitude))) * (COS(RADIANS(@targetLon -longitude) * 111),) as distance FROM tmpDeltaData ORDER BY distance DROP TEMPORARY TABLE tmpDeltaData I would also test the WHERE clause from tmpDeltaData with your original query to compare speeds of the two methods. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Gerald Taylor [EMAIL PROTECTED] wrote on 10/04/2004 10:33:22 AM: Query optimization question I am selecting from a single table but it has a lot of rows and it has a very involved calculation. What I really want to do is is FIRST restrict the number of rows so that the big calculation is only performed on the ones that are within 3 degrees. Using 4.0.20 A sample query is given here: The application interpolates variable values such as 44.6 into the query string, so from mysql's point of view they are constants, right? And the explain doc says it optimizes constants, but it is looking at all the rows and I see why. SELECT city, state, country, latitude, longitude, IF(latitude REGEXP '[0-9]+$' AND longitude REGEXP'[0-9]+$', ROUND(DEGREES(ACOS((SIN(RADIANS(44.6))) * (SIN(RADIANS(latitude))) + (COS(RADIANS(44.6))) * (COS(RADIANS(latitude))) * (COS(RADIANS(-123.28 -longitude) * 111),) as distance FROM londata WHERE ABS(44.6-latitude) = 3.0 AND ABS(-123.28-longitude) = 3.0 ORDER BY distance; I guess I can't do a subselect with my version... If I could what would it look like? Something like below? (I might be able to talk the powers that be into an upgrade.) And if I can't is it more horrible to manually create a temporary table and perform the calculations on it than it is to just do what I am doing? SELECT city, state, country, latitude, longitude, IF(latitude REGEXP '[0-9]+$' AND longitude REGEXP'[0-9]+$', ROUND(DEGREES(ACOS((SIN(RADIANS(44.6))) * (SIN(RADIANS(latitude))) + (COS(RADIANS(44.6))) * (COS(RADIANS(latitude))) * (COS(RADIANS(-123.28 -longitude) * 111),) as distance FROM (SELECT * FROM londata WHERE ABS(44.6-latitude) = 3.0 AND ABS(-123.28-longitude) = 3.0) as sublon ORDER BY distance; Thanks. GT -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Reg Backup
Hi, Is there any suitable command in MySQL 4.0 with out InnoDB for taking the backup. Please suggest. 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.
Re: Reg Backup
On Mon, 4 Oct 2004 21:55:58 +0530, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Is there any suitable command in MySQL 4.0 with out InnoDB for taking the backup. Please suggest. Have you looked at mysqlhotcopy ? http://dev.mysql.com/doc/mysql/en/mysqlhotcopy.html -- Greg Donald Zend Certified Engineer http://gdconsultants.com/ http://destiney.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Reg Backup
mysqlhotocpy supports only Linux and Netware software. Will it support windows operating system. Please clarify. Thanks, Narasimha -Original Message- From: Greg Donald [mailto:[EMAIL PROTECTED] Sent: Mon 10/4/2004 10:06 PM To: [EMAIL PROTECTED] Cc: Subject: Re: Reg Backup On Mon, 4 Oct 2004 21:55:58 +0530, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Is there any suitable command in MySQL 4.0 with out InnoDB for taking the backup. Please suggest. Have you looked at mysqlhotcopy ? http://dev.mysql.com/doc/mysql/en/mysqlhotcopy.html -- Greg Donald Zend Certified Engineer http://gdconsultants.com/ http://destiney.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] 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.
Re: Reg Backup
On Mon, 4 Oct 2004 [EMAIL PROTECTED] wrote: Hi, Is there any suitable command in MySQL 4.0 with out InnoDB for taking the backup. Please suggest. Thanks, Narasimha Hi Narasimha, when posting a question to a mailing list, please don't ask it many times, or the users might say hey, this guy sucks, we heard his question already. If you need quick answers to your questions, as well as real support, the MySQL guys can sell you some. Additionnaly, this list is archived, so your question might have been already answered, so browse the mailing list archives first before asking. Regards, Olivier Kaloudoff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: **[SPAM]** Backslash \ in query
I'm populating a new table from excel sheets and for some silly reason several column values are populated with \ (not my doing!). I can easily fix the problem but I was just wondering. Thanks Everyone. Jay Blanchard [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] [snip] What's the deal w/ backslash? [/snip] It is an escape character. In order to get things with backslashes, as you have deduced, you SELECT \\ Do you have a column '\'? If so, why? Backslashes are often used in this way... SELECT businessName FROM table WHERE businessName = 'Doc\'s'; Because the apostrophe in Doc's would cause the field to be read as 'Doc', but escaping the apostrophe allows it to be searched properly. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reg Backup
On Mon, 4 Oct 2004 22:10:39 +0530, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: mysqlhotocpy supports only Linux and Netware software. Will it support windows operating system. Please clarify. I have heard of people using Perl on windows, so I would guess it will run, mysqlhotcopy being a Perl script and all. Feel free to try it and discover the definative answer for yourself. -- Greg Donald Zend Certified Engineer http://gdconsultants.com/ http://destiney.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEW
Hai Martin, I suppose these are the ways with Databases. They don't work the way we like them to. So please adjust with it. For this matter I think, any RDBMS will be doing their or paddings to the scripts when they are loading it in memories. just the same way for oracle(where i have some (in)experience). so, don;'t worry, be happy. Regards, Eldo. On Wed, 29 Sep 2004 11:57:22 +0200, Martijn Tonies [EMAIL PROTECTED] wrote: Hi there, I'm testing MySQL 5.0.1 a bit - and I noticed the following... When executing this: create view myview2 (t0) as select c1 from t It returns this when doing a SHOW CREATE VIEW myview2: CREATE VIEW test.myview2 AS select `test`.`t`.`c1` AS `t0` from `test`.`t` This is not at all what I entered. I dislike backticks unless I specify them. I don't want a database name included unless I specify it (from `test`.`t`). Can the annoying behaviour be changed or turned off? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Thanks Regards, Eldo Skaria -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEW
Hello Eldo, I suppose these are the ways with Databases. They don't work the way we like them to. So please adjust with it. For this matter I think, any RDBMS will be doing their or paddings to the scripts when they are loading it in memories. just the same way for oracle(where i have some (in)experience). so, don;'t worry, be happy. This is plain rubbish. See my other example with a more complicated view source. When adjusting the view, or extracting a script - the view source becomes complete gibberish. MS SQL, or Firebird, for example, store the view-source as defined - this includes comments, spacing etc etc... In short: it becomes usuable. MySQL should do this too. From reading these lists, I think MySQL only stores the resulting structure - or something similar - and (currently) not the view source. To make views useful, better change it... :-) With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Order by 2 date fields
Hi Khan, I would klike to approach ur problem in a diffrent manner. My suggestion is that, in both the date fields u use dates only. (I don't know the useabilty of '0' in a date field). While using this, u have to store the creation time a the modification time, at the creation of record. This is the technique we are using very extensivey in our application to store creation and modifcation time as well as users. Here when there is no modification, the the creation time will be used for processing, which is same as modfy time. So always you need to order against the modify time only. You may find it difficult to adjust ur application so that it processes the modification time in the new fashion. this has an advantage that, during sorting, it uses less resources, hence a fster query is resulted. further, if ur table is having hundreds of thousands of records, then ur order by clause with functions will become all the more process intensive as compared to a direct filed sorting. reg, Eldo. On Mon, 4 Oct 2004 10:06:03 +0100, Paul [EMAIL PROTECTED] wrote: On Mon, 4 Oct 2004 11:03:13 +0200, Martijn Tonies [EMAIL PROTECTED] wrote: Hello, I have two date fields (1095689105) in mysql. One is Creation date and other is Modify date. If news is not modified its value is 0. How can I sort my news so modify date is more important (if exists) than creation date? What about an ORDER BY with a CASE statement that uses the CreationDate if ModifyDate = 0 and ModifyDate if it's 0. With regards, Or use: ORDER BY if(ModifyDate = 0,1, ModifyDate), CreationDate Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Thanks Regards, Eldo Skaria -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEW
On Tue, 5 Oct 2004 00:27:45 +0530, Eldo Skaria wrote: I suppose these are the ways with Databases. They don't work the way we like them to. So please adjust with it. For this matter I think, any RDBMS will be doing their or paddings to the scripts when they are loading it in memories. But that doesn't make it right. Specifically, I seem to remember from the SQL standard that the use of database.table.field is undesired (or schema.table.field if you don't use MySQL) and instead database.table should be aliassed. In that case it should be: CREATE VIEW test.myview2 AS select alias.c1 AS t0 from test.t alias; Further, I would like to second Martijn's opinion regarding backticks. It is bad enough that the SQL standard double quote isn't used, let's not make it worse by including unwanted backticks. Jochem -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE VIEW gets all mangled up when doing SHOW CREATE VIEW
I suppose these are the ways with Databases. They don't work the way we like them to. So please adjust with it. For this matter I think, any RDBMS will be doing their or paddings to the scripts when they are loading it in memories. But that doesn't make it right. Specifically, I seem to remember from the SQL standard that the use of database.table.field is undesired (or schema.table.field if you don't use MySQL) and instead database.table should be aliassed. In that case it should be: CREATE VIEW test.myview2 AS select alias.c1 AS t0 from test.t alias; Further, I would like to second Martijn's opinion regarding backticks. It is bad enough that the SQL standard double quote isn't used, let's not make it worse by including unwanted backticks. I also mentioned a more real life example - I just realized I send that to the internals list, and not to this one. You can view my message here: http://lists.mysql.com/internals/17270 If I would be a user of MySQL 5 (and I am, working on a developer tool and all), I would be seriously ticked off if this would make it into the production release... Have a look at the message at the URL above and tell me this is the right way to bring a system into production and keep it maintainable. With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Order by 2 date fields
I agree with keeping only dates or nulls in a date-type column. One other option, you could use a NULL value for ModifyDate with the COALESCE() function like this SELECT, COALESCE(ModifyDate, CreationDate) as recordDate FROM ... WHERE ... ORDER BY recordDate if ModifyDate is not null, that is the value returned, otherwise the COALESCE() function moves to the next term in the list. It will continue checking its value list until it reaches the first non-null value or runs out of records to check. http://dev.mysql.com/doc/mysql/en/Comparison_Operators.html I don't think you will be able to form query this to use an index for the ORDER BY because you are choosing between two different columns to represent the same value (the value you want to sequence your records with). Sorry. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Eldo Skaria [EMAIL PROTECTED] wrote on 10/04/2004 03:29:46 PM: Hi Khan, I would klike to approach ur problem in a diffrent manner. My suggestion is that, in both the date fields u use dates only. (I don't know the useabilty of '0' in a date field). While using this, u have to store the creation time a the modification time, at the creation of record. This is the technique we are using very extensivey in our application to store creation and modifcation time as well as users. Here when there is no modification, the the creation time will be used for processing, which is same as modfy time. So always you need to order against the modify time only. You may find it difficult to adjust ur application so that it processes the modification time in the new fashion. this has an advantage that, during sorting, it uses less resources, hence a fster query is resulted. further, if ur table is having hundreds of thousands of records, then ur order by clause with functions will become all the more process intensive as compared to a direct filed sorting. reg, Eldo. On Mon, 4 Oct 2004 10:06:03 +0100, Paul [EMAIL PROTECTED] wrote: On Mon, 4 Oct 2004 11:03:13 +0200, Martijn Tonies m. [EMAIL PROTECTED] wrote: Hello, I have two date fields (1095689105) in mysql. One is Creation date and other is Modify date. If news is not modified its value is 0. How can I sort my news so modify date is more important (if exists) than creation date? What about an ORDER BY with a CASE statement that uses the CreationDate if ModifyDate = 0 and ModifyDate if it's 0. With regards, Or use: ORDER BY if(ModifyDate = 0,1, ModifyDate), CreationDate Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Thanks Regards, Eldo Skaria -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unknown table ... in where clause
Greetings, I'm trying to update existing records in one table so that value of a column winds up matching the value of a column in a second table, based on a match in a second column in each table. I'm not sure if I'm taking the wrong approach on this, of if I'm missing something simple in the SQL syntax (like this being illegal), but I'd appreciate any feedback on why MySQL is returning the Unknown table error when the table does indeed exist (see output below). I can do this manually (v. small number of records involved) but for cases where doing it by hand would be impractical, it would be helpful to be able to do this without writing a script. Thanks, Ted mysql UPDATE dp_populate_vals - SET dp_populate_vals.tbl_id=dp_populate_tables.tbl_id - WHERE dp_populate_tables.tgt_tbl=dp_populate_vals.tgt_tbl; ERROR 1109: Unknown table 'dp_populate_tables' in where clause mysql select * from dp_populate_tables; ++-+-+ | tbl_id | tgt_tbl | last_populated | ++-+-+ | 1 | dp_dbusage | -00-00 00:00:00 | | 2 | dp_logusage | -00-00 00:00:00 | ++-+-+ 2 rows in set (0.01 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Tying records together across mulitple tables.
I've worked through some of this but still would like some opinions. Maybe it's not clear but I haven't received any responses. Basically to tie the records together I will use the recordID (auto incrementing) in every table where the records are written. Then I can grab everything out of that recordID. I'm not sure , can't resolve in my mind if this is a M2M or something else. I have 5 tables, users may enter multiple records in each table. The only trick is (for me) is how to tie a unified record together across all of them. I'll try to illustrate, and only use 2 tables to keep it brief. Table1 - Bob has 3 records 1st record - Spoken Language is Spanish 2nd record - Spoken Language is French 3rd record - Spoken Language is English Table 2 - Bob has 3 records 1st record - I am Spanish 2nd record - I am French 3rd reocrd - I am English Okay the table strutures: Table1Table2 RecordID (int, autoinc) RecordID (int, autinc) MemberID (int)MemberID (int) Language (varchar)Nationality (varchar) I'm trying to say here is a record , that would form the result of I am Bob, I speak English, I am English I know, this probably sounds a bit weird :) Best way I can come up with right now to illustrate. If someone was searching through records, they would say I found someone who is Spanish and yes, they are Spanish. Not, I found someone who is Spanish and they speak Spanish , French and English. I considered (as this is part of a web site) generating an ID and then passing it into each table entry as the forms (that comprise the process) are submitted. Just to clarify, 5 tables - 5 forms , all part of 1 web entry. Stuart -- 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: Unknown table ... in where clause
I'm trying to update existing records in one table so that value of a column winds up matching the value of a column in a second table, based on a match in a second column in each table. I'm not sure if I'm taking the wrong approach on this, of if I'm missing something simple in the SQL syntax (like this being illegal), but I'd appreciate any feedback on why MySQL is returning the Unknown table error when the table does indeed exist (see output below). I can do this manually (v. small number of records involved) but for cases where doing it by hand would be impractical, it would be helpful to be able to do this without writing a script. Thanks, Ted mysql UPDATE dp_populate_vals - SET dp_populate_vals.tbl_id=dp_populate_tables.tbl_id - WHERE dp_populate_tables.tgt_tbl=dp_populate_vals.tgt_tbl; I think: update dg_populate_vals v set v.tbl_id = (select t.tbl_id from dp_populate_tables t where t.tgt_tlb = v.tgt_table) With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unknown table ... in where clause
I guess the docs aren't totally clear on this as you are not the first person to have problems forming a multiple-table UPDATE statement. http://dev.mysql.com/doc/mysql/en/UPDATE.html UPDATE dp_populate_vals INNER JOIN dp_populate_tables ON dp_populate_tables.tgt_tbl=dp_populate_vals.tgt_tbl SET dp_populate_vals.tbl_id=dp_populate_tables.tbl_id Shawn Green Database Administrator Unimin Corporation - Spruce Pine Ted Byrne [EMAIL PROTECTED] wrote on 10/04/2004 03:45:20 PM: Greetings, I'm trying to update existing records in one table so that value of a column winds up matching the value of a column in a second table, based on a match in a second column in each table. I'm not sure if I'm taking the wrong approach on this, of if I'm missing something simple in the SQL syntax (like this being illegal), but I'd appreciate any feedback on why MySQL is returning the Unknown table error when the table does indeed exist (see output below). I can do this manually (v. small number of records involved) but for cases where doing it by hand would be impractical, it would be helpful to be able to do this without writing a script. Thanks, Ted mysql UPDATE dp_populate_vals - SET dp_populate_vals.tbl_id=dp_populate_tables.tbl_id - WHERE dp_populate_tables.tgt_tbl=dp_populate_vals.tgt_tbl; ERROR 1109: Unknown table 'dp_populate_tables' in where clause mysql select * from dp_populate_tables; ++-+-+ | tbl_id | tgt_tbl | last_populated | ++-+-+ | 1 | dp_dbusage | -00-00 00:00:00 | | 2 | dp_logusage | -00-00 00:00:00 | ++-+-+ 2 rows in set (0.01 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tying records together across mulitple tables.
Tables are tied together by whichever field(s) you use to store their parent's reference. For one second, imagine I am writing an inventory control program for somebody like Wal-Mart or Target. Those businesses have so many locations that they are divided into regions, each region will have multiple warehouses, each region would also have multiple stores. Each store could be within supply range of several warehouses. Each warehouse can supply several stores. That gives us the following relationships: 1 region : many warehouses 1 region : many stores many stores : many warehouses CREATE TABLE region ( ID int auto_increment, Name varchar(20) primary key, key(id) ) CREATE TABLE warehouse ( ID int auto_increment, region_id int not null, Name varchar(50) primary key, key(ID) ) CREATE TABLE store ( ID int auto_increment, region_id int not null, Name varchar(50) primary key, key(ID) ) CREATE TABLE stores_warehouses ( store_id int not null, warehouse_id int not null, primary key (store_id, warehouse_id) ) I left out a lot of other useful information (like addresses, phone numbers, etc) but I think you see the basic structure. The warehouse table relates to the region table through the field region_id. The store table relates to the region field through the field region_id. The stores and warehouses relate to each other through the table stores_warehouses and their respective ID columns. It appears to me that your tables are similar in organization but you have: 1 member : many table1's (bad choice of table name. shouldn't this be member_language?) 1 member : many table2's (maybe you could call this member_nationality ?) It seems that their common fact is that they both refer to the same member. That's how their records are related, by their common member. The other 3 tables are also contains lists of things that belong to the member, right? Please post the unadulterated results of SHOW CREATE TABLE for each of these 6 tables and we can help you formulate the query you want to write. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Stuart Felenstein [EMAIL PROTECTED] wrote on 10/04/2004 03:46:49 PM: I've worked through some of this but still would like some opinions. Maybe it's not clear but I haven't received any responses. Basically to tie the records together I will use the recordID (auto incrementing) in every table where the records are written. Then I can grab everything out of that recordID. I'm not sure , can't resolve in my mind if this is a M2M or something else. I have 5 tables, users may enter multiple records in each table. The only trick is (for me) is how to tie a unified record together across all of them. I'll try to illustrate, and only use 2 tables to keep it brief. Table1 - Bob has 3 records 1st record - Spoken Language is Spanish 2nd record - Spoken Language is French 3rd record - Spoken Language is English Table 2 - Bob has 3 records 1st record - I am Spanish 2nd record - I am French 3rd reocrd - I am English Okay the table strutures: Table1Table2 RecordID (int, autoinc) RecordID (int, autinc) MemberID (int)MemberID (int) Language (varchar)Nationality (varchar) I'm trying to say here is a record , that would form the result of I am Bob, I speak English, I am English I know, this probably sounds a bit weird :) Best way I can come up with right now to illustrate. If someone was searching through records, they would say I found someone who is Spanish and yes, they are Spanish. Not, I found someone who is Spanish and they speak Spanish , French and English. I considered (as this is part of a web site) generating an ID and then passing it into each table entry as the forms (that comprise the process) are submitted. Just to clarify, 5 tables - 5 forms , all part of 1 web entry. Stuart -- 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: Using Visio to diagram MySQL db, export SQL
Is there a version or a product available for LINUX? On Tue, 28 Sep 2004 13:55:19 -0500, Tim Hayes [EMAIL PROTECTED] wrote: ari MYdbAL which you can download at www.it-map.com is completely FREE and includes data modeling, DDL generation or whatever you need to create your MYSQL database. You might consider taking a look at DBDesigner4, which does a good job with a number of databases. http://www.fabforce.com/dbdesigner4 Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
counting records and JOINS
Hi,
I have a process that relies on three tables. There is a Process
table, a Process_description table and a third table that holds some
display decoration hints that depend on the perceived nature of each
process.
The decoration table is static. It looks like
decoration
{
severity_level int
label VARCHAR(30)
decoration VARCHAR(128)
}
the Process table looks like
Process
{
id int auto_increment
last_process_description int
...
}
Process_description
{
idint auto_increment
Process_idint
decoration_id int
...
}
What the above means to me is that a Process entry may have zero or
more Process_description entries linked to it through the
Process_description.Process_id field. Each Process_description will
have a decoration entry.
All this is peachy. I realized early that I would want to get
access to the LAST Process_description record entered so I added the
Process.last_process_description field to hold it. When I INSERT new
Process_description records I UPDATE the matching Process table entry
and set the new last_process_description field.
All of this is still peachy. I have this SELECT with two LEFT JOINS
that pulls the necessary data from all three tables and sets it all up
for me the way I want. That is I get every Process record in a
certain order that also contains the necessary data from the last
Process_description entry added (if there was one) and the display
hints for that record.
Time has passed and everyone has been happy. Until today. Now my
people would like to see everything they currently see plus the count of
Process_description records that go with each Process record.
I'm drawing a blank. Can this be done?
Here is a simplified query similar to the one I am doing:
SELECT P.id,
P.date,
D.browser_decoration,
PD.entry_date
FROM Process P
LEFT JOIN Process_description PD
ON P.last_logbook_entry_id = PD.id
LEFT JOIN Decoration D
ON D.level = PD.severity
What I'm hoping for is something like:
SELECT P.id,
P.date,
D.browser_decoration,
PD.entry_date
COUNT(PD.*)
FROM Process P
LEFT JOIN Process_description PD
ON P.last_logbook_entry_id = PD.id
LEFT JOIN Decoration D
ON D.level = PD.severity
How should I do this? Is there a better way to do any of this?
All the best,
Dean...K...
--
Dean Karres / karres at itg dot uiuc dot edu / www.itg.uiuc.edu
Imaging Technology Group / Beckman Institute
University of Illinois
405 North Mathews / Urbana, IL 61801 USA
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Indexing for OR clauses
Thanks for the suggestions over the weekend! I will be looking in to this in a few days - for now I think I am just going to have to re-write my PHP script to make 2 separate queries and array_merge() them - which invariably seems to solve OR problems. But there should be a better way, no? -Dave -Original Message- From: David Turner [mailto:[EMAIL PROTECTED] Sent: October 1, 2004 4:04 PM To: [EMAIL PROTECTED] Subject: Indexing for OR clauses Wondering if anyone can give me advice on indexing for OR clauses. I have a table with a number of fields, two of which are sender_id and receiver_id. I also have a query such as this: SELECT ... WHERE (sender_id = 98765 OR reciever_id = 98765) The query is OK for a limit of 10, but if I increase that to 25, it becomes inordinately slower (it is a large table). Is it better for me to have two separate indexes, one for each of sender_id and receiver_id, or one index with both sender_id and receiver_id? Or should I just avoid the use of OR? Or am I simply missing something? -Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order rows before applying GROUP BY / DISTINCT
If you aren't tired of this problem, I have one more question... The last query worked well, except one small detail... SELECT user, refurl, max( entry ) AS most_recent, count( user ) AS visits FROM userlog WHERE user != '' GROUP BY user ORDER BY most_recent DESC LIMIT 10 I am now including ONE extra field: refurl. MySQL doesn't grab the refurl from the SAME row as it grabs the last entry time! I would like to be able to get this extra field (and maybe others too), and as they are potentially different from time to time, just any of them won't do it. I could probably get around the problem with a join on the time column (maybe I should create an index to enhance efficiency?), but I wonder if there is a more efficient way to solve this? Additionally, IF there were two entries at the same time (which is highly improbable, and not critical at all if it happens once in a million) I might not get the correct result anyway. Maybe I'm being picky, but while I'm at it it might as well get well done. Thank you for the help so far anyway!! Remi Mikalsen E-Mail: [EMAIL PROTECTED] URL:http://www.iMikalsen.com On 4 Oct 2004 at 10:22, [EMAIL PROTECTED] wrote: I would be very surprised if this query fails as you say it does. select user, max(time) as most_recent, count(user) as visits from userlog where user!='' group by user order by most_recent desc limit 10; This represents the last 10 users to sign in, when they signed in last, and many times they have signed in so far. I may not know everything but unless you have some problem with the data so that your datetime field time is not acting the same for all visitors, this should work. Please describe why this data is wrong for your question. It is entirely likely I misunderstood what you wanted to find in your data. That would make my suggestions wrong (my apologies if so!!) Shawn Green Database Administrator Unimin Corporation - Spruce Pine Remi Mikalsen [EMAIL PROTECTED]wrote on 10/04/2004 10:11:09 AM: Thank you for the answer! However, it doesn't seem to solve my problem. Running the query without modifications repeated users (because of the group by user, HOST). I removed the HOST, and ran the query over again. Now it returned unique users, but it still didn't return the LAST login of them many logins for each user. Currently I am using a new implementation with two queries and PHP in between. I won't write all the code here, just explain the basics. 1. A query gets the total number of logins for each user 2. PHP retrieves the logins and orders them with a perfect hash - I create an array with indexes correspondig to usernames - the values in the array are the numbers of logins corresponding to each username - this way I have practically no overhead when accessing the array for example: $users['a_username'] = 10; /* 10 logins by user a_username */ 3. Another query gets all logins ordered by the time column, last first 4. PHP reads the results. When a user is found that has more than 0 logins in the $users array, I print the user and the amount of logins, and set the value in the $users array to 0. This guarantees only printing a user once, and only printing the last login due to ordering by the time field. But this also has obvious drawbacks as to performance. I may have to retrieve 10.000 rows from the userlog table to get 10 unique users, or I might never have 10 unique users in the table, even with 1.000.000.000 records! I just never know. I also have to make two queries, which gives a small overhead. Finally, using scripting to interprete intermediate results is much slower than MySQL. All in all, this just doesn't seem to be a very scaleable solution. Remi Mikalsen E-Mail: [EMAIL PROTECTED] URL: http://www.iMikalsen.com On 4 Oct 2004 at 9:14, [EMAIL PROTECTED] wrote: What you are looking for is the MAX() of the Time column (most recent entry) select user, host, max(time) as time, count(user) as visits from userlog where user!='' group by user, host order by time desc limit 10; The issue you ran into is caused by an non-standard SQL extension created by MySQL. Its behavior is documented here: http://dev.mysql.com/doc/mysql/en/GROUP-BY-hidden-fields.html SOAPBOX ON IMHO, this extension of the SQL spec has caused more problems than it has helped. I believe a better extension would have been to add a new GROUP BY predicate like FIRST() or LAST() so that the behavior would be explicit and controllable. It is even documented that the value returned is potentially random Do not use this feature if the columns you omit from
Trouble compiling 4.0.21 under amd64 Debian unstable
A heads-up and a call for advice for anyone compiling from source on Linux amd64: The latest Debian amd64 unstable upgrade of libc6-dev to version 2.3.2.ds1-17 broke compiling MySQL 4.0.21 from source using gcc-3.4. (Also breaks similarly with the default gcc for this platform, gcc-3.3.4.) Specifically, the comment at the top of /usr/include/pthread.h no longer begins with the word LinuxThreads, so configure no longer thinks LinuxThreads is there at all and the configure process stops. Guessing that it's just a comment change and not a capability change, I forced configure to believe LinuxThreads was there, and configured and compiled normally, but then make test quickly ran into a problem: TEST RESULT -- alias [ pass ] ./mysql-test-run: line 1119: 25903 Segmentation fault (core dumped) $@ $CUR_MYERR 21 (wd: /usr/src/mysql-4.0.21) alter_table[ fail ] Prior to the libc6-dev upgrade, configuration and compilation with gcc-3.4 worked smoothly, passed all tests, and has been working well for us. The MySQL binaries work for us. We were compiling with gcc-3.4 because the precompiled binaries section of the manual says it uses gcc-3.2.1 for amd64, and as I understand it gcc-3.4 handles the Opteron better than 3.2. Perhaps LinuxThreads really isn't there? Perhaps something else changed about threading? Maybe there's a more robust test for LinuxThreads than the comment in pthread.h? My configure line looks like: === # Comment these out to use default compiler. export CXX=g++-3.4 export CC=gcc-3.4 ./configure --prefix=/usr/local/mysql \ --exec-prefix=/usr/local/mysql \ --with-mysqld-ldflags=-all-static \ --disable-shared --enable-thread-safe-client \ --with-extra-charsets=all === Any advice appreciated. Thanks, -- Pete Harlan [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unknown table ... in where clause
At 04:05 PM 10/4/2004, [EMAIL PROTECTED] wrote: I guess the docs aren't totally clear on this as you are not the first person to have problems forming a multiple-table UPDATE statement. Thanks for the quick response. It's somewhat gratifying to hear that I'm not alone in my confusion... Ted -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Real Basic Question
I am a beginner, so forgive me. From mysql if I type: Mysqlshow tables; The result scrolls to the last lines and then the prompt again. Is there an argument or a variable I can set that will act like 'more' or 'less' in unix? I want it to stop scolling so I can walk through the results. Thanks, Brent
Re: Real Basic Question
Mysql \P less PAGER set to less Mysql show tables; You can see other cool flags by typing 'help;' at the mysql prompt. On Mon, 4 Oct 2004 14:58:29 -0700, OMalley, Brent [EMAIL PROTECTED] wrote: I am a beginner, so forgive me. From mysql if I type: Mysqlshow tables; The result scrolls to the last lines and then the prompt again. Is there an argument or a variable I can set that will act like 'more' or 'less' in unix? I want it to stop scolling so I can walk through the results. Thanks, Brent -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Real Basic Question
Thanks! -Original Message- From: Kyle Kirkland [mailto:[EMAIL PROTECTED] Sent: Monday, October 04, 2004 3:15 PM To: OMalley, Brent Cc: [EMAIL PROTECTED] Subject: Re: Real Basic Question Mysql \P less PAGER set to less Mysql show tables; You can see other cool flags by typing 'help;' at the mysql prompt. On Mon, 4 Oct 2004 14:58:29 -0700, OMalley, Brent [EMAIL PROTECTED] wrote: I am a beginner, so forgive me. From mysql if I type: Mysqlshow tables; The result scrolls to the last lines and then the prompt again. Is there an argument or a variable I can set that will act like 'more' or 'less' in unix? I want it to stop scolling so I can walk through the results. Thanks, Brent -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Does anyone has the experience on migrating mysql server from 32bit sysem to a 64bit system?
Does anyone has the experience on migrating mysql server from 32bit sysem to a 64bit system? Can data files(from 32bit system) be readed properly by mysql server after copy to a 64bit system? Thank you in advance! --Alex __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [OT] HP DL760G2 vs Opteron for large DB on Linux
It's a great server (the DL760 G2). They really opened up the bus and the Xeon 3.0 is superfast. darren [EMAIL PROTECTED] 10/03/2004 07:51 PM To: [EMAIL PROTECTED] cc: Subject:[OT] HP DL760G2 vs Opteron for large DB on Linux Hi all, I am looking to purchase hardware for a large database running MySQL or Oracle on Linux. Was thinking of the Opteron for its good scalability and memory bandwidth until my friend told me that the DL760 from HP that uses the F8 (from Intel Profusion) chipset allowing up to 8 CPUs and memeory up to 32GB. I have always though that the Xeons cannot go beyond 4 CPUs and 4GB but this changes the equation. Has anyone got experience with this server or similar ones? How is the performance for running databases, esp in terms of large RAM usage? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Dyna III electronic ignition install questions R80/7
Anyone have experience with installing the Dyna III electronic ignition? I put it on my R80/7, but since it wasn't running before I did the install, and it's not running now, I'm not sure if I installed it correctly or not. But, after I did the install, I tried to do the static timing as per the instructions that came with it, and i can't get the test light to come on at all which says something, I think. Any help appreciated. Thanks, Don
Re: Does anyone has the experience on migrating mysql server from 32bit sysem to a 64bit system?
The safest thing you can do to protect floats and the like is to use mysqldump to dump them to a .sql on the 32 bit system then import them again on the 64 bit system. -Eric On Mon, 4 Oct 2004 16:38:13 -0700 (PDT), alex ye [EMAIL PROTECTED] wrote: Does anyone has the experience on migrating mysql server from 32bit sysem to a 64bit system? Can data files(from 32bit system) be readed properly by mysql server after copy to a 64bit system? Thank you in advance! --Alex __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- Eric Bergen [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Reg REstore
Hi, Thank you backup is working fine. Any one can help me how to restore the back up database?. Not at table level. At the database level. How to run a sql file from mysql prompt?. While restoring any more things to be taken care, regarding database?. Thanks, NARasimha -Original Message- From: Hassan Schroeder [mailto:[EMAIL PROTECTED] Sent: Mon 10/4/2004 10:38 PM To: Lakshmi NarasimhaRao (WT01 - TELECOM SOLUTIONS) Cc: Subject: Re: Reg Backup [EMAIL PROTECTED] wrote: That i donot know, that is what i want to know, is mysqldump works fine with MyISM in windows for backup?. Please confirm. Yes, it works fine. And if you're concerned about Windows and you have a copy running, it wouldn't be that hard to test, eh? :-) -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. 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.
Re: Does anyone has the experience on migrating mysql server from 32bit sysem to a 64bit system?
Thanks Eric. But our data is very big(about 400G), server don't have enough disk space and it will take long time to finish the dump. It may cause problem to floats if just copy data files to a 64 bit system? --Alex Eric Bergen [EMAIL PROTECTED] wrote: The safest thing you can do to protect floats and the like is to use mysqldump to dump them to a .sql on the 32 bit system then import them again on the 64 bit system. -Eric On Mon, 4 Oct 2004 16:38:13 -0700 (PDT), alex ye wrote: Does anyone has the experience on migrating mysql server from 32bit sysem to a 64bit system? Can data files(from 32bit system) be readed properly by mysql server after copy to a 64bit system? Thank you in advance! --Alex __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- Eric Bergen [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish.
Re: Does anyone has the experience on migrating mysql server from 32bit sysem to a 64bit system?
I'm by no means an expert on this, but some things you might want to consider... An alternative method to not impact the disk space on the 32-bit system: If you have connectivity between the 32-bit and 64-bit systems is to use mysqldump on the 64-bit system and specify the 32-bit host using the -h switch. This could take quite a while, depending on the throughput you can get across your network. (It's also possible that you may not have adequate disk space on the new system.) However, it would effective at preserving your data. It would be worthwhile testing this on a small subset of your data, with and without the -C switch. It might also be possible to pipe the mysqldump output to gzip so that the size of your resultant disk file(s) would be minimized. Ted At 11:20 PM 10/4/2004, alex ye wrote: Thanks Eric. But our data is very big(about 400G), server don't have enough disk space and it will take long time to finish the dump. It may cause problem to floats if just copy data files to a 64 bit system? --Alex Eric Bergen [EMAIL PROTECTED] wrote: The safest thing you can do to protect floats and the like is to use mysqldump to dump them to a .sql on the 32 bit system then import them again on the 64 bit system. -Eric On Mon, 4 Oct 2004 16:38:13 -0700 (PDT), alex ye wrote: Does anyone has the experience on migrating mysql server from 32bit sysem to a 64bit system? Can data files(from 32bit system) be readed properly by mysql server after copy to a 64bit system? Thank you in advance! --Alex __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- Eric Bergen [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does anyone has the experience on migrating mysql server from 32bit sysem to a 64bit system?
Thanks for all your help! Ted Byrne [EMAIL PROTECTED] wrote: I'm by no means an expert on this, but some things you might want to consider... An alternative method to not impact the disk space on the 32-bit system: If you have connectivity between the 32-bit and 64-bit systems is to use mysqldump on the 64-bit system and specify the 32-bit host using the -h switch. This could take quite a while, depending on the throughput you can get across your network. (It's also possible that you may not have adequate disk space on the new system.) However, it would effective at preserving your data. It would be worthwhile testing this on a small subset of your data, with and without the -C switch. It might also be possible to pipe the mysqldump output to gzip so that the size of your resultant disk file(s) would be minimized. Ted At 11:20 PM 10/4/2004, alex ye wrote: Thanks Eric. But our data is very big(about 400G), server don't have enough disk space and it will take long time to finish the dump. It may cause problem to floats if just copy data files to a 64 bit system? --Alex Eric Bergen wrote: The safest thing you can do to protect floats and the like is to use mysqldump to dump them to a .sql on the 32 bit system then import them again on the 64 bit system. -Eric On Mon, 4 Oct 2004 16:38:13 -0700 (PDT), alex ye wrote: Does anyone has the experience on migrating mysql server from 32bit sysem to a 64bit system? Can data files(from 32bit system) be readed properly by mysql server after copy to a 64bit system? Thank you in advance! --Alex __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- Eric Bergen [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - Do you Yahoo!? Yahoo! Mail - You care about security. So do we.
