Re: Phone Number Storage
From: Sujay Koduri I guess anywhere we have 3 levels of hierarchies for a phone number. (Country code, Area code and the actual number). That's a pretty wild guess IMHO. You have to be absolutely sure that this is true for each and every country in the world. Furthermore, various telecom providers have short phone numbers for their own services that are only valid for their clients. If your application is to store these numbers too (e.g. a billing application) then you will have phone numbers without country and area code! The advantage of seperating them into different columns(Either an integer or a string) is that he can group different phone numbers based on area code or country code. Phone numbers cannot be considered to be numbers IMHO. The customer might want to store the name version of a phone number (in some countries you can get phone 'numbers' in certain fictive area codes where the actual number consists of letters (e.g. 0800-mysql). If these 'numbers' are to be stored too an integer type field will not suffice. If you need to group phone numbers it will only be based on country code, country code plus area code, or country code plus area code plus a part of the actual number. So you need to group by phone number starts with. This can easily be done with a string type and an index. Using a clever and consistent format to seperate the various parts of the phone number (whole parts with leading zero's!) combined with knowledge on how to build the number to dial (drop the leading zero or not, etc.) is all you need to make an application that works in more than one country. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL and Mandrake 10.2
I made an upgrade from Mandrake 10.1 to 10.2. Since, I cannot connect anymore to the database locally using '127.0.0.1': [EMAIL PROTECTED] sysconfig]# mysql -u root -h 127.0.0.1 ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111) I suppose that something has changed concerning the configuration of MySQL or the access to the network, but I do not what. Anyone an idea? -- Prof. Pascal Francq Université libre de Bruxelles CAD/CAM Department Avenue F.D. Roosevelt, 50 CP 165/14 B-1050 Brussels BELGIUM Tel. +32-2-650 47 65 Fax +32-2-650 47 24 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and Mandrake 10.2
See the release notes: http://qa.mandriva.com/twiki/bin/view/Main/MandrivaLinux2005ReleaseNotes#MySQL j- k- On Monday 25 July 2005 23:43, Pascal Francq said something like: I made an upgrade from Mandrake 10.1 to 10.2. Since, I cannot connect anymore to the database locally using '127.0.0.1': [EMAIL PROTECTED] sysconfig]# mysql -u root -h 127.0.0.1 ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1' (111) I suppose that something has changed concerning the configuration of MySQL or the access to the network, but I do not what. Anyone an idea? -- Prof. Pascal Francq Université libre de Bruxelles CAD/CAM Department Avenue F.D. Roosevelt, 50 CP 165/14 B-1050 Brussels BELGIUM Tel. +32-2-650 47 65 Fax +32-2-650 47 24 -- Joshua J. Kugler -- Fairbanks, Alaska -- ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Phone Number Storage
-Original Message- From: Jigal van Hemert [mailto:[EMAIL PROTECTED] Subject: Re: Phone Number Storage From: Sujay Koduri I guess anywhere we have 3 levels of hierarchies for a phone number. (Country code, Area code and the actual number). That's a pretty wild guess IMHO. You have to be absolutely sure that this is true for each and every country in the world. Furthermore, various telecom providers have short phone numbers for their own services that are only valid for their clients. If your application is to store these numbers too (e.g. a billing application) then you will have phone numbers without country and area code! I've spent much of the last 6 months dealing with Australian international numbers, and the true situation is considerably more complex than Sujay suggests. For example, standard Australian numbers are 8 digits, but 10 with STD (inter-state). Free call ranges are 10 digits starting with 18, local call are 6 or 10 digits starting with 13. Mobiles start with 04, domestic satellite phones start with 14 and Optus have 15 digit number ranges starting with 127. The usual international prefix is 0011, and customers can specify carrier override with 4 digits prepended to the destination number. The advantage of seperating them into different columns(Either an integer or a string) is that he can group different phone numbers based on area code or country code. Phone numbers cannot be considered to be numbers IMHO. The customer might want to store the name version of a phone number (in some countries you can get phone 'numbers' in certain fictive area codes where the actual number consists of letters (e.g. 0800-mysql). If these 'numbers' are to be stored too an integer type field will not suffice. If you need to group phone numbers it will only be based on country code, country code plus area code, or country code plus area code plus a part of the actual number. So you need to group by phone number starts with. This can easily be done with a string type and an index. Using a clever and consistent format to seperate the various parts of the phone number (whole parts with leading zero's!) combined with knowledge on how to build the number to dial (drop the leading zero or not, etc.) is all you need to make an application that works in more than one country. Separators tend to be standardised to an extent - usually space or hyphen, and voice switches ignore them, but assuming that they will only appear in specific places is unrealistic. Your comment about treating numbers as strings is spot-on (and initially may be counter-intuitive). Separating into area code plus number doesn't work here in Oz - for example, 08 is Western Australia, and also South Australia. And literally millions of numbers don't have an area code at all - mobiles and everything starting with 1. Paul Wilson iiNet Ltd 6/263 Adelaide Tce, Perth, WA6000 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and Mandrake 10.2
Since I make an install, Mandrake has hold the old /etc/init.d/mysql script (rather than /usr/init.d/mysqld) and the file in /ect/sysconfig/mysql was not created. On Tuesday 26 July 2005 09:56, Joshua J. Kugler wrote: http://qa.mandriva.com/twiki/bin/view/Main/MandrivaLinux2005ReleaseNotes#My SQL -- Prof. Pascal Francq Université libre de Bruxelles CAD/CAM Department Avenue F.D. Roosevelt, 50 CP 165/14 B-1050 Brussels BELGIUM Tel. +32-2-650 47 65 Fax +32-2-650 47 24 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Correct way to use innodb_file_per_table?
Hi Bruce, Thanks again for your advice. I have control over the client access so it a possibility to do what Heikki is suggesting, additionally I have to do it to a master and slave, I could always try it on one and if it fails then go down the dump and import route, this way I wouldn't lose data. We had similar issues with character sets also when upgrading but we got those resolved. Regarding the file size issue, we are on a 32-bit system running redhat AS3, we already have idb files in excess of 21Gb, I'm not sure what the limit is though if any ? Just one thought about the shared space, do you think it would be possible to back up all the current shared data files along with the iblog files, change the my.cnf file to use a single ibdata file of 2 gig, then try to start it up. If it did fail how about reverting the my.cnf and restoring the original ibdata and iblog files, would it still work after restoring the original files ? With this I could test Heikki idea without the pssibility of losing data. Cheers, Marvin. -Original Message- From: Bruce Dembecki [mailto:[EMAIL PROTECTED] Sent: 25 July 2005 17:55 To: Marvin Wright; mysql@lists.mysql.com Subject: Re: Correct way to use innodb_file_per_table? On Jul 25, 2005, at 5:33 AM, Marvin Wright wrote: Hi, Thanks for your reply. I've only just moved all tables to there own table space so that I can put certain databases on different disks. Right now my shared tablespace does not hold any databases. I'm aware that I still need the shared table space but I don't need 200gb now, I just want to decrease it down to 10Gb. It seems a bit daft that I still have to dump all tables even when they are in their own tablespace. I guess this is because the table definitions are still stored in the shared space. Marvin. Hi! These are good questions... Heikki once told me that if there is no activity going on AND the innodb status page shows nothing being processed AND everything is up to date according to the innodb status page, you could (in theory) shutdown mysql and bring it back with a new shared table space under these circumstances... That is going to require that every connection to the database server be idle, or better still shut off... Depending on how your machines access your database server that may be easy or hard to do... We had some character set issues to work on and were (are - it's an ongoing project) needing to do a dump and an import to do the move from 4.0 to 4.1 at the same time... So we didn't actually try and bounce a server into a smaller shared table space live... I have total control over my client connections to the database server and can easily prevent them from connecting with a hardware load balancer, and I'm still not sure I would want to try that though. Hint if you are going the dump and import route... The fastest way to dump and for sure the fastest way to import is to use mysqldump -- tab=/var/tmp/somewhere and use mysqlimport to import the tab delimited data... using --tab on the dump creates two files for each table.. an sql file with the create table statement, and a txt file with the tab delimited data... We create our databases using cat /var/ tmp/somewhere/*sql | mysql ourDatabase, and then use mysqlimport ourDatabase /var/tmp/somewhere/*.txt - mysql import is smart enough to insert data into tables matching the filename, it's the fastest way to do the whole dump and import thing by a lot. On the issue of how much shared space, Heikki told me 200Mbytes would be far more than we would need if everything is innodb_file_per_table... but as my old file space was made with 2000M files I just kept ibdata01 and commented out the rest of the line certainly haven't any issues with the 2Gbyte shared table space, I would think 10G would be overkill (I think my 2G is overkill). The only other area we discovered was an issue is that if you are running a 32 bit file system there is likely to be a a problem on any table that needs more file space than the file system will give a single file. The solutions here are to use a 64 bit file system which doesn't care so much, or create a larger shared table space and turn off innodb_file_per_table and alter the table to innodb (even if it is already innodb, altering it like this will recreate it new). turn on innodb_file_per_table again and that table will stay in the shared table space, the rest will be in their own files. the main problem here is that once the file reached the OS limit InnoDB thought the table was full(which technically it was)... so Innodb's autoextending files don't know how to launch a second file once the File system's upper limit has been reached. Best Regards, Bruce ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in
Re: MySQL and Mandrake 10.2
THe MySQL packages was not well installed. I remove the packages and re-install them after, the file /ect/sysconfig/mysql appears, and after modifications, everything works well. Thanks. On Tuesday 26 July 2005 10:09, Pascal Francq wrote: Since I make an install, Mandrake has hold the old /etc/init.d/mysql script (rather than /usr/init.d/mysqld) and the file in /ect/sysconfig/mysql was not created. On Tuesday 26 July 2005 09:56, Joshua J. Kugler wrote: http://qa.mandriva.com/twiki/bin/view/Main/MandrivaLinux2005ReleaseNotes# My SQL -- Prof. Pascal Francq Université libre de Bruxelles CAD/CAM Department Avenue F.D. Roosevelt, 50 CP 165/14 B-1050 Brussels BELGIUM Tel. +32-2-650 47 65 Fax +32-2-650 47 24 -- Prof. Pascal Francq Université libre de Bruxelles CAD/CAM Department Avenue F.D. Roosevelt, 50 CP 165/14 B-1050 Brussels BELGIUM Tel. +32-2-650 47 65 Fax +32-2-650 47 24 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ORACLE and Mysql: Create tables, constraints, swquences, storage. ..?
Good morning, I have scripts to create constraints, sequences, storage..., tables from ORACLE and I don't know if I can create them in MySQL? Appreciated your help, Thank you, Phong -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORACLE and Mysql: Create tables, constraints, swquences, storage. ..?
Hi, I have scripts to create constraints, sequences, storage..., tables from ORACLE and I don't know if I can create them in MySQL? MySQL doesn't have sequences or storage specifiers. You cannot run your script directly. Depending on your current Oracle structure, you might want to give our Schema Migration tool in Database Workbench a try. Download a copy here: www.upscene.com With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
malloc troubles on 64-bit machine
Hi all, We're trying to get a new 64-bit machine going to get around the memory limitations of the 32-bit machines we have. On this dual Opteron server with 8GB memory we've installed Fedora Core 4 and MySQL 4.1.13. Our sysadmin configured MySQL to use a 7GB buffer pool to accomodate a few big tables. After a while he needed to restart MySQL (made some changes somewhere) and it refused to do so: 050726 14:13:12 mysqld started 050726 14:13:12 InnoDB: Error: cannot allocate 7340048384 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 78086952 bytes. Operating system errno: 12 InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. InnoDB: We keep retrying the allocation for 60 seconds... InnoDB: Fatal error: cannot allocate the memory for the buffer pool 050726 14:14:12 [ERROR] Can't init databases 050726 14:14:12 [ERROR] Aborting 050726 14:14:12 [Note] /usr/sbin/mysqld: Shutdown complete 050726 14:14:12 mysqld ended He then rebooted the entire server and: 050726 14:16:37 mysqld started 050726 14:16:41 InnoDB: Started; log sequence number 0 43635 /usr/sbin/mysqld: ready for connections. Version: '4.1.13-standard' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL) ...it runs happily again. Any ideas anyone on the cause and (more importantly) how to fix this problem? Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORACLE and Mysql: Create tables, constraints, swquences, storage. ..?
Nguyen, Phong wrote: Good morning, I have scripts to create constraints, sequences, storage..., tables from ORACLE and I don't know if I can create them in MySQL? You can try our Migration Toolkit which has pretty good support for Oracle now... http://www.mysql.com/products/migration-toolkit/ One of the ways I like to use it is to reverse engineer the Oracle schema, and then have the MySQL schema definition script created for further modification. Note we do not have support for sequences, and its typical to just use AUTO_INCREMENT columns for these. Regards, Josh -- Josh Chamas Director, Professional Services MySQL Inc., www.mysql.com Get More with MySQL! www.mysql.com/consulting -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: very slow inserts on InnoDB [InnoDB Performance Tuning]
Hi Catalin, Here are some InnoDB performance tuning tips that may boost your insert speed: Catalin Trifu wrote: ... innodb_buffer_pool_size = 256M Higher is better, in fact pushing this up to 60%-80% on a dedicated database would be good. If there are other things running like a web server, then you will have to take its memory requirements into account, but 256M could likely be bigger. Maybe this could be 512M ? Then data sets up to this size will be as fast as possible. innodb_additional_mem_pool_size = 64M Rarely does this need to be set over 8M. # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 32M Try setting this to 1/2 your buffer pool size, otherwise you might get too much checkpointing during lots of inserts. innodb_log_buffer_size = 8M Looks good. innodb_flush_log_at_trx_commit = 1 Try setting to trx_commit = 2 for faster insert performance, however you then lose ACID transactions, where if you have a system failure you could lose around 1 second worth committed data. These suggestions will not necessarily fix your problem. If you continue to have issues and they go unresolved on this list, you might consider getting help via our commercial offerings: http://www.mysql.com/network/ - OR - http://www.mysql.com/consulting/packaged/performance.html http://www.mysql.com/consulting/packaged/rapidresponse.html Regards, Josh -- Josh Chamas Director, Professional Services MySQL Inc., www.mysql.com Get More with MySQL! www.mysql.com/consulting -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query on a very big table [MySQL partitioning of large tables]
Christos Andronis wrote: Hi all, we are trying to run the following query on a table that contains over 600 million rows: 'ALTER TABLE `typed_strengths` CHANGE `entity1_id` `entity1_id` int(10) UNSIGNED DEFAULT NULL FIRST' The query takes ages to run (has been running for over 10 hours now). Is this normal? Yes, this is normal, but not desirable of course! The fundamental issue is that your table is likely too large, and rebuilding indexes cannot fit into memory, and goes to disk. You need to break the table up into smaller shards or partitions using horizontal table partitioning methodologies. Usually what one will do is have say 10M or 100M rows in a table say for one months data or some such. If this is MyISAM, then all of those sub tables can be put into a MERGE, and queried normally: http://dev.mysql.com/doc/mysql/en/merge-storage-engine.html If this is InnoDB, then you have to teach your application how to query the partitions individually and aggregate the results on its own. We will have a better partitioning implementation in MySQL 5.1, being developed still, but you can get early information on this in the Partitioning Forum here: http://forums.mysql.com/list.php?106 One nice side effect in data warehousing type applications is that when you go to delete the data, you can just drop the old table, with having to do large deletes and rebuilding the table to defragment. As a side issue, is MySQL suited for such big tables? I've seen a couple of case studies with MySQL databases over 1.4 billion rows but it is not clear to me whether this size corresponds to the whole database or whether it is for a single table. Yes, its fine. You just need to partition your huge tables, this is true in all database platforms. The MySQL distribution we're using is 4.1.12. The database sits on a HP Proliant DL585 server with 2 dual-core Opterons and 12 GB of RAM, running Linux Fedora Core 3. If this is InnoDB, then you might make sure that your InnoDB buffer pool is set very large, say 10GB, and this will improve performance there. If this is MyISAM, then you want to set myisam_sort_buffer_size and key_buffer_size to 4GB or just under. There is a 4GB limit currently on those settings. myisam_sort_buffer_size is used for rebuilding an index and you need to make sure your index will fit in that amount of memory when you partition your tables. If you continue to need help, then you might want to enlist our on-site consulting for your project needs: http://www.mysql.com/consulting/packaged/performance.html Regards, Josh -- Josh Chamas Director, Professional Services MySQL Inc., www.mysql.com Get More with MySQL! www.mysql.com/consulting -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: ORACLE and Mysql: Create tables, constraints, swquences, storage. ..?
I recomend to you FabForce DBDesigner 4.x with this tool you can do Reverse Engineering to a database in oracle, and then once created a model, export sql to mysql and that's it! I have made this to some databases in oracle, is really easy do it! Saludos! -Original Message- From: Nguyen, Phong [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 26, 2005 7:55 AM To: mysql@lists.mysql.com Subject: ORACLE and Mysql: Create tables, constraints, swquences, storage. ..? Good morning, I have scripts to create constraints, sequences, storage..., tables from ORACLE and I don't know if I can create them in MySQL? Appreciated your help, Thank you, Phong -- 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: malloc troubles on 64-bit machine
Hi Jigal! Jigal van Hemert wrote: [[...]] After a while he needed to restart MySQL (made some changes somewhere) and it refused to do so: 050726 14:13:12 mysqld started 050726 14:13:12 InnoDB: Error: cannot allocate 7340048384 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 78086952 bytes. Operating system errno: 12 [[...]] He then rebooted the entire server and: [[...]] ...it runs happily again. Any ideas anyone on the cause and (more importantly) how to fix this problem? On my machine (Linux: SuSE 9.1), I have this line in /usr/include/asm-generic/errno-base.h : #define ENOMEM 12 /* Out of memory */ So it looks like some address space (paging area?) was not yet free when the restart was attempted. Maybe the MySQL server had not yet fully terminated? HTH, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
concat function problems
i've got a strange problem with concat() function
i have the following data structure:
CREATE TABLE table1 (
field1 int(11) NOT NULL auto_increment,
PRIMARY KEY (field1)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 AUTO_INCREMENT=3;
CREATE TABLE table2 (
field2 varchar(255) NOT NULL default ''
) ENGINE=InnoDB DEFAULT CHARSET=cp1251;
INSERT INTO table1 VALUES (1);
INSERT INTO table1 VALUES (2);
INSERT INTO table2 VALUES ('test');
When i try to execute the following query
(the query is meaningless -- it is generated just for debug purpose.
the original query where the problem occured is too long and
complicated)
SELECT
COUNT(DISTINCT field1) as value1,
CONCAT(field2, '-') as value2
FROM
table1,
table2
GROUP
BY value2
i get this error:
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:6
Current database: test
ERROR 2013 (HY000): Lost connection to MySQL server during query
but if i change my query to this one everything is fine
SELECT
COUNT(DISTINCT field1) as value1,
CONCAT(field2, '') as value2
FROM
table1,
table2
GROUP
BY value2
(here CONCAT(field2, '-') is replaced with CONCAT(field2, '') )
does anyone know what the matter is?
ps
my mysql version is
mysql Ver 14.7 Distrib 4.1.12, for unknown-freebsd4.6 (i386) using EditLine
wrapper
and my system is FreeBSD 4.6
--
Best regards,
Sergey Averyanov
mailto:[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: malloc troubles on 64-bit machine
Hi Joerg, From: Joerg Bruehe Jigal van Hemert wrote: 050726 14:13:12 mysqld started 050726 14:13:12 InnoDB: Error: cannot allocate 7340048384 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 78086952 bytes. Operating system errno: 12 On my machine (Linux: SuSE 9.1), I have this line in /usr/include/asm-generic/errno-base.h : #define ENOMEM 12 /* Out of memory */ And perror 12 also produces a similar error description. So it looks like some address space (paging area?) was not yet free when the restart was attempted. Maybe the MySQL server had not yet fully terminated? MySQL server was terminated; at least it didn't show up in the output of the ps-command. It doesn't happen all the time; the server was running for a few days now. We have never encountered such a situation on a 32-bit machine yet. You could simply terminate MySQL and start it immediately. Can memory get fragmented in some way after it is allocated? Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Correct way to use innodb_file_per_table?
On Jul 26, 2005, at 3:56 AM, Marvin Wright wrote: Regarding the file size issue, we are on a 32-bit system running redhat AS3, we already have idb files in excess of 21Gb, I'm not sure what the limit is though if any ? No, typically a 32 bit file system would have limits like 2G or 4G... at 21G already I doubt you'll have a problem... That said I know little more about Linux than how to spell it, so I'm not the best person to give specific information on that. Just one thought about the shared space, do you think it would be possible to back up all the current shared data files along with the iblog files, change the my.cnf file to use a single ibdata file of 2 gig, then try to start it up. If it did fail how about reverting the my.cnf and restoring the original ibdata and iblog files, would it still work after restoring the original files ? With this I could test Heikki idea without the pssibility of losing data. Yes, if you start MySQL without the files (simply moving them to a holding directory) and it doesn't come up or can't find the data after it boots you can always put the files (and your original my.cnf) back and go on from there. At least that's how it's supposed to work :-) Best Regards, Bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with C API
Hi all, i try to use the C API but i get a segmentation fault... my code looks like : MYSQL mysql; if( ! mysql_init(mysql) ) exit(0); if( ! mysql_real_connect(mysql,localhost,user,xxx,,3306,NULL,0) ) exit(0); if( mysql_select_db(mysql, nomBdd) != 0 ) exit(0); mysql_query(mysql,SELECT * FROM TABLE_1); mysql_close(mysql); Without the mysql_query line, it works perfectly! But when i try a simple query, i've this error message : Segmentation fault (core dumped) I try with gdb : $ gdb Config.exe GNU gdb 6.3.50_2004-12-28-cvs (cygwin-special) Copyright 2004 Free Software Foundation, Inc. GDB is free software, covered by the GNU General Public License, and you are welcome to change it and/or distribute copies of it under certain conditions. Type show copying to see the conditions. There is absolutely no warranty for GDB. Type show warranty for details. This GDB was configured as i686-pc-cygwin...(no debugging symbols found) (gdb) run Starting program: /cygdrive/y/connexion_bdd/Config.exe Program received signal SIGSEGV, Segmentation fault. 0x0001 in ?? () (gdb) where #0 0x0001 in ?? () #1 0x0030 in ?? () #2 0x0022e934 in ?? () #3 0x719f7e50 in WSHTCPIP!WSHJoinLeaf () from /cygdrive/c/WINDOWS/System32/mswsock.dll #4 0x003f43a8 in ?? () #5 0x003f63a8 in ?? () #6 0x003f43a8 in ?? () #7 0x003f43a8 in ?? () #8 0x06a0 in ?? () #9 0x2000 in ?? () #10 0x4000 in ?? () #11 0x0008 in ?? () #12 0x0008 in ?? () #13 0x01e13380 in ?? () #14 0x01e13380 in ?? () #15 0x0001 in ?? () #16 0x in ?? () from (gdb) I work with Cygwin : $ gcc -v Reading specs from /usr/lib/gcc/i686-pc-cygwin/3.4.4/specs Configured with: /gcc/gcc-3.4.4/gcc-3.4.4-1/configure --verbose --prefix=/usr --exec-prefix=/usr --sysconfdir=/etc --libdir=/ usr/lib --libexecdir=/usr/lib --mandir=/usr/share/man --infodir=/usr/share/info --enable-languages=c,ada,c++,d,f77,java,objc --enable-nls --without-included-gettext --enable-version-specific-runtime-libs --without-x --enable-libgcj --disable-java-awt --with-system-zlib --enable-interpreter --disable-libgcj-debug --enable-threads=posix --enable-java-gc=boehm --disable-win32 -registry --enable-sjlj-exceptions --enable-hash-synchronization --enable-libstdcxx-debug : (reconfigured) Thread model: posix gcc version 3.4.4 (cygming special) (gdc 0.12, using dmd 0.125) MySQL : Ver 14.7 Distrib 4.1.12a, for Win32 (ia32) Do you have any idea? Thanks in advance! pico. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: concat function problems
[EMAIL PROTECTED] wrote:
i've got a strange problem with concat() function
i have the following data structure:
CREATE TABLE table1 (
field1 int(11) NOT NULL auto_increment,
PRIMARY KEY (field1)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 AUTO_INCREMENT=3;
CREATE TABLE table2 (
field2 varchar(255) NOT NULL default ''
) ENGINE=InnoDB DEFAULT CHARSET=cp1251;
INSERT INTO table1 VALUES (1);
INSERT INTO table1 VALUES (2);
INSERT INTO table2 VALUES ('test');
When i try to execute the following query
(the query is meaningless -- it is generated just for debug purpose.
the original query where the problem occured is too long and
complicated)
This is an important point. Do you get the reported error for the simplified
query below? If not, it is useless. To find the problem, we must have a
query that produces the problem.
SELECT
COUNT(DISTINCT field1) as value1,
CONCAT(field2, '-') as value2
FROM
table1,
table2
GROUP
BY value2
With 4.1.11 on Mac OS X 10.3.9, I get
+++
| value1 | value2 |
+++
| 2 | test- |
+++
1 row in set (0.09 sec)
i get this error:
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:6
Current database: test
ERROR 2013 (HY000): Lost connection to MySQL server during query
Does the mysql server actually crash, or are you just losing the connection
from your client? Have you checked the error log? Have you checked the
manual http://dev.mysql.com/doc/mysql/en/gone-away.html?
but if i change my query to this one everything is fine
SELECT
COUNT(DISTINCT field1) as value1,
CONCAT(field2, '') as value2
FROM
table1,
table2
GROUP
BY value2
(here CONCAT(field2, '-') is replaced with CONCAT(field2, '') )
does anyone know what the matter is?
Does the simple query
SELECT CONCAT('test', '-');
work or produce the same error?
ps
my mysql version is
mysql Ver 14.7 Distrib 4.1.12, for unknown-freebsd4.6 (i386) using EditLine wrapper
and my system is FreeBSD 4.6
I've not used EditLine wrapper with mysql. Is there any chance it is doing
something with '-'?
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with C API
Sorry to disturb you, i have resolved my problem by using other mysql headers! Selon [EMAIL PROTECTED]: Hi all, i try to use the C API but i get a segmentation fault... my code looks like : MYSQL mysql; if( ! mysql_init(mysql) ) exit(0); if( ! mysql_real_connect(mysql,localhost,user,xxx,,3306,NULL,0) ) exit(0); if( mysql_select_db(mysql, nomBdd) != 0 ) exit(0); mysql_query(mysql,SELECT * FROM TABLE_1); mysql_close(mysql); Without the mysql_query line, it works perfectly! But when i try a simple query, i've this error message : Segmentation fault (core dumped) I try with gdb : $ gdb Config.exe GNU gdb 6.3.50_2004-12-28-cvs (cygwin-special) Copyright 2004 Free Software Foundation, Inc. GDB is free software, covered by the GNU General Public License, and you are welcome to change it and/or distribute copies of it under certain conditions. Type show copying to see the conditions. There is absolutely no warranty for GDB. Type show warranty for details. This GDB was configured as i686-pc-cygwin...(no debugging symbols found) (gdb) run Starting program: /cygdrive/y/connexion_bdd/Config.exe Program received signal SIGSEGV, Segmentation fault. 0x0001 in ?? () (gdb) where #0 0x0001 in ?? () #1 0x0030 in ?? () #2 0x0022e934 in ?? () #3 0x719f7e50 in WSHTCPIP!WSHJoinLeaf () from /cygdrive/c/WINDOWS/System32/mswsock.dll #4 0x003f43a8 in ?? () #5 0x003f63a8 in ?? () #6 0x003f43a8 in ?? () #7 0x003f43a8 in ?? () #8 0x06a0 in ?? () #9 0x2000 in ?? () #10 0x4000 in ?? () #11 0x0008 in ?? () #12 0x0008 in ?? () #13 0x01e13380 in ?? () #14 0x01e13380 in ?? () #15 0x0001 in ?? () #16 0x in ?? () from (gdb) I work with Cygwin : $ gcc -v Reading specs from /usr/lib/gcc/i686-pc-cygwin/3.4.4/specs Configured with: /gcc/gcc-3.4.4/gcc-3.4.4-1/configure --verbose --prefix=/usr --exec-prefix=/usr --sysconfdir=/etc --libdir=/ usr/lib --libexecdir=/usr/lib --mandir=/usr/share/man --infodir=/usr/share/info --enable-languages=c,ada,c++,d,f77,java,objc --enable-nls --without-included-gettext --enable-version-specific-runtime-libs --without-x --enable-libgcj --disable-java-awt --with-system-zlib --enable-interpreter --disable-libgcj-debug --enable-threads=posix --enable-java-gc=boehm --disable-win32 -registry --enable-sjlj-exceptions --enable-hash-synchronization --enable-libstdcxx-debug : (reconfigured) Thread model: posix gcc version 3.4.4 (cygming special) (gdc 0.12, using dmd 0.125) MySQL : Ver 14.7 Distrib 4.1.12a, for Win32 (ia32) Do you have any idea? Thanks in advance! pico. -- 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: why so many table scans?
Michael Stassen wrote: Sort of. The order of requirements in the WHERE clause is irrelevant. ... No composite index will be fully used here. MySQL uses composite indexes from left to right, *stopping on the first key part used in a range* rather than to match a constant. WHERE latitude 39 is a range, so the composite index on (latitude, longitude, link_id) will be no better than a single column index on latitude. Michael Thanks for the corrections, Michael :) My apologies for posting wrong info to the list For my own understanding of this, I created a simple table... CREATE TABLE `test` ( `a` int(11) NOT NULL default '0', `b` int(11) NOT NULL default '0', `c` int(11) default NULL, KEY `a` (`a`,`b`,`c`) ); and inserted about a dozen random integers, then checked the output of EXPLAIN for two queries, one using a range on all three columns, the other using a constant for the first column... mysql explain select * from test where a 30 and b 30 and c 30; ++-+---+---+---+--+-+--+--+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra| ++-+---+---+---+--+-+--+--+--+ | 1 | SIMPLE | test | range | a | a| 4 | NULL | 13 | Using where; Using index | ++-+---+---+---+--+-+--+--+--+ 1 row in set (0.00 sec) mysql explain select * from test where a = 30 and b 30 and c 30; ++-+---+---+---+--+-+--+--+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra| ++-+---+---+---+--+-+--+--+--+ | 1 | SIMPLE | test | range | a | a| 8 | NULL |1 | Using where; Using index | ++-+---+---+---+--+-+--+--+--+ 1 row in set (0.00 sec) The only difference is that in the second query, key_len is 8 bytes, not 4. Makes sense, since as you say, each query only uses the left-most part of the index, stopping if a range is encountered. Thanks again, Devananda vdv -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql-shared-compat for 4.13
I've got a brand new dell server with RedHat ES3 on it. As usuall it came pre-installed with mysql 3.23. Trying to upgrade to mysql 4.1.13 (MySQL-server-standard-4.1.13-0.rhel3.i386.rpm) and I'm getting the usual errors of required dependencies because I need the shared-compat package. Problem is, I can't find the rpm or source for the MySQL-shared-compat for 4.1.13 (or any other version for that matter). Anyone know where I can get my hands on the correct RPM? Are they hidden somewhere on the MySQL site? Best regards, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB migration between servers
Hello list,
I'm having some mysterious problems when I try to migrate several
databases from one server to another one. This
databases contain some InnoDB tables - db size is around ~3GB. On both
servers I'm using MySQL version 4.1.12,
the same kernel version, same libc (2.3.2), but different architectures
(source x86, target amd64). Filesystem on both
side are xfs formated (but also tried with reiser ext3).
I tried two ways to transfer the databases between the servers:
*) No running mysqld on both servers (successfully shutdown, no crash).
Copying the whole bunch of datafiles
(/var/lib/mysql) with rsync/scp to the other machine (files are ok,
md5check). Startup - Everything ok
*) Active mysqld, both sides no clients connected. mysqldump (--opt
--single-transaction) from the source server,
copy the dump to the target server (dump is fine, md5check). Import on
the target server - Everything ok
Now I let my clients connecting to the new server.
As soon as there is any data-changing query (INSERT, UPDATE) on one of
the InnoDB tables on the new server,
the query hangs - the state is update. All other queries which also
wants to update some data gets state locked
(like it should be).
Process-List:
mysql show processlist\G
*** 2. row ***
Id: 16
User: sfz.info
Host: lskeletor.:51828
db: db_sfz
Command: Query
Time: 2524
State: update
Info: INSERT INTO 4images_sessionvars
(session_id, sessionvars_name, sessionvars_value)
*** 3. row ***
Id: 34
User: sfz.info
Host: lskeletor:52044
db: db_sfz
Command: Query
Time: 2455
State: Locked
Info: DELETE FROM 4images_sessionvars
WHERE session_id NOT IN ('37b5643b556224e8c6e43e11aa
3 rows in set (0.00 sec)
But SHOW INNODB STATUS doesn't showup these transactions:
mysql show innodb status \G
*** 1. row ***
Status:
=
050726 19:57:16 INNODB MONITOR OUTPUT
=
Per second averages calculated from the last 34 seconds
--
SEMAPHORES
--
OS WAIT ARRAY INFO: reservation count 1163, signal count 1160
Mutex spin waits 1129, rounds 7484, OS waits 220
RW-shared spins 1848, OS waits 922; RW-excl spins 20, OS waits 12
TRANSACTIONS
Trx id counter 0 1797
Purge done for trx's n:o 0 0 undo n:o 0 0
History list length 0
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, process no 15450, OS thread id 1134426480
MySQL thread id 13, query id 1254 localhost root
show innodb status
FILE I/O
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
Pending normal aio reads: 0, aio writes: 0,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
537 OS file reads, 31672 OS file writes, 5620 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-
INSERT BUFFER AND ADAPTIVE HASH INDEX
-
Ibuf for space 0: size 1, free list len 0, seg size 2, is empty
Ibuf for space 0: size 1, free list len 0, seg size 2,
355 inserts, 355 merged recs, 41 merges
Hash table size 553253, used cells 4527, node heap has 8 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 0 797420905
Log flushed up to 0 797420905
Last checkpoint at 0 797420905
0 pending log writes, 0 pending chkp writes
2464 log i/o's done, 0.00 log i/o's/second
--
BUFFER POOL AND MEMORY
--
Total memory allocated 329850640; in additional pool allocated 2808320
Buffer pool size 16384
Free buffers 0
Database pages 16376
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 701, created 54627, written 91113
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--
ROW OPERATIONS
--
0 queries inside InnoDB, 0 queries in queue
Main thread process no. 15450, id 1132460400, state: waiting for server
activity
Number of rows inserted 2439094, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
END OF INNODB MONITOR OUTPUT
These are simply queries like insert into table (field1, field2)
values (value1, value2). No tricky conditions or indexes
and so on.
When I strace the mysqld process it doesn't do anything, it's waiting
for more connections with select(); But the update
query never finish. In my.cnf I specified a 60sec timeout
Where on count(*)
I have esentially this query (the list of integers may differ): SELECT `Plans`.`id`, `Plans`.`name`, count(*) as 'count' FROM `Plans` JOIN `TechsPerPlan` ON `Plans`.`id` = `TechsPerPlan`.`plan` WHERE `TechsPerPlan`.`id` IN (17, 48, 54, 64, 75, 13, 30, 37, 45, 55, 65, 76, 11, 33, 46, 58, 68, 80) GROUP BY `Plans`.`id` Of that result I want those with count bigger than N (being N a number, like 3), I tried this: SELECT `Plans`.`id`, `Plans`.`name`, count(*) as 'count' FROM `Plans` JOIN `TechsPerPlan` ON `Plans`.`id` = `TechsPerPlan`.`plan` WHERE `TechsPerPlan`.`id` IN (17, 48, 54, 64, 75, 13, 30, 37, 45, 55, 65, 76, 11, 33, 46, 58, 68, 80) AND count = 3 GROUP BY `Plans`.`id` but it selected only those with less that 3, what I am doing wrong ? Thanks -- Pupeno [EMAIL PROTECTED] (http://pupeno.com) Reading ? Science Fiction ? http://sfreaders.com.ar pgpScG4lH4kdP.pgp Description: PGP signature
Can someone send me a link or two....
I am trying to find documentation on how to use the new Distributed Transactions (XA) feature without going through a JDBC driver. According to a Webinar I just reviewed, the XA protocols are available in the SQL layer but I am having the hardest time finding documentation on how to initiate, complete, or abort a two-phase commit. Can anyone help? I would rather NOT crawl the source code and reverse engineer the statements that way, if that's OK with you. Are there any docs online yet on how to do this outside of a JDBC driver? Thanks! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Where on count(*)
Pupeno [EMAIL PROTECTED] wrote on 07/26/2005 03:53:10 PM: I have esentially this query (the list of integers may differ): SELECT `Plans`.`id`, `Plans`.`name`, count(*) as 'count' FROM `Plans` JOIN `TechsPerPlan` ON `Plans`.`id` = `TechsPerPlan`.`plan` WHERE `TechsPerPlan`.`id` IN (17, 48, 54, 64, 75, 13, 30, 37, 45, 55, 65, 76, 11, 33, 46, 58, 68, 80) GROUP BY `Plans`.`id` Of that result I want those with count bigger than N (being N a number, like 3), I tried this: SELECT `Plans`.`id`, `Plans`.`name`, count(*) as 'count' FROM `Plans` JOIN `TechsPerPlan` ON `Plans`.`id` = `TechsPerPlan`.`plan` WHERE `TechsPerPlan`.`id` IN (17, 48, 54, 64, 75, 13, 30, 37, 45, 55, 65, 76, 11, 33, 46, 58, 68, 80) AND count = 3 GROUP BY `Plans`.`id` but it selected only those with less that 3, what I am doing wrong ? Thanks -- Pupeno [EMAIL PROTECTED] (http://pupeno.com) WHERE clauses are evaluated *before* the GROUP BY is processed. The GROUP BY processing is where the value of `count` is computed. What you want to do is to place your condition in a HAVING clause like this: SELECT `Plans`.`id`, `Plans`.`name`, count(*) as 'count' FROM `Plans` JOIN `TechsPerPlan` ON `Plans`.`id` = `TechsPerPlan`.`plan` WHERE `TechsPerPlan`.`id` IN (17, 48, 54, 64, 75, 13, 30, 37, 45, 55, 65, 76, 11, 33, 46, 58, 68, 80)GROUP BY `Plans`.`id` HAVING count = 3 Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Mysql-shared-compat for 4.13
you can find what you need here: http://public.pdinc.us/rpms/mysql please email the list with questions and comments -Jason Pyeron On Tue, 26 Jul 2005, Jeff wrote: I've got a brand new dell server with RedHat ES3 on it. As usuall it came pre-installed with mysql 3.23. Trying to upgrade to mysql 4.1.13 (MySQL-server-standard-4.1.13-0.rhel3.i386.rpm) and I'm getting the usual errors of required dependencies because I need the shared-compat package. Problem is, I can't find the rpm or source for the MySQL-shared-compat for 4.1.13 (or any other version for that matter). Anyone know where I can get my hands on the correct RPM? Are they hidden somewhere on the MySQL site? Best regards, Jeff -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - - - Jason Pyeron PD Inc. http://www.pdinc.us - - Partner Sr. Manager 7 West 24th Street #100 - - +1 (443) 921-0381 Baltimore, Maryland 21218 - - - -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, purge the message from your system and notify the sender immediately. Any other use of the email by you is prohibited. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Databases in a different location than the default
Tim Holmes wrote: Hello. You could find the clues in the error log. See: http://dev.mysql.com/doc/mysql/en/error-log.html Good Afternoon: I have rebuilt by web / database server from bare metal this morning. The computer is running Fedora Core 3, and is fully patched and up to date. The MySQL version is 4.1.12-1, and I have installed the following components - Server - Client - Compat-libs All were installed from the RPMs provided by MySQL.com To start of, I am fairly new to administering database servers, and to using them, so if my question seems a little elementary, please understand and answer accordingly. My question is this. My databases are located on a different physical machine from the one running the database server - (for backup etc reasons) The databases reside in /home/mysql - that's an NFS share, which I know for a fact is valid, as I have other data being used from the share the default my.cnf is listed here: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid When I start the mysql server with this configuration, it appears to start in good order. It is my understanding that in order to have the server utilize my databases that are on the other machine, I need to change the datadir directive in the my.cnf file to read datadir=/home/mysql when I do this however, mysql fails to start - it waits for a period of time and gives me a failed error. Can someone please explain to me what is going wrong, and what I can do to fix it. - I am not interested in having someone fix this for me, I just need some guidance so that I can learn how to fix it myself, so when it happens again, I will know what to do. [Tim Holmes] Gleb, et. al. As you suggested, I have checked out the log files and this is what I have found: 050713 11:00:09 mysqld started 050713 11:00:09 [Warning] Asked for 196608 thread stack, but got 126976 050713 11:00:09 [ERROR] Can't start server: Bind on TCP/IP port: Address already in use 050713 11:00:09 [ERROR] Do you already have another mysqld server running on port: 3306 ? 050713 11:00:09 [ERROR] Aborting 050713 11:00:09 [Note] /usr/sbin/mysqld: Shutdown complete 050713 11:00:09 mysqld ended This suggests to me a communications problem on either the database server, or the file server where the databases reside. I guess the next question is how do I check to see whats going on here. The error says that you tried to start another server, listening in the same (default) port 3306, which is not possible. Try to start in a different port (like 33306, or 3307). How do you start mysql? A suggest create a different start script for the other databases (/home/mysql), that uses a different my.conf, where you have the other parameters on the database I tried telnet 192.168.0.5:3306 and got the following [EMAIL PROTECTED] log]# telnet 192.168.0.5:3306 192.168.0.5:3306/telnet: Name or service not known This may suggest that telnet is not installed, or it may indicate another problem You used the wrong syntax, you should have tried # telnet 192.168.0.5 3306 (With a space instead of a semicolon) It should prompt you with some imperceptible data, where you can see the version of MySQL Server in the middle. Close it with Ctrl+D Any suggestions are welcome TIM -- Nuno Pereira email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql-shared-compat for 4.13
Thanks! Jeff -Original Message- From: Jason Pyeron [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 26, 2005 3:03 PM To: mysql@lists.mysql.com Subject: Re: Mysql-shared-compat for 4.13 you can find what you need here: http://public.pdinc.us/rpms/mysql please email the list with questions and comments -Jason Pyeron On Tue, 26 Jul 2005, Jeff wrote: I've got a brand new dell server with RedHat ES3 on it. As usuall it came pre-installed with mysql 3.23. Trying to upgrade to mysql 4.1.13 (MySQL-server-standard-4.1.13-0.rhel3.i386.rpm) and I'm getting the usual errors of required dependencies because I need the shared-compat package. Problem is, I can't find the rpm or source for the MySQL-shared-compat for 4.1.13 (or any other version for that matter). Anyone know where I can get my hands on the correct RPM? Are they hidden somewhere on the MySQL site? Best regards, Jeff -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- - - - Jason Pyeron PD Inc. http://www.pdinc.us - - Partner Sr. Manager 7 West 24th Street #100 - - +1 (443) 921-0381 Baltimore, Maryland 21218 - - - -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, purge the message from your system and notify the sender immediately. Any other use of the email by you is prohibited. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: concat function problems
[EMAIL PROTECTED] wrote: (...) When i try to execute the following query (...) i get this error: ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id:6 Current database: test ERROR 2013 (HY000): Lost connection to MySQL server during query but if i change my query to this one everything is fine SELECT COUNT(DISTINCT field1) as value1, CONCAT(field2, '') as value2 FROM table1, table2 GROUP BY value2 (here CONCAT(field2, '-') is replaced with CONCAT(field2, '') ) does anyone know what the matter is? Did you try to make the first query for a second time, when you execute the second query? It seems that the server abords or closes the connection meanwhile. Did you waited too long to write/send the query? Check this for more help http://dev.mysql.com/doc/mysql/en/gone-away.html Try this query and output the result SHOW VARIABLES LIKE '%time%'; if you don't find a solution. -- Nuno Pereira email: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where on count(*)
Hi, Look at having : having count(*) 3 for example Mathias Selon Pupeno [EMAIL PROTECTED]: I have esentially this query (the list of integers may differ): SELECT `Plans`.`id`, `Plans`.`name`, count(*) as 'count' FROM `Plans` JOIN `TechsPerPlan` ON `Plans`.`id` = `TechsPerPlan`.`plan` WHERE `TechsPerPlan`.`id` IN (17, 48, 54, 64, 75, 13, 30, 37, 45, 55, 65, 76, 11, 33, 46, 58, 68, 80) GROUP BY `Plans`.`id` Of that result I want those with count bigger than N (being N a number, like 3), I tried this: SELECT `Plans`.`id`, `Plans`.`name`, count(*) as 'count' FROM `Plans` JOIN `TechsPerPlan` ON `Plans`.`id` = `TechsPerPlan`.`plan` WHERE `TechsPerPlan`.`id` IN (17, 48, 54, 64, 75, 13, 30, 37, 45, 55, 65, 76, 11, 33, 46, 58, 68, 80) AND count = 3 GROUP BY `Plans`.`id` but it selected only those with less that 3, what I am doing wrong ? Thanks -- Pupeno [EMAIL PROTECTED] (http://pupeno.com) Reading ? Science Fiction ? http://sfreaders.com.ar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
optimizing not empty selections
Hello, How to optimize != statement? SELECT * FROM table1 WHERE char_255_column != ''; How to fast select all rows, where CHAR(255) NOT NULL column not empty? Sincerely, Michael, http://xoib.com/ http://3d2f.com/ http://qaix.com/ http://ryxi.com/ http://gyxe.com/ http://gyxu.com/ http://xywe.com/ http://xyqe.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
install fails
When I try to install MySQL-server-4.1.13-0.glibc23.ia64.rpm MySQL-Max-4.1.13-0.glibc23.ia64.rpm MySQL-devel-4.1.13-0.glibc23.ia64.rpm MySQL-debuginfo-4.1.13-0.glibc23.ia64.rpm MySQL-client-4.1.13-0.glibc23.ia64.rpm MySQL-bench-4.1.13-0.glibc23.ia64.rpm I get lots of messages like this: Error: Missing Dependency: libc.so.6.1()(64bit) is needed by package I do have [EMAIL PROTECTED] ~]# locate libc.so /lib/i686/libc.so.6 /lib/tls/libc.so.6 /lib/libc.so.6 /lib64/tls/libc.so.6 /lib64/libc.so.6 /usr/lib/libc.so /usr/lib64/nptl/libc.so /usr/lib64/libc.so And when I do an rpmfind on libc.so.6.1 I get glibc 2.2, 2.1 and 2.0 packages...? I though this mysql used glibc 2.3? What am I missing here... Anne -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: optimizing not empty selections
Michael Monashev [EMAIL PROTECTED] wrote on 07/26/2005 04:41:28 PM: Hello, How to optimize != statement? SELECT * FROM table1 WHERE char_255_column != ''; How to fast select all rows, where CHAR(255) NOT NULL column not empty? Sincerely, Michael, You could try WHERE char_255_column '' If the field has any text in it at all, the comparison will be true. Also, since this is a ranged comparison, you give the optimizer the opportunity to use an index if one is available. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Problem Escaping a Boolean Query
I'm using a boolean query to perform an exact match on musicians within a text
field. However, if the musician's name contains a quote, I get inaccurate
results. For example, this query works fine:
select * from feeds where MATCH(feed_title, feed_content) AGAINST('Tom Petty'
IN BOOLEAN MODE) order by feed_date DESC
The above query returns all feeds referencing the phrase Tom Petty. I run
into a problem when the musician has quotes in their name:
select * from feeds where MATCH(feed_title, feed_content) AGAINST('Sean \P.
Diddy\ Combs' IN BOOLEAN MODE) order by feed_date DESC
I'm trying to escape Sean P. Diddy Combs with backslashes, but it doesn't
work. This query returns every single row in my table, and it takes about 11
seconds to run. The other queries take a fraction of a second.
Any help would be greatly appreciated. Thanks!
Shaun
http://www.blogfreaks.com
all user command
hello what command should i type to see all the user connected to the database. thank you very much. rgds, Joeffrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can someone send me a link or two....
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 [EMAIL PROTECTED] wrote: I am trying to find documentation on how to use the new Distributed Transactions (XA) feature without going through a JDBC driver. According to a Webinar I just reviewed, the XA protocols are available in the SQL layer but I am having the hardest time finding documentation on how to initiate, complete, or abort a two-phase commit. Can anyone help? I would rather NOT crawl the source code and reverse engineer the statements that way, if that's OK with you. Are there any docs online yet on how to do this outside of a JDBC driver? Thanks! Shawn Green Database Administrator Unimin Corporation - Spruce Pine Shawn, It's pretty straightforward (if you know XA). I'll give you the syntax that the JDBC driver uses, if you don't know XA let me know and I'll explain further (it's late here, and I haven't been away from the computer all day): The following SQL statements map directly to their counterparts in XA, where XID can be represented in the following way: GTRID, BQAUL, FORMATID (I use 0x hex notation for each, where GTRID is the global transaction ID, BQUAL is the branch qualifier (both are up to 64 bytes in length, the only requirement is that the combination of the two be globally unique), and FORMATID is a 32-bit integer). XA PREPARE XID XA ROLLBACK XID XA END [SUSPEND] XID (suspended tx can only be resumed on same physical connection) XA START XID [JOIN|RESUME] XA COMMIT XID [ONE PHASE] You (well, really, your transaction mananger) issue these commands as you would any other SQL query. -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Connectivity www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFC5uwytvXNTca6JD8RAsAZAJ4qsxPQhOZwnOovHR0kwSdDI+5MGACgo/yV //dYu3u4yVelbDsNuP9OGhA= =QtAd -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: all user command
mysqladmin processlist On Jul 26, 2005, at 5:21 PM, Joeffrey Betita wrote: hello what command should i type to see all the user connected to the database. thank you very much. Rich Allen Dare Do -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: concat function problems
Hello mysql,
i've got a strange problem with concat() function
i have the following data structure:
CREATE TABLE table1 (
field1 int(11) NOT NULL auto_increment,
PRIMARY KEY (field1)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 AUTO_INCREMENT=3;
CREATE TABLE table2 (
field2 varchar(255) NOT NULL default ''
) ENGINE=InnoDB DEFAULT CHARSET=cp1251;
INSERT INTO table1 VALUES (1);
INSERT INTO table1 VALUES (2);
INSERT INTO table2 VALUES ('test');
When i try to execute the following query
(the query is meaningless -- it is generated just for debug purpose.
the original query where the problem occured is too long and
complicated)
SELECT
COUNT(DISTINCT field1) as value1,
CONCAT(field2, '-') as value2
FROM
table1,
table2
GROUP
BY value2
i get this error:
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:6
Current database: test
ERROR 2013 (HY000): Lost connection to MySQL server during query
This is an important point. Do you get the reported error for the simplified
query below? If not, it is useless. To find the problem, we must have a
query that produces the problem.
Yes, i have the problem exactly in THIS query
SELECT
COUNT(DISTINCT field1) as value1,
CONCAT(field2, '-') as value2
FROM
table1,
table2
GROUP
BY value2
With 4.1.11 on Mac OS X 10.3.9, I get
+++
| value1 | value2 |
+++
| 2 | test- |
+++
1 row in set (0.09 sec)
i get this error:
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:6
Current database: test
ERROR 2013 (HY000): Lost connection to MySQL server during query
Does the mysql server actually crash, or are you just losing the connection
from your client? Have you checked the error log? Have you checked the
manual http://dev.mysql.com/doc/mysql/en/gone-away.html?
Yes, it really crashes. mysqld restarts after that
but if i change my query to this one everything is fine
SELECT
COUNT(DISTINCT field1) as value1,
CONCAT(field2, '') as value2
FROM
table1,
table2
GROUP
BY value2
(here CONCAT(field2, '-') is replaced with CONCAT(field2, '') )
does anyone know what the matter is?
Does the simple query
SELECT CONCAT('test', '-');
work or produce the same error?
This query works.
This one does NOT:
SELECT
COUNT(DISTINCT field1) as value1,
CONCAT(field2, '-') as value2
FROM
table1,
table2
GROUP
BY value2
All of the following ones WORK fine too:
SELECT
max(field1) as value1,
CONCAT(field2, '-') as value2
FROM
table1,
table2
GROUP
BY value2
(here count is replaced with MAX() for experimental purposes)
SELECT
COUNT(field1) as value1,
CONCAT(field2, '-') as value2
FROM
table1,
table2
GROUP
BY value2
(here distinct is removed)
SELECT
COUNT(field1) as value1,
CONCAT(field2, '-') as value2
FROM
table2,
table1
GROUP
BY value2
(here tables order in FROM is changed)
ps
my mysql version is
mysql Ver 14.7 Distrib 4.1.12, for unknown-freebsd4.6 (i386) using
EditLine wrapper
and my system is FreeBSD 4.6
I've not used EditLine wrapper with mysql. Is there any chance it is doing
something with '-'?
no, i've tried other symbols too
--
Best regards,
averyanov mailto:[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
