R: Comma's in data?
Hi Jason, Commas are not special characters inside a varchar type of a mysql table. What you have to watch is not to break strings. That usually happens when you use the same type of quotes ,as a value of the string, of the ones used to enclose the string itself. For instance: insert into Employees (FirstName,LastName) values ('John','O'Connor'); Gives you an error, you broke the string! You should modify your statement in: insert into Employees (FirstName,LastName) values ('John','O\'Connor'); Backslash(\) is a special instruction to the mysql command interpreter that suggests to treat the following character as a value of the string and not as a closing quote(in this case). As for Mysql 5, special characters to be escaped inside a string are: \0 An ASCII 0 (NUL) character. \' A single quote (') character. \ A double quote () character. \b A backspace character. \n A newline (linefeed) character. \r A carriage return character. \t A tab character. \Z ASCII 26 (Control-Z). See note following the table. \\ A backslash (\) character. \% A % character. See note following the table. \_ A _ character. See note following the table. As you see you don't have the Comma character. Aloha! Claudio Nanni -Messaggio originale- Da: J. Todd Slack [mailto:[EMAIL PROTECTED] Inviato: sabato 8 marzo 2008 8.53 A: mysql@lists.mysql.com Oggetto: Comma's in data? Hi All, I have a client that wants to insert data into a VarChar field that contains commas. These are property addresses. Example: 2966 Moorpark Ave, San Jose, CA, 95128 1 Infinite Loop, Cupertino, CA, 95 How can I allow this? Thanks! -Jason Questo messaggio ed ogni suo allegato sono confidenziali e possono essere riservati o, comunque, protetti dall'essere diffusi. Se il ricevente non é il destinatario diretto del presente messaggio, é pregato di contattare l'originario mittente e di cancellare questo messaggio ed ogni suo allegato dal sistema di posta. Se il ricevente non é il destinatario diretto del presente messaggio, sono vietati l'uso, la riproduzione e la stampa di questo messaggio e di ogni suo allegato, nonché la diffusione del loro contenuto a qualsiasi altro soggetto * This message and any attachment are confidential and may be privileged or otherwise protected from disclosure. If you are not the intended recipient, please contact the sender and delete this message and any attachment from your system. If you are not the intended recipient you must not use, copy or print this message or attachment or disclose the contents to any other person. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MYSQL FUNCTIONS
Hi All, While i was going through mysql reference manual. I saw that A query cannot be cached if it contains any of the functions shown below BENCHMARK() CONNECTION_ID() CONVERT_TZ() CURDATE() CURRENT_DATE() CURRENT_TIME() CURRENT_TIMESTAMP() CURTIME()DATABASE() ENCRYPT() with one parameter FOUND_ROWS() GET_LOCK() LAST_INSERT_ID() LOAD_FILE() MASTER_POS_WAIT() NOW() RAND() RELEASE_LOCK() UNIX_TIMESTAMP() with no paramet- SLEEP() SYSDATE() USER() On my production server, the following query is being used. select * from student where regis_date=now(); Then what should i do so that the query get cached. Thanks, Prajapati
Select Statement
Hi, I need to write up a select statement something like: Select a.supcode,a.code,b.desc,sum(c.qty),c.dept where a.supcode=b.supcode and a.code=c.code and a.code=b.code and c.dept between $tring1 and $tring2. group by supcode This is fine but the problem is that there is duplicate supcode in a. When running this query I often have c values which does not relate to supcode. Anyone can help? Thanks Velen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL FUNCTIONS
Krishna Chandra Prajapati schrieb: Hi All, While i was going through mysql reference manual. I saw that A query cannot be cached if it contains any of the functions shown below BENCHMARK() CONNECTION_ID() CONVERT_TZ() CURDATE() CURRENT_DATE() CURRENT_TIME() CURRENT_TIMESTAMP() CURTIME()DATABASE() ENCRYPT() with one parameter FOUND_ROWS() GET_LOCK() LAST_INSERT_ID() LOAD_FILE() MASTER_POS_WAIT() NOW() RAND() RELEASE_LOCK() UNIX_TIMESTAMP() with no paramet- SLEEP() SYSDATE() USER() On my production server, the following query is being used. select * from student where regis_date=now(); Then what should i do so that the query get cached. this would be like a time service would record once the current time, and than always just send this recorded time ... wired, not? -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select Statement
Velen schrieb: Hi, I need to write up a select statement something like: Select a.supcode,a.code,b.desc,sum(c.qty),c.dept where a.supcode=b.supcode and a.code=c.code and a.code=b.code and c.dept between $tring1 and $tring2. group by supcode This is fine but the problem is that there is duplicate supcode in a. When running this query I often have c values which does not relate to supcode. yes, because c is JOINED by `code` and not by `subcode` with a and a.code=c.code -- Sebastian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server Instance Setup Error
Craig Huffstetler escribió: Greetings again Andrew, That error message is usually when you try to login to MySQL by whatever means (the Windows install Wizard may be attempting this in the final steps upon starting up? But it should not be starting up as root...). Can you complete the installation using the wizard (I am assuming this is where the error is occurring), then start-up MySQL? This error generally occurs when a user attempts to login. We may need some more details to troubleshoot this. Can you please re-run the install. Sincerely, Craig Huffstetler On Sun, Mar 9, 2008 at 6:12 PM, AndrewMcHorney [EMAIL PROTECTED] wrote: Hello I am running on Windows XP and MYSQL 5.05.1A. I have not yet started up mysql. Andrew At 13:52 2008-03-09, you wrote: mysql GRANT ALL ON databaseName.* TO 'your_mysql_name'@'your_client_host'; #mysql -S /tmp/mysql.sock -- Ing. Vidal Garza Tirado Depto. Sistemas Aduanet S.A. de C.V. Tel. (867)711-5850 ext. 4346, Fax (867)711-5855. Ave. César López de Lara No. 3603 Int. B Col Jardín. Nuevo Laredo, Tamaulipas, México. -- Este mensaje ha sido analizado por MailScanner en busca de virus y otros contenidos peligrosos, y se considera que está limpio. For all your IT requirements visit: http://www.aduanet.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
No database selected error when running mysql_tzinfo_to_sql
I'm getting an error trying to run this command: root mysql_tzinfo_to_sql /usr/share/zoneinfo/America/ | mysql -u xxx -p xxx ERROR 1046 (3D000) at line 1: No database selected Funny thing is I know I've run this on 2 other servers with identical software without issue, however I see that I've run yum update on this box but not on the other two. /var/log Nov 13 14:51:58 Updated: tzdata.noarch 2007h-1.el5 OS: CentOS 5 Mysql Version: 5.0.22 Google not helping much with this. Kinda stumped... --David.
Re: No database selected error when running mysql_tzinfo_to_sql
On Mon, Mar 10, 2008 at 11:07 AM, [EMAIL PROTECTED] wrote: I'm getting an error trying to run this command: root mysql_tzinfo_to_sql /usr/share/zoneinfo/America/ | mysql -u xxx -p xxx ERROR 1046 (3D000) at line 1: No database selected mysql -D mysql -u xxx -p mysql_tzinfo_to_sql /usr/share/zoneinfo/America/ The -D flag selects the database `mysql`, which is where the time zone information belongs. The redirect reads from the file (which, in this case, is actually a redirected STDOUT) into the database. -- /Dan Daniel P. Brown Senior Unix Geek ? while(1) { $me = $mind--; sleep(86400); } ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYSQL FUNCTIONS
On Mon, 10 Mar 2008, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: While i was going through mysql reference manual. I saw that A query cannot be cached if it contains any of the functions shown below ... NOW() On my production server, the following query is being used. select * from student where regis_date=now(); Then what should i do so that the query get cached. http://www.mysqlperformanceblog.com/2006/07/27/mysql-query-cache/ is a paper that explains a bit about MySQL caching. It starts First let me clarify what MySQL Query Cache is - I've seen number of people being confused, thinking MySQL Query Cache is the same as Oracle Query Cache - meaning cache where execution plans are cached. MySQL Query Cache is not. It does not cache the plan but full result sets. That appears to be an expansion of the official text at http://dev.mysql.com/doc/refman/5.0/en/query-cache.html, which is The query cache stores the text of a SELECT statement together with the corresponding result that was sent to the client. (6.0's page has the same sentence.) That leads me to think that the only way to cache the proposed query would be to stop time. Otherwise, the result of running NOW() will change from run to run, causing a different result set. -- Tim McDaniel, [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: No database selected error when running mysql_tzinfo_to_sql
On Mon, 10 Mar 2008, [EMAIL PROTECTED] wrote: I'm getting an error trying to run this command: root mysql_tzinfo_to_sql /usr/share/zoneinfo/America/ | mysql -u xxx -p xxx ERROR 1046 (3D000) at line 1: No database selected The MySQL 5.0 manual explains mysql_tzinfo_to_sql at http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html 6.0 at http://dev.mysql.com/doc/refman/6.0/en/time-zone-support.html looks to be the same. Its example line is mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql There's a trailing mysql, which specifies the mysql database. It also specifies all zoneinfo, not America/. http://dev.mysql.com/doc/refman/6.0/en/mysql-tzinfo-to-sql.html describes the 6.0 version in a little more detail. Its examples also specify the mysql database explicitly and use the whole zoneinfo database. I know little about the whole situation, so you may well know better than me, but I'd use the examples provided. A later example, to see whether tables have been loaded, is SELECT COUNT(*) FROM mysql.time_zone_name; Funny thing is I know I've run this on 2 other servers with identical software without issue Have you tried the SELECT command above to verify? I don't know about MySQL, but in other databases I've used, it's possible to specify a default database to use on each session unless another database is explicitly specified. Brief reading says that ~/.my.cnf can be used as an options file. -- Tim McDaniel, [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: No database selected error when running mysql_tzinfo_to_sql
On Mon, 10 Mar 2008, Daniel Brown [EMAIL PROTECTED] wrote: mysql -D mysql -u xxx -p mysql_tzinfo_to_sql /usr/share/zoneinfo/America/ The -D flag selects the database `mysql`, which is where the time zone information belongs. The redirect reads from the file (which, in this case, is actually a redirected STDOUT) into the database. That is not legal shell syntax on UNIXy systems (or CMD.EXE, for that matter). is followed by the input file name, so the command above would read a file named mysql_tzinfo_to_sql in the current directory. It does not run the mysql_tzinfo_to_sql command. The way to redirect command output into the input of another command is to use | in the proper way. Please see the on-line man pages that I posted in my other note a minute ago. -- Tim McDaniel, [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: No database selected error when running mysql_tzinfo_to_sql
On Mon, Mar 10, 2008 at 11:07 AM, [EMAIL PROTECTED] wrote: I'm getting an error trying to run this command: root mysql_tzinfo_to_sql /usr/share/zoneinfo/America/ | mysql -u xxx -p xxx ERROR 1046 (3D000) at line 1: No database selected mysql -D mysql -u xxx -p mysql_tzinfo_to_sql /usr/share/zoneinfo/America/ The -D flag selects the database `mysql`, which is where the time zone information belongs. The redirect reads from the file (which, in this case, is actually a redirected STDOUT) into the database. -- /Dan Daniel P. Brown Senior Unix Geek ? while(1) { $me = $mind--; sleep(86400); } ? Thanks Daniel. The command worked with this syntax: root mysql_tzinfo_to_sql /usr/share/zoneinfo/America/ | mysql -D mysql -u xxx -p xxx Still curious though why the databse needed to be explicitly selected now when I don't recall having to do that before. mysql_tzinfo_to_sql surely must know which db.tables to update... --David.
Re: No database selected error when running mysql_tzinfo_to_sql
Its example line is mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql There's a trailing mysql, which specifies the mysql database. It also specifies all zoneinfo, not America/. Thanks, Tim. I totally missed the trailing mysql. Duh! --David.
Re: No database selected error when running mysql_tzinfo_to_sql
On Mon, Mar 10, 2008 at 11:36 AM, Tim McDaniel [EMAIL PROTECTED] wrote: On Mon, 10 Mar 2008, Daniel Brown [EMAIL PROTECTED] wrote: mysql -D mysql -u xxx -p mysql_tzinfo_to_sql /usr/share/zoneinfo/America/ The -D flag selects the database `mysql`, which is where the time zone information belongs. The redirect reads from the file (which, in this case, is actually a redirected STDOUT) into the database. That is not legal shell syntax on UNIXy systems (or CMD.EXE, for that matter). is followed by the input file name, so the command above would read a file named mysql_tzinfo_to_sql in the current directory. It does not run the mysql_tzinfo_to_sql command. The way to redirect command output into the input of another command is to use | in the proper way. Please see the on-line man pages that I posted in my other note a minute ago. You're right. I can't find the pipe character on my Treo 700wx's keyboard, so I meant to show an alternative method (directing it to a file and directing the file into MySQL), but must've screwed up and lost train of thought halfway through. Odd. It should've been as follows: mysql_tzinfo_to_sql /usr/share/zoneinfo/America/ /tmp/time_zone_info.sql mysql -D mysql -u xxx -p /tmp/time_zone_info.sql -- /Dan Daniel P. Brown Senior Unix Geek ? while(1) { $me = $mind--; sleep(86400); } ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: No database selected error when running mysql_tzinfo_to_sql
On Mon, 10 Mar 2008, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: wrote: The command worked with this syntax: root mysql_tzinfo_to_sql /usr/share/zoneinfo/America/ | mysql -D mysql -u xxx -p xxx It's easy to know that the command ran without error, but I don't know how to test it to know that it actually did what was intended. That is, when I ran mysql_tzinfo_to_sql /usr/share/zoneinfo | less (no America/) on the archaic version of MySQL we run, the timezone names were like INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('Africa/Abidjan', @time_zone_id); ... INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('America/Adak', @time_zone_id); ... INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('US/Central', @time_zone_id); ... But as mysql_tzinfo_to_sql /usr/share/zoneinfo/America | less it used only timezones in America/ and without that leading value, like INSERT INTO time_zone_name (Name, Time_zone_id) VALUES ('Adak', @time_zone_id); Furthermore, the output starts TRUNCATE TABLE time_zone; TRUNCATE TABLE time_zone_name; TRUNCATE TABLE time_zone_transition; TRUNCATE TABLE time_zone_transition_type; so the process wipes out all other time zone information. So you lose zones like UTC, EST5EDT, US/Central, posix/America/Fort_Wayne, and such. You'll only have city or country names like Chicago, Barbados, Argentina/Rio_Gallegos, New_York, and such. I know little about how timezones are handled in MySQL, but that's why I personally would stick to using the provided examples. Still curious though why the databse needed to be explicitly selected now when I don't recall having to do that before. mysql_tzinfo_to_sql surely must know which db.tables to update... Run it and look at the output with your favorite pager or text editor. In my ancient MySQL, at least, all the table names are emitted without a database specified, as shown in the output above. I can see a use for omitting the database name from the output. Without it, you can load the timezone data into a test database and examine it there with SQL, without clobbering the system information. -- Tim McDaniel, [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select Statement
In fact my sql statement is like this: select b.customer_name Customer,a.sale_id DocNo,a.sale_date Date,a.prod_code Product,a.quantity Quantity,c.cost_price Cost,a.price Price, c.prod_description,a.store,d.payMode from sale_trans a,customer_master b,prod_master c,saletrans_cons d where a.sale_id=d.sale_id and d.cust_code = b.customer_code And a.prod_code = c.ProdBarcode And a.prod_code between 'txtbarcodefm' and 'txtbarcodeto' and a.sale_date between 'dtFrom' and 'dtTo' and a.sbranchid between 'brNmfm' and 'brNmto' group by a.nuniqid order by .prod_code,a.sale_id,b.customer_name The sale_id can be duplicate as different sbranchid can have same sale_id. My problem is that it is displaying a.sale_id but different customer_name as it is taking sale_id from d and matching cust_code with b The tables contains links as follows: a contains sale_id b contains cust_code c contains prodbarcode d contains sale_id,cust_code Can you suggest any correction? Thanks Velen
Re: Select Statement
Velen, My problem is that it is displaying a.sale_id but different customer_name as it is taking sale_id from d and matching cust_code with b Any non-aggregate SELECTed value that does not have a 1:1 relationship with your GROUP BY column will show arbitrary results, so the first thing to get clear on is what the GROUP BY clause is intended to do. PB - Velen wrote: In fact my sql statement is like this: select b.customer_name Customer,a.sale_id DocNo,a.sale_date Date,a.prod_code Product,a.quantity Quantity,c.cost_price Cost,a.price Price, c.prod_description,a.store,d.payMode from sale_trans a,customer_master b,prod_master c,saletrans_cons d where a.sale_id=d.sale_id and d.cust_code = b.customer_code And a.prod_code = c.ProdBarcode And a.prod_code between 'txtbarcodefm' and 'txtbarcodeto' and a.sale_date between 'dtFrom' and 'dtTo' and a.sbranchid between 'brNmfm' and 'brNmto' group by a.nuniqid order by .prod_code,a.sale_id,b.customer_name The sale_id can be duplicate as different sbranchid can have same sale_id. My problem is that it is displaying a.sale_id but different customer_name as it is taking sale_id from d and matching cust_code with b The tables contains links as follows: a contains sale_id b contains cust_code c contains prodbarcode d contains sale_id,cust_code Can you suggest any correction? Thanks Velen No virus found in this incoming message. Checked by AVG. Version: 7.5.518 / Virus Database: 269.21.7/1323 - Release Date: 3/10/2008 11:07 AM
Re: Select Statement
On Mon, Mar 10, 2008 at 1:38 PM, Peter Brawley [EMAIL PROTECTED] wrote: Velen, My problem is that it is displaying a.sale_id but different customer_name as it is taking sale_id from d and matching cust_code with b Any non-aggregate SELECTed value that does not have a 1:1 relationship with your GROUP BY column will show arbitrary results, so the first thing to get clear on is what the GROUP BY clause is intended to do. I agree with Peter. To help avoid problems, try this: SET @@sql_mode := 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'; Now run your query again. You should get an error if you're selecting a non-grouped column in a GROUP BY query. I think the above settings are sort of a baseline for sanity's sake. They keep you from doing invalid or stupid things without knowing it. MySQL lets you do these things by default. Baron PB - Velen wrote: In fact my sql statement is like this: select b.customer_name Customer,a.sale_id DocNo,a.sale_date Date,a.prod_code Product,a.quantity Quantity,c.cost_price Cost,a.price Price, c.prod_description,a.store,d.payMode from sale_trans a,customer_master b,prod_master c,saletrans_cons d where a.sale_id=d.sale_id and d.cust_code = b.customer_code And a.prod_code = c.ProdBarcode And a.prod_code between 'txtbarcodefm' and 'txtbarcodeto' and a.sale_date between 'dtFrom' and 'dtTo' and a.sbranchid between 'brNmfm' and 'brNmto' group by a.nuniqid order by .prod_code,a.sale_id,b.customer_name The sale_id can be duplicate as different sbranchid can have same sale_id. My problem is that it is displaying a.sale_id but different customer_name as it is taking sale_id from d and matching cust_code with b The tables contains links as follows: a contains sale_id b contains cust_code c contains prodbarcode d contains sale_id,cust_code Can you suggest any correction? Thanks Velen No virus found in this incoming message. Checked by AVG. Version: 7.5.518 / Virus Database: 269.21.7/1323 - Release Date: 3/10/2008 11:07 AM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Updating rows in a table with the information from the same table
I have a products table with historical price information. Some records are missing price information. I added another field - closest_price, to be populated for records with 0 price. This would be price values from the same table, same product with non-zero price with earliest date. So my update statement looks like this: update t1 a, (select price_date, product_id, price from t1 group by product_id having price_date = min(price_date) and price != 0 ) b set a.closest_price = b.price where a.product_id = b.product_id and a.price = 0; This statement doesn't work. I don't get error - just 0 rows updated. I do get results from b if I ran it on its own. Appreciate any help -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Migrate HUGE Database
Hello, I have a huge database that I would like to migrate from a server running 4.0.16 to a server running the Windows version 5.0.45. The database is approximately 3,500,000 records. I get timeout errors using PHPMyAdmin to export the data. Does anyone have any suggestions for how I can do this? Thanks, Terry Terry Babbey Infrastructure Specialist Information Technology, Lambton College of Applied Arts Technology [EMAIL PROTECTED], 519.542.7751 x3293
MySQL 6.0.4 Alpha has been released ! (part 1 of 2)
Dear MySQL users, MySQL 6.0.4-alpha, a new version of the MySQL database system including the Falcon transactional storage engine (now at beta stage), has been released. The main page for MySQL 6.0 is at: http://www.mysql.com/mysql60/ If you are new to the Falcon storage engine and need more information, please read the Falcon Evaluation Guide at: http://www.mysql.com/why-mysql/white-papers/falcon-getting-started.php and the Falcon White Paper at: http://www.mysql.com/why-mysql/white-papers/storage-engines-falcon.php MySQL 6.0.4-alpha is available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/mysql/6.0.html and mirror sites. Note that not all mirror sites may be up to date at this point in time, so if you can't find this version on some mirror, please try again later or choose another download site. We welcome and appreciate your feedback, bug reports, bug fixes, patches etc.: http://forge.mysql.com/wiki/Contributing Despite all trimming, describing all changes since the last released version of MySQL 6.0 exceeds the mailing list configuration. We had to split this message into two parts, this one (part 1) lists all changes which are labeled functionality, security, incompatible, or important. You can view the full list online at http://dev.mysql.com/doc/refman/6.0/en/news-6-0-4.html Functionality, security, incompatible, or important changes since the last release: Functionality added or changed: * Important Change: Partitioning: Security Fix: It was possible, by creating a partitioned table using the DATA DIRECTORY and INDEX DIRECTORY options to gain privileges on other tables having the same name as the partitioned table. As a result of this fix, any table-level DATA DIRECTORY or INDEX DIRECTORY options are now ignored for partitioned tables. (Bug#32091: http://bugs.mysql.com/32091, CVE-2007-5970 (http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2007-5970)) See also Bug#29325: http://bugs.mysql.com/29325, Bug#32111: http://bugs.mysql.com/32111 * Incompatible Change: The Unicode implementation has been extended to provide support for supplementary characters that lie outside the Basic Multilingual Plane (BMP). Noteworthy features: + utf16 and utf32 character sets have been added. These correspond to the UTF-16 and UTF-32 encodings of the Unicode character set, and they both support supplementary characters. + The utf8 character set from previous versions of MySQL has been renamed to utf8mb3, to reflect that its encoding uses a maximum of three bytes for multi-byte characters. (Old tables that previously used utf8 will be reported as using utf8mb3 after an in-place upgrade to MySQL 6.0, but otherwise work as before.) + The new utf8 character set in MySQL 6.0 is similar to utf8mb3, but its encoding allows up to four bytes per character to enable support for supplementary characters. + The ucs2 character set is essentially unchanged except for the inclusion of some newer BMP characters. In most respects, upgrading from MySQL 5.1 to 6.0 should present few problems with regard to Unicode usage, although there are some potential areas of incompatibility. Some examples: + For the variable-length character data types (VARCHAR and the TEXT types), the maximum length in characters for utf8 columns is less in MySQL 6.0 than previously. + For all character data types (CHAR, VARCHAR, and the TEXT types), the maximum number of characters for utf8 columns that can be indexed is less in MySQL 6.0 than previously. Consequently, if you want to upgrade tables from the old utf8 (now utf8mb3) to the current utf8, it may be necessary to change some column or index definitions. For additional details about the new Unicode character sets and potential incompatibilities, see Section 9.1.8, Unicode Support, and Section 9.1.9, Upgrading from Previous to Current Unicode Support. If you use events, a known issue is that if you upgrade from MySQL 5.1 to 6.0.4, the event scheduler will not work, even after you run mysql_upgrade. (This is an issue only for an upgrade, not for a new installation of MySQL 6.0.4.) To work around this upgrading problem, use these instructions: 1. In MySQL 5.1, before upgrading, create a dump file containing your mysql.event table: shell mysqldump -uroot -p mysql event event.sql 2. Stop the server, upgrade to MySQL 6.0, and start the server. 3. Recreate the mysql.event table using the dump file: shell mysql -uroot -p mysql event.sql 4. Run mysql_upgrade to upgrade the other system tables in the mysql database: shell mysql_upgrade -uroot -p 5. Restart the server. The event scheduler should run normally. * Incompatible Change: Because of a change in the format of the Falcon pages stored within Falcon database files, Falcon databases created in MySQL 6.0.4
MySQL 6.0.4 Alpha has been released ! (part 2 of 2)
Dear MySQL users, MySQL 6.0.4-alpha, a new version of the MySQL database system including the Falcon transactional storage engine (now at beta stage), has been released. The main page for MySQL 6.0 is at: http://www.mysql.com/mysql60/ If you are new to the Falcon storage engine and need more information, please read the Falcon Evaluation Guide at: http://www.mysql.com/why-mysql/white-papers/falcon-getting-started.php and the Falcon White Paper at: http://www.mysql.com/why-mysql/white-papers/storage-engines-falcon.php MySQL 6.0.4-alpha is available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/mysql/6.0.html and mirror sites. Note that not all mirror sites may be up to date at this point in time, so if you can't find this version on some mirror, please try again later or choose another download site. We welcome and appreciate your feedback, bug reports, bug fixes, patches etc.: http://forge.mysql.com/wiki/Contributing Despite all trimming, describing all changes since the last released version of MySQL 6.0 exceeds the mailing list configuration. We had to split this message into two parts: part 1 (the other) lists all changes which are labeled functionality, security, incompatible, or important, while this one lists the changes without such a label. You can view the full list online at http://dev.mysql.com/doc/refman/6.0/en/news-6-0-4.html Changes since the last release: ... Bugs fixed (part 2): * Partitioning: MySQL Cluster: EXPLAIN PARTITIONS reported partition usage by queries on NDB tables according to the standard MySQL hash function than the hash function used in the NDB storage engine. (Bug#29550: http://bugs.mysql.com/29550) * Replication: MySQL Cluster: Row-based replication from or to a big-endian machine where the table used the NDB storage engine failed, if the same table on the other machine was either non-NDB or the other machine was little-endian. (Bug#29549: http://bugs.mysql.com/29549, Bug#30790: http://bugs.mysql.com/30790) See also Bug#24231: http://bugs.mysql.com/24231, Bug#30024: http://bugs.mysql.com/30024, Bug#30133: http://bugs.mysql.com/30133, Bug#30134: http://bugs.mysql.com/30134 * MySQL Cluster: An insert or update with combined range and equality constraints failed when run against an NDB table with the error Got unknown error from NDB. An example of such a statement would be UPDATE t1 SET b = 5 WHERE a IN (7,8) OR a = 10;. (Bug#31874: http://bugs.mysql.com/31874) * MySQL Cluster: An error with an if statement in sql/ha_ndbcluster.cc could potentially lead to an infinite loop in case of failure when working with AUTO_INCREMENT columns in NDB tables. (Bug#31810: http://bugs.mysql.com/31810) * MySQL Cluster: The NDB storage engine code was not safe for strict-alias optimization in gcc 4.2.1. (Bug#31761: http://bugs.mysql.com/31761) * MySQL Cluster: Following an upgrade, ndb_mgmd would fail with an ArbitrationError. (Bug#31690: http://bugs.mysql.com/31690) * MySQL Cluster: It was possible in some cases for a node group to be lost due to missed local checkpoints following a system restart. (Bug#31525: http://bugs.mysql.com/31525) * MySQL Cluster: A query against a table with TEXT or BLOB columns that would return more than a certain amount of data failed with Got error 4350 'Transaction already aborted' from NDBCLUSTER. (Bug#31482: http://bugs.mysql.com/31482) This regression was introduced by Bug#29102: http://bugs.mysql.com/29102 * MySQL Cluster: NDB tables having names containing non-alphanumeric characters (such as $ ) were not discovered correctly. (Bug#31470: http://bugs.mysql.com/31470) * MySQL Cluster: A node failure during a local checkpoint could lead to a subsequent failure of the cluster during a system restart. (Bug#31257: http://bugs.mysql.com/31257) * MySQL Cluster: In some cases, the cluster managment server logged entries multiple times following a restart of mgmd. (Bug#29565: http://bugs.mysql.com/29565) * MySQL Cluster: ndb_mgm --help did not display any information about the -a option. (Bug#29509: http://bugs.mysql.com/29509) * MySQL Cluster: An interpreted program of sufficient size and complexity could cause all cluster data nodes to shut down due to buffer overruns. (Bug#29390: http://bugs.mysql.com/29390) * MySQL Cluster: Performing DELETE operations after a data node had been shut down could lead to inconsistent data following a restart of the node. (Bug#26450: http://bugs.mysql.com/26450) * MySQL Cluster: UPDATE IGNORE could sometimes fail on NDB tables due to the use of unitialized data when checking for duplicate keys to be ignored. (Bug#25817: http://bugs.mysql.com/25817) * MySQL Cluster: The cluster log was formatted inconsistently and contained extraneous newline characters. (Bug#25064: http://bugs.mysql.com/25064) * MySQL Cluster: (Replication):
Re: Migrate HUGE Database
On Mon, Mar 10, 2008 at 2:29 PM, Terry Babbey [EMAIL PROTECTED] wrote: Hello, I have a huge database that I would like to migrate from a server running 4.0.16 to a server running the Windows version 5.0.45. The database is approximately 3,500,000 records. I get timeout errors using PHPMyAdmin to export the data. If you have shell access, do the following: mysqldump -u username -p database_name database_name.sql To explain: -u username Replace 'username' with the database username. -p This signifies that you'll use a password (at a prompt) database_nameThe full name of the database to dump Redirects all output to a file, deleting previous data, if any database_name.sql The SQL output file, written to the current directory. -- /Dan Daniel P. Brown Senior Unix Geek ? while(1) { $me = $mind--; sleep(86400); } ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migrate HUGE Database
On Mon, Mar 10, 2008 at 11:29 AM, Terry Babbey [EMAIL PROTECTED] wrote: Hello, I have a huge database that I would like to migrate from a server running 4.0.16 to a server running the Windows version 5.0.45. The database is approximately 3,500,000 records. I get timeout errors using PHPMyAdmin to export the data. Does anyone have any suggestions for how I can do this? Thanks, Terry Terry Babbey Infrastructure Specialist Information Technology, Lambton College of Applied Arts Technology [EMAIL PROTECTED], 519.542.7751 x3293 First off 3.5M is not huge to many of us... http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html would be the way to go. -- Rob Wultsch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migrate HUGE Database
On Mon, 10 Mar 2008 at 14:29 -0400, [EMAIL PROTECTED] confabulated: Hello, I have a huge database that I would like to migrate from a server running 4.0.16 to a server running the Windows version 5.0.45. The database is approximately 3,500,000 records. I get timeout errors using PHPMyAdmin to export the data. Does anyone have any suggestions for how I can do this? Perhap you can use mysqldump to dump out what needs to be moved and import the resulting dump into the other server after copying the dump over. I'm sure there are other ways of doing this. - _|_ |_| | -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Migrate HUGE Database
You can take this a step further nohup mysqldump -hhost of Linux Machine -uusername -ppassword --all-databases --routines --triggers | mysql -hhost of Windows Machine -A This will pipe all the data directly to Windows machine without an intermittent file. Even if you logout of Linux, it should keep going -Original Message- From: Daniel Brown [mailto:[EMAIL PROTECTED] Sent: Monday, March 10, 2008 2:40 PM To: Terry Babbey Cc: mysql@lists.mysql.com Subject: Re: Migrate HUGE Database On Mon, Mar 10, 2008 at 2:29 PM, Terry Babbey [EMAIL PROTECTED] wrote: Hello, I have a huge database that I would like to migrate from a server running 4.0.16 to a server running the Windows version 5.0.45. The database is approximately 3,500,000 records. I get timeout errors using PHPMyAdmin to export the data. If you have shell access, do the following: mysqldump -u username -p database_name database_name.sql To explain: -u username Replace 'username' with the database username. -p This signifies that you'll use a password (at a prompt) database_nameThe full name of the database to dump Redirects all output to a file, deleting previous data, if any database_name.sql The SQL output file, written to the current directory. -- /Dan Daniel P. Brown Senior Unix Geek ? while(1) { $me = $mind--; sleep(86400); } ? -- 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]
Copying tables
Hi Folks I'm trying to copy a database table form one database to another on a different server. Is it possible through myphpadmin, or do I need software? If software, do you know of any good programs to do this? Bob -- View this message in context: http://www.nabble.com/Copying-tables-tp15951907p15951907.html Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Copying tables
On Mon, Mar 10, 2008 at 2:58 PM, skills2go [EMAIL PROTECTED] wrote: Hi Folks I'm trying to copy a database table form one database to another on a different server. Is it possible through myphpadmin, or do I need software? If software, do you know of any good programs to do this? The same question just received an excellent answer from Rolando Edwards about ten minutes ago. Check the archive here: http://marc.info/?l=mysqlm=120517563300467w=2 -- /Dan Daniel P. Brown Senior Unix Geek ? while(1) { $me = $mind--; sleep(86400); } ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migrate HUGE Database
On Mon, 10 Mar 2008, Daniel Brown [EMAIL PROTECTED] wrote: mysqldump -u username -p database_name database_name.sql To explain: -u usernameReplace 'username' with the database username. -p This signifies that you'll use a password (at a prompt) database_name The full name of the database to dump Redirects all output to a file, deleting previous data, if any I was a bit puzzled seeing -p database_name, as I was expecting that to be seen as the password. After a bit of experimentation, it turns out that (for example) -uUSERNAME is treated the same as -u USERNAME but -pPASSWORD interprets PASSWORD as the password, but -p WORD prompts for the password and uses WORD as the next argument (no relation to the password), in this case as the database name. How very inconsistent and obnoxious. Daniel, thank you for the prompt to look at this. -- Tim McDaniel, [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Copying tables
On Mon, 10 Mar 2008, Daniel Brown [EMAIL PROTECTED] wrote: On Mon, Mar 10, 2008 at 2:58 PM, skills2go [EMAIL PROTECTED] wrote: I'm trying to copy a database table form one database to another on a different server. Is it possible through myphpadmin, or do I need software? If software, do you know of any good programs to do this? The same question just received an excellent answer from Rolando Edwards about ten minutes ago. Check the archive here: http://marc.info/?l=mysqlm=120517563300467w=2 The one missing piece: mysqldump can choose to dump only one or a few databases, and if given one database name, can dump only selected tables. http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html: There are three general ways to invoke mysqldump: shell mysqldump [options] db_name [tables] shell mysqldump [options] --databases db_name1 [db_name2 db_name3...] shell mysqldump [options] --all-databases Read the manual for your version of MySQL for more options. --opt looks like it might be most useful. -- Tim McDaniel, [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Copying tables
On Mon, Mar 10, 2008 at 3:26 PM, Tim McDaniel [EMAIL PROTECTED] wrote: On Mon, 10 Mar 2008, Daniel Brown [EMAIL PROTECTED] wrote: The same question just received an excellent answer from Rolando Edwards about ten minutes ago. Check the archive here: http://marc.info/?l=mysqlm=120517563300467w=2 The one missing piece: mysqldump can choose to dump only one or a few databases, and if given one database name, can dump only selected tables. http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html: Good point. I forgot to mention that. Thanks, Tim. -- /Dan Daniel P. Brown Senior Unix Geek ? while(1) { $me = $mind--; sleep(86400); } ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migrate HUGE Database
Tim McDaniel wrote: I was a bit puzzled seeing -p database_name, ... How very inconsistent and obnoxious. It's best to think of -p as never taking an argument, always asking interactively. Many operating systems will let a processes access the command line parameters of another process, making it possible to get the password in the clear if you pass it to a program this way. It's nice to know that you can pass it this way if absolutely necessary, but I try not to use it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migrate HUGE Database
mysqldump from the commandline. You are most likely running into php execution time limits using phpmyadmin OR you could probably just copying the underlying files, .frm,MYI and MYD I've successfully done that with myisam databases going from version 4 - 5 on tables exceeding 50M rows. Not sure about innoDB though. On Mon, Mar 10, 2008 at 1:29 PM, Terry Babbey [EMAIL PROTECTED] wrote: Hello, I have a huge database that I would like to migrate from a server running 4.0.16 to a server running the Windows version 5.0.45. The database is approximately 3,500,000 records. I get timeout errors using PHPMyAdmin to export the data. Does anyone have any suggestions for how I can do this? Thanks, Terry Terry Babbey Infrastructure Specialist Information Technology, Lambton College of Applied Arts Technology [EMAIL PROTECTED], 519.542.7751 x3293 -- Help build our city at http://free-dc.myminicity.com !
RE: Migrate HUGE Database
Thanks to all for the quick replies. Yes, the mysqldump worked perfectly. Boy do I feel like a newbie now! If I use the method below, will that transfer the mysql admin database too with the user information? Thanks, Terry -Original Message- From: Rolando Edwards [mailto:[EMAIL PROTECTED] Sent: March 10, 2008 2:52 PM To: Daniel Brown; Terry Babbey Cc: mysql@lists.mysql.com Subject: RE: Migrate HUGE Database You can take this a step further nohup mysqldump -hhost of Linux Machine -uusername -ppassword --all-databases --routines --triggers | mysql -hhost of Windows Machine -A This will pipe all the data directly to Windows machine without an intermittent file. Even if you logout of Linux, it should keep going -Original Message- From: Daniel Brown [mailto:[EMAIL PROTECTED] Sent: Monday, March 10, 2008 2:40 PM To: Terry Babbey Cc: mysql@lists.mysql.com Subject: Re: Migrate HUGE Database On Mon, Mar 10, 2008 at 2:29 PM, Terry Babbey [EMAIL PROTECTED] wrote: Hello, I have a huge database that I would like to migrate from a server running 4.0.16 to a server running the Windows version 5.0.45. The database is approximately 3,500,000 records. I get timeout errors using PHPMyAdmin to export the data. If you have shell access, do the following: mysqldump -u username -p database_name database_name.sql To explain: -u username Replace 'username' with the database username. -p This signifies that you'll use a password (at a prompt) database_nameThe full name of the database to dump Redirects all output to a file, deleting previous data, if any database_name.sql The SQL output file, written to the current directory. -- /Dan Daniel P. Brown Senior Unix Geek ? while(1) { $me = $mind--; sleep(86400); } ? -- 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: Migrate HUGE Database
On Mon, Mar 10, 2008 at 4:05 PM, Terry Babbey [EMAIL PROTECTED] wrote: Thanks to all for the quick replies. Yes, the mysqldump worked perfectly. Boy do I feel like a newbie now! If I use the method below, will that transfer the mysql admin database too with the user information? Thanks, Terry nohup mysqldump -hhost of Linux Machine -uusername -ppassword --all-databases --routines --triggers | mysql -hhost of Windows Machine -A Yes, the --all-databases flag recursively copies all databases and tables, including the 'mysql' database with user and time zone information. -- /Dan Daniel P. Brown Senior Unix Geek ? while(1) { $me = $mind--; sleep(86400); } ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Migrate HUGE Database
On Mon, 10 Mar 2008, Daniel Brown [EMAIL PROTECTED] wrote: On Mon, Mar 10, 2008 at 4:05 PM, Terry Babbey [EMAIL PROTECTED] wrote: If I use the method below, will that transfer the mysql admin database too with the user information? nohup mysqldump -hhost of Linux Machine -uusername -ppassword --all-databases --routines --triggers | mysql -hhost of Windows Machine -A Yes, the --all-databases flag recursively copies all databases and tables, including the 'mysql' database with user and time zone information. To expand on that: mysqldump outputs plain SQL statements, which are readable text unless you have character data or identifiers that are UNICODE or something. (You can deduce that by noting that mysql is the normal CLI-type interface, and the pipe just feeds the output of mysqlsump into mysql as if you'd typed the CREATE TABLE, INSERT, etc. commands yourself.) So if you have any questions about what mysqldump outputs, you can feed its output into a pager program like less or more, or into a temporary file, and just look at it. One way to reduce a mountain of output would be to just mysqldump specific databases or tables. -- Tim McDaniel, [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ordering my regex
Hi all, I'm doing a select * from comments where c.content REGEXP 'http://[^i].*' and I would like to sort the urls found by repetition of the same urls. As an example if I get 3 records with http://google.com url in the content and two with http://mysql.com I would get the first the 3 comments with google.com and then the 2 with mysql.com. Any idea how to do that? Thanks in advance. Pat -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how: many-to-many with LEFT JOIN
Hello guys before to a last doom requeriment, i used to work with this type of relation ArticuloNoAuto (english ArticleNoCar like MotorCycle or Motor) and CabeceraComprobanteVenta (like a header of Receipt of some sell SalesReceiptHeader) so 1 CabeceraComprobanteVenta can [b]sell/contain one[/b] ArticuloNoAuto and 1 ArticuloNoAuto can be contained in 1 to 2 CabeceraComprobanteVenta (normal) or (canceled,newnormal) so this sql work fine [code] SELECT ccv.*,dcv.*,ana.*,ar.*,c.*, ar.descripcion as descripcionArticulo , dcv.valorVenta as valorVentaDetalle, m.nombre as nombreMedida FROM cabeceracomprobanteventa ccv LEFT JOIN detallecomprobanteventa dcv ON dcv.idCabeceraComprobanteVenta=ccv.idCabeceraComprobanteVenta LEFT JOIN articulonoauto ana ON ccv.idArticuloNoAuto=ana.idArticuloNoAuto LEFT JOIN articulo ar ON dcv.idArticulo=ar.idArticulo LEFT JOIN medida m ON m.idMedida=ar.idMedida LEFT JOIN cliente c ON c.idCliente=ccv.idCliente WHERE ccv.numComprobanteVenta='003-03' [/code] i can see in the IReport preview the header of the Sales Receipt and the MotorCycle and maybe [b](not always exists some items detallecomprobanteventa, like oil,car parts)[/b] the last new requeriment was that a wonderful 1 CabeceraComprobanteVenta can sell/contain [b]many items[/b] ArticuloNoAuto (1-4) and 1 ArticuloNoAuto can be contained in 1 to 2 CabeceraComprobanteVenta (normal) or (canceled,newnormal) so we have a relation many-to-many already resolved by hibernate with a link table called articulonoautocabeceracomprobanteventa so my new sql query is [code] SELECT ccv.*,dcv.*,ana.*,ar.*,c.*, ar.descripcion as descripcionArticulo , dcv.valorVenta as valorVentaDetalle, m.nombre as nombreMedida [b]FROM articulonoautocabeceracomprobanteventa link[/b] LEFT JOIN cabeceracomprobanteventa ccv ON [b]ccv.idCabeceraComprobanteVenta=link.idCabeceraComprobanteVenta[/b] LEFT JOIN articulonoauto ana ON [b]link.idArticuloNoAuto=ana.idArticuloNoAuto[/b] LEFT JOIN detallecomprobanteventa dcv ON dcv.idCabeceraComprobanteVenta=ccv.idCabeceraComprobanteVenta LEFT JOIN articulo ar ON dcv.idArticulo=ar.idArticulo LEFT JOIN medida m ON m.idMedida=ar.idMedida LEFT JOIN cliente c ON c.idCliente=ccv.idCliente WHERE ccv.numComprobanteVenta='003-88' [/code] see now this [b]FROM articulonoautocabeceracomprobanteventa link[/b] the code works but there is a problematic detail i have this situation, if i want to sell 2 MotorCycle with 2 car parts in my IReport preview i see 4 MotorCycles (the 2 desired and theses same repeated 2 times) why? if i sell 2 MotorCycle [b]without 2[/b] car parts i can see the only 2 desired MotorCycle whay is wrong??? and how i can resolve this?? some missing condition in the query? thanks in advanced p.d: mysql 5.0.27 -- View this message in context: http://www.nabble.com/how%3A-many-to-many-with-LEFT-JOIN-tp15974071p15974071.html Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 6.0.4 Alpha has been released ! (part 1 of 2)
Missing feature not mentioned... Falcon works on PowerPC and UltraSparc. Regards, Antony On 10 Mar, 2008, at 11:53, Joerg Bruehe wrote: Dear MySQL users, MySQL 6.0.4-alpha, a new version of the MySQL database system including the Falcon transactional storage engine (now at beta stage), has been released. The main page for MySQL 6.0 is at: http://www.mysql.com/mysql60/ If you are new to the Falcon storage engine and need more information, please read the Falcon Evaluation Guide at: http://www.mysql.com/why-mysql/white-papers/falcon-getting-started.php and the Falcon White Paper at: http://www.mysql.com/why-mysql/white-papers/storage-engines-falcon.php MySQL 6.0.4-alpha is available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/mysql/6.0.html and mirror sites. Note that not all mirror sites may be up to date at this point in time, so if you can't find this version on some mirror, please try again later or choose another download site. We welcome and appreciate your feedback, bug reports, bug fixes, patches etc.: http://forge.mysql.com/wiki/Contributing Despite all trimming, describing all changes since the last released version of MySQL 6.0 exceeds the mailing list configuration. We had to split this message into two parts, this one (part 1) lists all changes which are labeled functionality, security, incompatible, or important. You can view the full list online at http://dev.mysql.com/doc/refman/6.0/en/news-6-0-4.html Functionality, security, incompatible, or important changes since the last release: Functionality added or changed: * Important Change: Partitioning: Security Fix: It was possible, by creating a partitioned table using the DATA DIRECTORY and INDEX DIRECTORY options to gain privileges on other tables having the same name as the partitioned table. As a result of this fix, any table-level DATA DIRECTORY or INDEX DIRECTORY options are now ignored for partitioned tables. (Bug#32091: http://bugs.mysql.com/32091, CVE-2007-5970 (http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2007-5970)) See also Bug#29325: http://bugs.mysql.com/29325, Bug#32111: http://bugs.mysql.com/32111 * Incompatible Change: The Unicode implementation has been extended to provide support for supplementary characters that lie outside the Basic Multilingual Plane (BMP). Noteworthy features: + utf16 and utf32 character sets have been added. These correspond to the UTF-16 and UTF-32 encodings of the Unicode character set, and they both support supplementary characters. + The utf8 character set from previous versions of MySQL has been renamed to utf8mb3, to reflect that its encoding uses a maximum of three bytes for multi-byte characters. (Old tables that previously used utf8 will be reported as using utf8mb3 after an in-place upgrade to MySQL 6.0, but otherwise work as before.) + The new utf8 character set in MySQL 6.0 is similar to utf8mb3, but its encoding allows up to four bytes per character to enable support for supplementary characters. + The ucs2 character set is essentially unchanged except for the inclusion of some newer BMP characters. In most respects, upgrading from MySQL 5.1 to 6.0 should present few problems with regard to Unicode usage, although there are some potential areas of incompatibility. Some examples: + For the variable-length character data types (VARCHAR and the TEXT types), the maximum length in characters for utf8 columns is less in MySQL 6.0 than previously. + For all character data types (CHAR, VARCHAR, and the TEXT types), the maximum number of characters for utf8 columns that can be indexed is less in MySQL 6.0 than previously. Consequently, if you want to upgrade tables from the old utf8 (now utf8mb3) to the current utf8, it may be necessary to change some column or index definitions. For additional details about the new Unicode character sets and potential incompatibilities, see Section 9.1.8, Unicode Support, and Section 9.1.9, Upgrading from Previous to Current Unicode Support. If you use events, a known issue is that if you upgrade from MySQL 5.1 to 6.0.4, the event scheduler will not work, even after you run mysql_upgrade. (This is an issue only for an upgrade, not for a new installation of MySQL 6.0.4.) To work around this upgrading problem, use these instructions: 1. In MySQL 5.1, before upgrading, create a dump file containing your mysql.event table: shell mysqldump -uroot -p mysql event event.sql 2. Stop the server, upgrade to MySQL 6.0, and start the server. 3. Recreate the mysql.event table using the dump file: shell mysql -uroot -p mysql event.sql 4. Run mysql_upgrade to upgrade the other system tables in the mysql database: shell mysql_upgrade -uroot -p 5. Restart the server. The event scheduler should run normally. * Incompatible Change: Because of a change in the format of the Falcon pages stored within Falcon database files, Falcon