[ANNOUNCE] paper covering MySQL
Hi List, I have just published a performance benchmark paper which includes MySQL in its tests. http://devloop.org.uk/documentation/database-performance/ I hope you will find some of these results interesting, as I did. I am planning on making a new set of tests, including various engines (InnoDB, BerkeleyDB, etc) as well as a more optimized setup. Bear in mind that the scope of the benchmark is limited (not trying to do what TPC does). Regards Antoine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to get access to mysql db with in mysql server
Hello all, I just recieved a mysql server box from another department within my company.. I would like to get the password for the mysql database for mysql.. Is this possible in any other way other then using mysql -u admin -p ? -- Antoine W. Solomon Jr. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
impossible to start mysql
I try to install mysql 3.23 on mandrake 9.0 but I have somme problems first I have this problem /usr/share/rpm-helper/add-service: line 27: [: : integer expression expected a the end of install i saw for mandrake 9.1 a patch so i modified my add-service in rpm- helper but after that a the end of install i receive this message /usr/sbin/mysqld: Shutdown Complete and impossible t start mysql with safe_mysqld error message is : shell-init: could not get current directory: getcwd: cannot access parent directories: No such file or directory pwd: could not get current directory: getcwd: cannot access parent directories: No such file or directory Starting mysqld daemon with databases from /var/lib/mysql 030724 13:44:37 mysqld ended please help me thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie question -- PHP document
En réponse à George Pitcher [EMAIL PROTECTED]: -- Début du message d'origine Nate, The absence of details on the db and table structure prevent testing. However, if you 'View Source' you'll see thet the form isn't being sent to any response page, so there's nothing to handle the query and show the results. 'form action=?form=yes method=post' Hope this helps George in Oxford -Original Message- From: Nate Dawg [mailto:[EMAIL PROTECTED] Sent: 21 July 2003 6:56 am To: [EMAIL PROTECTED] Subject: Newbie question -- PHP document Thanks for the help with my first question. using the USE mysql command did the trick. The book I am reading ( http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?userid =2T67AHHR6Wisbn=0764516507itm=1 ) PHP MySQL for Dummies has a CD Rom with several PHP files. One is called mysql_send.php. Book says The program mysql_send.php has one simple function: to execute queries and display the results. Copy the program from the CD into the directory where you are developing your web application, change the information in lines 11-13 and point your browser at the program. Ok so the mysql_send.php loads great. ( Apache 2 is the web server the file is in the htdocs folder. ) But when I try to run any command I get no results. Other php pages work. Please take a look. The first one just verifies the MySQL database is up. http://warriorscamp.bbs.us/mysql_up.php mysql_send: http://warriorscamp.bbs.us/mysql_send.php If anyone can tell me ( or point me in the correct direction ) why the mysql_send.php does not respond to anything I do I wouldappreciate it. ** ** For Your Protection This Email Was Scanned by Apollo Systems MailServer. For More Services Please Visit http://www.apollosystems.net ** ** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --- Fin du message d'origine - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
java , mysql and php
I have an intranet, a database mysql (i admin with phpmyadmin) I use a program java in order to put data in mysql and i want to know if it is possible to put prog java in php page on my intranet ? I want to start my program on the web ? please help! excuse me for my english antoine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
authentification intranet
bonjour a tous, je viens juste de m'inscrire a votre liste. meme si je ne suis que stagiaire et mon experience pas tres longue j'espere tout de meme pouvoir vous aider mon projet de stage est de mettre en place un intranet mon systeme est compose d'un serveru apache2 dun serveur mysql4.1 de phpmyadmin4.0.1 sur mandrake 9.1 pour me connecter a mon intranet j'utilise les scripts checklogin et secure.php qui s'authentifie aupres de ma base de donnees intranet l'interface de connection se compose d'un login et d'un mot de passe le probleme arrive le pb ne se pose pas pour le login mais pour le password car il m'indique que le mot de passe n'est pas valide j'ai fait le test avec un utilistauer inexistant et il me dit bien utilisateur inexistant j'ai reverifie le mot de passe il est correct quelqu'un aurait -il eu deja ce probleme ? merci pour votre aide -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
authentification intranet
excuse me i send my last e mail in french so i reexplain i have a mandrake 9.1 apache2 mysql 4.1 and phpmyadmin 4.0.1 my problem is : when i give my login and my password in intranet interface, i receive password not exact but password is good in database in mysql i use cheklogin.php and secure.php a another information when i give a bad login server says user doesn't exist so please help me thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
can't connect to mysql3.23.54
I have a problem connection from a remote host this is the error I get ERROR 2013: Lost connection to MySQL server during query I am using redhat 8.0 and mysql3.23.54 please help thank you -- Antoine [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
error connecting to mysql
I have a problem connection from a remote host this is the error I get ERROR 2013: Lost connection to MySQL server during query I am using redhat 8.0 and mysql3.23.54 please help thank you -- Antoine [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
FLUSH TABLES WITH READ LOCK data corruption (the followup)
Hello, Still on this database consistency problem with FLUSH TABLES WITH READ LOCK... I've tried upgrading to MySQL 4.0.5a binary distribution (instead of source), but the problem does not disappear. So I've made a little test. I open too shells. - Shell 1: I launch the MySQL client. Then type : mysql FLUSH TABLES WITH READ LOCK; Query OK, 0 rows affected (1.00 sec) I don't close the client. - Shell 2: I go the MySQL data directory and type: myisamchk -Ts */*.MYI I get: myisamchk: MyISAM file isis_international/delta_flow.MYI myisamchk: warning: 1 clients is using or hasn't closed the table properly MyISAM-table 'isis_international/delta_flow.MYI' is usable but should be fixed I abort after this first error as checking 20 GB of database would be too long. - Shell 1: mysql UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec) mysql CHECK TABLE isis_international.delta_flow; +---+---+--++| + Table | Op| Msg_type | Msg_text | +---+---+--++| + isis_international.delta_flow | check | warning | 1 clients is using or hasn't closed the table properly | | isis_international.delta_flow | check | status | OK | +---+---+--++2 + rows in set (0.06 sec) This confirms that FLUSH TABLES WITH READ LOCK has not closed all tables properly. Some data has been lost and/or corrupted on the way. By the way : the system has SCSI drives and ECC memory. I've never experienced any data corruption outside of the FLUSH TABLES WITH READ LOCK problem. System: Linux *.fr 2.4.19 #3 SMP Fri Aug 9 15:18:06 CEST 2002 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/ccGCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.3 2.96-113) Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=pentiumpro' CXX='gcc' CXXFLAGS='-O2 -mcpu=pentiumpro -felide-constructors' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Jul 18 13:18 /lib/libc.so.6 - libc-2.2.5.so -rwxr-xr-x1 root root 1260480 Apr 15 2002 /lib/libc-2.2.5.so -rw-r--r--1 root root 2310808 Apr 15 2002 /usr/lib/libc.a -rw-r--r--1 root root 178 Apr 15 2002 /usr/lib/libc.so Configure command: ./configure '--prefix=/usr/local/mysql' '--with-comment=Official MySQL-standard binary' '--with-extra-charsets=complex' '--with-server-suffix=-standard' '--enable-thread-safe-client' '--enable-local-infile' '--enable-assembler' '--disable-shared' '--with-client-ldflags=-all-static' '--with-mysqld-ldflags=-all-static' '--with-innodb' 'CFLAGS=-O2 -mcpu=pentiumpro' 'CXXFLAGS=-O2 -mcpu=pentiumpro -felide-constructors' 'CXX=gcc' Is there something I can do to investigate further into this ? Thank you Antoine. - 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
bug / feature : privilege inconsistency
Hi, The new CREATE TEMPORARY privilege is a very good idea. Unfortunately, when an user creates a temporary table, he can't write to it if he doesn't also have the INSERT privilege. It would be much more useful if an user were always allowed to write to its own private temporary tables, regardless of his having the INSERT privilege or not. Otherwise the CREATE TEMPORARY privilege loses much of its interest. I want users to be able to create *and* write to temporary tables (this is mandatory until MySQL has subqueries and the like, because users may have to do complex statistics), but I do not want them to be able to mess with public tables. On a side note : I still have this corruption problem with backups made with FLUSH TABLES WITH READ LOCK. Quite annoying. Regards Antoine. (Filter stuff : sql, query) - 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: [Fwd: possible problems with FLUSH TABLES WITH READ LOCK]
Hello Victoria,
You need to do it with 2 sessions.
According MySQL manual:
If you are using a Veritas filesystem, you can do:
1. From a client (or Perl), execute: FLUSH TABLES WITH READ LOCK.
2. From another shell, execute: mount vxfs snapshot.
3. From the first client, execute: UNLOCK TABLES.
4. Copy files from snapshot.
5. Unmount snapshot.
Thank you for the answer. My backup script is a Perl script and it
keeps the MySQL connection during the snapshot. Here it is :
#!/usr/bin/perl
use strict;
use DBI;
my $dbh = DBI-connect(dbi:mysql:, login, pass, {RaiseError = 1});
print Flushing database with read lock...\n;
$dbh-do(FLUSH TABLES WITH READ LOCK);
print Creating snapshot...\n;
system(lvcreate -L 10G -c 256k -s -n backup /dev/vgdata/data);
print Unlocking database...\n;
$dbh-do(UNLOCK TABLES);
print Closing MySQL connection...\n;
$dbh-disconnect;
print Mounting snapshot...\n;
system(mount /backup);
print Backuping snapshot...\n;
system(cd /backup ; /usr/bin/time tar cvf /dev/st0 *);
print Snapshot statistics... please check it doesn't get full!\n;
system(lvdisplay /dev/vgdata/backup);
print Rewinding and ejecting tape...\n;
system(mt -f /dev/st0 eject);
print Destroying snapshot...\n;
system(umount /backup);
system(lvremove -f /dev/vgdata/backup);
print Backup successfully done!\n\n;
I check the console output in the mail that cron automatically sends,
and everything is correct (no error message). Is there anything wrong in
this script ?
Regards
Antoine.
-
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
[Fwd: possible problems with FLUSH TABLES WITH READ LOCK]
Sorry to insist, but nobody has any clues about this ? I can supply further info if needed. Is the FLUSH TABLES WITH READ LOCK functionality well tested ? Having corrupted backups is annoying, and I can't really take the system down for a 4-hour tape backup... ;( Message original Sujet: possible problems with FLUSH TABLES WITH READ LOCK De: Antoine [EMAIL PROTECTED] Date: Mar, 26 Novembre 2002, 17:27 A: [EMAIL PROTECTED] Hi, I am using FLUSH TABLES WITH READ LOCK to get consistent snapshots of my database without shutting it down. The setup is : - bi-P4 Xeon with Redhat 7.3 - 2.4.19 kernel with properly patched LVM (compiled from source) - MySQL server 4.0.4 (compiled from source) - ext3 filesystem on a 44 GB LVM logical volume named /dev/vgdata/data - the whole database is 20 GB in size - all tables are MYISAM ; some with dynamic records, some fixed, some compressed - some tables - not all - are created with DELAYED_KEY_WRITE=1 to get more speed (30% faster thanks to this) The backup sequence is : - FLUSH TABLES WITH READ LOCK - lvcreate -L 5G -c 256k -s -n backup /dev/vgdata/data (this creates a 5GB snapshot volume named backup from the logical volume containing the database) - UNLOCK TABLES - mount /dev/vgdata/backup /backup -oro,noatime - cd /backup/ ; tar cvf /dev/st0 * Today I've tried restoring a backup on a test partition just to see (you're never too careful). Restoring is OK (of course) but when I run myisamchk -c *.MYI, just to be sure, I get various kinds of errors, on some tables but not all. Common errors include : 1 clients is using or hasn't closed the table properly error: Size of indexfile is: 17404928Should be: 17507328 warning: Size of datafile is: 24922872Should be: 24896378 error: Found 185731 keys of 186676 error: Found key at page 1024 that points to record outside datafile In fact, all kind of errors that you'd expect to find if you copy your files without doing a FLUSH TABLES WITH READ LOCK first. Thus I was wondering if the latter command does work properly. Is it likeky to be due to : - SMP problems ? (it has hyperthreading enabled, BTW, but this shouldn't make any further difference : it just sees 4 logical CPUs instead of 2) - DELAYED_KEY_WRITE ? (but some tables that aren't created as such have problems too, so this shouldn't be the _only_ problem) - specific Linux locking behaviour wrt flushing locking tables ? - Linux LVM bug ? (unlikely in my opinion, it seems heavily used) - other... ? Please note : tables are written to in a continuous way, so it's no surprise many tables get corrupted if the lock is not absolutely consistent and fail-proof ;)) Also, I know the backup volume is large enough (I print the occupied size at the end of the backup procedure). Well, of course, it may just be the backup tape itself that was screwed up, but it doesn't seem very likely, at least in my opinion, otherwise un-tar-ing it should have failed somewhere. What do you think ? Thank you Regards, Antoine. - 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
possible problems with FLUSH TABLES WITH READ LOCK
Hi, I am using FLUSH TABLES WITH READ LOCK to get consistent snapshots of my database without shutting it down. The setup is : - bi-P4 Xeon with Redhat 7.3 - 2.4.19 kernel with properly patched LVM (compiled from source) - MySQL server 4.0.4 (compiled from source) - ext3 filesystem on a 44 GB LVM logical volume named /dev/vgdata/data - the whole database is 20 GB in size - all tables are MYISAM ; some with dynamic records, some fixed, some compressed - some tables - not all - are created with DELAYED_KEY_WRITE=1 to get more speed (30% faster thanks to this) The backup sequence is : - FLUSH TABLES WITH READ LOCK - lvcreate -L 5G -c 256k -s -n backup /dev/vgdata/data (this creates a 5GB snapshot volume named backup from the logical volume containing the database) - UNLOCK TABLES - mount /dev/vgdata/backup /backup -oro,noatime - cd /backup/ ; tar cvf /dev/st0 * Today I've tried restoring a backup on a test partition just to see (you're never too careful). Restoring is OK (of course) but when I run myisamchk -c *.MYI, just to be sure, I get various kinds of errors, on some tables but not all. Common errors include : 1 clients is using or hasn't closed the table properly error: Size of indexfile is: 17404928Should be: 17507328 warning: Size of datafile is: 24922872Should be: 24896378 error: Found 185731 keys of 186676 error: Found key at page 1024 that points to record outside datafile In fact, all kind of errors that you'd expect to find if you copy your files without doing a FLUSH TABLES WITH READ LOCK first. Thus I was wondering if the latter command does work properly. Is it likeky to be due to : - SMP problems ? (it has hyperthreading enabled, BTW, but this shouldn't make any further difference : it just sees 4 logical CPUs instead of 2) - DELAYED_KEY_WRITE ? (but some tables that aren't created as such have problems too, so this shouldn't be the _only_ problem) - specific Linux locking behaviour wrt flushing locking tables ? - Linux LVM bug ? (unlikely in my opinion, it seems heavily used) - other... ? Please note : tables are written to in a continuous way, so it's no surprise many tables get corrupted if the lock is not absolutely consistent and fail-proof ;)) Also, I know the backup volume is large enough (I print the occupied size at the end of the backup procedure). Well, of course, it may just be the backup tape itself that was screwed up, but it doesn't seem very likely, at least in my opinion, otherwise un-tar-ing it should have failed somewhere. What do you think ? Thank you Regards, Antoine. - 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 + PHP : Commands out of sync; You can't run this command now
Hi, I'm having weird errors right now, running MySQL queries from PHP as an Apache module. Sometimes (in a seemingly random way) some queries fail and mysql_error() displays Commands out of sync; You can't run this command now. I would say it seems to happen when different queries are launched at the same time by different threads, or nearly. It is MySQL 4.0.4, compiled from source, together with PHP/4.2.3. They are on two different machines. What does this error message mean ? I've never had it before... (and I've been using this setup for a bit of time). Thanks Antoine. - 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 + PHP : 'Commands out of sync; You can't run this command now'
Just replying to myself... After replacing mysql_pconnect() with mysql_connect(), the problems seems gone so far. So chances are it is a bug on the PHP side. Regards Antoine. Hi, I'm having weird errors right now, running MySQL queries from PHP as an Apache module. Sometimes (in a seemingly random way) some queries fail and mysql_error() displays Commands out of sync; You can't run this command now. I would say it seems to happen when different queries are launched at the same time by different threads, or nearly. - 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: access db port to mysql
I wanted to move the Access Database to Mysql.. I downloaded MysqlFront and that did the trick... Thanks so much On Thu, 2002-11-14 at 00:27, Chris Couture wrote: When you say port are you asking to export the access DB to mysql? If so, Intelligent Converters makes one and you can also do it with EMS MySQL manager. If you wanting to view the mysql data in access; you can do this using myodbc and open it using external data using the ODBC source. -Original Message- From: Antoine [mailto:asolomon15;nyc.rr.com] Sent: Wednesday, November 13, 2002 10:16 PM To: [EMAIL PROTECTED] Subject: access db port to mysql Hello everyone I was wondering if there are any tools that would allow me to port a access database to mysql? - 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
access db port to mysql
Hello everyone I was wondering if there are any tools that would allow me to port a access database to mysql? - 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: Performance Question
I am writing a serverside application in Java which needs to query/search 200 000 rows and update affected records once every 2 minutes. As performance/speed is of importance here - and this Table only contains 5-10 Columns of Integer numbers, I figured that using a HEAP type of table would be the fastest.. (right?) The imaginary system this will run on, is a Dual CPU P4/1,5Ghz or similar with 2Gb RAM. Really, you will have no problem with that kind of workload. Just keep MyISAM (the default) and it's ok. You should of course tune MySQL a little (with 2 GB Ram you can set at least 256 MB for key_buffer_size, which is the most important setting). Keep in mind that MySQL doesn't cache the data by itself, it just uses the filesystem cache, so don't fill all of your RAM with the other apps. Run top from time to time, and check that cached memory is fairly used ; on my MySQL server with 2 GB RAM, the cached memory is currently ~800 MB (but my database is bigger than yours ;-PP). And don't forget to run EXPLAIN on your queries to check they use indexes correctly ;-)) However, given the figures you told us, I don't think you should worry very much - 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
AW: Table partitioning functionality
Hi, You could do the partitioning logic on the application side. For example you can create a new table every month with a different suffix corresponding to the year/month number. Then you add the info about this new table into a master description table, which holds one row per created table, together with creation time and last modification. Your single fact table will actually be a bunch of physical tables whose size you then control quite accurately (if you kow the average data quantity inserted every month). When you want to query some data, you can first select which tables are concerned on the basis of the time interval you want to select data from (it's a very simple SELECT over the master description table). And then query each of these tables in turn (or maybe in parallel if you have tweaked your database so as to spread tables on different physical disks ;-)). If you often do statistical reports which cover only a period in time, this means you can avoid a lot of table scanning just by discarding tables which are outside of the time period you are considering ;-) An alternative is to use MERGE tables which are supposed to handle this stuff automatically, but I don't know if early table selection based on index values is done (if not, it would be slower than the manually-controlled method I described, because it would open every table to only find out that a pair of them would have been enough). I use it at work with multiple tables, multiple databases, two different archiving policies. It works fine and I begin to have a bit of data (10 gigabytes, albeit in several different logical tables). A neat thing is that you can even compress older tables, because they are read-only once you don't insert data anymore (see myisampack in the documentation). This saves some space (the amount of space saved depends on course on the nature of your data, but for a regular logging/statistical application I think you can expect at least a 30% gain). By the way : if your indexes take lots of place and some of them are slowly varying integer indexes - like timestamps - I recommend you specify PACK_KEYS=1 on table creation, this saves space too. (note : I'm talking about MyISAM here, of course) Good luck, Regards Antoine. Hi, Does MySQL offering a similar functionality as Oracle does with table partitioning? Oracle can split a table into several pieces where the pieces are - usually - identified by a range of valus. For example, one may split a table holding data on a daily basis into monthly partitions. Oracle will store the data in the partition identified by month of the new inserted data. As the tablespace used by each partition can be specified this is very usefull to spread the disk IO to several harddisks. The main reason for partitioning a table is to make VERY large tables more performant. Oracle fetches only the partions needed by the SQL statement, this means a full table scan will only process partitions identified by the WHERE clause and not the full range of data. If MySQL does not offer this or a simmilar functionality, how does MySQL perform on very large tables (I'm talking here about 10 Gigs+ of data stored in a single fact table)? Best Regards, Wolfgang - 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
[BUG] Table corrupted by specific sequence (4.0.3-beta)
Description: The following sequence corrupts the table with MySQL 4.0.3-beta. It works fine with 3.23.51. LOCK TABLES t WRITE; DELETE FROM t; OPTIMIZE TABLE t; How to reproduce: - mysql SELECT version(); ++ | version() | ++ | 4.0.3-beta | ++ 1 row in set (0.00 sec) mysql CREATE TABLE t (a INT); Query OK, 0 rows affected (0.00 sec) mysql INSERT t VALUES (1), (2), (3); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql LOCK TABLES t WRITE; Query OK, 0 rows affected (0.00 sec) mysql DELETE FROM t; Query OK, 3 rows affected (0.00 sec) mysql OPTIMIZE TABLE t; ++--+--++ | Table | Op | Msg_type | Msg_text | ++--+--++ | test.t | optimize | warning | Number of rows changed from 0 to 3 | | test.t | optimize | status | OK | ++--+--++ 2 rows in set (0.00 sec) mysql SELECT * FROM t; ERROR 1030: Got error 127 from table handler mysql CHECK TABLE t; ++---+--+--+ | Table | Op| Msg_type | Msg_text | ++---+--+--+ | test.t | check | error| Size of datafile is: 0 Should be: 15 | | test.t | check | error| Corrupt | ++---+--+--+ 2 rows in set (0.00 sec) Environment: System: Linux ***.fr 2.4.19 #3 SMP Fri Aug 9 15:18:06 CEST 2002 i686 unknown Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/ccGCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 2731 (Red Hat Linux 7.3 2.96-110) Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Jul 18 13:18 /lib/libc.so.6 - libc-2.2.5.so -rwxr-xr-x1 root root 1260480 Apr 15 15:44 /lib/libc-2.2.5.so -rw-r--r--1 root root 2310808 Apr 15 16:02 /usr/lib/libc.a -rw-r--r--1 root root 178 Apr 15 15:55 /usr/lib/libc.so Configure command: ./configure --with-innodb --localstatedir=/data/mysql - 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
LVM snapshots and MySQL ?
Hi, I use LVM (Linux Volume Manager) on my box. The MySQL datadir is mounted on a specific ext3-formatted logical volume ; thus I am able to make live snapshots of the database using the LVM snapshot fonctionnality. Internally, LVM snapshots function by syncing the original partition to disk and then backing up all further modified contents onto the snapshot partition. So it is fast (creating the snapshot itself takes less than one second), does not consume much disk (only changes are written to the snapshot partition) and very convenient : one creates the snapshot, takes the time to backup its contents onto a tape (or whatever), and then removes the snapshot. All the while, the database is alive and can be written to by MySQL without disturbing the backup. The question is : although the snapshot syncs the partition, is it sufficient to guarantee that the data is in a consistent state ? That is, if it occurs in the middle of a write by MySQL, will the MYD file be in good state or may it be corrupted ? If consistency is not guaranteed, then I'll just shutdown MySQL before creating the snapshot : this will make the database unavailable for a few seconds - not a really big deal ;-). Does anyone already use this kind of setup ? Any comments about it ? Thank you Antoine. - 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: LVM snapshots and MySQL ?
Hi, According to the documentation, you can use: FLUSH TABLES WITH READ LOCK for that. See http://www.mysql.com/doc/en/FLUSH.html for details Well, that seems perfect. I guess I should have RTFM before ;)) Thanks a lot Antoine. - 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 : 4.0.2-alpha slower than 3.23.51 :-(
Hi,
I have tried to upgrade from MySQL 3.23.51 to 4.0.2-alpha (both source
distribution, same environment, same compiler, same configuration file)
and I was puzzled to see that my application became 5 to 10 times slower.
Most queries in my application (at least the critical part) are write
queries (UPDATEs, INSERTs, REPLACEs). I have isolated a simple example
of a query which has become really slower - but other, bigger or more
complex queries show the same slowdown.
Just do the following under 3.23.51 :
CREATE TABLE route_state_change (
date BIGINTUNSIGNED NOT NULL,
date_end BIGINTUNSIGNED NOT NULL,
duration BIGINTUNSIGNED NOT NULL,
route_k BIGINTUNSIGNED NOT NULL,
stateENUM ('down', 'up') NOT NULL,
KEY date (date),
KEY route(route_k, date_end),
KEY state(state));
Then try the following query (with the mysql client) :
INSERT route_state_change (date, route_k, state) VALUES
(10297598884803,0x84F00F09383D4C11,'down'),
(10297598884803,0xCE7E157EC14416A5,'down'),
(10297598884803,0xD665AB1D45B0A608,'down'),
(10297598884803,0xFD940CAC8CFD55A6,'down'),
(10297598884803,0x3DDB9286093F33C6,'down'),
(10297598884803,0xEC1D5ADC0D3C5DBB,'down'),
(10297598884803,0x51CB867E525127CC,'down'),
(10297598884803,0xCAA6EFB009DC2A02,'down'),
(10297598884803,0xF649CFA95CEDA18C,'down');
(just a plain example from my app)
In 3.23.51, it always takes 0.00 sec (even repeated several times).
After switching to 4.0.2, it always takes 0.08 sec. Which seems huge given
that we have a nearly empty table and the query is rather simple !
Here is the config file /etc/my.cnf :
[client]
port= 3306
socket = /tmp/mysql.sock
[mysqld]
port= 3306
socket = /tmp/mysql.sock
skip-locking
skip-innodb
set-variable= key_buffer=512M
set-variable= max_allowed_packet=1M
set-variable= table_cache=1024
set-variable= sort_buffer=64M
set-variable= join_buffer_size=32M
set-variable= record_buffer=32M
set-variable= thread_cache=8
set-variable= tmp_table_size=256M
set-variable= max_heap_table_size=128M
set-variable= myisam_sort_buffer_size=64M
server-id = 1
[mysql]
no-auto-rehash
The environment is :
System: Linux machine name 2.4.19 #3 SMP Fri Aug 9 15:18:06 CEST 2002 i686
unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/ccGCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs
gcc version 2.96 2731 (Red Hat Linux 7.3 2.96-110)
Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS=''
LIBC:
lrwxrwxrwx1 root root 13 Jul 18 13:18 /lib/libc.so.6 -
libc-2.2.5.so
-rwxr-xr-x1 root root 1260480 Apr 15 15:44 /lib/libc-2.2.5.so
-rw-r--r--1 root root 2310808 Apr 15 16:02 /usr/lib/libc.a
-rw-r--r--1 root root 178 Apr 15 15:55 /usr/lib/libc.so
Configure command: ./configure --localstatedir=/data/mysql --with-innodb
Regards
Antoine.
-
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 : 4.0.2-alpha slower than 3.23.51 :-(
Do you use so huge queries that you really need sort_buffer=64M and record_buffer=32M? Don't forget that record_buffer and sort_buffer are specified per-thread. I don't know exactly which values I need, but when I generate statistical data from the tables it often spans a not-so-small dataset. Also, there is not much concurrency and I am not tight on memory (the box has 2 GB). Would it have something to do with the problem ? Regards Antoine. (keywords : sql,query) - 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: 4.0.2-alpha slower than 3.23.51 - record_buffer
Hi again, Do you use so huge queries that you really need sort_buffer=64M and record_buffer=32M? Don't forget that record_buffer and sort_buffer are specified per-thread. Well, some further input about this as I've just tried at home (MySQL 4.0.1-alpha-nt). You seem to be right : with record_buffer=2M, the query takes 0.01 sec ; with record_buffer=32M, it jumps to 0.16 sec ! Although there is no swapping, and the task manager shows no change in allocated memory. (sort_buffer on the other hand, doesn't do anything) I'll try to confirm this tomorrow at work However, it's a really strange and annoying behaviour. Nowhere in the documentation is stated something like that, and 3.23 was fine about it. Is it a bug ? Thanks, Antoine. - 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 with ENUM ?
Hello,
I have the following kind of table :
CREATE TABLE route (
route_k BIGINT UNSIGNED NOT NULL,
state ENUM('down', 'up') NOT NULL,
[ ... other columns ...]
PRIMARY KEY (route_k),
KEY state (state)
) TYPE = MyISAM;
---
Now here is the time taken by two semantically identical requests :
mysql select avg(route_k) from route where state='down';
+--+
| avg(route_k) |
+--+
| 9269165327512991744. |
+--+
1 row in set (0.08 sec)
mysql select avg(route_k) from route where state!='up';
+--+
| avg(route_k) |
+--+
| 9269165327512991744. |
+--+
1 row in set (0.21 sec)
EXPLAIN :
mysql explain select avg(route_k) from route where state='down';
+---+--+---+---+-+---+---++
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+---+--+---+---+-+---+---++
| route | ref | state | state | 1 | const | 17479 | where used |
+---+--+---+---+-+---+---++
1 row in set (0.00 sec)
mysql explain select avg(route_k) from route where state!='up';
+---+--+---+--+-+--+++
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+---+--+---+--+-+--+++
| route | ALL | NULL | NULL |NULL | NULL | 141883 | where used |
+---+--+---+--+-+--+++
1 row in set (0.00 sec)
With state != 'up', it fails to understand that it can use the index
to do the job and get better performance.
---
And now two other requests (complementary in fact) :
mysql select avg(route_k) from route where state='up';
+--+
| avg(route_k) |
+--+
| 9230562543838910464. |
+--+
1 row in set (0.54 sec)
mysql select avg(route_k) from route where state!='down';
+--+
| avg(route_k) |
+--+
| 9230562543838910464. |
+--+
1 row in set (0.22 sec)
EXPLAIN :
mysql explain select avg(route_k) from route where state='up';
+---+--+---+---+-+---+++
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+---+--+---+---+-+---+++
| route | ref | state | state | 1 | const | 124402 | where used |
+---+--+---+---+-+---+++
1 row in set (0.00 sec)
mysql explain select avg(route_k) from route where state!='down';
+---+--+---+--+-+--+++
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+---+--+---+--+-+--+++
| route | ALL | NULL | NULL |NULL | NULL | 141883 | where used |
+---+--+---+--+-+--+++
1 row in set (0.00 sec)
Here it's another problem : with state='up' it fails to recognize that
'up' represents roughly 80% of all rows, and thus a scan of the table should
be used instead of the index (the documentation states the index is used only
if the estimated number of selected rows is below one third of the total).
Regards
Antoine.
-
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: bug 11 wysql 3.23.49 win (OS Windows XP)
sql From: [EMAIL PROTECTED] To: Antoine Clanché [EMAIL PROTECTED] Subject: Re: bug 11 wysql 3.23.49 win (OS Windows XP) Date: Tue, 28 May 2002 10:32:48 +0200 Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: sql,query If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. Just typing the word MySQL once will be sufficient, for example. You have written the following: Antoine Clanché [EMAIL PROTECTED] 0663645239 _ MSN Photos est le moyen le plus simple de partager et d'imprimer vos photos : http://photos.msn.com/support/worldwide.aspx Antoine Clanché [EMAIL PROTECTED] 0663645239 _ MSN Photos est le moyen le plus simple de partager et d'imprimer vos photos : http://photos.msn.com/support/worldwide.aspx - 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
bug mySQL 3.23.49
sql Antoine Clanché [EMAIL PROTECTED] 0663645239 - 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
48-bit integers, partial integer indexes ?
Hi, I have a suggestion (two, in fact) : - add a 48-bit integer type, which would nicely fill in the gap between INT and BIGINT. Some value types are too large for INT but being forced to use BIGINT is really a waste (for example, millisecond-precise timestamps, or hash values over a collection of one million elements). - add the possibility to index only a prefix of an integer (like can already be done with strings). For example, index only the four high-order bytes of a BIGINT, so that searches remain fast but the index does not take too much place (especially when chances are low that ignoring some low-order bytes introduces many more duplicates). These two features would be really nice when one wants to optimize the space taken by a table. I have some large tables which only consist of five BIGINT columns with some single- and multiple-column indexes. All these columns and indexes would be candidates for both optimizations mentioned above, and it would yield probably 30% to 50% disk space savings, as well as maybe faster queries. I know, I could try to do it with CHAR(6) BINARY, but this is tricky and moreover I wouldn't be able to use arithmetic operations in MySQL (which is really annoying because application languages like Perl do not necessarily accept integers wider than 32 bits). Thank you Antoine. - 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 to retrieve a hierarchical structure
Hi, A simple way is to make a recursive search by starting from the root, and collecting all ID's from each layer. You need only one query per layer, not one per node. It looks like this : - start from the desired directory : SELECT * FROM table WHERE id_parent IN (8) - say it returns children ids 4,5,12, then : SELECT * FROM table WHERE id_parent IN (4,5,12) and so on until the query returns an empty result set, which simply means the search has ended. If you really have a big tree, though, there is another way, less intuitive, which is described here : http://www.mip.berkeley.edu/mip/related/thesaurus/thesaurus.pdf Yours Hi, can someone suggest the best way to perform a query that extract from a category table all the structure, like a filesystem? For example, I've a table of categorie like this: IDnamedescr ID_parent and I've assigned -1 to the root category. I'd like to draw a directory-like structure (like windows explorer), but I don't know how to extract a directory name, and all sub-directory recursively. - 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: table type comparison (poor SELECT performance with HEAP tables ?)
Hi, At first thanks for the answer. I was wondering if my mail was precise enough. Your results could have a lot to do with the fact that HEAP tables used hashed indexes rather than the B-Tree indexes of MyISAM. The set No, I've really used queries that take advantage of the hash indexes : i.e. only full keys (not partial), and equality tests (not range or inequality). I've checked it by asking EXPLAIN SELECT : the indexes are used. The first tests were done with partial keys and results were really worse, by the way, but I figured this out and corrected it. Also, the queries are very simple so there is no chance that queries on MyISAM use a bigger amount of indexed columns than on HEAP. It really seems to me that SELECTs should be faster than INSERTs because everything the SELECT does (finding the right leaf in the index tree / hash), the INSERT also does, and the latter furthermore allocated and appends data. I have come to think that the overhead could come from parsing the request and optimizing it, or maybe sending the data to the client (by the way, it is connected via a Unix socket - networking is disabled in the my.cnf) ? (the test does 5000 simple SELECTs and it lasts for 2 or 3 seconds, whereas for 1 simple INSERTs it only lasts for a fraction of second - all this on HEAP tables). This was done with MySQL 3.23.43 source distribution, on a bi-processor Intel Redhat box. Which kernel version? Did the machine swap during your tests? 2.4.16, the filesystem is ext3 with noatime. The machine didn't seem to swap (top showed no change in the amount of allocated memory). Yours Antoine. - 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
table type comparison (poor SELECT performance with HEAP tables ?)
Hello, Well, for my project at work, I've done some small benchmarking of different table types for relatively small sizes (some tens or hundreds of thousands rows - small enough so that key buffer size and other memory factors should not limit performance) and lots of very simple operations. I have the following observation from it : - INSERTs : HEAP tables are incredibly faster than everything else. Then come MYISAM tables (10 to 50 times slower than HEAP), and then the transactional tables (BDB and InnoDB). However the INSERTs were not contained in a single transaction, so this may improve performance. - SELECTs using indexes : HEAP are the best again, but with quite erratic performance. On some occasions they were extremely fast (100 times faster than other tables), and with other record sets they were only twice faster than other tables (which had, on their side, very stable results) - which means even SELECT COUNT(*) FROM my_table is slower than an INSERT ! I find this quite disturbing, and wonder about the reliability of HEAP tables. I wonder if anyone has had performance problems with HEAP tables (erratic performance given that at some time they can be really light-speed, and at others just *a bit* faster than disk-based tables) ? Another problem for HEAP tables is the slowness of DELETE and even TRUNCATE TABLE (you'd better DROP then re-CREATE them). Also, for disk-based tables, setting the 'noatime' flag was a real benefit for lots of very small queries (some tens of percent faster). Endly, it seems that restarting MySQL has some positive impact on SELECTs (strangely enough, because one could think that it would have to reload the indexes and thus spend some more time), expect on HEAP tables (!) ; INSERTs weren't affected though. Are there explanations for some of these facts ?. For HEAP tables, I was wondering if the client side was the bottleneck, but 'top' showed me that MySQL really took most CPU time. This was done with MySQL 3.23.43 source distribution, on a bi-processor Intel Redhat box. The machine is at the same time a production machine but with small loads, and each test was run several times. I can send the test files (4 small PHP scripts ;-)) to the MySQL developpers if they find it interesting. Regards Antoine. - 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
MERGE Table DELETE FROM component
I'd like to have precisions regarding MERGE TABLE. Let's say we have a MERGE on jan, feb, mar. If I do a DELETE FROM mar. Every thing seems to work correctly, however the manual says that this request have some unexpected results. I don't understand why. Emptying a MERGE component doesn't seem to me as a big problem. Is it really ? thanx, Antoine -- Antoine PICHOT PGP KEY : http://www-eleves.int-evry.fr/~pichot_a/pgp.txt ICQ : 455751 Port : +33 6 08747743 Tel : +33 1 69362278 - 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
pb with compressed tables
Hello, I have a database with tables getting bigger and bigger (log tables). Thus every month I make an archive and then compress it with myisampack. I have a problem though. It seems that without a FLUSH TABLES, the tables don't appear correctly as compressed and can't be read until the end. I've made my code fine and added FLUSH TABLES, but however before doing that I'd tried to REPAIR one of those compressed tables (because I thought it was really corrupted) and now the table ends up completely unreadable by MySQL. I am a bit stuck because it seems I can't find any means of recovering the table after the REPAIR command (however the REPAIR itself said it was successful ;-)). mysql show table status like link_change_2001_10; | link_change_2001_10 | NULL | NULL | NULL | NULL | NULL |NULL | NULL | NULL | NULL | NULL | NULL| NULL| NULL | NULL| # myisamchk -efv /var/mysql/db/link_change_2001_10.MYI myisamchk: error: 127 when opening MyISAM-table '/var/mysql/db/link_change_2001_10.MYI' myisamchk: error: 127 when opening MyISAM-table '/var/mysql/db/link_change_2001_10.MYI' A quick sum up of the chronological process as I don't know if the above is quite clear : - myisampack -s /var/mysql/db/link_change_2001_10 - myisamchk -srq /var/mysql/db/link_change_2001_10 - the table is shown as fixed in SHOW TABLE STATUS, and can't be entirely read - I tried REPAIR TABLE so as to fix what I thought was a problem ; REPAIR returned ok - the table cannot be accessed anymore (NULL values in SHOW TABLE STATUS, myisamchk fails) The MySQL version is 3.23.43 (source distribution), with Berkeley DB and InnoDB enabled (however not used). It runs on a dual P3 1GHz with 1GB RAM and a three-disk harware RAID5 SCSI disk array. Thanks in advance for your help Yours Antoine. - 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
Size of DB
Hello, I'm new to MySQL and I was wondering if there was a command that you can issue to see the current size of a mysql database (in kb or MB)? = 'Toine - 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
Maximum number of columns in a table
Hello, Does any of you know how much columns a MySQl table can handle? Is it fixed or does it depends of some parameters (fields' datatypes, RAM, total table size...)? Thanks DELAUNAY Antoine - 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
Date query problem (hard one)
I've been strugling with a query for some time now and I can't get the desired answer from mysql. The desired answer: a date (bv. 2001-02-08) What is the input: a week number What date do I need: The date of the monday where week = x the idea was: select date(week(10),1); But offcourse this doesn't work.. Anyone any idea's Antoine Adams - 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 date worries
Hi all, Im kind of strugling with a query for some time now and I need extra input: What is the desired answer from mysql: a date. what is the input: a week number What dat do I need: The date of the monday (week starts at sunday) in the weeknumber. something like: select date(week(10),1); But this doesn't work. Anyone any idea's? I'm blocked.. - 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
