Didn't find any fields in table t0000
mysql I have a problem in using mysql in my server: Redhat 8. when i use the database ,i get the err message: mysql use db00010 Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Didn't find any fields in table 't00060' Didn't find any fields in table 't00061' Didn't find any fields in table 't00063' Didn't find any fields in table 't00064' Didn't find any fields in table 't00067' and when i select the t00060: mysql select * from t00060; ERROR 1033: Incorrect information in file: './db00010/t00060.frm' but the t00060.frm is really at the directory... I don't know why . And it happens often. In old times i copy backup file to overwrite it.(This way i lost many data) But today i have no backup files. Please help me .thanks! Best Regard! [EMAIL PROTECTED] 2004-03-11 NCIC, P.O.Box 2704,Beijing,100080 People's Republic of China -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie question on Comparing fields in 2 tables?
I'd just like to say thanks to both Michael and Rocar for both solutions. I will be giving them a go. I now need to buy a more comprehensive manual for MySQL, as neither of these commands are in the 2 books I currently have. Many thanks Ian -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 10, 2004 11:29 PM To: Ian Izzard Cc: Rocar Peças; [EMAIL PROTECTED] Subject: Re: Newbie question on Comparing fields in 2 tables? An equivalent, but slightly simpler, query would be SELECT sw.pcname, sw.product FROM software sw, keywords kw WHERE sw.product RLIKE kw.Searchname RLIKE is for regular expression pattern matching. Regular expressions, unlike LIKE patterns, don't have to match the whole string, so there is no need to paste '%' onto each end. For more, see http://www.mysql.com/doc/en/String_comparison_functions.html http://www.mysql.com/doc/en/Pattern_matching.html Michael Rocar Peças wrote: Mr. Izzard, We have these tables: Table software - pcname char(..) - product char(..) Table keywords - id int(..) - searchname char(...) and you want to pick out the pcname from the software table, where the product field contains the searchname from the keywords table Try this and you´ll succeed: == SELECT software.pcname, software.product FROM software, keywords WHERE software.product LIKE CONCAT(%, keywords.searchname, %) == Best wishes, Leandro M Neves, ROCAR PEÇAS LTD. Sete Lagoas/MG - Brazil - Original Message - From: Ian Izzard [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, March 10, 2004 2:05 PM Subject: Newbie question on Comparing fields in 2 tables? Hi, I am new to using MySQL and SQL queries. I have in the past only carried out simple queries. I am trying to write a query for use in our Helpdesk/Audit software. I want to compare the values in one table (Keywords) to the values found in another table (Software) so that I can get records of games that are installed on PCs. The Software table is created from an audit run on each PC. The keywords table is created by myself. The keywords table contains 2 fields, ID and Searchname. A sample of the data in this table would be: ID Searchname 1worm 2kazaa 3delta 4game The software table has 2 fields, pcname and product. A sample of the data in this table would be: pcname product SW0638CADS Support SW0638Citrix ICA Client SW0638Winzip SW0653Winzip SW0653Delta Force 2 SW0462Winzip SW0462Delta Force SW0462Worms 2000 SW0785Winzip SW0785Worms2 The software table has some 50,000 records in it. What I am looking to do is to pick out the pcname from the software table, where the product field contains the searchname from the keywords table. Something like: select pcname, product from software, keywords where product like searchname I would then expect the results to come out as: pcname product SW0653Delta Force 2 SW0462Delta Force SW0462Worms 2000 SW0785Worm2 I have tried using the LIKE command, but the manuals only show examples when comparing a field to a string, ie product LIKE 'worm%' As the keyword table is likely to get quite long (currently 163 records) I don't want to do a query using the LIKE command immediately above (, as the query will get very long and unmanageable. Can someone help with a solution to this? Can it be done in a single query? Is there a command that I just haven't yet found? If it is of any help, the version of MySQL being used 4.0.16. This version is installed by the Helpdesk/Audit software, and so cannot be upgraded. Your help is greatly appreciated. Ian Izzard Visit our web site at www.scottwilson.com Privilege and Confidentiality Notice. This e-mail and any attachments to it are intended only for the party to whom they are addressed. They may contain privileged and/or confidential information. If you have received this transmission in error, please notify the sender immediately and delete any digital copies and destroy any paper copies. Thank you. Scott Wilson Kirkpatrick Co Ltd Registered in London: No. 880328 Registered office: Scott House, Basing View, Basingstoke, Hampshire, RG21 4JG. UK. This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk Visit our web site at
How to minimize Master-Slave Traffic during replication?
Hi, we got a little problem with a master-slave replication (both running MySQL 4.0.18) eating up our complete bandwidth. The slave is connected via a 2MBit-SDSL-Line which is also used to connect our Office-PCs with the internet so I get complaints about the slow connection which is caused by the huge amount of master-slave traffic through this line. Most of the replication traffic is caused by tables, which are only created once, used for further selects and then deleted. The tables can't be created as temporary tables (that's what our programmers are telling me - I'm only the Admin and don't know whether thei're right or wrong) but are completely useless for replication. My first attempt to reduce traffic between master and slave was to set replicate-wild-ignore-table for the tables described above. Ok, after reading the MySQL-Manual several times I discovered that this was useless cause the insert, update and delete statements are transfered from master to slave before they are tested whether to be executed on the slave - it would be nice if this could be set in the master-config for every slave, so the useless sql-statements would'nt have to be transmitted. Now I want to test the slave_compressed_protocol=1 to reduce traffic (meanwhile I estimated that app. 90% of the traffic between master and slave is useless for replication). First I want to test it out with another replication-slave where I set the variable and restarted the slave. Unfortunately I don't see any slave_compressed_protocol-Variable when I do a mysqladmin variables so I don't know whether the variable is recognized or not. Does anyone use the slave_compressend_protocol - variable successfully and can tell me, where I can see, whether the slave recognized this switch and do I have to set this switch on the server too? Greetings Lutz Maibach -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Limiting Return
g'day, 've had a good look through the manual and archives but can't find an answer. I'm pulling, among other things, a field from the DB that holds description data (a few paragrahps of text). I want to limit it so that it only pulls the first 50 words (or characters if thats not possible) Cheers, Keith
Re: Limiting Return
Keith [EMAIL PROTECTED] wrote on 11/03/2004 09:50:35: 've had a good look through the manual and archives but can't find an answer. I'm pulling, among other things, a field from the DB that holds description data (a few paragrahps of text). I want to limit it so that it only pulls the first 50 words (or characters if thats not possible) I think characters is possible: use the function LEFT(string, chars). See also SUBSTRING(). If your texts is strictly space separated, SUBSTRING_INDEX(str,delim,count) may be able to select words for you with ' ' as the delim. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Limiting Return
look for SUBSTRING_INDEX(field,'\.',2) -Original Message- From: Keith [mailto:[EMAIL PROTECTED] Sent: Thursday, March 11, 2004 8:51 PM To: [EMAIL PROTECTED] Subject: Limiting Return g'day, 've had a good look through the manual and archives but can't find an answer. I'm pulling, among other things, a field from the DB that holds description data (a few paragrahps of text). I want to limit it so that it only pulls the first 50 words (or characters if thats not possible) Cheers, Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
https access to phpmyadmin - mysql
Hi, I was wondering if anyone could point me to info on setting up https access to the admin page on phpmyadmin? Thanks anyone. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Security
You've been perfectly clear. The MySQL permission system will not define this level of security. You must design you application so that it will only give access to the rows that pertain to the customer that is logged in. Create a MySQL user which can read and write to your database. Then create another table in your database which defines users and passwords (separate from the MySQL users). When a user logs in, you check their username and password against your user table, and then once they are logged in, you make sure the only rows they see or update are rows that pertain to them. I hope this makes things clear. On Wednesday 10 March 2004 05:39 pm, Mulugeta Maru wrote: Hi Mike, I am sorry for the confusion I might have caused. May be it would help to give a clear example. Table - Customers (CustomerID, CustomerName, Address, etc) Table - Transaction(TransactionID,CustomerID,Date,Amount) Note: CustomerID in Customer Table is a Primary Key. TransactionID is a Primary Key and CustomerID is a Foreign Key in Transaction Table). Question: How would I be able to give my customers access to the database so that they can update the customer table (for example address change) and add transactions to the transaction table. What I do not want to happen is that customer A is able to modify customer B's record. In short how would you restrict customer a to see transactions that pertain to him/her. Many thanks. -- Joshua J. Kugler Fairbanks, Alaska Computer Consultant--Systems Designer .--- --- ... ..- .--.- ..- --. .-.. . .-. [EMAIL PROTECTED] ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Serveral mysqld instances
Mr. Pendleton, Exactly. ps -aux | grep mysqld show several instances of the mysql_server running at the same time. By now, I have 69!. Thanks in advance. Leandro M Neves - Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: 'Rocar Peças ' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, March 10, 2004 7:39 PM Subject: RE: Serveral mysqld instances Are you looking at ps -aux | grep mysql (or some variant) to determine this? -Original Message- From: Rocar Peças To: [EMAIL PROTECTED] Sent: 3/10/04 4:19 PM Subject: Serveral mysqld instances Hi, Folks! I get a MySQL 4.0.18 server running on a Conectiva Linux Kernel 2.4.5. The MySQL server supports datum for a C apliccation which everybody in the company uses. The problem is that huge mysqld processes come up as people loads their programs, which connect to the mysql server. Each mysqld quickly gets 80MB large in RAM, and the number of mysqld usually reaches 80, so my 1GB RAM server gets out of memory early in the morning. What is happening? Isn´t mysqld supposed to have only one instance running on kernel? Thanks. Leandro M Neves, ROCAR PEÇAS LTD Sete Lagoas/MG - Brazil Rocar Peças Ltda. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: Performance problem with 4.0.18
VP Can you supply us with an example? Some explain plans to corroborate your VP reported slowness. Of course. Here is the query. It is big and ugly, I'm curently working on system optimization. But why the same query is good at one machine and bad at another? I've made some experiments and can say that the second machine was just without load, so results were better. But its performance is anyway much worse than MyISAM. We switched back to MyISAM, it works excellent. As I think, for using InnoDB succesfully , we should review our database scheme, and use only those queries which use indexes as much as possible and have WHERE clause which limits the result to small amount of records. Anyway, can somebody recommend how to make InnoDB performance close to MyISAM? Our tables have about 2,5 millions records. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimise SELECT ... LIMIT
Hi list, I'm trying to optimise a SELECT ... LIMIT query, perhaps i miss something important and so, i'm asking your help :) I'm using MySQL 4.0.15 under Linux. Here's a test query : mysql explain SELECT * FROM F4000 WHERE ReplyTo=8711465 ORDER BY Numero LIMIT 234599,20; +---+--+++-+---+ +-+ | table | type | possible_keys | key| key_len | ref | rows | Extra | +---+--+++-+---+ +-+ | F4000 | ref | ReplyTo_Numero | ReplyTo_Numero | 4 | const | 188063 | Using where | +---+--+++-+---+ +-+ And here's index description : mysql show index from F4000; | F4000 | 0 | PRIMARY| 1 | Numero | A | 2535091 | NULL | NULL | | BTREE | F4000 | 1 | ReplyTo_Numero | 1 | ReplyTo | A |NULL | NULL | NULL | | BTREE | F4000 | 1 | ReplyTo_Numero | 2 | Numero | A |NULL | NULL | NULL | | BTREE ReplyTo and Numero are both integer. This query took around 1 sec, could i made something to have better performance ? PS : I know that Richard Davey have post a question on limit, 2 two days ago, but i didn't find anything that can help me. I also look at http://www.mysql.com/doc/en/LIMIT_optimisation.html without any success. Thanks. David Any Idea ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie - dependencies
Having just set up a RedHat Enterprise Server V.3 I am trying to add a MySQL database function. I tried and failed using up2date (following instructions from RH), so downloaded the rpms and began to install by myself. My input and the output is shown below: # rpm -i mysql-server-3.23.58-1.i386.rpm error:Failed dependencies: libmysqlclient.so.10 is needed by mysql-server-3.23.58-1 Suggested resolutions: mysql-3.23.58-1.i386.rpm # rpm -i mysql-3.23.58-1.i386.rpm error:Failed dependencies: perl-DBD-MySQL is needed by mysql-3.23.58-1 Suggested resolutions: perl-DBD-MySQL-2.1021-3.i386.rpm # rpm -i perl-DBD-MySQL-2.1021-3.i386.rpm error:Failed dependencies: libmysqlclient.so.10 is needed by perl-DBD-MySQL-2.1021-3 Suggested resolutions: mysql-3.23.58-1.i386.rpm Which takes me back to the second line and begins a loop. Libmysqlclient.so.10 is in /usr/lib/mysql/ problably from the initial setup (before I found out that the mysql-server wasn't on the setup CDs). I went for RHEL on the strength that it has up2date and I would not meet this kind of problem. Anyone got any ideas/get out of jail cards? Robb -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie - dependencies
Having just set up a RedHat Enterprise Server V.3 I am trying to add a MySQL database function. I tried and failed using up2date (following instructions from RH), so downloaded the rpms and began to install by myself. My input and the output is shown below: # rpm -i mysql-server-3.23.58-1.i386.rpm error:Failed dependencies: libmysqlclient.so.10 is needed by mysql-server-3.23.58-1 Suggested resolutions: mysql-3.23.58-1.i386.rpm # rpm -i mysql-3.23.58-1.i386.rpm error:Failed dependencies: perl-DBD-MySQL is needed by mysql-3.23.58-1 Suggested resolutions: perl-DBD-MySQL-2.1021-3.i386.rpm # rpm -i perl-DBD-MySQL-2.1021-3.i386.rpm error:Failed dependencies: libmysqlclient.so.10 is needed by perl-DBD-MySQL-2.1021-3 Suggested resolutions: mysql-3.23.58-1.i386.rpm Which takes me back to the second line and begins a loop. Libmysqlclient.so.10 is in /usr/lib/mysql/ problably from the initial setup (before I found out that the mysql-server wasn't on the setup CDs). I went for RHEL on the strength that it has up2date and I would not meet this kind of problem. Anyone got any ideas/get out of jail cards? rpm -ivh perl-DBD-MySQL-2.1021-3.i386.rpm mysql-server-3.23.58-1.i386.rpm mysql-3.23.58-1.i386.rpm -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie - dependencies
Having just set up a RedHat Enterprise Server V.3 I am trying to add a MySQL database function. I tried and failed using up2date (following instructions from RH), so downloaded the rpms and began to install by myself. My input and the output is shown below: # rpm -i mysql-server-3.23.58-1.i386.rpm error:Failed dependencies: libmysqlclient.so.10 is needed by mysql-server-3.23.58-1 Suggested resolutions: mysql-3.23.58-1.i386.rpm # rpm -i mysql-3.23.58-1.i386.rpm error:Failed dependencies: perl-DBD-MySQL is needed by mysql-3.23.58-1 Suggested resolutions: perl-DBD-MySQL-2.1021-3.i386.rpm # rpm -i perl-DBD-MySQL-2.1021-3.i386.rpm error:Failed dependencies: libmysqlclient.so.10 is needed by perl-DBD-MySQL-2.1021-3 Suggested resolutions: mysql-3.23.58-1.i386.rpm Which takes me back to the second line and begins a loop. Libmysqlclient.so.10 is in /usr/lib/mysql/ problably from the initial setup (before I found out that the mysql-server wasn't on the setup CDs). I went for RHEL on the strength that it has up2date and I would not meet this kind of problem. Anyone got any ideas/get out of jail cards? rpm -ivh perl-DBD-MySQL-2.1021-3.i386.rpm mysql-server-3.23.58-1.i386.rpm mysql-3.23.58-1.i386.rpm -- Alex - Cheers for the reply Alex. Tried your method and got: MySQL-server conflicts with mysql-server-3.23.58-1 mysql conflicts with mysql-3.23.58-1 I tried 'locate' but could not find either file, so then tried 'rpm -e' of both. I got 'not installed' as an answer. So tried your line once more and got the same output. Robb - still in jail :-( -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie - dependencies
Having just set up a RedHat Enterprise Server V.3 I am trying to add a MySQL database function. I tried and failed using up2date (following instructions from RH), so downloaded the rpms and began to install by myself. My input and the output is shown below: # rpm -i mysql-server-3.23.58-1.i386.rpm error:Failed dependencies: libmysqlclient.so.10 is needed by mysql-server-3.23.58-1 Suggested resolutions: mysql-3.23.58-1.i386.rpm # rpm -i mysql-3.23.58-1.i386.rpm error:Failed dependencies: perl-DBD-MySQL is needed by mysql-3.23.58-1 Suggested resolutions: perl-DBD-MySQL-2.1021-3.i386.rpm # rpm -i perl-DBD-MySQL-2.1021-3.i386.rpm error:Failed dependencies: libmysqlclient.so.10 is needed by perl-DBD-MySQL-2.1021-3 Suggested resolutions: mysql-3.23.58-1.i386.rpm Which takes me back to the second line and begins a loop. Libmysqlclient.so.10 is in /usr/lib/mysql/ problably from the initial setup (before I found out that the mysql-server wasn't on the setup CDs). I went for RHEL on the strength that it has up2date and I would not meet this kind of problem. Anyone got any ideas/get out of jail cards? rpm -ivh perl-DBD-MySQL-2.1021-3.i386.rpm mysql-server-3.23.58-1.i386.rpm mysql-3.23.58-1.i386.rpm -- Alex - Cheers for the reply Alex. Tried your method and got: MySQL-server conflicts with mysql-server-3.23.58-1 mysql conflicts with mysql-3.23.58-1 You have a previous version of MySQL installed, from the mysql.com RPM's. To get rid of it, do: rpm -e 'MySQL*' first, then: rpm -ivh perl-DBD-MySQL-2.1021-3.i386.rpm mysql-server-3.23.58-1.i386.rpm mysql-3.23.58-1.i386.rpm I tried 'locate' but could not find either file, so then tried 'rpm -e' of both When you use rpm -e, you give it a package name (e.g. mysql-3.23.58-1.i386 or mysql) not a file name. -- Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie - dependencies
Hi! anyway, why dont you use the graphic install utility redhat uses to install mysql?? it will resolve the dependencies for you =) PS: i think it's called redhat-config-packages Best Regards! On Thu, 2004-03-11 at 09:05, Robert Ross wrote: Having just set up a RedHat Enterprise Server V.3 I am trying to add a MySQL database function. I tried and failed using up2date (following instructions from RH), so downloaded the rpms and began to install by myself. My input and the output is shown below: # rpm -i mysql-server-3.23.58-1.i386.rpm error:Failed dependencies: libmysqlclient.so.10 is needed by mysql-server-3.23.58-1 Suggested resolutions: mysql-3.23.58-1.i386.rpm # rpm -i mysql-3.23.58-1.i386.rpm error:Failed dependencies: perl-DBD-MySQL is needed by mysql-3.23.58-1 Suggested resolutions: perl-DBD-MySQL-2.1021-3.i386.rpm # rpm -i perl-DBD-MySQL-2.1021-3.i386.rpm error:Failed dependencies: libmysqlclient.so.10 is needed by perl-DBD-MySQL-2.1021-3 Suggested resolutions: mysql-3.23.58-1.i386.rpm Which takes me back to the second line and begins a loop. Libmysqlclient.so.10 is in /usr/lib/mysql/ problably from the initial setup (before I found out that the mysql-server wasn't on the setup CDs). I went for RHEL on the strength that it has up2date and I would not meet this kind of problem. Anyone got any ideas/get out of jail cards? rpm -ivh perl-DBD-MySQL-2.1021-3.i386.rpm mysql-server-3.23.58-1.i386.rpm mysql-3.23.58-1.i386.rpm -- Alex - Cheers for the reply Alex. Tried your method and got: MySQL-server conflicts with mysql-server-3.23.58-1 mysql conflicts with mysql-3.23.58-1 I tried 'locate' but could not find either file, so then tried 'rpm -e' of both. I got 'not installed' as an answer. So tried your line once more and got the same output. Robb - still in jail :-( -- |...| | _ _|Victor Medina M | |\ \ \| | _ \ / \ |Linux - Java - MySQL | | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | |/_/_/|_|_| /_/ \_\|ext. 325 - Tél: +58-241-8507325 | ||geek by nature - linux by choice | |...|
Query with IF acting wierd.
I have 3 tables: main(id int, type tinyint(1)) categories(id int, name varchar) items(id int, name varchar) I want to select the id and name. If type is 1 then I want to select the name from categories, if type is 0 I want to select the name from items, here is the query I'm trying to use: SELECT main.id, IF(main.type,categories.name,items.name), IF(main.type,cat,item) AS type FROM main,items,categories WHERE IF(main.type,categories.id,items.id)=main.id; This query gives me each row couple of times, can anyone tell me why? or can any one give me a better solution? My solution which I guess is not good is adding GROUP BY: SELECT main.id, IF(main.type,categories.name,items.name), IF(main.type,cat,item) AS type FROM main,items,categories WHERE IF(main.type,categories.id,items.id)=main.id GROUP BY id, type; Thanks, -Amir. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query with IF acting wierd.
SELECT distinct main.id, etc. etc. At 14:37 11-3-04, you wrote: I have 3 tables: main(id int, type tinyint(1)) categories(id int, name varchar) items(id int, name varchar) I want to select the id and name. If type is 1 then I want to select the name from categories, if type is 0 I want to select the name from items, here is the query I'm trying to use: SELECT main.id, IF(main.type,categories.name,items.name), IF(main.type,cat,item) AS type FROM main,items,categories WHERE IF(main.type,categories.id,items.id)=main.id; This query gives me each row couple of times, can anyone tell me why? or can any one give me a better solution? My solution which I guess is not good is adding GROUP BY: SELECT main.id, IF(main.type,categories.name,items.name), IF(main.type,cat,item) AS type FROM main,items,categories WHERE IF(main.type,categories.id,items.id)=main.id GROUP BY id, type; Thanks, -Amir. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie - dependencies
Hi Victor, I tried that initially and thought I had installed everything - RH Enterprise has the option in the GUI to install mysql - what it doesn't tell you is that it is the client and not the server. When you (eventually) find this out and try to install the server, problems occur because the server should be installed before the client (I think). So you then uninstall both server and client, then re-install the server. If sucessful, you still get problems when you try to re-install the client as the GUI cannot find the server's headers and so will not install client software. So you go and find the rpm and try to install it yourself and get the dependencies problem. I think you get an idea of my frustration, and I have not even got to the part of trying to use mysql yet! Still, I will continue on...:-) Thanks for your input. Robb -Original Message- From: Victor Medina [mailto:[EMAIL PROTECTED] Sent: 11 March 2004 13:31 To: Robert Ross Cc: [EMAIL PROTECTED] Subject: RE: Newbie - dependencies Hi! anyway, why dont you use the graphic install utility redhat uses to install mysql?? it will resolve the dependencies for you =) PS: i think it's called redhat-config-packages Best Regards! On Thu, 2004-03-11 at 09:05, Robert Ross wrote: Having just set up a RedHat Enterprise Server V.3 I am trying to add a MySQL database function. I tried and failed using up2date (following instructions from RH), so downloaded the rpms and began to install by myself. My input and the output is shown below: # rpm -i mysql-server-3.23.58-1.i386.rpm error:Failed dependencies: libmysqlclient.so.10 is needed by mysql-server-3.23.58-1 Suggested resolutions: mysql-3.23.58-1.i386.rpm # rpm -i mysql-3.23.58-1.i386.rpm error:Failed dependencies: perl-DBD-MySQL is needed by mysql-3.23.58-1 Suggested resolutions: perl-DBD-MySQL-2.1021-3.i386.rpm # rpm -i perl-DBD-MySQL-2.1021-3.i386.rpm error:Failed dependencies: libmysqlclient.so.10 is needed by perl-DBD-MySQL-2.1021-3 Suggested resolutions: mysql-3.23.58-1.i386.rpm Which takes me back to the second line and begins a loop. Libmysqlclient.so.10 is in /usr/lib/mysql/ problably from the initial setup (before I found out that the mysql-server wasn't on the setup CDs). I went for RHEL on the strength that it has up2date and I would not meet this kind of problem. Anyone got any ideas/get out of jail cards? rpm -ivh perl-DBD-MySQL-2.1021-3.i386.rpm mysql-server-3.23.58-1.i386.rpm mysql-3.23.58-1.i386.rpm -- Alex - Cheers for the reply Alex. Tried your method and got: MySQL-server conflicts with mysql-server-3.23.58-1 mysql conflicts with mysql-3.23.58-1 I tried 'locate' but could not find either file, so then tried 'rpm -e' of both. I got 'not installed' as an answer. So tried your line once more and got the same output. Robb - still in jail :-( -- |...| | _ _|Victor Medina M | |\ \ \| | _ \ / \ |Linux - Java - MySQL | | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | |/_/_/|_|_| /_/ \_\|ext. 325 - Tél: +58-241-8507325 | ||geek by nature - linux by choice | |...| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query with IF acting wierd.
Thanks, but this is just a nicer way to apply my second solution, I'm looking for a more efficient solution (and if someone can than for an explanation - why are the results getting duplicated?) -Amir. On Thursday 11 March 2004 15:40, Hans van Dalen wrote: SELECT distinct main.id, etc. etc. At 14:37 11-3-04, you wrote: I have 3 tables: main(id int, type tinyint(1)) categories(id int, name varchar) items(id int, name varchar) I want to select the id and name. If type is 1 then I want to select the name from categories, if type is 0 I want to select the name from items, here is the query I'm trying to use: SELECT main.id, IF(main.type,categories.name,items.name), IF(main.type,cat,item) AS type FROM main,items,categories WHERE IF(main.type,categories.id,items.id)=main.id; This query gives me each row couple of times, can anyone tell me why? or can any one give me a better solution? My solution which I guess is not good is adding GROUP BY: SELECT main.id, IF(main.type,categories.name,items.name), IF(main.type,cat,item) AS type FROM main,items,categories WHERE IF(main.type,categories.id,items.id)=main.id GROUP BY id, type; Thanks, -Amir. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query with IF acting wierd.
Amir, I don't know your table content but if you join two tables eg : table1(id, desc) and table2(id, refid, desc) wich containts: table1: 1 test1 2 test2 table2: 1 1 testbla 2 1 testbla and you select: select id from table1 where table1.id = table2.refid you got two rows (because if you show * it would be: 1 test1 1 1 testbla and 1 test1 2 1 testbla So distinct is the keyword for getting only the unique records. Distinct is in MYSQL very effective (see the user manual for : How MySQL Optimizes `DISTINCT'). regards At 15:02 11-3-04, you wrote: Thanks, but this is just a nicer way to apply my second solution, I'm looking for a more efficient solution (and if someone can than for an explanation - why are the results getting duplicated?) -Amir. On Thursday 11 March 2004 15:40, Hans van Dalen wrote: SELECT distinct main.id, etc. etc. At 14:37 11-3-04, you wrote: I have 3 tables: main(id int, type tinyint(1)) categories(id int, name varchar) items(id int, name varchar) I want to select the id and name. If type is 1 then I want to select the name from categories, if type is 0 I want to select the name from items, here is the query I'm trying to use: SELECT main.id, IF(main.type,categories.name,items.name), IF(main.type,cat,item) AS type FROM main,items,categories WHERE IF(main.type,categories.id,items.id)=main.id; This query gives me each row couple of times, can anyone tell me why? or can any one give me a better solution? My solution which I guess is not good is adding GROUP BY: SELECT main.id, IF(main.type,categories.name,items.name), IF(main.type,cat,item) AS type FROM main,items,categories WHERE IF(main.type,categories.id,items.id)=main.id GROUP BY id, type; Thanks, -Amir. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JDBC timeout after 4.0.8 - 4.0.18 upgrade?
With respect to this problem, I am not running on Windows, but Redhat, and seeing this problem often. Which part of: http://www.mysql.com/documentation/connector-j/index.html#id2803835 should i be looking at? thanks alan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best practise roll-backs?
Hi, I have a PHP script that is running 4 queries. If the 4th fails, ideally I would like the other 3 queires to roll back and leave the database as if they were never run at all. Can anyone advise on any technique that would do this easily, as opposed to manually writing and running queries to set the database back (which would be messy IMO). This project is being developed on MySQL 3.23.37. Any help is appreciated, thanks. Andy Hall.
RE: Privilege to single database being revoked occasionally
Is it the same database, same user? Does it follow any maintenance or large import? -Original Message- From: Terence To: [EMAIL PROTECTED] Sent: 3/11/04 12:25 AM Subject: Privilege to single database being revoked occasionally Dear Lists, We moved to 4.1.0 when it was first launched, and twice a month or so, we get access denied commands to one particular database for one particular user. The privileges are correctly set, and the only way to restore access for the user, is to revoke and re-apply the privileges and all works fine again. I am the only user with permission to change privileges. I am just wondering if anyone else has stumbled across this problem in 4.1.0? It doesnt seem to have been fixed in version 4.1.1 according to: http://www.mysql.com/doc/en/News-4.1.1.html If there's any data I can provide for debugging purposes, please let me know. Thanks Terence -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Serveral mysqld instances
This display has been fixed in Red Hat 9.0. If you look at the list, the children processes are not consuming memory, since the memory is shared. -Original Message- From: Rocar Peças To: Victor Pendleton; [EMAIL PROTECTED] Sent: 3/11/04 5:54 AM Subject: Re: Serveral mysqld instances Mr. Pendleton, Exactly. ps -aux | grep mysqld show several instances of the mysql_server running at the same time. By now, I have 69!. Thanks in advance. Leandro M Neves - Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: 'Rocar Peças ' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, March 10, 2004 7:39 PM Subject: RE: Serveral mysqld instances Are you looking at ps -aux | grep mysql (or some variant) to determine this? -Original Message- From: Rocar Peças To: [EMAIL PROTECTED] Sent: 3/10/04 4:19 PM Subject: Serveral mysqld instances Hi, Folks! I get a MySQL 4.0.18 server running on a Conectiva Linux Kernel 2.4.5. The MySQL server supports datum for a C apliccation which everybody in the company uses. The problem is that huge mysqld processes come up as people loads their programs, which connect to the mysql server. Each mysqld quickly gets 80MB large in RAM, and the number of mysqld usually reaches 80, so my 1GB RAM server gets out of memory early in the morning. What is happening? Isn´t mysqld supposed to have only one instance running on kernel? Thanks. Leandro M Neves, ROCAR PEÇAS LTD Sete Lagoas/MG - Brazil Rocar Peças Ltda. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Saving file into database
Use the BLOB, Luke! See your local MySQL manual for details. We're using BLOBs to store PDF in our database, and through the use of HTTP headers, we're able to let user download the PDFs without having to store a local copy on disk, directly from the database (content-disposition header). Hi Kurt, I have been using MySQL to store links to PDFs which live in other directories. Is there an advantage to storing the PDFs directly into the database? -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Best practise roll-backs?
Any reason you can use a version of MySQL that contains transaction aware tables? -Original Message- From: Andy Hall To: [EMAIL PROTECTED] Sent: 3/11/04 8:29 AM Subject: Best practise roll-backs? Hi, I have a PHP script that is running 4 queries. If the 4th fails, ideally I would like the other 3 queires to roll back and leave the database as if they were never run at all. Can anyone advise on any technique that would do this easily, as opposed to manually writing and running queries to set the database back (which would be messy IMO). This project is being developed on MySQL 3.23.37. Any help is appreciated, thanks. Andy Hall. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Didn't find any fields in table t0000
??? [EMAIL PROTECTED] wrote: I have a problem in using mysql in my server: Redhat 8. when i use the database ,i get the err message: mysql use db00010 Reading table information for completion of table and column= names You can turn off this feature to get a quicker startup with -A Didn't find any fields in table 't00060' Didn't find any fields in table 't00061' Didn't find any fields in table 't00063' Didn't find any fields in table 't00064' Didn't find any fields in table 't00067' and when i select the t00060: mysql select * from t00060; ERROR 1033: Incorrect information in file:= './db00010/t00060.frm' but the t00060.frm is really at the directory... What is the type of the above tables? InnoDB? I don't know why . And it happens often. In old times i copy= backup file to overwrite it.(This way i lost many data) But today i have no backup files. -- 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]
RE: Saving file into database
Is there an advantage to storing the PDFs directly into the database? I'm also curious how large this would make a database. Is there any space saved through this method, or would they still be the same size as the original PDF? - Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie - dependencies
OK, a few things you can try 1.- I usually compile everything my server uses. I recomended you to use the gui just because i tought rh installer would resolve the dependencies by itself, big mistake here =) RH seems to prefer PostgreSQL and considers MySQL a second hand choice =( S, I can help you out compiling a fresh MySQL server or... 2.- Download a precompiled binaries from the mysql.com site, read the install instrucctions (quite easy by the way, we can help on that also!) and use mysql's own binaries Any choice is good and valid, make a choice and let us know if you have problems with it =) Sure we can help! =) Best Regards! On Thu, 2004-03-11 at 09:41, Robert Ross wrote: Hi Victor, I tried that initially and thought I had installed everything - RH Enterprise has the option in the GUI to install mysql - what it doesn't tell you is that it is the client and not the server. When you (eventually) find this out and try to install the server, problems occur because the server should be installed before the client (I think). So you then uninstall both server and client, then re-install the server. If sucessful, you still get problems when you try to re-install the client as the GUI cannot find the server's headers and so will not install client software. So you go and find the rpm and try to install it yourself and get the dependencies problem. I think you get an idea of my frustration, and I have not even got to the part of trying to use mysql yet! Still, I will continue on...:-) Thanks for your input. Robb -Original Message- From: Victor Medina [mailto:[EMAIL PROTECTED] Sent: 11 March 2004 13:31 To: Robert Ross Cc: [EMAIL PROTECTED] Subject: RE: Newbie - dependencies Hi! anyway, why dont you use the graphic install utility redhat uses to install mysql?? it will resolve the dependencies for you =) PS: i think it's called redhat-config-packages Best Regards! On Thu, 2004-03-11 at 09:05, Robert Ross wrote: Having just set up a RedHat Enterprise Server V.3 I am trying to add a MySQL database function. I tried and failed using up2date (following instructions from RH), so downloaded the rpms and began to install by myself. My input and the output is shown below: # rpm -i mysql-server-3.23.58-1.i386.rpm error:Failed dependencies: libmysqlclient.so.10 is needed by mysql-server-3.23.58-1 Suggested resolutions: mysql-3.23.58-1.i386.rpm # rpm -i mysql-3.23.58-1.i386.rpm error:Failed dependencies: perl-DBD-MySQL is needed by mysql-3.23.58-1 Suggested resolutions: perl-DBD-MySQL-2.1021-3.i386.rpm # rpm -i perl-DBD-MySQL-2.1021-3.i386.rpm error:Failed dependencies: libmysqlclient.so.10 is needed by perl-DBD-MySQL-2.1021-3 Suggested resolutions: mysql-3.23.58-1.i386.rpm Which takes me back to the second line and begins a loop. Libmysqlclient.so.10 is in /usr/lib/mysql/ problably from the initial setup (before I found out that the mysql-server wasn't on the setup CDs). I went for RHEL on the strength that it has up2date and I would not meet this kind of problem. Anyone got any ideas/get out of jail cards? rpm -ivh perl-DBD-MySQL-2.1021-3.i386.rpm mysql-server-3.23.58-1.i386.rpm mysql-3.23.58-1.i386.rpm -- Alex - Cheers for the reply Alex. Tried your method and got: MySQL-server conflicts with mysql-server-3.23.58-1 mysql conflicts with mysql-3.23.58-1 I tried 'locate' but could not find either file, so then tried 'rpm -e' of both. I got 'not installed' as an answer. So tried your line once more and got the same output. Robb - still in jail :-( -- |...| | _ _|Victor Medina M | |\ \ \| | _ \ / \ |Linux - Java - MySQL | | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | |/_/_/|_|_| /_/ \_\|ext. 325 - Tél: +58-241-8507325 | ||geek by nature - linux by choice | |...| -- |...| | _ _|Victor Medina M | |\ \ \| | _ \ / \ |Linux - Java - MySQL | | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | |/_/_/|_|_| /_/ \_\|ext. 325 - Tél: +58-241-8507325 | ||geek by nature - linux by choice | |...| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL
RE: Security
From: Mulugeta Maru [mailto:[EMAIL PROTECTED] Hi Mike, I am sorry for the confusion I might have caused. May be it would help to give a clear example. Table - Customers (CustomerID, CustomerName, Address, etc) Table - Transaction(TransactionID,CustomerID,Date,Amount) Note: CustomerID in Customer Table is a Primary Key. TransactionID is a Primary Key and CustomerID is a Foreign Key in Transaction Table). Question: How would I be able to give my customers access to the database so that they can update the customer table (for example address change) and add transactions to the transaction table. What I do not want to happen is that customer A is able to modify customer B's record. In short how would you restrict customer a to see transactions that pertain to him/her. As Paul DuBois said earlier, this is something you want to control in your application itself. You still haven't specified if you're actually using an application in this scenario, so I'm still assuming you're talking about giving the clients access to the native mysql client. Just as your bank gives you a web or executable client with which to access your records and transactions, they don't give you access to the database itself. One example I'm talking about is developing an application for the clients in PHP. It would take basic login information and from there keep track of what client it is. At that point, you have the CustomerID, so only display to them info pertinent to them (SELECT * FROM Customers WHERE CustomerID='$CustomerID'; SELECT * FROM Transaction WHERE CustomerID='$CustomerID'), thus only allowing them to update or view records /through the web app/ relating to them. So long as you never select records for Customer B, Customer A will never have the ability to view or modify Customer B's records. Does that make any more sense? -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie - dependencies
You have a previous version of MySQL installed, from the mysql.com RPM's. To get rid of it, do: rpm -e 'MySQL*' first, then: rpm -ivh perl-DBD-MySQL-2.1021-3.i386.rpm mysql-server-3.23.58-1.i386.rpm mysql-3.23.58-1.i386.rpm I tried 'locate' but could not find either file, so then tried 'rpm -e' of both When you use rpm -e, you give it a package name (e.g. mysql-3.23.58-1.i386 or mysql) not a file name. -- Alex -- Genius mate, It worked! I had tried the whole package name when I used '-e' but it did not seem to make much difference. However, using your wildcard idea seems to have worked. A pause then the command line again. Put it your multi rpm line and it kicked in and loaded beautifully. Seems to be working, so now I can get horribly confused with setting up the database. Steep learning curve, but really interesting. Thanks again for you help. Robb (I'm free:-)) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Saving file into database
Erich Beyrent wrote: Use the BLOB, Luke! See your local MySQL manual for details. We're using BLOBs to store PDF in our database, and through the use of HTTP headers, we're able to let user download the PDFs without having to store a local copy on disk, directly from the database (content-disposition header). Hi Kurt, I have been using MySQL to store links to PDFs which live in other directories. Is there an advantage to storing the PDFs directly into the database? -Erich- Not all PDF's are viewable for all users. First it is determined whether the user can be granted access to the file, before he's presented with a hyperlink. This authorization is stored in the database, thus our web app can access and present the PDF using the same protocol it used for authentication/authorization. It can even use the same open database connection. No need to deal with other protocols and protection/authorization of those files on file system- and web server-level: if you're not authorized, you can't access them in any way. Backoffice maintenance and content management can be done with only a single database connection. In our case, this resulted in much simpler and safer code. Other advantages I can think of are, amongst other, master/slave replication of the database, and no required needs to tune your database server for anything else than MySQL activity. Disadvantages which come to mind are a much larger database, which is often more difficult to maintain than large file systems. To some people, at least. Just ideas, food for thought. Regards, Kurt.
RE: Query with IF acting wierd.
From: Amir Hardon [mailto:[EMAIL PROTECTED] I have 3 tables: main(id int, type tinyint(1)) categories(id int, name varchar) items(id int, name varchar) I want to select the id and name. If type is 1 then I want to select the name from categories, if type is 0 I want to select the name from items, here is the query I'm trying to use: SELECT main.id, IF(main.type,categories.name,items.name), IF(main.type,cat,item) AS type FROM main,items,categories WHERE IF(main.type,categories.id,items.id)=main.id; This query gives me each row couple of times, can anyone tell me why? or can any one give me a better solution? The problem is that in either case of the first IF, you're still joining on 3 tables when you really want two. If main.type is a category and the WHERE clause ends up evaluating to `WHERE categories.id=main.id' you're still joining on the items table unnecessarily, and vice-versa. Without another WHERE clause to limit it, you're getting the full cartesian product from the first two tables against the third. I don't think this works, but it's essentially what you want: SELECT main.id, IF(main.type,categories.name,items.name), IF(main.type,cat,item) AS type FROM main, IF(main.type,items,categories) WHERE IF(main.type,categories.id,items.id)=main.id; Note the extra IF in there to join only on /either/ items or categories. I never knew you could use an IF clause to specify a column in a WHERE clause, though, so maybe this will work. It's worh a shot at least. HTH! -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Saving file into database
Eve Atley wrote: Is there an advantage to storing the PDFs directly into the database? I'm also curious how large this would make a database. Is there any space saved through this method, or would they still be the same size as the original PDF? - Eve There's a percentage of disk space lost to database overhead (headers and such). Fragmentation, internal and external, is comparable to storage at the file-system level. I don't have exact figures what this would mean to MySQL, since our app currently runs on Oracle. Easy enough to test this in lab environments, though. Regards, Kurt
Re: Re: Message
Here is the file. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Query with IF acting wierd.
From: Mike Johnson SELECT main.id, IF(main.type,categories.name,items.name), IF(main.type,cat,item) AS type FROM main, IF(main.type,items,categories) WHERE IF(main.type,categories.id,items.id)=main.id; Oh, my mistake. I just realized I reversed items and categories in the IF clause. Try this instead: SELECT main.id, IF(main.type,categories.name,items.name), IF(main.type,cat,item) AS type FROM main, IF(main.type,categories,items) WHERE IF(main.type,categories.id,items.id)=main.id; Sorry! Actually, now I'm really curious if this works or not. Let me know how it turns out. -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select and Limit
I am trying to split the results from a select query across several pages. I have a contacts database and if someone searches for a contact who has a name like %mith% I want it to return the results with a page across the bottom, like google. How do I do this with mySQL and ASP or PHP? I understand that I could use LIMIT, but has anyone got any working example of this? Thanks Keith Wilson www.giraffedog.net --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.618 / Virus Database: 397 - Release Date: 09/03/2004 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Serveral mysqld instances
Rocar Pe?as [EMAIL PROTECTED] wrote: I get a MySQL 4.0.18 server running on a Conectiva Linux Kernel 2.4.5. The MySQL server supports datum for a C apliccation which everybody in the company uses. The problem is that huge mysqld processes come up as people loads their programs, which connect to the mysql server. Each mysqld quickly gets 80MB large in RAM, and the number of mysqld usually reaches 80, so my 1GB RAM server gets out of memory early in = the morning. What is happening? Isn=B4t mysqld supposed to have only one instance running on kernel? It's not several instance of mysqld, ps shows threads as processes. -- 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]
Perfomance issues
Hi folks: I would like to know if there are a perfomance loss using PEAR DB comparing with PHP native Mysql functions, and if this is a significant issue. Also I´m wondering if using persistent connections in PHP could lead to have many connections at the same time slowing the server, and how to handle this. Greetings from Costa Rica, Pura Vida!!! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select and Limit
Keith Wilson (www.giraffedog.net) [EMAIL PROTECTED] wrote: I am trying to split the results from a select query across several pages. I have a contacts database and if someone searches for a contact who has a name like %mith% I want it to return the results with a page across the bottom, like google. How do I do this with mySQL and ASP or PHP? I understand that I could use LIMIT, but has anyone got any working example of this? Yes, use LIMIT clause to get only certain number of rows. For example, to retrieve forst 10 rows use the following statement: SELECT .. FROM .. ORDER BY .. LIMIT 0,10; Then to retrieve next 10 rows: SELECT .. FROM .. ORDER BY .. LIMIT 10,10; retrieve rows 11-20 -- 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]
Replication - separate binary logs
Hello (first post) I'm a noob to this group but I have an issue which I am hoping there is a solution to. I would like to have one instance of mysqld serving many databases, and acting as a master server. Each slave will only replicate one database, and it would be undesirable for them to be able to access separate databases. I thought the built in replication tool was exactly what was required, with separate users for each DB. But I came across a snag. Even though each slave has access to only one database, the replication log they retrieve from the master contains sql statements pertaining to the other databases, from which they can gather information I would rather they not see. So my question is - can one instance of mysqld maintain multiple binary logs, specific to each database, or would I need separate instances of mysqld for each database? TIA Ron Heywood -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Saving file into database
I store any kind of files, PDF/word/etc.. I just like not having lots of directories with 1000's of files each in them... Seems more organized to me.. On Thu, 11 Mar 2004, Erich Beyrent wrote: Use the BLOB, Luke! See your local MySQL manual for details. We're using BLOBs to store PDF in our database, and through the use of HTTP headers, we're able to let user download the PDFs without having to store a local copy on disk, directly from the database (content-disposition header). Hi Kurt, I have been using MySQL to store links to PDFs which live in other directories. Is there an advantage to storing the PDFs directly into the database? -Erich- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Find Missing Sequence Numbers
All - I've got a table that has an unsigned int that stores increasing sequence numbers that are stored in UDP payloads. Occasionally, messages get lost and we'll have missing numbers in the sequence. These are not auto_incrementing columns, the sequence numbers are assigned by the application that generates the network traffic. What's the easiest way to identify gaps in the sequence numbers? For example, sequence numbers might look like the following: 100, 101, 102, 112, ... How can I easily detect the gaps? Thanks, Mark Mark Riehl Agile Communications, Inc. Email: [EMAIL PROTECTED] Office: (732) 528-9305 Fax: (732) 528-9308 Cell: (732) 996-7630
RE: Saving file into database
It does make the database larger.. as far as overhead... As you can't just store the file as a blob.. You'll need some referencing data in order to find it, and restore it back out of the database.. I just checked out my database (100's of files) which has: Total file size: 1765.34MB Mysql files are: -rw-r-1 mysqlmysql 23216 Feb 27 03:49 file.MYD -rw-r-1 mysqlmysql 10240 Feb 28 03:50 file.MYI -rw-r-1 mysqlmysql8756 Feb 23 2003 file.frm -rw-r-1 mysqlmysql1808037152 Feb 27 03:53 filedata.MYD -rw-r-1 mysqlmysql 400384 Feb 28 03:50 filedata.MYI -rw-r-1 mysqlmysql8614 Feb 23 2003 filedata.frm So it's not too bad as far as overhead.. On Thu, 11 Mar 2004, Eve Atley wrote: Is there an advantage to storing the PDFs directly into the database? I'm also curious how large this would make a database. Is there any space saved through this method, or would they still be the same size as the original PDF? - Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication - separate binary logs
If you are wanting each database to replicate only certain databases then you should look over the replication commands. http://www.mysql.com/doc/en/Replication_Options.html --replicate-do-db=database_name might be able to solve your issue. -Original Message- From: ron To: [EMAIL PROTECTED] Sent: 3/11/04 10:39 AM Subject: Replication - separate binary logs Hello (first post) I'm a noob to this group but I have an issue which I am hoping there is a solution to. I would like to have one instance of mysqld serving many databases, and acting as a master server. Each slave will only replicate one database, and it would be undesirable for them to be able to access separate databases. I thought the built in replication tool was exactly what was required, with separate users for each DB. But I came across a snag. Even though each slave has access to only one database, the replication log they retrieve from the master contains sql statements pertaining to the other databases, from which they can gather information I would rather they not see. So my question is - can one instance of mysqld maintain multiple binary logs, specific to each database, or would I need separate instances of mysqld for each database? TIA Ron Heywood -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
auto_increment id
Hi, I have a question about auto_increment: I have 2 tables, on of them holds my online transactions, the other one the offline transactions. Every transaction is created in the first table (call it t1) and then moved to t2. The id is generated using auto_increment. My problem is that the id's should be unique across both tables. If I dont restart the server this works fine, but if I restart it, mySQL assigns the lowest number for the new row in t1, but this id exits already in t2 which results in an error if I try to move from t1 to t2. I hope I explained the problem in a appropriate way. How can I tell mySQL to hold the auto_increment counter for Table t1 persistent ? BTW I am using JBoss to access the DB. Thanks a lot in advance, Regards Stefan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Find Missing Sequence Numbers
I don't think you can get a query which will return a row for each missing result, because a query can only return data that's there, not data that's missing. (although if you generated another table with all the numbers 1 through 1,000,000 that would work). Anyway, to get one result row per *gap* use: SELECT * FROM table LEFT JOIN table2 ON table2.id=(table.id+1) WHERE table2.id IS NULL; Tom. Mark Riehl wrote: All - I've got a table that has an unsigned int that stores increasing sequence numbers that are stored in UDP payloads. Occasionally, messages get lost and we'll have missing numbers in the sequence. These are not auto_incrementing columns, the sequence numbers are assigned by the application that generates the network traffic. What's the easiest way to identify gaps in the sequence numbers? For example, sequence numbers might look like the following: 100, 101, 102, 112, ... How can I easily detect the gaps? Thanks, Mark Mark Riehl Agile Communications, Inc. Email: [EMAIL PROTECTED] Office: (732) 528-9305 Fax: (732) 528-9308 Cell: (732) 996-7630 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best practise roll-backs?
If you have a reason not to use InnoDB tables (e.g., speed, licensing, fulltext indexes), you can implement pretty good client-side rollback in PHP. Just send all calls through a database class (insert(), update(), delete()) and if a transaction flag is set then they store in a stack a little query to reverse the effects of the first query (this may mean using a 'select' before each update/insert/delete to find out what data was there beforehand). I have a class does this happily, 4 a half pages of PHP. Only real drawback is that you can't do anything like INSERT ... SELECT. Tom. Andy Hall wrote: Hi, I have a PHP script that is running 4 queries. If the 4th fails, ideally I would like the other 3 queires to roll back and leave the database as if they were never run at all. Can anyone advise on any technique that would do this easily, as opposed to manually writing and running queries to set the database back (which would be messy IMO). This project is being developed on MySQL 3.23.37. Any help is appreciated, thanks. Andy Hall. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best practise roll-backs?
Hi, before you start to make inserts/updates to the database you can put $db-query(begin;) - this is to begin a transaction, in the end of the script you put commit ($db-query(commit;) if all goes ok, otherwise rollback ($db-query(rollback;). andré brás Citando Andy Hall [EMAIL PROTECTED]: Hi, I have a PHP script that is running 4 queries. If the 4th fails, ideally I would like the other 3 queires to roll back and leave the database as if they were never run at all. Can anyone advise on any technique that would do this easily, as opposed to manually writing and running queries to set the database back (which would be messy IMO). This project is being developed on MySQL 3.23.37. Any help is appreciated, thanks. Andy Hall. O SAPO já está livre de vírus com a Panda Software, fique você também! Clique em: http://antivirus.sapo.pt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment id
Stefan Schuster [EMAIL PROTECTED] wrote: Hi, I have a question about auto_increment: I have 2 tables, on of them holds my online transactions, the other one the offline transactions. Every transaction is created in the first table (call it t1) and then moved to t2. The id is generated using auto_increment. My problem is that the id's should be unique across both tables. If I dont restart the server this works fine, but if I restart it, mySQL assigns the lowest number for the new row in t1, but this id exits already in t2 which results in an error if I try to move from t1 to t2. I hope I explained the problem in a appropriate way. How can I tell mySQL to hold the auto_increment counter for Table t1 persistent ? Do you use InnoDB tables? If so, it's a known behavior. If you restart MySQL server InnoDB may reuse old value for AUTO_INCREMENT column. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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]
Innodb logfiles timestamp question
Hi, I am running 4.0.4 using innodb tables on a linux box. My innodb config is innodb_data_file_path = ibdata1:1800M;ibdata2:1800M;...ibdata10:1800M set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=500M set-variable = innodb_log_buffer_size=30M set-variable = innodb_buffer_pool_size=800M set-variable = innodb_additional_mem_pool_size=150M My question has to do with innodb's usage of the redo log files. Currently they are: 524288000 Mar 11 11:19 ib_logfile0 524288000 Mar 3 08:59 ib_logfile1 524288000 Mar 11 11:19 ib_logfile2 I always see them timestamped like this, with two having identical times, or the same within a minute or two. The particular pair of the three having the same timestamp varies. I was under the impression that mysql would cycle through these logfiles - e.g. write to logfile0 until it is full, then switch to logfile1 until full, then logfile2.., then logfile0, etc. This doesn't appear to be the case. Has anyone run across this before? (My goal is to reduce the size of the logfiles so that the time between switching is on the order of a couple hours rather than days.) Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication - separate binary logs
ron [EMAIL PROTECTED] wrote: Hello (first post) I'm a noob to this group but I have an issue which I am hoping there is a solution to. I would like to have one instance of mysqld serving many databases, and acting as a master server. Each slave will only replicate one database, and it would be undesirable for them to be able to access separate databases. I thought the built in replication tool was exactly what was required, with separate users for each DB. But I came across a snag. Even though each slave has access to only one database, the replication log they retrieve from the master contains sql statements pertaining to the other databases, from which they can gather information I would rather they not see. So my question is - can one instance of mysqld maintain multiple binary logs, specific to each database, or would I need separate instances of mysqld for each database? If you want that relay logs contain queries per database, you need separate instances for each database that you want to replicate. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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]
LOAD INDEX INTO CACHE problem
Hi, I'm trying to load an index into cache hot_cache, but it is not working. Here is my step to step: CREATE TABLE GEILSON (CPF VARCHAR(14) NOT NULL, NAME VARCHAR(75) NOT NULL, PRIMARY KEY (CPF), FULLTEXT (NAME)); INSERT INTO GEILSON (CPF,NAME) VALUES (08238512786, DAVID ESCODINO); INSERT INTO GEILSON (CPF,NAME) VALUES (05233113783, JAMES SCOTT); SET GLOBAL hot_cache.key_buffer_size = 128*1024*1024; CACHE INDEX GEILSON IN hot_cache; LOAD INDEX INTO CACHE GEILSON; I'm getting this error: +---+--+--+---+ | Table | Op | Msg_type | Msg_text | +---+--+--+---+ | NATT.GEILSON | preload_keys | error| Indexes use different block sizes | | NATT.GEILSON | preload_keys | status | Operation failed | +---+--+--+---+ I use mysql on Redhat 9.0. My version: # mysqld --version mysqld Ver 4.1.1-alpha-standard for pc-linux on i686 (Official MySQL RPM) What's the problem? Sincerelly, Geilson C. F. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Low_Priority Updates
Mysqlians, Does a low_priority behave like a DELAYED for insert and return to the caller before the update has actually been executed? If not are their plans for a DELAYED option with update? Regards, Trevor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie - dependencies
When I upgraded to the newer version of mySQL I got the same errors, but the web site I down loaded the new rpms from had a rpm to put both versions of the dependency files on the system. Go here http://www.mysql.com/downloads/mysql-4.0.html then look in the Linux downloads about 1/4 the way down, It is labeled Libraries and header files, this put both the old version and the new version. When I found this error I searched all the forums and everyone had very complex(at lease for me) ways of recompiling mySQL. But, just installing this rpm fixed it for me. Good Luck, Todd Hackathorn -Original Message- From: Robert Ross [mailto:[EMAIL PROTECTED] Sent: Thursday, March 11, 2004 7:41 AM To: [EMAIL PROTECTED] Subject: RE: Newbie - dependencies Hi Victor, I tried that initially and thought I had installed everything - RH Enterprise has the option in the GUI to install mysql - what it doesn't tell you is that it is the client and not the server. When you (eventually) find this out and try to install the server, problems occur because the server should be installed before the client (I think). So you then uninstall both server and client, then re-install the server. If sucessful, you still get problems when you try to re-install the client as the GUI cannot find the server's headers and so will not install client software. So you go and find the rpm and try to install it yourself and get the dependencies problem. I think you get an idea of my frustration, and I have not even got to the part of trying to use mysql yet! Still, I will continue on...:-) Thanks for your input. Robb -Original Message- From: Victor Medina [mailto:[EMAIL PROTECTED] Sent: 11 March 2004 13:31 To: Robert Ross Cc: [EMAIL PROTECTED] Subject: RE: Newbie - dependencies Hi! anyway, why dont you use the graphic install utility redhat uses to install mysql?? it will resolve the dependencies for you =) PS: i think it's called redhat-config-packages Best Regards! On Thu, 2004-03-11 at 09:05, Robert Ross wrote: Having just set up a RedHat Enterprise Server V.3 I am trying to add a MySQL database function. I tried and failed using up2date (following instructions from RH), so downloaded the rpms and began to install by myself. My input and the output is shown below: # rpm -i mysql-server-3.23.58-1.i386.rpm error:Failed dependencies: libmysqlclient.so.10 is needed by mysql-server-3.23.58-1 Suggested resolutions: mysql-3.23.58-1.i386.rpm # rpm -i mysql-3.23.58-1.i386.rpm error:Failed dependencies: perl-DBD-MySQL is needed by mysql-3.23.58-1 Suggested resolutions: perl-DBD-MySQL-2.1021-3.i386.rpm # rpm -i perl-DBD-MySQL-2.1021-3.i386.rpm error:Failed dependencies: libmysqlclient.so.10 is needed by perl-DBD-MySQL-2.1021-3 Suggested resolutions: mysql-3.23.58-1.i386.rpm Which takes me back to the second line and begins a loop. Libmysqlclient.so.10 is in /usr/lib/mysql/ problably from the initial setup (before I found out that the mysql-server wasn't on the setup CDs). I went for RHEL on the strength that it has up2date and I would not meet this kind of problem. Anyone got any ideas/get out of jail cards? rpm -ivh perl-DBD-MySQL-2.1021-3.i386.rpm mysql-server-3.23.58-1.i386.rpm mysql-3.23.58-1.i386.rpm -- Alex - Cheers for the reply Alex. Tried your method and got: MySQL-server conflicts with mysql-server-3.23.58-1 mysql conflicts with mysql-3.23.58-1 I tried 'locate' but could not find either file, so then tried 'rpm -e' of both. I got 'not installed' as an answer. So tried your line once more and got the same output. Robb - still in jail :-( -- |...| | _ _|Victor Medina M | |\ \ \| | _ \ / \ |Linux - Java - MySQL | | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | |/_/_/|_|_| /_/ \_\|ext. 325 - Tél: +58-241-8507325 | ||geek by nature - linux by choice | |...| -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb logfiles timestamp question
John, - Original Message - From: John Thorpe [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, March 11, 2004 7:34 PM Subject: Innodb logfiles timestamp question Hi, I am running 4.0.4 using innodb tables on a linux box. My innodb config is innodb_data_file_path = ibdata1:1800M;ibdata2:1800M;...ibdata10:1800M set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=500M set-variable = innodb_log_buffer_size=30M set-variable = innodb_buffer_pool_size=800M set-variable = innodb_additional_mem_pool_size=150M My question has to do with innodb's usage of the redo log files. Currently they are: 524288000 Mar 11 11:19 ib_logfile0 524288000 Mar 3 08:59 ib_logfile1 524288000 Mar 11 11:19 ib_logfile2 I always see them timestamped like this, with two having identical times, or the same within a minute or two. The particular pair of the three having the same timestamp varies. the checkpoint stamp fields are in the first ib_logfile. Does that explain the observed phenomenon? I was under the impression that mysql would cycle through these logfiles - e.g. write to logfile0 until it is full, then switch to logfile1 until full, then logfile2.., then logfile0, etc. This doesn't appear to be the case. Has anyone run across this before? (My goal is to reduce the size of the logfiles so that the time between switching is on the order of a couple hours rather than days.) Thanks, John Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Register now for the 2004 MySQL Users Conference! http://www.mysql.com/events/uc2004/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database crash, lock held too long
Zen, the printout looks like the thread reading in the transaction system header from the first data file would have stuck. Please send to me the printouts from other crashes. Maybe this is a bug in the trx system header handling. Though, my first guess is that this is an OS/drivers/hardware bug and the thread has hung inside a call of pread(). Unfortunately, the InnoDB Monitor thread has also hung on one of the reserved semaphores, and we do not get more detailed diagnostic printout below. I added to MySQL-4.0 now more diagnostics. It will always print the number of pending pread() and pwrite() calls if there is a long semaphore wait. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Register now for the 2004 MySQL Users Conference! http://www.mysql.com/events/uc2004/index.html - Original Message - From: ZenShadow [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, March 11, 2004 12:58 AM Subject: Database crash, lock held too long --=-4vGO7MeWwD4jujspp0iW Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Folks, We've been running a fairly high-traffic mysql server for some time, and we've been occasionally seeing messages like this in the logs: InnoDB: Error: semaphore wait has lasted 600 seconds InnoDB: We intentionally crash the server, because it appears to be hung. 040309 18:20:36 InnoDB: Assertion failure in thread 2623773248 in file sync0arr.c line 934 InnoDB: Failing assertion: 0 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] This is a fairly critical issue for us (the latest crash borked replication, which is never fun), so if anyone has an answer it would be most appreciated. I've attached the full relevant log snippet and the my.cnf for anyone who's willing to take a look. I'd provide more information, but it has so far been impossible to tell what's causing it. This server averages 2000 queries per second (25% of which is inserts and updates, I'd guess), and is also the replication master for another database. It seems to be random so far. This system is running on a dual Xeon (2.4GHz + hyperthread) with 4GB of memory, Two U160 SCSI arrays (configured for 1TB @ RAID1+0), and the stock RedHat 2.4.20-8smp kernel (the box is pretty much a stock RedHat 9 install). Again, any insight would be most appreciated. --ZS -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
BLOB, SUBSTRING and 65536 characters limit
Hello, I run into a problem where I need to get contents of BLOB to my php script. I found out that I'm limited by max_packet_size (which i can't change because of my ISP) and i tried to work around it by using SELECT substring(column,x,1024) where x is number increasing in steps of 1024. But once x reaches 65536 i get empty result. What am I doing wrong or how can I work around this? I'm using MySQL 4.0.14 running on FreeBSD 4.9. Thanks in advance for your help, Tomas Zvala -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: checksum error in innodb - what can do to find the reason ?
Christian, do you mean that even if you recreate the whole tablespace, you will get corruption on some data page rather quickly? Please send to me the whole .err log for analysis. The printout below shows that the start and the end of the page are ok, but in the middle there is some change that has happened since InnoDB wrote the page to the ibdata file. Thus, this is probably an OS/drivers/hardware bug. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Register now for the 2004 MySQL Users Conference! http://www.mysql.com/events/uc2004/index.html - Original Message - From: Christian Rabe [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, March 10, 2004 2:11 PM Subject: checksum error in innodb - what can do to find the reason ? Hello together, I'm getting the following error every few hours. I can force it by trying to dump the db. Even if I drop the named table and create an empty one the error still occurs after some time. I tried both, raw-device and normal. But nothing solves this problem. The data is stored on a raid and the discs should be faultless. Running debian sarge with kernel 2.4.25 2x2.4 GHz Xeon 2GB RAM RAID bus controller: 3ware Inc 3ware 7000-series ATA-RAID (rev 01) -- -- InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 105283. InnoDB: You may have to recover from a backup. 040310 1:01:09 InnoDB: Page dump in ascii and hex (16384 bytes): len 16384; hex 9f84195800019b4300019a319b550001c8b03f3545bf 001a3f5a00d324091f950002000200680 040310 1:01:09 InnoDB: Page checksum 3314471633, prior-to-4.0.14-form checksum 2546962952 InnoDB: stored checksum 2676234584, prior-to-4.0.14-form stored checksum 2546962952 InnoDB: Page lsn 1 3366993717, low 4 bytes of lsn at page end 3366993717 InnoDB: Page may be an index page where index id is 0 22 InnoDB: and table adserver/banner_stats_running index PRIMARY InnoDB: Database page corruption on disk or a failed InnoDB: file read of page 105283. InnoDB: You may have to recover from a backup. InnoDB: It is also possible that your operating InnoDB: system has corrupted its own file cache InnoDB: and rebooting your computer removes the InnoDB: error. InnoDB: If the corrupt page is an index page InnoDB: you can also try to fix the corruption InnoDB: by dumping, dropping, and reimporting InnoDB: the corrupt table. You can use CHECK InnoDB: TABLE to scan your table for corruption. InnoDB: Look also at section 6.1 of InnoDB: http://www.innodb.com/ibman.html about InnoDB: forcing recovery. InnoDB: Ending processing because of a corrupt database page. -- mysql show table status like 'banner_stats_running'; +--+++--++-- ---+-+--+---++-- ---+-+++ + | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment| +--+++--++-- ---+-+--+---++-- ---+-+++ + | banner_stats_running | InnoDB | Fixed | 11750021 |154 | 1812987904 |NULL | 3481616384 | 0 | NULL | NULL| NULL| NULL || InnoDB free: 43975680 kB; InnoDB free: 15213568 kB | +--+++--++-- ---+-+--+---++-- ---+-+++ + 1 row in set (0.52 sec) Note: the first Innodb-free is from a dump mysql \s -- mysql Ver 12.22 Distrib 4.0.18, for pc-linux (i686) Connection id: 173011 Current database: Current user: [EMAIL PROTECTED] SSL:Not in use Current pager: stdout Using outfile: '' Server version: 4.0.18 Protocol version: 10 Connection: Localhost via UNIX socket Client characterset:latin1 Server characterset:latin1 UNIX socket:/tmp/mysql.sock Uptime:
link error gcc compiling mysql on solaris 9 (lib not found)
This may be more solaris related, but I need to compile mysql with gcc because perl is compiled with gcc and I need DBD::mysql. It seems the linker can't find the libs. I seem to have the same problem linked dynamicaly expect the build completes and then can't load the shared library when I start mysql. I hope I have include all relevant info, hope some one can point me the right way! Thanks Ken much cut ../dbug/libdbug.a ../regex/libregex.a ../strings/libmystrings.a -ldl -lz -lposix4 -lcrypt -lgen -lsocket -ln sl -lm -lpthread -lthread ld: fatal: library -lrt: not found ld: fatal: library -ldl: not found ld: fatal: library -lposix4: not found ld: fatal: library -lpthread: not found ld: fatal: library -lthread: not found ld: fatal: File processing errors. No output written to mysqld collect2: ld returned 1 exit status gmake[4]: *** [mysqld] Error 1 gmake[3]: *** [all-recursive] Error 1 gmake[2]: *** [all] Error 2 gmake[1]: *** [all-recursive] Error 1 gmake: *** [all] Error 2 --- additional info hill crle Configuration file [3]: /var/ld/ld.config Default Library Path (ELF): /usr/lib:/usr/local/mysql/lib/mysql Trusted Directories (ELF):/usr/lib/secure (system default) Command line: crle -c /var/ld/ld.config -l /usr/lib:/usr/local/mysql/lib/mysql hillCC=gcc CFLAGS=-O3 CXX=gcc \ CXXFLAGS=-O3 -felide-constructors -fno-exceptions -fno-rtti \ ./configure $DEBUG --enable-large-files --with-innodb \ $LOWMEM --enable-assembler --with-mysqld-ldflags=-all-static hillgmake -s hill gcc -v Reading specs from /usr/local/lib/gcc-lib/sparc-sun-solaris2.9/3.3.2/specs Configured with: ../configure --with-as=/usr/ccs/bin/as --with-ld=/usr/ccs/bin/ld --dis able-nls Thread model: posix gcc version 3.3.2 Ken Menzel ICQ# 9325188 www.icarz.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Conditional controlling where used in a query
I have two tables that I want write one query that uses different where clauses under conditions in the where clause. In the first table I have type of document with document names. Through a third party program I am passing a document name as a parameter. This table also has a key that is called DoctypeID. The second table lists the possible subfolders that the documents can be stored in, which is located based on matching the DoctypeID. Certain document types can only go into one specific subfolder. i.e. a New Hire document can only go into the New Hire subfolder and Payroll documents can only go into the Payroll subfolder. However other documents can go into any subfolder. i.e. email documents. The first attempt at writing this query is below. select distinct subfolderdescription from doctype a, subfolddesc b where if(a.doctype = HR and a.doctypeid = b.doctypeid, a.doctype = HR and a.doctypeid = b.doctypeid, b.doctypeid like %); This did not work the way that I intended for it to work. What I want to do is have one query that will find the record that matches the document type in the first table and if it finds a match for that record in the second table return subfolder for the matching record. But if it does not find a match to return the complete list of all the unique subfolders that are in the second table. Does anyone have any ideas on how to do this? Thank you Eric H. Lommatsch Programmer MICRONix, Inc. 2087 South Grant Street Denver, CO 80210 Tel 303-777-8939 Fax 303-778-0378
Re: BLOB, SUBSTRING and 65536 characters limit
http://php.dreamwerx.net/forums/viewtopic.php?t=6 storage implementation that is not affected by max_packet_size. On Thu, 11 Mar 2004, Tomas Zvala wrote: Hello, I run into a problem where I need to get contents of BLOB to my php script. I found out that I'm limited by max_packet_size (which i can't change because of my ISP) and i tried to work around it by using SELECT substring(column,x,1024) where x is number increasing in steps of 1024. But once x reaches 65536 i get empty result. What am I doing wrong or how can I work around this? I'm using MySQL 4.0.14 running on FreeBSD 4.9. Thanks in advance for your help, Tomas Zvala -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysterious 'x' when retrieving records
I have a very simple query SELECT ProblemReports.* FROM ProbelmReports; If there is no data in the column 'Description' it returns 'x'. There is no default value being used and there is no data in the field. Why would it return 'x'? The field type is 'TEXT'. I have another 'TEXT' field in the same table and it doesn't do this. Does anyone have any suggestions? Jacque
Prepared Statements with NULL terminated strings
According to this page in the online manual http://www.mysql.com/doc/en/C_API_Prepared_statement_datatypes.html, the length element of the MYSQL_BIND structure when set to 0 will treat the buffer element as a NULL terminated string. If I do such a thing, prepare some query, bind some columns and execute it, the string values inserted into the database are not correct. What it looks like it is doing is when length is 0, it uses buffer_length instead of doing a strlen( buffer ). To see this, try inserting some data with setting length, run a query like SELECT CONCAT( 'x', col, 'x' ) FROM table; You get back values like xVALUEx Now try inserting some data leaving length set to 0 and rerun the same query as above and you get: xVALUE The trailing x never gets displayed So my question is, is this a bug in the API, a bug in the documentation, or just an unfinished feature? -- Nathaniel Blanchard Developer Sane Solutions, LLC 401-295-4809 ext 142 401-295-4154 fax [EMAIL PROTECTED] This e-mail message may contain confidential information. If you are not the intended recipient, any use, dissemination, distribution or copying of this e-mail message is strictly prohibited. If you have received this message in error, please immediately notify the sender and delete this e-mail message from your computer. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysterious 'x' when retrieving records
Can you post some sample data and ddl? -Original Message- From: Jacque Scott To: [EMAIL PROTECTED] Sent: 3/11/04 3:12 PM Subject: Mysterious 'x' when retrieving records I have a very simple query SELECT ProblemReports.* FROM ProbelmReports; If there is no data in the column 'Description' it returns 'x'. There is no default value being used and there is no data in the field. Why would it return 'x'? The field type is 'TEXT'. I have another 'TEXT' field in the same table and it doesn't do this. Does anyone have any suggestions? Jacque -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysterious 'x' when retrieving records
Here is my code that retrieves the data. It's weird because I don't get the 'x' when I run the same query in ControlCenter. It's only when using the code below. Also this is the first time that I have seen this. This code works perfect with all my other queries. Dim cn As New ADODB.Connection Dim cn2 As New ADODB.Connection Dim RS As New ADODB.Recordset Dim fld As ADODB.Field Dim DataArray() As String Dim i As Long Dim j As Integer If strFileName Then cn.Open Provider=Microsoft.Jet.OLEDB.4.0;Data Source= strFileName ;Persist Security Info=False If cn.State = adStateOpen Then ' Open employee table. Set RS = New ADODB.Recordset RS.Source = strSQL Set RS.ActiveConnection = cn RS.CursorLocation = adUseClient RS.Open ReDim DataArray(RS.Fields.Count - 1, RS.RecordCount) ' set the size of the array that we're storing the recordset into. For Each fld In RS.Fields ' we put the recordset field names in the zero record of the array DataArray(i, 0) = fld.Name i = i + 1 Next If RS.RecordCount 0 Then For i = 1 To RS.RecordCount ' count through the rows For Each fld In RS.Fields ' count through the fields DataArray(j, i) = fld.Value ' always append a empty string to the value in the recordset to make sure we don't have any NULLs stored in the array. j = j + 1 Next RS.MoveNext j = 0 Next i End If Victor Pendleton [EMAIL PROTECTED] 3/11/2004 1:15:45 PM Can you post some sample data and ddl? -Original Message- From: Jacque Scott To: [EMAIL PROTECTED] Sent: 3/11/04 3:12 PM Subject: Mysterious 'x' when retrieving records I have a very simple query SELECT ProblemReports.* FROM ProbelmReports; If there is no data in the column 'Description' it returns 'x'. There is no default value being used and there is no data in the field. Why would it return 'x'? The field type is 'TEXT'. I have another 'TEXT' field in the same table and it doesn't do this. Does anyone have any suggestions? Jacque
Re: link error gcc compiling mysql on solaris 9 (lib not found)
Ken Menzel wrote:
This may be more solaris related, but I need to compile mysql with
gcc because perl is compiled with gcc and I need DBD::mysql. It seems
the linker can't find the libs. I seem to have the same problem
linked dynamicaly expect the build completes and then can't load the
shared library when I start mysql.
I hope I have include all relevant info, hope some one can point me
the right way!
Thanks Ken
much cut
../dbug/libdbug.a ../regex/libregex.a
../strings/libmystrings.a -ldl -lz -lposix4 -lcrypt -lgen -lsocket -ln
sl -lm -lpthread -lthread
ld: fatal: library -lrt: not found
ld: fatal: library -ldl: not found
ld: fatal: library -lposix4: not found
ld: fatal: library -lpthread: not found
ld: fatal: library -lthread: not found
ld: fatal: File processing errors. No output written to mysqld
Ken:
That's a mess of a problem. If the libraries were included in the link,
configure must have detected them on your system somehow, but now they are not
working. Did you by any chance use --with-mysqld-ldflags=-all-static ?
If not, start troubleshooting by trying to get something like this to compile
and execute:
-test.c
#include pthread.h
void* run(void* p)
{
return 0;
}
int main()
{
pthread_t th;
pthread_create(th,0,run,0);
}
end test.c-
--
Sasha Pachev
Create online surveys at http://www.surveyz.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Low_Priority Updates
Trevor Price wrote: Mysqlians, Does a low_priority behave like a DELAYED for insert and return to the caller before the update has actually been executed? If not are their plans for a DELAYED option with update? No - low_priority means that if there is a lock contention for a table, the lock priority will go to other threads. As far as I know there are no plans for a delayed update. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment id
Stefan Schuster [EMAIL PROTECTED] wrote: Hi, I have a question about auto_increment: I have 2 tables, on of them holds my online transactions, the other one the offline transactions. Every transaction is created in the first table (call it t1) and then moved to t2. The id is generated using auto_increment. My problem is that the id's should be unique across both tables. If I dont restart the server this works fine, but if I restart it, mySQL assigns the lowest number for the new row in t1, but this id exits already in t2 which results in an error if I try to move from t1 to t2. I hope I explained the problem in a appropriate way. How can I tell mySQL to hold the auto_increment counter for Table t1 persistent ? Do you use InnoDB tables? If so, it's a known behavior. If you restart MySQL server InnoDB may reuse old value for AUTO_INCREMENT column. I need transaction support, as I understand it InnoDB is the only tabletype that supports this. Is there any way to change the behaviour, maybe a table type that has the desired (persistent incrementcounter) behaviour AND supports transactions ? Thanks, -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: https access to phpmyadmin - mysql
codefit wrote: Hi, I was wondering if anyone could point me to info on setting up https access to the admin page on phpmyadmin? Use mod_ssl -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to minimize Master-Slave Traffic during replication?
Lutz Maibach wrote: Hi, we got a little problem with a master-slave replication (both running MySQL 4.0.18) eating up our complete bandwidth. The slave is connected via a 2MBit-SDSL-Line which is also used to connect our Office-PCs with the internet so I get complaints about the slow connection which is caused by the huge amount of master-slave traffic through this line. Most of the replication traffic is caused by tables, which are only created once, used for further selects and then deleted. The tables can't be created as temporary tables (that's what our programmers are telling me - I'm only the Admin and don't know whether thei're right or wrong) but are completely useless for replication. Use SET SQL_LOG_BIN=0 on the connection (process privilege needed) to turn off binary logging for the queries that do not need to be replicated to the slave. SET SQL_LOG_BIN=1 to turn logging back on. Does anyone use the slave_compressend_protocol - variable successfully and can tell me, where I can see, whether the slave recognized this switch and do I have to set this switch on the server too? It's a fairly new feature, but it should be very safe - it just enables the use of some very well field tested code. You should set it only on the slave - the slave will tell the master that it wants to use compression. To see if the daemon has it, strings mysqld | grep slave_compression_protocol -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't create thread
Does Red Hat have some kind of userland address space hack that
we're not aware of?
Do you have any special kernel config options that you did not use before?
the thread stacks are 2MB apiece (bf601000-bf80 is
2093056 bytes, or 2044kB)! Yet:
# mysql -e 'show variables' | grep thread_stack
thread_stack196608
It seems like the setting does nothing for us. We top out at
exactly 256 threads.
Ok, that's the problem. Thread stack on older glibc versions is hard-coded.
Newer versions (at least 2.3.2) will have a truly adjustable stack size if it is
compiled with FLOATING_STACKS defined, which is off by default. One possibility
is that the MySQL build team moved to linking against glibc 2.3.2, but did not
enable floating stacks in it. Maybe Lenz can comment on it.
We wrote a wrapper with LD_PRELOAD. When mysqld does
pthread_attr_setstacksize() we translate it into an anonymous mmap()
and use pthread_attr_setstackaddr() instead. It's the equivalent
of FLOATING_STACKS. ;)
Our mysqld (which is running as slave) will seg fault if we set
the thread_stack to 256k. Running it with thread_stack set to 2MB
works flawlessly (that is until we allocate 256 threads).
mysql 3.23.58 doesn't like 2MB thread stacks in our environment.
Is this normal?
Here's the code if you want to see for yourself.
Build with:
gcc -Wall -shared -o pthread_preload.so pthread_preload.c -lpthread -ldl
Ignore the deprecated warnings (ergo the glibc comment below).
Run LD_PRELOAD=pthread_preload.so before launching mysqld.
--- pthread_preload.c ---
/* Copyright (C) Netgraft Corp 2004 GPL license */
#include stdio.h
#include limits.h
#include pthread.h
#include sys/mman.h
#define __USE_GNU 1
#include dlfcn.h
#define PAGE_SIZE 4096 /* size of crash zone at end of stack */
/* XXX: this assumes that the stacks grow towards decreasing addresses! */
extern int pthread_attr_setstack (pthread_attr_t *__attr, void *__stackaddr, size_t
__stacksize);
int
pthread_attr_setstacksize(pthread_attr_t *attr, size_t stacksize)
{
static void *start = NULL, *stop = NULL;
static int (*parent)(pthread_attr_t *,size_t) = NULL;
static FILE *dbg = NULL;
if (!parent) {
parent = dlsym(RTLD_NEXT, pthread_attr_setstacksize);
if (parent == pthread_attr_setstacksize) {
parent = NULL;
}
}
if (!dbg) dbg = fopen(/tmp/buh,w);
if (dbg) { fprintf(dbg,buh:%d,%p\n,(int)stacksize,parent); fflush(dbg); }
if (stacksize = PTHREAD_STACK_MIN)
stacksize = PTHREAD_STACK_MIN;
if (stacksize = 2*1024*1024 parent) {
/* big stack, let the parent do this */
return parent(attr, stacksize);
}
if (((int)start=mmap(NULL, stacksize+PAGE_SIZE,
PROT_EXEC|PROT_READ|PROT_WRITE, MAP_ANONYMOUS|MAP_PRIVATE, -1, 0)) == -1) {
if (parent) {
return parent(attr, stacksize);
} else {
return -1;
}
}
/* unmap the lowest addresses of the stack for overflow */
mprotect(start, PAGE_SIZE, PROT_NONE);
start += PAGE_SIZE;
/* stop is the highest address in the stack,
* actually just beyond it */
stop = start + stacksize;
if (parent) {
int ret;
if (dbg) { fprintf(dbg,stacksize...\n); fflush(dbg); }
ret = parent(attr,stacksize);
if (ret) return ret;
if (dbg) { fprintf(dbg,stackaddr(%p-%p)...\n,start,stop); fflush(dbg); }
ret = pthread_attr_setstackaddr(attr, stop);
if (dbg) { fprintf(dbg,ret=%d...\n,ret); fflush(dbg); }
return ret;
} else {
/* this doesn't work because the glibc people should be shot */
/* so preferentially use the setstacksize/addr interface */
pthread_attr_setstack(attr, stop, stacksize);
}
return 0;
}
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: https access to phpmyadmin - mysql
Yes I have mod_ssl and have used it for a long time for other applications. What I am looking for are phpmyadmin specific instructions as to how to configure phpmyadmin to use mod_ssl and and DISALLOW http logins and only allow https logins and use of phpmyadmin. Thanks anyone. -Original Message- From: Sasha Pachev [mailto:[EMAIL PROTECTED] Sent: Thursday, March 11, 2004 5:27 PM To: codefit Cc: [EMAIL PROTECTED] Subject: Re: https access to phpmyadmin - mysql codefit wrote: Hi, I was wondering if anyone could point me to info on setting up https access to the admin page on phpmyadmin? Use mod_ssl -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: https access to phpmyadmin - mysql
Just set PHPMyAdmin under the directory tree of your https server, and not under the directory tree of your http server. That way you have to connect to the https server to log in. j- k- On Thursday 11 March 2004 02:15 pm, codefit wrote: Yes I have mod_ssl and have used it for a long time for other applications. What I am looking for are phpmyadmin specific instructions as to how to configure phpmyadmin to use mod_ssl and and DISALLOW http logins and only allow https logins and use of phpmyadmin. Thanks anyone. -- Joshua J. Kugler Fairbanks, Alaska Computer Consultant--Systems Designer .--- --- ... ..- .--.- ..- --. .-.. . .-. [EMAIL PROTECTED] ICQ#:13706295 Every knee shall bow, and every tongue confess, in heaven, on earth, and under the earth, that Jesus Christ is LORD -- Count on it! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Privilege to single database being revoked occasionally
Same database, and same user. Nope, it's a fairly low traffic application (a helpdesk) and there's no large importing done. When it happens again I will print out some SQL results. Anything I can look out for or provide the next time it happens? Thanks - Original Message - From: Victor Pendleton [EMAIL PROTECTED] To: 'Terence ' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, March 11, 2004 10:41 PM Subject: RE: Privilege to single database being revoked occasionally Is it the same database, same user? Does it follow any maintenance or large import? -Original Message- From: Terence To: [EMAIL PROTECTED] Sent: 3/11/04 12:25 AM Subject: Privilege to single database being revoked occasionally Dear Lists, We moved to 4.1.0 when it was first launched, and twice a month or so, we get access denied commands to one particular database for one particular user. The privileges are correctly set, and the only way to restore access for the user, is to revoke and re-apply the privileges and all works fine again. I am the only user with permission to change privileges. I am just wondering if anyone else has stumbled across this problem in 4.1.0? It doesnt seem to have been fixed in version 4.1.1 according to: http://www.mysql.com/doc/en/News-4.1.1.html If there's any data I can provide for debugging purposes, please let me know. Thanks Terence -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT ... UPDATE
Hi Everyone, Is there an easy way to update a field for the last record referenced by an ID number? where that ID number is used on multiple rows? At the moment I am doing this which works: CREATE TEMPORARY TABLE tmp_user ( SELECT user_id, MAX(last_updated) AS max_last_updated FROM employee GROUP BY user_id); UPDATE tmp_user, employee SET picture='My Pic3' WHERE employee.user_id='19' AND last_updated=tmp_user.max_last_updated; Where user_id is the user I wish to update, and picture is just a string (which will hold the filename for their picture). But this just seems too complicated? So any thoughts? I'm also using v4.0.18... Yours Sincerely Darran -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql-4.0.18 build problem in FreeBSD-5.2-CURRENT
Hi, Today I wanted to upgrade mysql-4.0.17 to mysql-4.0.18 in FreeBSD-5.2-CURRENT and got error below. I used following options to compile from ports collection: make WITH_CHARSET=cp1251 WITH_LINUXTHREADS=yes BUILD_STATIC=yes install Error message: - /usr/lib/libc.a(res_init.o): In function `__h_error': res_init.o(.text+0x1104): multiple definition of `__h_error' /usr/local/lib/liblthread.a(errno.o):/usr/ports/devel/linuxthreads/work/linuxthreads-2.2.3_14/errno.c:29: first defined here /usr/bin/ld: Warning: size of symbol `__h_error' changed from 88 to 36 in /usr/lib/libc.a(res_init.o) *** Error code 1 Stop in /usr/ports/databases/mysql40-server/work/mysql-4.0.18/sql. *** Error code 1 Stop in /usr/ports/databases/mysql40-server/work/mysql-4.0.18/sql. *** Error code 1 Stop in /usr/ports/databases/mysql40-server/work/mysql-4.0.18/sql. *** Error code 1 Stop in /usr/ports/databases/mysql40-server/work/mysql-4.0.18. *** Error code 1 Stop in /usr/ports/databases/mysql40-server/work/mysql-4.0.18. *** Error code 1 Stop in /usr/ports/databases/mysql40-server. --- What should do in this case? Is there anybody solved this problem before? tia, Ganbold -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Random Table Locking in 3.23.58-7 ?
Here is the problem. I am getting random table locks in my databases on a new server running Fedora 0.96 and Mysql 3.23.58-7. It seems that different tables will lock -- what I mean by that is when I try to execute a query on them, mysql just freezes up -- it doesn't crash or return errors it just hangs. This can happen on different tables at different times, just a couple of tables on a database, or on an entire database as just happened to me right now. There seems to be no rhyme or reason. Another symptom, at least I think so, is that when I try to restart mysql with service mysqld restart I get: Mysql Stopped [OK] Mysql Started [Failed]. ANd when I look at processes running by user mysql, I see a pid with mysqld attached to it. If I kill it via a kill -9 command another pops up immediately. However, if I then do a mysqld server restart, it shuts down [OK] and then starts [OK] and it all appears to be working again. But then another table locks again and I have repeat this process. On a mildly used server this happening every 5-30 minutes or more frequently and I am just not accessing the tables soon enough to notice. Sometimes I can browse the tables just fine, but trying input or update an entry in the table will hang. IF I cancel that query, I can still browse it. I am thoroughly confused and looking for ANY help you might be able to give me. Ryan Shrout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fw: Load data + odbc
Now is on the list where it belongs :) Em please help! - Original Message - From: Carl Karsten [EMAIL PROTECTED] To: Stan Sebastian [EMAIL PROTECTED] Sent: Thursday, March 11, 2004 5:58 PM Subject: Re: Load data + odbc One of us (looks like me) replied directly instead of posting to the list. I have some ideas, but this and my ideas should be on the list. post it up there and I will reply to it. Carl K - Original Message - From: Stan Sebastian [EMAIL PROTECTED] To: Carl Karsten [EMAIL PROTECTED] Sent: Thursday, March 11, 2004 1:39 AM Subject: Re: Load data + odbc Thanks for you reply. The first test i've done was on, lets say, BoxA and it worked just fine. Than, i said,'ok, lets install the client on another WKS' that is BoxB. And from BoxB it's not working. I've got 2 workstations and a server on which i have Mysql 3.23.52 - nt. Lets call the workstations BoxA and BoxB, and the server BoxC. The clients are build in Visual FoxPro and use MyODBC for connecting to server. From both WKS statements like select, insert, update, delete work just fine, but when i want to 'LOAD DATA local INFILE' it works OK only from one WKS. Here's my code open database opreluare CREATE CONNECTION transfer ; DATASOURCE MYSQLSERVER ; USERID incarc PASSWORD incarc ; DATABASE OCUPAT vQuery=LOAD DATA local INFILE 'c:/ocupat/preluare/baza.txt' into table baza FIELDS TERMINATED BY '' r=sqlexec(sqlconnect(transfer),vQuery) and r is -1 after that. It's funny that another sql statements, SELECT, DELETE, UPDATE, INSERT work. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
