Re: MySQL Query Help Needed
Not sure I understand what you need? Can you create a dummy output table of what you would like your result to look like? At first glance, from reading your question, it sounds like you simply want this: select * from ppc_keywords where keywords like %job% [group by keywords] order by bid; where the group by keywords is kinda optional for this query, but it can't be that simple right? ;) On Wed, 2003-09-17 at 13:31, Johan Potgieter wrote: Can anybody help me sort my results in the correct way. My Table description is: +--+---+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+-++ | id | int(11) | | PRI | NULL| auto_increment | | link_id | int(11) | | MUL | 0 || | user_id | int(11) | | MUL | 0 || | keywords | varchar(255) | YES | | NULL|| | bid | decimal(3,2) | | MUL | 0.01|| | bid_time | timestamp(14) | YES | | NULL|| +--+---+--+-+-++ I want to do a query that will return the most relevant keywords and they highest bid amount my query looks like: select * from ppc_keywords where keywords like %job%; The results I get is something like: ++-+-+--+--++ | id | link_id | user_id | keywords | bid | bid_time | ++-+-+--+--++ | 20 | 4 | 3 | job | 0.01 | 20030916130423 | | 23 | 4 | 3 | jobs | 0.01 | 20030916130537 | | 40 | 2 | 3 | JOBS | 0.02 | 20030916144512 | | 41 | 2 | 3 | job | 0.05 | 20030916144655 | | 42 | 2 | 3 | ajob | 0.06 | 20030917124722 | | 43 | 2 | 3 | ajobs| 0.01 | 20030917120650 | | 46 | 1 | 5 | job | 0.01 | 20030917134229 | | 47 | 1 | 5 | jobs | 0.01 | 20030917134236 | | 48 | 1 | 5 | ajob | 0.01 | 20030917134240 | | 49 | 1 | 5 | ajobs| 0.01 | 20030917134245 | ++-+-+--+--++ As you can see this is not correct. I want job to be grouped together with it's highest bid amount. The following should be listed by relevance and bid. Can anyone help refine this search, please Johan Potgieter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Query Help Needed
I think the most difficult ( if not impossible with just using mysql query) is the part where you apply your logic as to the most relevant keyword. You need to be able to have a mechanism of telling the query which keyword is the most relevant, ie, human logic says, the one that matches the search string exactly, the next most relevant is the search string that has the least deviation from the keyword etc. I'm almost convinced this cannot be done with one or even multiple queries, and I would personally use something like PHP to apply that kind of logic on the result set. try the following: select distinct keywords, bid from ppc_keywords where keywords like %job% group by keywords order by bid; This should return the result grouped by the keyword ( in alphabetical order I think) and the order sorted by the highest bid, but as you can see, ajob and ajobs will be reported BEFORE job. But once you have this result, you can use PHP or PERL or the language of choice to sort it by relevance. Again, I stand to be corrected on whether mysql can return this kind of result on it's own, and I would certainly be very interested to see this done. With the advent of PHP5 with subselects and stored procedures, it might be more feasible? On Wed, 2003-09-17 at 14:51, Johan Potgieter wrote: It's not that simple as you say. I want the most relevant keywords at the top but it must be ordered by bid amount. The output I want must be: ++-+-+--+--++ | id | link_id | user_id | keywords | bid | bid_time | ++-+-+--+--++ | 41 | 2 | 3 | job | 0.05 | 20030916144655 | | 20 | 4 | 3 | job | 0.01 | 20030916130423 | | 46 | 1 | 5 | job | 0.01 | 20030917134229 | | 42 | 2 | 3 | ajob | 0.06 | 20030917124722 | | 48 | 1 | 5 | ajob | 0.01 | 20030917134240 | | 43 | 2 | 3 | ajobs| 0.01 | 20030917120650 | | 49 | 1 | 5 | ajobs| 0.01 | 20030917134245 | | 40 | 2 | 3 | JOBS | 0.02 | 20030916144512 | | 23 | 4 | 3 | jobs | 0.01 | 20030916130537 | | 47 | 1 | 5 | jobs | 0.01 | 20030917134236 | ++-+-+--+--++ I hope it makes more sense. The idea is to return keywords that are exactly what the user supplied but also keywords that closely resemble what was entered. All those results must be grouped by keyword relevance and second by bid amount. Johan At 02:25 PM 2003/09/17 +0200, you wrote: Not sure I understand what you need? Can you create a dummy output table of what you would like your result to look like? At first glance, from reading your question, it sounds like you simply want this: select * from ppc_keywords where keywords like %job% [group by keywords] order by bid; where the group by keywords is kinda optional for this query, but it can't be that simple right? ;) On Wed, 2003-09-17 at 13:31, Johan Potgieter wrote: Can anybody help me sort my results in the correct way. My Table description is: +--+---+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+-++ | id | int(11) | | PRI | NULL| auto_increment | | link_id | int(11) | | MUL | 0 || | user_id | int(11) | | MUL | 0 || | keywords | varchar(255) | YES | | NULL|| | bid | decimal(3,2) | | MUL | 0.01|| | bid_time | timestamp(14) | YES | | NULL|| +--+---+--+-+-++ I want to do a query that will return the most relevant keywords and they highest bid amount my query looks like: select * from ppc_keywords where keywords like %job%; The results I get is something like: ++-+-+--+--++ | id | link_id | user_id | keywords | bid | bid_time | ++-+-+--+--++ | 20 | 4 | 3 | job | 0.01 | 20030916130423 | | 23 | 4 | 3 | jobs | 0.01 | 20030916130537 | | 40 | 2 | 3 | JOBS | 0.02 | 20030916144512 | | 41 | 2 | 3 | job | 0.05 | 20030916144655 | | 42 | 2 | 3 | ajob | 0.06 | 20030917124722 | | 43 | 2 | 3 | ajobs| 0.01 | 20030917120650 | | 46 | 1 | 5 | job | 0.01 | 20030917134229 | | 47 | 1 | 5 | jobs | 0.01 | 20030917134236 | | 48 | 1 | 5 | ajob | 0.01 | 20030917134240 | | 49 | 1 | 5 | ajobs| 0.01 | 20030917134245 |
RE: SELECT only unique records
Sorry for hijacking this question, but while we are on this topic: How can one select unique rows based on a set of fields select distinct state_ID from financial_master where category_id='1' only returns rows based on the uniqueness of one field. what if there is another field that COMBINED with state_ID forms a unique row; how can one search for those? On Fri, 2003-09-12 at 20:13, Paul DuBois wrote: At 2:07 PM -0400 9/12/03, Dan Greene wrote: SELECT state_ID FROM financial_master WHERE category_ID = '1' becomes SELECT unique state_ID FROM financial_master WHERE category_ID = '1' -or- SELECT distinct state_ID FROM financial_master WHERE category_ID = '1' DISTINCT will work, but UNIQUE will not. DISTINCTROW is a synonym for DISTINCT. 2 points ! -Original Message- From: Comcast [mailto:[EMAIL PROTECTED] Sent: Friday, September 12, 2003 2:03 PM To: [EMAIL PROTECTED] Subject: SELECT only unique records I am sure this is a slam-dunk, but I am new to this and stumped ... thanks. I have the following statement, but I need it to pull only unique listings - I get repeated items. SELECT state_ID FROM financial_master WHERE category_ID = '1' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table is read only after copying table files to new server
Hi Bennett I had a similar problem on my system a while back. With my particular case, the problem was the the default priv's on the new server for it's users did NOT include INSERT rights. Basically what I'm saying is: these are the reasons for a table to be (or appear to be) read-only: 1) Table files belong to someone other than mysql ( not the case here) 2) The permissions on the files are set read-only ( do a chmod to the correct permissions you require) 3)the user for that database has no insert, update, delete etc, rights. For the last case you need to issue the correct grants, or you can edit the mysql user table directly and change the N to Y in the appropriate fields. Then you should probably restart the mysqld daemon. On Sun, 2003-08-17 at 01:16, Bennett Haselton wrote: I'm trying to port my MySQL tables for a database called tracerlock from one server to another. On the old server, in the /var/lib/mysql/tracerlock directory, there was a .MYD, .MYI and .frm file for every table in the database. So after creating a database called tracerlock on the new server, I copied these files over to the /var/lib/mysql/tracerlock directory on the new server. On both servers, all the table files are owned by user mysql in the mysql group. Now, when I connect to MySQL on the new machine and load the tracerlock database, show tables shows all the tables as expected, and I can perform SELECT statements on them and get the correct results. But when I try doing INSERT statements on one of the tables that was copied over, I get: mysql INSERT INTO test VALUES(3); ERROR 1036: Table 'test' is read only However, if I create a new table called test2, I can perform INSERT statements on it with no error: mysql insert into test2 values(3); Query OK, 1 row affected (0.00 sec) The files corresponding to test and test2 have the same permissions and ownership, so that's not it: -rw-rw1 mysqlmysql 20 Aug 15 21:22 test.MYD -rw-rw1 mysqlmysql1024 Aug 15 21:22 test.MYI -rw-rw1 mysqlmysql8556 Aug 15 21:22 test.frm -rw-rw1 mysqlmysql 5 Aug 16 19:13 test2.MYD -rw-rw1 mysqlmysql1024 Aug 16 19:13 test2.MYI -rw-rw1 mysqlmysql8556 Aug 16 19:13 test2.frm So how do I get rid of the error that Table 'test' is read only? -Bennett [EMAIL PROTECTED] http://www.peacefire.org (425) 497 9002 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: help to structure query without using subselects
Thanks Roger, I will give it a bash. PS, just to get my head around things. The distinct clause will return only one occurrance of the implicated field. distinctrow would do the same, but for an entire row. what I am looking for is something like distinctset(field1,field2,field7) , is this what the MAX-CONCAT trick attempts to do? You see, what I'm getting at is that I am afraid that even with the MAX-CONCAT trick it will not know to include the anything_else from the distinct row with the highest id, but rather the first occurrance thereof. And by the way, as *i think* I mentioned, anything_else would most definately NOT be an integer or anything that I would think can be MAX'ed. However, I'm already discrediting/doubting a solution without even testing it... Geez, I'm a prick hey? ;) Maybe you can also help me to think about the following: id namesomething else 1 joe testtest2 2 joe testtest3 3 jacktesta test 4 jacktestb test1 5 jacktestb test2 Now I want to query with the following pseudo string: I first need to get hold of the distinct set of name,something with the highest row id, and basically remove everything else from the equation. For instance, should I do select id,name, something from this_table where else = 'test2' it should NOT return a value. I know this doesn't make sense, because this query is valid, and *should* by rights return 1 joe test 5 jacktestb BUT, that is my dilemma, for what I'm lookinmg for, only 5 jacktestb should return because that row did in fact match the criteria for the else = test2, BUT, it also was indeed the last row with the distinct set of jack and testb. My problem is having that last condition added to the string that says where else = 'test2' BUT only if distinct(name, something) is the row with the highest id. When I think of this, it seems that the only way to do this is to first create a temporary table that only contains the last row for that particular name/something combination, and only once you have that table, to query it again with a where else ='test2', cause then you KNOW, there can only be 1 or 0 rows in the result set. However, to create this temporary table that INCLUDES the else, I am back at square one. It's easy enough to get a temp table with only one occurrance of say name OR something, but to get one with a COMBINATION of name AND something is my achiles heal... Again, sorry if your proposed solution does exactly this ( touch wood, and a BIG HUGE thank you then), I just want to make sure that you (or anyone else interested in this thread) understands exactly what the question is ( believe me, after a while thinking of it myself, I sometimes lose the entire question completely inbetween all the if and and or's flying through my head.). Thanks in any event for your time and interest. -Original Message- From: Roger Baklund [mailto:[EMAIL PROTECTED] Sent: Friday, August 01, 2003 2:49 AM To: [EMAIL PROTECTED] Cc: Petre Agenbag Subject: Re: help to structure query without using subselects * Petre Agenbag [...] This works: select distinct name, max(id) as mid from table group by name but it only returns the name and the highest id for such a name. if I try this: select distinct name, max(id) as mid, anything_else from table group by name it returns the FIRST anything_else, and not the value in the last row for that name ( as I would like it to) Try the MAX-CONCAT trick: select distinct name, max(concat(id,'|',anything_else,'|',even_more)) as name_info from table group by name Then you must programatically split the 'name_info' column back to the individual fields, or use SUBSTRING, like in the example in the manual: URL: http://www.mysql.com/doc/en/example-Maximum-column-group-row.html HTH, -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help to structure query without using subselects
Hi List Can anyone help me to do the following without the need of subselects: I have a table that contains rows where mostly, the only common field is the name. The rest of the data fields, even though they are related to the name, differs from row to row ( they are things like the address etc, which could have changed on the next entry for the same name) What I'm trying to get out of the table is a list of all the names ( with the duplicates pruned out by DISTINCT) and other collumns for the row with the highest id. This works: select distinct name, max(id) as mid from table group by name but it only returns the name and the highest id for such a name. if I try this: select distinct name, max(id) as mid, anything_else from table group by name it returns the FIRST anything_else, and not the value in the last row for that name ( as I would like it to) Any help? mysql, query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: cant connect to mysql server through socket
Did you build mysql yourself, or did you install it along with Suse? If it's installed with suse, start the mysql service via the suse method (not sure what that is, on RedHat, one would simply say service mysqld start). If you built it by installing the binary, then your mysql should be located in /usr/local/mysql to start, cd to that folder and say bin/mysqld_safe (for 4.x) or bin/safe_mysqld (for 3.x) Also, if you installed the binary, make sure the folder that contains the mysql files ( /usr/local/mysql is a symlink to the actual location) is owned by mysql user (chown -R mysql.mysql /path/to/files From the sounds of things however, it looks like you are trying to use the version that came with suse. For that, you normally need to run the mysql_install_db script first, and then start the mysql daemon. Then you need to run mysqladmin to set a root password and configure the grants usually, mysqladmin -u root password 'your_root_password_for_mysql' will set the root password. You will need to restart the mysql daemon after that. On Wed, 2003-07-30 at 13:07, D. K. wrote: Petre; Running ps -aux i cannot see mysql among listed processes. I checked mysql folders under several directories , they are owned by root. Also mysql start/mysql stop etc. commands dont work producing the same warning Cant connect.. What can i do more? Thanks Devrim Petre Agenbag [EMAIL PROTECTED] schrieb am 28.07.03 08:35:48: 1) make sure mysql is running [and as user mysql] do a ps -aux to see. 2) make sure that the mysql folder and files are owned by mysql (ls -l) On Sun, 2003-07-27 at 10:38, D. K. wrote: Hi; I have installed suse 8.2 a short while ago and at my first attempt to connect mysql (via shell and a perl dbi script) i get following error: can't connect to mysql server through socjet '/var/lib/mysql/mysql.sock'. i know this looks like a common error and I am terribly sorry if this kind of subject has been posted before but I'll appreciate any urgent help. Thanks Devrim __ ComputerBild 15-03 bestaetigt: Den besten Spam-Schutz gibt es bei WEB.DE FreeMail - Deutschlands beste E-Mail - http://s.web.de/?mc=021121 __ ComputerBild (15-03) empfiehlt: Der beste Spam-Schutz ist bei WEB.DE FreeMail - Deutschlands beste E-Mail - http://s.web.de/?mc=021124 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: Re: cant connect to mysql server through socket
Don't stare too long at the Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) , it will blind you after a couple of minutes ;p Seriously, the fact that the sock file is not there simply tells me that mysql is not running, ie, even if you could copy a mysql.sock file to that location, your problems would still be there. the sock file is created by mysql when it starts up. You should first run the mysql_install_db script, then start mysql, then set the password. mysqladmin won't run either if mysql daemon is not running. what I suggest you do is to check that mysql is set to start in runlevels 3 to 5 in your init scripts, then reboot the machine and see if suse will have better luck in starting mysql. You can't do anything before mysql is not running. Also, check to see in your my.cnf file that all the values listed there makes sense for your system. On Wed, 2003-07-30 at 15:04, D. K. wrote: Petre; My installation is coming with suse.I ran mysql_install_db and after that i tried setting root passwd as you see below,and i got foollowing errors. I had checked mentioned socket under /var/lib/mysql -mysql.sock- before and it doesnt exist. Blame me for not reporting it before. Thank you for kind help. Devrim linux:/usr/bin # mysqladmin -u root password 'devrim' mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)' Check that mysqld is running and that the socket: '/var/lib/mysql/mysql.sock' exists! __ Wo gibt es den besten Spam-Schutz? Laut ComputerBild 15-03 bei WEB.DE FreeMail - Deutschlands beste E-Mail - http://s.web.de/?mc=021122 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
subselects or join?
Hi List, My head is not on right today. I have something like this: main_table id namemain_type 1 joe type1 2 johntype2 3 jacktype1 table type1 id_type1id action 1 1 2003-07-01 2 1 2003-08-03 3 2 2003-04-04 4 1 2003-09-09 I want to do a query to find all the names with an action date between a range of dates. For instance: The logic reads. I want the names, dates of the action and main_types of all the people who have an action entry in the type1 table between date1 and date2 (where date1 and date2 are user input for the search criteria). The result MUST contain duplicate names (if like for example Joe in the example above, has more than one action entry in the table that lies in the search date range.) Help? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Left Join multiple tables
Hi List Me again. I'm trying to return from multiple tables, the records that have field information_sent between two dates. The tables are all related by means of the id of the entry in the main table, ie.. main id entity_name ... fof id_fof id information_sent ... pub id_pub id information_sent ... etc. So, I tried the following join select * from main left join fof on main.id = fof.id left join pub on main.id = pub.id left join gov on main.id = gov.id left join med on main.id = med.id left join ngo on main.id = ngo.id left join own on main.id = own.id left join sup on main.id = sup.id left join tra on main.id = tra.id where ( (fof.information_sent '$date1' and fof.information_sent '$date2') OR (pub.information_sent '$date1' and pub.information_sent '$date2') OR (gov.information_sent '$date1' and gov.information_sent '$date2') OR (med.information_sent '$date1' and med.information_sent '$date2') OR (ngo.information_sent '$date1' and ngo.information_sent '$date2') OR (own.information_sent '$date1' and own.information_sent '$date2') OR (sup.information_sent '$date1' and sup.information_sent '$date2') OR (tra.information_sent '$date1' and tra.information_sent '$date2') ) order by entity_name BUT, although it seems to be joining the tables correctly AND only returning the ones with the correct date criteria, it does NOT return the id or the information_sent fields correctly ( due to duplication in the result ) What am I doing wrong? Thanks mysql, query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: cant connect to mysql server through socket
1) make sure mysql is running [and as user mysql] do a ps -aux to see. 2) make sure that the mysql folder and files are owned by mysql (ls -l) On Sun, 2003-07-27 at 10:38, D. K. wrote: Hi; I have installed suse 8.2 a short while ago and at my first attempt to connect mysql (via shell and a perl dbi script) i get following error: can't connect to mysql server through socjet '/var/lib/mysql/mysql.sock'. i know this looks like a common error and I am terribly sorry if this kind of subject has been posted before but I'll appreciate any urgent help. Thanks Devrim __ ComputerBild 15-03 bestaetigt: Den besten Spam-Schutz gibt es bei WEB.DE FreeMail - Deutschlands beste E-Mail - http://s.web.de/?mc=021121 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql lockups
Hi List Been trying to help out with this problem on a SunOS5.8 box running 3.23.56 It experiences periodic lockups of mysql. It does not seem that this server has a my.cnf file for mysql, and I'm not sure if that matters (what are the defaults mysql use when it cannot find the .cnf file?) Anyway, I have asked for any help in making a my.cnf file in other threads, but it seems everyone is on 4.x already, or they cannot help in that regard. Below is some feedback from the server 1st : top last pid: 11217; load averages: 0.00, 0.00, 0.00 07:54:43 17 processes: 16 sleeping, 1 on cpu CPU states: 85.2% idle, 10.2% user, 4.4% kernel, 0.2% iowait, 0.0% swap Memory: 2048M real, 667M free, 2065M swap in use, 3403M swap free PID USERNAME THR PRI NICE SIZE RES STATETIMECPU COMMAND 22435 21 580 89M 32M sleep2:43 0.58% caspeng 11210 1 580 2896K 1664K cpu 0:00 0.54% top 9961 31 580 15M 6600K sleep0:13 0.27% mysqld 11139 1 580 5608K 1752K sleep0:00 0.01% sshd 18642 5 580 2312K 936K sleep0:20 0.00% caspd 491 5 580 3768K 2016K sleep0:10 0.00% httpd 9948 1 400 1048K 760K sleep0:00 0.00% safe_mysqld 11141 1 480 2384K 1904K sleep0:00 0.00% tcsh 9228 3 580 4000K 2576K sleep0:00 0.00% httpd 10999 4 580 3920K 2472K sleep0:00 0.00% httpd 10510 3 580 3888K 2448K sleep0:00 0.00% httpd 11001 4 580 3888K 2440K sleep0:00 0.00% httpd 11081 4 580 3880K 2432K sleep0:00 0.00% httpd 11075 4 580 3872K 2424K sleep0:00 0.00% httpd 11076 4 580 3872K 2408K sleep0:00 0.00% httpd and 2nd is ..% mysqladmin -u root -p -v pro Enter password: +-++---++-+--+---+---+ | Id | User | Host | db | Command | Time | State | Info | +-++---++-+--+---+---+ | 187 | root | localhost | Users | Sleep | 278 | | | | 190 | root | localhost | Boards | Sleep | 278 | | | | 191 | root | localhost | Users | Sleep | 278 | | | | 192 | root | localhost | Boards | Sleep | 278 | | | | 193 | root | localhost | Boards | Sleep | 278 | | | | 194 | root | localhost | Boards | Sleep | 278 | | | | 226 | root | localhost | Boards | Sleep | 278 | | | | 227 | root | localhost | Users | Sleep | 123 | | | | 228 | root | localhost | Boards | Sleep | 123 | | | | 229 | root | localhost | Boards | Sleep | 123 | | | | 230 | root | localhost | Users | Sleep | 11 | | | | 231 | root | localhost | Boards | Sleep | 123 | | | | 232 | root | localhost | Boards | Sleep | 123 | | | | 233 | root | localhost | Users | Sleep | 271 | | | | 237 | user1 | localhost | Philosophies | Sleep | 113 | | | | 238 | user1 | localhost | PhilosophiesCustom | Sleep | 133 | | | | 239 | user1 | localhost | Countries | Sleep | 133 | | | | 240 | root | localhost | Debatabase | Sleep | 123 | | | | 241 | root | localhost | Debatabase | Sleep | 123 | | | | 242 | root | localhost | Debatabase | Sleep | 123 | | | | 243 | root | localhost | Debatabase | Sleep | 123 | | | | 244 | root | localhost | Debatabase | Sleep | 14 | | | | 245 | root | localhost | Debatabase | Sleep | 14 | | | | 246 | root | localhost | Debatabase | Sleep | 14 | | | | 248 | root | localhost || Query | 0| | show full processlist | +-++---++-+--+---+---+ ..% and this is the error reported by the browser on such lockups. ADODB.Recordset.1 error '80004005'
any help with my.cnf plz?
HI Is there ANYONE that can help me with a my.cnf for a large site for version 3.23.56, or even just where I can download one? I am trying to download that version and then get my own, but the dl times out... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: any help with my.cnf plz?
Lenz Thanks for the reply, will go to the link immediately. I would just like to clarify something: I have 3.23.56 , your's is 4.x, does it matter? I should obviously just leave out the stuff that is not relevant like InnoDB support etc... CAn you give me possibly pointers to the absolute crucial things that should be in that file? Also, would not having a my.cnf file cause strange behaviour like lockups? Thanks again for the help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
my.cnf for 3.23.56
HI List Need advise on a suitable my.cnf for 3.23.56 on SunOS5.8 MySQL is installed on a virtual machine with plenty of ram and swap space. However, there is no my.cnf file currently, and mysql seems to lock up completely from time to time even though system seems fairly stable (although I saw some pretty hefty swap usage on the virtual nachine (while the RAM was still reporting plenty available--strange or not?)). Anyway, am hoping that a well defined my.cnf might do the trick here. Anyone has a sample for me configured (equivalent to the my_large.cnf usually accompanying the source) I cannot find it on this particular machine. PS, will/should having a my.cnf file help? Anything else I can check to see what could cause this BEFORE I start hacking at the ASP code (could it be a misbehaving ASP page causing this?) PPS, it looked more like mysql was locking out users (too many connections?), but I don't know SunOS that good to go and look for evidence (there were no errors in the mysql logfiles, BUT, mysql was not configured to log much it seems. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Find entry with max date.
Hi I have a relational db with a couple of tables holding things like comments and complaints and actions. In the main table, I have the contact details. What I'd like to do is to query the db with a php app and to display the entries in the result set with a last action/comment/contact done one max date. ie. main_table: id namesurname date 1 johndoe 2003-07-07 2 janedoe 2003-07-08 contact_table main_id date 1 2003-07-09 1 2003-07-12 2 2003-07-09 complaints_table main_id date 1 2003-07-10 2 2003-07-11 actions_table main_id action date 1 action1 2003-07-13 Now, querying the db for all the entries in main, i'd like to return something like: NameSurname Last action/comment/complaint JohnDoe action:action1 2003-07-13 JaneDoe complaint: 2003-07-11 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: import from MySQL into MySQL
If you cannot get file system access on the webserver, then you will need to dump the contents of the tables into a text file with the appropriate dump commands. If the webserver has something like phpMyAdmin, you can use that to easily create a text file that you can then import into your mysql on your windows machine. Easiest, as I've said, ask the company that does the hosting to zip the tables in the data_dir and mail it to you, you would then simply create the DB and user on your machine, and drop the files into you data_dir ( all this assumes mysql ver 3.23.5x or higher and MyISAM tables, else, do the manual dumps) On Fri, 2003-07-11 at 15:36, Paul wrote: I have the following problem : I have been asked to redevelop a clients website which is written in PHP and uses MySQL as the backend. This site is on a remote server so there is only so much I can do with it. They want me to move it to a Windows box in our office and convert the pages to asp but retain the backend. I have installed a copy of MySQL on our windows box (SQL Yog front end) and have downloaded the pages. The problem I have is getting to the data. How can I extract the database from the remote server and set it up on my local Windows version of MySQL ? I am fairly new to MySQL and would appreciate any tips any of you can give me. Thanks in Advance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
reset id of tables
Hi List Probably a stupid question, but I want to know if there is a quick way to reset all the id's on my tables to 0? I ran a whole bunch of tests on the app; writing and deleting to the tables, and would like to reset all tables to start at 0 when I make the app live. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: reset id of tables - SOLVED
Nevermind, found it: truncate table; On Mon, 2003-06-23 at 15:59, Petre Agenbag wrote: Hi List Probably a stupid question, but I want to know if there is a quick way to reset all the id's on my tables to 0? I ran a whole bunch of tests on the app; writing and deleting to the tables, and would like to reset all tables to start at 0 when I make the app live. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ETA on 4.1
Hi List Is there any way to get an estimate of the release date of the next stable version of MySQL? The reason I'm asking; I am currently busy configuring new webservers, but it is not crucial as to the exact time I deploy them ( they will replace currently running servers), so if I knew 4.1 would be released in a week or 2 ( or 3 or 4 ...), I could maybe wait for it before I deploy the new servers ( will just save me one migration), and I'd hate to deploy the servers and have 4.1 stable release come out the next day Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Getting the row back with the highest ID.
Hi List. I think I'm having a very off day and need some confirmations on how MySQL works with it's result sets. I have a couple of relational tables , the first holding the person's name and address for instance, and the other tables holds comments and complaints respectively. Each table has it's own id field, as well as a master_id that equals the id of the main table. The app that I'm writing (in PHP), lists the users with a simple select * from main , and this returns all the users currently on the system ( I have names as varchar and unique). My problem is now with the following: When the user clicks on one of the names, I want to do a couple of things: a) The user details be displayed along with all the comments and complaints that correspond to that users id located in the other tables. pseudo SQL - select * from comments where (place_holder id in comments) = (id in main table); b) Being able to list the comments and complaints in reverse order ie, older ones first: pseudo SQL - select * from comments where (place_holder id) = (id in main table) order by id desc; c) List ONLY the last (newest) comments/complaints THIS IS WHERE I have problems: If I do a select MAX(id), comment from comments where (place_holder id) = (id in main table) will MySQL automagically grab the comment from the row that has the maximum ID? If so, is there a shorter way of doing this query? For my example here, it's not a big deal, but with larger tables with more collumns, having to specify the collumns in the query ( when I want ALL to be returned) becomes a bit of a hassle.. I basically want to say: return ONLY the last comment added where the id matches the supplied id from main_table. So, I need to Translate this to SQL... Can I do this with SQL, or must I first establish the id with the select MAX(id) from comments where id = provided_id, and then do a new query select * from comments where id = MAX(id) Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Getting the last entered row from a relational table
Hi List. I think I'm having a very off day and need some confirmations on how MySQL works with it's result sets. I have a couple of relational tables , the first holding the person's name and address for instance, and the other tables holds comments and complaints respectively. Each table has it's own id field, as well as a master_id that equals the id of the main table. The app that I'm writing (in PHP), lists the users with a simple select * from main , and this returns all the users currently on the system ( I have names as varchar and unique). My problem is now with the following: When the user clicks on one of the names, I want to do a couple of things: a) The user details be displayed along with all the comments and complaints that correspond to that users id located in the other tables. pseudo SQL - select * from comments where (place_holder id in comments) = (id in main table); b) Being able to list the comments and complaints in reverse order ie, older ones first: pseudo SQL - select * from comments where (place_holder id) = (id in main table) order by id desc; c) List ONLY the last (newest) comments/complaints THIS IS WHERE I have problems: If I do a select MAX(id), comment from comments where (place_holder id) = (id in main table) will MySQL automagically grab the comment from the row that has the maximum ID? If so, is there a shorter way of doing this query? For my example here, it's not a big deal, but with larger tables with more collumns, having to specify the collumns in the query ( when I want ALL to be returned) becomes a bit of a hassle.. I basically want to say: return ONLY the last comment added where the id matches the supplied id from main_table. So, I need to Translate this to SQL... Can I do this with SQL, or must I first establish the id with the select MAX(id) from comments where id = provided_id, and then do a new query select * from comments where id = MAX(id) Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting the row back with the highest ID.
Thanks, I presume that subselects is not yet featured in MySQL 4.0.13? ( Sorry, can prolly get this by RTFM ), If it does, how would the query look? Thanks On Thu, 2003-06-05 at 11:44, Egor Egorov wrote: Petre Agenbag [EMAIL PROTECTED] wrote: I think I'm having a very off day and need some confirmations on how MySQL works with it's result sets. I have a couple of relational tables , the first holding the person's name and address for instance, and the other tables holds comments and complaints respectively. Each table has it's own id field, as well as a master_id that equals the id of the main table. The app that I'm writing (in PHP), lists the users with a simple select * from main , and this returns all the users currently on the system ( I have names as varchar and unique). My problem is now with the following: When the user clicks on one of the names, I want to do a couple of things: a) The user details be displayed along with all the comments and complaints that correspond to that users id located in the other tables. pseudo SQL - select * from comments where (place_holder id in comments) = (id in main table); b) Being able to list the comments and complaints in reverse order ie, older ones first: pseudo SQL - select * from comments where (place_holder id) = (id in main table) order by id desc; c) List ONLY the last (newest) comments/complaints THIS IS WHERE I have problems: If I do a select MAX(id), comment from comments where (place_holder id) = (id in main table) will MySQL automagically grab the comment from the row that has the maximum ID? If so, is there a shorter way of doing this query? For my example here, it's not a big deal, but with larger tables with more collumns, having to specify the collumns in the query ( when I want ALL to be returned) becomes a bit of a hassle.. Nope. MySQL will return fist found comment. I basically want to say: return ONLY the last comment added where the id matches the supplied id from main_table. So, I need to Translate this to SQL... Can I do this with SQL, or must I first establish the id with the select MAX(id) from comments where id = provided_id, and then do a new query select * from comments where id = MAX(id) Without subselects you need 2 queries. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
unique results
Hi I am having trouble with a query to return only one instance of a name in a table. For instance: id nametowndate 1 jacktown1 2002-12-12 2 johntown2 2002-12-13 3 jacktown4 2002-12-21 4 jacktown5 2002-12-30 5 johntown2 2002-12-15 I want to have a query that will return this: id nametowndate 4 jacktown5 2002-12-30 5 johntown2 2002-12-15 So, I want only the last entry based on the name. I tried select MAX(id), name from table group by name select distinct name from table group by name But both sometimes fail. Note: I am using a sample table here, I suspect that in my table with the actual data, that there might be spaces before or after the names, so I might need to clean them up from the table ( any quick way of doing this?) as it seems to be the only logical explanation for the queries to return multiple instances. Also, the rows are not 100% unique wrt the other fields, so that might be why distinct won't work? Any ideas? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
distinct and distinctrow
sql,query Hi List Please can someone help me with this: I need to filter duplicate entries from my result query, but there's a twist... The table has something like this: id namekey 1 name1 key1 2 name2 key2 3 name3 key3 4 name1 key1 5 name 1 key1 6 name2 key2 Now I want to search the table for all unique name/key combinations. I tried select DISTINCTROW name,key from table order by name; but, it returns name1 key1 name2 key2 name3 key3 name 1 key1 Where name 1 key1 was obviously a typo, yet, I would ideally want to filter or flag this through some kind of logic = ( there is already a key1 associated with a name1, cannot associate key1 with another name!) Firstly, my problem is with the query as I quoted it itself, ie, is that the best way to prevent duplicates? I might also try this: select DISTINCTROW MAX(id),name,key from table group by name; but this will only ensure that only the LATEST key/name pair is listed right? Second, how do I translate my logic above into SQL? Or should I do it in PHP? Thanks. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
search through entries
HI can someone help me with general search syntax. I want to search a table with a search string obtained from a form input ( PHP/Mysql app) The problem is: Lets say there is an entry in the table: John Doe When I search for that, with something like: select * from table where name like '%$name%' it generally works fine, BUT, when they make a typo, something like John Do, it doesn't return a result. What is the best way of searching through a table to ensure the best possible hit? I thought of splitting the $name search string into the seperate words john and doe and then searching for both with a like'%%', and then combining the result of both as the result for the search, but I was hoping there might be a better way to do this, specially if the search string contains only one word. So, I would like the results to be rather relaxed, if they search for johnn, it should return john as well as a posibility. Hope this won't be too complicated to do. sql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Linux conf files in mysql tables
mysql,query Hi List Not sure if this is the right list to post to, might need a cross posting on a general Linux list as well, anyway; I would like to explore the possibilities of having (or converting) all Linux services use mysql tables for their configuration files instead of the usual flat-file .conf files. At this point the benefits are much clearer than the negatives, such as being able to modify services much easier via apps written in PHP or PERL, making it much easier to write webmin like apps, or even maybe the greatest advantage of all, being able to modify your entire system from one central database, much like mysql's own mysql table. One of the downsides to this will most probably be the fact that Linux will then be dependant on mysql, so to prevent that situation, it would probably be best to have an either or situation. Anyway, I don't want to blabber my own opinions here, I'm more interested in what others think, and more so in who would need to be involved to achieve this, ie, will the coders of the individual services need to modify their apps? Thanks Petre - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
slow queries
mysql,query Hi List I have a couple of slow queries listed in my slow log, but I don't know what to do from here to make them fast. Here is an EXPLAIN table: mysql explain w3t_Users; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | U_Username | varchar(30) | | MUL | || | U_Password | varchar(32) | | | || | U_Email| varchar(50) | YES | | NULL || | U_Fakeemail| varchar(50) | YES | | NULL || | U_Name | varchar(100) | YES | | NULL || | U_Totalposts | int(9) unsigned | YES | | NULL || | U_Laston | int(11) unsigned | YES | | NULL || | U_Signature| text | YES | | NULL || | U_Homepage | varchar(150) | YES | | NULL || | U_Occupation | varchar(150) | YES | | NULL || | U_Hobbies | varchar(200) | YES | | NULL || | U_Location | varchar(200) | YES | | NULL || | U_Bio | text | YES | | NULL || | U_Status | varchar(15) | | MUL | User || | U_Sort | int(4) unsigned | YES | | NULL || | U_Display | varchar(10) | | | || | U_View | varchar(10) | | | || | U_PostsPer | int(11) unsigned | YES | | NULL || | U_Number | int(9) unsigned | | PRI | NULL| auto_increment | | U_EReplies | char(3) | | | || | U_Notify | char(3) | | | || | U_TextCols | char(3) | YES | | NULL || | U_TextRows | char(3) | YES | | NULL || | U_Extra1 | varchar(200) | YES | | NULL || | U_Extra2 | varchar(200) | YES | | NULL || | U_Extra3 | varchar(200) | YES | | NULL || | U_Extra4 | varchar(200) | YES | | NULL || | U_Extra5 | varchar(200) | YES | | NULL || | U_Post_Format | varchar(5) | | | || | U_Registered | int(11) unsigned | YES | | NULL || | U_Preview | varchar(5) | YES | | NULL || | U_Picture | varchar(150) | YES | | NULL || | U_PictureView | char(3) | YES | | NULL || | U_Visible | char(3) | YES | | yes || | U_PicturePosts | char(3) | YES | | NULL || | U_AcceptPriv | char(3) | YES | | yes || | U_RegEmail | varchar(50) | YES | | NULL || | U_RegIP| varchar(15) | YES | | NULL || | U_Groups | varchar(250) | YES | | -1- || | U_Language | varchar(20) | YES | | NULL || | U_Title| varchar(100) | YES | | NULL || | U_FlatPosts| char(2) | YES | | NULL || | U_TempPass | varchar(32) | YES | | NULL || | U_Color| varchar(15) | YES | | NULL || | U_TempRead | text | YES | | NULL || | U_StyleSheet | varchar(50) | YES | | NULL || | U_TimeOffset | varchar(10) | YES | | NULL || | U_Privates | int(4) unsigned | YES | | 0 || | U_FrontPage| varchar(20) | YES | | NULL || | U_ActiveThread | int(4) unsigned | YES | | NULL || | U_StartPage| char(2) | YES | | cp || | U_Favorites| varchar(250) | YES | | - || | U_ShowSigs | char(3) | YES | | NULL || | U_OnlineFormat | char(3) | YES | | NULL || | U_Rating | varchar(5) | YES | | 0 || | U_Rates| int(4) unsigned | YES | | 0 || | U_RealRating | int(1) unsigned | YES | | NULL || | U_PicWidth | int(4) unsigned | YES | | NULL || | U_PicHeight| int(4) unsigned | YES | |
Re: How to port MS Access to MySQL ??
myodbc works fairly OK for me. On Fri, 2002-11-08 at 14:17, tmb wrote: Is there a tool for doing a quick port from MS Access to MySQL? Or must you manually create all the tables sql statements in MySQL and then export the MS Access data to a comma delimited file... then import it into MySQL? Thanks for any help - tmb __ Do you Yahoo!? U2 on LAUNCH - Exclusive greatest hits videos http://launch.yahoo.com/u2 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
unique data sets
Hi just out of interest, How can I create unique data sets, or rather, set a set of data as unique, to prevent another entry in the table that meets the same criteria, for instance: id namesurname address 1 johndoe 1 street 2 maryjane2 lane 3 johndoe 3 ave 4 johndoe 1 street As you can see row one and row 4 is identical in all 3 collumns, so how would I prevent a write like this to the table? sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
lost connection inquery : ODBC
query , sql Hi Any known issues with RH8.0 and ODBC? I keep getting lost connection messages when trying to connect from a windows box to mysql, it worked 100% on a 7.3RH box. There is no firewall. Any workarounds? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: lost connection inquery : ODBC
Hi Walt Thanks for the quick reply. Answer to both questions is yes. As a matter of interest, What I usually do is to create a myodbc db with a myodbc user and password myodbc I create this db and user with the mysql_setpermission util, and I use % for the host name. YET, when I try to connect from the localhost, it denies permission, only accepts connections from remote location ( not ODBC though, I used a php script on another host to test connection) Any ideas? Thanks On Tue, 2002-10-22 at 21:37, walt wrote: Petre Agenbag wrote: query , sql Hi Any known issues with RH8.0 and ODBC? I keep getting lost connection messages when trying to connect from a windows box to mysql, it worked 100% on a 7.3RH box. There is no firewall. Any workarounds? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Petre, If you run netstat -an |grep tcp do you see that the mysql port (3306 ) listening? Also, even if you dont have a firewall, if you run iptables -L does it show all rules set to ACCEPT? walt - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: lost connection inquery : ODBC
That works fine, I can log in as my root user and the perms look fine, I also use phpmyadmin, and looks fine too, can access all my db's that way, just the odbc that seems to be bitchin. It's not a train smash, I exported it to my older rh7.0 server over the net, but I'd still like to know for future what causes this, as I'm to decide soon what to upgrade to, 7.3 or 8.0, at this point, I'm leaning heavily towards 7.3 as my gutt tells me this might be some kind of a bug. Thanks for the help so far. On Tue, 2002-10-22 at 22:07, walt wrote: On Tuesday 22 October 2002 03:41 pm, Petre Agenbag wrote: Hi Walt Thanks for the quick reply. Answer to both questions is yes. As a matter of interest, What I usually do is to create a myodbc db with a myodbc user and password myodbc I create this db and user with the mysql_setpermission util, and I use % for the host name. YET, when I try to connect from the localhost, it denies permission, only accepts connections from remote location ( not ODBC though, I used a php script on another host to test connection) Any ideas? Thanks On Tue, 2002-10-22 at 21:37, walt wrote: Petre Agenbag wrote: query , sql Hi Any known issues with RH8.0 and ODBC? I keep getting lost connection messages when trying to connect from a windows box to mysql, it worked 100% on a 7.3RH box. There is no firewall. Any workarounds? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php snip Petre, I've never used the mysql_setpermission util before so I'm really not sure. Have you tried logging in on the server using the OS mysql user? To clarify - su - mysql /usr/bin/mysql If you can get in that way, you can look at the different user permissions and possibly see what's wrong -- Walter Anthony System Administrator National Electronic Attachment Atlanta, Georgia 1-800-782-5150 ext. 1608 If it's not broketweak it - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
rolling over mysql logs
HI Probably a silly question, but how can I roll the logs for mysql? I started it with safe_mysqld --log-slow-queries --log So it created two logs in my /var/lib/mysql folder, one holding ALL queries, and one for only the slow queries. The general log is growing quite rapidly, and I would like to gzip the log at the end of each day so it will be easier to find what you are looking for, and so I can delete old logs at a glance. Can someone help me with a script to do this? Thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: loggin all calls made to mysql from php
Thanks, I did it and it works. I just would like to know one more thing. You mention a slow log option, how do invoke that type specifically? I restarted mysql with safe_mysqld --log and it created a myserver.log file in the data directory, but it seems to log ALL queries and is growing pretty fast, so the slow option seems a better bet, as I *think* that the slow queries would be the ones most likely to cause the problems anyway? So do I do safe_mysqld --log-slow or how? Thanks On Thu, 2002-10-17 at 09:51, Chung Ha-nyung wrote: With --log[=filename] option to mysqld_safe, you can specify the path and name of log file. You may put the line like this to my.cnf file log = filename If absoulte path is not given, the path is considered one relative to $data directory, in my case /usr/local/mysql/var and filename is not given, `hostname-s`-slow.log will be used. -- Chung Ha-nyung alita@[neowiz.com|kldp.org] Sayclub http://www.sayclub.com NeoWiz http://www.neowiz.com -Original Message- From: Petre Agenbag [mailto:internet;boesmanland.com] Sent: Thursday, October 17, 2002 4:39 PM To: 정 하녕 Cc: [EMAIL PROTECTED] Subject: RE: loggin all calls made to mysql from php Hi Thanks for the prompt reply. Can you please tell me where the logfile will be located if I start it with the --log option? Will syslogd take over the function of the logging, ie, do the log rotation etc. and if possible, could you give me an example of how the log would look? Sorry for asking all these questions, I just want to make sure that it is what I'm looking for, as I will need to stop the mysql server to do this... Thanks On Thu, 2002-10-17 at 09:21, Chung Ha-nyung wrote: Use --log option when starting mysqld_safe(or safe_mysqld) -- Chung Ha-nyung alita@[neowiz.com|kldp.org] Sayclub http://www.sayclub.com NeoWiz http://www.neowiz.com -Original Message- From: Petre Agenbag [mailto:internet;boesmanland.com] Sent: Thursday, October 17, 2002 4:05 PM To: [EMAIL PROTECTED] Subject: loggin all calls made to mysql from php HI I posted this question on PHP list as well. I would like to be able to log all mysql queries made from php scripts. I have had a couple of strange mysql lockups on my RedHat server, where mysqld process won't even be killed, and since no user works with mysql except through PHP, the normal mysql logs doesn't seem to be very helpful. I have multiple virtual hosts on the server, all allowing .php and most having mysql db's, so I want to be able to see what scripts are causing the problem. Is this the right way to go about finding the problem? Are there any other ideas or methods in use by other admins out there to determine if there are poorly coded scripts/queries run on the server? Thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
loggin all calls made to mysql from php
HI I posted this question on PHP list as well. I would like to be able to log all mysql queries made from php scripts. I have had a couple of strange mysql lockups on my RedHat server, where mysqld process won't even be killed, and since no user works with mysql except through PHP, the normal mysql logs doesn't seem to be very helpful. I have multiple virtual hosts on the server, all allowing .php and most having mysql db's, so I want to be able to see what scripts are causing the problem. Is this the right way to go about finding the problem? Are there any other ideas or methods in use by other admins out there to determine if there are poorly coded scripts/queries run on the server? Thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: loggin all calls made to mysql from php
Hi Thanks for the prompt reply. Can you please tell me where the logfile will be located if I start it with the --log option? Will syslogd take over the function of the logging, ie, do the log rotation etc. and if possible, could you give me an example of how the log would look? Sorry for asking all these questions, I just want to make sure that it is what I'm looking for, as I will need to stop the mysql server to do this... Thanks On Thu, 2002-10-17 at 09:21, Chung Ha-nyung wrote: Use --log option when starting mysqld_safe(or safe_mysqld) -- Chung Ha-nyung alita@[neowiz.com|kldp.org] Sayclub http://www.sayclub.com NeoWiz http://www.neowiz.com -Original Message- From: Petre Agenbag [mailto:[EMAIL PROTECTED]] Sent: Thursday, October 17, 2002 4:05 PM To: [EMAIL PROTECTED] Subject: loggin all calls made to mysql from php HI I posted this question on PHP list as well. I would like to be able to log all mysql queries made from php scripts. I have had a couple of strange mysql lockups on my RedHat server, where mysqld process won't even be killed, and since no user works with mysql except through PHP, the normal mysql logs doesn't seem to be very helpful. I have multiple virtual hosts on the server, all allowing .php and most having mysql db's, so I want to be able to see what scripts are causing the problem. Is this the right way to go about finding the problem? Are there any other ideas or methods in use by other admins out there to determine if there are poorly coded scripts/queries run on the server? Thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: and or in query
Sorry, let me try and explain a bit better: Table: Field1 Field2 Field3 annetypist good betty reception fair sandy typist excellent elise pa bad danareception excellent cindy reception bad Now, I want to look for all receptionists AND typists that are good OR excellent So, I would expect to get back only anne,sandy and dana. And my sql would look like: select * from table where field2 = typist or field2 = reception and field3 = good or field3 = excellent. Hope this helps you to help me ;) On Fri, 2002-09-13 at 00:27, Norris, Joseph wrote: I would write this in this way: select * from table where (field = x and ($field = y or field = z)) or (field = x and (field = a or field = b or field = c)); assuming that you want a record with x in field having either y or z in field or a or b o c in field HATH -Original Message- From: Petre Agenbag [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 12, 2002 2:49 PM To: [EMAIL PROTECTED] Subject: and or in query Hi I would like to issue a logical query as follow: select * from table where field1 = x and ((field2 = y or field2 = z) or (field3 = a or field3 = b or field3 = c)); But this syntax is not returning what I expect. Plz help me with syntax - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
and or in query
Hi I would like to issue a logical query as follow: select * from table where field1 = x and ((field2 = y or field2 = z) or (field3 = a or field3 = b or field3 = c)); But this syntax is not returning what I expect. Plz help me with syntax - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Beta training program
Can someone plz send me the posting from mysqlab concerning the beta training , I deleted it accidentally... Thanks sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MAX vs plain vanilla mysql
Thanks, So, if you install MySQL MAX, you can use either myisam OR innodb / bdb tables, ie, if I install ONE mysql max server, then different users on the server can use different types of tables(innodb/myisam) as they choose?, also, if you create one db, can you have different types of tables in one db? Sorry if I'm asking stupid questions, just want to know what is possible and what not, was thinking the other day if it wouldn't be etter if one had different types of tables in one relation db ; myisam types for the normal static data, and innodb or bdb tables for the tables that need to commit/rollback. Heikki Tuuri wrote: Hi! I have to correct: if you put [mysqld] skip-innodb skip-bdb to your my.cnf, then -Max uses only about 1.5 MB more memory than plain MySQL, because of a larger executable. MyISAM tables in -Max are as reliable as in plain MySQL. It is the same source code. Best regards, Heikki Innobase Oy ... Petre, Monday, July 01, 2002, 9:26:49 AM, you wrote: PA Probably a stupid question, but which is best to have on ones server, PA the old mysql, or max. I.O.W, if you install MAX and never use innodb PA or berkeley tables, will it be equivalent to a normal mysql installation PA wrt performance and stability? PA I'm asking from an ISP's point of view, if that makes any difference ( PA obviously if from developer it will depend on the needs of the app..) MySQL-Max binary is larger. It takes a lot more memory and it is useless without usage of InnoDB and/or BDB. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MAX vs plain vanilla mysql
Thanks, So, just to be 100% sure, the BEST to do is to install max, as I can't see any reason why NOT to...? No performance issues when used as a plain vanilla model, no stability and no security issues either, only down side to it is that it is a tad bit larger ? PS, Oh, and why do I always get a returned mail when posting to this list, yet my message still seems to reach it? Thanks Petre Paul DuBois wrote: At 19:31 +0200 7/2/02, Petre Agenbag wrote: Thanks, So, if you install MySQL MAX, you can use either myisam OR innodb / bdb tables, ie, if I install ONE mysql max server, then different users on the server can use different types of tables(innodb/myisam) as they choose?, also, if you create one db, can you have different types of tables in one db? Sorry if I'm asking stupid questions, just want to know what is possible and what not, was thinking the other day if it wouldn't be etter if one had different types of tables in one relation db ; myisam types for the normal static data, and innodb or bdb tables for the tables that need to commit/rollback. Yes, yes, yes, and yes. :-) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mysql vs MySQL max
Hi Probably a stupid question, but which is best to have on ones server, the old mysql, or max. I.O.W, if you install MAX and never use innodb or berkeley tables, will it be equivalent to a normal mysql installation wrt performance and stability? I'm asking from an ISP's point of view, if that makes any difference ( obviously if from developer it will depend on the needs of the app..) Thanks sql, mysql, query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Thank you!I need your help!
I think the problem is that the mysql package is still tarred. You need to
tar -xvf mysqlx.tar
This will extract the archive into a new folder, and from within that
folder you should run the commands as listed in your original mail
(./configure .)
So in short, the commands you are trying to use *appears* to be correct,
you just need to unpack the files before issuing the commands.
Your situation is similar to having a mysql package that is winzipped,
you first need to unzip it.
Jocelyn Fournier wrote:
Hi,
checking for working aclocal... missing
checking for working autoconf... missing
checking for working automake... missing
checking for working autoheader... missing
checking for working makeinfo... missing
You need to install automake and autoconf before trying to compile MySQL.
(texinfo is optional)
Regards,
Jocelyn Fournier
- Original Message -
From: ÕÅÉƽð [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, June 24, 2002 12:24 PM
Subject: Thank you!I need your help!
please tell what mean the follow:
[root@guanqun zsj]# cd /usr/local/src
[root@guanqun src]# cd mysql-3.23.44.tar/
[root@guanqun mysql-3.23.44.tar]# ./configure --prefix=/usr/local/mysql
bash: ./configure: No such file or directory
[root@guanqun mysql-3.23.44.tar]# ls
mysql-3.23.44 mysql-3.23.44.tar
[root@guanqun mysql-3.23.44.tar]# cd mysql-3.23.44
[root@guanqun mysql-3.23.44]# ./configure --prefix=/usr/local/mysql
loading cache ./config.cache
checking host system type... i686-pc-linux-gnu
checking target system type... i686-pc-linux-gnu
checking build system type... i686-pc-linux-gnu
checking for a BSD compatible install... (cached) /usr/bin/install -c
checking whether build environment is sane... yes
checking whether make sets ${MAKE}... (cached) yes
checking for working aclocal... missing
checking for working autoconf... missing
checking for working automake... missing
checking for working autoheader... missing
checking for working makeinfo... missing
checking whether to enable maintainer-specific portions of Makefiles... no
checking whether build environment is sane... yes
checking whether make sets ${MAKE}... (cached) yes
checking for gawk... (cached) gawk
checking for gcc... (cached) gcc
checking whether the C compiler (gcc ) works... yes
checking whether the C compiler (gcc ) is a cross-compiler... no
checking whether we are using GNU C... (cached) yes
checking whether gcc accepts -g... (cached) yes
checking for c++... (cached) c++
checking whether the C++ compiler (c++ ) works... yes
checking whether the C++ compiler (c++ ) is a cross-compiler... no
checking whether we are using GNU C++... (cached) yes
checking whether c++ accepts -g... (cached) yes
checking how to run the C preprocessor... (cached) gcc -E
checking for ranlib... (cached) ranlib
checking for ld used by GCC... (cached) /usr/bin/ld
checking if the linker (/usr/bin/ld) is GNU ld... (cached) yes
checking for BSD-compatible nm... (cached) /usr/bin/nm -B
checking whether ln -s works... (cached) yes
loading cache ./config.cache within ltconfig
checking for object suffix... o
checking for executable suffix... (cached) no
checking for gcc option to produce PIC... -fPIC
checking if gcc PIC flag -fPIC works... yes
checking if gcc supports -c -o file.o... yes
checking if gcc supports -c -o file.lo... yes
checking if gcc supports -fno-rtti -fno-exceptions ... yes
checking if gcc static flag -static works... -static
checking if the linker (/usr/bin/ld) is GNU ld... yes
checking whether the linker (/usr/bin/ld) supports shared libraries... yes
checking command to parse /usr/bin/nm -B output... ok
checking how to hardcode library paths into programs... immediate
checking for /usr/bin/ld option to reload object files... -r
checking dynamic linker characteristics... Linux ld.so
checking if libtool supports shared libraries... yes
checking whether to build shared libraries... yes
checking whether to build static libraries... yes
checking for objdir... .libs
creating libtool
loading cache ./config.cache
checking for a BSD compatible install... /usr/bin/install -c
checking for bison... no
checking for byacc... no
checking for pdftex... no
checking return type of sprintf... int
checking for uname... /bin/uname
checking operating system... Linux
checking if we should use skip-locking as default for linux-gnu... yes
checking for ln... /bin/ln
checking for ln... /bin/ln
checking for mv... /bin/mv
checking for rm... /bin/rm
checking for cp... /bin/cp
checking for sed... /bin/sed
checking for cmp... /usr/bin/cmp
checking for chmod... /bin/chmod
checking for hostname... /bin/hostname
checking for perl... /usr/bin/perl
checking for ps... /bin/ps
checking how to check if pid exists... /bin/ps p $$PID | grep mysqld
/dev/null
checking for kill... /bin/kill
checking for kill switches... /bin/kill -0 $$PID /dev/null 2 /dev/null
checking for gcc option to accept ANSI C...
checking whether to use RAID... no
checking for getconf... getconf
checking for
Frontend and report tool
Hi Can anyone tell me if they know about a good frontend for mysql with reporting facilities, or alternatively only a report generator? Thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
insert problems
Are there any documented reasons why mysql would only enter certain fields and skip others? I am having some strange problems where it seems that data are randomly dropped from inserts via web forms. Have posted before stating a possible cache or proxy problem, but I am still at a loss... - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Proxy server problems
Hi I have recently had some problems with web forms not updating the website ( use PHP and the insert page should first enter the form data into a table and then update the status field in a second table correlating to the entry just made), hoever, only the first sql seemed to go through and the 2nd table never got the updated status field. It bothered me as when I test the system it worked 100%, yet some customers complained about the fact that their entries are not registered. Then I noticed that one of them had a proxy server, and it started me thinking that that could be the possible problem. So, my question is; can an outdated proxy wreak havoc with my mysql/php pages, and if so, is there a way that I can force a webpage NOT to go through a proxy? The solution will have to be software and server side based as I cannot rely on the cooperation of the end user for a solution. I know the solution probably has more to do with PHP, but I guess the PHP guru's will frequent this list as well??? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
