Re: Permission Denied for INSTALL-BINARY
On Wed, 9 Feb 2005, Mark Sargent wrote: Hi All, using Fedora2 and trying to install from a .tar file. Extracted to this dir, mysql-standard-4.1.9-pc-linux-gnu-i686 where I see the INSTALL-BINARY file. Using this cmd, ./INSTALL-BINARY gives the following error, [EMAIL PROTECTED] mysql-standard-4.1.9-pc-linux-gnu-i686]# ./INSTALL-BINARY -bash: ./INSTALL-BINARY: Permission denied What am I doing wrong here.? I'm rather new to Linux too. Cheers. Mark Sargent. Hi Mark, the file INSTALL-BINARY is a text file that includes documentation you have to read to know how you should proceed to install the package. (with more INSTALL-BINARY, for example, quit with q). so read this and follow the instructions ;-) Olivier PS: note that if you have a RedHat based system, like SuSE, Fedora, Mandrake, that use the RPM (Redhat Package Manager), you should download the RPM package instead. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Difference between 'LIKE' and '='
| But I have a question: is there any difference between the following? | SELECT lname, fname FROM contacts WHERE lname = 'smith'; | SELECT lname, fname FROM contacts WHERE lname LIKE 'smith'; | Sincerely, | -Josh My gut hunch is that if your LIKE expression is going to contain no wildcards, you should probably write it as an '=' simply because it is likely to perform better. If anyone reading this is knowledgeable on MySQL performance, please jump in and correct me if I'm wrong. Rhino There is nothing wrong... If you'll use '=' you will get in EXPLAIN SELECT a type of 'ref' and if you're using 'LIKE' you will get a type of 'range' witch is slower than 'ref'.. I have a table that stores id of a city, ccode is the country code for that city, and city... is a varchar containing the name of the town... there is an index '2din3' on 'ccode,city' let's see: Queries: EXPLAIN SELECT * FROM `com_cities` WHERE `ccode` LIKE 'EN'; EXPLAIN SELECT * FROM `com_cities` WHERE `ccode` = 'EN'; Results showing only differencies: type; ref range;NULL ref;const Of course things for me would make no big difference since `ccode` si a 2 letter CHAR... but for a varchar and a big table would ! Gabriel PREDA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqld error
I am trying to get mysql working on my Windows ME machine. When trying Server\bin\mysqld unknown option '--enable-named-pipe' MySQL version 4.1.9. Any help greatly appreciated. Dick WinME does not support named pipes... so neither mysqld... so you should remove from my.cnf or my.ini witchever you use... any declaration on named pipes... Gabriel PREDA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query problem
On Tuesday 08 February 2005 17:03, Coz Web wrote: This will (as I believe Daniel suggested) keep things relatively simple, avoiding an overly complex query that you cannot maintain in the future. Well, my solution was, as you'll have observed, vastly over-complicated. I think I'd misunderstood the nature of the problem (or I'm just too bunged up with cold, at the moment, to think straight :-/ - whichever)... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems with decimal numbers
On Tuesday 08 February 2005 21:11, Stephane Pinel wrote: Hello, When I try to insert decimal numbers like 12857.59, 13858.58 or 14785.60, they are inserted as 12857.58, 13858.57 and 14785.59 ?! DataType is DECIMAL 10,2 Any idea of a way to avoid this annoying issue ? Thanks. Regards. Stéphane. Is this Windows/PHP, perchance? The double-precision floating point maths PHP uses isn't always precise. Values are stored as binary numbers, which may only be approximations of their decimal values. For instance, on 32-bit Windows, the following evaluates as false: ?php $a =1.1; $b=0.4; echo (($a-b == 0.7)? 'true' : 'false'); ? Seventenths is an irrational number in binary maths, you see. You'd do well to make sure that it isn't actually your operating system, that's munging your values, rather than MySQL. If it is PHP, try echoing your INSERT statements, before they are executed, to see what is actually being inserted. (Otherwise, I haven't a clue, so ignore me :). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Random 1036 Table XXX is read-only
After upgrading mysql server from 4.1.3beta to 4.1.9 (FreeBSD 4.8, mysql is installed from ports) I sometimes have the following problem: when some perl client tries to execute INSERT or UPDATE query via DBD::mysql it from time to time (but NOT always ) gets error 1036 (Table 'XX' is read only). Meanwhile: 1) Mysql user who tries to execute a query has all necessary priveleges 2) File system priveleges are also correctly set. mysqld runs under user mysql. Mysql datadir and ALL database files are owned by it and have 770 privilege mask. 3) The problem refers to MANY tables in different databases, but does NOT turn up systematicaly (by random from time to time). 4) Dumping and restoring problem tables did not solve the case 5) Upgrading DBD and DBI did not help either -- mailto:[EMAIL PROTECTED] icq: 346192719 http://www.portalsoft.ru -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
bcc with mysql
hi, I want to connect to mysql using borland c. The version is bcc5.0 and for mysql it is mysql4.0.13 The compilation is ok... but its showing linker error like unresolved external mysql_init referenced from module ... what is the problem??? How to specify the correct library... If anybody knows the solutions, pls reply... unnikrishnan - Do you Yahoo!? Yahoo! Search presents - Jib Jab's 'Second Term'
backup/synchronize tables/db web-local server
Hi I use mysql/php on several client web sites (each client has its own db). For development purpose, I have a local web server with php/mysql. When a web site is still in development, this is the master db, even if a copy has been uploaded on the live web server at my isp. When a site is completed, the web db becomes the master (as clients and visitors are updating data on the web db). My problem is that I would like to keep the local db synchronized with the web db (at least for the tables that are regularly updated on the web). Manually I would do a dump (data export only) of these web tables onto a local file, then empty the local equivalent tables and then running the sql statements of the dump file (insert). I don't do a systematic download of the whole web db as it is sometimes too big and besides not all the tables are updated by the client/visitors. My idea is to dynamically build a mysql script via php that can do the job for each client web sites for each of the tables contained in an array and be executed from a simple click on a button on the local server of course. As I am not a mysql/php guru, I would appreciate suggestions to do this as simple as possible. Please copy the reply to my email address. Thanks in advance. -- Kind regards, Paul. Gondwana [EMAIL PROTECTED] http://www.gondwanastudio.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT DISTINCT Problem
People, including me, often expect the wrong thing from SELECT DISTINCT, May suggest you do the following: Create table temp_tb_spots as Select * from tb_spots where aired_station = '??' ; select distinct Date from temp_tb_spots group ; In a different window run Select Date from temp_tb_spots group by Date ; The creation of the extra table is so you can browse it to help convince yourself you are getting what you intend The two select statements are equivalent in MySQL and will give you the same result. This result should be the same as your original query. If not then there is a bug. Hope that helps set your expectation of the distinct key word. Best Regards, Boyd E. Hemphill WEST Project Manager MySQL Certified Professional [EMAIL PROTECTED] Triand, Inc. www.triand.com -Original Message- From: James Purser [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 08, 2005 7:19 PM To: mysql@lists.mysql.com Subject: SELECT DISTINCT Problem I have a large database that I am trying to run a SELECT DISTINCT across. SELECT DISTINCT Date FROM tb_spots WHERE aired_station = '??' However the results I am getting from this query do not match up with the data on the database, instead there are large gaps. Is there any know problem with SELECT DISTINCT across large databases? -- James Purser Winnet Developer +61 2 4223 4131 http://www.winnet.com.au -- 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: backup/synchronize tables/db web-local server
On Wed, 9 Feb 2005 13:10:16 +0200, Paul Godard [EMAIL PROTECTED] wrote: Hi I use mysql/php on several client web sites (each client has its own db). For development purpose, I have a local web server with php/mysql. When a web site is still in development, this is the master db, even if a copy has been uploaded on the live web server at my isp. When a site is completed, the web db becomes the master (as clients and visitors are updating data on the web db). My problem is that I would like to keep the local db synchronized with the web db (at least for the tables that are regularly updated on the web). Manually I would do a dump (data export only) of these web tables onto a local file, then empty the local equivalent tables and then running the sql statements of the dump file (insert). I don't do a systematic download of the whole web db as it is sometimes too big and besides not all the tables are updated by the client/visitors. My idea is to dynamically build a mysql script via php that can do the job for each client web sites for each of the tables contained in an array and be executed from a simple click on a button on the local server of course. As I am not a mysql/php guru, I would appreciate suggestions to do this as simple as possible. Have you thought about using mysqldump piped to the local DB? For example: mysqldump -h server_name_or_ip -u username -ppassword db_name table1 [table2 ...] mysql -u localuser -p db_name This is (I believe) shown in the on-line manual under mysqldump. It fulfills my needs. You could even set this up in cron (scheduled tasks). HTH Coz -- CozWeb Solutions Ltd http://www.cozweb.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Memory limit?
Hi, I try to install MySQL 4.1.9 (official RPM from mysql.com). My machine is running linux 2.6.9, and it has 4GB of RAM. The problem is MySQL won't start if I set innodb_buffer_pool_size to = 2GB. Here is my ulimit. [EMAIL PROTECTED] mysql]# ulimit -a core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited file size (blocks, -f) unlimited pending signals (-i) 1024 max locked memory (kbytes, -l) 32 max memory size (kbytes, -m) unlimited open files (-n) 1024 pipe size(512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 stack size (kbytes, -s) 10240 cpu time (seconds, -t) unlimited max user processes (-u) 63484 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited Here is the error message: 050209 20:41:18 mysqld started 050209 20:41:18 [Warning] Asked for 196608 thread stack, but got 126976 050209 20:41:18 InnoDB: Fatal error: cannot allocate 2147500032 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 34049176 bytes. Operating system errno: 12 InnoDB: Cannot continue operation! InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. InnoDB: Fatal error: cannot allocate the memory for the buffer pool 050209 20:41:18 [ERROR] Can't init databases 050209 20:41:18 [ERROR] Aborting I have also checked the archive and it looks like other people also having similar problem, but I couldn't find the answer. Is there any memory limit when it is run on linux? What can I do so MySQL can run with = 2GB RAM? Thank you in advance. Regards, --bk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Qcache and read data from master
Hi, I noticed the following behaviour on mysql 4.1.8 and two servers; to setup replication, I did the required grant on the master, and choose to use LOAD DATA FROM MASTER on the slave. As we monitor the Qcache usage on the master, it happend that Qcache was reset to 0 Queries during this operation. I would like to know if this behaviour is expected, and would be interrested if someone can explain me why this is necessary ? Olivier -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Performance of Sockets vs. TCP/IP connections on localhost?
Hi all! Simple question: What is fastest when doing a connection to a local machine? TCP/IP or connecting via the socket? the application is a ACL helper program under Squid which makes a persistent connection to the database (100 parallel programs runs on the machine each connecting to the MySQL database) Regards -- Lasse Laursen · VP, Hosting Technology NetGroup Processing Aps Phone: +45 3370 1526 · Fax: +45 3313 0066 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Qcache and read data from master
I believe it may be because the tables in the Query cache have been modified causing the cached results to become invalid. Olivier Kaloudoff wrote: Hi, I noticed the following behaviour on mysql 4.1.8 and two servers; to setup replication, I did the required grant on the master, and choose to use LOAD DATA FROM MASTER on the slave. As we monitor the Qcache usage on the master, it happend that Qcache was reset to 0 Queries during this operation. I would like to know if this behaviour is expected, and would be interrested if someone can explain me why this is necessary ? Olivier -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
slow query, how can i imporve it?
Hi, I have a question regarding speed of the query. In my application i am useing Mysql 4.0.20a-nt. I have 10 tables and each table contains 400 records and also 61 columns. I already created indexs on six column which are important for me. i fired the query on tables through servlet(thread). I fired same query on all tables on same time, but it has take time to getting result . allmost 7 to 10 minute . so please tell me how can i imporve speed of the Mysql or query? so it will take less time ! Thanks Shailendra -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance of Sockets vs. TCP/IP connections on localhost?
Lasse Laursen [EMAIL PROTECTED] wrote on 09/02/2005 13:24:27: Hi all! Simple question: What is fastest when doing a connection to a local machine? TCP/IP or connecting via the socket? the application is a ACL helper program under Squid which makes a persistent connection to the database (100 parallel programs runs on the machine each connecting to the MySQL database) From the Connector/J documentation: Named pipes only work when connecting to a MySQL server on the same physical machine as the one the JDBC driver is being used on. In simple performance tests, it appears that named pipe access is between 30%-50% faster than the standard TCP/IP access. Alec Cawley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: slow query, how can i imporve it?
[snip] I have a question regarding speed of the query. In my application i am useing Mysql 4.0.20a-nt. I have 10 tables and each table contains 400 records and also 61 columns. I already created indexs on six column which are important for me. i fired the query on tables through servlet(thread). I fired same query on all tables on same time, but it has take time to getting result . allmost 7 to 10 minute . so please tell me how can i imporve speed of the Mysql or query? [/snip] Faster hardware and/or more memory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Memory limit?
On Wed, 9 Feb 2005, Batara Kesuma wrote: Hi, I try to install MySQL 4.1.9 (official RPM from mysql.com). My machine is running linux 2.6.9, and it has 4GB of RAM. The problem is MySQL won't start if I set innodb_buffer_pool_size to = 2GB. Here is my ulimit. Are you trying this on a 32-bit cpu machine? [EMAIL PROTECTED] mysql]# ulimit -a core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited file size (blocks, -f) unlimited pending signals (-i) 1024 max locked memory (kbytes, -l) 32 max memory size (kbytes, -m) unlimited open files (-n) 1024 pipe size(512 bytes, -p) 8 POSIX message queues (bytes, -q) 819200 stack size (kbytes, -s) 10240 cpu time (seconds, -t) unlimited max user processes (-u) 63484 virtual memory (kbytes, -v) unlimited file locks (-x) unlimited Here is the error message: 050209 20:41:18 mysqld started 050209 20:41:18 [Warning] Asked for 196608 thread stack, but got 126976 050209 20:41:18 InnoDB: Fatal error: cannot allocate 2147500032 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 34049176 bytes. Operating system errno: 12 InnoDB: Cannot continue operation! InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. InnoDB: Fatal error: cannot allocate the memory for the buffer pool 050209 20:41:18 [ERROR] Can't init databases 050209 20:41:18 [ERROR] Aborting I have also checked the archive and it looks like other people also having similar problem, but I couldn't find the answer. Is there any memory limit when it is run on linux? What can I do so MySQL can run with = 2GB RAM? Thank you in advance. Regards, --bk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup/synchronize tables/db web-local server
On Wed, 9 Feb 2005 15:48:12 +0200, Paul Godard [EMAIL PROTECTED] wrote: On Wed, 9 Feb 2005 13:10:16 +0200, Paul Godard [EMAIL PROTECTED] wrote: Hi I use mysql/php on several client web sites (each client has its own db). For development purpose, I have a local web server with php/mysql. When a web site is still in development, this is the master db, even if a copy has been uploaded on the live web server at my isp. When a site is completed, the web db becomes the master (as clients and visitors are updating data on the web db). My problem is that I would like to keep the local db synchronized with the web db (at least for the tables that are regularly updated on the web). Manually I would do a dump (data export only) of these web tables onto a local file, then empty the local equivalent tables and then running the sql statements of the dump file (insert). I don't do a systematic download of the whole web db as it is sometimes too big and besides not all the tables are updated by the client/visitors. My idea is to dynamically build a mysql script via php that can do the job for each client web sites for each of the tables contained in an array and be executed from a simple click on a button on the local server of course. As I am not a mysql/php guru, I would appreciate suggestions to do this as simple as possible. Have you thought about using mysqldump piped to the local DB? For example: mysqldump -h server_name_or_ip -u username -ppassword db_name table1 [table2 ...] mysql -u localuser -p db_name This is (I believe) shown in the on-line manual under mysqldump. It fulfills my needs. You could even set this up in cron (scheduled tasks). Hi Thanks for the suggestion. Do I have to execute that command shell on my local server (because of firewall my local server is not on line)? Is it possible to trigger mysqldump from a php script? The idea is to have a button on the local site to sync the local db to the web db. -- The piped mysqldump can work in either direction as mysql will also accept a host parameter and php can run shell commands. However, I guess your firewall could be an issue if it prevents connections to the mysql port. There was a thread I believe on the PHP list about doing a mysqldump and emailing the dump file and then processing that dump file on the recipients box. Coz -- CozWeb Solutions Ltd http://www.cozweb.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merge vs multiple innodb performance
Marcin Lewandowski [EMAIL PROTECTED] wrote on 02/08/2005 05:29:39 PM: Chuck Herrick napisa(a): 200 - 400 tables is too many. Is it too many for merge, innodb or both? Try having one CUSTOMERS table. You know who is logged in, so you can use that information in a WHERE clause. Yes, but If somebody would find a password (maybe using brute-force attack) to one account, could delete data of other users... -- Marcin Lewandowski [ mailto:[EMAIL PROTECTED] gg:188068 jid:[EMAIL PROTECTED] ] [ http://www.nosoftwarepatents.com/pl/m/intro/index.html ] No, they couldn't if you don't give them direct rights to edit that table. Make them use your PHP interface to set and reset their passwords. That way you have a secret password in your app that allows only you to access that table and they will only be able to change the records you let them change. If you don't allow them to edit EVERYONE's information, they won't be able to. I agree with the other respondent. One table per user is excessive, especially when we are talking about login information (one row per table?). Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Problems installing MySql...
Steve Grosz [EMAIL PROTECTED] wrote on 02/08/2005 06:04:48 PM: I already have a version of MySql 4.1.17 running on a different server, but am trying to setup v.4.0.23 to see if it will allow me to use PHPBB's software. I'm finding that 4.0.23 isn't as easy to set up as the later versions. I modified the my.ini file and put it in the \windows directory for Windows Server 2003. When I try to use the MySql Administator, and put in the server name, root account and password defined in the my.ini file, it doesn't connect, using 'localhost'. What am I doing incorrectly? Steve Did you remember to cross your fingers and gnash your teeth? What about the blue button, did you press it? (Just kidding) First off, you left out a lot of useful information about your situation: How did you install the 4.0.23 (zip, msi,...)? Have you verified that the server is running? What other connection methods have you tried? Did they work? Have you been following the documented installation procedure ( http://dev.mysql.com/doc/mysql/en/installing.html)? If you have, what step are you on? Have you looked at the Troubleshooting guide ( http://dev.mysql.com/doc/mysql/en/problems.html)? Besides posting to this list, what other things have you tried? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Multiple table join help
E SA [EMAIL PROTECTED] wrote on 02/08/2005 06:40:40 PM: All, I have done some reading and research; however, I seem to be at a loss... And this time, I am not sure how to ask Google... Here is the problem: Table A: id INT valuevarchar (10) Table B: id INT valuevarchar (10) Table C: id INT AINT BINT DINT Table D: id INT ... other_values Table C is in order to normalize values for A and B So, I want to be able to do a query to C that returns the id values of D in order to cross reference that table later. Since table C only knows the numeric values of the string, I need to be able to do so as part of the query by comparing C.a with A.id; however, I also need to compare with the value string... Now, I can do that with one table (A, for example): mysql select C.D from C, A - where C.a = A.id AND A.value='berry'; However, I am not sure how to add the next condition: - where C.b = B.id AND B.value='fruit'; That would allow me to obtain the values on C where A.value = berry and B.value = fruit; however using the numeric values of A.id and B.id There most be a simple solution... To add to the problem, I am using MySQL 4.0.15, so the multiple select would not work... Any help would be appreciated as I have little hair left!!! Beforehand, thank you for the help! If you lurk here long, you would find out that I am NOT a fan of the comma-separated JOIN construction. I prefer to EXPLICITLY declare my JOINS so that is how I will respond to your request. It's not invalid to do it the other way, I just feel it leaves the user open to more opportunities for mistakes. Also, the explicit form is the only way to declare the outer joins (LEFT JOIN or RIGHT JOIN). You can't do that with the comma-list method. Since you say that you only want rows from C that match the conditions you impose on the A and B tables, you should use INNER JOINs to lookup your d values from C (it's possible with the another type of join to achieve the same results but it won't be as efficient). SELECT C.d FROM C INNER JOIN A ON A.ID = C.a INNER JOIN B ON B.ID = C.b WHERE A.value='berry' AND B.value = 'fruit' However, you could save yourself a step and get the records straight from D (unless you need that list of C.d values for other purposes) SELECT D.ID, D.somefield, D.someotherfield,... FROM D INNER JOIN C ON D.ID = C.d INNER JOIN A on C.a = A.ID INNER JOIN B ON B.ID = C.b WHERE A.value='berry' AND B.value='fruit' There are lots of tutorials out there that teach the basics of JOINing tables. Hopefully this will get you started!! Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: slow query, how can i imporve it?
Shailendra Soni [EMAIL PROTECTED] wrote on 02/09/2005 08:28:36 AM: Hi, I have a question regarding speed of the query. In my application i am useing Mysql 4.0.20a-nt. I have 10 tables and each table contains 400 records and also 61 columns. I already created indexs on six column which are important for me. i fired the query on tables through servlet(thread). I fired same query on all tables on same time, but it has take time to getting result . allmost 7 to 10 minute . so please tell me how can i imporve speed of the Mysql or query? so it will take less time ! Thanks Shailendra Have you tried looking at this for ideas, too? http://dev.mysql.com/doc/mysql/en/query-speed.html Most of us start with an EXPLAIN of the query and work from there (see suggested reading). Check your table structures and, if the frequency of this query justifies it, an appropriate multi-column index (not multiple single-column indexes). Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: backup/synchronize tables/db web-local server
The mySQL command should read : mysql -u username -p[password] db_name dumpfile Note that you can give the password on the command line but it is best to get into the habit of just providing -p and typing in the password when prompted. BTW, when doing the mysqldump for tables that already exist in the destination database use --add-drop-table this will effectively drop the existing table before importing the new data. Coz On Wed, 9 Feb 2005 17:52:52 +0200, Paul Godard [EMAIL PROTECTED] wrote: The piped mysqldump can work in either direction as mysql will also accept a host parameter and php can run shell commands. However, I guess your firewall could be an issue if it prevents connections to the mysql port. There was a thread I believe on the PHP list about doing a mysqldump and emailing the dump file and then processing that dump file on the recipients box. Thanks again I couldn't find the thread but I manage to save the dump file on my local server (at the same location as the php script) by running a mysqldump on my local server to fetch a table on the web server. mysqldump -h server_ip -u username -p password db_name table1 filename.sql However I couldn't manage to create and insert data on the local server (the table1 being not there of course). I mean the part below didn't work. mysql -u localuser -p local password db_name I also tried to run mysql directly but with no success. mysql -e source filename.sql db_name or mysql db_name filename.sql However the operation worked via phpMyAdmin (read text file) directly. My conclusion is the firewall is not a problem but for some reason the syntax of the read dump part is not correct... Any idea why? -- Kind regards, Paul. Gondwana [EMAIL PROTECTED] http://www.gondwanastudio.com -- CozWeb Solutions Ltd http://www.cozweb.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to log some queries and not others
Hello, I have the following problem: I have a table with a blob field and turned on query logging. I'd like to log every query except inserts into table with a blob field because my log files grow very fast and I don't like to see binary data in my log files, while other logs are very informative and important. How can I do that? Best Regards, -- George Chelidze -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Aes Encryption
Hi, Aes Encryption requires a password (key) to access data, now where do we store this key? with the source code ? or in seperate database ? because any body who has the access to souce code can view the critical info or even if you store passwords in another database then it is not a big deal to get those passwords if source code is accessible. What i think Aes encryption reduce the risk but does not make data 100% safe because of source code dependency. Love Kumar Perl Develpment Team eBookers.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
key on the month portion of a date field
Short Version: is there any way to make an index for a date field that appears in the WHERE as a MONTH() argument? I have a table with some hundreds of thousands of rows already, and now i have the need to show upcoming birthdays to some users. the query uses WHERE MONTH(birthday).. the `birthday` field is of date (-00-00) type It is not too slow this way since i started the WHERE with lots of checks that cuts down to a medium of 200 rows that actualy gets to this check, but i feel uncorfotable to not use a index. So, is there any way to make an index for that kind of query, or i must re-estruct the table to have a month field? Thanks! Gabriel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: key on the month portion of a date field
use below: alter table table_name add index (birthday); Love Kumar .. [EMAIL PROTECTED] wrote: Short Version: is there any way to make an index for a date field that appears in the WHERE as a MONTH() argument? I have a table with some hundreds of thousands of rows already, and now i have the need to show upcoming birthdays to some users. the query uses WHERE MONTH(birthday).. the `birthday` field is of date (-00-00) type It is not too slow this way since i started the WHERE with lots of checks that cuts down to a medium of 200 rows that actualy gets to this check, but i feel uncorfotable to not use a index. So, is there any way to make an index for that kind of query, or i must re-estruct the table to have a month field? Thanks! Gabriel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] The information transmitted is intended only for the person or entity to whom it is addressed and may contain confidential and / or privileged Material. Any review, re-transmission, dissemination or other use of or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from your computer. Thank you for your understanding co-operation.
Re: key on the month portion of a date field
On Wed, 09 Feb 2005 17:24:10 +, love [EMAIL PROTECTED] wrote: alter table table_name add index (birthday); But would that index improve this kind of query? the docs talk about only direct matchs, like birthday now() or birthday between x and y. They're all full date values. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: key on the month portion of a date field
At 15:37 -0200 2/9/05, Gabriel B. wrote: On Wed, 09 Feb 2005 17:24:10 +, love [EMAIL PROTECTED] wrote: alter table table_name add index (birthday); But would that index improve this kind of query? the docs talk about only direct matchs, like birthday now() or birthday between x and y. They're all full date values. It won't help except for queries that test the value of birthday directly. As soon as you use the column in a calculation such as a function call, the index can't be used. To use month in indexed fashion, you could store dates as separate year, month, and date columns. But that might be more of a hassle than you want to deal with. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
breaking session start/stop datetime records on time/day bounds
Hi, I have a table full of activity records. Each record has a DATETIME field for when the activity started and one for when it ended. I want to post process this table and produce billing records from it. Billing records will have a link back to the activity record that produces them; i.e. there could be more than one billing record for each event. The reason for this is that there are different billing rates per event type and also different rates depending on the time of day [office hour rates are different than early morning, evening, night/weekend rates]. Let's say that office-hour-rates start from 8am - 4:59:59pm Monday - Friday. Everything else is a night/weekend rate. It is easy to get the records where the event start/stop DATETIMES are in the same day and are both in or out of the office hours range. it is fairly easy to get the records the event start/stop DATETIMES are in the same day and the start record is early morning and the stop record is business hours or the start record is business and the end os evening. Let's say that I have an activity record that starts at 6am. It continues all day until 9pm that same evening. I would want to prodauce three billing records: 06:00:00 - 07:59:59 08:00:00 - 16:59:59 17:00:00 - 21:00:00 What if the activity flows over a day change? More than one day cahnge? Should I just punt and return the activity DATETIMES as UNIX_TIMESTAMPs and do the date manipulation myslef? Dean...K... -- Dean Karres / karres at itg dot uiuc dot edu / www.itg.uiuc.edu Imaging Technology Group / Beckman Institute University of Illinois 405 North Mathews / Urbana, IL 61801 USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: key on the month portion of a date field
On Wed, 9 Feb 2005 15:17:36 -0200, Gabriel B. [EMAIL PROTECTED] wrote: It is not too slow this way since i started the WHERE with lots of checks that cuts down to a medium of 200 rows that actualy gets to this check, but i feel uncorfotable to not use a index. Isn't there a limit of 1 index per table in a query? If you're already using where statements to eliminate rows, I'm assuming that you're already using an index. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Test Message
Our mail server has been stopping emails to the list. This is a test message to see if it bounces again. Please ignore (and accept my apology for generating noise). David. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup/synchronize tables/db web-local server
SQLyog Enterprise's Database Sync Tool is the answer for your queries. www.webyog.com Rgds, Karam --- Paul Godard [EMAIL PROTECTED] wrote: Hi I use mysql/php on several client web sites (each client has its own db). For development purpose, I have a local web server with php/mysql. When a web site is still in development, this is the master db, even if a copy has been uploaded on the live web server at my isp. When a site is completed, the web db becomes the master (as clients and visitors are updating data on the web db). My problem is that I would like to keep the local db synchronized with the web db (at least for the tables that are regularly updated on the web). Manually I would do a dump (data export only) of these web tables onto a local file, then empty the local equivalent tables and then running the sql statements of the dump file (insert). I don't do a systematic download of the whole web db as it is sometimes too big and besides not all the tables are updated by the client/visitors. My idea is to dynamically build a mysql script via php that can do the job for each client web sites for each of the tables contained in an array and be executed from a simple click on a button on the local server of course. As I am not a mysql/php guru, I would appreciate suggestions to do this as simple as possible. Please copy the reply to my email address. Thanks in advance. -- Kind regards, Paul. Gondwana [EMAIL PROTECTED] http://www.gondwanastudio.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Meet the all-new My Yahoo! - Try it today! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup/synchronize tables/db web-local server
It is very easier with mysql replication concipt, this automatic replication from Master server to Slave server and real time. I wonder how you do not know about this. http://dev.mysql.com/doc/mysql/en/replication.html Love kumar .. [EMAIL PROTECTED] wrote: SQLyog Enterprise's Database Sync Tool is the answer for your queries. www.webyog.com Rgds, Karam --- Paul Godard [EMAIL PROTECTED] wrote: Hi I use mysql/php on several client web sites (each client has its own db). For development purpose, I have a local web server with php/mysql. When a web site is still in development, this is the master db, even if a copy has been uploaded on the live web server at my isp. When a site is completed, the web db becomes the master (as clients and visitors are updating data on the web db). My problem is that I would like to keep the local db synchronized with the web db (at least for the tables that are regularly updated on the web). Manually I would do a dump (data export only) of these web tables onto a local file, then empty the local equivalent tables and then running the sql statements of the dump file (insert). I don't do a systematic download of the whole web db as it is sometimes too big and besides not all the tables are updated by the client/visitors. My idea is to dynamically build a mysql script via php that can do the job for each client web sites for each of the tables contained in an array and be executed from a simple click on a button on the local server of course. As I am not a mysql/php guru, I would appreciate suggestions to do this as simple as possible. Please copy the reply to my email address. Thanks in advance. -- Kind regards, Paul. Gondwana [EMAIL PROTECTED] http://www.gondwanastudio.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Meet the all-new My Yahoo! - Try it today! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] The information transmitted is intended only for the person or entity to whom it is addressed and may contain confidential and / or privileged Material. Any review, re-transmission, dissemination or other use of or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from your computer. Thank you for your understanding co-operation.
Re: breaking session start/stop datetime records on time/day bounds
Dean Karres [EMAIL PROTECTED] wrote on 02/09/2005 12:46:46 PM: Hi, I have a table full of activity records. Each record has a DATETIME field for when the activity started and one for when it ended. I want to post process this table and produce billing records from it. Billing records will have a link back to the activity record that produces them; i.e. there could be more than one billing record for each event. The reason for this is that there are different billing rates per event type and also different rates depending on the time of day [office hour rates are different than early morning, evening, night/weekend rates]. Let's say that office-hour-rates start from 8am - 4:59:59pm Monday - Friday. Everything else is a night/weekend rate. It is easy to get the records where the event start/stop DATETIMES are in the same day and are both in or out of the office hours range. it is fairly easy to get the records the event start/stop DATETIMES are in the same day and the start record is early morning and the stop record is business hours or the start record is business and the end os evening. Let's say that I have an activity record that starts at 6am. It continues all day until 9pm that same evening. I would want to prodauce three billing records: 06:00:00 - 07:59:59 08:00:00 - 16:59:59 17:00:00 - 21:00:00 What if the activity flows over a day change? More than one day cahnge? Should I just punt and return the activity DATETIMES as UNIX_TIMESTAMPs and do the date manipulation myslef? Dean...K... -- Dean Karres / karres at itg dot uiuc dot edu / www.itg.uiuc.edu Imaging Technology Group / Beckman Institute University of Illinois 405 North Mathews / Urbana, IL 61801 USA I believe your options depend on the language you are using for the post-processing. If your language has datetime datatypes then stick with those. If not, use the unix_timestamp values and integer math (mostly). I have a hard time understanding if you are having problems with the data, the database, or your program design BTW - There is at least one case you didn't mention. What happens if an activity is still active at the time you post-process? Do you break it at the last midnight or at the point in time that you are processing? Really, this sounds more like a program design and logic flow issue and less of a MySQL issue. If you need help getting at data or forming your queries we will be more than happy to help but the sort of logic you are asking about probably belongs in your application code, not your SQL statements. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: key on the month portion of a date field
Remember, a low cardinality index will possibly be ignored by the optimizer and an index on month will never have a cardinality of more than 12. For testing purposes, you might try added a column for month and populating it off your current data. update the_table set the_field=MONTH(the_field) Then, add an index on that column and test your queries against the new index. I'd be surprised if you saw much increase in speed, especially as your data set grows. Greg On Wednesday 09 February 2005 09:17 am, Gabriel B. wrote: Short Version: is there any way to make an index for a date field that appears in the WHERE as a MONTH() argument? I have a table with some hundreds of thousands of rows already, and now i have the need to show upcoming birthdays to some users. the query uses WHERE MONTH(birthday).. the `birthday` field is of date (-00-00) type It is not too slow this way since i started the WHERE with lots of checks that cuts down to a medium of 200 rows that actualy gets to this check, but i feel uncorfotable to not use a index. So, is there any way to make an index for that kind of query, or i must re-estruct the table to have a month field? Thanks! Gabriel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to log some queries and not others
Cron hack? have a console running: tail -f hostname.log | grep tablename filter.log -or- tail -f hostname.log | grep -v insert into blobtablename filter.log then have a cronjob every X minutes running: echohostname.log To truncate the full querylog from getting too big? Not pretty.. On Wed, 09 Feb 2005 19:52:33 +0300, George Chelidze [EMAIL PROTECTED] wrote: Hello, I have the following problem: I have a table with a blob field and turned on query logging. I'd like to log every query except inserts into table with a blob field because my log files grow very fast and I don't like to see binary data in my log files, while other logs are very informative and important. How can I do that? Best Regards, -- George Chelidze -- 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: bcc with mysql
unni krishnan napisa(a): hi, I want to connect to mysql using borland c. The version is bcc5.0 and for mysql it is mysql4.0.13 The compilation is ok... but its showing linker error like unresolved external mysql_init referenced from module ... what is the problem??? How to specify the correct library... If anybody knows the solutions, pls reply... unnikrishnan I'm using mysql4 with bcb6. You should: 1. download mysql. unpack it 2. Copy *.h files from includes dir to bcb include dir. 3. Copy libmysql.dll and libmysql.lib to your app's dir. 4. Convert libmysql.lib using coff2omf tool 5. My app wasn't compiling succesfully without NO_CLIENT_LONG_LONG defined 6. Add .lib to your project 7. #include winsock.h before #include mysql.h 8. In future, use google -- Marcin Lewandowski [ mailto:[EMAIL PROTECTED] gg:188068 jid:[EMAIL PROTECTED] ] [ http://www.nosoftwarepatents.com/pl/m/intro/index.html ] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to log some queries and not others
At 19:52 +0300 2/9/05, George Chelidze wrote: Hello, I have the following problem: I have a table with a blob field and turned on query logging. I'd like to log every query except inserts into table with a blob field because my log files grow very fast and I don't like to see binary data in my log files, while other logs are very informative and important. How can I do that? See: http://dev.mysql.com/doc/mysql/en/set-option.html Check the descriptions for SQL_LOG_BIN and SQL_LOG_OFF. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backup/synchronize tables/db web-local server
Well I know about it and I am working on MySQL Replication, the point is that you dont have control over replicatation when your MySQL server is installed by your ISP on shared server, In that case it is simply not possible :) Moreover, the simple setup I have, I prefer this then the nittygritties of replication. Agreed, if you have a heavy duty setup then the only answer is MySQL replication. Karam --- love [EMAIL PROTECTED] wrote: It is very easier with mysql replication concipt, this automatic replication from Master server to Slave server and real time. I wonder how you do not know about this. http://dev.mysql.com/doc/mysql/en/replication.html Love kumar .. [EMAIL PROTECTED] wrote: SQLyog Enterprise's Database Sync Tool is the answer for your queries. www.webyog.com Rgds, Karam --- Paul Godard [EMAIL PROTECTED] wrote: Hi I use mysql/php on several client web sites (each client has its own db). For development purpose, I have a local web server with php/mysql. When a web site is still in development, this is the master db, even if a copy has been uploaded on the live web server at my isp. When a site is completed, the web db becomes the master (as clients and visitors are updating data on the web db). My problem is that I would like to keep the local db synchronized with the web db (at least for the tables that are regularly updated on the web). Manually I would do a dump (data export only) of these web tables onto a local file, then empty the local equivalent tables and then running the sql statements of the dump file (insert). I don't do a systematic download of the whole web db as it is sometimes too big and besides not all the tables are updated by the client/visitors. My idea is to dynamically build a mysql script via php that can do the job for each client web sites for each of the tables contained in an array and be executed from a simple click on a button on the local server of course. As I am not a mysql/php guru, I would appreciate suggestions to do this as simple as possible. Please copy the reply to my email address. Thanks in advance. -- Kind regards, Paul. Gondwana [EMAIL PROTECTED] http://www.gondwanastudio.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Meet the all-new My Yahoo! - Try it today! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] The information transmitted is intended only for the person or entity to whom it is addressed and may contain confidential and / or privileged Material. Any review, re-transmission, dissemination or other use of or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from your computer. Thank you for your understanding co-operation. __ Do you Yahoo!? All your favorites on one personal page Try My Yahoo! http://my.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Warning: V3 DSA signature: NOKEY, key ID 5072e1f5
When I RPM upgrade MySQL on Fedora Core 3 from the installed version 3.23 to version 4.1.9, the rpm command yeilds the following warning: Warning: VMySQL-server-4.1.9-0.i386.rpm: 3 DSA signature: NOKEY, key ID 5072e1f5 What does this mean? Should I ignore this warning? Should I expect problems with MySQL as a result of this warning? How do I RPM without this warning? thanks -- Chuck Herrick mailto:[EMAIL PROTECTED] 512 289 0926 (cell) 830 839 4437 (home) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems installing MySql...
reconstituted from a munged response (original post) Steve Grosz [EMAIL PROTECTED] wrote on 02/08/2005 06:04:48 PM: I already have a version of MySql 4.1.17 running on a different server, but am trying to setup v.4.0.23 to see if it will allow me to use PHPBB's software. I'm finding that 4.0.23 isn't as easy to set up as the later versions. I modified the my.ini file and put it in the \windows directory for Windows Server 2003. When I try to use the MySql Administator, and put in the server name, root account and password defined in the my.ini file, it doesn't connect, using 'localhost'. What am I doing incorrectly? Steve Did you remember to cross your fingers and gnash your teeth? What about the blue button, did you press it? (Just kidding) First off, you left out a lot of useful information about your situation: How did you install the 4.0.23 (zip, msi,...)? Have you verified that the server is running? What other connection methods have you tried? Did they work? Have you been following the documented installation procedure ( http://dev.mysql.com/doc/mysql/en/installing.html)? If you have, what step are you on? Have you looked at the Troubleshooting guide ( http://dev.mysql.com/doc/mysql/en/problems.html)? Besides posting to this list, what other things have you tried? Shawn Green Database Administrator Unimin Corporation - Spruce Pine Steve's Reply Steve Grosz [EMAIL PROTECTED] wrote on 02/09/2005 02:01:14 PM: I guess I didn't hold my mouth right.hate it when that happens! :-) I installed from a ZIP file, and the Admin tool does show that the server is running. I have tried to telnet into that server, and it fails, but that may be due to the firewall. Currently I am on step #2.3.10 of the install process. If I didn't answer all your questions, let me know! Thanks for the help!! Steve Just curious, if you are being faithful to the installation docs How is the Admin tool running? You aren't to that step yet... ;-) Anyway, it's odd that you cannot telnet to a server when you say it is up. Note: You DO NOT get a normal telnet session, all you should be able to see when you telnet to a MySQL server is the version number and some garbage characters. That tells you that you have port-to-port connectivity from your machine to the server. It would be prohibitively difficult to use telnet (read: practically impossible) to work directly with a MySQL server manually through telnet. How did you start the server, is it installed as a service or are you running it manually from a command prompt? Is your problem connecting to the server through mysqladmin or getting the server running? Can you connect through the command line client mysql.exe? What error messages are you getting and from what program? And one last thing... please CC: the list on all responses. It will not only help the next dba through a similar issue but it will also make sure you get more responses. In the event that I became unavailable or gave you bad advice, anyone else on the list could kick in and help get you going, too. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Copying Database from One Hard Drive to Another
Yikes...I've been replying to myself! I forgot that when I reply to messages from the MySQL newsgroup on this e-mail account, it doesn't include mysql@lists.mysql.com, for some odd reason. At any rate, I wanted to say that I have just ONE file type left - FRM. My MYD and MYI files have both vanished. But I took another look and discovered that SOME tables are represented by all three files, and when I paste them into my new MySQL Data folder, they work just fine. I also discovered the following files in my backup folder: ibdata1...215,040 KB ib_logfile1...5,120 KB ib_logfile0...5,120 KB ib_arch_log_0...25 KB Can these somehow be used to reconstitute lost database tables? The situation isn't critical, as I can recreate most of these tables from my original data, which is on spreadsheets. But recreating all the fields and keys would obviously be a pain in the butt; I had over 100 tables. Thanks. __ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Copying Database from One Hard Drive to Another
InnoDB files can not be copied from one machine to another like the MyISAM files. If you did not dump the data or you do not have an archived backup you will more than likely have to recreate the table structures. David Blomstrom wrote: Yikes...I've been replying to myself! I forgot that when I reply to messages from the MySQL newsgroup on this e-mail account, it doesn't include mysql@lists.mysql.com, for some odd reason. At any rate, I wanted to say that I have just ONE file type left - FRM. My MYD and MYI files have both vanished. But I took another look and discovered that SOME tables are represented by all three files, and when I paste them into my new MySQL Data folder, they work just fine. I also discovered the following files in my backup folder: ibdata1...215,040 KB ib_logfile1...5,120 KB ib_logfile0...5,120 KB ib_arch_log_0...25 KB Can these somehow be used to reconstitute lost database tables? The situation isn't critical, as I can recreate most of these tables from my original data, which is on spreadsheets. But recreating all the fields and keys would obviously be a pain in the butt; I had over 100 tables. Thanks. __ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems installing MySql...
Steve Grosz [EMAIL PROTECTED] wrote on 02/09/2005 03:43:22 PM: I downloaded the MySql Administrator and simply run that. and dragged the icon to the startup menu so it loads MySql on startup. Not entirely sure how to load MySql as a 'service' on Win2003 Server. The only message I get, and this is when trying to administer the server through the Admin tool, is that it can't connect to 'localhost' with 'root' and 'password' defined in my.ini Steve snip - see previous posts in this thread for history Since you are on installation step 2.3.10, I can only guess that you haven't started your MySQL server. There are normally 2 programs you have to have going to use MySQL. The server and a client. I don't think you have your server going. Follow the directions from 2.3.10 on and you should complete your installation just fine. (I would skip 2.3.11 since you are going to be running MySQL as a service anyway) 2.3.13 shows how to test your setup. 2.3.14 has troubleshooting advice. IF you finish the installation procedure and still cannot connect. Come back to the list again and we will be able to help you out some more, OK? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Slow Replication
Hi all, We use MySQL as a database backend on a portal site. We have a two database server setup (one master, one slave). The master is a PIV 3,2 GHz., 2 GB Ram and a 80GB Raid-1 system. The slave is a PIV 3.2 GHz., 4 GB Ram and a 80GB Raid-0 system. Both run on MySQL 4.1.9 and only use InnoDB. Even though the slave is a bigger system and is quite fast with selects, it always falls behind in replication (Seconds behind the server keeps growing at high-load times). Is there any way to speed up the replication a little more? I have already tried a whole lot of things but have never been successful, yet :-( Here is a snapshot of the configuration: skip-name-resolve key_buffer=1M max_allowed_packet=1M thread_cache_size=128 thread_stack=128K table_cache=1024 join_buffer_size=5M read_buffer_size=5M sort_buffer_size=5M thread_concurrency=4 query_cache_size = 32M query_cache_limit = 1M query_cache_type = 2 max_connections=900 innodb_data_file_path=ibdata1:2G:autoextend innodb_buffer_pool_size=1200M innodb_additional_mem_pool_size=20M P.S.: I hope I have given you enough information - it's my post on the list...;-) I appreciate your help, Hannes Rohde -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RETURNING Keyword?
Good Afternoon, In Oracle there is a keyword called RETURNING, where I can do an insert and specify what row to return, this helps with autoincrement fields; How can I achieve this in MySQL? Thanks a bunch!! -- Justin W. Burger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Experience with MySQL 5
Hello, Does anyone has experience with MySQL 5.x? How reliable is it? Is it compatible with JDBC, PHP and other drivers and connectors? Thank you, C.F. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Copying Database from One Hard Drive to Another
Ah, I see. So the tables that are represented by all three files are presumably MyISAM files, right? --- Victor Pendleton [EMAIL PROTECTED] wrote: InnoDB files can not be copied from one machine to another like the MyISAM files. If you did not dump the data or you do not have an archived backup you will more than likely have to recreate the table structures. David Blomstrom wrote: Yikes...I've been replying to myself! I forgot that when I reply to messages from the MySQL newsgroup on this e-mail account, it doesn't include mysql@lists.mysql.com, for some odd reason. At any rate, I wanted to say that I have just ONE file type left - FRM. My MYD and MYI files have both vanished. But I took another look and discovered that SOME tables are represented by all three files, and when I paste them into my new MySQL Data folder, they work just fine. I also discovered the following files in my backup folder: ibdata1...215,040 KB ib_logfile1...5,120 KB ib_logfile0...5,120 KB ib_arch_log_0...25 KB Can these somehow be used to reconstitute lost database tables? The situation isn't critical, as I can recreate most of these tables from my original data, which is on spreadsheets. But recreating all the fields and keys would obviously be a pain in the butt; I had over 100 tables. Thanks. __ Do you Yahoo!? Yahoo! Mail - Helps protect you from nasty viruses. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Mail - You care about security. So do we. http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Experience with MySQL 5
I've been running it for a while. Admittedly, I don't do anything truly heavy with it -- it is part of my non-critical dev stuff. I've had no issues with it, reliability wise, running it as the backend to a Tomcat 5 install and about to switch it over to a Tomcat 5.5. Basically, on that box, I keep everything as the latest binaries available. I haven't worked with it through PHP, but I certainly have through JDBC, and the ODBC. No issues to date, really. Martin C.F. Scheidecker Antunes wrote: Hello, Does anyone has experience with MySQL 5.x? How reliable is it? Is it compatible with JDBC, PHP and other drivers and connectors? Thank you, C.F. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication issue: I/O thread dies immediately after START SLAVE with no logged errors
Hi all. Sorry if you get this twice -- it was posted to mysql-replication earlier, but it doesn't look like that list is really used. I'm having a problem with my replication setup. This is my first time setting up replication, so this may be a simple problem. I'm using one master and one slave, both running debian-testing, and they both have brand new 4.1.9 mysql installs (via apt-get). The problem is that each time I do a START SLAVE, the I/O thread dies almost immediately. I can see it running only if I do START SLAVE; SHOW SLAVE STATUS\G on a single line. The master's log shows the following each time I START SLAVE or START SLAVE IO_THREAD: 6 Connect slave@IP on 6 Query SELECT UNIX_TIMESTAMP() 6 Query SHOW VARIABLES LIKE 'SERVER_ID' 6 Query SELECT @@GLOBAL.COLLATION_SERVER 6 Query SELECT @@GLOBAL.TIME_ZONE 6 Query SHOW SLAVE HOSTS 6 Quit There are no error messages in the .err file on either server, even with --log-warnings on both. I've added everything appropriate that I've been able to find to my.cnf on each server (see below). If anyone has any suggestions, please let me know -- I've been looking through docs and mailing lists for quite some time now, with no luck. Thanks, Tierney Here's what I did to set up replication: I added / changed a number of fields in my.cnf on both machines (see below). Master: Started mysqld. Created a new database, a new table, and put a single row in it. Created a slave account: GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' IDENTIFIED BY 'x' Locked the database: FLUSH TABLES WITH READ LOCK Tar'd the data_dir/replicated directory and transferred it to the slave. Checked the binlog file and position and unlocked the database: SHOW MASTER STATUS UNLOCK TABLES Slave: Started mysqld. Set the master: CHANGE MASTER TO MASTER_HOST='IP', MASTER_USER='slave', MASTER_PASSWORD='x', MASTER_LOG_FILE='recorded value', MASTER_LOG_POS=recorded value; Began replication: START SLAVE; Master's my.cnf (comment lines removed): [client] port= 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice= 0 [mysqld] user= mysql pid-file= /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port= 3306 log = /var/log/mysql/mysql.log basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language= /usr/share/mysql/english skip-external-locking old-passwords = 1 key_buffer = 16M max_allowed_packet = 16M thread_stack= 128K query_cache_limit = 1048576 query_cache_size= 26214400 query_cache_type= 1 server-id = 1 log-bin = /var/log/mysql/mysql-bin.log binlog-do-db= replicated log-warnings binlog-ignore-db= mysql binlog-ignore-db= test [mysqldump] quick quote-names max_allowed_packet = 16M [mysql] [isamchk] key_buffer = 16M Slave's my.cnf (comment lines removed) [client] port= 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice= 0 [mysqld] user= mysql pid-file= /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port= 3306 log = /var/log/mysql/mysql.log basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language= /usr/share/mysql/english skip-external-locking old-passwords = 1 key_buffer = 16M max_allowed_packet = 16M thread_stack= 128K query_cache_limit = 1048576 query_cache_size= 26214400 query_cache_type= 1 server-id = 2 replicate-do-db = replicated replicate-ignore-db = mysql replicate-ignore-db = test log-warnings log-bin = /var/log/mysql/mysql-bin.log [mysqldump] quick quote-names max_allowed_packet = 16M [mysql] [isamchk] key_buffer = 16M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Experience with MySQL 5
It work with php too (at least with phpMyAdmin) Martin ha scritto: I've been running it for a while. Admittedly, I don't do anything truly heavy with it -- it is part of my non-critical dev stuff. I've had no issues with it, reliability wise, running it as the backend to a Tomcat 5 install and about to switch it over to a Tomcat 5.5. Basically, on that box, I keep everything as the latest binaries available. I haven't worked with it through PHP, but I certainly have through JDBC, and the ODBC. No issues to date, really. Martin C.F. Scheidecker Antunes wrote: Hello, Does anyone has experience with MySQL 5.x? How reliable is it? Is it compatible with JDBC, PHP and other drivers and connectors? Thank you, C.F. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Header files
Dears, I have started Linux-programming with C++. I need to header files's name of MySQL. Please give that's URL. Yours,Mohsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Header files
Hi Mohsen, If you install the client libraries from http://www.mysql.com for the version you wish, this will install the header files in the mysql/include directory. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mohsen Pahlevanzadeh Sent: Thursday, 10 February 2005 9:00 AM To: mysql@lists.mysql.com Subject: Header files Dears, I have started Linux-programming with C++. I need to header files's name of MySQL. Please give that's URL. Yours,Mohsen -- 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: Header files
dear member, My problem is name of those.When i wish to apply a function,I don't know name of that's header file.I need to a document for it. --Mohsen On Thursday 10 February 2005 02:06, Logan, David (SST - Adelaide) wrote: Hi Mohsen, If you install the client libraries from http://www.mysql.com for the version you wish, this will install the header files in the mysql/include directory. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Mohsen Pahlevanzadeh Sent: Thursday, 10 February 2005 9:00 AM To: mysql@lists.mysql.com Subject: Header files Dears, I have started Linux-programming with C++. I need to header files's name of MySQL. Please give that's URL. Yours,Mohsen -- 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]
Daily Incremental Backups on Mysql
Is it possible to do daily incremental backups on mysql? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Daily Incremental Backups on Mysql
Jerry Swanson wrote: Is it possible to do daily incremental backups on mysql? Thanks Yes. mysqldump will give you a starting point, and the binary transaction logs give you your incremental backups - copy these somewhere each day - onto a backup tape or something. Read the documentation for mysqldump and for processing the binary transaction logs. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
(newbie) Problems INSERT'ing field type SET
Hi, I'm new to MySQL and at this point, I'm just trying to insert rows of data into a table. When I insert a row of data that includes a field of type set (corresponding to an HTML set of checkbox answers), only the first item in the list gets inserted into the row. For example, I have a table where one column, called subject, is of type set. During one INSERT, the field subject corresponded to the values family, money. After I did the INSERT, the only thing that showed up in the subjects field was family. If I did it again with a different list as values (eg, jobs, school, parents), only jobs would show up in the table. Here's the syntax: INSERT into my_table (name, subjects) VALUES (Joyce, family, money) Or, using a command line interface: mysql insert - into my_table - (name, subjects) - VALUES - (Joyce, family, money) - ; The insert goes fine, but in the subjects column, only family is in the field. Everything else in the row gets into the table with no problems. And there is no error message - as far as the MySQL server is concerned, everything went fine. I've looked at different doc sites about MySQL to see if you have to format set data differently from other field types during INSERT, but have found nothing. Any suggestions?? Thanks, J. Wermont -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Compound Order By
I'd like to do a dual order by. First, I want to order by City, then I want to order name. That way, the names within the city are alphabetized. I can easily do an order by City into a temp table, then order by name from within the temp table. Is this the best way? Is this the only way? Just wonderin' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Memory limit?
Hi Tobias, On Wed, 9 Feb 2005 14:48:16 +0100 (CET) Tobias Asplund [EMAIL PROTECTED] wrote: I try to install MySQL 4.1.9 (official RPM from mysql.com). My machine is running linux 2.6.9, and it has 4GB of RAM. The problem is MySQL won't start if I set innodb_buffer_pool_size to = 2GB. Here is my ulimit. Are you trying this on a 32-bit cpu machine? Sorry I forgot to mention. Yes, it is a 32-bit CPU machine. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Compound Order By
Does order by City,name help? - Original Message - From: Robert Pruitt [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, February 10, 2005 10:11 AM Subject: Compound Order By I'd like to do a dual order by. First, I want to order by City, then I want to order name. That way, the names within the city are alphabetized. I can easily do an order by City into a temp table, then order by name from within the temp table. Is this the best way? Is this the only way? Just wonderin' -- 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: Compound Order By
Hi, I'd like to do a dual order by. First, I want to order by City, then I want to order name. That way, the names within the city are alphabetized. I can easily do an order by City into a temp table, then order by name from within the temp table. Is this the best way? Is this the only way? ORDER BY city, name http://dev.mysql.com/doc/mysql/en/sorting-rows.html I hope you're wearing a flame-retardant suit, because the flames are about to fly. :) Regards, Jeremy -- Jeremy Cole Technical Yahoo - MySQL (Database) Geek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Compound Order By
Well, I think I'll just stick with the story that I wuz jes' tryin' to keep everbody on their toes It's the only way out I can see. Thank-you. :) Jeremy Cole wrote: Hi, I'd like to do a dual order by. First, I want to order by City, then I want to order name. That way, the names within the city are alphabetized. I can easily do an order by City into a temp table, then order by name from within the temp table. Is this the best way? Is this the only way? ORDER BY city, name http://dev.mysql.com/doc/mysql/en/sorting-rows.html I hope you're wearing a flame-retardant suit, because the flames are about to fly. :) Regards, Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: (newbie) Problems INSERT'ing field type SET
Your problem seems to be one of spaces. This query tries to set the SET to TWO values, family and money (notice the space before money INSERT into my_table (name, subjects) VALUES (Joyce, family, money) Try it without the space: INSERT into my_table (name, subjects) VALUES (Joyce, family,money) Chris J Wermont wrote: Hi, I'm new to MySQL and at this point, I'm just trying to insert rows of data into a table. When I insert a row of data that includes a field of type set (corresponding to an HTML set of checkbox answers), only the first item in the list gets inserted into the row. For example, I have a table where one column, called subject, is of type set. During one INSERT, the field subject corresponded to the values family, money. After I did the INSERT, the only thing that showed up in the subjects field was family. If I did it again with a different list as values (eg, jobs, school, parents), only jobs would show up in the table. Here's the syntax: INSERT into my_table (name, subjects) VALUES (Joyce, family, money) Or, using a command line interface: mysql insert - into my_table - (name, subjects) - VALUES - (Joyce, family, money) - ; The insert goes fine, but in the subjects column, only family is in the field. Everything else in the row gets into the table with no problems. And there is no error message - as far as the MySQL server is concerned, everything went fine. I've looked at different doc sites about MySQL to see if you have to format set data differently from other field types during INSERT, but have found nothing. Any suggestions?? Thanks, J. Wermont -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RETURNING Keyword?
Justin Burger wrote: Good Afternoon, In Oracle there is a keyword called RETURNING, where I can do an insert and specify what row to return, this helps with autoincrement fields; How can I achieve this in MySQL? If you only want to know the value of the last inserted autoinc field, use LAST_INSERT_ID: http://dev.mysql.com/doc/mysql/en/information-functions.html Thanks a bunch!! -- Justin W. Burger HTH, Wolfram -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
