Waste of data while using MySQL database
Hi! I have LAN with some PC, which use MySQL database. Sometime power supplay failure occurs. I have back-UPS too, but them can't hold power for 2 or more hours and because it server loose power supplay and just stop (without correct system shut-down). I got such problem: I waste data in this situation (users waste what them did). As I understand, MySQL server don't write data on disc immediatly, but after some time. What can I do for saving data regularly (for example once at hour or something like that)? With best regards - Andis Grasis Latvia
How to ROUND numbers
How to round the result number after the multiplication, below are the PHP codes, any help greatly appreciated ?php If (@$rcurr ==) { echo (); ? ?php while ( $row = mysql_fetch_array($result) ) { echo(tr); echo(td . $row[Room_Category] . /td); echo(td . $row[Room_Size] . /td); echo(td align='right' . $row[xsp] . /td); echo(td align='right' . $row[xan] . /td); echo(/tr); } echo(/table); ? ?php } else { ? ?php while ( $row = mysql_fetch_array($result) ) { echo(tr); echo(td . $row[Room_Category] . /td); echo(td . $row[Room_Size] . /td); echo(td align='right' . $row[xsp]*$rcurr . /td); echo(td align='right' . $row[xan]*$rcurr . /td); echo(/tr); } echo(/table); } ? - 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: Waste of data while using MySQL database
Hi Andis. I have no experience with this, but once your UPS kicks in can you schedule a graceful shutdown after some number of minutes? On my Red Hat 7.0 machine, I see this in /etc/inittab: # When our UPS tells us power has failed, assume we have a few minutes # of power left. Schedule a shutdown for 2 minutes from now. # This does, of course, assume you have powerd installed and your # UPS connected and working correctly. pf::powerfail:/sbin/shutdown -f -h +2 Power Failure; System Shutting Down # If power was restored before the shutdown kicked in, cancel it. pr:12345:powerokwait:/sbin/shutdown -c Power Restored; Shutdown Cancelled Maurice On Wed, Jul 25, 2001 at 10:01:27AM -0700, Andis wrote: Hi! I have LAN with some PC, which use MySQL database. Sometime power supplay failure occurs. I have back-UPS too, but them can't hold power for 2 or more hours and because it server loose power supplay and just stop (without correct system shut-down). I got such problem: I waste data in this situation (users waste what them did). As I understand, MySQL server don't write data on disc immediatly, but after some time. What can I do for saving data regularly (for example once at hour or something like that)? With best regards - Andis Grasis Latvia -- Maurice Aubrey [EMAIL PROTECTED] MySQL 3.22.32: up 6.0 days, processed 412,923,893 queries (802/sec. avg.) - 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
installing mysql
Hi, I have a problem. I have unzip the file with winzip, but I couldn't get it to install. I downloaded both the 3.22 and 3.23 verison, but I still couldn't get it to work as install. my teacher in school told me to install it with winzip and then use mysqladmin to work, but I couldn't get either working. is there something I am missing? thank you so much, and your help is high appreciated :) yours sincerely, Maggie
Re: Waste of data while using MySQL database
Andis wrote: I have LAN with some PC, which use MySQL database. Sometime power supplay failure occurs. I have back-UPS too, but them can't hold power for 2 or more hours and because it server loose power supplay and just stop (without correct system shut-down). I got such problem: I waste data in this situation (users waste what them did). As I understand, MySQL server don't write data on disc immediatly, but after some time. What can I do for saving data regularly (for example once at hour or something like that)? mysql -e "FLUSH TABLES" writes everything down and closes all files. OS can then maybe delay it and you should issue "sync". -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Tonu Samuel [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Security Administrator /_/ /_/\_, /___/\___\_\___/ Hong Kong, China ___/ 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
Bad optimization on ORDER BY .. DESC.
Hello All, When I use order by .. DESC in query, time always more then in order by .. ASC case. Explain say Using filesort,in DESC case query. Why??? Why I can't use key value in select like that: select * from TableName where KEY(KEYNAME)concat('value1','value2') or select * from TableName order by KEY(KEYNAME) I think in that case query optimizing will be more simple. example: CREATE TABLE cSectText ( SectID int(11) NOT NULL default '0', ID int(11) NOT NULL default '0', Date date NOT NULL default '-00-00', PRIMARY KEY (ID,SectID), UNIQUE KEY SectID (SectID,Date,ID) ) TYPE=MyISAM; mysql select * from cSectText where SectID=1 order by date desc limit 1; ++---++ | SectID | ID| Date | ++---++ | 1 | 35131 | 2001-07-16 | ++---++ 1 row in set (0.15 sec) mysql select * from cSectText where SectID=1 order by date limit 1; ++---++ | SectID | ID| Date | ++---++ | 1 | 31118 | 1993-02-15 | ++---++ 1 row in set (0.00 sec) mysql explain select * from cSectText where SectID=1 order by date desc limit 1; +---+--+---++-+---+---+-+ | table | type | possible_keys | key| key_len | ref | rows | Extra | | +---+--+---++-+---+---+-+ | cSectText | ref | SectID| SectID | 4 | const | 21892 | where used; |Using index; Using filesort | +---+--+---++-+---+---+-+ 1 row in set (0.00 sec) mysql explain select * from cSectText where SectID=1 order by date limit 1; +---+--+---++-+---+---+-+ | table | type | possible_keys | key| key_len | ref | rows | Extra | | +---+--+---++-+---+---+-+ | cSectText | ref | SectID| SectID | 4 | const | 21892 | where used; |Using index | +---+--+---++-+---+---+-+ 1 row in set (0.00 sec) -- Best regards, Artem mailto:[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
How to round
How to round the result number after the multiplication, below are the PHP codes, any help greatly appreciated ?php If (@$rcurr ==) { echo (); ? ?php while ( $row = mysql_fetch_array($result) ) { echo(tr); echo(td . $row[Room_Category] . /td); echo(td . $row[Room_Size] . /td); echo(td align='right' . $row[xsp] . /td); echo(td align='right' . $row[xan] . /td); echo(/tr); } echo(/table); ? ?php } else { ? ?php while ( $row = mysql_fetch_array($result) ) { echo(tr); echo(td . $row[Room_Category] . /td); echo(td . $row[Room_Size] . /td); echo(td align='right' . $row[xsp]*$rcurr . /td); echo(td align='right' . $row[xan]*$rcurr . /td); echo(/tr); } echo(/table); } ? - 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: installing mysql
if you are working on windows, unzip the file using winzip classic and then use the extract function to extract the files to a desired directory. after that, just click on setup.exe to setup mysql. regards shane -Original Message- From: Maggie Cheang [mailto:[EMAIL PROTECTED]] Sent: Wednesday, July 25, 2001 3:32 PM To: [EMAIL PROTECTED] Subject: installing mysql Hi, I have a problem. I have unzip the file with winzip, but I couldn't get it to install. I downloaded both the 3.22 and 3.23 verison, but I still couldn't get it to work as install. my teacher in school told me to install it with winzip and then use mysqladmin to work, but I couldn't get either working. is there something I am missing? thank you so much, and your help is high appreciated :) yours sincerely, Maggie - 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
Sorting w/ leading articles ignored
Can this be done? What I want is to generate a list of journal sorted by the titles, ignoring the leading articles (the, a, an, etc.). If there is no SQL statement for this, How could I do this w/ DBI or PHP? I am using mysql 3.23.40 on Solaris 7. Thanks for any advise. Bin - 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
Invalid SQL..?
hi all.. this error came out after i make backup for my database using mysqldump what this error say is wrong... maybe my database is corrupt or crash or something like that.. any command can fix this error..?. any idea what i should do. Database error: Invalid SQL: select id, subid from policy where id='2' and subid='0' MySQL Error: 1054 (Unknown column 'id' in 'field list') Session halted. -- Get your free email from www.linuxmail.org Powered by Outblaze - 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: Invalid SQL..?
hi all.. this error came out after i make backup for my database using mysqldump what this error say is wrong... maybe my database is corrupt or crash or something like that.. any command can fix this error..?. any idea what i should do. Please paste the output of typing DESCRIBE policy; into the mysql client. Database error: Invalid SQL: select id, subid from policy where id='2' and subid='0' MySQL Error: 1054 (Unknown column 'id' in 'field list') Session halted. Now there's a pointless SQL query... heh - 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
Upgrade/installation problem
Ok problem. Upgrade from 3.23.37 to 3.23.40 on linux readhat 7.0 - using rpm and after the uppdate I get : Cant find file ./mysql/host.frm (error: 13) so I copied the db and run mysql_install_db, still get the same msg, So I checked the error 13 = permission denined. So I doublecheck tthe permission, and even tried to give ugo=rwx on all the files, same error. So Just to se if it for some reason read from another datadit i removed the datadir, and I get Table mysql.host doesn't exists (= another error) wich tells me that it actualy is reading from the right datadir. So I tried to uninstall all the mysql rpm and installing it againg same error so what can be wrong Ran out of clues. The installation has been upgraded several times before with any problems. /roger
Re: Sorting w/ leading articles ignored
In PHP specifically, no clue. Using Perl, something like so (and this assumes a 1:1 mapping on titles/ids): my $get_art_list = $sql-prepare(eosql); SELECT title, id FROM table eosql $get_art_list-execute; my %articles; while (my $art = $get_art_list-fetchrow_hashref) { $articles{$art-{'title'}} = $articles-{$art-{'id'};} my @ordered = map {$articles{$_}} sort map {/^((a)|(an)|(the))\s+(.*)/?$3:$_} keys %articles; # you now have @ordered, which has key ids in order the way you want them. Dodger - Original Message - From: Bin Zhang [EMAIL PROTECTED] To: Msql-Mysql-Modules [EMAIL PROTECTED]; Mysql [EMAIL PROTECTED] Sent: Wednesday, July 25, 2001 5:20 AM Subject: Sorting w/ leading articles ignored Can this be done? What I want is to generate a list of journal sorted by the titles, ignoring the leading articles (the, a, an, etc.). If there is no SQL statement for this, How could I do this w/ DBI or PHP? I am using mysql 3.23.40 on Solaris 7. Thanks for any advise. Bin - Please check http://www.mysql.com/Manual_chapter/manual_toc.html; before posting. To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, send a message to the address shown in the List-Unsubscribe header of this message. If you cannot see it, e-mail [EMAIL PROTECTED] instead. - 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: Upgrade/installation problem
Error 13 is permission denied. Mysql does not own the files. chown -R mysql /var/lib/mysql ( Or whatever ) Roger Westin wrote: Ok problem. Upgrade from 3.23.37 to 3.23.40 on linux readhat 7.0 - using rpm and after the uppdate I get : Cant find file ./mysql/host.frm (error: 13) so I copied the db and run mysql_install_db, still get the same msg, So I checked the error 13 = permission denined. So I doublecheck tthe permission, and even tried to give ugo=rwx on all the files, same error. So Just to se if it for some reason read from another datadit i removed the datadir, and I get Table mysql.host doesn't exists (= another error) wich tells me that it actualy is reading from the right datadir. So I tried to uninstall all the mysql rpm and installing it againg same error so what can be wrong Ran out of clues. The installation has been upgraded several times before with any problems. /roger -- Gerald L. Clark [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
'SHOW DATABASES' - vi.recover
Hello, I'm having a problem with MySQL when it runs for a couple of days. At some time mysqld 'forgets' where it's databases are and searches for them in the mysql temp dir. (In my case /var/tmp). In the tmp dir there is always the recover directory for vi (vi.recover) so mysqld thinks that it's the only database. Funny enough mysql remembers what the grant rights are for all the users. I've searched the mailing lists/newsgroups for this problem. This problem was reported before by D. Jordan on 2000/10/05 and a month before that. Sadly nobody replied to it. :( Does somebody know what's going on? Specs: FreeBSD 4.3 RELEASE, Mysql 3.23.39, PHP 4.0.5, Apache 1.3.20 all built from ports. Regards, -- Sebastiaan Smit | email: [EMAIL PROTECTED] | web:www.echelon.nl ECHELON consultancy and software development | phone: +31 (0)53 48 36 585 PO Box 545, 7500AM Enschede, The Netherlands | fax: +31 (0)53 43 36 222 - 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: could not insert BLOB data type in a table
PD At 10:43 AM +0530 7/25/01, [EMAIL PROTECTED] wrote: Hi, I just want to know how to insert a BLOB data type in a field of a table. I want to put the binary data stream not the file link in the filed. The language i m using is PERL5. PD Use $dbh-quote() or placeholders to insert the BLOB value into PD the query string. See the DBI docs. Only use placeholders. $dbh-quote() is unreliable (and this fact is documented in DBI documentation). -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | Ilya Martynov (http://martynov.org/)| | GnuPG 1024D/323BDEE6 D7F7 561E 4C1D 8A15 8E80 E4AE BE1A 53EB 323B DEE6 | | AGAVA Software Company (http://www.agava.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
CASE WHEN help
I don't know if I can do a query such as this. Could someone please help... SELECT B.name Boat name, (CASE WHEN B.motor_sail=S THEN Sail boat WHEN B.motor_sail=S THEN Motor boat WHEN B.motor_sail=S THEN Paddle boat ELSE Unknown END), IF(B.age=0,NEW,B.age) Years old, B.colour Colour, CONCAT(B.length_m,.,B.length_cm, m) Length (METRIC), CONCAT(B.length_ft,ft ,B.length_in,in) Length (IMPERIAL), CONCAT(A.fname, ,A.lname) Seller FROM be_customer A, be_ad B ORDER BY A.lname,B.ad_id; Thanks, Robert - 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: Bad optimization on ORDER BY .. DESC.
Artem V. Ryabov writes: Hello All, When I use order by .. DESC in query, time always more then in order by .. ASC case. Explain say Using filesort,in DESC case query. Why??? -- Best regards, Artem mailto:[EMAIL PROTECTED] Hi! This is a known deficiency that shall be fixed in 4.0. -- 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
Re: installation Apache / Win 98
Stephen, you have to add to the my.cnf or my.ini file the following parameters, to the [mysqld] section: Suppose you have a Windows NT machine with 128 MB RAM and a single 10 GB hard disk. Below is an example of possible configuration parameters in `my.cnf' for InnoDB: [mysqld] # You can write your other MySQL server options here # ... # innodb_data_home_dir = c:\ibdata innodb_data_file_path = ibdata1:2000M;ibdata2:2000M 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 = innodb_additional_mem_pool_size=10M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 See the manual at www.innodb.com InnoDB does not create directories, you have to do it yourself. Regards, Heikki Tuuri Innobase Oy Copied message: .. I'm trying to learn php with mysql, I've installed Apache server for windows with php on my computer. However I can't seem to get Mysql to work. I've installed on Mysql for windows which I downloaded from Mysql website. I then double click on mysqld ( it says to do that in the book I've bought). Unfortunately I get the following error:- Cannot initialise InnoDB as 'innodb_data_file_path' is not set. I presume I have to change the httpd.conf somehow but I'm not sure how. Please can somebody help!:) Stephen Hammond - 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: solairs 8 x86 get signal 11 with bdb support
On 2001 Jul 23, [EMAIL PROTECTED] wrote: Description: Whenever I compile mysql 3.23.40 with Berkeley DB support, I get a signal 11 when attempting to start the mysql server. If I omit Berkeley DB support, everything starts fine. Synopsis:solairs 8 x86 get signal 11 with bdb support Release: mysql-3.23.40 (Source distribution) Brian, I'm not sure how well Berkeley DB is supported on x86 Solaris. Can you try running some of the programs inside the bdb/build_unix directory, to see if they crash or if they work? If they also crash, then something is wrong with the way bdb is being built. You might try getting a stock BerkeleyDB from sleepycat.com; if it works and ours doesn't, then clearly there is something wrong with how we're building ours. For example, try running 'db_printlog -V', to see if it prints out its version. Another thing to do is to pass the --with-debug flag to configure when you build MySQL, and run mysqld under a debugger to find out exactly where it crashes. You can find detailed instructions on how to do this in our manual: http://www.mysql.com/doc/D/e/Debugging_server.html If you could do this, it would help us a lot! Thanks, Tim -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Tim Smith [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-time Developer /_/ /_/\_, /___/\___\_\___/ Boone, NC USA ___/ 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
AUTO_INCREMENT problem in table
Hi, got a problem: My table (part of it) looks like this: CREATE TABLE cust ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (id) ); When i type INSERT INTO cust VALUES(NULL,Peter); the value of id will increase by 1 every time i insert a record. By misstake i typed INSERT INTO cust VALUES(20,Maria); the id (20) then was downsized to the top limit of SMALLINT - 65535. Next time i type INSERT INTO cust VALUES(NULL,Joe); the AUTO_INCREMENT tries to increase id by 1, but it cant because AUTO_INCREMENT for id starts with 65535 and cant go any higher. Even if i delete the record where id=65535 DELETE FROM cust WHERE id=65535 AUTO_INCREMENT starts with 65535 and i cant add any more records. How do i get out of this mess? /Peter - 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
what's the easiest way to transfer a local database to a remote one? - 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 running ./mysql_install_db
When installing mysql the only problem I receive is the following which cannot install the grant tables. I am unable to view the log because my access is denied. What to do? Anyone? [localhost:/usr/local/bin] nezi% ./mysql_install_db mkdir: /usr/local/var/mysql: Permission denied chmod: /usr/local/var/mysql: Permission denied mkdir: /usr/local/var/test: Permission denied chmod: /usr/local/var/test: Permission denied Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables /usr/local/libexec/mysqld: Can't change dir to '/usr/local/var/' (Errcode: 13) 010725 9:56:55 Aborting 010725 9:56:55 /usr/local/libexec/mysqld: Shutdown Complete Installation of grant tables failed! Examine the logs in /usr/local/var for more information. You can also try to start the mysqld daemon with: /usr/local/libexec/mysqld --skip-grant You can use the command line tool /usr/local/bin/mysql to connect to the mysql database and look at the grant tables: shell /usr/local/bin/mysql -u root mysql mysql show tables Try 'mysqld --help' if you have problems with paths. Using --log gives you a log in /usr/local/var that may be helpful. The latest information about MySQL is available on the web at http://www.mysql.com Please consult the MySQL manual section: 'Problems running mysql_install_db', and the manual section that describes problems on your OS. Another information source is the MySQL email archive. Please check all of the above before mailing us! And if you do mail us, you MUST use the /usr/local/bin/mysqlbug script! -- - 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
- Original Message - From: Jay McGarry [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, July 25, 2001 7:57 AM Subject: Mysql what's the easiest way to transfer a local database to a remote one? Depends on many factors really. Are you doing this as a one-off thing or something that needs to happen on a regular basis? Are you doing this across platforms or not? If its a one time thing and you need to do this quickly, you could use a dump file from one and transfer it to the other. Good Luck, Dennis ** Beridney Computer Services http://www.beridney.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
character sets
hi all, I'm having a problem wrt character sets in mysql. i'm trying to add the euro symbol to a table (MyISAM), but it seems that it is being replaced with '?'. Having looked into the problem a bit, I assume it is that the table is using the default latin1 character set, which does not include the euro symbol. How have other people worked around this? I don't know how to define new character sets (technically), having looked at those defined in share/charsets/*.conf. Can somebody explain these files, or point me to somewhere I can read up on them? mysql version 3.23.37 (nt 4) rgds, Paul. Software Architect TERMINAL FOUR Solutions Ltd. 1, Herbert Street, Dublin 2, Ireland. T: +353 1 2403110 F: +353 1 6615504 - 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 running ./mysql_install_db
Try doing it as root if you're not already. Tyler Longren Captain Jack Communications [EMAIL PROTECTED] www.captainjack.com - Original Message - From: Michael Nezi [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, July 25, 2001 11:08 AM Subject: Problems running ./mysql_install_db When installing mysql the only problem I receive is the following which cannot install the grant tables. I am unable to view the log because my access is denied. What to do? Anyone? [localhost:/usr/local/bin] nezi% ./mysql_install_db mkdir: /usr/local/var/mysql: Permission denied chmod: /usr/local/var/mysql: Permission denied mkdir: /usr/local/var/test: Permission denied chmod: /usr/local/var/test: Permission denied Preparing db table Preparing host table Preparing user table Preparing func table Preparing tables_priv table Preparing columns_priv table Installing all prepared tables /usr/local/libexec/mysqld: Can't change dir to '/usr/local/var/' (Errcode: 13) 010725 9:56:55 Aborting 010725 9:56:55 /usr/local/libexec/mysqld: Shutdown Complete Installation of grant tables failed! Examine the logs in /usr/local/var for more information. You can also try to start the mysqld daemon with: /usr/local/libexec/mysqld --skip-grant You can use the command line tool /usr/local/bin/mysql to connect to the mysql database and look at the grant tables: shell /usr/local/bin/mysql -u root mysql mysql show tables Try 'mysqld --help' if you have problems with paths. Using --log gives you a log in /usr/local/var that may be helpful. The latest information about MySQL is available on the web at http://www.mysql.com Please consult the MySQL manual section: 'Problems running mysql_install_db', and the manual section that describes problems on your OS. Another information source is the MySQL email archive. Please check all of the above before mailing us! And if you do mail us, you MUST use the /usr/local/bin/mysqlbug script! -- - 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
FW: encrypt
I hope this is not to stupid. I have looked at how to encrypt strings in my database. I know that you need to use a blob and I think I can figure out how to encrypt from a query or SELECT but how do I encrypt on the INSERT. Thanks for your help Jason - 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
Please help with syntax !!!
Hello ! This is a syntax taken from MS Access: SELECT messages.message_id, messages_1.message_id FROM messages AS messages_1 INNER JOIN (messages INNER JOIN connections ON messages.message_id = connections.parent) ON messages_1.message_id = connections.child ORDER BY messages.message_id, messages_1.message_id For whatever reason this syntax is not valid for MySQL. Please - help me to write the right equivalent one. Thank you for your help ! Ziggi
Compressed column support?
Hi, I have looked through the documentation (http://www.mysql.com/docs) and the mailing list archives (http://www.geocrawler.com/lists/3/Databases/8/0/) and find no mention of a feature that I was hoping was available: * text or varchar columns that are automatically compressed/uncompressed I have a table with highly compressible data. One of the fields is a 380-character field which represents a set of indicator flags for upcoming dates. (I am working in the travel industry.) (Please don't talk to me about third normal form... this comes from a legacy database, and it was easiest to keep their physical design.) A daily data file is 5MB compressed (300MB uncompressed). It adds roughly 300MB to the database each day! It actually is causing me to consider storing this data outside the database in compressed files. The ugliness of this is somewhat appalling however. I wish there were a feature in MySQL which would allow me to define the table to contain text or varchar columns which would be automatically compressed and decompressed dynamically (and completely transparently to the user). create table widget ( widget_id integer not null auto_increment primary key, widget_cd varchar(8) not null, indicatorstext null compressed, more_flagsvarchar(255) null compressed, unique widget_ak1 (widget_cd) ); Has there been talk of such a feature? (I am new to the list, but I didn't see it in the archives.) Would it be hard to develop? Where would one start? Stephen - 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
Setting up no password users?
I recently did my first mySQL installation (the OS is Linux). How can I set up users so that they do not need to supply a password to use mysql? I want to be able to issue commands like mysql -e select host,db,user from db mysql instead of having to do something like mysql -e select host,db,user from db -u johndoe -pmypwd mysql Thanks! KJ - 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 on os x 10.0.4
At 10:44 AM -0600 7/25/01, Michael Nezi wrote: Saw your post on mysql.com and wondering if you could help me. Working with the Mac OS X Terminal Mac OS X comes with a Terminal application that can be used for issuing instructions to the OS from a command line. You will find it necessary at times to perform some tasks using the Terminal application and so should become a bit familiar with its use. When you open the terminal application it starts a shell specific for your user account. Many of the commands and utilities needed for administrative purposes however require that you open a root shell, in other words, you need to log in as the root administrator. When you install Mac OS X you set up an Administrator user name and password, this OS user account allows you to perform administrative functions but is not the root account that has complete control over all functions of the system. Apple has elected to not enable this account by default. It must be explicitly enabled for it to be available. In my opinion, when administering MySQL on the command line using the terminal program on Mac OS X you are best off not enabling the root account. The reason for this is to lessen the chance that you will create files and install programs that can only be used by root, thus not allowing the system to work for a user account with fewer privileges. It is also common practice in UNIX not to use the root account to perform general tasks and it should only be used when it is necessary. To work with just the Admin user name, prefix any command with sudo. If a password is required, you will be prompted for it. The sudo program takes as an argument a command line to be executed as root. Your user account must be included in a configuration file to be allowed to utilize this command. This file also specifies the commands you can run, in the case of Mac OS X the user that you set up when installing the OS is included on this list by default and assigned privileges that allow it to perform any task that root is able to. For example, to run the above command use: root% sudo ./mysqladmin -u root password thenewpassword Alternatively, if you will be executing a series of commands as root then start with the sudo su or sudo -s command to start a new shell as the root user. Use exit to stop issuing commands as root and exit the shell. For example: % sudo su Password: root% ./mysqladmin -u root password thenewpassword root% (another command) root% (another command) root% exit While sudo would require you to enter a password each time: % sudo ./mysqladmin -u root password thenewpassword password: % sudo (another command) password: % sudo (another command) password: % sudo etc password: If you open a root shell to accomplish a series of tasks, there will not be a need to use sudo for each command. Note that in this document, lines that begin with % indicate a UNIX command prompt, it may actually look more like: [localhost:~] mcollins% The text that follows is what would be typed into the terminal. In addition, root% at the start of the line indicates that the command needs to be issued while logged in as root or with root permissions. The Root Account If there are any other users with accounts on your server, it is essential that you protect the root account by creating a password for this account. Even if you are the only one with access to the machine, it is prudent to plug any holes that may be exploited by hackers. The key thing to keep in mind is that the OS root is not the same as the MySQL root. You don't need to have root access to the server to attempt to use the MySQL root account. There might be other user accounts to the server that may be able to navigate to the mysql utility and log in as the MySQL root with no password. This can happen even through a remote telnet session, since even though root is set to localhost only, a telnet session allows one to connect and work as a user would on the localhost. Having no root password would mean this intruder could send any SQL command to the databases, and even delete them. The root account created in MySQL uses the name root by convention, it is not required that the account use the name root, it could be changed to any name you want. Setting the MySQL root password is an example of when you will need to use the Mac OS Terminal. The task of setting the root password is accomplished using mysqladmin, one of the utility programs installed by MySQL (and MySQL). The command is as follows: root% cd /usr/local/ root% ./bin/mysqladmin --socket=/tmp/MySQL.sock -u root password thenewpassword Tip: If a password was already set for MySQL root and you want to change the password, you could add the -p option (after root in the above command), you will then be prompted for the existing MySQL root password. Starting and Stopping MySQL Mac OS X has a problem with stopping MySQL, so you need to find the process ID
error 127
Hi- I am getting error 127 and I cant seem to get the myisamchk command to work for me. Can anyone help me with this issue? Is there a phone-based support option available? Money is no object - I am desperate and looking to fix this issue before my client finds out. Thanks, Marko
Re: Please help with syntax !!!
Hi, It seems like what you are trying to do is select all messages that have child messages and display the results sorted by the parent message ID. How about... select messages.message_id, messages_1.message_id from messages, connections, messages messages_1 where connections.parent = messages.message_id and messages_1.message_id = connections.child order by messages.message_id, messages_1.message_id; Or more clearly... select parent_msg.message_id, child_msg.message_id from messages parent_msg, connections connect, messages child_msg where connect.parent = parent_msg.message_id and child_msg.message_id = connect.child order by parent_msg.message_id, child_msg.message_id; Stephen P.S. I am not an expert on MS Access and whether your original query would include parent messages which have no children (via connections) or not. If so, you would need to enhance the suggested SQL with some MySQL version of INNER or OUTER join syntax. At 05:40 PM 7/25/2001 +0200, Zbigniew Szczesny wrote: Hello ! This is a syntax taken from MS Access: SELECT messages.message_id, messages_1.message_id FROM messages AS messages_1 INNER JOIN (messages INNER JOIN connections ON messages.message_id = connections.parent) ON messages_1.message_id = connections.child ORDER BY messages.message_id, messages_1.message_id For whatever reason this syntax is not valid for MySQL. Please - help me to write the right equivalent one. Thank you for your help ! Ziggi - 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 and credit cards
Greetings: I was wondering if anyone has any ideas about the best way to store credit cards in a database ... and I'm not referring that much to the field type, but rather encrytption techniques. Thanks. Alan Cox [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: could not insert BLOB data type in a table
At 5:23 PM +0400 7/25/01, Ilya Martynov wrote: PD At 10:43 AM +0530 7/25/01, [EMAIL PROTECTED] wrote: Hi, I just want to know how to insert a BLOB data type in a field of a table. I want to put the binary data stream not the file link in the filed. The language i m using is PERL5. PD Use $dbh-quote() or placeholders to insert the BLOB value into PD the query string. See the DBI docs. Only use placeholders. $dbh-quote() is unreliable (and this fact is documented in DBI documentation). Please show me the part of the documentation that you're referring to, and please supply a counter-example. quote() is extremely useful for producing SQL statements that will be executed by another program, a situation for which placeholders are useless. -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | Ilya Martynov (http://martynov.org/)| | GnuPG 1024D/323BDEE6 D7F7 561E 4C1D 8A15 8E80 E4AE BE1A 53EB 323B DEE6 | | AGAVA Software Company (http://www.agava.com/) | -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- -- Paul DuBois, [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: AUTO_INCREMENT problem in table
At 4:47 PM +0200 7/25/01, Peter Wiherkoski wrote: Hi, got a problem: My table (part of it) looks like this: CREATE TABLE cust ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (id) ); When i type INSERT INTO cust VALUES(NULL,Peter); the value of id will increase by 1 every time i insert a record. By misstake i typed INSERT INTO cust VALUES(20,Maria); the id (20) then was downsized to the top limit of SMALLINT - 65535. Next time i type INSERT INTO cust VALUES(NULL,Joe); the AUTO_INCREMENT tries to increase id by 1, but it cant because AUTO_INCREMENT for id starts with 65535 and cant go any higher. Even if i delete the record where id=65535 DELETE FROM cust WHERE id=65535 AUTO_INCREMENT starts with 65535 and i cant add any more records. How do i get out of this mess? /Peter Reset the sequence value down: ALTER TABLE cust AUTO_INCREMENT = 0 Then it will revert to using the smallest unused value. I'm assuming you're using MySQL 3.23.xx; this won't work in earlier versions. -- Paul DuBois, [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: Builder.com coverage of mysql
At 9:02 AM -0700 7/25/01, John Meyer wrote: http://builder.cnet.com/webbuilding/0-7537-8-6580620-1.html?tag=sd It's a general overview of MySQL, but it's a pretty fair comparison. From the article: Transactions and commit/rollback is another management feature that's not directly supported by MySQL. A transaction is a group (or set) of tasks executed together as a unit. If one transaction can't be completed, none of the transactions are executed. Commercial sites with online ordering capabilities will find this lack of transaction support disappointing. MaxSQL, a separate server, supports transactions through the use of extraneous tables. I like the extraneous tables part. :-) I suspect she meant something else. It is worth a read. John Meyer [EMAIL PROTECTED] Programmer If we didn't have Microsoft, we'd have to blame ourselves for all of our programs crashing -- Paul DuBois, [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: could not insert BLOB data type in a table
Only use placeholders. $dbh-quote() is unreliable (and this fact is documented in DBI documentation). PD Please show me the part of the documentation that you're referring to, PD and please supply a counter-example. quote() is extremely useful for PD producing SQL statements that will be executed by another program, a PD situation for which placeholders are useless. perldoc DBI: Quote will probably not be able to deal with all pos- sible input (such as binary data or data containing newlines), .. There is no need to quote values being used with the Placeholders and Bind Values entry elsewhere in this document. Here a proof that quote sucks: http://lists.mysql.com/cgi-ez/ezmlm-cgi?1:msp:73935:phlgjhgmdiikjknclakk User had trobles with quote and binary data but once he rewrote code with placeholders problem disappeared. -- -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=- | Ilya Martynov (http://martynov.org/)| | GnuPG 1024D/323BDEE6 D7F7 561E 4C1D 8A15 8E80 E4AE BE1A 53EB 323B DEE6 | | AGAVA Software Company (http://www.agava.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: installing mysql
Dear Maggie, http://mysql.com/Downloads/MySQL-3.23/mysql-3.23.39-win.zip has a SETUP.EXE which will install MySQL (database server plus client tools to work with the server) - by default it will install in C:\mysql. After setup, it's easiest to restart Windows. Now, the MySQL server should load. You will find a traffic light symbol in the system tray (lower right corner of your screen). Right click on it, and choose - Win9.x Start the server standalone (for Win 95 / 98) - or WinNT Install the service, then WinNT Start the service (for Win NT or Win 2000) If the traffic light symbol isn't there, you have to start this tool manually: - C:\mysql\bin\winmysqladmin.exe Afterwards, same procedure as above. To connect to the database server, you can use the MySQL Monitor (a command line tool, very good for learning SQL, but make sure you have a good book like Paul Dubois' MySQL, and / or the MySQL manual which is found here after setup: C:\mysql\Docs\manual.html). There is a graphical frontend, too (C:\mysql\bin\MySqlManager.exe). Regards, -- Stefan Hinz Geschäftsführer / CEO iConnect e-commerce solutions GmbH # www.js-webShop.com www.iConnect.de # Gustav-Meyer-Allee 25, 13355 Berlin # Tel: +49-30-46307-382 Fax: +49-30-46307-388 - Original Message - From: Maggie Cheang [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, July 25, 2001 9:31 AM Subject: installing mysql Hi, I have a problem. I have unzip the file with winzip, but I couldn't get it to install. I downloaded both the 3.22 and 3.23 verison, but I still couldn't get it to work as install. my teacher in school told me to install it with winzip and then use mysqladmin to work, but I couldn't get either working. is there something I am missing? thank you so much, and your help is high appreciated :) yours sincerely, Maggie - 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: Upgrade/installation problem
Dear /roger, some suggestions ... - Check mysqladmin variables to get the value for datadir (that's where the server will expect the mysql database (containing the host table) to be. - Check /etc/my.cnf to see if the datadir variable has been set correctly. Obviously, your server does not find the database directory. (It is supposed to be /var/lib/mysql for modern versions of MySQL, but as I heard it has been something like /usr/local/mysql until version 3.22). Regards, -- Stefan Hinz Geschäftsführer / CEO iConnect e-commerce solutions GmbH # www.js-webShop.com www.iConnect.de # Gustav-Meyer-Allee 25, 13355 Berlin # Tel: +49-30-46307-382 Fax: +49-30-46307-388 - Original Message - From: Roger Westin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, July 25, 2001 2:25 PM Subject: Upgrade/installation problem Ok problem. Upgrade from 3.23.37 to 3.23.40 on linux readhat 7.0 - using rpm and after the uppdate I get : Cant find file ./mysql/host.frm (error: 13) so I copied the db and run mysql_install_db, still get the same msg, So I checked the error 13 = permission denined. So I doublecheck tthe permission, and even tried to give ugo=rwx on all the files, same error. So Just to se if it for some reason read from another datadit i removed the datadir, and I get Table mysql.host doesn't exists (= another error) wich tells me that it actualy is reading from the right datadir. So I tried to uninstall all the mysql rpm and installing it againg same error so what can be wrong Ran out of clues. The installation has been upgraded several times before with any problems. /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: Sorting w/ leading articles ignored
Dear Zhang, check for the regular expression section in the MySQL manual. You can filter out any patterns you want with RegExp :) Regards, -- Stefan Hinz Geschäftsführer / CEO iConnect e-commerce solutions GmbH # www.js-webShop.com www.iConnect.de # Gustav-Meyer-Allee 25, 13355 Berlin # Tel: +49-30-46307-382 Fax: +49-30-46307-388 - Original Message - From: Bin Zhang [EMAIL PROTECTED] To: Msql-Mysql-Modules [EMAIL PROTECTED]; Mysql [EMAIL PROTECTED] Sent: Wednesday, July 25, 2001 11:20 AM Subject: Sorting w/ leading articles ignored Can this be done? What I want is to generate a list of journal sorted by the titles, ignoring the leading articles (the, a, an, etc.). If there is no SQL statement for this, How could I do this w/ DBI or PHP? I am using mysql 3.23.40 on Solaris 7. Thanks for any advise. Bin - 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: error 127
in the mysql command line, try this: use databasename; repair table tablename; (repeat that last line for each of your tables) I haven't tried myisamchk at all. This is what I use. Walter At 12:13 PM -0400 7/25/01, Marko Andrus wrote: Hi- I am getting error 127 and I cant seem to get the myisamchk command to work for me. Can anyone help me with this issue? Is there a phone-based support option available? Money is no object - I am desperate and looking to fix this issue before my client finds out. Thanks, Marko - 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 complains about query...
to all, warm regards from brazil. here's the plot: my isp has been complaining for last couple of days about this query, generated automatically by a query generator (no, duh??)... SELECT * FROM listaemail WHERE id LIKE %% OR nome LIKE %% OR empresa LIKE %% OR email LIKE %% OR ativo LIKE %% ORDER BY nome; Time: 11 Lock_time: 0 Rows_sent: 30567 Rows_examined: 30568 before anyone jumps out and points it out, it is far from being optimized. it should (and will, as soon as i update the generator) read: select * from listaemail order by nome; my question is: could there possibly be a problem in the execution of such a query??? my isp's system administrator states this query is causing the server to become unstable. i firmly believe there is no harm in executing such query, even if it's not optimal. any insights will be greatly appreciated. kindest regards, mauricio portasio [EMAIL PROTECTED] sao paulo - brazil - 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 with syntax !!!
Hello, Try SELECT messages.message_id, messages_1.message_id FROM messages as messages_1 INNER JOIN connections ON messages_1.message_id = connections.child INNER JOIN messages ON messages.message_id = connections.parent ORDER BY messages.message_id, messages_1.message_id or SELECT messages.message_id, messages_1.message_id FROM messages as messages_1, connections, messages WHERE messages.message_id = connections.parent AND messages_1.message_id = connections.child ORDER BY messages.message_id, messages_1.message_id Regards Jean-Claude MAES - Original Message - From: Zbigniew Szczesny [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, July 25, 2001 5:40 PM Subject: Please help with syntax !!! Hello ! This is a syntax taken from MS Access: SELECT messages.message_id, messages_1.message_id FROM messages AS messages_1 INNER JOIN (messages INNER JOIN connections ON messages.message_id = connections.parent) ON messages_1.message_id = connections.child ORDER BY messages.message_id, messages_1.message_id For whatever reason this syntax is not valid for MySQL. Please - help me to write the right equivalent one. Thank you for your help ! Ziggi - 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 credit cards
Depending on the 'environment' (i.e. O/S you're using), the tools available might be different... But, using Linux (and I'm sure any other 'flavor' of Unix), my company used the latest 'libmcrypt' to encrypt the credit card numbers (as well as the user passwords) within a PHP function prior to storing it in the database. You have to hide the 'key' or 'salt' that you used in order to 'decrypt' it again (when using it to 'charge' transactions) pretty good (don't keep it on the same server as the database, in case someone gets their hands on a copy of your DB or a 'dump'). I have attached my simple 'webdoc' on how to install libmcrypt for use with Apache/PHP - Original Message - From: Alan Cox [EMAIL PROTECTED] To: mysql [EMAIL PROTECTED] Sent: Sunday, February 20, 2000 9:57 AM Subject: mysql and credit cards Greetings: I was wondering if anyone has any ideas about the best way to store credit cards in a database ... and I'm not referring that much to the field type, but rather encrytption techniques. Thanks. Alan Cox [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
Re: mysql and credit cards
So sprach »Alan Cox« am 2000-02-20 um 11:57:47 -0500 : Greetings: I was wondering if anyone has any ideas about the best way to store credit cards in a database ... and I'm not referring that much to the field type, but rather encrytption techniques. 'encryption'? Hmm, how about: none? If you don't need to reconstruct the cc#, md5 will be good. However, if you need to reconstruct it, nothing is safe. And that's quite simple: a) You need to get access to the MySQL server. Impossible to do from the outside if '--skip-networking' is used. b) So, only possible from the localhost. This means, there must have been a break in to the MySQL server. Once he's on the server, he can do anything he likes. He can also read the source code of your PHP/PERL pages. There the password will be stored, somewhere. Once the password has been found (which is nothing but a matter of time), your encryption is broken. BUT: If you're using something like C or any other compiled language and do not have the source code flying around and are sure that the password cannot be decrypted from the binary, any symetrical encryption will probably do. The last also applies, if you use a program to decrypt the cc# which is not stored on the same computer as the MySQL database. But then you need to worry about how to securly transfer the data from the MySQL database to the computer running the decryption program. So, overall, I'd say: Don't hassle with encryption: It's not worth it. Alexander Skwar -- How to quote: http://learn.to/quote (german) http://quote.6x.to (english) Homepage: http://www.digitalprojects.com | http://www.iso-top.de iso-top.de - Die günstige Art an Linux Distributionen zu kommen Uptime: 2 days 17 hours 44 minutes - 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: AUTO_INCREMENT problem in table
Dear Peter, Even if i delete the record where id=65535 DELETE FROM cust WHERE id=65535 AUTO_INCREMENT starts with 65535 and i cant add any more records. Sad, but true, MySQL remembers the biggest number inserted into an auto_increment column. If you delete the row in question, the next insert will add the biggest number again. The only workaround I can see is to copy the table (without the problem id row) to another table with the same structure. Maybe someone has a better solution?! Regards, -- Stefan Hinz Geschäftsführer / CEO iConnect e-commerce solutions GmbH # www.js-webShop.com www.iConnect.de # Gustav-Meyer-Allee 25, 13355 Berlin # Tel: +49-30-46307-382 Fax: +49-30-46307-388 - Original Message - From: Peter Wiherkoski [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, July 25, 2001 4:47 PM Subject: AUTO_INCREMENT problem in table Hi, got a problem: My table (part of it) looks like this: CREATE TABLE cust ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (id) ); When i type INSERT INTO cust VALUES(NULL,Peter); the value of id will increase by 1 every time i insert a record. By misstake i typed INSERT INTO cust VALUES(20,Maria); the id (20) then was downsized to the top limit of SMALLINT - 65535. Next time i type INSERT INTO cust VALUES(NULL,Joe); the AUTO_INCREMENT tries to increase id by 1, but it cant because AUTO_INCREMENT for id starts with 65535 and cant go any higher. Even if i delete the record where id=65535 DELETE FROM cust WHERE id=65535 AUTO_INCREMENT starts with 65535 and i cant add any more records. How do i get out of this mess? /Peter - 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: Setting up no password users?
Dear KJ, How can I set up users so that they do not need to supply a password to use mysql? Use MySQL Monitor (the command line tool, mysql) and type: GRANT ALL ON *.* TO @% This will grant all rights on all databases and tables to anyone () from anywhere (%) without password. Regards, -- Stefan Hinz Geschäftsführer / CEO iConnect e-commerce solutions GmbH # www.js-webShop.com www.iConnect.de # Gustav-Meyer-Allee 25, 13355 Berlin # Tel: +49-30-46307-382 Fax: +49-30-46307-388 - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, July 25, 2001 5:59 PM Subject: Setting up no password users? I recently did my first mySQL installation (the OS is Linux). How can I set up users so that they do not need to supply a password to use mysql? I want to be able to issue commands like mysql -e select host,db,user from db mysql instead of having to do something like mysql -e select host,db,user from db -u johndoe -pmypwd mysql Thanks! KJ - 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
Urgent Help needed for installing
Hello All, I've been trying upgrade our current MySQL 3.23.39 to the 3.23.40 I have tired with both the binary distro and source distro with no luck. The install seems to go OK, but I can't start the mysql server. This is on a RH/Linux 6.2 Kernel 2.4.6 i686 system. The main problem seems to be, that our dedicated server comes with a WebHosting Mgr browser interface and Cpanel for customers to use and the MySQL installation is all tied into this. So it must be, for what I have been told, that we must use the current settings to upgrade, which I did. Datadir /var/lib/mysql I tried with Mysql-Max binary install, but couldn't get the symbolic link to work properly. I unpacked the tar in the /var/lib folder, then created the symbolic link as it specifies to the mysql folder, still no luck. I tried installing the source distro, with the ./configure --prefix=/var/lib/mysql Everything seem to install OK, but when attempting to start the mysql server, sometimes it looks for the mysql.sock in the /var/lib/mysql folder, and sometimes it looks for it in the /tmp folder. I copied the mysql.server to the /etc/rc.d/init.d folder chmoded it 500, create the various symbolic links to the /etc/rc2.d and rc3.d, rc4.d as outlined. Then if I do: /etc/rc.d/init.d/mysql stop or start it complains it can create or find the .pid file which is suppose to be in the /var/lib/mysql folder. all the chown settings are correct: chown mysql.root for the /var/lib/mysql folder I have a feeling something from the current version is causing some problems and screwing with the upgrade. If any one maybe able to shed some lite to what I maybe doing work, it would be much appreciated. Our support/tech people are very slow to responded to help and we need to have this installed in the next day and require the transaction support with BDB tables. THX's, Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225)686-2002 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= - 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: Invalid SQL..?
the output look like this.. mysql desc policy; +++--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +++--+-+-+---+ | service_charge | int(2) | | | 0 | | +++--+-+-+---+ 1 row in set (0.01 sec) -Original Message- From: Chris Bolt [EMAIL PROTECTED] Date: Wed, 25 Jul 2001 05:07:02 -0600 To: [EMAIL PROTECTED] Subject: RE: Invalid SQL..? Re: hi all.. Re: Re: this error came out after i make backup for my database using mysqldump Re: what this error say is wrong... maybe my database is corrupt or Re: crash or something like that.. any command can fix this error..?. Re: any idea what i should do. Re: Re: Please paste the output of typing DESCRIBE policy; into the mysql client. Re: Re: Database error: Invalid SQL: select id, subid from policy where Re: id='2' and subid='0' Re: MySQL Error: 1054 (Unknown column 'id' in 'field list') Re: Session halted. Re: Re: Now there's a pointless SQL query... heh Re: Re: Re: - Re: Before posting, please check: Re: A HREF=http://www.mysql.com/manual.php; TARGET=_newFONT COLOR=BLUEhttp://www.mysql.com/manual.php/FONT/A (the manual) Re: A HREF=http://lists.mysql.com/; TARGET=_newFONT COLOR=BLUEhttp://lists.mysql.com//FONT/A (the list archive) Re: Re: To request this thread, e-mail [EMAIL PROTECTED] Re: To unsubscribe, e-mail [EMAIL PROTECTED] Re: Trouble unsubscribing? Try: A HREF=http://lists.mysql.com/php/unsubscribe.php; TARGET=_newFONT COLOR=BLUEhttp://lists.mysql.com/php/unsubscribe.php/FONT/A Re: Re: -- Get your free email from www.linuxmail.org Powered by Outblaze - 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: Setting up no password users?
hello kynn, you have to : first you must UNDERSTAND the USER-table from mysql-DB on your box. from that point you are able to insert directly values into USERS like values into any other MySQL-Table: example: INSERT INTO USER VALUES ('localhost','Testuser','PASSWORD('little lamer'),'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y') ^ | |__ no password for Testuser means:insert '' here___| Hope it helps Christian [EMAIL PROTECTED] mailto:[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
MySQL vs. Oracle
I am currently trying to examine the benefits of continuing to use MySQL over Oracle. I need to know what MySQL's features are vs. those of Oracle. Also I am looking for performance data. What kind of scalability does MySQL have vs. that of Oracle. Is there (or will there be) any support in MySQL for something akin to Oracle's Parallel Server? Can I use MySQL with Veritas? Thanks, Shon Stephens - 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: Setting up no password users?
You probably want to GRANT ALL ON oneDatabase.* TO login@%. I can't image a situation where letting everyone see everything (including the mysql database with all the permissions) would ever be a good idea. Walter At 7:44 PM +0200 7/25/01, Stefan Hinz wrote: Dear KJ, How can I set up users so that they do not need to supply a password to use mysql? Use MySQL Monitor (the command line tool, mysql) and type: GRANT ALL ON *.* TO @% This will grant all rights on all databases and tables to anyone () from anywhere (%) without password. Regards, -- Stefan Hinz Geschäftsführer / CEO iConnect e-commerce solutions GmbH # www.js-webShop.com www.iConnect.de # Gustav-Meyer-Allee 25, 13355 Berlin # Tel: +49-30-46307-382 Fax: +49-30-46307-388 - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, July 25, 2001 5:59 PM Subject: Setting up no password users? I recently did my first mySQL installation (the OS is Linux). How can I set up users so that they do not need to supply a password to use mysql? I want to be able to issue commands like mysql -e select host,db,user from db mysql instead of having to do something like mysql -e select host,db,user from db -u johndoe -pmypwd mysql Thanks! KJ - 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: error using InnoDB with LOAD DATA
George, current binary versions do not yet support rows of size 8000 bytes. The next binary version 3.23.41 will do it. If you want right now to use 8000 byte rows, you have to download the source distribution innodb-3.23.39b from www.innodb.com, replace the InnoDB files in the source distribution of MySQL-3.23.40 and compile. Regards, Heikki Copied message: ... Hello all! I'm running the current MySQL vers. with InnoDB on a Linux box. Now I tried to load data using a command like load data infile '/mysql/out.txt' REPLACE into table LSWEB_CONTENT fields terminated by '~' lines terminated by '}'; Running the same command in a Myisam table everything works fine, but trying to insert it into an InnoDB table I get the error message: ERROR 1030: Got error 139 from table handler A few inserts were actually done, and the point where it stops seems to be a very large field with approx. 15000 characters which shall be put into a largetext field. I already tried out some other formats for the field [like blob or similar (all should be able to hold more than 15000 characters)], but I failed admirably. Do you have any idea how to prevent that error ? Thanks in advance for your comments. George - 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
localisation error - please, please help me
Hi, I have configured MySQL with charset latin2 on a Linux box. I made a big table with a mediumtext filed and a search engine using MySQL match-against. On the Linux box everything looked fine. Now we replaced our server to FreeBSD. I compiled the same source with the same configuration settings. Now I have several problems with special iso-latin2 characters: - For example when I search for a string which contains a character which has the code of F5 (t+F5+zsde), it selects words which is the same but contains the character with code F4 (t+F4+zsde). The difference is that the character F4 is an element of the standard character set also. They look familiar and being afraid of codepage problems some sites use F4 instead of the official F5. A nice behaviour from MySQL that it also select the words with F6 (t+F6+zsde). F5 and F6 completely different letters, although they pronounced nearly the same and the look of letter itself is similar. Again, F6 is the member of the standard codepage. - Another example if I search for a word starts with F5 (F5+rmester), I get no results from MySQL. If I use F6 (F6+rmester) which is grammaticaly completely incorrect, I get the result. This is funny because the result contains F5 (F5+rmester)! (F5 is o with two long diacritics, F4 is o with one small v turned upsidedown on the top of it, F6 is o with two dots on it. F5 F6 are the official characters, but they are different.) I would really apreciate if someone could enlighten me... Where is the problem? Is there a problem wih MySQL? Or the problem is with the localisation function of FreeBSD? Both the previous Linux server and the current BSD has setlocale installed and has the iso-8859.2 locale. I am sorry, but my English might be to week to explain the situation correctly. Regards, Peter - 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: Join with LIMIT faster in 2 queries than 1 query, why ?
Did you read about optimizing joins? Yes I've read the manuals docs. I see in the manual a section entitled Optimising LEFT JOIN and RIGHT JOIN but my JOIN in question is neither LEFT or RIGHT. Can you please point me at some specific URLs ? thanks, - Sam. Werner Stuerenburg [EMAIL PROTECTED] wrote: Did you read about optimizing joins? It is discussed in detail in the manual. Recently, we had a similar discussion here (thread Left Join very sl..) where it turned out that all the tricks in the manual applied. You can analyze your query and get detailed info on where and how to change your indexes, syntax etc. S A schrieb am Dienstag, 24. Juli 2001, 01:08:31: I have standard search functionality on my site where a user can see NN items per screen of a much longer list of search results. I've found that MySQL returns small result sets out of a list of hits faster when queried in 2 parts than queried once. Am I doing something wrong ? I have a fairly standard join query on a few tables with about 20,000 rows. SELECT g.poster_id, g.poster_name, p.file_name, p.file_size, p.post_subject, p.post_id, DATE_FORMAT(p.post_date,'%b-%d-%y') AS date, p.file_extension FROM users_to_files u, posts p, posters g WHERE p.poster_id = g.poster_id AND p.post_id = u.post_id AND u.user_id = $folder_owner_id AND u.folder_id = $src_folder ORDER_BY g.poster_name, p.post_subject LIMIT 0,10 When I do a SELECT on that join to get just 10 rows using an ORDER BY and a LIMIT 0,10 it takes about 2 seconds per set of 10. As an experiment I broke up this query into 2 parts it only takes about 0.2 seconds or less per set of 10. #1 The first query gets just the post_id's that I care about. SELECT p.post_id FROM posts p, posters g $POSTS_TO_GROUPS WHERE p.poster_id = g.poster_id AND p.file_size 0 ORDER BY g.poster_name, p.post_subject #2 Then I do the joined query on all the columns on just the small list of post_ids to act as a LIMIT. SELECT g.poster_id, g.poster_name, p.file_name, p.file_size, p.post_subject DATE_FORMAT(p.post_date,'%b-%d-%y') AS date, p.file_extension FROM posts p, posters g WHERE p.post_id IN ($post_ids_in) AND p.poster_id = g.poster_id ORDER BY g.poster_name, p.post_subject Shouldn't MySQL be as fast or faster to do this join in the 1 query than the 2 queries ? Does the MySQL optimizer not recognize that only the few rows specified by the LIMIT actually need to be retrieved ? If so is there a way to help MySQL optimize the join ? Displaying a page full portion at a time of a long list of results is a very common web thing for MySQL to do so hopefully I'm just doing something wrong. thanks, - Sam. - Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/ -- Herzlich Werner Stuerenburg _ ISIS Verlag, Teut 3, D-32683 Barntrup-Alverdissen Tel 0(049) 5224-997 407 · Fax 0(049) 5224-997 409 http://pferdezeitung.de - Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/
Urgent Help needed for installing
Hello All, I've been trying upgrade our current MySQL 3.23.39 to the 3.23.40 I have tired with both the binary distro and source distro with no luck. The install seems to go OK, but I can't start the mysql server. This is on a RH/Linux 6.2 Kernel 2.4.6 i686 system. Please disregard this posting, after blowing everything out and starting from scratch I was finally able to get the source distro successfully installed now! :) Just one other question, I thought that the source distro 3.23.40 came with BDB table handlers enabled by default?? Come find out it doesn't!! Is there something else that needs to be done during the install to enable BDB table handlers?? THX's Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225)686-2002 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= - 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: AUTO_INCREMENT problem in table
If this is a MyISAM table you can use myisamchk to set the auto_incriment value. Check out the man page. ryan - Original Message - From: Stefan Hinz [EMAIL PROTECTED] To: [EMAIL PROTECTED]; Peter Wiherkoski [EMAIL PROTECTED] Sent: Wednesday, July 25, 2001 12:15 PM Subject: Re: AUTO_INCREMENT problem in table Dear Peter, Even if i delete the record where id=65535 DELETE FROM cust WHERE id=65535 AUTO_INCREMENT starts with 65535 and i cant add any more records. Sad, but true, MySQL remembers the biggest number inserted into an auto_increment column. If you delete the row in question, the next insert will add the biggest number again. The only workaround I can see is to copy the table (without the problem id row) to another table with the same structure. Maybe someone has a better solution?! Regards, -- Stefan Hinz Geschäftsführer / CEO iConnect e-commerce solutions GmbH # www.js-webShop.com www.iConnect.de # Gustav-Meyer-Allee 25, 13355 Berlin # Tel: +49-30-46307-382 Fax: +49-30-46307-388 - Original Message - From: Peter Wiherkoski [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, July 25, 2001 4:47 PM Subject: AUTO_INCREMENT problem in table Hi, got a problem: My table (part of it) looks like this: CREATE TABLE cust ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (id) ); When i type INSERT INTO cust VALUES(NULL,Peter); the value of id will increase by 1 every time i insert a record. By misstake i typed INSERT INTO cust VALUES(20,Maria); the id (20) then was downsized to the top limit of SMALLINT - 65535. Next time i type INSERT INTO cust VALUES(NULL,Joe); the AUTO_INCREMENT tries to increase id by 1, but it cant because AUTO_INCREMENT for id starts with 65535 and cant go any higher. Even if i delete the record where id=65535 DELETE FROM cust WHERE id=65535 AUTO_INCREMENT starts with 65535 and i cant add any more records. How do i get out of this mess? /Peter - 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
Index names chosen for CREATE INDEX etc. matter to MySQL ?
Do the names of indexes have any significance to MySQL internals or is the name merely a convenience for the DB operator ? For now I try to match the index names to the column names used but I'm wondering if the name matters at all. thanks, - Sam. - Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/
innodb unrecognized option
I think mysqld may have a problem parsing long lines in my.cnf. I recieve the following error when trying to start mysqld: /usr/local/libexec/mysqld: unrecognized option `--r3/ibdata31:2G' Here is the line that is offending mysqld (it is all one line in my.cnf). If I remove or shorten this line in any way, mysql runs fine. innodb_data_file_path = dr1/ibdata0:2G;var/mysql/ibdata1:2G;var/mysql/ibdata2:2G;var/mysql/ibdata5:2 G;var/mysql/ibdata6:2G; dr3/ibdata7:2G;dr3/ibdata8:2G;dr3/ibdata9:2G;dr3/ibdata10:2G;dr3/ibdata11:2G ;dr3/ibdata12:2G;dr3/ibdata13:2G; dr3/ibdata14:2G;dr3/ibdata15:2G;dr3/ibdata16:2G;dr3/ibdata17:2G;dr3/ibdata18 :2G;dr3/ibdata19:2G;dr3/ibdata20:2G; dr3/ibdata21:2G;dr3/ibdata22:2G;dr3/ibdata23:2G;dr3/ibdata24:2G;dr3/ibdata25 :2G;dr3/ibdata26:2G;dr3/ibdata27:2G; dr3/ibdata28:2G;dr3/ibdata29:2G;var/mysql/ibdata30:2G;dr3/ibdata31:2G Anyone know what is going on? Is this a long line problem or something else? Thanks. 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 [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Index names chosen for CREATE INDEX etc. matter to MySQL ?
Do the names of indexes have any significance to MySQL internals or is the name merely a convenience for the DB operator ? Well, PRIMARY KEY does carry some significance... ;-) Other than that, no. But you need them if you should ever want to do an ALTER TABLE to change the index definitions. For now I try to match the index names to the column names used but I'm wondering if the name matters at all. They only matter as far as using logical names will help you keep your sanity when you need to restructure your DB three years hence. / 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: AUTO_INCREMENT problem in table
Dear Peter, Even if i delete the record where id=65535 DELETE FROM cust WHERE id=65535 AUTO_INCREMENT starts with 65535 and i cant add any more records. Sad, but true, MySQL remembers the biggest number inserted into an auto_increment column. If you delete the row in question, the next insert will add the biggest number again. The only workaround I can see is to copy the table (without the problem id row) to another table with the same structure. Maybe someone has a better solution?! how about upgrading the smallint id column to a larger integer size? mysql ALTER TABLE cust CHANGE id INT UNSIGNED - NOT NULL AUTO_INCREMENT; / 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: Urgent Help needed for installing
At 2:55 PM -0500 7/25/01, MikemickaloBlezien wrote: Hello All, I've been trying upgrade our current MySQL 3.23.39 to the 3.23.40 I have tired with both the binary distro and source distro with no luck. The install seems to go OK, but I can't start the mysql server. This is on a RH/Linux 6.2 Kernel 2.4.6 i686 system. Please disregard this posting, after blowing everything out and starting from scratch I was finally able to get the source distro successfully installed now! :) Just one other question, I thought that the source distro 3.23.40 came with BDB table handlers enabled by default?? Come find out it doesn't!! Is there something else that needs to be done during the install to enable BDB table handlers?? Source distributions don't really come with any table handler enabled by default, because there are no binaries included in the distribution. To see what options control the table handlers, run this command: ./configure --help | more THX's Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225)686-2002 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- Paul DuBois, [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
FW: encrypt
How about this: --- INSERT INTO Table VALUES(Lamer_ID, SELECT ENCRYPT(you are lame...!,sa)); --- uses the unix-crypt()-function, means, its a One-Way-Crypt! sa must be a 2-char long Salt-String... yo can also use Encode and Decode-Functions...Look into the MySQL-Manual! ;-) Guten Abend Christian [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: Urgent Help needed for installing
MikemickaloBlezien [EMAIL PROTECTED] writes: Hello All, I've been trying upgrade our current MySQL 3.23.39 to the 3.23.40 I have tired with both the binary distro and source distro with no luck. The install seems to go OK, but I can't start the mysql server. This is on a RH/Linux 6.2 Kernel 2.4.6 i686 system. Please disregard this posting, after blowing everything out and starting from scratch I was finally able to get the source distro successfully installed now! :) Just one other question, I thought that the source distro 3.23.40 came with BDB table handlers enabled by default?? Come find out it doesn't!! Is there something else that needs to be done during the install to enable BDB table handlers?? Hi, Please read: http://www.mysql.com/doc/B/D/BDB_install.html Regards, Matt -- For technical support contracts, visit https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Matt Wagner [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Herr Direktor /_/ /_/\_, /___/\___\_\___/ Hopkins, Minnesota USA ___/ 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
FW: encrypt
Sorry --- How about this WITHOUT SELECT: --- INSERT INTO Table VALUES(Lamer_ID, ENCRYPT(you are lame...!,sa)); --- uses the unix-crypt()-function, means, its a One-Way-Crypt! sa must be a 2-char long Salt-String... yo can also use Encode and Decode-Functions...Look into the MySQL-Manual! ;-) Guten Abend Christian [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
sql/table structure question.
We have an optimization question that probably could be easily resolved with some of the additional features of some commercial database products, but, being stubborn about migrating away from MySQL (What can't it do again?) I'm looking for a solution to a particular design and optimization issue we are having. This may of course not be the most appropriate list for the question, but, it does contains perhaps the best group of individuals for which to pose it to. Here's the basic overview: We are (re)designing a manufacturing process flow (recipe) system which tracks the various processess/specficiations/parameters that a widget sees from raw material to assembly. So, essentially, we have Process Steps (generalized steps in the manufacturing process), which contain specifications (like do it for x seconds, with y tool) and parameters (do it at 80 degrees C, do it at 200T pressure). So, a flow (recipe) would contain process steps which the associated parameters/specifications in a given sequence. To accomodate this we have the following table structures: --- # The basic flow information CREATE TABLE base_flows ( flowID int(10) unsigned NOT NULL default '0', flow_title char(100) NOT NULL default '', date_added int(10) unsigned NOT NULL default '0', added_by mediumint(8) unsigned NOT NULL default '0', PRIMARY KEY (flowID) ); # Process steps CREATE TABLE process_steps ( process_stepID mediumint(8) unsigned NOT NULL default '0', process_step_name char(50) NOT NULL default '', process_step_type tinyint(3) unsigned NOT NULL default '0', process_step_location tinyint(3) unsigned NOT NULL default '0', toolID mediumint(8) unsigned NOT NULL default '0', PRIMARY KEY (process_stepID) ); # Names of step specifications/parameters (since names like Pressure) # might be reused with varying values CREATE TABLE step_specification_names ( step_spec_nameID mediumint(8) unsigned NOT NULL default '0', step_spec_name char(50) NOT NULL default '', step_spec_type tinyint(1) unsigned NOT NULL default '0', PRIMARY KEY (step_spec_nameID), KEY step_spec_type (step_spec_type) ); # Step specifications (containing the target value, a unitID referencing # measurement units (T, sec, C), max and min ranges that we want to hit, # and step_spec_nameID to reference the step_spec_name (Pressure, Temp) CREATE TABLE step_specifications ( step_specID mediumint(8) unsigned NOT NULL default '0', step_spec_nameID mediumint(8) unsigned NOT NULL default '0', target_value float NOT NULL default '0', unitID mediumint(8) unsigned NOT NULL default '0', target_max float NOT NULL default '0', target_min float NOT NULL default '0', date_added int(10) unsigned NOT NULL default '0', added_by mediumint(8) unsigned NOT NULL default '0', PRIMARY KEY (step_specID), KEY step_spec_nameID (step_spec_nameID), KEY target_value (target_value) ); # The actual recipe collections (sequences) CREATE TABLE base_flow_recipes ( flowID int(10) unsigned NOT NULL default '0', process_stepID mediumint(8) unsigned NOT NULL default '0', step_specID mediumint(8) unsigned NOT NULL default '0', seq tinyint(3) unsigned NOT NULL default '0', PRIMARY KEY (flowID,process_stepID,step_specID,seq), KEY process_stepID (process_stepID), KEY seq (seq), KEY step_specID (step_specID) ); This works reasonably well (We can fairly easily query the above data to build recipes). But, enter in some snags. 1) In order to avoid creating thousands of recipes we do things called SPLITS when we are in a development phase. Meaning, we take a few parameters and change the values / order (meaning, if we were making bread we might use 2 cups of flour as compared to 3, and do so after adding the water as opposed to before). So, we need a smart way to store changes to the above flows without recreating whole new flows. 2) It turns out not all values are clear cut floats. Ie, we might have odd ball parameters that really only have ALPHA representations. Ie, instead of it always being something like 10.2 F we might have to store LLL Oven). So, the obvious solution might be to remove the floats and use chars in the step_specifications table. Well, wait until you see 3. 3) We need a smart way to SCORE SIMILARITY between recipes (and, to make it even more complex, score similarity taking into account splits). Meaning, we want to be able to say give me the flows the closest resembly THIS flow. Or, more accurately, show me the widgets that closest resemble this widget. Meaning it should be smart enough to know that 100 C is close to 90 C then is 110 C in process step 5 of two compared flows. This is why chars become difficult to deal with. Sounds like the job of a recursive select.. but, perhaps there exists some additional table hierarchies that I'm not thinking about to easily compare multiple levels of data. Your brilliance is requested and thanked in advance,
large_files_support
Hi All:) I'm running mysql-3.23.30-gamma on FreeBSD 4.3-stable and running into a problem with max file sizes. I've got a table, names, which is taking 4294967288 bytes. I notice this is 8 bytes less than 2^32, so I assume there's a 32 bit pointer issue within mysql somewhere. large_files_support is ON, but trying to write more records gives me this error: Err 1114 (The table 'names' is full) writing record Please, someone, tell me that mysql isn't limited to 4 GB tables:) How do I create much larger tables? Thanks! --- David - 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: Urgent Help needed for installing
At 4:08 PM -0500 7/25/01, MikemickaloBlezien wrote: On Wed, 25 Jul 2001 16:04:17 -0500, Paul DuBois [EMAIL PROTECTED] wrote: Source distributions don't really come with any table handler enabled by default, because there are no binaries included in the distribution. To see what options control the table handlers, run this command: ./configure --help | more Paul, So this would have to be done thur the install of the source distro?? Yes. That's what you were asking about, weren't you? Binary distributions don't include a configure script. Do I need to re-install the source again to use BDB tables?? You need to recompile, then reinstall the binaries that the recompilation builds, yes. Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225)686-2002 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- Paul DuBois, [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
Urgent help needed, is this a version bug?
Mysql: We have installed mysql 3.23.26 beta version to a BSD 4.2 server. It works fine, however when we test a php file which just function to insert some data to a table in database, and display all data on the web page. After run this, the data have been inserted and displayed; however, it give us a warning message Warning: MySQL: Unable to save result set in . . I test the same scripts in many other servers with MySQl 3.22.23,(BSD) , MYSQL 2.23.36(Linux), there are no errors or any warning messges at all. Is there a way to reconfigration MYSQL, or PHP to eliminate this problem. The following is a typical test .php: to run this sript, you just create a table call user on your test database. SQL Scripts to create the table create table user (Uname varchar(20) passvarchar(8)); HTML BODD TITLE New Document /TITLE /HEAD BODY ?php $db = mysql_connect(localhost, test, test1) ; mysql_select_db(testdata); if($submit) { $query = INSERT INTO user (user, pass) values ('$user', '$pass') ; $result = mysql_query($query, $db); echo Your values have been inserted in databaseBR; } $select = SELECT * FROM user; $result1 = mysql_query($select, $db); while($r = mysql_fetch_array($result1)) { $user = $r[user]; $pass = $r[pass]; echo This is user: $userBR; echo This is pass: $passBR; } ? form name = frm method = get Username:input type = text name = userbr Password:input type = password name = pass input type = submit name = submit /form /BODY /HTML Thanks, John _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - 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
src rpm build error
Greetings all, I have a RedHat 7.1 on i386 (Pentium III), and I am trying to build the 2.23.40-1 src rpm, and I get the following error: --- th-extra-charsets=complex --exec-prefix=/usr --libexecdir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share --localstatedir=/var/lib/mysql --infodir=/usr/info --includedir=/usr/include --mandir=/usr/man --with-comment=Official MySQL RPM; # Add this for more debugging support # --with-debug # Add this for MyISAM RAID support: # --with-raid ' creating cache ./config.cache checking host system type... i686-pc-linux-gnu checking target system type... i686-pc-linux-gnu checking build system type... i686-pc-linux-gnu checking for a BSD compatible install... /usr/bin/install -c checking whether build environment is sane... yes checking whether make sets ${MAKE}... yes checking for working aclocal... found checking for working autoconf... found checking for working automake... found checking for working autoheader... found checking for working makeinfo... found checking whether to enable maintainer-specific portions of Makefiles... no checking whether build environment is sane... yes checking whether make sets ${MAKE}... (cached) yes checking for gawk... gawk checking for gcc... egcs checking whether the C compiler (egcs -O3 ) works... no configure: error: installation or configuration problem: C compiler cannot create executables. error: Bad exit status from /var/tmp/rpm-tmp.97303 (%build) RPM build errors: Bad exit status from /var/tmp/rpm-tmp.97303 (%build) My gcc version is gcc-2.96-85 .. Any ideas on what is going wrong? When I compile the source tar ball, it compiles, and the configure script doesn't give the same error. Nicholas - 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: Urgent Help needed for installing
I've been trying upgrade our current MySQL 3.23.39 to the 3.23.40 I have tired with both the binary distro and source distro with no luck. The install seems to go OK, but I can't start the mysql server. This is on a RH/Linux 6.2 Kernel 2.4.6 i686 system. Please disregard this posting, after blowing everything out and starting from scratch I was finally able to get the source distro successfully installed now! :) Just one other question, I thought that the source distro 3.23.40 came with BDB table handlers enabled by default?? Come find out it doesn't!! Is there something else that needs to be done during the install to enable BDB table handlers?? Thanks Matt! I looked all over the manual, and I'll be damn if I could find that! Oh well :) Thx's On Wed, 25 Jul 2001 16:08:52 -0500, Matt Wagner [EMAIL PROTECTED] wrote: Hi, Please read: http://www.mysql.com/doc/B/D/BDB_install.html Regards, Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225)686-2002 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= - 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 credit cards
On Wed, Jul 25, 2001 at 07:12:17PM +0200, Alexander Skwar wrote: [snip] 'encryption'? Hmm, how about: none? If you don't need to reconstruct the cc#, md5 will be good. Indeed. That is however rarely the case with credit card numbers. However, if you need to reconstruct it, nothing is safe. And that's quite simple: a) You need to get access to the MySQL server. Impossible to do from the outside if '--skip-networking' is used. b) So, only possible from the localhost. This means, there must have been a break in to the MySQL server. Once he's on the server, he can do anything he likes. He can also read the source code of your PHP/PERL pages. There the password will be stored, somewhere. Once the password has been found (which is nothing but a matter of time), your encryption is broken. That is only true for a parallel cipher. A non-parallel cipher (like PGP) allows you to store just the public key on the server, and have the private key stored somewhere else save, on a way more secured box that actually handles the transactions. Greetz, Peter -- Against Free Sex! http://www.dataloss.nl/Megahard_en.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
Re: mysql and credit cards
On 7/25/01 at 7:12 PM, Alexander Skwar wrote: However, if you need to reconstruct it, nothing is safe. And that's quite simple: a) You need to get access to the MySQL server. Impossible to do from the outside if '--skip-networking' is used. b) So, only possible from the localhost. This means, there must have been a break in to the MySQL server. Once he's on the server, he can do anything he likes. He can also read the source code of your PHP/PERL pages. There the password will be stored, somewhere. Once the password has been found (which is nothing but a matter of time), your encryption is broken. I respectfully disagree with this assessment. Encrypting data in a database can be useful, even if the key is relatively easy to find. The situation you describe is one of a determined individual trying to gain access to credit card data. This is indeed the most difficult type of person to prevent gaining access to your data. If they are able to compromise your system then they will be able to decrypt the data in the database and get the credit card numbers. However, encryption prevents casual viewing of credit card data and prevents the discovery of credit card data if a secondary system is compromised. For casual viewing, you may have employees who have access to the database for the purpose of fulfilling orders or contacting customers. If credit card numbers are encrypted then the employees will have to take a positive step to steal credit card numbers. They won't be able to simply jot down numbers out of the data they have available. For example, if an individual gains the ability to execute SQL statements on your server, but doesn't have file access. If the credit card numbers are stored plain then they can gain access to them. If they are stored encrypted then the data is of no use without the key. So, overall, I'd say: Don't hassle with encryption: It's not worth it. I would counter that symmetric encryption is reasonably easy to implement and provides a modicum of security, so why not go ahead and do it. Just don't be fooled that a determined individual won't be able to defeat your encryption. It's rather like HTTPS encryption. It's a first step that prevents casual peeking at the data which is being transmitted between a client and the server. However, it does not prevent a determined individual from seeing the traffic. [fletcher] -- Fletcher Sandbeck [EMAIL PROTECTED] Lasso Product Specialist [EMAIL PROTECTED] Blue World Communications, Inc. http://www.blueworld.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: Direct access to a mySQL DB via Visual Basic
MyVbQL is a direct interface to MySQL databases ... you don't need to use the ADO/ODBC combination ... you can get some more details on the MyVbQL web page ... http://www.icarz.com/mysql/index.html Eric Stephen Sherlock wrote: I've been told that it's possible to connect directly to a mySQL DataBase with Visual Basic, by using the {mySQL} provider within the ADO Data Control, however, none of the providers on the list include mySQL at all, and I was wondering if anyone could shed some light on this. I want to connect to the DB directly rather than through ODBC because the final project I'm working on will include some hefty processor and memory usage as it is, and the last thing I need is more resources being taken up by ODBC. Thanks in advance, Stephen Sherlock - 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
In possibility 1, if the database is truly running on local, you should be able to: a)mysqldump --all-databases all_that_dump.sql b)mysql (logon stuff for remote machine) all_that_dump.sql Because the database is running on local the client (mysql) should also be loaded. Have a great day... John On Wednesday 25 July 2001 13:23, Stefan Hinz wrote: Dear Jay, 1st possibility: a) mysqldump --all-databases all_that_dump.sql b) FTP all_that_dump.sql to remote machine c) [on remote machine:] mysql all_that_dump.sql 2nd possibility: Like above, but using PhpMyAdmin (www.phpmyadmin.com) for writing to (a) and reading from (c) dump file. You won't have to ftp the dump file as you can read it from the local machine. 3rd possibility: ftp the database directories from local to remote machine. You need to have MyISAM tables (extensions: .MYI and .MYD), not ISAM tables if there are different operating systems on those two machines. Datadir for all databases is usually /var/lib/mysql on Linux machines and C:\mysql\data on Windows machines. Regards, -- Stefan Hinz Geschäftsführer / CEO iConnect e-commerce solutions GmbH # www.js-webShop.com www.iConnect.de # Gustav-Meyer-Allee 25, 13355 Berlin # Tel: +49-30-46307-382 Fax: +49-30-46307-388 - Original Message - From: Jay McGarry [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, July 25, 2001 4:57 PM Subject: Mysql what's the easiest way to transfer a local database to a remote one? - 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
up arrow using mysql client on windows
I know I've seen the solution to this somewhere... Running bin\mysql in a DOS window I find that my up arrow will not recall commands from a command buffer, even when DOSKEY is loaded in the parent DOS window. But I know that on a Linux machine where I used to run mysql I could up arrow through previous commands. Is this feature disabled in Windows? Is there a switch I can throw to enable it? Thanks, and please no heat about not being on Linux... it wasn't my choice... Alan - 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 credit cards
AND, you'd want to 'protect' the data from viewing if you've 'dumped' the database or backed it up to an 'insecure' media or location (to tape/disk)... Encrypting the data, with the 'key' or 'salt' located somewhere else, would allow you to 'transport' the tables containing the sensitive data (i.e. credit card info) with some sense of security... - Original Message - From: Fletcher Sandbeck [EMAIL PROTECTED] To: mysql [EMAIL PROTECTED] Sent: Wednesday, July 25, 2001 3:08 PM Subject: Re: mysql and credit cards On 7/25/01 at 7:12 PM, Alexander Skwar wrote: However, if you need to reconstruct it, nothing is safe. And that's quite simple: a) You need to get access to the MySQL server. Impossible to do from the outside if '--skip-networking' is used. b) So, only possible from the localhost. This means, there must have been a break in to the MySQL server. Once he's on the server, he can do anything he likes. He can also read the source code of your PHP/PERL pages. There the password will be stored, somewhere. Once the password has been found (which is nothing but a matter of time), your encryption is broken. I respectfully disagree with this assessment. Encrypting data in a database can be useful, even if the key is relatively easy to find. The situation you describe is one of a determined individual trying to gain access to credit card data. This is indeed the most difficult type of person to prevent gaining access to your data. If they are able to compromise your system then they will be able to decrypt the data in the database and get the credit card numbers. However, encryption prevents casual viewing of credit card data and prevents the discovery of credit card data if a secondary system is compromised. For casual viewing, you may have employees who have access to the database for the purpose of fulfilling orders or contacting customers. If credit card numbers are encrypted then the employees will have to take a positive step to steal credit card numbers. They won't be able to simply jot down numbers out of the data they have available. For example, if an individual gains the ability to execute SQL statements on your server, but doesn't have file access. If the credit card numbers are stored plain then they can gain access to them. If they are stored encrypted then the data is of no use without the key. So, overall, I'd say: Don't hassle with encryption: It's not worth it. I would counter that symmetric encryption is reasonably easy to implement and provides a modicum of security, so why not go ahead and do it. Just don't be fooled that a determined individual won't be able to defeat your encryption. It's rather like HTTPS encryption. It's a first step that prevents casual peeking at the data which is being transmitted between a client and the server. However, it does not prevent a determined individual from seeing the traffic. [fletcher] -- Fletcher Sandbeck [EMAIL PROTECTED] Lasso Product Specialist [EMAIL PROTECTED] Blue World Communications, Inc. http://www.blueworld.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
Beginner with MYSQL on linux RH 7.1
Hi everyone! I just install the RH 7.1 with mySQL. I'm developer JAVA, and I'd like to know how I start the service (mysql) on linux RH 7.1. The directory is /usr/share/mysql I want to set the port and create a table. I know the comand to create a table, insert data and so on... But I dont know how I can start the client to do the table. Thanks a lot. Ass. Mário Coelho __ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.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
I need help constructing a search thingy using % _ in LIKE
Hi all, I have a problem, I have some VARCHAR data in a column called Sequence looking like this:- 5;6 12;6 120;6 5;6;1 12;6;12 120;6;105 So there could be anything from 2 to n numbers seperated by ; Each number could be 1 to 3 digits long (eg 5 12 or 121). I have tried various searches like the following but none of them seem fool proof, i.e. they miss data:- # This is for the maximum of 3 sequences searching by the 1st icon SELECT Sequence, Message, id FROM $sqlTable WHERE Sequence LIKE '$filterValue;%_%_' ORDER BY Message; # This is for the maximum of 3 sequences searching by the 2nd icon SELECT Sequence, Message, id FROM $sqlTable WHERE Sequence LIKE '_;$filterValue' OR Sequence LIKE '__;$filterValue' OR Sequence LIKE '%;$filterValue;%' ORDER BY Message; # This is for the maximum of 3 sequences searching by the 3rd icon SELECT Sequence, Message, id FROM $sqlTable WHERE Sequence LIKE '%;%;$filterValue' ORDER BY Message; 'Sequence' is where the numbers to search on are stored. $sqlTable is a variable containing the name of the table. $filterValue is a variable containing the search number. So what rules can I create to work for all examples, though the above examples are for only 3 sequences of ; seperated numbers, there could be upto 6 (though I would prefer a rule to cope with n amount). I am using perl so the OR LIKE part could be created PRE SELECT and then added onto the SELECT statement. This one is hurting my head.:-) M. -- Work:- postmasterAThinwick.demon.co.uk WEB:- http://www.hinwick.demon.co.uk Work:- mworsdallATshaftesburysoc.org.uk REPLACE AT with @ Home:- hinwickATworsdall.demon.co.ukWEB:- http://www.wizdom.org.uk Shadow:- webmasterATshadow.org.uk WEB:- http://www.shadow.org.uk - 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: up arrow using mysql client on windows
At 6:52 PM -0400 7/25/01, Alan Zaitchik wrote: I know I've seen the solution to this somewhere... In the manual: http://www.mysql.com/doc/W/i/Windows_running.html You want to use mysqlc, not mysql. Running bin\mysql in a DOS window I find that my up arrow will not recall commands from a command buffer, even when DOSKEY is loaded in the parent DOS window. But I know that on a Linux machine where I used to run mysql I could up arrow through previous commands. Is this feature disabled in Windows? Is there a switch I can throw to enable it? Thanks, and please no heat about not being on Linux... it wasn't my choice... Alan -- Paul DuBois, [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
NULL valule
I have a date field, how do I check if it is null or empty in sql statement. I tried : select * where fDate = NULL; and also tried where fDate = ''; Both return nothing but actually there are records with no value. _ http://messenger.yahoo.com.au - Yahoo! Messenger - Voice chat, mail alerts, stock quotes and favourite news and lots more! - 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
Slave thread terminating
Hi all, I am currently researching an issue with slave thread terminating when an error occurs. background: Win2000 server SP2 MySQL server/client ver 3.23.38 MySQL ODBC ver 2.50.33 Program language Clarion 5.5 Our database program can insert and update records without a problem. When a new record is added to the database it flows through to the slave ok. When we change a record, the master bin-log shows an INSERT statement instead of a UPDATE statement to the slave. This causes a error with the slave thread which is cannot insert duplicate key and the slave will terminate. I am looking for a solution which will log the error, skip the error line, and restart the slave itself automatically. Is it possible to set the SQL_SLAVE_SKIP_COUNTER=1 permanently and make the slave thread go again after a retry? Another approach could be to monitor the slave thread and run a batch file with the above commands in it. Any help on the above issue would be greatly appreciated. TIA Chris Knighton Technical Manager Readysell Computer Solutions [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: mysql and credit cards
You can always degrade the credit card. 1) verify AUTH ONLY (not capture) with your CC provider. (if you want to verify they have funds available and the CC is valid) 2) store the CC # in the database 3) ..do your order processing thing or whatever you need to have the CC for... 4) capture funds 5) degrade card value in the DB That seems to work well for non recurring credit card transactions (ie single purchases) Bill Elvis Gibbs goEbusiness.com - putting e-motion in your business email - [EMAIL PROTECTED] work - 301-668-5090 cell - 301-748-6938 On Wed, 25 Jul 2001, Steve Brazill wrote: AND, you'd want to 'protect' the data from viewing if you've 'dumped' the database or backed it up to an 'insecure' media or location (to tape/disk)... Encrypting the data, with the 'key' or 'salt' located somewhere else, would allow you to 'transport' the tables containing the sensitive data (i.e. credit card info) with some sense of security... - Original Message - From: Fletcher Sandbeck [EMAIL PROTECTED] To: mysql [EMAIL PROTECTED] Sent: Wednesday, July 25, 2001 3:08 PM Subject: Re: mysql and credit cards On 7/25/01 at 7:12 PM, Alexander Skwar wrote: However, if you need to reconstruct it, nothing is safe. And that's quite simple: a) You need to get access to the MySQL server. Impossible to do from the outside if '--skip-networking' is used. b) So, only possible from the localhost. This means, there must have been a break in to the MySQL server. Once he's on the server, he can do anything he likes. He can also read the source code of your PHP/PERL pages. There the password will be stored, somewhere. Once the password has been found (which is nothing but a matter of time), your encryption is broken. I respectfully disagree with this assessment. Encrypting data in a database can be useful, even if the key is relatively easy to find. The situation you describe is one of a determined individual trying to gain access to credit card data. This is indeed the most difficult type of person to prevent gaining access to your data. If they are able to compromise your system then they will be able to decrypt the data in the database and get the credit card numbers. However, encryption prevents casual viewing of credit card data and prevents the discovery of credit card data if a secondary system is compromised. For casual viewing, you may have employees who have access to the database for the purpose of fulfilling orders or contacting customers. If credit card numbers are encrypted then the employees will have to take a positive step to steal credit card numbers. They won't be able to simply jot down numbers out of the data they have available. For example, if an individual gains the ability to execute SQL statements on your server, but doesn't have file access. If the credit card numbers are stored plain then they can gain access to them. If they are stored encrypted then the data is of no use without the key. So, overall, I'd say: Don't hassle with encryption: It's not worth it. I would counter that symmetric encryption is reasonably easy to implement and provides a modicum of security, so why not go ahead and do it. Just don't be fooled that a determined individual won't be able to defeat your encryption. It's rather like HTTPS encryption. It's a first step that prevents casual peeking at the data which is being transmitted between a client and the server. However, it does not prevent a determined individual from seeing the traffic. [fletcher] -- Fletcher Sandbeck [EMAIL PROTECTED] Lasso Product Specialist [EMAIL PROTECTED] Blue World Communications, Inc. http://www.blueworld.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 - 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
Uploading files using PHP to database.
Could anyone shed some light on how to go about uploading a text file with a web interface using php into a mysql database. wow, that was a mouthfull. I would much appreciate it. Kit _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - 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
my sql data base
Apologies in advance: I am a developer with mysql and active and my work involved CAPI of MYSQl,Database DeZign with Dezign and database development. Currently looking out for job opportunities in mySQl related areas.Can suggest me any exclusive websites and openings in mysql related areas. ..I do apologize in advance for asking this in this forum.I am hoping that someone will get back to me. Regards, Ravi Do You Yahoo!? Get your free @yahoo.co.uk address at http://mail.yahoo.co.uk or your free @yahoo.ie address at http://mail.yahoo.ie - 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
Question on design
(please reply directly) I'm new to mysql and I'm working on a design for an application that will require aLOT of lookups on IP addresses. Data set is on the order of millions of addresses. In my reading, I have not encountered a TYPE of ip_address or some such. Did I miss it? If there is no such type, what would be the best way to setup the addressing for the fastest possible locate? Use varchar (15) and put the whole address in? Use 4 fields A, B, C D all as tinyint to represent A.B.C.D? My assumption is that an integer search would be faster thana text search - am I correct? What works best? How would you tackle this? Thanks, David Lott smime.p7s
Re: NULL valule
At 10:17 AM +1000 7/26/01, Alan Tse wrote: I have a date field, how do I check if it is null or empty in sql statement. I tried : select * where fDate = NULL; Wrong test. NULL is special. Use: where fDATE IS NULL and also tried where fDate = ''; Both return nothing but actually there are records with no value. -- Paul DuBois, [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: Question on design
At 7:53 PM -0600 7/25/01, David Lott wrote: (please reply directly) I'm new to mysql and I'm working on a design for an application that will require a LOT of lookups on IP addresses. Data set is on the order of millions of addresses. In my reading, I have not encountered a TYPE of ip_address or some such. Did I miss it? No. There's no such type in MySQL. If there is no such type, what would be the best way to setup the addressing for the fastest possible locate? Use varchar (15) and put the whole address in? Use 4 fields A, B, C D all as tinyint to represent A.B.C.D? Or convert the address to a 32-bit integer and store it in an INT column. If you use separate columns, there are probably network mask operations and such that you'll find more difficult to do than if you store the address in a single column. Depends on what kind of things you want to do with the addresses. My assumption is that an integer search would be faster than a text search - am I correct? A 4-byte integer like INT would certainly be faster than a 15-char string. What works best? How would you tackle this? Thanks, David Lott -- Paul DuBois, [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: Mysql dumps FTP
Since the dump is ascii text with lots of repeating characters, it should zip or gzip quite nicely. The compressed file will take a lot less time in transfer over FTP, if network time/bandwidth is a problem. This becomes important when doing over-WAN stuff. The time involved for 1-3GB arch logs (compressed), if I remember, takes a number of hours to compress and up to 3 days to send over a 100kbps WAN across the world. Oracle arch logs don't compress (maybe they are already compressed?). Jim Michaels ([EMAIL PROTECTED]) - Original Message - From: Stefan Hinz [EMAIL PROTECTED] To: Jay McGarry [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, July 25, 2001 10:23 AM Subject: Re: Mysql Dear Jay, 1st possibility: a) mysqldump --all-databases all_that_dump.sql b) FTP all_that_dump.sql to remote machine c) [on remote machine:] mysql all_that_dump.sql 2nd possibility: Like above, but using PhpMyAdmin (www.phpmyadmin.com) for writing to (a) and reading from (c) dump file. You won't have to ftp the dump file as you can read it from the local machine. 3rd possibility: ftp the database directories from local to remote machine. You need to have MyISAM tables (extensions: .MYI and .MYD), not ISAM tables if there are different operating systems on those two machines. Datadir for all databases is usually /var/lib/mysql on Linux machines and C:\mysql\data on Windows machines. Regards, -- Stefan Hinz Geschäftsführer / CEO iConnect e-commerce solutions GmbH # www.js-webShop.com www.iConnect.de # Gustav-Meyer-Allee 25, 13355 Berlin # Tel: +49-30-46307-382 Fax: +49-30-46307-388 - Original Message - From: Jay McGarry [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, July 25, 2001 4:57 PM Subject: Mysql what's the easiest way to transfer a local database to a remote one? - 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: BLOB and LONGTEXT question
At 10:11 PM -0500 7/25/01, Mike wrote: I have throughly looked at the documentation and I am beginning to get frustrated. It says that a LONGTEXT field's max length is L+4 bytes, where L 2^32. I understand the extra 4 bytes(+4) which is to keep track of the size sense LONGTEXT is no more than a really big VARCHAR field. If my calculations are correct that is 4294967296 bytes or just over 4Gig. Am I right or way off? If I am correct, I remember reading that is about the max size of a MySQL DB. And why couldn't they just come out and say this is the max size of the LONGTEXT. Do they just want to irritate me? I doubt if they are trying to irritate you. The maximum size of a MySQL database depends on your file system constraints, not on the size of a LONGTEXT. Some OSes have a max file size of 2GB, for example; others don't. Anyway, my second question is, in this field I will want to save formatting characters such as newlines, etc... Is it LONGTEXT what I want or do I want to use blob? Either can hold anything. The only difference is that BLOB types are case sensitive and TEXT types aren't. I have plenty more questions but I will try to answer them on my own first. Tata and thanks for your time, Mike 1 John 2:1 My little children, I write these things to you so that you may not sin. And if anyone sins, we have an Advocate with the Father, Jesus Christ the righteous. -- Paul DuBois, [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: Question on design
I would suggest that you use an INT column type to store the ip address. This would use a lot less space than a varchar column (when dealing with the magnitude that you describe)... the smaller the faster... The following functions make this very easy: INET_NTOA and INET_ATON They are described on http://www.mysql.com/doc/M/i/Miscellaneous_functions.html Because the entire address is stored in one INT field if you wanted to do any kind of select based on subnet you could use bitfield operations. Hope this helps. ryan - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: David Lott [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, July 25, 2001 9:00 PM Subject: Re: Question on design At 7:53 PM -0600 7/25/01, David Lott wrote: (please reply directly) I'm new to mysql and I'm working on a design for an application that will require a LOT of lookups on IP addresses. Data set is on the order of millions of addresses. In my reading, I have not encountered a TYPE of ip_address or some such. Did I miss it? No. There's no such type in MySQL. If there is no such type, what would be the best way to setup the addressing for the fastest possible locate? Use varchar (15) and put the whole address in? Use 4 fields A, B, C D all as tinyint to represent A.B.C.D? Or convert the address to a 32-bit integer and store it in an INT column. If you use separate columns, there are probably network mask operations and such that you'll find more difficult to do than if you store the address in a single column. Depends on what kind of things you want to do with the addresses. My assumption is that an integer search would be faster than a text search - am I correct? A 4-byte integer like INT would certainly be faster than a 15-char string. What works best? How would you tackle this? Thanks, David Lott -- Paul DuBois, [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
Moved MySQL to new machine.
I recently moved my MySQL databases from one RedHat Linux 6.x machine to a new RedHat 7.0 machine. Now, I can't seem to add databases. I get the following error message: mysqladmin: CREATE DATABASE failed; error: 'Can't create database 'jrtest'. (errno: 13)' And, I think I lost some sequences. I'm using Phorum for a message board. The sequence table was at 2942, but when I try to write a record to it, it changes the sequence back to 1! Is this a MySQL problem, or a problem with Phorum? Any help that you can render would be greatly appreciated. Jeremy
Re: BLOB and LONGTEXT question
On Wed, 25 Jul 2001, Paul DuBois wrote: I doubt if they are trying to irritate you. The maximum size of a MySQL database depends on your file system constraints, not on the size of a LONGTEXT. Some OSes have a max file size of 2GB, for example; others don't. Linux ext2 filesystem only allow 2GB max file system. Should I recompile with --enable-raid or --enable-large-files. Waht about standard binary and Myslqd-Max? - 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
Importing a text file into Mysql
Hi folks. I have a local Access database here which I have created tab seperated text files of each of the tables from to import into the Mysql db we are running on our ISP's server. However I have one field in one of the tables that is a text file and contains sometimes very long descriptions of a particular item, depending on which record it is. Now every time I import my text file (using phpmyadmin) when it gets to this particular field in certain records where there are a couple of paragraphs of information it inserts the first line of information. But then nothing else into this field. I'm pretty sure the import is chocking on the carriage returns used in the longer descriptions in some records. Does anyone know a way of getting around this problem and getting it to import properly. Basically the settings I use for importing are: Records seperated by tab, nothing is used to identify text and // is used to escape characters. Any hints would be much appreciated, and I can send samples of the import files if anyone needs to look at them to get a better idea. -- Duncan Pilcher Laboratory Systems Co-ordinator Analytical Reference Laboratories 5 Leveson St North Melbourne Victoria 3051 Ph: (03) 9328 3586 Fax: (03) 9326 5004 Email: [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: Compressed column support?
Hi, I have looked through the documentation (http://www.mysql.com/docs) and the mailing list archives (http://www.geocrawler.com/lists/3/Databases/8/0/) and find no mention of a feature that I was hoping was available: * text or varchar columns that are automatically compressed/uncompressed myisampack (http://www.mysql.com/doc/C/o/Compressed_format.html) will compress an entire table and mysql will decompress it when needed, but the table must be unpacked if you want to modify it in any way. - 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: isp complains about query...
SELECT * FROM listaemail WHERE id LIKE %% OR nome LIKE %% OR empresa LIKE %% OR email LIKE %% OR ativo LIKE %% ORDER BY nome; Time: 11 Lock_time: 0 Rows_sent: 30567 Rows_examined: 30568 before anyone jumps out and points it out, it is far from being optimized. it should (and will, as soon as i update the generator) read: select * from listaemail order by nome; my question is: could there possibly be a problem in the execution of such a query??? my isp's system administrator states this query is causing the server to become unstable. i firmly believe there is no harm in executing such query, even if it's not optimal. How often is this query being executed? Why do you need to fetch all 30k rows? - 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: Invalid SQL..?
Database error: Invalid SQL: select id, subid from policy where id='2' and subid='0' MySQL Error: 1054 (Unknown column 'id' in 'field list') Session halted. the output look like this.. mysql desc policy; +++--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +++--+-+-+---+ | service_charge | int(2) | | | 0 | | +++--+-+-+---+ 1 row in set (0.01 sec) The error message is quite self explanatory, there's no id or subid column in the table you are querying. - 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 multiple text fields
Hello I need to build a search utility which can query records based on multiple text parameter, my table will have about 10 fields and users can search on 3-5 parameters in one query. is there any way to achieve this other than doing a full table scan. thanks raghu -- Global Internet phone calls, voicemail, fax, e-mail and instant messaging. Sign-up today at http://www.hotvoice.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 vs. Oracle
I am currently trying to examine the benefits of continuing to use MySQL over Oracle. I need to know what MySQL's features are vs. those of Oracle. http://www.mysql.com/information/crash-me.php Also I am looking for performance data. Oracle prohibits benchmarks, so good luck finding them. What kind of scalability does MySQL have vs. that of Oracle. Is there (or will there be) any support in MySQL for something akin to Oracle's Parallel Server? I think MySQL's replication is what you would be looking for (http://www.mysql.com/doc/R/e/Replication.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