Re: Table crash!
- Original Message - From: Martin Mokrejs [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Friday, April 05, 2002 10:41 PM Subject: Table crash! Don't do this when the table is in the cache! The correct way (open two xterms and do commands in this order): mysql flush tables; mysql lock table repository write; $ myisamchk -f -d -v repository.MYI # this will tell you the status of the table based on the index file I've used myisamchk while mysql server was not running. I've shut it down first and then tryed to fix it with myisamchk. But I will try all those other options you have so kindly mentioned in your reply. THNX! Will tell you about the results. Regards, Ale Perme - 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
Advice for dataupload
Dear all, I have two design-identical database, one on my intranet, the other on the internet, is there any procedure that Synchronizes the content of two databases? I'm using PHP applications and MySQL Database, WindowsNT4 IIS Please advice Best Regards Hayan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
InnoDB-3.23.50 source prerelease downloadable from www.innodb.com
Hi! Since there has been a delay in the release of MySQL-3.23.50, due to problems with the gcc-3.0.4 compiler, Innobase Oy has decided to give out a source prerelease of InnoDB-3.23.50. It can be downloaded from http://www.innodb.com. SHOW CREATE TABLE or mysqldump do not list the FOREIGN KEYs in this prerelease because that requires a small change also in the MySQL part of code. Best regards, Heikki Tuuri Innobase Oy - 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
Help restoring data with mysqlbinlog
Hello all, I am trying to restore some data from the binary log and I am not sure on how to proceed. I understand restoring data is a two-stage process: 1. You start by restoring data saved with mysqldump. 2. Using mysqlbinlog, you restore the data logged in the binary log from the point where mysqldump stopped. It is on this second point that I would be grateful to get some help. My question is specifically: how does one know the value of the parameter position to pass on to mysqlbinlog? Thanks in advance, Balteo. (NOTE: this is a rephrased post of an earlier post that may have been confuse) - 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: Is it a bug in datetime function HOUR (now()-date_created) ?
* Son Nguyen
mysql select date_created from forums;
+-+
| date_created|
+-+
| 2002-04-04 19:27:03 |
+-+
1 row in set (0.00 sec)
mysql SELECT CONCAT(HOUR(now()-date_created), 'H',
MINUTE(now()-date_created), 'M', SECOND(now()-date_created), 'S') AS
dated_created from forums;
+---+
| dated_created |
+---+
| 282H24M24S|
+---+
1 row in set (0.00 sec)
mysql select now();
+-+
| now() |
+-+
| 2002-04-07 01:51:38 |
+-+
1 row in set (0.00 sec)
Please ignore me for the 11 second different between the select CONCAT
... statement and the select now() The thing I would like to ask is
something wrong with the function HOUR(now()-date_created) Why it
yielded a wrong number of hours for the subtraction function ???
The HOUR() function is not working the way you seem to think from the
manual:
`HOUR(time)'
Returns the hour for `time', in the range `0' to `23'.
mysql select HOUR('10:05:03');
- 10
What you probably need to do is to transform both datetime values to
seconds, find the difference, and then calculate the hours. Something like
this:
SELECT
@d1:=unix_timestamp(now()),
@d2:=unix_timestamp(date_created),
@d3:=@d1-@d2,
@h:=floor(@d3/3600),
@m:=floor((@d3-@h*3600)/60),
@s:=@d3-(@h*3600)-(@m*60),
CONCAT(@h,'H',@m,'M',@s,'S')
from forums;
--
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: Advice for dataupload
* Hayan Al Mamoun I have two design-identical database, one on my intranet, the other on the internet, is there any procedure that Synchronizes the content of two databases? Depends on what you mean with 'Synchronizes'. 'Real' synchronization, where both servers are written to and both must update eachother in close-to-real-time, is rather difficult, but possible. The challenge is to avoid duplicate primary keys and other problems arising from the fact that the data will not be inserted in the same order on both servers. Your application must be written/re-written with this in mind. mysql supports replication, both two-way and one-way. Two-way replication is when multiple servers are written to, and the problems mentioned above apply. One-way replication is when one server (the master) is written to, and the other(s) (the slaves) are only read from. This is pretty straight forward, and will not require any changes in your application. URL: http://www.mysql.com/doc/R/e/Replication.html For a simple, one-time transferral of the database from one server to another server, use mysqldump: URL: http://www.mysql.com/doc/m/y/mysqldump.html If you just need to synchronize the two databases once, you could try to 'merge' the output of mysqldump from the two databases. If the amount of data is small, this could be done manually with a text editor, otherwise some scripting language could be used, reading both files simultaneously and changing the conflicting primary keys. -- 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
Newbie question.
Hello people,
I am new to MySQL (to all SQL, better said) and I am in need of some help.
I create a table with these characteristics:
mysql CREATE TABLE Usuarios (Nombre TEXT NOT NULL,
- Password TEXT NOT NULL,
- Permiso INT(1),
- PRIMARY KEY(Nombre(30)));
Query OK, 0 rows affected (0.05 sec)
This allows me to insert things like: INSERT INTO Usuarios
(Nombre,Password) VALUES ('','');
I need to force those fields to have at least one letter or number, not
empty, nor space.
Is there a way I can do this, at least having it not empty?.
Thank you very much.
Regards,
Gastón.
-
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/Perl installation problem
I am having a problem with testing MySql (max) version 3.23.49a. I downloaded the 3 required Perl packages (Data-Dumper, DBI, and Msql-Mysql-modules), the latest versions, and installed them. The OS is RH Linux 7.2. The installation of both the binary MySql distribution and the Perl packages seemed to go ok, and the MySql installation passed the little initial tests. I am getting the following error doing run-all-tests: [root@scully sql-bench]# run-all-tests install_driver(mysql) failed: Can't locate DBD/mysql.pm in @INC (@INC contains: /usr/lib/perl5/5.6.0/i386-linux /usr/lib/perl5/5.6.0 /usr/lib/perl5/site_perl/5.6.0/i386-linux /usr/lib/perl5/site_perl/5.6.0 /usr/lib/perl5/site_perl .) at (eval 44) line 3. Perhaps the DBD::mysql perl module hasn't been fully installed, or perhaps the capitalisation of 'mysql' isn't right. Available drivers: ADO, ExampleP, Multiplex, Proxy. at /usr/local/mysql-max-3.23.49a-pc-linux-gnu-i686/sql-bench/server-cfg line 238 Any thoughts about what is wrong? Thanks. Chris Evans [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: Newbie question.
*Gastón Sancassano
mysql CREATE TABLE Usuarios (Nombre TEXT NOT NULL,
- Password TEXT NOT NULL,
- Permiso INT(1),
- PRIMARY KEY(Nombre(30)));
Query OK, 0 rows affected (0.05 sec)
This allows me to insert things like: INSERT INTO Usuarios
(Nombre,Password) VALUES ('','');
I need to force those fields to have at least one letter or number, not
empty, nor space.
Is there a way I can do this, at least having it not empty?.
No, you must do this in your application. There is currently no support for
such constraints in mysql, but there will probably be in version 4.x.
(Why do you use TEXT for Nombre and Password? CHAR or VARCHAR seems to be
more appropriate... also, INT(1) looks a little weird... you should use
TINYINT.)
--
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: SQL Question...
Thanks, My book that I have been studying from, only show commands and no examples. MySQL web had want I wanted at the very top. I have finish my page now. Chuck on 4/6/02 11:46 PM, Georg Richter at [EMAIL PROTECTED] wrote: On Sunday, 7. April 2002 05:56, Chuck \PUP\ Payne wrote: Hi, I am trying to set up a SQL statement using NOW(), what I am wanting to do is to use NOW() then do a search on any date less than 7 days. Before I get several e-mails asking why. I am trying to a news base database for the company intranet and I am wanting to only show newsitems that are 7 days or less. And I want NOW() to set today date and then go back as far as seven days. But I am not sure now to use less than in my statement with NOW(), or if it will work. http://www.mysql.com/doc/D/a/Date_and_time_functions.html There is a sample how to use TO_DAYS function. Regards Georg - 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: SQL Question...
The other way to do this is with the DATE_ADD function: select [columns] where [date column] DATE_ADD(NOW(), INTERVAL -7 DAY) --jeff - Original Message - From: Chuck PUP Payne [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Sunday, April 07, 2002 10:29 AM Subject: Re: SQL Question... Thanks, My book that I have been studying from, only show commands and no examples. MySQL web had want I wanted at the very top. I have finish my page now. Chuck on 4/6/02 11:46 PM, Georg Richter at [EMAIL PROTECTED] wrote: On Sunday, 7. April 2002 05:56, Chuck \PUP\ Payne wrote: Hi, I am trying to set up a SQL statement using NOW(), what I am wanting to do is to use NOW() then do a search on any date less than 7 days. Before I get several e-mails asking why. I am trying to a news base database for the company intranet and I am wanting to only show newsitems that are 7 days or less. And I want NOW() to set today date and then go back as far as seven days. But I am not sure now to use less than in my statement with NOW(), or if it will work. http://www.mysql.com/doc/D/a/Date_and_time_functions.html There is a sample how to use TO_DAYS function. Regards Georg - 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
last_insert_id gives 0
I am running MySQL 3.23.41-17 on a stock SuSE 7.3 install. I have a table customers with an auto-incrementing primary key customer_id. If I insert a record: mysql insert into customers(customer_id) values (10); and then ask for the last insert: mysql select last_insert_id(); (which I thought was the correct syntax) I get the response 0. If I ask: mysql select last_insert_id() from customers; I get as many zeros as there are records. If I ask: mysql select last_insert_id(customer_id) from customers; I get the ids for all the records. If I then ask: mysql select last_insert_id(); I get the last record inserted (which was what was supposed to happen, I thought), BUT if I insert another record and repeat: mysql select last_insert_id(); I get the same number as the last time I asked - ie the last but one. However, if I then ask: mysql select last_insert_id(customer_id) from customers; followed by: mysql select last_insert_id(); I get the most recently-added record number. Could some kind person on the list please explain what is happening here, and why the standard syntax does not give the results I expect? Thank you. Kevin - 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
Uninstalling on MacOS X
SMW Mac OS X user looking for someone, anyone, to help him uninstall mySQL-3.23.49 because he has royally screwed up the installation. Chronology of events: 1) installed from binary, process went smoothly 2) several weeks go by without use, forget password to mysql 3) cannot remember password, at all 4) install mysql over itself, doesn't fix problem 5) Delete mysql directory 6) start over by installing from source thinking that¹s the REAL way to do it anyway. 7) More problems, use 'locate' to delete all (I thought) mysql files; don't think I really did delete all of them though. 8) Reinstall from binary, again! 9) now mysql is just broken, ka-putz, destroyed. Server won't start -- nuttin. I was thinking of updating the 'locate' database, and then searching for all files that have 'mysql' in them, again. Is this the best way to remove mysql? Or is there a better way? Also do all of mysql's files have mysql in their name? I have just enough CL knowledge to get myself into trouble, but not enough to pull myself out of it. Thanks, Gilbert Wilson - 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/Perl installation problem
Hi, It doesn't sound to me that you've installed these packages (if they are packages) for the correct version of perl; Possibly they've been dumped in the OS default? (/usr/lib/perl5/5.00503) etc.? I suggest fetching the latest source from CPAN and building it manually. Chris Evans wrote: I am having a problem with testing MySql (max) version 3.23.49a. I downloaded the 3 required Perl packages (Data-Dumper, DBI, and Msql-Mysql-modules), the latest versions, and installed them. The OS is RH Linux 7.2. The installation of both the binary MySql distribution and the Perl packages seemed to go ok, and the MySql installation passed the little initial tests. I am getting the following error doing run-all-tests: [root@scully sql-bench]# run-all-tests install_driver(mysql) failed: Can't locate DBD/mysql.pm in @INC (@INC contains: /usr/lib/perl5/5.6.0/i386-linux /usr/lib/perl5/5.6.0 /usr/lib/perl5/site_perl/5.6.0/i386-linux /usr/lib/perl5/site_perl/5.6.0 /usr/lib/perl5/site_perl .) at (eval 44) line 3. Perhaps the DBD::mysql perl module hasn't been fully installed, or perhaps the capitalisation of 'mysql' isn't right. Available drivers: ADO, ExampleP, Multiplex, Proxy. at /usr/local/mysql-max-3.23.49a-pc-linux-gnu-i686/sql-bench/server-cfg line 238 Any thoughts about what is wrong? Thanks. Chris Evans [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 -- Colin Faber (303) 859-1491 fpsn.net, Inc. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
New to MySQL
Hi I'm new to sql but am familiar with Access \ SQL 7 \ ASP. My ISP offers MySql Support so I need to get up to speed. I can now using Myodbc and access or excel connect to the sample database they put online for me. OF course I have my own tables etc that I want to upload. I get the feeling that I install mysql on my own PC create the database structure etc and simply upload to my ISP after which I can get data into the structure via ASP \ Access \ Excel. Am I on the right lines ? Regards John Berman [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: Problem with Sun Os
Looks like mysql was compiled to use a shared version of zlib. You'll need to get it from: http://www.gzip.org/zlib/ Andrea Soracchi wrote: Hello, When i try to launch my server mysqld I have the follwing result: ld.so.1: ./bin/my_print_defaults: fatal: libz.so.1: open failed: No such file or directory I have installed the following version mysql-3.23.49 (Official MySQL binary) Environment: System: SunOS lan-ba14ub-04 5.8 Generic_108528-13 sun4u sparc SUNW,UltraSPARC-IIi-cEngine Architecture: sun4 Some paths: /usr/bin/perl /usr/ccs/bin/make /usr/local/bin/gcc GCC: Reading specs from /usr/local/lib/gcc-lib/sparc-sun-solaris2.8/2.95.3/specs gcc version 2.95.3 20010315 (release) Compilation info: CC='gcc' CFLAGS='-O3 -fno-omit-frame-pointer' CXX='gcc' CXXFLAGS='-O3 -fno-omit-frame-pointer -felide-con structors -fno-exceptions -fno-rtti' LDFLAGS='' LIBC: -rw-r--r-- 1 root bin 1768120 Feb 16 00:02 /lib/libc.a lrwxrwxrwx 1 root root 11 Feb 26 09:59 /lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 1146392 Feb 16 00:02 /lib/libc.so.1 -rw-r--r-- 1 root bin 1768120 Feb 16 00:02 /usr/lib/libc.a lrwxrwxrwx 1 root root 11 Feb 26 09:59 /usr/lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 1146392 Feb 16 00:02 /usr/lib/libc.so.1 Configure command: ./configure --prefix=/usr/local/mysql '--with-comment=Official MySQL binary' --with-extra-charsets=complex --with-server-suffix= --enable-thread-safe-client --enable-local-infile --e nable-assembler --disable-shared Perl: This is perl, version 5.005_03 built for sun4-solaris Thanks in advanced Andrea Soracchi - 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: How can I kill the slow query automatically?
Without setting up replication, you could use 'UPDATE LOW PRIORITY': http://www.mysql.com/doc/U/P/UPDATE.html If you do setup replication, you can set the replica database to use low priority updates too by using the --low-priority-updates option to mysqld: http://www.mysql.com/doc/C/o/Command-line_options.html You should try both of these before switching to innodb as the performance is not as fast as myisam. -Marc Ken Menzel wrote: Hi, There is no way to kill slow queries autmatically unless you were to write a script (maybe in perl or php?) to do this job. I have a similar problem and am thinking of replicating the database just to avoid this problem. I would then direct certain type of queries to the replicated server. The issue I have is I need selects not to lock the table for update! On FreeBSD this seems also to cause the CPU usage to jump very high. I was thinking of writing some test scripts, but I think the real answer for both of us may be to change to the InnoDB table type. Have you tried this? use ALTER TABLE type=innodb, after you have intalled mysql-max and created the tablespace. I hope this helps, Ken - Original Message - From: °í¼ø¹Î [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, April 03, 2002 5:28 AM Subject: How can I kill the slow query automatically? My DB has many update queries and a few select queries. Sometimes select queries take very long time so update can't proceed. I can kill the select query manually. But I can't check the status all the time. Are there any ways to kill slow query automatically? If you know, please let me know. Any information will be appreciated. - 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/Perl installation problem
I have downloaded and installed DBD-mysql-2.1011, which has allowed the test suite $MYSQL_HOME/sql-bench/run-all-tests to complete successfully. (I had thought that the insert test was not working, but I was simply too impatient--it took about 45 min of clock time to run.) Everything is working now I think, although I am having a little problem with setting up user passwords. Thanks for the help! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Keeping MySQL Databases Heathly
We run a CHECK TABLE nightly on every table (see: http://www.mysql.com/doc/C/H/CHECK_TABLE.html) and I would HIGHLY recommend doing so. If the check table fails, we run a REPAIR TABLE. We also used to do a REPAIR TABLE EXTENDED on weekends to optimize indexes until our db got too big for that to finish in a reasonable amount of time. Alex Pilson wrote: Do MySQL server administrators recommend running a cron script daily to isamchk? Is there any other things that should be run daily to keep MySQL running top-notch? If so, does anyone have any pointers to a script that has already been developed? Thanks. -- - Alex Pilson FlagShip Interactive, Inc. [EMAIL PROTECTED] 404.728.4417 404.642.8225 CELL // Web Design // Lasso Application Development // Filemaker Pro / SQL Development // Sonic Solutions Creator Authoring // Apple DVD Studio Pro Authoring // Macromedia Director/Flash Authoring - - 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: Keeping MySQL Databases Heathly
And here's a script that will do the check/repair for you. It has some
custom logging libs in it that you'll have to remove to make it work
(Admutil and Getpassword), but you'll get the general idea.
Alex Pilson wrote:
Do MySQL server administrators recommend running a cron script daily
to isamchk?
Is there any other things that should be run daily to keep MySQL
running top-notch? If so, does anyone have any pointers to a script
that has already been developed?
Thanks.
--
#!/usr/local/bin/perl
#
# $Id: 60mysql,v 1.30 2002-04-07 09:55:24-04 mprewitt Exp $
# $Source: /usr/local/src/daily/0.1/RCS/60mysql,v $
# $Locker: $
# Copyright 2002 (c) Chelsea Networks, All rights reserved
use strict;
use Admutil;
use File::Basename;
use GetPassword (); # import no exports
use Getopt::Long;
my $opt_extended_check;
GetOptions(
'extended-check!' = \$opt_extended_check,
) || print STDERR Usage: 60mysql [--extended-check]\n;
my $script = new Admutil;
my $script_name = basename( $0 );
$script-StartLog( -logdir=/var/adm/daily/$script_name, -private=1,
-days=14 );
$script-ErrMailTo(mail-oncall);
$script-StartSyslog(-facility=local7);
#
# End standard Admutil header here. Begin regular script.
#
# Look at comments in /usr/local/httpd/conf/wusage.conf.template for how
to
# configure things.
#
use DBI();
use Sys::Hostname;
my $hostname = hostname;
my $dir = /export/DB/mysqldb;
unless ( -d $dir ) {
print $script_name does't run on $hostname because it is not a mysql
server\n;
exit;
}
my $password = GetPassword::GetPassword( -context='mysql', -user='mysql'
) or
$script-Error( -level=CRIT,
-text=Unable to get password for mysql );
my $username = mysql;
#
# Database checks first.
#
my $day = (localtime)[6];
my $check_type = FAST;
# extended check takes too long. run manually.
# if ($day == 0){
# #EXTENDED Do a full key lookup for all keys for each row.
# print running extended check\n;
# $check_type = EXTENDED;
# } else {
# #FAST Only check tables which haven't been closed properly.
# $check_type = FAST;
# }
if ($opt_extended_check){
#EXTENDED Do a full key lookup for all keys for each row.
print running extended check\n;
$check_type = EXTENDED;
}
#
# First, connect to the dataserver.
#
my $dbh = DBI-connect( DBI:mysql:mysql, $username, $password,
{'RaiseError' = 0 } )
||
$script-Error( -level=CRIT,
-text=Unable to connect to mysql [$DBI::errstr] );
my $sth = $dbh-prepare( SHOW DATABASES );
$sth-execute ||
$script-Error( -level=CRIT,
-text=Unable to obtain list of databases
[$DBI::errstr] );
while( my $db = ( $sth-fetchrow_array )[0] ) {
next if ( $db eq binlogs || $db eq RCS || $db eq lost+found
|| $db eq hash || $db eq OUTPUT );
print Checking database $db\n;
my $sth = $dbh-do( use $db ) ||
$script-Error( -level=WARN,
-text=Unable to access $db for checking
[$DBI::errstr] );
my $sth = $dbh-prepare('show tables')
|| $script-Error( -level=WARN,
-text= show tables prepare failed
[$DBI::errstr] );
$sth-execute
|| $script-Error( -level=WARN,
-text= show tables execute failed
[$DBI::errstr] );
my @tables = map $_-[0], @{ $sth-fetchall_arrayref };
if ( @tables ) {
foreach my $table ( @tables ) {
#
# if CHECK fails REPAIR TABLE should be run(look in sub
statement below)
#
my $sql_check = CHECK TABLE $table $check_type;
my $sql_analyze = ANALYZE TABLE $table;
my $sql_repair = REPAIR TABLE $table;
print table $table\n;
if( statement($sql_check) ) {
#
# Analyze it.
#
# statement($sql_analyze);
} else {
#
# Uh-oh...better repair it.
#
statement($sql_repair);
}
}
} else {
if ( $sth-errstr ) {
$script-Error(-level=WARN,
-text= Unable to enumerate tables from
database $db);
}
}
}
#
# Did the slave die?
#
sub check_slave {
if ( my $sth = $dbh-prepare('show slave status')) {
if ( $sth-execute ) {
return ( $sth-fetchrow_array )[0,4,5,6,10];
} else {
return $script-Error(-level=WARN,
-text= Unable to execute show slave
status
[$DBI::errstr] );
}
} else {
return $script-Error(-level=WARN,
-text= Unable to prepare show slave status
[$DBI::errstr] );
}
}
my( $host, $file, $pos1, $status, $error ) = check_slave;
if ( $host $file $status eq No ) {
$dbh-do( SLAVE START ) ||
$script-Error(-level=WARN,
FOREIGN KEY Constraints
From the mysql docs, it looks like you can only use foreign keys if your tables are type InnoDB. Is this correct? Carl - 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
Converting table types
I don't believe my web host has either InnoDB or BDB installed on their system so I ran some tests here: mysql alter table Development_EventType TYPE=INNODB; Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 That's the result I get, but when I do a table dump, the type is still showing MyISAM. 1. Is my syntax correct on this? 2. Is there a way to list table types installed in a system? I do have php installed , there, if that helps. Carl - 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: New to MySQL
You could do that, but if you want to keep it simple, I would download some gui tools like mysql-front (mysqlfront.de) and do all of your development work remotely. Craig At 05:16 PM 4/7/2002, you wrote: Hi I'm new to sql but am familiar with Access \ SQL 7 \ ASP. My ISP offers MySql Support so I need to get up to speed. I can now using Myodbc and access or excel connect to the sample database they put online for me. OF course I have my own tables etc that I want to upload. I get the feeling that I install mysql on my own PC create the database structure etc and simply upload to my ISP after which I can get data into the structure via ASP \ Access \ Excel. Am I on the right lines ? Regards John Berman [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 database privilege problems...
On Sat, 6 Apr 2002 18:54:14 -0800 (PST) Kip Kramer [EMAIL PROTECTED] wrote: Hi All, After working happily for months, my MySQL (v. 3.23.48-1) database has begun displaying the following problem: Whenever I log in as mysql root and try to change privileges for my database as follows: grant insert, select on MyDB_Name to MyExistingUserName; I get the error that the database mysql is read only. Is there an easy way to re-create the mysql permissions database and rebuild the permissions from scratch? I tried renaming the mysql db to mysql.bak and then restrating mysql, but it would not start until I renamed the file as it originally was. The error came from OS side, more exactly is Filesystem ... you have to change the file permission which related to error. Nothing to be done with mysql ;-) -- Let's call it an accidental feature. -- Larry Wall - 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: mysqlbinlog: Duplicate entry '1' for key 1
On Sun, 07 Apr 2002 00:55:59 -0500 Balteo [EMAIL PROTECTED] wrote: Hello, I am trying to restore some data from the binary log but I get the following error: Duplicate entry '1' for key 1 I understand restoring data is a two-stage process: 1. You do a source myfile.sql to restore the data saved with mysqldump 2. You restore the data logged in the binary log using mysqlbinlog I am having the above problem at the second stage and what's more, I don't understand how mysql is supposed to know from what point to start the restore from. The error means that a query from mysqlbinlog file consist a key that already exists on your database. what is your method in order to restoring from binary log ? maybe we can help you to get through :D -- Let's call it an accidental feature. -- Larry Wall - 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: corrupt .FRM files?
On Sat, 6 Apr 2002 21:17:40 +0200 DanceGrooves Info [EMAIL PROTECTED] wrote: Yesterday we discovered a strange problem with our database running on MySQL version 3.23.45 on a Linux OS version 2.2.16 The MySQL database had been running perfectly with a PHP interface on the web since january 30th and records were added normaly. Now suddenly the MySQL server is brought back into the state of March 11th, which means that all data since then are not shown. It seems that there is a problem with the permission to files of DB. there a solution on how we can fix or repair DB - files? Also does anyone have ideas on what might have happened. try to mysqladmin -p flush-tables or mysqladmin -p reload also don't forget to check the file permission from Linux side ... plus the timestamp on the files ... maybe somehow the file is replaced with the old one ;-) -- Let's call it an accidental feature. -- Larry Wall - 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: last_insert_id gives 0
I am running MySQL 3.23.41-17 on a stock SuSE 7.3 install. I have a table customers with an auto-incrementing primary key customer_id. If I insert a record: mysql insert into customers(customer_id) values (10); and then ask for the last insert: mysql select last_insert_id(); (which I thought was the correct syntax) I get the response 0. You're inserting a specific value into the AUTO_INCREMENT field, which doesn't result in the creation of a new automatic sequence number. If you want to insert a record with a specific value in that column, *and* you want the value to be treated like an AUTO_INCREMENT value, so that LAST_INSERT_ID() will return that value, then do this: INSERT INTO customers (customer_id VALUES(LAST_INSERT_ID(10)); If I ask: mysql select last_insert_id() from customers; I get as many zeros as there are records. If I ask: mysql select last_insert_id(customer_id) from customers; I get the ids for all the records. If I then ask: mysql select last_insert_id(); I get the last record inserted (which was what was supposed to happen, I thought), BUT if I insert another record and repeat: mysql select last_insert_id(); I get the same number as the last time I asked - ie the last but one. However, if I then ask: mysql select last_insert_id(customer_id) from customers; followed by: mysql select last_insert_id(); I get the most recently-added record number. Could some kind person on the list please explain what is happening here, and why the standard syntax does not give the results I expect? The standard syntax is for automatically created sequence numbers. You're not automatically creating a sequence number. Thank you. Kevin - 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: last_insert_id gives 0
On Sun, 7 Apr 2002 19:56:47 -0500 Paul DuBois [EMAIL PROTECTED] wrote: I am running MySQL 3.23.41-17 on a stock SuSE 7.3 install. I have a table customers with an auto-incrementing primary key customer_id. If I insert a record: mysql insert into customers(customer_id) values (10); and then ask for the last insert: mysql select last_insert_id(); (which I thought was the correct syntax) I get the response 0. You're inserting a specific value into the AUTO_INCREMENT field, which doesn't result in the creation of a new automatic sequence number. why don't just use insert into customers(customer_id) values (); ??? -- Let's call it an accidental feature. -- Larry Wall - 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: last_insert_id gives 0
On Sun, 7 Apr 2002 19:56:47 -0500 Paul DuBois [EMAIL PROTECTED] wrote: I am running MySQL 3.23.41-17 on a stock SuSE 7.3 install. I have a table customers with an auto-incrementing primary key customer_id. If I insert a record: mysql insert into customers(customer_id) values (10); and then ask for the last insert: mysql select last_insert_id(); (which I thought was the correct syntax) I get the response 0. You're inserting a specific value into the AUTO_INCREMENT field, which doesn't result in the creation of a new automatic sequence number. why don't just use insert into customers(customer_id) values (); ??? Because he apparently wasn't trying to create an automatically generated sequence number, he was trying insert a specific value and have it be treated like an automatically generated sequence number. By the way, it's better to insert NULL in your example than to insert an empty string; your example relies implicitly on a convert-empty-string- to-zero operation and on the behavior that inserting zero is currently that same as inserting NULL. -- Let's call it an accidental feature. -- Larry Wall - 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: Converting table types
Excellent, thanks much, that solved the problem: mysql SHOW VARIABLES LIKE 'have\_%'; +---+---+ | Variable_name | Value | +---+---+ | have_bdb | NO| | have_gemini | NO| | have_innodb | NO| | have_isam | YES | | have_raid | NO| | have_ssl | NO| +---+---+ 6 rows in set (0.00 sec) mysql Looks to pretty clear like it ain't there. Carl On Sun, 7 Apr 2002, Paul DuBois wrote: At 20:23 -0400 4/7/02, Carl Schmidt wrote: I don't believe my web host has either InnoDB or BDB installed on their system so I ran some tests here: mysql alter table Development_EventType TYPE=INNODB; Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 That's the result I get, but when I do a table dump, the type is still showing MyISAM. 1. Is my syntax correct on this? Yes, but MySQL won't complain if you alter a table to a type that isn't available. 2. Is there a way to list table types installed in a system? I do have php installed , there, if that helps. SHOW VARIABLES LIKE 'have\_%' will show many of them. MyISAM doesn't show up in the list; it's always available, from MySQL 3.23 on. Carl - 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
Follow up question to table types
One thing did occur to me though. I was looking at the syntax for actually creating a database on the mysql server. I wanted to make sure that those table types that are installed with mysql do not have to be specified as _available_ to tables in a particular database. In other words, when a database is created from the command line, are there any options that say something like make InnoDB types available for this database or something like that? Kind of a shot in the dark, but I wanted to cover all the bases. Carl - 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
Help with Tables Please
Hi all, I have create the following mysql table, and when I try to add more than 128 rows in the table, I get this error: Duplicate entry '127' for key 1 I have no duplicate entries in there, and I can not figure out what's going on! CREATE TABLE CMSHOWNREQUIREDFIELDS1 ( FieldID tinyint(4) DEFAULT '0' NOT NULL, FieldName tinytext NOT NULL, ShownRegistration tinyint(3) unsigned DEFAULT '0' NOT NULL, Required tinyint(4) DEFAULT '0' NOT NULL, DisplayName tinytext NOT NULL, DisplayNameFarsi tinytext NOT NULL, ModifyFlag tinyint(3) unsigned DEFAULT '1' NOT NULL, ShownProfile tinyint(3) unsigned DEFAULT '0' NOT NULL, ProfileRequired tinyint(4) DEFAULT '0' NOT NULL, ShownUpgradeAccount tinyint(3) unsigned DEFAULT '0' NOT NULL, UpgradeRequired tinyint(4) DEFAULT '0' NOT NULL, ShownMakeDonation tinyint(3) unsigned DEFAULT '0' NOT NULL, DonationRequired tinyint(4) DEFAULT '0' NOT NULL, FTPAllowed tinyint(3) unsigned, Price tinytext, PRIMARY KEY (FieldID) ); Thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help with Tables Please
In the last episode (Apr 07), Soheil Shaghaghi said: Hi all, I have create the following mysql table, and when I try to add more than 128 rows in the table, I get this error: Duplicate entry '127' for key 1 I have no duplicate entries in there, and I can not figure out what's going on! CREATE TABLE CMSHOWNREQUIREDFIELDS1 ( FieldID tinyint(4) DEFAULT '0' NOT NULL, PRIMARY KEY (FieldID) ); This is a FAQ. TINYINT has a range of -127 to 127. Change it to INT. -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help with Tables Please
HINTS: tinyint - Original Message - From: Soheil Shaghaghi [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, April 08, 2002 9:59 AM Subject: Help with Tables Please Hi all, I have create the following mysql table, and when I try to add more than 128 rows in the table, I get this error: Duplicate entry '127' for key 1 I have no duplicate entries in there, and I can not figure out what's going on! CREATE TABLE CMSHOWNREQUIREDFIELDS1 ( FieldID tinyint(4) DEFAULT '0' NOT NULL, FieldName tinytext NOT NULL, ShownRegistration tinyint(3) unsigned DEFAULT '0' NOT NULL, Required tinyint(4) DEFAULT '0' NOT NULL, DisplayName tinytext NOT NULL, DisplayNameFarsi tinytext NOT NULL, ModifyFlag tinyint(3) unsigned DEFAULT '1' NOT NULL, ShownProfile tinyint(3) unsigned DEFAULT '0' NOT NULL, ProfileRequired tinyint(4) DEFAULT '0' NOT NULL, ShownUpgradeAccount tinyint(3) unsigned DEFAULT '0' NOT NULL, UpgradeRequired tinyint(4) DEFAULT '0' NOT NULL, ShownMakeDonation tinyint(3) unsigned DEFAULT '0' NOT NULL, DonationRequired tinyint(4) DEFAULT '0' NOT NULL, FTPAllowed tinyint(3) unsigned, Price tinytext, PRIMARY KEY (FieldID) ); Thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Can AUTO_INCREMENT return from INSERT function into a table ?
mysql desc threads; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | thread_ID | int(11) | | PRI | NULL| auto_increment | | subject | varchar(255) | YES | | NULL|| | author | varchar(30) | YES | | NULL|| | last_by | varchar(30) | YES | | NULL|| | views | int(11) | | | 0 || | date_posted | datetime | YES | | NULL|| +-+--+--+-+-++ 6 rows in set (0.00 sec) $SQL_insert_thread = INSERT into threads ; $SQL_insert_thread .= (subject, author, last_by, views, ; $SQL_insert_thread .= date_posted) values ; $SQL_insert_thread .= (\'some_subject\', \'test_username\', $SQL_insert_thread .= \'test_username\', 1, now()); In the table threads above, I do have a field: thread_ID with auto_increment value. My question is can I obtain the value thread_ID while I do the insert statement just by 1 mySQL statement? If yes, please can somebody give me a sample code? Son Nguyen __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Can AUTO_INCREMENT return from INSERT function into a table ?
Son, At 09:48 PM 4/7/2002, you wrote: mysql desc threads; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | thread_ID | int(11) | | PRI | NULL| auto_increment | | subject | varchar(255) | YES | | NULL|| | author | varchar(30) | YES | | NULL|| | last_by | varchar(30) | YES | | NULL|| | views | int(11) | | | 0 || | date_posted | datetime | YES | | NULL|| +-+--+--+-+-++ 6 rows in set (0.00 sec) $SQL_insert_thread = INSERT into threads ; $SQL_insert_thread .= (subject, author, last_by, views, ; $SQL_insert_thread .= date_posted) values ; $SQL_insert_thread .= (\'some_subject\', \'test_username\', $SQL_insert_thread .= \'test_username\', 1, now()); In the table threads above, I do have a field: thread_ID with auto_increment value. My question is can I obtain the value thread_ID while I do the insert statement just by 1 mySQL statement? If yes, please can somebody give me a sample code? execute the MySQL query: select last_insert_id(); and retrieve the returned value or $last_thread_id = mysql_insert_id(); This function will not work properly if the auto-incrementing column is BigInt, because PHP does not have a BigInt equivalent. Int or smaller works fine. Brent _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.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
SQL HELP!
What the hell this query do!? SELECT student.name FROM student WHERE not exists( SELECT * FROM enrollment WHERE not exists( SELECT * FROM class WHERE class.name = enrollment.classname AND enrollment.studentnumber = student.sid ) ) It returns the name of the students which are enrolled in a class? I need to know what returns in order to translate it into SQL that mysql can parse... Any help would be appreciated! Roberto Ramírez - 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: Help with Tables Please
Thanks for the tip Dan, I changed it, but it still gives me the same error. Any other suggestion? -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED]] Sent: Sunday, April 07, 2002 7:08 PM To: Soheil Shaghaghi Cc: [EMAIL PROTECTED] Subject: Re: Help with Tables Please In the last episode (Apr 07), Soheil Shaghaghi said: Hi all, I have create the following mysql table, and when I try to add more than 128 rows in the table, I get this error: Duplicate entry '127' for key 1 I have no duplicate entries in there, and I can not figure out what's going on! CREATE TABLE CMSHOWNREQUIREDFIELDS1 ( FieldID tinyint(4) DEFAULT '0' NOT NULL, PRIMARY KEY (FieldID) ); This is a FAQ. TINYINT has a range of -127 to 127. Change it to INT. -- Dan Nelson [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mysql++ Difficulties
Hello, I am having trouble getting MySQL++ to work on a Linux system. I couldn't compile the source distribution because the automake command fails saying that there are some required files that aren't present such as the ChangeLog, etc. Then I downloaded a rpm package and it appears to have installed successfully. However, when I try to compile a test program it produces errors like this: In file included from /usr/include/sqlplus.hh:9, from sqltest.cpp:3: /usr/include/defs:5:19: mysql.h: No such file or directory In file included from /usr/include/coldata1.hh:8, from /usr/include/sqlplus.hh:12, from sqltest.cpp:3: Any help is appreciated. Thank you. __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.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
Segmentation Fault when connecting using DBI and option file
Description: When connecting to mysql database using Perl DBI AND specifying a mysql_read_default_file, this causes a Segmentation Fault. However if a mysql_read_default_file is not used then no error occurs. Linux version is 7.2 Redhat and DBI and DBD-mysql were installed from redhat.com and installed without errors. How-To-Repeat: #!/usr/local/bin/perl -w use DBI; my $dbh= DBI-connect(DBI:mysql:buxt;mysql_read_default_file=/home/buxton/main.cnf); main.cnf contains [client] user=test password=testpass Fix: put User and Password in connect statement Submitter-Id: submitter ID Originator: Ray Norrish, [EMAIL PROTECTED] Organization: Portjile Pty Ltd MySQL support: None Synopsis: Connect fails using DBI when a options file is specified (Core Dump) Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-3.23.49a (Official MySQL RPM) Environment: Pentium based server running Redhat 7.2 libraries: perl-Digest-MD5-2.13-1 perl-MIME-Base64-2.12-6 perl-libwww-perl-5.53-3 perl-libxml-perl-0.07-5 groff-perl-1.17.2-7.0.2 perl-CPAN-1.59_54-26.72.3 perl-DBI-1.14-10 perl-DateManip-5.39-5 perl-HTML-Tagset-3.03-3 perl-libnet-1.0703-6 perl-Parse-Yapp-1.04-3 perl-URI-1.12-5 perl-XML-Encoding-1.01-2 perl-XML-Parser-2.30-7 perl-libxml-enno-1.02-5 perl-XML-Twig-2.02-2 perl-DB_File-1.75-26.72.3 mod_perl-1.26-2 perl-DBD-MySQL-1.2215-1 perl-5.6.1-26.72.3 perl-CGI-2.752-26.72.3 perl-HTML-Parser-3.25-2 perl-Storable-0.6.11-6 perl-XML-Grove-0.46alpha-3 perl-XML-Dumper-0.4-5 perl-SGMLSpm-1.03ii-4 perl-NDBM_File-1.75-26.72.3 System: Linux amulet.com.au 2.4.18 #1 SMP Mon Mar 18 13:50:53 EST 2002 i686 unknown Architecture: i686 Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-98) Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='gcc' CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Jan 24 17:01 /lib/libc.so.6 - libc-2.2.4.so -rwxr-xr-x1 root root 1283964 Dec 9 01:14 /lib/libc-2.2.4.so -rw-r--r--1 root root 27314296 Dec 9 01:02 /usr/lib/libc.a -rw-r--r--1 root root 178 Dec 9 01:02 /usr/lib/libc.so Configure command: ./configure --disable-shared --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --with-other-libc=/usr/local/mysql-glibc --without-berkeley-db --without-innodb --enable-assembler --enable-local-infile --with-mysqld-user=mysql --with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/ --with-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' CC=gcc 'CFLAGS=-O6 -fno-omit-frame-pointer -mpentium' 'CXXFLAGS=-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' CXX=gcc - 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: Can AUTO_INCREMENT return from INSERT function into a table ?
--- BD [EMAIL PROTECTED] wrote: Son, At 09:48 PM 4/7/2002, you wrote: mysql desc threads; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | thread_ID | int(11) | | PRI | NULL| auto_increment | | subject | varchar(255) | YES | | NULL| | | author | varchar(30) | YES | | NULL| | | last_by | varchar(30) | YES | | NULL| | | views | int(11) | | | 0 | | | date_posted | datetime | YES | | NULL| | +-+--+--+-+-++ 6 rows in set (0.00 sec) $SQL_insert_thread = INSERT into threads ; $SQL_insert_thread .= (subject, author, last_by, views, ; $SQL_insert_thread .= date_posted) values ; $SQL_insert_thread .= (\'some_subject\', \'test_username\', $SQL_insert_thread .= \'test_username\', 1, now()); In the table threads above, I do have a field: thread_ID with auto_increment value. My question is can I obtain the value thread_ID while I do the insert statement just by 1 mySQL statement? If yes, please can somebody give me a sample code? execute the MySQL query: select last_insert_id(); 1st: select last_insert_id() doesn't work 2nd: It's not the answer I am looking for ... Because of the race condition if two users access the that script at the same time... I will have when I leave the INSERT statement !!! Here is what I mean: think about this situation: USER 1 === INSERT STOP == due to process scheduling USER 2 === INSERT SELECT last_insert_id(); USER 1 === SELECT last_insert_id(); It's the race condition, I am talking about. and retrieve the returned value or $last_thread_id = mysql_insert_id(); This function will not work properly if the auto-incrementing column is BigInt, because PHP does not have a BigInt equivalent. Int or smaller works fine. Brent _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.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 __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.yahoo.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Segmentation Fault when connecting using DBI and option file
--- [EMAIL PROTECTED] wrote: Description: When connecting to mysql database using Perl DBI AND specifying a mysql_read_default_file, this causes a Segmentation Fault. However if a mysql_read_default_file is not used then no error occurs. Linux version is 7.2 Redhat and DBI and DBD-mysql were installed from redhat.com and installed without errors. How-To-Repeat: #!/usr/local/bin/perl -w use DBI; my $dbh= DBI-connect(DBI:mysql:buxt;mysql_read_default_file=/home/buxton/main.cnf); Why don't you try this: --- require 'database.cfg'; # Try to connect to the database or exit with message Can't create access to database $dbh = DBI-connect(DBI:mysql:$DSN, $mySQL_user, $mySQL_password) or die Can't create access to database: $DSN.BR\n; --- * database.cfg $DSN= buxt; $mySQL_user = test; $mySQL_password = testpass; --- main.cnf contains [client] user=test password=testpass Fix: put User and Password in connect statement Submitter-Id:submitter ID Originator: Ray Norrish, [EMAIL PROTECTED] Organization: Portjile Pty Ltd MySQL support: None Synopsis: Connect fails using DBI when a options file is specified (Core Dump) Severity: serious Priority: medium Category: mysql Class:sw-bug Release: mysql-3.23.49a (Official MySQL RPM) Environment: Pentium based server running Redhat 7.2 libraries: perl-Digest-MD5-2.13-1 perl-MIME-Base64-2.12-6 perl-libwww-perl-5.53-3 perl-libxml-perl-0.07-5 groff-perl-1.17.2-7.0.2 perl-CPAN-1.59_54-26.72.3 perl-DBI-1.14-10 perl-DateManip-5.39-5 perl-HTML-Tagset-3.03-3 perl-libnet-1.0703-6 perl-Parse-Yapp-1.04-3 perl-URI-1.12-5 perl-XML-Encoding-1.01-2 perl-XML-Parser-2.30-7 perl-libxml-enno-1.02-5 perl-XML-Twig-2.02-2 perl-DB_File-1.75-26.72.3 mod_perl-1.26-2 perl-DBD-MySQL-1.2215-1 perl-5.6.1-26.72.3 perl-CGI-2.752-26.72.3 perl-HTML-Parser-3.25-2 perl-Storable-0.6.11-6 perl-XML-Grove-0.46alpha-3 perl-XML-Dumper-0.4-5 perl-SGMLSpm-1.03ii-4 perl-NDBM_File-1.75-26.72.3 System: Linux amulet.com.au 2.4.18 #1 SMP Mon Mar 18 13:50:53 EST 2002 i686 unknown Architecture: i686 Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.1 2.96-98) Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='gcc' CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Jan 24 17:01 /lib/libc.so.6 - libc-2.2.4.so -rwxr-xr-x1 root root 1283964 Dec 9 01:14 /lib/libc-2.2.4.so -rw-r--r--1 root root 27314296 Dec 9 01:02 /usr/lib/libc.a -rw-r--r--1 root root 178 Dec 9 01:02 /usr/lib/libc.so Configure command: ./configure --disable-shared --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --with-other-libc=/usr/local/mysql-glibc --without-berkeley-db --without-innodb --enable-assembler --enable-local-infile --with-mysqld-user=mysql --with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/ --with-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' CC=gcc 'CFLAGS=-O6 -fno-omit-frame-pointer -mpentium' 'CXXFLAGS=-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' CXX=gcc - 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 __ Do You Yahoo!? Yahoo! Tax Center - online filing with TurboTax http://taxes.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
È«ÐÂÍƳöasp.net jspÐéÄâÖ÷»ú£¡³ÏÑû´úÀíÒµÎñ,ÀûÈó¿É´ï40%£¡
м¼Êõ£¡£¡Jsp¡¢Asp.net¡¢phpµÄÐéÄâÖ÷»ú¡¢Í¨ÓÃÍøÖ·¡¢´øVDNSµÄ¿ÉÊÓ»¯ÓòÃû¹ÜÀíϵͳ¡£www.todaynic.com ¾Ý¸÷µØÐÂÎÅýÌåµÄ±¨µÀ,CNNICÍƳöµÄÐÂÖÐÎÄÓòÃû¡°Í¨ÓÃÍøÖ·¡±ÒѾºÍ΢ÈíµÄä¯ÀÀÆ÷½øÐÐÁËÀ¦°óÏúÊÛ£¬ÒÔºó´ó¼ÒÖ»ÐèÔÚä¯ÀÀÆ÷ÖÐÖ±½ÓÊäÈëÖÐÎļò¡¢·±ÌåµÄºº×Ö»òÕßÓ¢Îĵ¥´Ê¾Í¿ÉÒÔ·ÃÎʵ½ÄúÏëҪȥµÄµØ·½£¬×¢²áºóÁ¢¿ÌÈ«ÇòÉúЧ£¬Ê¹Ó÷dz£·½±ã¡£Ìص㣺 1¡¢ÎÞÐè¡°www¡±µÄǰ׺£¬Ò²ÎÞÐè¡°.com¡±¡°.net¡±¡°.com.cn¡±µÄºó׺£¬µ«Í¬Ñù¿ÉÒÔÈ«Çò·ÃÎÊ£¬Í¬ÊÇÓÉÓÚËüÔÚ¹úÄÚÍⶼÅäÖÃÁËÏȽøµÄ·þÎñÆ÷£¬ËùÒÔ¿ÉÒÔ±£Ö¤·ÃÎÊʱÔÚ¹úÄÚÍⶼ»áÓзdz£ºÃµÄ·ÃÎÊËٶȺÍЧ¹û¡£ 2¡¢»ñµÃ¹ú¼ÊÉÏÈÏ¿É£¬ÎÞÐè²å¼þ¾Í¿ÉÖ±½Ó·ÃÎÊ£¬ÕæÕýµÄÂÌÉ«²úÆ·¡£ 3¡¢·ûºÏ´«Í³ÔĶÁºÍʹÓõÄÏ°¹ß£¬ÎÞÐè¼ÇÒäÒ»´ó´®µÄ×Öĸ£¬Ö»ÐèÊäÈëºÍÆóÒµÓйصĹ«Ë¾Ãû¡¢²úÆ·Ãû¡¢¹ã¸æÓï»ò800µç»°µÈ£¬¾Í¿ÉÒÔÕÒµ½ÄúµÄÆóÒµÕ¾µã¡£ 3¡¢ÖÇÄÜÍƲ⹦ÄÜ£¬¼´Ê¹Ãû³ÆÊäÈë²»ÍêÈ«£¬Ò²Í¬Ñù¿ÉÒÔ·ÃÎʵ½ÄúµÄÍøÕ¾¡£ 4¡¢ËæÒâÖ¸Ïò£¬Í¨ÓÃÍøÖ·µÄÉî²ãÁ´½Ó¹¦ÄÜ£¬¿ÉÒÔÈÃÄúµÄ¿Í»§Ö±½Ó·ÃÎʵ½ÄúËùÍƼöµÄÄÚÈÝ¡£ 5¡¢×¢²áÓÐÐÐÒµÌصãµÄרÓÐÃû´Ê£¬ÀýÈç¡°ÊÖ»ú¡±¡¢¡°·þ×°¡±¡¢¡°µçÄÔ¡±£¬Á¢¿ÌÓµÓÐÎÞÏÞµÄÔöֵDZÄÜ£¬¼´¿ÉʹÓÃÓÖ¿ÉÊղء£ ÏÖÔÚ£¬ÓÐÒ»¶¨ÒâÒåµÄ¡°Í¨ÓÃÍøÖ·¡±ÒѾ±»ÓйصIJ¿ÃÅ£¬»òÏà¹ØµÄ¹«Ë¾ºÍÓÐÉùÍûµÄ¸öÈËÇÀ×¢ºÍ±£»¤ÆðÀ´£¬Èç¹ûÄúÒѾÒâʶµ½Í¨ÓÃÍøÖ·µÄÖØÒªÐÔºÍËüµÄ¼ÛÖµ£¬ÄÇôÄúÒ²Òª¿ªÊ¼Ðж¯ÁË£¬×¢²áºÍ×Ô¼º¹«Ë¾¡¢ÆóÒµ¡¢¸öÈËÓйصÄͨÓÃÍøÖ·¡£ ³ýͨÓÃÍøÖ·£¬Ê±´´ÍøÂçÒѾÂÊÏÈÍƳöÖ§³ÖJsp Asp.net php µÈÓïÑԵĿռ䣬ȫÃæÖ§³ÖMysql¡¢Access¡¢sql sever 2000¡¡µÄÊý¾Ý¿â¡£¿Õ¼ä¸ßËÙ¡¢Îȶ¨¡¢²»ÏÞÁ÷Á¿¡£ ÓòÃû´øÓÐvdns¿ÉÊÓ»¯ÓòÃû¹ÜÀíϵͳ£¬¿É²úÉú¶à¸ö´Î¼¶ÓòÃû£¬ÊµÏÖÒ»¸öÓòÃû½¨Á¢¶à¸ö¶ÀÁ¢µÄÍøÕ¾¡£ ʱ´´ÍøÂç×÷ΪCNNICµÄÈÏÖ¤»ú¹¹£¬ÍƳöͨÓÃÍøÖ·¡¢ÐéÄâÖ÷»ú¡¢ÉÁËÙÓʾ֡¢ÓòÃû×¢²á¡¢ÖÇÄܽ¨Õ¾¡¢´úÀíÒµÎñÒ»ÌõÁú·þÎñ£¡ÎÒÃÇÌṩµÄ·þÎñרҵ¡¢¿ìËÙ¡¢¿É¿¿£¬Ë«Ö¤ÈÏ¿É£¬¾ø¶Ô¿É¿¿£¬ÊʺϸüרҵµÄÄú£¡ ´úÀíÉêÇëƽ̨£ºhttp://www.pro.net.cn ÓŻݻÏêÇéÇë¼û£ºhttp://www.now.net.cn/CNAddress/present.php »¶ÓÄú·ÃÎÊToday's Network http://www.todaynic.com Ö麣ʱ´´ÍøÂç 0756--2125593 2125583 2125523 2252872 0756--2236575 2125594 2216376 ´«Õ棺 0756--2229669 ÓʼþÖ§³Ö£º[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
select * into outfile
Hi, i am using mysql 4.0.0 version -alpha in windows 2000. i'd like to export the mysql table data to excel format. So,i use the outfile method. mysqlselect * into outfile 'backup.xls' from tablename; When i open 'backup.xls' in microsoft excel,i can view all the data ,but i can't view the field name. Is there any method to export the field name together when i use outfile method? Tq. - 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
