Re: Mysql docs
on 11/16/05 7:06 PM, Jasper Bryant-Greene at [EMAIL PROTECTED] wrote: Scott Haneda wrote: Google this: subselect site:dev.mysql.com And I get mostly non English stuff, limiting to english and I get a whopping 37 pages, none of which seem to help me much. Try googling for subquery, considering that's what they're called... Regardless, I still can not find any concrete definitions as to what versions support it and what do not. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql docs
Scott Haneda wrote: Hello, with the release of 5, I seem to have all dead links to my old docs for mysql 4. The way they keep changing the urls is a little maddedning :-) I usually search using: http://www.mysql.com/keyword http://www.mysql.com/subquery will lead to: http://dev.mysql.com/doc/refman/5.0/en/subqueries.html This will lead to the 5.0 documentation, but changing the '5.0' in the url to '4.1' will give you the 4.1 documentation. http://dev.mysql.com/doc/refman/4.1/en/subqueries.html Quite easy I think... Kind regards, Jigal. smime.p7s Description: S/MIME Cryptographic Signature
Re: Foreign characters in the shell
Hello Gleb! The answer to the scrambled characters in the shell und XP is, as you assumed, that the shell simply does not handle characters like the Windows-GUI does... . Now, that I know about this fact it's no longer a problem ( perhaps a feature, ;-) ?). Thx again have a good time, Goetz -- -- ! Diese E-Mail hat 0 Anhänge -ABSENDER- name Goetz M. Ritter country Germany e-mail [EMAIL PROTECTED] -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to search the manual
sheeri kritzer schrieb: Hi folks, You might be amazed getting answers that link right to the MySQL documentation. The documentation is fabulous, but of course we cannot memorize it! One tip I picked up in a MySQL course (which was worth the $$ of the course itself), which is IMMENSELY useful, is the following: ... Hello Sheeri, Thanks a lot for these hints (especially because they are completely free of charge, :-) )! Regards, Goetz -- ! This E-Mail has no attachments -ABSENDER--- name Goetz M. Ritter country Germany e-mail [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Money making plan
Kyle, you're a twat for sending s**t like this to this list... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Money making plan
Don't know if that's the word I'd use, but credibility is lost for that nonsense. No proffesionalism whatsoever. If I were admin I'd ban for less. - Sent from my NYPL BlackBerry Handheld. - Original Message - From: Raz [EMAIL PROTECTED] Sent: 11/17/2005 05:08 AM To: kyle [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: Re: Money making plan Kyle, you're a twat for sending s**t like this to this list... -- 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: Money making plan
Yeah, sorry for that language, but I couldn't believe what I had just read and lost it... On 17/11/05, Peter J Milanese [EMAIL PROTECTED] wrote: Don't know if that's the word I'd use, but credibility is lost for that nonsense. No proffesionalism whatsoever. If I were admin I'd ban for less. - Sent from my NYPL BlackBerry Handheld. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Innodb Crash on 2 concurrent select
On Wednesday, 16 November 2005 at 20:40:35 +0700, Ady Wicaksono wrote: I have MySQL with about 12 billion rows when i try to create 2 process, each select count(*) on the same table after a long time about 30 minutes it crashed :( ANy information? ... InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html InnoDB: about forcing recovery. mysqld got signal 11; This is obviously a bug. I've just checked the bug database, but I don't see a report on it yet. Could you please enter one? Greg -- Greg Lehey, Senior Software Engineer MySQL AB, http://www.mysql.com/ Echunga, South Australia Phone: +61-8-8388-8286 Mobile: +61-418-838-708 Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
using dinamic table name inside stored procedure
I need to pass to a stored procedure a var a table name. But I don't known how use the variable in the sql statement. If I use the method described belowe I get an error, the procedure use the variable name as the name of the table. Someone can help ? CREATE FUNCTION `myFunc`( tbl_name varchar(100)) RETURNS int(11) BEGIN declare dt1 timestamp default 0; select max(dt_mod) into dt1 from tbl_name; END $$ select myFunc( 'pmv_status' ); Error Code : 1146 Table 'pmv_manager.tbl_name' doesn't exist (0 ms taken) Regards, Enzo Arlati [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
dynamic fieldname to assign to
Hi All, I have a simple problem and hope you can help me. I have an input table Updates with various fields, incl. Month and MonthlyValue. The field Updates.Month ranges from 1 to 12. I also have a target Data table with 12 fields, called Jan, Feb, Mar, ... `Dec`. Depending on Updates.Month the MonthlyValue must be put in the proper Data field. Now I use 12 UPDATE queries, like: UPDATE Data INNER JOIN Updates ON ... SET Data.Jan = Updates.Cell WHERE Updates.Month = 1; ... UPDATE Data INNER JOIN Updates ON ... SET Data.Dec = Updates.Cell WHERE Updates.Month = 12; My question: can it be done in a single query ? Thanks in advance, Cor
Re: How to reload my.cnf?
uhh.NO! That's what databases are for, to remember things. They do that by writing data to disk and reading it back to you when you ask for it. What exactly do you think you will be losing by stopping and restarting the server daemon (service to you MS-types). What data do you think only exists in the RAM of the machine and hasn't been written to disk? Shawn Green Database Administrator Unimin Corporation - Spruce Pine Tedy Aulia [EMAIL PROTECTED] wrote on 11/17/2005 12:53:25 AM: Hi Gleb, I understand that the replication shouldn't break, but restarting master means losing the updates made to the master database, which we can't afford to do that. TA Hello. Why are you afraid of shutting down the server for a while? Is it so critical? If yes, send more detailed description of your situation. Replication shouldn't break. From: http://dev.mysql.com/doc/refman/5.0/en/replication-features.html It is safe to shut down a master server and restart it later. Tedy Aulia wrote: Hi Gleb, Thanks for your reply. I am using MySQL 3.23.33. The ones that I will need to add in my.cnf are the syncronisation parameters as follow: binlog-do-db=XYZ replicate-do-db=XYZ replicate-ignore-table=XYZ.table1 replicate-ignore-table=XYZ.table2 replicate-ignore-table=XYZ.table3 Database XYZ was in synch for the past 12 months, I have found database is not in synch anymore as the parameters I mentioned above are missing in my.cnf. I want to put back those parameters but I can't afford to restart the server as MySQL server is also used by other database which is currently in synch and runs happily. Cheers, TA Hello. You can change some variables without restarting the server. See: http://dev.mysql.com/doc/refman/5.0/en/dynamic-system-variables.html Tedy Aulia wrote: Hi All, I will need to change my.cnf in master machine, but I can't afford to restart MySQL server as the server has been used for heavy traffic databases. Can anyone tell me how to do it? Cheers, *Tedy Aulia*
Re: Background tasks performed by MySQL?
Hi, and thank you both for valuable tips. The MySQLd in question runs on a mailserver, and a large amount of processes (Postfix, Maildrop and Squirrelmail) connect to it, run one or two simple queries, and then disconnects. There is only one client that is constantly connected, namely a configuration server. It does not have a cache, and only asks a few small questions every now and then. I have modified the code so that it logs any SQL-queries that take more than 2 clock seconds, which should show me if the config server is the culprit. I tried SHOW INNODB STATUS when MySQLd was taking 100% of one CPU, and the Main thread state was sleeping. Also from the same command all TRANSACTIONs seemed idle except for one, which was running my SHOW INNODB STATUS command. I draw the conclusion that whatever is happening, it's not InnoDB. Since all the tables that the configuration server uses are in InnoDB, it also seems likely that the config server is not the culprit either. Is there any way for me to find out exactly what queries have been run in the last X minutes? When the load goes up, I could check to see what queries ran before, to possibly find a pattern. If I could temporarily log queries and the time they took to complete, that would also be a good way forward. The General Query log doesn't seem to log the time a query took (as I read the manual). I have a cron-job that logs the current machine load and a SHOW STATUS every five minutes. I just awk:ed through it, and I might imagine it, but there is a possible connection between the Max_used_connections parameter increasing and the machine's load going up. Could a massive storm of connections be causing the slowdown? Some form of lock contention having to do with new connections or similar? Can I reset the Max_used_connections parameter so I get a maxlevel for the last five minutes rather than since the last restart? Lots of questions, so I am deeply grateful for any insights into any of them, /Viktor... On Nov 16, 2005, at 00:48, Heikki Tuuri wrote: Hi! Also look at SHOW INNODB STATUS\G during the slow phase. What does it say about the 'Main thread ... state'? What does it say about transactions? Best regards, Heikki Oracle Corp./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 - Original Message - From: Bruce Dembecki [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, November 15, 2005 5:08 PM Subject: Re: Background tasks performed by MySQL? I would expect this to finally be something on the client end, rather than the server end... is there a search index that gets rebuilt periodically? Maybe some reports that get generated against the data? The last example that comes to my mind is if you use a client that caches data, does the cache get dumped or the client restarted at some point? These are the sort of things that we found led to the type of behavior you are talking about. Let me give clearer examples... Our databases typically do Discussion Boards... usually very large scale discussion boards (think eBay or HBO scales). The discussion board server (in this case the database client) keeps it's own search index, but need to update it on a regular basis to keep it current. If that period is too infrequent or the queries poorly optimized, they can generate a lot of load on the database, and you get the type of results you are seeing. Or if the discussion board tries to analyze the stats for the last day (or week or month etc) to provide information for reports... in our example a million page views a day means a million stats records a day, and any analysis can be quite the load generator. Same thing with our cache on our discussion board... if our discussion board has been up for some time it has all the messages most frequently used already in local cache, it doesn't do a query to recover each message in this situation... an instance of the discussion board going live into production with no data in the cache can mean a huge database hit for a few minutes while the caches in the discussion board get populated. These are just examples from our life, but I'm pretty sure when al is said and done that the cause will be some process that your client is generating to do something periodic, rather than the MySQL Server running some sort of process, which we've never seen. Take a look at the process list when it is in one of these cycles (from the mysql command line client type show processlist;). it should give you a pretty good idea of what's doing what at the time and will give you some idea on where to look. Best Regards, Bruce On Nov 15, 2005, at 2:20 AM, Viktor Fougstedt wrote: Hi. We have a MySQLd with both MyISAM and InnoDB tables that at uneven intervals stops responding
A bit of SQL help for a MySQL novice.
Hello All. I am hoping for a bit of help with some code that has really given me some trouble. If this is not he correct forum for this any help in pointing me to a more suited list would be appreciated. I have a MySQL 4.1.x database containing records with phone numbers. Most of the phone numbers are enter in 12035551212 format, but some are entered with spaces or - or ( or other characters. I need to read the first 4 positions in the phone number to determine it's location. My statement looks like this: 'Select mid(phone, 1,4) as phoneareacode from phonetable' This works but if the number is entered as 1(203)-555-1212 the above would return 1(20 which is not what I am looking for. Is there a way to have the select statement examine only numeric values in the phone number so it would disregard the other charcters? In Lasso, you can use a Replace with a Regular Expression function to have just the digits 0-9 examined but haven't been able find a way to do this in SQL. Any help is appreciated. Thank you. Rick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joins - where clause, booking system
Not sure what you are asking for. If you are trying to get the opposite of what you are getting, try adding AND student_accom.start_date IS NULL to the end of the query. You can actually use just about any field instead of start_date. For easier readability, you can rephrase your query to clarify the join. SELECT distinct accomodation.accom_id FROM accomodation LEFT JOIN student_accom ON accomodation.accom_id = student_accom.accom_id AND ((student_accom.start_date Between '2005-10-01' And '2005-11-30') OR (student_accom.leave_date Between '2005-10-01' And '2005-10-30')) WHERE student_accom.start_date IS NULL It may actually end up being faster by moving your original WHERE to the join. On Nov 16, 2005, at 11:54 PM, [EMAIL PROTECTED] wrote: Hello, I'm running mysql 4.0.12 I have a bookings database in which I'm having problems pulling out the available accomodation. Tables are: Accomodation - accomid (primary key), town, address, contactnumber etc student_accom - studentaccomid (primary key), accomid, studentid My query is as follows so far, this is pulling out all the accomodation that is booked between specified dates - not what I'm after - SELECT distinct accomodation.accom_id FROM accomodation LEFT JOIN student_accom ON accomodation.accom_id = student_accom.accom_id where ((student_accom.start_date Between '2005-10-01' And '2005-11-30') OR (student_accom.leave_date Between '2005-10-01' And '2005-10-30')) Appreciate any assistance. Thanks Gavin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [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: A bit of SQL help for a MySQL novice.
Rick Dwyer [EMAIL PROTECTED] wrote on 11/17/2005 10:28:51 AM: Hello All. I am hoping for a bit of help with some code that has really given me some trouble. If this is not he correct forum for this any help in pointing me to a more suited list would be appreciated. I have a MySQL 4.1.x database containing records with phone numbers. Most of the phone numbers are enter in 12035551212 format, but some are entered with spaces or - or ( or other characters. I need to read the first 4 positions in the phone number to determine it's location. My statement looks like this: 'Select mid(phone, 1,4) as phoneareacode from phonetable' This works but if the number is entered as 1(203)-555-1212 the above would return 1(20 which is not what I am looking for. Is there a way to have the select statement examine only numeric values in the phone number so it would disregard the other charcters? In Lasso, you can use a Replace with a Regular Expression function to have just the digits 0-9 examined but haven't been able find a way to do this in SQL. Any help is appreciated. Thank you. Rick The problem is, you have dirty data making it very hard to use it as a search target. There are different ways of handling this: a) scrub your data (preferably during input or import) so that all phone numbers are stored in the exact same pattern b) store each number as its component parts (country code, city/area code, exchange, circuit) c) add a field of scrubbed data to your existing table and populate it. d) add fields for each part of the phone number to your tables and populate them. e) handle all searching and substring matching outside of SQL. Options a) and c) require that a clean copy of the data be stored in the database. That means that you pick a pattern and make all of your numbers look like that pattern. If, for instance, you get just the number 555-1212 you would need to generate something like 'x-xxx-555-1212' as a replacment (where the x is used to indicate missing information). This is slower to search on because you have to do substring matches but since phone numbers are already organized from least-specific to most-specific (left to right) it's already optimized for some types of substring matches. Options b) and d) provide the ability to index each part of a phone number. Here is an example breakdown: 1 (203) 555-1212 - country code:1, citycode:203, exchange: 555, circut: 1212 If any part of a number is missing, you can use a NULL value for that part... 555-1212 - country code: NULL, citycode: NULL, exchange: 555, circut: 1212 This would be all integer comparisons, VERY fast to search. If you have millions of phone numbers or international phone numbers, consider this schema. It may be hard to determine the foreign `exchange` and `circuit` parts. In that case just pick either the `exchange` or `circuit` field and put that whole portion of it in there. This takes more time to set up but is MUCH faster to search (how do you think the phone company does it?) Options b) and d) also allow you to have searchable data while preserving the original information. Option e) has the most flexibility but takes the database server out of the loop, which will destroy your search performance. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: A bit of SQL help for a MySQL novice.
- Original Message - From: Rick Dwyer [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, November 17, 2005 10:28 AM Subject: A bit of SQL help for a MySQL novice. Hello All. I am hoping for a bit of help with some code that has really given me some trouble. If this is not he correct forum for this any help in pointing me to a more suited list would be appreciated. I have a MySQL 4.1.x database containing records with phone numbers. Most of the phone numbers are enter in 12035551212 format, but some are entered with spaces or - or ( or other characters. I need to read the first 4 positions in the phone number to determine it's location. My statement looks like this: 'Select mid(phone, 1,4) as phoneareacode from phonetable' This works but if the number is entered as 1(203)-555-1212 the above would return 1(20 which is not what I am looking for. Is there a way to have the select statement examine only numeric values in the phone number so it would disregard the other charcters? In Lasso, you can use a Replace with a Regular Expression function to have just the digits 0-9 examined but haven't been able find a way to do this in SQL. Any help is appreciated. You have two basic options: 1. Make the data uniform in format so that it is easily accessed. 2. Let the users input the data in whatever format they like and then try to deal with it. It looks like you have opted for the second choice. If it were me, I'd _strongly_ prefer the first choice. I would put edits on the forms or applications that prompt the user for the phone number and force the input to match one format. For instance, if you prefer to see the phone number as one long string of numbers, e.g. 12025551212, either ignore any characters they type that aren't digits or strip out the punctuation characters afterwards. By the way, I'm _not_ saying that you should store the numbers as one long string; there are other options but I would choose the one that was going to be most useful to you based on your business requirements. If the area code is going to be important to you, as it appears from your question, it might be a good idea to store it in a separate column. For instance, you could put the country code (the '1') in a Country_Code column, put the area code in an Area_Code column, put the 7 digit number in its own column, and then put the extension (if applicable) in yet another column if that would help you. Beware of foreign phone numbers though because they don't look like US ones (and don't make the mistake of thinking that the '1' at the beginning of the phone number automatically means the US; I'm in Canada and our phone numbers also start with 1, our area codes are also three digits, and the rest of the number is also 7 digits. Phone numbers in Europe or Africa or Asia follow rather different patterns that are shared by Canada and the US.) Now, your input routines _could_ mimic the way you store the phone numbers. For instance, if you want separate columns in the database for country code, area code, the rest of the number, and the extension (if any), you _could_ provide a separate field in your input form for each of those things. However, you don't have to do it that way; you could just as well put the full phone number in one input field and then split it out when you insert it into the database. That's up to you. But I would definitely use the input routines to force the phone numbers to follow whatever pattern you want it to have. It shouldn't be the database's job to handle this sort of thing, at least in my opinion. Of course, you'll want to fix the data that is already in the database, too. (If there are only a few rows in the table, you could do that manually. If not, you could write SQL to do it.) However, if you insist on allowing multiple formats for your phone numbers, the String Functions in MySQL should help you. Just look for them in the manual: http://dev.mysql.com/doc/refman/4.1/en/index.html (chapter 12). You may have to use a combination of functions to create new temporary versions of the phone number that don't have the punctuation but you can probably manage something, although it might be ugly. Another possibility is that you could write a user-defined function to strip the punctuation out of the phone numbers. See http://dev.mysql.com/doc/refman/4.1/en/adding-functions.html for information about this. Basically, you would write your own function in C or C++ to do this work for you, then plug the function into MySQL so that you can use it in your SQL. For instance, if you wrote a function called STRIP_PHONE_PUNCTUATION() and installed it in MySQL, your query would look like this: 'Select mid(STRIP_PHONE_PUNCTUATION(phone, 1,4)) as phoneareacode from phonetable' Your new function would give you a version of the phone number that had no punctuation, then the mid() function would find the area code for
Re: A bit of SQL help for a MySQL novice.
Unfortunately, the phone numbers come from text logs that get imported into mysql. Because the phone number is often displayed on a document for the customer, they will dictate how they want it to appear (i.e. with ( ) etc.). The phone logs simply record those values as they are so data will be entered in an unclean manner. Therefore I must deal with it on the backend. Thanks for the pointers. Rick On Nov 17, 2005, at 11:15 AM, Rhino wrote: - Original Message - From: Rick Dwyer [EMAIL PROTECTED] link.com To: mysql@lists.mysql.com Sent: Thursday, November 17, 2005 10:28 AM Subject: A bit of SQL help for a MySQL novice. Hello All. I am hoping for a bit of help with some code that has really given me some trouble. If this is not he correct forum for this any help in pointing me to a more suited list would be appreciated. I have a MySQL 4.1.x database containing records with phone numbers. Most of the phone numbers are enter in 12035551212 format, but some are entered with spaces or - or ( or other characters. I need to read the first 4 positions in the phone number to determine it's location. My statement looks like this: 'Select mid(phone, 1,4) as phoneareacode from phonetable' This works but if the number is entered as 1(203)-555-1212 the above would return 1(20 which is not what I am looking for. Is there a way to have the select statement examine only numeric values in the phone number so it would disregard the other charcters? In Lasso, you can use a Replace with a Regular Expression function to have just the digits 0-9 examined but haven't been able find a way to do this in SQL. Any help is appreciated. You have two basic options: 1. Make the data uniform in format so that it is easily accessed. 2. Let the users input the data in whatever format they like and then try to deal with it. It looks like you have opted for the second choice. If it were me, I'd _strongly_ prefer the first choice. I would put edits on the forms or applications that prompt the user for the phone number and force the input to match one format. For instance, if you prefer to see the phone number as one long string of numbers, e.g. 12025551212, either ignore any characters they type that aren't digits or strip out the punctuation characters afterwards. By the way, I'm _not_ saying that you should store the numbers as one long string; there are other options but I would choose the one that was going to be most useful to you based on your business requirements. If the area code is going to be important to you, as it appears from your question, it might be a good idea to store it in a separate column. For instance, you could put the country code (the '1') in a Country_Code column, put the area code in an Area_Code column, put the 7 digit number in its own column, and then put the extension (if applicable) in yet another column if that would help you. Beware of foreign phone numbers though because they don't look like US ones (and don't make the mistake of thinking that the '1' at the beginning of the phone number automatically means the US; I'm in Canada and our phone numbers also start with 1, our area codes are also three digits, and the rest of the number is also 7 digits. Phone numbers in Europe or Africa or Asia follow rather different patterns that are shared by Canada and the US.) Now, your input routines _could_ mimic the way you store the phone numbers. For instance, if you want separate columns in the database for country code, area code, the rest of the number, and the extension (if any), you _could_ provide a separate field in your input form for each of those things. However, you don't have to do it that way; you could just as well put the full phone number in one input field and then split it out when you insert it into the database. That's up to you. But I would definitely use the input routines to force the phone numbers to follow whatever pattern you want it to have. It shouldn't be the database's job to handle this sort of thing, at least in my opinion. Of course, you'll want to fix the data that is already in the database, too. (If there are only a few rows in the table, you could do that manually. If not, you could write SQL to do it.) However, if you insist on allowing multiple formats for your phone numbers, the String Functions in MySQL should help you. Just look for them in the manual: http://dev.mysql.com/doc/refman/4.1/en/ index.html (chapter 12). You may have to use a combination of functions to create new temporary versions of the phone number that don't have the punctuation but you can probably manage something, although it might be ugly. Another possibility is that you could write a user-defined function to strip the punctuation out of the phone numbers. See http:// dev.mysql.com/doc/refman/4.1/en/adding-functions.html for
Re: A few questions about triggers in MySQL 5
Hello. 4. triggers reference another table They're allowed as of MySQL 5.0.10. See: http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html Jenny Chen wrote: Does the current latest MySQL 5.0 support: 1. column based triggers 2. trigger restriction 3. multiple triggers of same type of the table 4. triggers reference another table -- 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: mysqld doesn't starts with Fatal Error
Hello. 051116 16:27:54 [ERROR] Fatal error: Can't open and lock privilege tables:= Table 'mysql.host' doesn't exist Have a invoked mysql_install_db? See: http://dev.mysql.com/doc/refman/5.0/en/unix-post-installation.html Jörg Nowak wrote: Hello list, I compiled I special mysql-5-1-2 with the following configuration: ./configure --with-ndbcluster=20 --prefix=3D/mycomputer/mysql-5.1.2-binary --with-partition=20 --with-row-based-replication --without-innodb --without-berkeley-db=20 --enable-thread-safe-client --enable-shared Afterwards I configured a cluster and it works fine. But starting mysqld: /usr/local/mysql/libexec/mysqld --user=3Droot --log-bin=3Dudo-bin fails 051116 16:27:54 [Note] Starting Cluster Binlog 051116 16:27:54 [ERROR] Fatal error: Can't open and lock privilege tables:= Table 'mysql.host' doesn't exist Any suggestions, ideas how to solve that =3F =20 J=F6rg Nowak =5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F=5F Verschicken Sie romantische, coole und witzige Bilder per SMS! Jetzt bei WEB.DE FreeMail: http://f.web.de/=3Fmc=3D021193 -- 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: Remove all non-alpha characters?
Hello. I haven't said that this is the most effective solution, however, in my opinion it should work :) Fan, Wellington wrote: Gleb, Thanks; using REPLACE(), as I understand it, would require me to list ALL non-alpha characters, and assuming just ASCII characters, approx (127 - 52) nested calls to REPLACE()... select REPLACE( ...REPLACE( REPLACE( REPLACE( text,'~',''), '!',''), '@','') ...) -Original Message- -- 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: MySQL Administrator crash on Mac OS X
Hello. At least there is one verified crash of MySQL Administrator on Mac OS, however, not during the connection process: http://bugs.mysql.com/bug.php?id=14269 You have a good description of your problem, and may want to report it at: http://bugs.mysql.com Yves Goergen wrote: Hi, I just downloaded MySQL Administrator 1.1.0 for Mac OS X. When I run it, I can see the connection dialogue, when I click to connect, it crashes. MySQL Query Browser, also latest version, just installed, too, works perfectly with the same (and only) connection. Crash report from OS X is attached. Date/Time: 2005-11-16 21:38:10.722 +0100 OS Version: 10.4.3 (Build 8F46) Report Version: 3 Command: MySQL Administrator Path:/Applications/MySQL Administrator.app/Contents/MacOS/MySQL Administrator Parent: WindowServer [67] Version: ??? (1.1.0) PID:1028 Thread: 0 Exception: EXC_BAD_ACCESS (0x0001) Codes: KERN_PROTECTION_FAILURE (0x0002) at 0x Thread 0 Crashed: 0 libSystem.B.dylib 0x90003008 strlen + 8 1 libSystem.B.dylib 0x9001fac0 sscanf + 88 2 com.mysql.MySQLToolsCommon0x100354e0 myx_get_mysql_version + 96 3 com.mysql.MySQLToolsCommon0x100355b4 myx_get_mysql_major_version + 24 4 com.mysql.MySQLToolsCommon0x10001e50 -[MConnectionPanel(Private) connectionFinished:] + 216 5 com.apple.Foundation 0x928e8f00 __NSFireMainThreadPerform + 276 6 com.apple.CoreFoundation 0x9078df20 __CFRunLoopPerformPerform + 104 7 com.apple.CoreFoundation 0x9075da5c __CFRunLoopDoSources0 + 384 8 com.apple.CoreFoundation 0x9075cf8c __CFRunLoopRun + 452 9 com.apple.CoreFoundation 0x9075ca0c CFRunLoopRunSpecific + 268 10 com.apple.HIToolbox 0x931831e0 RunCurrentEventLoopInMode + 264 11 com.apple.HIToolbox 0x931827ec ReceiveNextEventCommon + 244 12 com.apple.HIToolbox 0x931826e0 BlockUntilNextEventMatchingListInMode + 96 13 com.apple.AppKit 0x93680904 _DPSNextEvent + 384 14 com.apple.AppKit 0x936805c8 -[NSApplication nextEventMatchingMask:untilDate:inMode:dequeue:] + 116 15 com.apple.AppKit 0x937213d8 -[NSButtonCell performClick:] + 668 16 com.apple.AppKit 0x93720b38 -[NSButton performKeyEquivalent:] + 424 17 com.apple.AppKit 0x9372093c -[NSView performKeyEquivalent:] + 140 18 com.apple.AppKit 0x9372089c -[NSWindow performKeyEquivalent:] + 32 19 com.apple.AppKit 0x9375f4b0 -[NSTextField textDidEndEditing:] + 684 20 com.apple.Foundation 0x928db018 _nsnote_callback + 180 21 com.apple.CoreFoundation 0x907844b8 __CFXNotificationPost + 368 22 com.apple.CoreFoundation 0x9077c594 _CFXNotificationPostNotification + 684 23 com.apple.Foundation 0x928c5420 -[NSNotificationCenter postNotificationName:object:userInfo:] + 92 24 com.apple.AppKit 0x937b0cb4 -[NSTextView(NSPrivate) _giveUpFirstResponder:] + 512 25 com.apple.AppKit 0x937b0aa0 -[NSTextView(NSKeyBindingCommands) insertNewline:] + 512 26 com.apple.AppKit 0x937b0500 -[NSTextView doCommandBySelector:] + 212 27 com.apple.AppKit 0x93763b60 -[NSKeyBindingManager(NSKeyBindingManager_MultiClients) interpretEventAsCommand:forClient:] + 1700 28 com.apple.AppKit 0x93763440 -[NSKeyBindingManager(NSKeyBindingManager_MultiClients) interpretKeyEvents:forClient:] + 200 29 com.apple.AppKit 0x9376163c -[NSView interpretKeyEvents:] + 64 30 com.apple.AppKit 0x93761490 -[NSTextView keyDown:] + 756 31 com.apple.AppKit 0x936dcd70 -[NSWindow sendEvent:] + 6424 32 com.apple.AppKit 0x936856f4 -[NSApplication sendEvent:] + 4172 33 com.apple.AppKit 0x9367cb30 -[NSApplication run] + 508 34 com.apple.AppKit 0x9376d618 NSApplicationMain + 452 35 com.mysql.Administrator 0x5a24 _start + 392 (crt.c:267) 36 com.mysql.Administrator 0x5898 start + 48 Thread 1: 0 libSystem.B.dylib 0x90049748 syscall_thread_switch + 8 1 com.apple.Foundation 0x928f3ad0 +[NSThread sleepUntilDate:] + 152 2 com.apple.AppKit 0x9371d7e4 -[NSUIHeartBeat _heartBeatThread:] + 1100 3 com.apple.Foundation 0x928db6d4 forkThreadForFunction + 108 4 libSystem.B.dylib 0x9002b200 _pthread_body + 96 Thread 2: 0 libSystem.B.dylib 0x9002b8a8 semaphore_wait_signal_trap + 8 1 libSystem.B.dylib
Re: A bit of SQL help for a MySQL novice.
The fact that the data is coming from the text logs doesn't really change anything; _something_ is generating the text logs so that something could be changed to force users to supply phone numbers in whatever format you want. Of course, that doesn't mean _you_ can force those changes to take place; if the text logs are coming from a customer, you may not be able to persuade them to change the way they generate the logs. Your boss may not even want you to explore the possibility with the customer for fear of ruffling feathers with the customer(s). If that is the case, I'd suggest writing a UDF (user-defined function) to do the stripping of the punctuation for you, unless you can finagle the existing MySQL functions to do the work for you. I'm surprised by how few string functions MySQL supports. I use DB2 most of the time and it has lots and lots of built-in functions, many of which are dedicated to string manipulation. If you can't keep the punctuation out of the data in the first place and you can't figure out how strip the punctuation with the existing MySQL functions, I'd say a UDF is pretty much the _only_ way to get that area code. Rhino - Original Message - From: Rick Dwyer [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, November 17, 2005 11:53 AM Subject: Re: A bit of SQL help for a MySQL novice. Unfortunately, the phone numbers come from text logs that get imported into mysql. Because the phone number is often displayed on a document for the customer, they will dictate how they want it to appear (i.e. with ( ) etc.). The phone logs simply record those values as they are so data will be entered in an unclean manner. Therefore I must deal with it on the backend. Thanks for the pointers. Rick On Nov 17, 2005, at 11:15 AM, Rhino wrote: - Original Message - From: Rick Dwyer [EMAIL PROTECTED] link.com To: mysql@lists.mysql.com Sent: Thursday, November 17, 2005 10:28 AM Subject: A bit of SQL help for a MySQL novice. Hello All. I am hoping for a bit of help with some code that has really given me some trouble. If this is not he correct forum for this any help in pointing me to a more suited list would be appreciated. I have a MySQL 4.1.x database containing records with phone numbers. Most of the phone numbers are enter in 12035551212 format, but some are entered with spaces or - or ( or other characters. I need to read the first 4 positions in the phone number to determine it's location. My statement looks like this: 'Select mid(phone, 1,4) as phoneareacode from phonetable' This works but if the number is entered as 1(203)-555-1212 the above would return 1(20 which is not what I am looking for. Is there a way to have the select statement examine only numeric values in the phone number so it would disregard the other charcters? In Lasso, you can use a Replace with a Regular Expression function to have just the digits 0-9 examined but haven't been able find a way to do this in SQL. Any help is appreciated. You have two basic options: 1. Make the data uniform in format so that it is easily accessed. 2. Let the users input the data in whatever format they like and then try to deal with it. It looks like you have opted for the second choice. If it were me, I'd _strongly_ prefer the first choice. I would put edits on the forms or applications that prompt the user for the phone number and force the input to match one format. For instance, if you prefer to see the phone number as one long string of numbers, e.g. 12025551212, either ignore any characters they type that aren't digits or strip out the punctuation characters afterwards. By the way, I'm _not_ saying that you should store the numbers as one long string; there are other options but I would choose the one that was going to be most useful to you based on your business requirements. If the area code is going to be important to you, as it appears from your question, it might be a good idea to store it in a separate column. For instance, you could put the country code (the '1') in a Country_Code column, put the area code in an Area_Code column, put the 7 digit number in its own column, and then put the extension (if applicable) in yet another column if that would help you. Beware of foreign phone numbers though because they don't look like US ones (and don't make the mistake of thinking that the '1' at the beginning of the phone number automatically means the US; I'm in Canada and our phone numbers also start with 1, our area codes are also three digits, and the rest of the number is also 7 digits. Phone numbers in Europe or Africa or Asia follow rather different patterns that are shared by Canada and the US.) Now, your input routines _could_ mimic the way you store the phone numbers. For instance, if you want separate columns in the database for country code, area code, the rest of the
Command Denied on Stored Procedure
I'm trying to execute a MySQL Stored Procedure from my ASP.NET page: Cmd=New MySqlCommand(sp_InsertNewCamper,Conn) Cmd.CommandType = CommandType.StoredProcedure paramReqID = Cmd.Parameters.Add(return,SqlDbType.Int) paramReqID.Direction = ParameterDirection.ReturnValue Cmd.Parameters.Add(FirstName,FirstName.Text) Cmd.Parameters.Add(LastName,LastName.Text) Cmd.Parameters.Add(UserName,UserName.Text) Cmd.Parameters.Add(Password,Password.Text) Cmd.ExecuteNonQuery() cID = CStr(Cmd.Parameters(return).Value) When I execute this code, I get the error, 42000 SELECT command denied to user 'FCCamp'@'localhost' for table 'proc'. The Stored Procedure is as follows: CREATE Procedure sp_InsertNewCamper( in cFirstName VarChar(30), in cLastName VarChar(30), in cUserName VarChar(30), in cPassword VarChar(30), out AddedID Int) BEGIN INSERT INTO Campers (FirstName, LastName, UserName, Password) VALUES (cFirstName, cLastName, cUserName, cPassword); Set AddedID = LAST_INSERT_ID(); END; The user FCCamp has SELECT rights on this database, but there is no proc table. And I don't know how to assign rights to execute stored procedures if that is even necessary. Anyone know how to take care of that. Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A bit of SQL help for a MySQL novice.
Rick
I need to read the first 4 positions in the phone number to determine
it's location.
My statement looks like this:
'Select mid(phone, 1,4) as phoneareacode from phonetable'
This works but if the number is entered as 1(203)-555-1212 the above
would return 1(20 which is not what I am looking for.
You need an unpunct() function. Not available in 4 or 5, easy to write
in 5.0 as a stored function, not hard to add as a 'C' udf in 4.1 if you
write 'C'. Since it's a common requirement, likely someone has written
it. Failing that, you may be stuck with the absurd
replace(replace(replace(replace(replace(@s,'(',''),')',''),'
',''),'-',''),'.','').
PB
-
Rick Dwyer wrote:
Hello All.
I am hoping for a bit of help with some code that has really given me
some trouble. If this is not he correct forum for this any help in
pointing me to a more suited list would be appreciated.
I have a MySQL 4.1.x database containing records with phone numbers.
Most of the phone numbers are enter in 12035551212 format, but some
are entered with spaces or - or ( or other characters.
I need to read the first 4 positions in the phone number to determine
it's location.
My statement looks like this:
'Select mid(phone, 1,4) as phoneareacode from phonetable'
This works but if the number is entered as 1(203)-555-1212 the above
would return 1(20 which is not what I am looking for.
Is there a way to have the select statement examine only numeric
values in the phone number so it would disregard the other charcters?
In Lasso, you can use a Replace with a Regular Expression function to
have just the digits 0-9 examined but haven't been able find a way
to do this in SQL.
Any help is appreciated.
Thank you.
Rick
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.3/173 - Release Date: 11/16/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie Query: Error starting MySQL.
Hi all First usage of MySQL. Newbie in Linux as well as MySQL. Using CentOS 4.2 with MySQL 4.1.12, rpm install. Changed the data directory. My /etc/my.conf --- [mysqld] datadir=/home.dbdata/mysql socket=/home.dbdata/mysql/mysql.sock # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid - - Ran these commands for permissions: chown -R mysql:mysql /home.dbdata/mysql chmod -R go-rwx /home.dbdata/mysql Running /etc/rc.d/init.d/mysqld start says that mysql start failed BUT ps -aux shows that mysql is running though it reports failure instead of OK when I start it. Also when I stop myswl from commandline, it stops and shows OK, but cannot also connect to it. Checked the pid file mentioned in the my.cnf and it gets created and deleted when mysql starts stops respectively. Please advise what is happening and what step I have taken wrong and what to do to correct it. Am a newbie but can follow instructions. Please help. With best regards. Sanjay. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: A bit of SQL help for a MySQL novice.
Hi Rick,
Below are some MySQL functions that might help with your problem.
Sorry, for not much of direct answer.
But the idea is that you can combine some of these (string) functions -
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html to parse
out the string you are looking for. If you are programming in one of
Unix, you could also pull your answer into UNIX script to parse it out
what ever you need.
I.
CONCAT_WS(separator, str1, str2,...)
CONCAT_WS(separator, str1, str2,...)
CONCAT_WS() stands for CONCAT With Separator and is a special form of
CONCAT(). The first argument is the separator for the rest of the
arguments. The separator is added between the strings to be
concatenated. The separator can be a string as can the rest of the
arguments. If the separator is NULL, the result is NULL. The function
skips any NULL values after the separator argument.
mysql SELECT CONCAT_WS(',',
'First name','Second name','Last Name');
- 'First name,Second name,Last Name'
mysql SELECT CONCAT_WS(',','First name',NULL,'Last Name');
- 'First name,Last Name'
Before MySQL 4.0.14, CONCAT_WS() skips empty strings as well as NULL
values.
II.
FIND_IN_SET(str,strlist)
FIND_IN_SET(str,strlist)
Returns a value 1 to N if the string str is in the string list strlist
consisting of N substrings. A string list is a string composed of
substrings separated by `,' characters. If the first argument is a
constant string and the second is a column of type SET, the
FIND_IN_SET() function is optimized to use bit arithmetic. Returns 0 if
str is not in strlist or if strlist is the empty string. Returns NULL if
either argument is NULL. This function will not work properly if the
first argument contains a comma (`,') character.
mysql SELECT FIND_IN_SET('b','a,b,c,d');
- 2
III.
INSTR(str,substr)
Returns the position of the first occurrence of substring substr in
string str. This is the same as the two-argument form of LOCATE(),
except that the arguments are swapped.
mysql SELECT INSTR('foobarbar', 'bar');
- 4
mysql SELECT INSTR('xbar', 'foobar');
- 0
This function is multi-byte safe. In MySQL 3.23, this function is case
sensitive. For 4.0 on, it is case sensitive only if either argument is a
binary string.
IV.
LEFT(str,len)
LEFT(str,len)
Returns the leftmost len characters from the string str.
mysql SELECT LEFT('foobarbar', 5);
- 'fooba'
V
LENGTH(str)
LENGTH(str)
Returns the length of the string str, measured in bytes. A multi-byte
character counts as multiple bytes. This means that for a string
containing five two-byte characters, LENGTH() returns 10, whereas
CHAR_LENGTH() returns 5.
mysql SELECT LENGTH('text');
- 4
Mikhail Berman
-Original Message-
From: Peter Brawley [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 17, 2005 1:51 PM
To: Rick Dwyer
Cc: mysql@lists.mysql.com
Subject: Re: A bit of SQL help for a MySQL novice.
Rick
I need to read the first 4 positions in the phone number to determine
it's location.
My statement looks like this:
'Select mid(phone, 1,4) as phoneareacode from phonetable'
This works but if the number is entered as 1(203)-555-1212 the above
would return 1(20 which is not what I am looking for.
You need an unpunct() function. Not available in 4 or 5, easy to write
in 5.0 as a stored function, not hard to add as a 'C' udf in 4.1 if you
write 'C'. Since it's a common requirement, likely someone has written
it. Failing that, you may be stuck with the absurd
replace(replace(replace(replace(replace(@s,'(',''),')',''),'
',''),'-',''),'.','').
PB
-
Rick Dwyer wrote:
Hello All.
I am hoping for a bit of help with some code that has really given me
some trouble. If this is not he correct forum for this any help in
pointing me to a more suited list would be appreciated.
I have a MySQL 4.1.x database containing records with phone numbers.
Most of the phone numbers are enter in 12035551212 format, but some
are entered with spaces or - or ( or other characters.
I need to read the first 4 positions in the phone number to determine
it's location.
My statement looks like this:
'Select mid(phone, 1,4) as phoneareacode from phonetable'
This works but if the number is entered as 1(203)-555-1212 the above
would return 1(20 which is not what I am looking for.
Is there a way to have the select statement examine only numeric
values in the phone number so it would disregard the other charcters?
In Lasso, you can use a Replace with a Regular Expression function to
have just the digits 0-9 examined but haven't been able find a way to
do this in SQL.
Any help is appreciated.
Thank you.
Rick
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.3/173 - Release Date:
11/16/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
SELECT with WHERE and GROUP BY -- can it go any faster?
I have the following table: CREATE TABLE history ( id int(10) unsigned NOT NULL auto_increment, time_sec int(10) unsigned NOT NULL default '0', account_id int(10) unsigned NOT NULL default '0', PRIMARY KEY (id), KEY account_id (account_id), KEY time_sec (time_sec), ) TYPE=MyISAM AUTO_INCREMENT=36653 ; I need to know which months have activity for any given account_id. Here is my query (which does give me exactly the data I need, albeit slowly): -- SELECT date_format(FROM_UNIXTIME(history.time_sec), '%Y%m') AS month FROM history WHERE history.account_id = 216 GROUP BY month ORDER BY history.time_sec DESC Showing rows 0 - 5 (6 total, Query took 0.1818 sec) -- The problem is speed. 0.1818 seconds to fetch six rows is too slow. The account in question has about 6000 rows. And there are a total of about 25000 rows in the table. I've at least figured out that the thing that there are two things slowing this down: the WHERE and GROUP BY. E.g., if I drop the WHERE clause (fetch rows for all accounts): -- SELECT date_format(FROM_UNIXTIME(history.time_sec), '%Y%m') AS month FROM history GROUP BY month ORDER BY history.time_sec DESC Showing rows 0 - 14 (15 total, Query took 0.0930 sec) -- ...it's about twice as fast (or half as slow :-) And If I drop the GROUP BY, e.g.: -- SELECT date_format(FROM_UNIXTIME(history.time_sec), '%Y%m') AS month FROM history ORDER BY history.time_sec DESC Showing rows 0 - 29 (23895 total, Query took 0.0008 sec) -- Then it's very fast. Do you guys—and girls!—have any suggestions about how to speed this up, besides adding indices on time_sec and account_id (which I've done)? Thanks for taking a look. ...Rene
Re: LIMIT in subquery or GROUP_CONCAT
Hello. I've written a stored procedure that can help you. However, I'd like to see a solution which will work for 4.1 as well. Here is the results. First group is select from the original table, second group is select from the temporary table with the results. id value 1 10 1 20 1 30 1 40 1 50 2 10 2 20 2 30 2 40 3 10 3 20 4 10 id value 1 50 1 40 1 30 2 40 2 30 2 20 3 20 3 10 4 10 Table t1 must exists. The contents of p1.sql: SELECT * FROM t1; CREATE TEMPORARY TABLE tcur(id int,value int); DROP PROCEDURE IF EXISTS query3; DELIMITER $$ CREATE PROCEDURE query3() DETERMINISTIC BEGIN DECLARE tid INT; DECLARE cur2_value INT; DECLARE cur1_value INT; DECLARE no_more BOOLEAN default FALSE; DECLARE cur1 CURSOR FOR SELECT DISTINCT id FROM t1 ORDER BY id; DECLARE cur2 CURSOR FOR SELECT value FROM t1 WHERE id = tid ORDER BY value DESC LIMIT 3; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET no_more = TRUE; open cur1; cur1_loop: LOOP FETCH cur1 INTO tid; IF no_more=TRUE THEN LEAVE cur1_loop; END IF; open cur2; cur2_loop: LOOP FETCH cur2 INTO cur2_value; IF no_more=TRUE THEN LEAVE cur2_loop; END IF; INSERT INTO tcur SET id=tid, value=cur2_value; END LOOP; close cur2; SET no_more=FALSE; END LOOP; close cur1; END$$ DELIMITER ; call query3(); SELECT * FROM tcur; DROP TEMPORARY TABLE tcur; The definition of t1: mysql show create table t1\G; *** 1. row *** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(11) default NULL, `value` int(11) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=utf8 1 row in set (0.00 sec) Peter Brodersen wrote: Hi, (mysql server 5.0.15 running under linux) I suppose this is a classic task. I just hope MySQL 5.0 is able to give the right result. I would like to select top three from different parts in the same table, e.g. for the following data set: id,value 1,10 1,20 1,30 1,40 1,50 2,10 2,20 2,30 2,40 3,10 3,20 4,10 =2E. I would like the output to be: id,value 1,50 1,40 1,30 2,40 2,30 2,20 3,20 3,10 4,10 That is, the top 3 from each id. The id could be a key in another table instead (just containing 1,2,3,4 as rows). The numbers of different ids might vary thus a static set of UNIONs is no answer. I tried using a subquery using LIMIT inside, but I just got the following result: ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT IN/A= LL/ANY/SOME subquery' It seems like this was a possibility in very early versions of MySQL 4.1. I tried with GROUP_CONCAT() as well, but even though there is an ORDER option, there is no LIMIT option here (feature request? :-) ) Maybe using a variable to keep track of internal count... --=20 - Peter Brodersen -- 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]
Can't get REGEXP to work properly with utf8
Hi! I hope this is the right mailing list for my problem. :) I recently changed the collation/character set in the whole database for a project I'm working on to utf8. After a while I realized that REGEXP didn't work as it used to. I looked in the manual and saw that REGEXP was not multibyte safe. For example, if I have column called 'info' in a table 'news' and a row with the string början in colum 'info' and I would use these queries: SELECT * FROM news WHERE REGEXP början; SELECT * FROM news WHERE REGEXP BÖRJAN; (I have simplified these queries so it would be more obvious, ordinarily the regexp's are much more complex, so I cannot for example use LIKE instead) This used to give the same result, I would get the row containing the string början whatever case I used. However, when I changed the database to utf8 (from latin1) the second query wouldn't work. I can still do REGEXP searches with different case as long as the characters are non-foreign (åäö and so on). I found this in http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html : REGEXP and RLIKE use the current character set (cp1252 Latin1 by default) when deciding the type of a character. Warning: These operators are not multi-byte safe. Does this mean that REGEXP will only work with foreign characters if the column has a latin1 collation? Or does it mean that I can change the current character set to utf8 and REGEXP will work with foreign characters? Because I have tried that and it will still not work. Everything is set to utf8: character_set_client, character_set_connection, character_set_database, character_set_results, character_set_server, character_set_system, and all collations are utf8. I have changed the MySQL-configuration (my.ini) so that default-character-set is utf8_general_ci (I have tried utf8_swedish_ci too) for both client and server. It wont work. But if I change collation only for the column I'm doing REGEXP's on to latin1 it works! Am I forced to use latin1 if I want to do proper REGEXP selects? Has anyone else had this problem? I would be ever grateful for any help. Thanks /Oskar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't get REGEXP to work properly with utf8
Oskar Joelson wrote: I found this in http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html : REGEXP and RLIKE use the current character set (cp1252 Latin1 by default) when deciding the type of a character. Warning: These operators are not multi-byte safe. These operators are not multi-byte safe means that they won't work correctly with multi-byte character sets. UTF-8 is a multi-byte character set. Jasper -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A key question
Hello everyone, Is it possible or makes sense to key a field that is a part of Unique Index already? Regards and thank you Mikhail Berman
Re: A key question
Mikhail Berman wrote: Is it possible or makes sense to key a field that is a part of Unique Index already? It's possible, but it doesn't make sense. A unique index is a normal index with an added unique constraint. Adding another index on the same field would make no sense (unless the field is a rightmost part of a unique index). Your question would have been answered by checking the archives less than a week back (this question comes up a lot) or reading the manual. Jasper -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT with WHERE and GROUP BY -- can it go any faster?
OK, after reading http://dev.mysql.com/doc/refman/5.0/en/order-by- optimization.html I have learned improved the speed somewhat by creating a multi-column index on account_id and time_sec, such that: -- SELECT date_format(FROM_UNIXTIME(history.time_sec), '%Y%m') AS month FROM history WHERE history.account_id = 216 GROUP BY month ORDER BY history.time_sec DESC Showing rows 0 - 5 (6 total, Query took 0.0267 sec) -- So, about 15x faster. But if I drop GROUP BY month, it goes really fast: -- SELECT date_format(FROM_UNIXTIME(history.time_sec), '%Y%m') AS month FROM history WHERE history.account_id = 216 ORDER BY history.time_sec DESC Showing rows 0 - 29 (6248 total, Query took 0.0009 sec) -- Is it possible to have the GROUP BY month as part of a multi-column index? Or do something to get the speed closer to 0.0009 sec? ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: A key question
Dear Jasper, I may not have been precise in my question, but the Unique Index in question is a two fields index, and I was looking to find out wisdom from the List if there is sense and/or experience in keying second(left) field on in the Unique Index to speed up a search. I am dealing with 32M rows table, where second field in the Unique Index is a date field. Unfortunately for my simple SELECT MAX(Date) as Latest_Date from THE_TABLE took 4 minutes and some seconds, so before I will go and buy bigger server I needed to re-assure myself that there is no other way. I would not bother the List without a good reason and doing what you said before Regards, Mikhail Berman -Original Message- From: Jasper Bryant-Greene [mailto:[EMAIL PROTECTED] Sent: Thursday, November 17, 2005 4:19 PM To: Mikhail Berman Cc: mysql@lists.mysql.com Subject: Re: A key question Mikhail Berman wrote: Is it possible or makes sense to key a field that is a part of Unique Index already? It's possible, but it doesn't make sense. A unique index is a normal index with an added unique constraint. Adding another index on the same field would make no sense (unless the field is a rightmost part of a unique index). Your question would have been answered by checking the archives less than a week back (this question comes up a lot) or reading the manual. Jasper -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT with WHERE and GROUP BY -- can it go any faster?
René Fournier [EMAIL PROTECTED] wrote on 11/17/2005 04:19:25 PM: OK, after reading http://dev.mysql.com/doc/refman/5.0/en/order-by- optimization.html I have learned improved the speed somewhat by creating a multi-column index on account_id and time_sec, such that: -- SELECT date_format(FROM_UNIXTIME(history.time_sec), '%Y%m') AS month FROM history WHERE history.account_id = 216 GROUP BY month ORDER BY history.time_sec DESC Showing rows 0 - 5 (6 total, Query took 0.0267 sec) -- So, about 15x faster. But if I drop GROUP BY month, it goes really fast: -- SELECT date_format(FROM_UNIXTIME(history.time_sec), '%Y%m') AS month FROM history WHERE history.account_id = 216 ORDER BY history.time_sec DESC Showing rows 0 - 29 (6248 total, Query took 0.0009 sec) -- Is it possible to have the GROUP BY month as part of a multi-column index? Or do something to get the speed closer to 0.0009 sec? ...Rene You are already pushing the limits of total response time: submit time + parse/validate time + optimization + execution (including: date conversion, grouping, and re-sorting) + formating and transmitting output = = .0267 sec If a network or disk access is involved in any part of that chain, I think you are optimal (it's hard to get some pings to return in sub .03 seconds). Have you thought about storing your data into a HEAP or MEMORY table? That might get more speed out of it. As would using a prepared statement (reducing the parse/validate portion of the equation). Why is this query so time-critical, if I may ask? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: a question of specifying Times for Recovery
What does your my.cnf configuration for mysql say? Check out: http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html I think you want character_set as your variable, not default_character_set. Just my guess, not seeing any of your config files. -Sheeri On 11/16/05, wangxu [EMAIL PROTECTED] wrote: I specifying Times for Recovery with mysqlbinlog. Follow is my command: mysqlbinlog --database=menagerie --stop-data=2005-11-14 9:22:01 C:\Program Files\MySQL\MySQL Server 5.0\data\1.01 | mysql -uroot -p11 menagerie Mysql server throw a exception: mysqlbinlog: unknown variable 'default-character-set=utf8' please help me. thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld crash with archive engine 2gb
Marc: Define crashing the server. Does the whole machine shut down? The mysqld process? What do error logs say? What version of MySQL are you using? What's the create table statement? what's the current avg_row_length and max_rows? What is ulimit -f as the MySQL user say? -Sheeri On 11/16/05, Marc Rossi [EMAIL PROTECTED] wrote: I am trying to populate a table using the archive engine that I estimate will take up ~ 8gb of disk space when finished. Right now I am crashing the server as soon as my file gets to the 2gb mark. OS is linux and there are other files on the same filesystem that are 30gb+ so I know the fs has support. Any ideas? I tried to do the ALTER TABLE x AVG_ROW_LENGTH=x MAX_ROWS=y using inputs that would exceed 2GB, same results. TIA, Marc -- 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: mysqld crash with archive engine 2gb
Hi Marc, Is there any message in the log files? or any error message from the OS? Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Marc Rossi [mailto:[EMAIL PROTECTED] Sent: Thursday, 17 November 2005 3:16 PM To: mysql@lists.mysql.com Subject: mysqld crash with archive engine 2gb I am trying to populate a table using the archive engine that I estimate will take up ~ 8gb of disk space when finished. Right now I am crashing the server as soon as my file gets to the 2gb mark. OS is linux and there are other files on the same filesystem that are 30gb+ so I know the fs has support. Any ideas? I tried to do the ALTER TABLE x AVG_ROW_LENGTH=x MAX_ROWS=y using inputs that would exceed 2GB, same results. TIA, Marc -- 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: SELECT with WHERE and GROUP BY -- can it go any faster?
On 17-Nov-05, at 2:41 PM, [EMAIL PROTECTED] wrote: René Fournier [EMAIL PROTECTED] wrote on 11/17/2005 04:19:25 PM: OK, after reading http://dev.mysql.com/doc/refman/5.0/en/order-by- optimization.html I have learned improved the speed somewhat by creating a multi-column index on account_id and time_sec, such that: - --- -- SELECT date_format(FROM_UNIXTIME(history.time_sec), '%Y%m') AS month FROM history WHERE history.account_id = 216 GROUP BY month ORDER BY history.time_sec DESC Showing rows 0 - 5 (6 total, Query took 0.0267 sec) - --- -- So, about 15x faster. But if I drop GROUP BY month, it goes really fast: - --- -- SELECT date_format(FROM_UNIXTIME(history.time_sec), '%Y%m') AS month FROM history WHERE history.account_id = 216 ORDER BY history.time_sec DESC Showing rows 0 - 29 (6248 total, Query took 0.0009 sec) - --- -- Is it possible to have the GROUP BY month as part of a multi-column index? Or do something to get the speed closer to 0.0009 sec? ...Rene You are already pushing the limits of total response time: submit time + parse/validate time + optimization + execution (including: date conversion, grouping, and re-sorting) + formating and transmitting output = = .0267 sec If a network or disk access is involved in any part of that chain, I think you are optimal (it's hard to get some pings to return in sub .03 seconds). Have you thought about storing your data into a HEAP or MEMORY table? That might get more speed out of it. As would using a prepared statement (reducing the parse/validate portion of the equation). Why is this query so time-critical, if I may ask? Well, I have a number of queries that are executed on every page, and I'm just trying to optimize them. I don't presume to be a DBA, but I would like to learn how to tune these queries as much as possible... Thanks for your response. Maybe 0.0267 seconds is as good as it gets. ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A key question
Mikhail, Is it possible or makes sense to key a field that is a part of Unique Index already? MySQL won't stop you, but it's a waste of space cpu cycles unless there's a high-priority query performance need for it. PB - Mikhail Berman wrote: Hello everyone, Is it possible or makes sense to key a field that is a part of Unique Index already? Regards and thank you Mikhail Berman No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.3/173 - Release Date: 11/16/2005 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.362 / Virus Database: 267.13.3/173 - Release Date: 11/16/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A key question
Hi Mikhail, I may not have been precise in my question, but the Unique Index in question is a two fields index, and I was looking to find out wisdom from the List if there is sense and/or experience in keying second(left) field on in the Unique Index to speed up a search. If you have a UNIQUE(a, b), then MySQL can use it as an index for (a), or (a, b), but NOT for (b). In this context, it won't help generally to create an index on (a), but it may help to create one on (b) depending on your queries. I am dealing with 32M rows table, where second field in the Unique Index is a date field. Unfortunately for my simple SELECT MAX(Date) as Latest_Date from THE_TABLE took 4 minutes and some seconds, so before I will go and buy bigger server I needed to re-assure myself that there is no other way. Four minutes to find a MAX(date) is too long for any kind of hardware. It should be much faster. Can you post the output of: * SHOW CREATE TABLE tbl * SHOW VARIABLES FYI: mysql select max(dep_time) from ontime_all; +-+ | max(dep_time) | +-+ | 2005-05-31 23:59:00 | +-+ 1 row in set (49.76 sec) mysql select count(*) from ontime_all; +--+ | count(*) | +--+ | 33395077 | +--+ 1 row in set (0.00 sec) Could be a lot faster, even, but these are MERGE tables so it's really 65 tables that are being checked... Regards, Jeremy -- Jeremy Cole MySQL Geek, Yahoo! Inc. Desk: 408 349 5104 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld crash with archive engine 2gb
Is there any message in the log files? or any error message from the OS? David - When the mysqld process restarts, there is nothing in the logfile except the basic startup info. I have listed it below:0:33). 051116 10:06:33 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.0.15-standard' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL) Number of processes running now: 0 051116 20:33:05 mysqld restarted 051116 20:33:05 InnoDB: Started; log sequence number 0 43665 051116 20:33:05 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.0.15-standard' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL) As you can see from the log I am running 5.0.15. I installed using the provided RPMs on a RH ES3 box. Below is the tablestatus. mysql show table status like 'trade' \G *** 1. row *** Name: trade Engine: ARCHIVE Version: 10 Row_format: Compressed Rows: 0 Avg_row_length: 4137 Data_length: 2147483647 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 2005-11-16 20:33:05 Update_time: 2005-11-16 20:33:05 Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: max_rows=4294967295 avg_row_length=4137 Comment: 1 row in set (0.01 sec) Thanks for any assistance you can give. I am also looking at alternative solutions in which I use multiple ARCHIVE dbs with each being 2 gb. Marc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: dynamic fieldname to assign to
C.R.Vegelin wrote: Hi All, I have a simple problem and hope you can help me. I have an input table Updates with various fields, incl. Month and MonthlyValue. The field Updates.Month ranges from 1 to 12. I also have a target Data table with 12 fields, called Jan, Feb, Mar, ... `Dec`. Depending on Updates.Month the MonthlyValue must be put in the proper Data field. Now I use 12 UPDATE queries, like: UPDATE Data INNER JOIN Updates ON ... SET Data.Jan = Updates.Cell WHERE Updates.Month = 1; ... UPDATE Data INNER JOIN Updates ON ... SET Data.Dec = Updates.Cell WHERE Updates.Month = 12; My question: can it be done in a single query ? Try something like this: UPDATE Data INNER JOIN Updates ON ... SET Data.Jan = IF(Updates.Month = 1,Updates.Cell,Data.Jan), SET Data.Feb = IF(Updates.Month = 2,Updates.Cell,Data.Feb), SET Data.Mar = IF(Updates.Month = 3,Updates.Cell,Data.Mar), ... -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysqld crash with archive engine 2gb
Hi Marc, I would be a bit suspicious of the version of zlib or something similar. If it has only been compiled with a 32bit compiler, this could be causing an artificial limit of 2Gb on a pointer. The ARCHIVE engine uses the zlib for its compression, that comes with mysql. I am presuming if it has been compiled in 32bit mode that a pointer or two maybe overflowing. These are just thoughts as I can't really find any reason in your logs as to why this should be happening. Do you have a ulimit set for the user? This could be constricting your file growth to 2Gb. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Marc Rossi [mailto:[EMAIL PROTECTED] Sent: Friday, 18 November 2005 10:01 AM To: Logan, David (SST - Adelaide); mysql@lists.mysql.com Subject: Re: mysqld crash with archive engine 2gb Is there any message in the log files? or any error message from the OS? David - When the mysqld process restarts, there is nothing in the logfile except the basic startup info. I have listed it below:0:33). 051116 10:06:33 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.0.15-standard' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL) Number of processes running now: 0 051116 20:33:05 mysqld restarted 051116 20:33:05 InnoDB: Started; log sequence number 0 43665 051116 20:33:05 [Note] /usr/sbin/mysqld: ready for connections. Version: '5.0.15-standard' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL) As you can see from the log I am running 5.0.15. I installed using the provided RPMs on a RH ES3 box. Below is the tablestatus. mysql show table status like 'trade' \G *** 1. row *** Name: trade Engine: ARCHIVE Version: 10 Row_format: Compressed Rows: 0 Avg_row_length: 4137 Data_length: 2147483647 Max_data_length: 0 Index_length: 0 Data_free: 0 Auto_increment: NULL Create_time: 2005-11-16 20:33:05 Update_time: 2005-11-16 20:33:05 Check_time: NULL Collation: latin1_swedish_ci Checksum: NULL Create_options: max_rows=4294967295 avg_row_length=4137 Comment: 1 row in set (0.01 sec) Thanks for any assistance you can give. I am also looking at alternative solutions in which I use multiple ARCHIVE dbs with each being 2 gb. Marc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
To upgrade, or not to upgrade...
...that is the question. I have some queries that would possibly benefit from subqueries, which means upgrading my stock Mac OS X Server 10.3.9 installation of MySQL (version 4.1.10a). The question is, and I would appreciate any comments, should I go just to 4.0.26 for the subqueries, or straight to 5.0.15? Also, the machine I am upgrading is a production box, so I am a little nervous about what can (and will) go wrong. Any good advice is much appreciated. Thanks. ...Rene -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql 4.1 full table (nearly) - best practice to alter?
Hi There, We are reaching 4GB with one of our tables (v4.1 on debian sarge) and are needing to run: ALTER TABLE tbl_name MAX_ROWS=10 AVG_ROW_LENGTH=nnn; as per the documentation.. I would be greatful for any input on best practices to do this, as the database needs to be offline for the absolute minimal amount of time so the fastest process that i can think of would be: 1). Backup everything! :) 2). mysqldump the table to a file 3). drop the table 4). recreate the table structure 5). do the alter 6). import the data back in Other questions are... can the alter be done to live data? how does this work? Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Joins - where clause, booking system
Your query was: SELECT distinct accomodation.accom_id FROM accomodation LEFT JOIN student_accom ON accomodation.accom_id = student_accom.accom_id where ((student_accom.start_date Between '2005-10-01' And '2005-11-30') OR (student_accom.leave_date Between '2005-10-01' And '2005-10-30')) The logic: find me all accomodations that have a start date in October or November, or have an end date in October or November. That way, you find all the bookings that start Oct. 1-Nov. 30 (was that a typo in that last '2005-10-30'?). You'll also find the bookings that start sometime before Oct. 1, and end sometime in October. You want a query whose logic is find me all accomodations that meet both of the following qualifications: 1) there are no bookings starting in Oct. and Nov, and 2) there are no bookings ending in Oct. and Nov. As has been said many times on this list, it's very difficult to find data that's NOT in the database. Also, did I miss the fact that there's an operator called Between? or is that a pseudo-query? (I'm coming back to MySQL after some time off, so between may be valid). Sounds like you want something like: SELECT distinct accomodation.accom_id FROM accomodation LEFT JOIN student_accomd on accomodation.accom_id=student_accom.accom_id where ((student_accom.start_date not between '2005-10-01' and '2005-11-30') AND (student_accom.leave_date not between '2005-10-01' and '2005-11-30')) This also follows the math logic that the inverse of this or that is 'not this' and 'not that'. -Sheeri On 11/16/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hello, I'm running mysql 4.0.12 I have a bookings database in which I'm having problems pulling out the available accomodation. Tables are: Accomodation - accomid (primary key), town, address, contactnumber etc student_accom - studentaccomid (primary key), accomid, studentid My query is as follows so far, this is pulling out all the accomodation that is booked between specified dates - not what I'm after - SELECT distinct accomodation.accom_id FROM accomodation LEFT JOIN student_accom ON accomodation.accom_id = student_accom.accom_id where ((student_accom.start_date Between '2005-10-01' And '2005-11-30') OR (student_accom.leave_date Between '2005-10-01' And '2005-10-30')) Appreciate any assistance. Thanks Gavin -- 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]
solution for preventing injection attacks
I have an idea for preventing sql injection attacks, however it would have to be implemented by the database vendor. Let me know if I am on the right track, this totally off base, or already implemented somewhere... Lets say you could have a format string such as in printf $format= SELECT %s FROM %s WHERE id='%s' ; $fieldname=last_name; $tablename=personel; $id=425; and you could execute a query like mysql_query_formatted($format, $fieldname, $tablename, $id); (the language would have to have variable argument lengths) now, the key is that instead of just adding the $fieldname, $tablename, $id to the $format string and passing it to mysql_query, it would be passed to the parser as separate strings. The parser should know how to handle that format. That way, the parser would always know where the different tables names, field names, and other strings start and end. So, the problem of injection attacks caused by some one confusing the parser by entering things like ' and is gone. It would be easier on the programmer. There would be no need to worry about escape characters when passing to this function, the strings would not have to be escaped.. The parser would no longer have to guess where the boundaries are. No more worrying about injection attacks. does that make sense? -- http://www.douglassdavis.com
Re: solution for preventing injection attacks
[EMAIL PROTECTED] wrote: I have an idea for preventing sql injection attacks, however it would have to be implemented by the database vendor. Let me know if I am on the right track, this totally off base, or already implemented somewhere... Lets say you could have a format string such as in printf $format= SELECT %s FROM %s WHERE id='%s' ; $fieldname=last_name; $tablename=personel; $id=425; and you could execute a query like mysql_query_formatted($format, $fieldname, $tablename, $id); (the language would have to have variable argument lengths) Google sql prepared statement or the like. What you suggest pretty much exists already. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Innodb Crash on 2 concurrent select
Ok greg, I'll report this bug Thx Greg 'groggy' Lehey wrote: On Wednesday, 16 November 2005 at 20:40:35 +0700, Ady Wicaksono wrote: I have MySQL with about 12 billion rows when i try to create 2 process, each select count(*) on the same table after a long time about 30 minutes it crashed :( ANy information? ... InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html InnoDB: about forcing recovery. mysqld got signal 11; This is obviously a bug. I've just checked the bug database, but I don't see a report on it yet. Could you please enter one? Greg -- Greg Lehey, Senior Software Engineer MySQL AB, http://www.mysql.com/ Echunga, South Australia Phone: +61-8-8388-8286 Mobile: +61-418-838-708 Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: a question of specifying Times for Recovery
Follow is a part of my my.ini. --- [client] port=3306 default-character-set=utf8 --- If default-character-set can't set in [client]? - Original Message - From: sheeri kritzer [EMAIL PROTECTED] To: wangxu [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Friday, November 18, 2005 5:44 AM Subject: Re: a question of specifying Times for Recovery What does your my.cnf configuration for mysql say? Check out: http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html I think you want character_set as your variable, not default_character_set. Just my guess, not seeing any of your config files. -Sheeri On 11/16/05, wangxu [EMAIL PROTECTED] wrote: I specifying Times for Recovery with mysqlbinlog. Follow is my command: mysqlbinlog --database=menagerie --stop-data=2005-11-14 9:22:01 C:\Program Files\MySQL\MySQL Server 5.0\data\1.01 | mysql -uroot -p11 menagerie Mysql server throw a exception: mysqlbinlog: unknown variable 'default-character-set=utf8' please help me. thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
