Table Lock
Hi, Is that true that MySQL lock table on each query? Does it means will cause slow speed? Then how to avoid table lock and speed up MySQL? TIA. Willy The Public is merely a multiplied me. -- Mark Twain -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Table Lock
Hi! sangprabv wrote: Hi, Is that true that MySQL lock table on each query? Does it means will cause slow speed? Then how to avoid table lock and speed up MySQL? TIA. You need to read up about the different table handlers available. They have different properties, including the locking behavior. Check the manual for the version you are using. Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
undefined reference to `mysql_bind_param'
Hi, I have installed MySQL 6.0.6-alpha on Redhat Linux. I have copied the example program from http://teaching-.cs.uml.edu/MySQLdocs/MySQL_Manual/mysql_execute.html and compiled as follows. gcc -o insert_by_bind -I/usr/local/mysql/include -L/usr/local/mysql/lib -lmysqlclient -lz -lrt -lcrypt -lnsl -lm insert_by_bind.c But I am getting the errors like: insert_by_bind.c: undefined reference to `mysql_prepare' /tmp/ccF5dT9h.o(.text+0x242):insert_by_bind.c: undefined reference to `mysql_param_count' /tmp/ccF5dT9h.o(.text+0x317):insert_by_bind.c: undefined reference to `mysql_bind_param' /tmp/ccF5dT9h.o(.text+0x3c1):insert_by_bind.c: undefined reference to `mysql_execute' /tmp/ccF5dT9h.o(.text+0x4cf):insert_by_bind.c: undefined reference to `mysql_execute' collect2: ld returned 1 exit status I have set all the parameter LD_LIBRARY_PATH -- /usr/local/mysql/lib/ LD_LIBRARY -- /usr/local/mysql/lib/ Added the /usr/local/mysql/lib/ to ld.so.conf and executed the command ldconfig. Hope someone will help me in this regard. Thanks in advance. Regards, iqbal EMAIL DISCLAIMER : 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. Any unauthorised distribution or copying is strictly prohibited. If you receive this transmission in error, please notify the sender by reply email and then destroy the message. Opinions, conclusions and other information in this message that do not relate to official business of Mascon shall be understood to be neither given nor endorsed by Mascon. Any information contained in this email, when addressed to Mascon clients is subject to the terms and conditions in governing client contract. Whilst Mascon takes steps to prevent the transmission of viruses via e-mail, we can not guarantee that any email or attachment is free from computer viruses and you are strongly advised to undertake your own anti-virus precautions. Mascon grants no warranties regarding performance, use or quality of any e-mail or attachment and undertakes no liability for loss or damage, howsoever caused.
Re: stuck on a query
Now that was fun and educational. :-) I like the third approach, but theres an added problem to the whole thing. This query is just a part of a bigger search. The tables, job and color and other fields, which will also be searchable. I am trying to automate the whole process, i mean, the user sees a combo for the fields and a text input, and he can add as many fields as he wants, even duplicated ones, like the two colors problem. I am trying to build a super query that contains all the searches the user wants to do on the tables. Its a stupid job. I understand its an ingrate, and probably stupid, question, but for complex searches like the one i mentioned, is there a line of thought i should consider? I mean, it surely has been done and efficiently, so i am not exactly inventing the wheel here. Its what i said, imagine two/three tables, all joinable by common ids, and allow the user to say i want to search the name of the job, contains color1 and color2, is from client x and has the perimeter greater than 100. I am teaching php how to do queries all by myself. :-P Thank you all for the great suggestions. Pag On Wed, Dec 17, 2008 at 7:08 PM, Gavin Towey ga...@swishmark.com wrote: Three solutions, the first one is not recommended I just showed it for fun -- I think the last one is the most efficient: mysql show create table job \G *** 1. row *** Table: job Create Table: CREATE TABLE `job` ( `job_id` int(10) unsigned NOT NULL auto_increment, `name` varchar(12) default NULL, PRIMARY KEY (`job_id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql show create table color \G *** 1. row *** Table: color Create Table: CREATE TABLE `color` ( `color_id` int(10) unsigned NOT NULL auto_increment, `color` varchar(32) default NULL, `job_id` int(10) unsigned default NULL, PRIMARY KEY (`color_id`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1 1 row in set (0.02 sec) mysql select * from job; ++---+ | job_id | name | ++---+ | 1 | job 1 | | 2 | job 2 | | 3 | job3 | ++---+ 3 rows in set (0.00 sec) mysql select * from color; +--+-++ | color_id | color | job_id | +--+-++ |1 | yellow | 1 | |2 | cyan| 1 | |3 | black | 1 | |4 | cyan| 2 | |5 | magenta | 2 | |6 | black | 2 | |7 | yellow | 2 | |8 | cyan| 3 | +--+-++ 8 rows in set (0.00 sec) Method #1 mysql select job_id, GROUP_CONCAT(color) as colors FROM job j JOIN color c USING (job_id) GROUP BY job_id HAVING colors LIKE '%cyan%magenta'; ++---+ | job_id | colors| ++---+ | 2 | black,yellow,cyan,magenta | ++---+ 1 row in set (0.01 sec) Method #2 SELECT j.job_id, c1.color, c2.color FROM job j JOIN color c1 ON j.job_id=c1.job_id AND c1.color='cyan' JOIN color c2 ON j.job_id=c2.job_id AND c2.color='magenta'; ++---+-+ | job_id | color | color | ++---+-+ | 2 | cyan | magenta | ++---+-+ 1 row in set (0.00 sec) Method #3 mysql SELECT job_id, BIT_OR(CASE WHEN c.color='cyan' THEN 1 WHEN c.color='magenta' THEN 2 END) as colors FROM job j JOIN color c USING (job_id) GROUP BY job_id HAVING colors=3; +++ | job_id | colors | +++ | 2 | 3 | +++ 1 row in set (0.00 sec)
Trying again, SSL between client and server using SslOption/mysql_ssl_set ( C++/C )
I have a few questions about the parameters for SslOption/mysql_ssl_set ( C++/C ) and about SSL in general between the server and the client. I do understand what a certificate, a private key, and a public key is in general but I am having trouble understandng how they work in MySql. 1) Are the first three parameters just the file name portions of the full file specifications for the private key, public key, and certificate respectively ? The documentation suggets that they can be file paths, but then the fourth parameter would seem redundant. 2) Is the fourth parameter the file path for all of the first three file names ? If this is so and the path is the current directory, can this be 0 ? 3) What determines a valid cipher name, and where is that specified in MySql SSL in general ? Is there a way to say that all ciphers are valid with this parameter ? When SSL goes between the client and the server, using the same certificate authority certificate, is there one set of public-private keys where the server's private key is the client's public key and the server's public key is the client's private key ? Or are there actually two distinct sets of public-private keys, one each for the client and the server ? The former seems logically correct but maybe I am missing something about how this works in SSL. If someone can explain this to me it would be appreciated. The MySql manual does not explain it. In general the documentation for MySql SSL in section 5.5.7 of the MySql manual is pretty abysmal, which makes me think that either no one ever uses SSL in MySql, MySql is an inadequate database for SSL, or that no one cares to actually explain how SSL works under MySql. I am hoping that someone know how this works from the programming point of view and can explain it adequately to me or actually point me to documentation/examples which does explain it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
[ANN] PBXT 1.0.07 RC Released!
Hi All, I have just posted the first Release Candidate version of the PBXT transactional storage engine for MySQL. For more information on PBXT, please check out the home page: http://www.primebase.org My blog has a some more details on the release: http://pbxt.blogspot.com/2008/12/pbxt-goes-rc.html PBXT can be downloaded from here: http://www.primebase.org/download Or you can get the bleeding edge from: https://launchpad.net/pbxt To prove we have been busy, here are the release notes: http://www.primebase.org/download/ChangeLog And lets not forget the awesome new new performance analysis tool 'xtstat': http://pbxt.blogspot.com/2008/12/xtstat-tells-you-exactly-what-pbxt-is.html Bug reports gladly accepted here: https://bugs.launchpad.net/pbxt Questions and comments are also welcome :) My thanks to all who have tested PBXT so far. If you haven't tried out the engine yet, please do! Best regards, Paul -- Paul McCullagh PrimeBase Technologies www.primebase.org www.blobstreaming.org pbxt.blogspot.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
select
Hi all, I would like how to function select working internals. If each select in BD is mono process or various select is processes in same time without lock table? Tks Marcos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Hypothetically looking for Kevin Bacon
Hello mysql list, I am looking for a little guidance on a a potential query. I am sure most people have heard of the limited degrees of separation one has to become connected to another person. This is much like the myspace in your extended network idea, though I would like my implementation to not be broken. If person A exists in mysql, as well as persons B through Z, and some are related or known to each other, they have a relationship. Very much a family tree. While I can see how one wold start at person A, and look for the relationship to another person, thereby drilling down to other known links... I would like to take it one step further. I think celebrities are the best example, so I would be able to enter in myself, and ask the question: show me a tree of how I am closest to x celebrity. Since this is not a real project, and just a hypothetical question, assume that the links are known to be valid, and there is some proven way to key one person to another that is accurate. For example, in this case, we would use an honor system where Person A logs in, and selects all the other persons that they know. What would be the query to find out all the relationships of person A to x celebrity? How expensive is this query? I am looking to build something as a real project that will use this idea as a feature, albeit in a different way, but the end result will be the same. I get the feeling this is one, that if it is a used and popular service, the database design before it is made will be highly important. I smell a bit more recursion in this idea that I am used to, and also feel that doing most of the work in the DB layer, and not in the application layer, is going to give me the best performance. Does anyone have suggestions, or perhaps examples of this in working use? Maybe there is a known algo of function that gets close to this already. Thank you -- Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
installation nightmare
Or should I say 4-day-mare... I have been trying to set up mysql for the last 4 days with weird success. I first installed mysql 6.0.8alpha not knowing I needed 5.0 compatibility for my ISP Website host. I go everything working pretty well... mysql6, php5, phpMyAdmin, all on FreeBSD 7.0 with apache2.2.9_5; all but the setup of the pba_history table for phpmyadmin - just no way. Anyway, I unistalled mysql6 and am now trying to install mySQL 5.1.30 from the FreeBSD ports collection. I have tried about everything I can think of besides the arcane documentation that is not at all accurate - for instance, there is no mysq-test.pl or something like that that is referenced in the database, but there is mysqltest in the .../bin directory. However, running that results in errors. When I do manage to set the root user password, it is not accepted... I just cannot understand what kind of a mess this is. The only thing I can provide is the error file: (biggie - is the name of the server) 081218 17:40:17 mysqld_safe Starting mysqld daemon with databases from /var/db/mysql /usr/local/libexec/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13) 081218 17:40:17 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 081218 17:40:18 InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: File operation call: 'create'. InnoDB: Cannot continue operation. 081218 17:40:18 mysqld_safe mysqld from pid file /var/db/mysql/biggie.pid ended 081218 17:46:04 mysqld_safe Starting mysqld daemon with databases from /var/db/mysql /usr/local/libexec/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13) 081218 17:46:04 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 081218 17:46:04 InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: File operation call: 'create'. InnoDB: Cannot continue operation. 081218 17:46:04 mysqld_safe mysqld from pid file /var/db/mysql/biggie.pid ended 081218 17:54:09 mysqld_safe Starting mysqld daemon with databases from /var/db/mysql /usr/local/libexec/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13) 081218 17:54:09 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 081218 17:54:09 InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: File operation call: 'create'. InnoDB: Cannot continue operation. 081218 17:54:09 mysqld_safe mysqld from pid file /var/db/mysql/biggie.pid ended Either I am totally incompetent and cannot decipher the instructions or there is some kind of beatie running here... Hope somebody can help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: installation nightmare
Hi PJ, To be quite honest you may have better luck downloading and installing MySQL yourself. It's very rare I install anything from the ports on FreeBSD or apt repositories on Debian, simply because you have little (if any) control over what goes on. At least if you do it yourself you know how you're setting the system up. That's just from personal experience. Download the FreeBSD binary from http://dev.mysql.com/downloads/mysql/5.1.html, and extract it somewhere on your system (e.g. /tmp.) Follow the instructions in http://dev.mysql.com/doc/refman/5.1/en/installing-binary.html which explains in full detail what to do and why you're doing what you're doing. Just in case you're wondering, your problems with 5.1.30 appear to be that the data directory it's trying to use (/var/db/mysql) is from a pre-5.1 server (and you need to run mysql_upgrade to correct it, but this requires an up-and-running system in the first place.) Your second issue is that InnoDB does not have permissions to create files in this directory. Check the owner/group MySQL is trying to run as has access to /var/db/mysql. Regards, Andy PJ wrote: Or should I say 4-day-mare... I have been trying to set up mysql for the last 4 days with weird success. I first installed mysql 6.0.8alpha not knowing I needed 5.0 compatibility for my ISP Website host. I go everything working pretty well... mysql6, php5, phpMyAdmin, all on FreeBSD 7.0 with apache2.2.9_5; all but the setup of the pba_history table for phpmyadmin - just no way. Anyway, I unistalled mysql6 and am now trying to install mySQL 5.1.30 from the FreeBSD ports collection. I have tried about everything I can think of besides the arcane documentation that is not at all accurate - for instance, there is no mysq-test.pl or something like that that is referenced in the database, but there is mysqltest in the .../bin directory. However, running that results in errors. When I do manage to set the root user password, it is not accepted... I just cannot understand what kind of a mess this is. The only thing I can provide is the error file: (biggie - is the name of the server) 081218 17:40:17 mysqld_safe Starting mysqld daemon with databases from /var/db/mysql /usr/local/libexec/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13) 081218 17:40:17 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 081218 17:40:18 InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: File operation call: 'create'. InnoDB: Cannot continue operation. 081218 17:40:18 mysqld_safe mysqld from pid file /var/db/mysql/biggie.pid ended 081218 17:46:04 mysqld_safe Starting mysqld daemon with databases from /var/db/mysql /usr/local/libexec/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13) 081218 17:46:04 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 081218 17:46:04 InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: File operation call: 'create'. InnoDB: Cannot continue operation. 081218 17:46:04 mysqld_safe mysqld from pid file /var/db/mysql/biggie.pid ended 081218 17:54:09 mysqld_safe Starting mysqld daemon with databases from /var/db/mysql /usr/local/libexec/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13) 081218 17:54:09 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 081218 17:54:09 InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: File operation call: 'create'. InnoDB: Cannot continue operation. 081218 17:54:09 mysqld_safe mysqld from pid file /var/db/mysql/biggie.pid ended Either I am totally incompetent and cannot decipher the instructions or there is some kind of beatie running here... Hope somebody can help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MysqlDump destination file
Hi! I'm a newbie trying to use the backup\dump command. My questions are: 1. When using the command mysqldump --single-transaction --quick -uroot -p --all-databases, what is the default destination path (server\volume\folder) and the default file name? 2. If using a redirection destination_file_name, is it possible to add a path before the filename (server\volume\folder) and what is regular\correct file name? TIA Nanu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
