Quick question about (Sep-09) conversion.
Our programming crew does not give us (Web Services) access to Oracle. We of course don't care kuz' mySQL suits us fine. But now we have run into a pickle. They are handing us text cdf files for import. One of the fields they are giving us is in the following condition: 02-SEP-02 We need to do a conversion of that to a `real' datetime format. I know of a function in php that will convert that but I need this in SQL only. Any ideas? Rob. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RedHat DB?!
Greetings ! What is RedHat Database? Is anybody using it? How is it? Better than MySQL? Looking forward to hearing from you. Regards, DT - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: ERROR 1006: Can't create database 'mynewdb'. (errno: 28)
What does df show? -Original Message- From: John Lepone [mailto:[EMAIL PROTECTED]] Sent: 17 December 2001 20:41 To: '[EMAIL PROTECTED]' Subject: Re: ERROR 1006: Can't create database 'mynewdb'. (errno: 28) I don't think I'm out of room on the volume. Below is the directory listing: -rw-rw1 mysqlmysql 0 Dec 13 07:09 mandrake-bin.001 -rw-rw1 mysqlmysql 0 Dec 13 07:12 mandrake-bin.002 -rw-rw1 mysqlmysql 0 Dec 13 07:17 mandrake-bin.003 -rw-rw1 mysqlmysql 0 Dec 13 14:42 mandrake-bin.004 -rw-rw1 mysqlmysql 0 Dec 13 15:25 mandrake-bin.005 -rw-rw1 mysqlmysql 0 Dec 14 03:20 mandrake-bin.006 -rw-rw1 mysqlmysql 0 Dec 17 02:31 mandrake-bin.007 -rw-rw1 mysqlmysql 0 Dec 17 02:31 mandrake-bin.index -rw-r--r--1 mysqlroot 2874 Dec 17 02:31 mandrake.err -rw-rw1 mysqlmysql 0 Dec 17 02:31 mandrake.pid -rwxr-xr-x1 root root 2530 Dec 13 07:08 my.cnf* drwx--x--x2 mysqlmysql4096 Dec 12 00:24 mysql/ srwxrwxrwx1 mysqlmysql 0 Dec 17 02:31 mysql.sock= drwxr-xr-x2 mysqlmysql4096 Dec 17 09:17 test/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Blocked MySQL
I've installed MySQL on SuSe Linux. It's 3.23.43 version. Below should be mirror of 'my.cnf'. Those error messages, which I wrote in my first mail, are shown whatever I type. It doesn't matter whether I 'select', 'update' or something else, always it prints Ignoring query to other database. Robertix my.cnf: [client] #password=my_password port=3306 socket=/tmp/mysql.sock # Here is entries for some specific programs # The following values assume you have at least 32M ram # The MySQL server [mysqld] port=3306 socket=/tmp/mysql.sock skip-locking default-character-set=latin1 set-variable = key_buffer=16M set-variable = max_allowed_packet=1M set-variable = thread_stack=128K set-variable = flush_time=1800 # Uncomment the following row if you move the MySQL distribution to another # location basedir = /var/lib/mysql/ [mysqldump] quick set-variable = max_allowed_packet=16M [mysql] no-auto-rehash [isamchk] set-variable= key=16M [client_fltk] #help_file= c:\apache\mysql\sql_client\MySQL.help #client_file= c:\apache\mysql\MySQL.options #history_length=20 #database = test #queries_root= c:\apache\mysql\queries #last_database_file= c:\apache\mysql\lastdb End. - Original Message - From: Scott Helms [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, December 17, 2001 3:04 PM Subject: Re: Blocked MySQL Robert, You need to provide alot more data. How about platform and MySQL version to start, a copy of your config (my.cnf) file, and provide exactly what you're typing and the response. Scott Helms - Original Message - From: robertix [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, December 17, 2001 7:02 AM Subject: Blocked MySQL When I try to do anything with MySQL, I always see Ignoring query to other database on my screen. When I type status, the computer prints: All updates ignored to this database. after Current user. Could anybody help me ? Does anybody know how can I unblock MySQL ? robertix - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Quick question about (Sep-09) conversion.
* zxcv
Our programming crew does not give us (Web Services) access to Oracle. We
of course don't care kuz' mySQL suits us fine. But now we have run into a
pickle. They are handing us text cdf files for import. One of the fields
they are giving us is in the following condition:
02-SEP-02
We need to do a conversion of that to a `real' datetime format. I know of
a function in php that will convert that but I need this in SQL only.
Any ideas?
Asuming all dates are 20xx:
mysql select
@d:='01-DEC-18',@d2:=concat('20',mid(@d,1,2),'-',field(mid(@d,4,3),
'JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC'),'-'
,mid(@d,8,2)) as converted, @d2 + interval 1 second as proof;
--
Roger
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Quick question about (Sep-09) conversion.
Thanks... That's exactly what I was looking for. Simple and elegant.
Rob.
On Tue, 18 Dec 2001, Roger Baklund wrote:
Asuming all dates are 20xx:
mysql select
@d:='01-DEC-18',@d2:=concat('20',mid(@d,1,2),'-',field(mid(@d,4,3),
'JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC'),'-'
,mid(@d,8,2)) as converted, @d2 + interval 1 second as proof;
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
some simple problems with 1 line answeres please help.
I have compiled and installed mysql on redhat 7.1 I am having a few problems. 1.) I compiled and installed as root then changed the ownership to the dba account. Will this cause any problems down the line. 2.) Is there s startup and shutdown script to put in /etc/rc.d/init.d or do I have to write my own ? 3.) I su to the dba account and want to a set the superuser password (for the mysql install not the OS) how can I do this. 4.) I su to the dba account and try to do anything I get eg: [mysqldba@jordan bin]$ ./mysqladmin create bathjobs ./mysqladmin: CREATE DATABASE failed; error: 'Access denied for user: '@localhost' to database 'bathjobs'' thanks in advance. PS I am finding this list a great help. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: some simple problems with 1 line answeres please help.
A little bit of help from some one who is recovering from Xmas parting... I am having a few problems. 1.) I compiled and installed as root then changed the ownership to the dba account. Will this cause any problems down the line. IF you do the normal mysql install you will have added a user Mysql so as long as you have done the same with user dba you should have no problmes 2.) Is there s startup and shutdown script to put in /etc/rc.d/init.d or do I have to write my own ? I think you should use mysqladmin but not shore about this... 3.) I su to the dba account and want to a set the superuser password (for the mysql install not the OS) how can I do this. 4.) I su to the dba account and try to do anything I get e [mysqldba@jordan bin]$ ./mysqladmin create bathjobs ./mysqladmin: CREATE DATABASE failed; error: 'Access denied for user: '@localhost' to database 'bathjobs'' Ok when you install MySQL you just log on /use/local/mysql/mysql But what you should have done is set the root password using mysqladmin then you would log on /usr/local/mysql/mysql -u root -p Then enter you pass word you just set. The rest would be done by using GRANT. I hope this helps a bit. Simon thanks in advance. PS I am finding this list a great help. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Downsides of MySQL?
Hi! I may be a little based, but here is some input regarding this. Good morning. Recently, we presented MySQL as a database option for a website that we might be working on. We've used it as our database in the past and we plan on using it in the future as possible. With that said, I confess I don't have as intimate a knowledge of mySQL to address some of the things in the email that was sent to me. I'd like to hear what some of you have to say/think about this. I know some of the things said below aren't entirely correct, but I'm not 100% sure about some of the others. --Begin Quote-- MySQL - as I said at our meeting, we would not be comfortable with this as an enterprise strength solution. MySQL is unsupported freeware MySQL is developed by a company, MySQL AB, who have been providing commercial support for MySQL since 1996. MySQL AB has the full copyright of the core server, who is mainly developed by it. A lot of tools and API's around are provided by the MySQL users around the world. and lacks enterprise management functionality. I agree that MySQL doesn't yet have all the nice admin GUI's that you can find for some of the other commercial big players, but MySQL is one the other hand much easier to manage, so there is much less need for these. There is a lot of administrative command tools for MySQL available that allows one to monitor and work with MySQL. It has a small limited feature set compared to ORACLE, DB/2 and is lacking the functionality to support data replication and has little capability for generating management info. MySQL supports replication since more than one year back (and you don't have to pay extra to get this :) You can find a quite complete feature comparison table at: http://www.mysql.com/information/crash-me.php You will notice that MySQL has most of the features that you will need for most projects. It has also a lot of unique features that makes it extremely suitable for web and logging applications, but this doesn't mean that MySQL isn't suitable for other applications as well. I agree that we don't support everything that Oracle and DB/2 does, but the things we support we do very well and we are closing the gap the whole time. We have always tried to avoid 'bloat' the server with features that only a few people seldom need... There are question marks around the scalability of the product. MySQL is typically much faster than Oracle and DB/2, something MySQL users have discovered over and over again. http://www.mysql.com/information/benchmarks.html (We will on this page soon have results from new multi-user benchmark that will contain a lot of new interesting data). If your application have a need for hard database speed, you can usually get much better performance with MySQL than you can with either Oracle or DBI/2 on the same hardware. (I have to say usually, as there is always exceptions for the rule; One database can't be better in every tests, something our benchmarks shows quite clearly..) I'm not sure of the locking algorithms used (whether row level or record level) - MySQL has a sophisticated handler concept which allows the user to choose different backends for different tables. With this you can choose between table locks, page locks, row locks or versioning (no locks at all). For example, if you are using InnodB tables, the locking algorithm used is very similar to what Oracle provides in their high end servers. the fact that it is not generally used in multi-user solutions MySQL is the most used open source database and is generally used in multi-user environments. You can find a lot of user stores that proves this on http://www.mysql.com. is a good enough indication that this is not accepted database technology for industrial-strength multi-user systems. I can only say that our customer base proves otherwise :) Just take a look in our email archive and check the email addresses from people that is on the MySQL mailing lists.. The fact that it is unsupported freeware would mean that an end user would potentially be held to ransom by a DBA with specific knowledge. I think the argument here is the wrong way. Thanks to being an open source database with millions of users, you are much likely to get someone that can help you with ANY problems with MySQL than you can with Oracle or DBI/2. There is also many knowledgeable MySQL DBI's available for hire. With a closed source product, you are hold ransome for every single bug in their database; If you happen to come across some of these, you are totally depended on the commercial company to get this fixed. You are also hold ransome for the performance the database can deliver, without any change to get this fixed. With MySQL you can always hire a developer to fix the bug, if you don't get it solved by MySQL AB (a thing that is not likely to happen; We have a clean track record when it comes to fixing bugs, something most
Re: Changes between 3.22 and 3.23
Hi! Michael == Michael Bacarella [EMAIL PROTECTED] writes: Michael Well, looks like I found the culprit. Michael Increasing thread_cache_size from 0 to 50 totally alleviated the stress Michael (both on the database and our clients). Recommended by the manual, Michael of course. That's strange. This is the first time I have seen someone needed to increase this to get the same performance with 3.23 as with 3.22. 3.22 didn't have this option, so I don't think this is the bases to your problem. When you upgraded to 3.23, did you upgrade your OS at the same time ? Michael For list-archival's sake: Michael FreeBSD 4.3-STABLE Michael /etc/my.cnf: Michael [mysqld] Michael user=mysql Michael set-variable = wait_timeout=3600 Michael set-variable = tmp_table_size=800 Michael set-variable = max_connections=500 Michael set-variable = table_cache=256 Michael set-variable = sort_buffer=4M Michael set-variable = key_buffer=64M Michael set-variable = thread_cache_size=50 Michael top: Michael CPU states: 66.3% user, 0.0% nice, 30.2% system, 3.1% interrupt, 0.4% idle Michael Mem: 33M Active, 92M Inact, 29M Wired, 8368K Cache, 35M Buf, 87M Free Michael Swap: 1024M Total, 88K Used, 1024M Free Michael mytop (a great utiltiy, may I add) is reassuring: Michael Queries Total: 129,866Avg/Sec: 450.92 Slow: 0 Michael Threads Total: 3 Active: 1 Cached: 48 Michael Key Efficiency: 99.73% Bytes in: 16,118,135 Bytes out: 26,368,181 Michael Unfortunately, the application does make a new connection for every Michael hit, which is why thread_cache_size probably made a difference. Yes, it should help in this case, but doesn't explain the difference to 3.23. Usually the thread_cache_size has only a 2-3 % impact on the overall performance. Michael Out of curiousity, what was different about 3.22? Based on the above, I can't say what it could be. You don't even have any 'slow' queries... If you ever found out what it could be, please keep us informed! Michael Anyway, Thanks to all who responded. Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MyODBC leaking handles...
Hi all, This is my first post in this forum. I've tried to find something in the documentation but failed. I cannot find out why the ODBC driver is leaking 1 handle for each request that I do! It doesn't matter if I close it or whatever I do. Anyone that knows what to do? Any hints or tips? Windows 2000 Professional MySQL 3.23 MyODBC 2.50.39 Regards, ___ Jens A. Collin Envox Technical System Developer Envox Group Söder Mälarstrand 43 118 25 Stockholm Sweden Office: +46 (0)8 56 256 000 Fax : +46 (0)8 56 256 050 [EMAIL PROTECTED] http://www.envox.com ___ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
difficult query help
Hy, I have a question on how to build a query. scenario: I have three tables, Users: -- user_id INT(32), username CHAR(64) Tasks: -- task_id(INT32), task_name CHAR(64) Authorization: -- user_id INT(32), task_id INT(32) some details of database design are neclected here. There is a Users table which holds some Information on users, this one is not really interesting for the following task, I give it only for drawing a better pictur of the scenario. The Tasks table holds information on all possible tasks in an application, and the Authorization Table has an entry for each assignment of a user to a specific task which he is allowed to act upon. Now I want to create a query to get all task_id's which one given User isn't already assigned to. I tried a lot of things, Joined in every direction and so but do not come to my result. I must somehow get all task_id's of Tasks which are not in a result when i look for the user_id in the Authorization table. I already fought some hours with that problem. but I just don't get it. Hints to a good source of online information to the JOIN statement are also very welcome as I think that's getting me to a solution but I really do not fully understand how that JOIN works and what it how does... TIA, henning - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Downsides of MySQL?
And on the other hand, with mySQL you can talk with the real developers that make it work while on other closed source DBs, with luck, you can get sensible costumer support... Cheers, Luis Ferro -Original Message- From: Michael Widenius [mailto:[EMAIL PROTECTED]] Sent: terca-feira, 18 de Dezembro de 2001 11:52 To: [EMAIL PROTECTED]; [EMAIL PROTECTED]] Cc: Robinson, Mike; [EMAIL PROTECTED] Subject: RE: Downsides of MySQL? Hi! I may be a little based, but here is some input regarding this. Good morning. Recently, we presented MySQL as a database option for a website that we might be working on. We've used it as our database in the past and we plan on using it in the future as possible. With that said, I confess I don't have as intimate a knowledge of mySQL to address some of the things in the email that was sent to me. I'd like to hear what some of you have to say/think about this. I know some of the things said below aren't entirely correct, but I'm not 100% sure about some of the others. --Begin Quote-- MySQL - as I said at our meeting, we would not be comfortable with this as an enterprise strength solution. MySQL is unsupported freeware MySQL is developed by a company, MySQL AB, who have been providing commercial support for MySQL since 1996. MySQL AB has the full copyright of the core server, who is mainly developed by it. A lot of tools and API's around are provided by the MySQL users around the world. and lacks enterprise management functionality. I agree that MySQL doesn't yet have all the nice admin GUI's that you can find for some of the other commercial big players, but MySQL is one the other hand much easier to manage, so there is much less need for these. There is a lot of administrative command tools for MySQL available that allows one to monitor and work with MySQL. It has a small limited feature set compared to ORACLE, DB/2 and is lacking the functionality to support data replication and has little capability for generating management info. MySQL supports replication since more than one year back (and you don't have to pay extra to get this :) You can find a quite complete feature comparison table at: http://www.mysql.com/information/crash-me.php You will notice that MySQL has most of the features that you will need for most projects. It has also a lot of unique features that makes it extremely suitable for web and logging applications, but this doesn't mean that MySQL isn't suitable for other applications as well. I agree that we don't support everything that Oracle and DB/2 does, but the things we support we do very well and we are closing the gap the whole time. We have always tried to avoid 'bloat' the server with features that only a few people seldom need... There are question marks around the scalability of the product. MySQL is typically much faster than Oracle and DB/2, something MySQL users have discovered over and over again. http://www.mysql.com/information/benchmarks.html (We will on this page soon have results from new multi-user benchmark that will contain a lot of new interesting data). If your application have a need for hard database speed, you can usually get much better performance with MySQL than you can with either Oracle or DBI/2 on the same hardware. (I have to say usually, as there is always exceptions for the rule; One database can't be better in every tests, something our benchmarks shows quite clearly..) I'm not sure of the locking algorithms used (whether row level or record level) - MySQL has a sophisticated handler concept which allows the user to choose different backends for different tables. With this you can choose between table locks, page locks, row locks or versioning (no locks at all). For example, if you are using InnodB tables, the locking algorithm used is very similar to what Oracle provides in their high end servers. the fact that it is not generally used in multi-user solutions MySQL is the most used open source database and is generally used in multi-user environments. You can find a lot of user stores that proves this on http://www.mysql.com. is a good enough indication that this is not accepted database technology for industrial-strength multi-user systems. I can only say that our customer base proves otherwise :) Just take a look in our email archive and check the email addresses from people that is on the MySQL mailing lists.. The fact that it is unsupported freeware would mean that an end user would potentially be held to ransom by a DBA with specific knowledge. I think the argument here is the wrong way. Thanks to being an open source database with millions of users, you are much likely to get someone that can help you with ANY problems with MySQL than you can with Oracle or DBI/2. There is also many knowledgeable MySQL DBI's available for hire. With a closed source product, you are hold ransome for every single bug in their database;
About a sql hacking for future
Hello, I try to reference some value of previous row in a sql statement. But today it's not possible due to the thread state of var, can t we do a row state ? thanks. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: difficult query help
* Henning Sprang Now I want to create a query to get all task_id's which one given User isn't already assigned to. I tried a lot of things, Joined in every direction and so but do not come to my result. The problem in this situation is that you want to join with records that are not there. I suppose you have no problem selecting the Tasks that _are_ assigned to a User. To find the non-existing records, you need to use LEFT JOIN. You use it like a normal JOIN, but a LEFT JOIN returns a row even if the associated record is not found, and fills all fields from that record with NULL. Because the non-existing records are what you want in this case, you add a ISNULL(Auth.user_id) to your WHERE clause: SELECT Task.task_id FROM Task,User LEFT JOIN Auth ON Auth.user_id=User.user_id AND Auth.task_id=Task.task_id WHERE ISNULL(Auth.user_id) AND User.username=roger; -- Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Moving data in table from mysql server A to server B
If I have mysql on server A and an identical version on server B, what is the easiest way to move data in Table C from server A to server B? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Innodb Win2000 error
I am trying to use mysql-max-3.23.46a for Windows (Win2000) with innodb in use. After installing from the setup program, I copied my-example to c:\my.cnf and uncommented the innodb lines with the following: innodb_data_file_path = ibdata1:20M innodb_data_home_dir = c:\ibdata\ set-variable = innodb_mirrored_log_groups=1 innodb_log_group_home_dir = c:\iblogs\ set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=30M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_log_arch_dir = c:\iblogs\ innodb_log_archive=0 set-variable = innodb_buffer_pool_size=80M set-variable=lower_case_table_names =0 set-variable = innodb_additional_mem_pool_size=10M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 When I start the server standalone I get the following: C:\mysql\binmysqld-max-nt.exe --standalone Cannot initialize InnoDB as 'innodb_data_file_path' is not set. If you do not want to use transactional InnoDB tables, add a line skip-innodb to the [mysqld] section of init parameters in your my.cnf or my.ini. If you want to use InnoDB tables, add for example, innodb_data_file_path = /mysql/data/ibdata1:20M But to get good performance you should adjust for your hardware the InnoDB startup options listed in section 7.6 at http://www.mysql.com/doc/ I did a search and no other my.cnf files (or my.ini) files are on my disks, so I tried to start the server with the innodb options on the command line and got the following error: C:\mysql\binmysqld-max-nt.exe --standalone --innodb_data_file_path=c:\ibdata\ibdata1:20M InnoDB: Warning: operating system error number 123 in a file operation. InnoDB: Cannot continue operation. Any help would be appreciated. Rick Mehalick Sr. Consultant Shell Information Technology International - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Moving data in table from mysql server A to server B
* Kemp Randy-W18971 If I have mysql on server A and an identical version on server B, what is the easiest way to move data in Table C from server A to server B? Asuming your databases are stored in /usr/local/mysql/var/ and the relevant database is named database: From Server A: scp /usr/local/mysql/var/database/Table_C.* Server_B:/usr/local/mysql/var/database/. If the data is big and the connection is slow, you may want to compress the data before you transfer it. -- Roger - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
create function: fails with error 1126
All, I'm trying to use the UDF functionality, but I keep getting the same error when I issue the CREATE FUNCTION command. This is the error: mysql CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME udf_example.so; ERROR 1126: Can't open shared library 'udf_example.so' (errno: 0 ld.so.1: /usr/local/mysql/libexec/mysqld: fatal: relocation erro) I've recompiled with debug support and this is what I get in the log: mysql_create_function: error: dlopen of udf_example.so failed, error: 0 (ld.so.1: /usr/local/mysql/libexec/mysqld: fatal: relocation error: file /usr/lib/udf_example.so: symbol default_charset_info: referenced symbol not found) Environment: - Solaris 7 (on Sparc Ultra 250) - MySQL version 3.23.46, compiled locally (not from a package) - gcc 2.95.3 - configure options: CC=gcc CXX=gcc ./configure --with-mysqld-ldflags=-rdynamic --prefix=/usr/local/mysql --loca lstatedir=/disk3/mysql - command for compiling udf_example.cc: gcc -shared -lgcc -DMYSQL_SERVER -DDEFAULT_MYSQL_HOME=\/usr/local/mysql\ -DDATADIR=\/disk3/mysql\ -DSHAREDIR=\/usr/local/mysql/share/mysql \ -DHAVE_CONFIG_H -I/usr/local/src/mysql-3.23.46/include -fno-implicit-te mplates -fno-exceptions -fno-rtti -o udf_example0.so udf_example.cc udf_example.so exists in /usr/lib I've seen a few posts on the net with a similar description of the problem, but never a solution :-( does anyone have a clue how to resolve this issue? Thanks, Tim Tim Wuyts Software Engineer EUnet Systems KPN Belgium - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB still gives poor analysis to the optimizer
On Mon, Dec 17, 2001 at 10:03:58PM -0600, Philip Molter wrote: : Is there any upcoming fix for this recurring problem? The table : handler is just giving poor data to the optimizer and the optimizer : is making bad decisions because of it. It appears to come and go, : depending on data that is in the table, what's been done, etc. Here's some specific data, because writing e-mails isn't too bright a thing to do late in the evening after struggling with a problem for the better part of the night. The crux of all this (for us, anyway) revolves around three tables in our join sequence: percept, hosts, and perceptType. percept.hid = hosts.hid (INNER) percept.ptid = perceptType.ptid (INNER) Other tables are joined in via LEFT JOINS, but they shouldn't (and don't) affect the optimization because they're just being joined in for ancillary data. So the three table structure is what I'm most concerned with. Everything is keyed properly, and this query optimizes correctly 100% of the time under MyISAM tables, and, for that matter, under PostgreSQL (which this is also being designed to run under, although Pg is worse for overall performance). So here's some row counts from the data: mysql select count(*) from hosts; = 38 mysql select count(*) from hosts where active=1;= 31 mysql select count(*) from perceptType; = 26 mysql select count(*) from perceptType where runinterval is not null;= 12 mysql select count(*) from percept; = 11305 mysql select count(*) from percept where deleted=0; = 10647 mysql select count(*) from percept p, hosts h where p.hid=h.hid and h.active=1 and p.deleted=0;= 9064 mysql select count(*) from percept p, perceptType pt where p.ptid=pt.ptid and pt.runinterval is not null and p.deleted=0;= 939 mysql select count(*) from percept p, perceptType pt, hosts h where p.hid=h.hid and p.ptid=pt.ptid and h.active=1 and pt.runinterval is not null and p.deleted=0;= 816 Here are some explains. These were taken literally 30 seconds apart as I wrote this e-mail and the optimization switched. Here is the EXPLAIN for a good match: mysql explain select count(*) from percept p, perceptType pt, hosts h where p.hid=h.hid and p.ptid=pt.ptid and h.active=1 and pt.runinterval is not null and p.deleted=0; +---++--+-+-+-+--+-+ | table | type | possible_keys| key | key_len | ref | rows | Extra || +---++--+-+-+-+--+-+ | pt| index | PRIMARY,ptid | ptid| 7 | NULL| 26 | where used; |Using index | | p | ref| deleted,hid,ptid | ptid| 4 | pt.ptid | 412 | where used || | h | eq_ref | PRIMARY,active | PRIMARY | 4 | p.hid |1 | where used || +---++--+-+-+-+--+-+ 3 rows in set (0.00 sec) Here is the EXPLAIN for a bad match: mysql explain select count(*) from percept p, perceptType pt, hosts h where p.hid=h.hid and p.ptid=pt.ptid and h.active=1 and pt.runinterval is not null and p.deleted=0; +---++--+-+-++--+-+ | table | type | possible_keys| key | key_len | ref| rows | Extra | | +---++--+-+-++--+-+ | h | index | PRIMARY,active | active | 4 | NULL | 38 | where used; |Using index | | p | ref| deleted,hid,ptid | hid | 4 | h.hid | 502 | where used | | | pt| eq_ref | PRIMARY,ptid | PRIMARY | 4 | p.ptid |1 | where used | | +---++--+-+-++--+-+ 3 rows in set (0.00 sec Given everything seen here, is there any way to understand why these poor decisions are being made? Not only is the first query information clearly better, but the underlying table data (row counts, etc.) clearly shows it to be better. I can't fathom how InnoDB is passing the optimizer information that is this far off. * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try:
Re: create function: fails with error 1126
Tim Wuyts writes: All, I'm trying to use the UDF functionality, but I keep getting the same error when I issue the CREATE FUNCTION command. This is the error: mysql CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME udf_example.so; ERROR 1126: Can't open shared library 'udf_example.so' (errno: 0 ld.so.1: /usr/local/mysql/libexec/mysqld: fatal: relocation erro) [skip] udf_example.so exists in /usr/lib I've seen a few posts on the net with a similar description of the problem, but never a solution :-( does anyone have a clue how to resolve this issue? Thanks, Tim Tim Wuyts Software Engineer EUnet Systems KPN Belgium Hi! Probably unresolved symbol from some library. Run ldd on shared lib and see what is missing. Most probably libmysqlclient.so... -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How to ignore duplicate records appending from MS-ACCESS.
Hi Everyone, I have just installed mysql 4 on a Linux server I'm comparing it against MS-SQL to see it handles one of our DBs better. The DB has over 8 million records MS-SQL shokes with it. I have a field defined to be UNIQUE and I need to append from another table into it from MS-ACCESS 2000 (using myodbc). My problem is that MS-ACCESS stops when it finds a duplicate record. I need it to IGNORE continue. It does not allow me to insert the keyword IGNORE (it gives me a syntax error) If I do it at the server I have no problem because I have the IGNORE option. Is there a way to formulate the SQL from MS_ACCESS to allow me to ignore duplicate records insert the others? Or is there a switch on mysqld or myodbc that will allow me to do it? Thanks in Advanced, Dante - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SQL syntax question
I would like to use an input form to add users to my database, however, if
the name is already in use, I do not want to add a duplicate record. I also
need this to be case insensitive (ie Santa Claus = santa Claus).
I've tried the following code, but it doesn't seem to be working
$chknamerow = mysql_fetch_array(runsql(SELECT FirstName,LastName FROM Names
WHERE FirstName LIKE '$addfirstname' AND LastName LIKE '$addlastname' ));
$chkname = $chknamerow[FirstName] . $chknamerow[LastName];
if( ($chknamerow[FirstName]) AND ($chknamerow[LastName]) )
{
$Evalname = $addfirstname2 . $addlastname2;
$evalchange = is ALREADY entered as ;
$NewName = $chkname;
printf(p class=\subtitle\The name %s was not added to the
database./p\n, $Evalname);
}
The function runsql() is as follows:
function runsql($query)
{
global $debugit;
global $dbname;
global $mysql_link;
$runresult = mysql_db_query($dbname, $query, $mysql_link);
if (($debugit ) AND ($runresult == ))
{
mysql_error($mysql_link);
echo mysql_errno().: .mysql_error($mysql_link). on database
$dbnameBR;
echo While running SQL: $queryBR;
}
return ($runresult);
}
Any advice?
Steve Osborne
[EMAIL PROTECTED]
?php
/* Happy Holidays */
mysql_select_db('North_Pole');
mysql_query('SELECT reindeer FROM stable WHERE nose_color=red');
?
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Coredump when running scripts/mysql_install_db
On Sun, 16 Dec 2001, Michael Widenius wrote:
cut
As this works on our HP-UX machine, it means that some library that we
are using is not compatible with yours :(
Michael My workaround is to compile mysql from source, adding --disable-largefile
Michael to the options recommended in the manual. Perhaps there is a better way
Michael -- maybe a fix for HPUX?
What kind of errors do you get if you don't use --disable-largefile ?
Michael As I reported in my earlier email on Oct. 29, I get essentially the same
Michael error as was reported here, namely, setrlimit fails. I configured as you
Michael recommend, run make, then make test. I get:
Michael ===
Michael $make test
Michael cd mysql-test ; ./mysql-test-run
Michael Installing Test Databases
Michael Removing Stale Files
Michael Installing Master Databases
Michael 011214 21:50:26 Warning: setrlimit couldn't increase number of open files
Michael to more than 60
Michael 011214 21:50:26 Warning: Changed limits: max_connections: 50
Michael table_cache: 64
Michael 011214 21:50:26 ../sql/mysqld: Shutdown Complete
cut
Michael join [ fail ]
Michael Aborting. To continue, re-run with '--force'.
Michael
Michael As you can see, setrlimit fails to increase the allowed number of open
Michael files, then the join test fails because it wants to open more than 60, the
Michael default on my system. The ouput quoted here is from 3.23.46 built today,
Michael but it's the same as I got in October with 3.23.43.
Thanks; Now I have a much better understanding of this problem.
On our system I can increase the number of open files to 500, without
any problems.
As can I, with largefiles disabled.
cut
Very strange!
Could you try to modify your test to do the proper casts and see if
you can find what is going wrong on your system ?
If we can find a test that fails on your system but works on ours, we
would know a lot more about of how to fix this problem.
Yes, as it turns out. The problem is in dce. It seems setrlimit is
replaced with cma_setrlimit in dce/cma_ux.h. I'll provide the modified
rlimtest.c and it's output below, but the short version is that including
pthread.h and linking with -ldce causes the problem.
Here's the code:
/*=*/
#include sys/resource.h
#include errno.h
#include stdio.h
#include pthread.h
int main()
{
struct rlimit rl;
uint x;
printf(Size of rlimit is %ld\n,sizeof(rl));
x = 510;
if (!getrlimit(RLIMIT_NOFILE,rl))
{
printf(getrlimit: cur=%ld max=%ld\n, (long)rl.rlim_cur,
(long)rl.rlim_max);
}
rl.rlim_cur = x;
rl.rlim_max = x;
printf(Changing to: cur=%ld max=%ld\n, (long)rl.rlim_cur,
(long)rl.rlim_max);
if (setrlimit(RLIMIT_NOFILE,rl))
{
printf(\nERROR: errno=%i\n, errno);
}
else
{
printf(\nSUCCESS\n);
}
0;
}
/*=*/
Here's the output:
stassenm@dr ~ $gcc -I/opt/dce/include -ldce rlimtest.c
stassenm@dr ~ $./a.out
Size of rlimit is 8
getrlimit: cur=60 max=1024
Changing to: cur=510 max=510
SUCCESS
That worked, but then I mention largefile:
stassenm@dr ~ $gcc -D_FILE64 -I/opt/dce/include -ldce rlimtest.c
stassenm@dr ~ $./a.out
Size of rlimit is 16
getrlimit: cur=60 max=1024
Changing to: cur=510 max=510
ERROR: errno=22
As you can see, setrlimit (really cma_setrlimit64 in this case) returns an
error. Error 22 is EINVAL, which seems to cover most of the possible ways
to go wrong.
The good news (for you, at least) is that seems pretty clearly an HPUX
bug on my machine, unless I'm still missing something, though I'm
wondering what's different about mine, since it works on yours.
Regards,
Monty
Michael
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SQL syntax question
At 10:02 AM -0800 12/18/01, Steve Osborne wrote:
I would like to use an input form to add users to my database, however, if
the name is already in use, I do not want to add a duplicate record. I also
need this to be case insensitive (ie Santa Claus = santa Claus).
Make the (LastName, FirstName) a primary key and use INSERT IGNORE. Then test
mysql_affected_rows() to see whether it's 1 or 0. If it's 1,
the record was inserted. If it's 0, you tried to insert a dup.
I've tried the following code, but it doesn't seem to be working
$chknamerow = mysql_fetch_array(runsql(SELECT FirstName,LastName FROM Names
WHERE FirstName LIKE '$addfirstname' AND LastName LIKE '$addlastname' ));
$chkname = $chknamerow[FirstName] . $chknamerow[LastName];
if( ($chknamerow[FirstName]) AND ($chknamerow[LastName]) )
{
$Evalname = $addfirstname2 . $addlastname2;
$evalchange = is ALREADY entered as ;
$NewName = $chkname;
printf(p class=\subtitle\The name %s was not added to the
database./p\n, $Evalname);
}
The function runsql() is as follows:
function runsql($query)
{
global $debugit;
global $dbname;
global $mysql_link;
$runresult = mysql_db_query($dbname, $query, $mysql_link);
if (($debugit ) AND ($runresult == ))
{
mysql_error($mysql_link);
echo mysql_errno().: .mysql_error($mysql_link). on database
$dbnameBR;
echo While running SQL: $queryBR;
}
return ($runresult);
}
Any advice?
Steve Osborne
[EMAIL PROTECTED]
?php
/* Happy Holidays */
mysql_select_db('North_Pole');
mysql_query('SELECT reindeer FROM stable WHERE nose_color=red');
?
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: RedHat DB?!
Did you try a google search - say http://www.google.com/search?q=%22redhat+database%22 ? The first link I saw (to RedHat itself) pretty much answers the question... - steve At 2:53 PM +0545 12/18/01, Deependra B. Tandukar wrote: Greetings ! What is RedHat Database? Is anybody using it? How is it? Better than MySQL? Looking forward to hearing from you. Regards, DT -- ++ | Steve Edberg [EMAIL PROTECTED] | | University of California, Davis (530)754-9127 | | Programming/Database/SysAdmin http://pgfsun.ucdavis.edu/ | ++ | Restriction of free thought and free speech is the most dangerous of | | all subversions. It is the one un-American act that could most easily | | defeat us.| | - Supreme Court Justice (1939-1975) William O. Douglas | ++ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Changing the locations of the tables
Is there a way without reinstalling MySQL to change the locations of the database tables? For example right now they are in /var/lib/mysql I want them /usr/lib/mysql Thanks in advance -- Stephen Johnson [EMAIL PROTECTED] http://www.pets4u.com Owner / Founder -- your next best friend is waiting for you --
RE: Changing the locations of the tables
Check the MySQL documentation, but I believe there are 2 ways. 1 In the startup of mysqld, set the datadir option, which tells the mysqld where the tables are. 2 use UNIX links. Using UNIX links is a good way to split your table and index I/O across disks. Note that you have to be careful about dropping and re-creating tables because some versions of MySQL will not follow the links on re-creation. Jim Josephs, P.Eng Vice President WARE Solutions Corp. #200, 1204 Kensington Road NW Calgary, AB Canada T2N 3P5 phone: 403.291.9678 x223 1.877.438.3245 cell : 403.620.7730 fax : 403.283.9670 http : //www.ware-solutions.com mail : [EMAIL PROTECTED] WARE Solutions is a publicly traded company on the CDNX:WSN -Original Message- From: Stephen Johnson [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 18, 2001 10:19 AM To: MySQL Subject: Changing the locations of the tables Is there a way without reinstalling MySQL to change the locations of the database tables? For example right now they are in /var/lib/mysql I want them /usr/lib/mysql Thanks in advance -- Stephen Johnson [EMAIL PROTECTED] http://www.pets4u.com Owner / Founder -- your next best friend is waiting for you -- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
keys
Which keys should I have for a database query like : SELECT qid, catid, point, flag_status, timestamp FROM questions WHERE (flag_status = 1 OR flag_status = 2) ORDER BY qid DESC LIMIT 0, 10 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Posibility of adding compress_*()/decompress_*() functions
Hi all, I would like to propose that mysql have compress_*()/decompress_*() functions added that would be similar to des_encrypt()/des_decrypt(). My idea would be to have functions available like: compress_gz()/decompress_gz() compress_bz()/decompress_bz() ...etc. I have read of people doing this in their applications: http://marc.theaimsgroup.com/?l=mysqlm=100823186816632w=2 ...but I could really use the feature directly in SQL. The ability to do something like: mysql SELECT event_id,decompress_gz(event_text) from events where event_date between 20011201 and 20011210; assuming that event_text is a *char and that records are always inserted using the compress_gz() function. Doing it this way (as a function) would allow people to still use software algorithms outside of MySQL if they wanted/when appropriate. The disadvantage is that you must be consistent in your use of the fields, and Mysql will need a defined behavior for when that fails (when one tries to decompress_gz a non-gz-compressed field). The other option is to have extended string types that are defined like: event_text varchar(255) not null default '' compress_gz, event_blob text not null default '' compress_bz, and then have Mysql handle the compression/decompression internally, and disallow using those fields in WHERE clauses. I personally prefer the former option (compress/decompress functions) because I prefer having the flexibility in exchange for taking the responsibility, but either method would be useful to me. Here is what prompted this request: root@# ls -l events.* events_raw.* | cut -b30-500 63571071 Dec 18 12:29 events.MYD 63397888 Dec 18 12:29 events.MYI 9119 Dec 6 18:15 events.frm 92184576 Dec 18 12:29 events_raw.MYD 12948480 Dec 18 12:29 events_raw.MYI 8602 Nov 21 00:19 events_raw.frm I have been working on a project where this events table, which has ~1.5 million records and grows by ~100k/per day, started out at about 600MB. I rearranged the data, moved large text strings out into associated tables with numeric IDs and joins, etc. Now the events table is much more scalable, and the system it supports much faster. The events_raw table, which has two fields (event_id,event_text), holds the raw event message that was received (before it was parsed and stored) which is extremely useful for debugging. The events_raw table is now the scalability problem, as it will hit a file size limit long before events. The events_raw table is only searched using a join on events.event_id=events_raw.event_id, so if I could compress the events_raw.event_text field in the table, problem solved. So, my example above would be more accurate for my case if written as: mysql SELECT e.event_id, decompress_gz(r.event_text) as original_event_text from events as e left join events_raw as r on e.event_id=r.event_id where e.event_date between 20011201 and 20011210; I think we could benefit from this feature in our MARC system, as well. marc.theaimsgroup.com Thanks for your time and consideration. Sincerely, -- Lester Hightower TheAIMSGroup.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Changing the locations of the tables
Here you go:-) [root@server src]# rpm -i MySQL-3.XX.XX-1.src.rpm [root@server src]# tar -xvzf ../../src/redhat/SOURCES/mysql-3.XX.XX.tar.gz [root@server src]# cd mysql-3.XX.XX [root@server mysql-3.XX.XX]# ./configure \ --prefix=/usr/lib/mysql [root@server mysql-3.XX.XX]# make [root@server mysql-3.XX.XX]# make install [root@server mysql-3.XX.XX]# scripts/mysql_install_db XX.XX is your mysql version Good Luck! -Original Message- From: Stephen Johnson [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 18, 2001 9:19 AM To: MySQL Subject: Changing the locations of the tables Is there a way without reinstalling MySQL to change the locations of the database tables? For example right now they are in /var/lib/mysql I want them /usr/lib/mysql Thanks in advance -- Stephen Johnson [EMAIL PROTECTED] http://www.pets4u.com Owner / Founder -- your next best friend is waiting for you -- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Innodb Win2000 error
Forwarded message: Return-Path: [EMAIL PROTECTED] From: Mehalick, Richard RE SITI-ITPSCA [EMAIL PROTECTED] To: 'Heikki Tuuri' [EMAIL PROTECTED] Subject: RE: Innodb Win2000 error Date: Tue, 18 Dec 2001 11:41:45 -0600 Thank you for responding. I was just going to post again that I solved the problem. It appears that the my.cnf file was named wrong :( I guess Notepad put a hidden extension on the file. Sorry for the trouble. Rick -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 18, 2001 11:35 AM To: Mehalick, Richard RE SITI-ITPSCA Subject: Re: Innodb Win2000 error Hi! From the MySQL online manual I read: ... There are two configuration files with the same function: `my.cnf' and `my.ini' file, however please note that only of one these should can used. Both files are plain text. The `my.cnf' file should be created in the root directory of drive C and the `my.ini' file on the WinDir directory e.g: `C:\WINDOWS' or `C:\WINNT'. If your PC uses a boot loader where the C drive isn't the boot drive, then your only option is to use the `my.ini' file. Also note that if you use the WinMySQLAdmin tool, only the `my.ini' file is used. The `\mysql\bin' directory contains a help file with instructions for using this tool. ... If the C: drive is you boot drive, but mysqld does not find C:\my.cnf then there is a bug in the current Windows version of MySQL, and I will submit a bug report to Miguel from MySQL AB. Please send me your my.cnf! You could try using my.ini instead. Error 123 in Windows means that the syntax of a file path is wrong. MySQL seems to put '.' as innodb_data_home_dir if it is not specified. You could try just mysqld --innodb_data_file_path=ibdata1:20M but to get your installation really working you have to be able to use my.ini or my.cnf Regards, Heikki At 09:26 AM 12/18/01 -0600, you wrote: I am trying to use mysql-max-3.23.46a for Windows (Win2000) with innodb in use. After installing from the setup program, I copied my-example to c:\my.cnf and uncommented the innodb lines with the following: innodb_data_file_path = ibdata1:20M innodb_data_home_dir = c:\ibdata\ set-variable = innodb_mirrored_log_groups=1 innodb_log_group_home_dir = c:\iblogs\ set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=30M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 innodb_log_arch_dir = c:\iblogs\ innodb_log_archive=0 set-variable = innodb_buffer_pool_size=80M set-variable=lower_case_table_names =0 set-variable = innodb_additional_mem_pool_size=10M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 When I start the server standalone I get the following: C:\mysql\binmysqld-max-nt.exe --standalone Cannot initialize InnoDB as 'innodb_data_file_path' is not set. If you do not want to use transactional InnoDB tables, add a line skip-innodb to the [mysqld] section of init parameters in your my.cnf or my.ini. If you want to use InnoDB tables, add for example, innodb_data_file_path = /mysql/data/ibdata1:20M But to get good performance you should adjust for your hardware the InnoDB startup options listed in section 7.6 at http://www.mysql.com/doc/ I did a search and no other my.cnf files (or my.ini) files are on my disks, so I tried to start the server with the innodb options on the command line and got the following error: C:\mysql\binmysqld-max-nt.exe --standalone --innodb_data_file_path=c:\ibdata\ibdata1:20M InnoDB: Warning: operating system error number 123 in a file operation. InnoDB: Cannot continue operation. Any help would be appreciated. Rick Mehalick Sr. Consultant Shell Information Technology International - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Changing the locations of the tables
I think the key was _without_ reinstalling. I emailed this privately first, but should work with minimum downtime and preserve data: mysqladmin -u . shutdown su cd /var/lib tar -cf - mysql | (cd /usr/lib ; tar xvBpf -) rm -rf mysql vi /etc/my.cnf :%s/\/var\/lib\/mysql/\/usr\/lib\/mysql/g :x ...restart mysqld. -Original Message- From: Bill Blowitz [mailto:[EMAIL PROTECTED]] Sent: 18 December 2001 17:54 To: 'Stephen Johnson'; 'MySQL' Subject: RE: Changing the locations of the tables Here you go:-) [root@server src]# rpm -i MySQL-3.XX.XX-1.src.rpm [root@server src]# tar -xvzf ../../src/redhat/SOURCES/mysql-3.XX.XX.tar.gz [root@server src]# cd mysql-3.XX.XX [root@server mysql-3.XX.XX]# ./configure \ --prefix=/usr/lib/mysql [root@server mysql-3.XX.XX]# make [root@server mysql-3.XX.XX]# make install [root@server mysql-3.XX.XX]# scripts/mysql_install_db XX.XX is your mysql version Good Luck! -Original Message- From: Stephen Johnson [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 18, 2001 9:19 AM To: MySQL Subject: Changing the locations of the tables Is there a way without reinstalling MySQL to change the locations of the database tables? For example right now they are in /var/lib/mysql I want them /usr/lib/mysql Thanks in advance -- Stephen Johnson [EMAIL PROTECTED] http://www.pets4u.com Owner / Founder -- your next best friend is waiting for you -- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Changing the locations of the tables
Here you go:-) [root@server src]# rpm -i MySQL-3.XX.XX-1.src.rpm [root@server src]# tar -xvzf ../../src/redhat/SOURCES/mysql-3.XX.XX.tar.gz [root@server src]# cd mysql-3.XX.XX [root@server mysql-3.XX.XX]# ./configure \ --prefix=/usr/lib/mysql [root@server mysql-3.XX.XX]# make [root@server mysql-3.XX.XX]# make install [root@server mysql-3.XX.XX]# scripts/mysql_install_db XX.XX is your mysql version Good Luck! He did say without reinstalling MySQL... This is a less drastic way of going about it: 1) Shut down mysqld 2) Copy (don't delete the originals until you're sure this works) the files to the new location. Make sure all directory/file permissions are maintained. 3) add or change the 'datadir' directive in mysql.cnf: [mysqld] ... datadir=/usr/lib/mysql ... or on the command line in your startup script: mysqld --datadir=/usr/lib/mysql 4) restart mysqld. / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq -Original Message- From: Stephen Johnson [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 18, 2001 9:19 AM To: MySQL Subject: Changing the locations of the tables Is there a way without reinstalling MySQL to change the locations of the database tables? For example right now they are in /var/lib/mysql I want them /usr/lib/mysql Thanks in advance -- Stephen Johnson [EMAIL PROTECTED] http://www.pets4u.com Owner / Founder -- your next best friend is waiting for you -- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MyODBC leaking handles...
Hi, -Original Message- From: Jens Collin [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 18, 2001 4:22 AM To: '[EMAIL PROTECTED]' Subject: MyODBC leaking handles... Hi all, This is my first post in this forum. I've tried to find something in the documentation but failed. I cannot find out why the ODBC driver is leaking 1 handle for each request that I do! It doesn't matter if I close it or whatever I do. Anyone that knows what to do? Any hints or tips? Recently we found the leaks in the initialization towards UNIX, and fixed them from driver and server, but we didn't find any thing from Windows (The Unix fixes will be there for 2.50.40 and 3.51.01) Windows 2000 Professional MySQL 3.23 MyODBC 2.50.39 Can you be more specific on this, please ? If you can send the leaks statistics, it will be a great help. Regards, Venu -- For technical support contracts, go to https://order.mysql.com __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mr. Venu [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Developer /_/ /_/\_, /___/\___\_\___/ California, USA ___/ www.mysql.com Regards, ___ Jens A. Collin Envox Technical System Developer Envox Group Söder Mälarstrand 43 118 25 Stockholm Sweden Office: +46 (0)8 56 256 000 Fax : +46 (0)8 56 256 050 [EMAIL PROTECTED] http://www.envox.com ___ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Changing the locations of the tables
Don't forget to ensure that the account that the mysqld is running under has the proper permissions to the new directory. I ran into that one before.. - Original Message - From: Matthew Smith [EMAIL PROTECTED] To: 'MySQL' [EMAIL PROTECTED] Sent: Tuesday, December 18, 2001 10:24 AM Subject: RE: Changing the locations of the tables I think the key was _without_ reinstalling. I emailed this privately first, but should work with minimum downtime and preserve data: mysqladmin -u . shutdown su cd /var/lib tar -cf - mysql | (cd /usr/lib ; tar xvBpf -) rm -rf mysql vi /etc/my.cnf :%s/\/var\/lib\/mysql/\/usr\/lib\/mysql/g :x ...restart mysqld. -Original Message- From: Bill Blowitz [mailto:[EMAIL PROTECTED]] Sent: 18 December 2001 17:54 To: 'Stephen Johnson'; 'MySQL' Subject: RE: Changing the locations of the tables Here you go:-) [root@server src]# rpm -i MySQL-3.XX.XX-1.src.rpm [root@server src]# tar -xvzf ../../src/redhat/SOURCES/mysql-3.XX.XX.tar.gz [root@server src]# cd mysql-3.XX.XX [root@server mysql-3.XX.XX]# ./configure \ --prefix=/usr/lib/mysql [root@server mysql-3.XX.XX]# make [root@server mysql-3.XX.XX]# make install [root@server mysql-3.XX.XX]# scripts/mysql_install_db XX.XX is your mysql version Good Luck! -Original Message- From: Stephen Johnson [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 18, 2001 9:19 AM To: MySQL Subject: Changing the locations of the tables Is there a way without reinstalling MySQL to change the locations of the database tables? For example right now they are in /var/lib/mysql I want them /usr/lib/mysql Thanks in advance -- Stephen Johnson [EMAIL PROTECTED] http://www.pets4u.com Owner / Founder -- your next best friend is waiting for you -- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql password
Im attempting to install mysql. The service started and everything seems to be going ok except for that I can't set the root password for the mysql server. To reset password i type: /usr/bin/mysqladmin -u root -p mypassword Then I get the following error: /usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: 'root@localhost' (Using password: YES)' Anyone, anyone? Bueller? Fry? JJ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Changing the locations of the tables
That is what the Bp flags from tar are for, and we did a su beforehand M -Original Message- From: David M. Peak [mailto:[EMAIL PROTECTED]] Sent: 18 December 2001 18:34 To: [EMAIL PROTECTED]; 'MySQL' Subject: Re: Changing the locations of the tables Don't forget to ensure that the account that the mysqld is running under has the proper permissions to the new directory. I ran into that one before.. - Original Message - From: Matthew Smith [EMAIL PROTECTED] To: 'MySQL' [EMAIL PROTECTED] Sent: Tuesday, December 18, 2001 10:24 AM Subject: RE: Changing the locations of the tables I think the key was _without_ reinstalling. I emailed this privately first, but should work with minimum downtime and preserve data: mysqladmin -u . shutdown su cd /var/lib tar -cf - mysql | (cd /usr/lib ; tar xvBpf -) rm -rf mysql vi /etc/my.cnf :%s/\/var\/lib\/mysql/\/usr\/lib\/mysql/g :x ...restart mysqld. -Original Message- From: Bill Blowitz [mailto:[EMAIL PROTECTED]] Sent: 18 December 2001 17:54 To: 'Stephen Johnson'; 'MySQL' Subject: RE: Changing the locations of the tables Here you go:-) [root@server src]# rpm -i MySQL-3.XX.XX-1.src.rpm [root@server src]# tar -xvzf ../../src/redhat/SOURCES/mysql-3.XX.XX.tar.gz [root@server src]# cd mysql-3.XX.XX [root@server mysql-3.XX.XX]# ./configure \ --prefix=/usr/lib/mysql [root@server mysql-3.XX.XX]# make [root@server mysql-3.XX.XX]# make install [root@server mysql-3.XX.XX]# scripts/mysql_install_db XX.XX is your mysql version Good Luck! -Original Message- From: Stephen Johnson [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 18, 2001 9:19 AM To: MySQL Subject: Changing the locations of the tables Is there a way without reinstalling MySQL to change the locations of the database tables? For example right now they are in /var/lib/mysql I want them /usr/lib/mysql Thanks in advance -- Stephen Johnson [EMAIL PROTECTED] http://www.pets4u.com Owner / Founder -- your next best friend is waiting for you -- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Searching mailinglist
To avoid duplicate questions, (and avoid anyone's mailbox to be blown up) I would like to point out that you can search the mysql mailinglist @ http://groups.google.com/groups?hl=engroup=mailing.database.mysql A lot of the questions I see lately have been posted in the past. Regards, Almar van Pel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Error on Windows 2000 service pack 2
If I install mysql (latest stable version) on Windows 2000, service pack 1, I can type the command d:\mysql\bin mysqld-nt --standalone, and the server runs fine. I then invoke the mysqlgui, and can do anything with table creation, reading, etc. But if I do the same thing on Windows 2000, service pack 2, I get the following error: Can't connect to MySQL server on 'localhost' (10061) Any ideas what might be causing this or how to gather more information? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql password
try: mysqladmin -u root password new_password_here - Original Message - From: Jeremiah Jester [EMAIL PROTECTED] To: 'MySQL' [EMAIL PROTECTED] Sent: Tuesday, December 18, 2001 10:50 AM Subject: mysql password Im attempting to install mysql. The service started and everything seems to be going ok except for that I can't set the root password for the mysql server. To reset password i type: /usr/bin/mysqladmin -u root -p mypassword Then I get the following error: /usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: 'root@localhost' (Using password: YES)' Anyone, anyone? Bueller? Fry? JJ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql password
Hy, Am 18 Dec 2001 10:50:40 -0800 schrieb Jeremiah Jester: /usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: 'root@localhost' (Using password: YES)' in the default installation at the first start there is no password set for the mysql root user. Note that the mysql root user isn't the same as the System root User! You can set Your initial Password with the mysqladmin program. greets, henning - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mysql password
Im attempting to install mysql. The service started and everything seems to be going ok except for that I can't set the root password for the mysql server. To reset password i type: /usr/bin/mysqladmin -u root -p mypassword Then I get the following error: /usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user: 'root@localhost' (Using password: YES)' I wonder when the mysql team will get around to changing that message... - Don't use '-p'. / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Downsides of MySQL?
There is a lot of administrative command tools for MySQL available that allows one to monitor and work with MySQL. Yes, I went looking for admin GUI's and found there to be a lot of them. I found useable ones for MacOSX, Linux and Windows. The one I settled on is mySQLfront (from a German dude) for w2k. It's even free, though I would have paid like, say, $50 or so for it. I may still send the guy a little something since it does everything I need so far without any problem. I learned how to initially get around mySQL on the command line in Linux (confusing at first!), and then went looking for a GUI. The basic understanding helped, but I'm amazed at how fast I've been able to sketch out a db, create it and have working pages. We use Cold Fusion 5 on Linux and mySQL on Linux and I use mySQLfront on w2k to access ha-ha the databases. I think another good one looked to be mascon or something from South Africa. = __ Do You Yahoo!? Check out Yahoo! Shopping and Yahoo! Auctions for all of your unique holiday gifts! Buy at http://shopping.yahoo.com or bid at http://auctions.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL and kernel upgrade
Sommai Fongnamthip [EMAIL PROTECTED] writes: Hi, I was read recommendation from MySQL download page to upgrade Linux Kernel for latest MySQL version. I have problem because I must still with Linux Red Hat 6.2 (kernel 2.2.16-3) Newer versions of the kernel has been released, and are highly recommended for security reasons. http://www.redhat.com/support/errata/RHSA-2001-142.html -- Trond Eivind Glomsrød Red Hat, Inc. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: RedHat DB?!
Deependra B. Tandukar [EMAIL PROTECTED] writes: What is RedHat Database? Postgresql. Is anybody using it? How is it? Better than MySQL? It's postgresql w/support, so the answer would be the same as regular postgresql vs. MySQL. This means that it has things like foreign keys, stored procedures, triggers, subqueries etc. which are missing in MySQL. MySQL is probably a bit more optimized for doing simple queries/inserts, though. -- Trond Eivind Glomsrød Red Hat, Inc. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Detecting OS through MySQL
Is there any way to detect the Operating System that a MySQL server is running on using SQL or DBI function calls only? -- Regards, Brent interactivetools.com, inc. Tel: (604)689-3347 - Fax: (604)689-3342 - Toll Free: 1(800)752-0455 Software for your Website - http://www.interactivetools.com/ Attachments accepted: TXT, HTML, RTF, PDF I do not accept attachments which are capable of harbouring viruses, for example Word for Windows. Please use File/Save As RTF or HTML. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mysql dies on signal 11
Description: Mysql dies in signal 11 How-To-Repeat: Don't know. Seems to happen when machine runs for more than 24 hours. Fix: Unknown. We will be trying the latest version of Mysql (3.23.46). Submitter-Id: [EMAIL PROTECTED] Originator:Petro Organization: Auctionwatch.com, Operations Department. MySQL support: login level. Synopsis: Mysql dies on signal 11 Severity: critical Priority: high Category: mysql Class: sw-bug Release: mysql-3.23.43 (Official MySQL binary) Environment: VALinux Fullon 2230, 2x600Mhz CPU, 2 Gig ram, 4x75 gig drives attached in a raid 0 config via a 3ware 6400, 1x34 gig scsi drive attached via onboard scsi controler as boot/root/log volume. Debian variant of Linux running kernel 2.4.16. System: Linux dbraux01-red.auctionwatch.com 2.4.16 #1 SMP Wed Dec 12 17:16:00 PST 2001 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make Compilation info: CC='gcc' CFLAGS='-O3 -mpentium ' CXX='gcc' CXXFLAGS='-O3 -mpentium -felide-constructors' LDFLAGS='-static' LIBC: lrwxrwxrwx1 root root 13 Dec 11 15:23 /lib/libc.so.6 - libc-2.2.3.so -rwxr-xr-x1 root root 1155720 Jul 27 13:42 /lib/libc-2.2.3.so -rw-r--r--1 root root 2579358 Jul 25 08:15 /usr/lib/libc.a -rw-r--r--1 root root 178 Jul 25 08:15 /usr/lib/libc.so Configure command: ./configure --prefix=/usr/local/mysql '--with-comment=Official MySQL binary' --with-extra-charsets=complex --with-server-suffix= --enable-assembler --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --disable-shared We have experienced a similar crashes across 3 instances of this environment. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
while...if statements???
Does anyone know why this isn't working?? What I am trying to do is display
photos from a database based on each month. This part works fine when I set
it to the current month of December (today) as that is when I uploaded the
photos.
However, when I manually set the date to a month that has no photos in it,
I want it to echo the No Animals string belowThis is only working
half-way...meaning that it will display a blank page with nothing on it
instead of actually echoing the No Animals string
Can anyone please help?? Is there really a way to have a while...if
statement
CODE=
?
$connection = mysql_connect($hostname, $user, $pass) or die (Unable to
connect!);
$query = SELECT petname, petDesc, petpicture FROM petinfo WHERE petmonth =
'Apr';
$result = mysql_db_query($database, $query, $connection) or die (Error in
query: $query. . mysql_error());
while (list($pet_name, $pet_Desc, $pet_picture) = mysql_fetch_row($result))
{
if ($pet_picture == )
{
echo No Animals have been posted for $date at this moment. Please
check
back soon.;
exit;
}
else
{
echo
FONT FACE=\Arial, Helvetica\BPet of the month/B/FONTBR
BR
IMG
SRC=\http://moss.bayou.com:/oppj/admin/animalcontrol/photos/$pet_picture\;
HEIGHT=150BR
BR
FONT FACE=\Arial, Helvetica\ SIZE=\-1\$pet_DescBR
BR
To adopt $pet_name please visit the Ouachita Parish Animal Shelter.BR
The adoption fee is \$50.00 which includes Spade, Neutering and 7-N-1
Shot.BR
BR
Sorry, we can not hold $pet_name for you (First come first serve
basis)./FONT
BRBR;
}
}
?
/CODE=
Thanks,
Confus3d
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB still gives poor analysis to the optimizer
Philip, I recommend using the MySQL STRAIGHT JOIN and USE INDEX clauses to manually force the best plan. You could also use innodb_table_monitor to check that the key value set cardinality estimates are approximately right for the tables h, pt, and p. But to put it the other way, how could the optimizer know beforehand the cardinality of the h,p partial join versus the pt,p partial join? Regards, Heikki Innobase Oy --- See http://www.innodb.com for the latest news about InnoDB Order commercial MySQL/InnoDB support at https://order.mysql.com/ On Mon, Dec 17, 2001 at 10:03:58PM -0600, Philip Molter wrote: : Is there any upcoming fix for this recurring problem? The table : handler is just giving poor data to the optimizer and the optimizer : is making bad decisions because of it. It appears to come and go, : depending on data that is in the table, what's been done, etc. Here's some specific data, because writing e-mails isn't too bright a thing to do late in the evening after struggling with a problem for the better part of the night. The crux of all this (for us, anyway) revolves around three tables in our join sequence: percept, hosts, and perceptType. percept.hid = hosts.hid (INNER) percept.ptid = perceptType.ptid (INNER) Other tables are joined in via LEFT JOINS, but they shouldn't (and don't) affect the optimization because they're just being joined in for ancillary data. So the three table structure is what I'm most concerned with. Everything is keyed properly, and this query optimizes correctly 100% of the time under MyISAM tables, and, for that matter, under PostgreSQL (which this is also being designed to run under, although Pg is worse for overall performance). So here's some row counts from the data: mysql select count(*) from hosts; = 38 mysql select count(*) from hosts where active=1;= 31 mysql select count(*) from perceptType; = 26 mysql select count(*) from perceptType where runinterval is not null;= 12 mysql select count(*) from percept; = 11305 mysql select count(*) from percept where deleted=0; = 10647 mysql select count(*) from percept p, hosts h where p.hid=h.hid and h.active=1 and p.deleted=0;= 9064 mysql select count(*) from percept p, perceptType pt where p.ptid=pt.ptid and pt.runinterval is not null and p.deleted=0;= 939 mysql select count(*) from percept p, perceptType pt, hosts h where p.hid=h.hid and p.ptid=pt.ptid and h.active=1 and pt.runinterval is not null and p.deleted=0;= 816 Here are some explains. These were taken literally 30 seconds apart as I wrote this e-mail and the optimization switched. Here is the EXPLAIN for a good match: mysql explain select count(*) from percept p, perceptType pt, hosts h where p.hid=h.hid and p.ptid=pt.ptid and h.active=1 and pt.runinterval is not null and p.deleted=0; +---++--+-+-+-+--+- + | table | type | possible_keys| key | key_len | ref | rows | Extra | +---++--+-+-+-+--+- + | pt| index | PRIMARY,ptid | ptid| 7 | NULL| 26 | where used; Using index | | p | ref| deleted,hid,ptid | ptid| 4 | pt.ptid | 412 | where used | | h | eq_ref | PRIMARY,active | PRIMARY | 4 | p.hid |1 | where used | +---++--+-+-+-+--+- + 3 rows in set (0.00 sec) Here is the EXPLAIN for a bad match: mysql explain select count(*) from percept p, perceptType pt, hosts h where p.hid=h.hid and p.ptid=pt.ptid and h.active=1 and pt.runinterval is not null and p.deleted=0; +---++--+-+-++--+-- ---+ | table | type | possible_keys| key | key_len | ref| rows | Extra | +---++--+-+-++--+-- ---+ | h | index | PRIMARY,active | active | 4 | NULL | 38 | where used; Using index | | p | ref| deleted,hid,ptid | hid | 4 | h.hid | 502 | where used | | pt| eq_ref | PRIMARY,ptid | PRIMARY | 4 | p.ptid |1 | where used | +---++--+-+-++--+-- ---+ 3 rows in set (0.00 sec Given everything seen here, is there any way to understand why these poor decisions are being made? Not only is the first query information clearly better, but the underlying table data (row counts, etc.) clearly
Installationproblem; missing MySQLAdmin
Hi, I installed MySQL-3.23.46-1.i386.rpm on my machine and it order me to set a password for the rootuser using mysqladmin, but I don't have mysqladmin on my machine, I can't find it.. what to do and how is this possible.. greets Piet - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: please help newbie
all of the information you people have given to me is great and useful,
however, I still have no comprehension as to how to make the conditional
html setup for the pages. my attempt is as thus:
#getdata.pl
globals:
$PageLimit = 0;
$PageNumber = 1;
open db:
$DSN = DBI:mysql:database=mydb;
--
connect to db:
my $dbh = DBI-connect($DSN, user, passwd ) || PrintError;
--
grab data from db-table to fill declarations-:
$sth = $dbh-prepare(qq{SELECT P.PartNumber, P.AlternatePartNumber,
P.Description, P.Quantity, P.Condition, S.CompanyName, S.Seller_ID,
P.Part_ID, S.Note FROM table1 as P, table2 as S WHERE P.Seller_ID =
S.Seller_ID AND ( P.AlternatePartNumber like %$PartialPartNumber% OR
P.PartNumber like %$PartialPartNumber% ) ORDER BY P.PartNumber LIMIT
$PageLimit,20 }) || die $dbh-errstr;
print form name=Form4 action=/cgi-bin/submit.pl method=post\n;
return 1;
print input type=hidden name=pagenumber value=$PageNumber\n;
print input type=hidden name=partialnmbrsrch
value=$PartialPartNumber\n;
print input type=submit value=\Save Items for RFQ\\n;
print input type=reset value=Reset\n;
print form action=/cgi-bin/getdata.pl method=post\n;
print script language=javascript\n;
print document.write a
href='/cgi-bin/Search.pl?PageLimit=$PageNumber+20'Next Page/a\n;
$sth-execute() || die $dbh-errstr;
while ( ($ref = $sth-fetchrow_hashref() )) {
---
$sth-finish();
$dbh-disconnect || warn $dbh-errstr;
i hope this is what you need to help me? i had to piece this together, hope
it makes sense to you guys
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB still gives poor analysis to the optimizer
On Tue, Dec 18, 2001 at 11:19:06PM +0200, Heikki Tuuri wrote: : Philip, : : I recommend using the MySQL STRAIGHT JOIN and USE INDEX clauses to manually : force the best plan. I'd love to, but I also have to maintain a spec of database agnosticism (which I could do in code, of course, but I shouldn't be forced into MySQL-specific SQL when perfectly valid SQL should [and sometimes does] work just as well). That still doesn't rectify the situation that InnoDB queries aren't being properly optimized, and I have seen it on other databases under other circumstances. : You could also use innodb_table_monitor to check that the key value set : cardinality estimates are approximately right for the tables h, pt, and p. The cardinality estimates are accurate, which makes it all the more baffling how InnoDB/the optimizer are arriving at these conclusions. : But to put it the other way, how could the optimizer know beforehand the : cardinality of the h,p partial join versus the pt,p partial join? You're right, the optimizer can't know beforehand the cardinality of the h,p vs pt,p partial join, *but* that cardinality shouldn't be changing, especially not on tables whose index fields aren't being updated. Records in those tables are being affected, yes, but those specific column entries aren't, so the h,p vs. pt,p cardinality should never be an issue once the optimal plan has been found. That's why, under MyISAM, you issue an ANALYZE TABLE on your table(s) after you've populated them with a good chunk of your data and then you generally don't need to worry about it again. Unless your data set radically shifts and you absolutely need to switch to another index, your optimizer plan doesn't need to change. ANALYZE TABLE isn't an option under InnoDB (although sometimes, it appears to help). This stuff is shifting back and forth, and it's *VERY FAR* off when it shifts the wrong way. My question is Why? * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Coredump when running scripts/mysql_install_db
While I have your attention, let me take this opportunity to report that I've run into quite a few obstacles building MySQL from source. (I reported most of these at the end of October, but have received no response.) 1) Back in August (8-8-2001, in thread Build problems (+solution) on HPUX10.20/gcc 2.95), you wrote On our HPUX 10.20 machine, we use the following configure line to build our binaries: CFLAGS=-DHPUX -I/opt/dce/include -O3 -fpic \ CXX=gcc \ CXXFLAGS=-DHPUX -I/opt/dce/include -felide-constructors -fno-exceptions -fno-rtti -O3 \ ./configure \ --prefix=/usr/local/mysql \ --with-comment=Official MySQL binary \ --with-extra-charsets=complex \ --with-server-suffix= \ --with-pthread \ --with-named-thread-libs=-ldce \ --disable-shared And this compiles without any problems. This differs from the manual. In particular, the manual leaves out the '-fpic' part of the CFLAGS. I ran into trouble building PHP and DBD::mysql before I saw your note. The manual should be fixed. 2) While this worked for 3.23.41, I cannot believe it has worked for you since 3.23.43. It doesn't for me. The problem is that somewhere between 41 and 43, mysqlbinlog got moved to the client directory (though this is not mentioned in the change log). As far as I can see, mysqlbinlog is threaded, unlike the rest of client. That is, mysqlbinlog needs -ldce (--with-named-thread-libs=). Hence, make dies with /bin/sh ../libtool --mode=link gcc -O3 -DDBUG_OFF -DHPUX -I/opt/dce/include -felide-constructors -fno-exceptions -fno-rtti -O3 -fno-implicit-templates -fno-exceptions -fno-rtti -DHAVE_BROKEN_SNPRINTF -D_INCLUDE_LONGLONG -DSIGNALS_DONT_BREAK_READ -o mysqlbinlog mysqlbinlog.o ../libmysql/libmysqlclient.la -lz -lcrypt -lm gcc -O3 -DDBUG_OFF -DHPUX -I/opt/dce/include -felide-constructors -fno-exceptions -fno-rtti -O3 -fno-implicit-templates -fno-exceptions -fno-rtti -DHAVE_BROKEN_SNPRINTF -D_INCLUDE_LONGLONG -DSIGNALS_DONT_BREAK_READ -o mysqlbinlog mysqlbinlog.o ../libmysql/.libs/libmysqlclient.a -lz -lcrypt -lm -lz -lcrypt -lm /usr/ccs/bin/ld: Unsatisfied symbols: pthread_once (code) pthread_setspecific (code) pthread_getspecific (code) pthread_keycreate (code) collect2: ld returned 1 exit status make[2]: *** [mysqlbinlog] Error 1 make[2]: Leaving directory `/scr/src/mysql-3.23.46/client' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/scr/src/mysql-3.23.46' make: *** [all-recursive-am] Error 2 every time. I have to copy that last command, cd into client, paste the last command and add '-ldce', then cd back up, and rerun make. Configure/make should have taken care of this. That is, --with-named-thread-libs=-ldce must be propagated to client/Makefile for mysqlbinlog. 3) (A minor thing I discovered by accident -- I don't need it.) If you use configure option --datadir (say --datadir=DATADIR), make test fails immediately because mysqld needs the contents of mysql-test/share but is looking for mysql-test/DATADIR. $make test cd mysql-test ; ./mysql-test-run Installing Test Databases Removing Stale Files Installing Master Databases ../sql/mysqld: Fatal error: Can't find messagefile'/src/mysql-3.23.46/mysql-test/DATADIR/mysql/english/errmsg.sys' Error: Could not install master test DBs make: *** [test] Error 1 Configure/make should either rename mysql-test/share to the value of datadir or symlink datadir to share in mysql-test, or at least a note should be added to the install docs. 4) Test rpl01 fails immediately because: The file '../../std_data/words.dat' must be in the database directory or be readable by all Clearly, configure/make should have made std_data/words.dat readable by all. 5) This is new as of 3.23.46: mysqld won't shut down. For example, make test ends with Ending Tests Shutting-down MySQL daemon Warning; Aborted waiting on pid file:'/src/mysql-3.23.46/mysql-test/var/run/mysqld.pid' after 20 seconds Master shutdown finished Warning; Aborted waiting on pid file:'/src/mysql-3.23.46/mysql-test/var/run/mysqld-slave.pid' after 20 seconds Slave shutdown finished but ps shows mysqld still running, and kill -9 is required to get rid of it. I note that one of the changes from .45 to .46 was to fix this exact problem for Mac OSX, so I wonder if this change broke things for HPUX 10.20. Michael - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
removing duplicates
hi! i have a table with alot duplicates in them. the tables has about 2 million rows and i expect that there are ca. 20.000 duplicates which have to be removed.. what i basicly wanna do is: delete rows from that table which have the same value on the column col1, col2 and col3 of that table. now the tricky part though: i only wanna keep the row with the highes fooId in that table.. all other duplicates should be deleted.. for example when i have those rows: fooId col1col2col3 1 bla yo orange 2 bla yo orange 3 bla yo orange 4 super yo orange 5 hey hey hey 6 hey hey hey it should delete the rows with the fooId: 1, 2 and 6... whats the easiest way to do that stuff with mysql? thanks alot in advance =) ciao! florian -- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Query suddently stopped working
I was doing testing on a query today and it suddenly stopped returning any results. Each time I would run the query it would return no records. I ran it from the console utility and from a web page using ASP with the same result. I also ran it from a GUI windows client (DBTools) with the same result. I had been doing some script work and accidentally wrote something that stuck in an infinite loop prior to the query stopping. When I went to the server it had a low on virtual memory message so I assumed that the two were connected. I rebooted the server and it still refused to return records. At that point I did a simple SELECT * FROM tablename in the console and it DID return records. Frustrated, I tried my original query it and it would then return records. Since that time I have tried, without success, to re-create the event. Any ideas? I posted my setup and the query below. This is what was called from the ASP page: Dim objRS Set objRS = Server.CreateObject(ADODB.Recordset) strSQL = SELECT r.recID,r.recFlagged,r.recChannel,r.recBegin,r.recEnd,r.recFile FROM recordings AS r WHERE ((r.recBegin BETWEEN ' dtmSearchBegin ' AND ' dtmSearchEnd ') OR (r.recEnd BETWEEN ' dtmSearchBegin ' AND ' dtmSearchEnd ')) AND (r.recChannel intSearchChannel ) ORDER BY r. strSearchOrderBy LIMIT intSearchRowBegin , intSearchRowMax objRS.Open strSQL, objConn - MySQL - MyODBC - Windows 2000 SP2 - Active Server Pages (ASP) Please reply to my e-mail address..thanks! Jeff Phillips [EMAIL PROTECTED] VoiceLogger, Inc. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Reset MySQL Root password
Well it seems as though i may have mistyped my root password for mysql since nothing seems be working. Anybody no how to set a new root password or how to reset the old password in mysql? Already tried uninstalling the RPM from in hopes to redo the install but when i try it tells me the package hasn't been installed. odd. When i try to install (reinstall) the RPM it tells me the package cannot install because it is already installed. Grrr. JJ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [PHP] Working with designers...
As a designer and a coder, I have always found that it is easiest to have the layout of the page done before adding the dynamic content. If the layout is done correctly, it should not be necessary to put in sample data that has to be removed after. After joining the workforce, as a database programmer, I now strictly code the backend stuff, and as before, the layouts are done and I simply add the dynamic content. Whatever arrangement you end up with, just make sure the ground rules are in place before you start. It may help to have them written down (I know, coders hate to document), as it will protect you in the future if problems do arise. Good luck, Steve Osborne Database Programmer Chinook Multimedia Inc. [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [PHP] Working with designers...SORRY
Oops !! Sorry, wrong list. Steve - Original Message - From: Steve Osborne [EMAIL PROTECTED] To: MySQL (E-mail) [EMAIL PROTECTED]; Mike Eheler [EMAIL PROTECTED] Sent: Tuesday, December 18, 2001 3:49 PM Subject: Re: [PHP] Working with designers... As a designer and a coder, I have always found that it is easiest to have the layout of the page done before adding the dynamic content. If the layout is done correctly, it should not be necessary to put in sample data that has to be removed after. After joining the workforce, as a database programmer, I now strictly code the backend stuff, and as before, the layouts are done and I simply add the dynamic content. Whatever arrangement you end up with, just make sure the ground rules are in place before you start. It may help to have them written down (I know, coders hate to document), as it will protect you in the future if problems do arise. Good luck, Steve Osborne Database Programmer Chinook Multimedia Inc. [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problems with grant/permissions
I duplicated the mysql database from another machine by copying over the table files. But now I find myself unable to access the tables. I restarted the mysql server using the --skip-grant-tables option. Then I could access the tables, but the grant command wasn't recognized so I couldn't just add my root id to the permissions. I tried adding myself by inserting rows into the user and host tables, but it still didn't work. Anybody got any ideas? I want to get this cleaned up so I can slave the new copy to the old one, to act as a backup server. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Backups
Looking over the documentation for MySQL, there doesn't seem to be a consensus on the best way to perform backups of MySQL databases. So far, I have come up with the following ideas: 1. Just copy the files in the data directory to wherever I want them backed up. This doesn't work too well because the files are in use as I copy them. 2. Shutdown the database server, copy the actual files in the data directory. I can't use this method because I can't have the database down for the amount of time it would take to copy this data. At least, I can't have the database server down like this on a daily basis. 3. Perform a database dump (using mysqldump) of the data to a file. The problem with this is that the data is very big in that format, and the backup takes a long time. 4. Perform a hot copy of the databases (using mysqlhotcopy). This also seems to take a while, but at least the database server does not have to shutdown. The trouble with this is that it locks the table from writes when it does the copy. Some of my tables take a while to copy (they are hundreds of MBs in size), and are written to quite often, so this option (while the best so far) also has its shortcomings. 5. Use MySQL replication to create another database server with the identical data. Then shutdown the database server on that machine and run a backup (like in #2). This way will not require shutting down the real backup server at all, so therefore the backup can take as long as I want. The downside to this approach is that the data being backed up is not necessarily the most up-to-date. If the backup takes 2 hours, the databases near the end of the backup can have some significant changes by then. However, this disadvantage is diminished because when the backup is done, I restart the slave server and the data replicates back down again. So, we have online database backups on the actual slave server and then we do daily (probably could be every couple of hours) tape backups of the data on the slave. Does anyone see a problem with idea #5? Does anyone have any better ideas for performing backups? Dave Greco [EMAIL PROTECTED] Network Administrator Gleim Publications, Inc. (800) 87-GLEIM x312 http://www.gleim.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Reset MySQL Root password
Am 18 Dec 2001 14:35:07 -0800 schrieb Jeremiah Jester: Well it seems as though i may have mistyped my root password for mysql since nothing seems be working. Anybody no how to set a new root password or how to reset the old password in mysql? Already tried uninstalling the RPM from in hopes to redo the install but when i try it tells me the package hasn't been installed. odd. When i try to install (reinstall) the RPM it tells me the package cannot install because it is already installed. Grrr. Simply uninstall wouldn't help as most distros will let the data tables on the system. You have to uninstall AND go to /var/mysql/... or wherever Mysql stores your Tables and remove them, too. You can, without uninstalling, remove all the mysql tables from /var/lib/mysql/mysql/ or wherever mysql stores your tables, they are called *.MYD, *.MYI, *.frm and then run mysql_install_db again. That will set up new user tables without any root password set. but maybe somebody else knows a better way... greets,henning - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problems with grant/permissions
Check the file ownership, and make sure that its the same as the mysql user. Barry Gold wrote: I duplicated the mysql database from another machine by copying over the table files. But now I find myself unable to access the tables. I restarted the mysql server using the --skip-grant-tables option. Then I could access the tables, but the grant command wasn't recognized so I couldn't just add my root id to the permissions. I tried adding myself by inserting rows into the user and host tables, but it still didn't work. Anybody got any ideas? I want to get this cleaned up so I can slave the new copy to the old one, to act as a backup server. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Backups
Another suggestion I saw someone make that seems reasonable is to use a break away mirror for backups. I think they lock the tables for a minute break the mirror and unlock the tables. Then they backup the broken mirror at their leisure. Dave On Tue, Dec 18, 2001 at 06:00:49PM -0500, Dave Greco wrote: Looking over the documentation for MySQL, there doesn't seem to be a consensus on the best way to perform backups of MySQL databases. So far, I have come up with the following ideas: 1. Just copy the files in the data directory to wherever I want them backed up. This doesn't work too well because the files are in use as I copy them. 2. Shutdown the database server, copy the actual files in the data directory. I can't use this method because I can't have the database down for the amount of time it would take to copy this data. At least, I can't have the database server down like this on a daily basis. 3. Perform a database dump (using mysqldump) of the data to a file. The problem with this is that the data is very big in that format, and the backup takes a long time. 4. Perform a hot copy of the databases (using mysqlhotcopy). This also seems to take a while, but at least the database server does not have to shutdown. The trouble with this is that it locks the table from writes when it does the copy. Some of my tables take a while to copy (they are hundreds of MBs in size), and are written to quite often, so this option (while the best so far) also has its shortcomings. 5. Use MySQL replication to create another database server with the identical data. Then shutdown the database server on that machine and run a backup (like in #2). This way will not require shutting down the real backup server at all, so therefore the backup can take as long as I want. The downside to this approach is that the data being backed up is not necessarily the most up-to-date. If the backup takes 2 hours, the databases near the end of the backup can have some significant changes by then. However, this disadvantage is diminished because when the backup is done, I restart the slave server and the data replicates back down again. So, we have online database backups on the actual slave server and then we do daily (probably could be every couple of hours) tape backups of the data on the slave. Does anyone see a problem with idea #5? Does anyone have any better ideas for performing backups? Dave Greco [EMAIL PROTECTED] Network Administrator Gleim Publications, Inc. (800) 87-GLEIM x312 http://www.gleim.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
packets involved in connecting to mysql
I tried to connect to mysql on another machine, but can't get through: ERROR 2003: Can't conect to MySQL server on 'other host' (111) I think this is because the two machines are in different domains, and the packets are being blocked by a firewall. Anybody know what I need to enable in the firewall to let the packets go through? Also, will this same port/packet-type/whatever work for slaving one database to another to act as a backup server.? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mysql compress_gz()/uncompress_gz() user defined functions
After sending my earlier email regarding compress_gz()/uncompress_gz() in
mysqld, I discovered user defined functions -- something I had never used
in mysqld until today.
So, I took a couple of hours and wrote a Mysql UDF to do what I requested:
# gcc -shared -DMYSQL_SERVER -DDEFAULT_MYSQL_HOME=\/usr/local/mysql\
-DDATADIR=\/usr/local/mysql/var\ -DSHAREDIR=\/usr/local/mysql/share/mysql\
-DHAVE_CONFIG_H -I./../include -I./../regex -I. -I../include -I.. -I.
-I/usr/local/mysql/include -O3 -DDBUG_OFF -export-dynamic -fPIC
-fno-implicit-templates -o mysqld_udf_zlib.so mysqld_udf_zlib.cc /usr/lib/libz.a
# cp ./mysqld_udf_zlib.so /usr/lib; ldconfig
# mysql mysql
mysql CREATE FUNCTION compress_gz RETURNS STRING SONAME mysqld_udf_zlib.so; CREATE
FUNCTION uncompress_gz RETURNS STRING SONAME mysqld_udf_zlib.so;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql select compress_gz('lester');
+---+
| compress_gz('lester') |
+---+
| xËI-.I-a |
+---+
1 row in set (0.00 sec)
mysql select uncompress_gz(compress_gz('lester'));
+--+
| uncompress_gz(compress_gz('lester')) |
+--+
| lester |
+--+
1 row in set (0.01 sec)
mysql drop function compress_gz; drop function uncompress_gz;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
It is late and I am going home for the evening. Tomorrow I will review
the code carefully, clean it up a bit, comment it well, and will then be
happy to contribute it to MySQL AB, if you guys are interested in having
it in the contributed software page. Is there a procedure for contribing
the code to you guys, or just email it to the mysql list, or what?
--
Lester H. Hightower TheAIMSGroup.com
Chief Technology Officer
The Advanced Integrated Management Solutions Group
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Backups
Dave Greco wrote: Looking over the documentation for MySQL, there doesn't seem to be a consensus on the best way to perform backups of MySQL databases. So far, I have come up with the following ideas: 1. Just copy the files in the data directory to wherever I want them backed up. This doesn't work too well because the files are in use as I copy them. 2. Shutdown the database server, copy the actual files in the data directory. I can't use this method because I can't have the database down for the amount of time it would take to copy this data. At least, I can't have the database server down like this on a daily basis. 3. Perform a database dump (using mysqldump) of the data to a file. The problem with this is that the data is very big in that format, and the backup takes a long time. 4. Perform a hot copy of the databases (using mysqlhotcopy). This also seems to take a while, but at least the database server does not have to shutdown. The trouble with this is that it locks the table from writes when it does the copy. Some of my tables take a while to copy (they are hundreds of MBs in size), and are written to quite often, so this option (while the best so far) also has its shortcomings. 5. Use MySQL replication to create another database server with the identical data. Then shutdown the database server on that machine and run a backup (like in #2). This way will not require shutting down the real backup server at all, so therefore the backup can take as long as I want. The downside to this approach is that the data being backed up is not necessarily the most up-to-date. If the backup takes 2 hours, the databases near the end of the backup can have some significant changes by then. However, this disadvantage is diminished because when the backup is done, I restart the slave server and the data replicates back down again. So, we have online database backups on the actual slave server and then we do daily (probably could be every couple of hours) tape backups of the data on the slave. Does anyone see a problem with idea #5? Does anyone have any better ideas for performing backups? Dave Greco [EMAIL PROTECTED] Network Administrator Gleim Publications, Inc. (800) 87-GLEIM x312 http://www.gleim.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php David, You to distinguished if you want hotbackups or cold backups. What the difference? Hotbackups means that the database server is never shutdown to perform the backups. Cold Backups means the database server is shutdown to perform backups. The reason for the difference is to protect the data integrity. So just doing a copy of the data can be a no no unless you stop the database server. If you going to do hot backup, usually export the database to a file. Place that export to a filesystem other then current database filesystem. Then you can completely backup that filesystem without ever having to stop the database server at all. Then when it comes time to restore you can restore database much easier this way. This also eliminates the need of shutting critical database that are backend to application and web servers that maybe running. The biggest reason is that coping data while the database server is up and running can cause corruption of the data as open files are an issue. But I know there are other application that help in getting around this. I'm not near familiar with mysql as I am with Oracle, Sybase, or Informix databases. Haven't played with DB2 but might in the future. -- *** Phillip B. Bruce *** *** http://pbbruce.home.mindspring.com *** *** [EMAIL PROTECTED] *** *** *** *** Have you ever noticed? Anybody going slower than*** *** you is an idiot, and anyone going faster than you*** *** is a maniac. - George Carlin*** - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql usernames
Hi, I've changed the length of the field 'user' in the table 'user' in the mysql DB, to varchar(128), because we need longer names to identify our users based on their domain names. Something like: each user is identified by a valid domain name. The same thing we are doing with the DB field. The thing is that even though I changed the length of 'user' and 'db' to varchar(128) binary in the tables 'user', 'db' and 'host', I'm getting the login name truncated in the 32th character. If the limitation was 16 character before the changes, why am I getting this 32 characters limitation after the changes? Any ideas? Thanks, Gustavo - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Show slave/master status permissions
What are the chances that mysql in the future will allow someone other then a user with process privileges to check the status of the master and or slaves. I would like to write a script to monitor this, but I dont want to put a script out there with process privileges. Thanks, Mike - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mysql query it too slow in big table
###table struct of COLL_DATA mysql desc COLL_DATA; +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | TASK_ID | bigint(20) | YES | | NULL | | | E_TYPE_ID | int(11) | YES | | NULL | | | IP_ADDR | varchar(50) | YES | MUL | NULL | | | ERR_TYPE_ID | int(11) | YES | | NULL | | | INST_OID | varchar(128) | YES | MUL | NULL | | | INST_VALUE | varchar(128) | YES | | NULL | | | INST_TYPE | int(11) | YES | | NULL | | | RSLT_STATUS | int(11) | YES | | NULL | | | THE_TIME | datetime | YES | MUL | NULL | | | THE_SN | bigint(20) | YES | | NULL | | | IS_SUCCESS | decimal(1,0) | YES | | NULL | | +-+--+--+-+-+---+ 11 rows in set (0.00 sec) ###COLL_DATA's index mysql show index from COLL_DATA; +---+++--+-+---+-+--++-+ + | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality || Sub_part | Packed | Comment | +---+++--+-+---+-+--++-+ + | COLL_DATA | 1 | RELATION_99_FK | 1 | IP_ADDR | A | 86 | NULL | NULL | | | COLL_DATA | 1 | RELATION_100 | 1 | INST_OID | A | 2625 | NULL | NULL | | | COLL_DATA | 1 | IDX_THE_TIME | 1 | THE_TIME | A | 8226 | NULL | NULL | | +---+++--+-+---+-+--++-+ + 3 rows in set (0.00 sec) ###COLL_DATA? record count mysql select count(*) from COLL_DATA; +--+ | count(*) | +--+ | 1357454 | +--+ 1 row in set (0.00 sec) ###query the last time mysql select max(THE_TIME) from COLL_DATA; +-+ | max(THE_TIME) | +-+ | 2001-11-26 14:38:05 | +-+ 1 row in set (0.00 sec) ###query the last time where ip='172.017.011.253' mysql select max(THE_TIME) from COLL_DATA where IP_ADDR='172.017.011.253'; +-+ | max(THE_TIME) | +-+ | 2001-11-26 14:35:18 | +-+ 1 row in set (6.77 sec) It's too slowly mysql explain select max(THE_TIME) from COLL_DATA where IP_ADDR='172.017.011.253'; +---+--+++-+---+++ + | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+++-+---+++ + | COLL_DATA | ref | RELATION_99_FK | RELATION_99_FK | 51 | const | 669429 | where used || +---+--+++-+---+++ + 1 row in set (0.00 sec) ~~~only used ip_addr as index ###Benchmark mysql select benchmark(100,1+1); ++ | benchmark(100,1+1) | ++ | 0 | ++ 1 row in set (0.13 sec)
Re: Mysql query it too slow in big table
###query the last time where ip='172.017.011.253' mysql select max(THE_TIME) from COLL_DATA where IP_ADDR='172.017.011.253'; +-+ | max(THE_TIME) | +-+ | 2001-11-26 14:35:18 | +-+ 1 row in set (6.77 sec) It's too slowly Try storing the IP address as a numeric type, possibly decimal(10), and using the functions INET_NTOA and INET_ATON to convert from the address back and forth between dotted hexadecimal and decimal. This will help because instead of searching for MySQL having to search a varchar(50) field, it can search a numeric field, which of course is faster. Ryan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql query it too slow in big table
In the last episode (Dec 19), xutian said: ###COLL_DATA's index mysql show index from COLL_DATA; +---+++--+-+---+-+--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +---+++--+-+---+-+--++-+ | COLL_DATA | 1 | RELATION_99_FK | 1 | IP_ADDR | A | 86 | NULL | NULL | | | COLL_DATA | 1 | RELATION_100 | 1 | INST_OID | A | 2625 | NULL | NULL | | | COLL_DATA | 1 | IDX_THE_TIME | 1 | THE_TIME | A | 8226 | NULL | NULL | | +---+++--+-+---+-+--++-+ ###query the last time where ip='172.017.011.253' mysql select max(THE_TIME) from COLL_DATA where IP_ADDR='172.017.011.253'; +-+ | max(THE_TIME) | +-+ | 2001-11-26 14:35:18 | +-+ 1 row in set (6.77 sec) It's too slowly Try creating an index on (IP_ADDR,THE_TIME). Mysql will only use one index for a table per query. Mysql will be able to use the 2-field index for both the WHERE and MAX parts of the query and won't have to touch the table at all. -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
moving MS Access to mySQL
I'm running MS Access DB roughly 30K records with several users accessing it at the same time (that's what worries me the most) so I'm looking into moving to some SQL server. I have tried exporting the tables and still using the access forms as a GUI through ODBC. I tested MSDE, MS SQL, mySQL which was the fastest but still slower than MS access file it's self, Especially using Continuous Forms My question is : (I'm not an expert, I know just enough to setup simple data base and use it..) What am I doing wrong? Are there some settings in access, ODBC or mySQL I do not know of.. Or perhaps should I forget access as GUI What are my other options. Thanks for any help - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
ISP problems with 3.22.32
My ISP, WebHosting, has been hosting one of my sites for about 5 years. I use MySQL to support the site and build the pages. About a month ago, I started getting the following message when I tried to use the command line interface: mysql select * from Properties; ERROR 1016: Can't open file: 'Properties.ISM'. (errno: 138) The techs at the ISP say that they don't know what happened, but they couldn't help me! Does anyone know what might have happened, and, if so, could you tell me how to fix it? I've got about 20Meg of data that I don't want to loose (no, I DON'T have a backup! That's what I was trying to do when this happened. Any suggestions? -- Bruce Hodo - Webmaster, GetAwayNetwork, Inc. ==Providing unique vacation information on the World Wide Web== For Villas, Resorts, Hotels, Air/Hotel Packages, Charter Airfares === Visit us at http://getawaynet.com === - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SELECT HELP
How do I use the select feature in this situation. I have a total of 5 fields that deal with names.. Three of the five are completely separate, and use one field for both first and last name. the last two fields are the first and last name of the person submitting the information.. When I do a select if someone searches for joe or smith it will pull up the info on all fields. but if someone searches for joe smith it will ONLY pull up the entries that match in the first three fields. it is imperative that I make this work. I can't change the layout of the table at this point, but I'm sure there's a way to combine two fields in the select and try to match two fields in the table to one field in the search form.. Here's a sample of the code.. (SELECT * FROM newsibsdatabase WHERE (firstname LIKE '%$searchname%' OR lastname LIKE '%$searchname%' OR adopteename LIKE '%$searchname%') now, can I add in a option that is something like this OR (firstname lastname LIKE '%$searchname$') how would this work.. Thanks in advance.. Jake. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: SELECT HELP
I believe you could use CONCAT(firstname, ' ', lastname) LIKE '%$searchname%' if you leave the space out of the middle it probably won't work, and you might want to trim firstname lastname to make sure no leading or trailing spaces exist. Hope this helps, P -Original Message- From: *Himerus* [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 18, 2001 10:09 PM To: [EMAIL PROTECTED] Subject: SELECT HELP How do I use the select feature in this situation. I have a total of 5 fields that deal with names.. Three of the five are completely separate, and use one field for both first and last name. the last two fields are the first and last name of the person submitting the information.. When I do a select if someone searches for joe or smith it will pull up the info on all fields. but if someone searches for joe smith it will ONLY pull up the entries that match in the first three fields. it is imperative that I make this work. I can't change the layout of the table at this point, but I'm sure there's a way to combine two fields in the select and try to match two fields in the table to one field in the search form.. Here's a sample of the code.. (SELECT * FROM newsibsdatabase WHERE (firstname LIKE '%$searchname%' OR lastname LIKE '%$searchname%' OR adopteename LIKE '%$searchname%') now, can I add in a option that is something like this OR (firstname lastname LIKE '%$searchname$') how would this work.. Thanks in advance.. Jake. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql query it too slow in big table
- Original Message - From: Dan Nelson [EMAIL PROTECTED] To: xutian [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, December 19, 2001 12:27 PM Subject: Re: Mysql query it too slow in big table In the last episode (Dec 19), xutian said: ###COLL_DATA's index mysql show index from COLL_DATA; +---+++--+-+ ---+-+--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +---+++--+-+ ---+-+--++-+ | COLL_DATA | 1 | RELATION_99_FK | 1 | IP_ADDR | A | 86 | NULL | NULL | | | COLL_DATA | 1 | RELATION_100 | 1 | INST_OID | A | 2625 | NULL | NULL | | | COLL_DATA | 1 | IDX_THE_TIME | 1 | THE_TIME | A | 8226 | NULL | NULL | | +---+++--+-+ ---+-+--++-+ ###query the last time where ip='172.017.011.253' mysql select max(THE_TIME) from COLL_DATA where IP_ADDR='172.017.011.253'; +-+ | max(THE_TIME) | +-+ | 2001-11-26 14:35:18 | +-+ 1 row in set (6.77 sec) It's too slowly Try creating an index on (IP_ADDR,THE_TIME). Mysql will only use one index for a table per query. Mysql will be able to use the 2-field index for both the WHERE and MAX parts of the query and won't have to touch the table at all. -- Dan Nelson [EMAIL PROTECTED] It's no use when I create a index on (IP_ADDR,THE_TIME). I think that maybe the result set of the query is too big to query quickly. I 've test some other query just like this , the result is the same. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
