Re: InnoDB MySQL-4.0.11a using Linux Mandrake RPMs
Andy Jefferson wrote: a simple question ... I have MySQL installed on my Mandrake Linux system, via the RPM's MySQL-4.0.11a-5.1mdk MySQL-common-4.0.11a-5.1mdk MySQL-client-4.0.11a-5.1mdk Can anyone tell me how a) I check if InnoDB is available with this version ? (in the RPM release notes it seems InnoDB was included in ver 3.23.50-1mdk, so I'm guessing that its still included). Its ok, I found an RPM from Mandrake that includes this MySQL-Max-4.0.11a-5mdk It is available through rpmfind if anyone needs it. -- Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Provider error '80040e37'
Hello, I am having trouble selecting text fields from my database. If I set the column type to varchar(255) it works fine. But when I change the column type to text or blob ( I need more space ) I get the error: Provider error '80040e37' Table does not exist. If I then change it back to varchar again, it works. I have tried googling all over and haven't been able to find anything that seems to relate to my problem, so if this is just a case of reading the proper manual, i apologise. But please let me know which is the proper manual. Oh, I'm using asp and oledb connectivity. Thanks, Robert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
replication
All, I have a situation whereby I have two servers each running mysql in a Master slave replicated setup. Currently if the master server goes down we have to manually failover the database servers, taking a snaphost etc.. Is this still the optimal way to do this, or has the 'Change Master' command, and various other things superceded this method now, and thus is there a better and more automated way to do db failover? Regards Marty p.s. If this is better implemented in Version 4, I would be happy to receive advice on this, as it would form ammunition to push for an upgrade. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.516 / Virus Database: 313 - Release Date: 01/09/2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't install Mysql 4.0.15 on Redhat AS Beta 2
From: James Sullivan To: [EMAIL PROTECTED] Subject:Can't install Mysql 4.0.15 on Redhat AS Beta 2 Description: Can't install on Redhat AS Beta 2 Installing all prepared tables /usr/bin/mysql_install_db: line 1: 2242 Segmentation fault /usr/sbin/mysqld --bootstrap --skip-grant-tables --basedir=/ --datadir=/var/lib/mysql --skip-innodb --skip-bdb Installation of grant tables failed! How-To-Repeat: Install RPM on Redhat AS Beta 2 Fix: how to correct or work around the problem, if known (multiple lines) Submitter-Id: submitter ID Originator:James Sullivan Organization: Thomson MySQL support: [none | licence | email support | extended email support ] Synopsis: synopsis of the problem (one line) Severity: [ critical ] (one line) Priority: [ low | medium | high ] (one line) Category: mysql Class: [ sw-bug | doc-bug | change-request | support ] (one line) Release: mysql-4.0.15-standard (Official MySQL RPM) C compiler:2.95.3 C++ compiler: 2.95.3 Environment: machine, os, target, libraries (multiple lines) System: Linux JPTOKYO1WWW10 2.4.21-1.1931.2.399.entsmp #1 SMP Wed Aug 20 15:51:09 EDT 2003 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=i486 -fno-strength-reduce' CXX='g++' CXXFLAGS='-O2 -mcpu=i486 -fno-strength-reduce -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' ASFLAGS='' LIBC: lrwxr-xr-x1 root root 13 Sep 24 2003 /lib/libc.so.6 - libc-2.3.2.so -rwxr-xr-x1 root root 1550424 Aug 19 06:46 /lib/libc-2.3.2.so -rw-r--r--1 root root 2454810 Aug 19 05:50 /usr/lib/libc.a -rw-r--r--1 root root 204 Aug 19 05:31 /usr/lib/libc.so Configure command: ./configure '--disable-shared' '--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static' '--with-server-suffix=-standard' '--without-embedded-server' '--without-berkeley-db' '--with-innodb' '--without-vio' '--without-openssl' '--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' '--libdir=/usr/lib' '--sysconfdir=/etc' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info' '--includedir=/usr/include' '--mandir=/usr/share/man' '--enable-thread-safe-client' '--with-comment=Official MySQL RPM' 'CC=' 'CFLAGS=-O2 -mcpu=i486 -fno-strength-reduce' 'CXXFLAGS=-O2 -mcpu=i486 -fno-strength-reduce -felide-constructors -fno-exceptions -fno-rtti' 'CXX=' This communication is intended solely for the addressee and is confidential and not for third party unauthorized distribution. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using SQL variables
I tried on MySQL 4.0.12, 4.0.14 and 4.1 and I am getting the same error. Whether these servers were on Windows or on Linux, it does not help. I also initialized the variables but the same error came. I even tried to run the example given in the MySQL documentation with the same results. MySQL thinks ``:='' is a NULL character. Maybe it's a problem with the character set. Thanks Emery - Original Message - From: Pete Harlan [EMAIL PROTECTED] To: Director General: NEFACOMP [EMAIL PROTECTED] Cc: Petr Vileta [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, September 23, 2003 18:49 Subject: Re: Using SQL variables You have to initialize @var to something first, or it's just null. So try set @var := 0; before your query. Also, you don't say which version of MySQL you're using, but I'm using 4.0.14 and I can't say ... as number, * from ..., but have to say ... as number, tableName.* from HTH, --Pete On Tue, Sep 23, 2003 at 04:54:03PM +0300, Director General: NEFACOMP wrote: Can you run this query and tell me if it works on your machine? SELECT (@var := @var + 1) AS Number, * FROM any_table_with_records; As per the manual, this should give something like: +-- NumberFieldOne 1Value 2Value 3Value ... But when I run it the error is :You have an error in your SQL syntax near 'NULL @var + 1) AS Number BLAH BLAH BLAH I have read everything about Using Variables (in the manual) and I apply what it says but it can't run. Even the simple example given by MySQL This is the example MySQL has given in the manual: SELECT @t1:=(@t2:=1)[EMAIL PROTECTED]:=4,@t1,@t2,@t3; When I run this query, the error message is: You have an error in your SQL syntax near '(@t2NULL1)[EMAIL PROTECTED],@t1,@t2,@t3' at line 1 Where do you think these NULL stuffs are coming from? Do I have to change the character set? Thanks Emery - Original Message - From: Petr Vileta [EMAIL PROTECTED] To: Director General: NEFACOMP [EMAIL PROTECTED] Sent: Tuesday, September 23, 2003 01:45 Subject: Re: Using SQL variables Can someone tell me what's wrong with my query? My Query was: SELECT (@num := @num + 1) as RecNum, AnotherField FROM tbl WHERE condition. When I change the := into =, the query returns results with a non changing RecNum. Because = mean some IS EQUIAL to another but := mean SET MYSQL VARIABLE to some value Consult your MySQL manual and look for Using variables :-) Petr Vileta, Czech republic -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using SQL variables
Yes you are right. I run the query in SQLyog and it came very nicely. Thanks for your time and help. Another question is: How do I send multiple queries at the same time the server? I mean, two instructions in the same query. Like SET @var = 0; SELECT (@var := @var + 1) AS Number, AnotherField FROM any_table_with_records; Thanks Emery - Original Message - From: Petr Vileta [EMAIL PROTECTED] To: Director General: NEFACOMP [EMAIL PROTECTED] Sent: Tuesday, September 23, 2003 18:55 Subject: Re: Using SQL variables Can you run this query and tell me if it works on your machine? SELECT (@var := @var + 1) AS Number, * FROM any_table_with_records; As per the manual, this should give something like: +-- NumberFieldOne 1Value 2Value 3Value ... But when I run it the error is :You have an error in your SQL syntax near 'NULL @var + 1) AS Number BLAH BLAH BLAH Yeah :-) You are using MySQL-Front, is it right? I get the same error in mysql-front but the syntax is right, test it using mysql.exe - a native mysql client in DOS mode. If you will use this syntax in some program in VisualBasic, PHP, Perl or other the command still work ok. If you preffer GUI interface for work you can test SQLyog on www.webyog.com Petr Vileta, Czech republic -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Update Date Problem with VB
Hey! So this is my first time here... I am using myODBC 3.51 Server 4.0.14 So I created a table with one columm called GebDat - format Date I connect to this DB using VB6 (SP5) with ADO. OK - So when I add a new data it works fine. But when I will update any one data I always get an error! I ´ve tried to set the string with a format like = FORMAT(Now, -mm-dd), but it also won´t work. Thanks for helping
Sub-selects
According to documentation, in MySQL version 4.0, it is possible to use sub-selects, but i haven't found the correct syntax... Does any one know how to do this? I need someting like this: Select a, (select sum(b) from T2 where T2.a=T1.a) as b from T1
Re: Update Date Problem with VB
Hi, What is the table definition? Have you got a primary index? I work with date fields without problems in this way: Update table set Date_Field = '2003-08-01' Where Date_Field = '2003-01-19' Lourdes. Optik Bacik Scheibbs escribió: Hey! So this is my first time here... I am using myODBC 3.51 Server 4.0.14 So I created a table with one columm called GebDat - format Date I connect to this DB using VB6 (SP5) with ADO. OK - So when I add a new data it works fine. But when I will update any one data I always get an error! I ´ve tried to set the string with a format like = FORMAT(Now, -mm-dd), but it also won´t work. Thanks for helping -- Sus tiendas favoritas, útiles herramientas de compra y grandes ideas para regalos. ¡Compre en línea cómodamente con [EMAIL PROTECTED] http://shopnow.netscape.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb: storage requirements
Roman, the table is probably very fragmented. The command ALTER TABLE pmsystem2.editor_competence_product TYPE=InnoDB; will defragment it. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL - Original Message - From: Roman Neuhauser [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, September 23, 2003 6:38 PM Subject: innodb: storage requirements A simple question regarding storage allocated / reported by MySQL/InnoDB. If BIGINT takes 8 bytes, and a table has exactly four BIGINT columns, why is the reported avg. row length 148 bytes? mysql show table status from pmsystem2 like 'editor_competence_product'\G *** 1. row *** Name: editor_competence_product Type: InnoDB Row_format: Fixed Rows: 152100 Avg_row_length: 148 Data_length: 22626304 Max_data_length: NULL Index_length: 23199744 Data_free: 0 Auto_increment: NULL Create_time: NULL Update_time: NULL Check_time: NULL Create_options: Comment: kompetence techniku; InnoDB free: 6144 kB; (zipid) REFER pmsystem2/zip(id) ON DELETE CASCADE; (brandid) REFER pmsystem2/brand(id) ON DELETE CASCADE; (editorid) REFER pmsystem2/editor(id) ON DELETE CASCADE 1 row in set (0.18 sec) mysql desc pmsystem2.editor_competence_product; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | zipid | bigint(20) unsigned | | PRI | 0 | | | brandid | bigint(20) unsigned | | PRI | 0 | | | productid | bigint(20) unsigned | | PRI | 0 | | | editorid | bigint(20) unsigned | | PRI | 0 | | +---+-+--+-+-+---+ 4 rows in set (0.02 sec) BTW InnoDB deserves much more detailed documentation. As it is, the more interesting the read the less information is provided. :) -- If you cc me or remove the list(s) completely I'll most likely ignore your message.see http://www.eyrie.org./~eagle/faqs/questions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update Date Problem with VB
Optik Bacik Scheibbs [EMAIL PROTECTED] wrote: So this is my first time here... I am using myODBC 3.51 Server 4.0.14 So I created a table with one columm called GebDat - format Date I connect to this DB using VB6 (SP5) with ADO. OK - So when I add a new data it works fine. But when I will update any one data I always get an error! I ?ve tried to set the string with a format like = FORMAT(Now, -mm-dd), but it also won?t work. Show UPDATE statement and error message. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Wildcards not allowed in GRANT commands for table names?
Knepley, Jim [EMAIL PROTECTED] wrote: The docs explicitly say that wildcards are allowed when specifying database names in GRANT commands, but don't say anything about wildcards being allowed in table names. For example, in one large database I have table names that are grouped by function: security.ids_events secuirty.ids_correlations etc. My tests at giving a user rights to 'security.ids_% (even by directly editing the tables_priv table) haven't worked out. Is this genuinely not possible or am I missing something? You can't use wildcards in the table names, only in the database names and in the hostname. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sub-selects
# [EMAIL PROTECTED] / 2003-09-24 02:32:10 -0600: According to documentation, in MySQL version 4.0, it is possible to use sub-selects According to what documentation? -- If you cc me or remove the list(s) completely I'll most likely ignore your message.see http://www.eyrie.org./~eagle/faqs/questions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sub-selects
[EMAIL PROTECTED] wrote: According to documentation, in MySQL version 4.0, it is possible to use sub-selects, but i haven't found the correct syntax... Does any one know how to do this? I need someting like this: Select a, (select sum(b) from T2 where T2.a=T1.a) as b from T1 In MySQL 4.1 you should find subselects ... not 4.0 -- Andy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb: storage requirements
# [EMAIL PROTECTED] / 2003-09-23 21:56:55 +0300: From: Roman Neuhauser [EMAIL PROTECTED] A simple question regarding storage allocated / reported by MySQL/InnoDB. If BIGINT takes 8 bytes, and a table has exactly four BIGINT columns, why is the reported avg. row length 148 bytes? the table is probably very fragmented. The command ALTER TABLE pmsystem2.editor_competence_product TYPE=InnoDB; will defragment it. ok, that *did* help, but makes me wonder about the usefulness of the SHOW TABLE STATUS output: mysql SHOW TABLE STATUS LIKE 'editor_competence_product'\G *** 1. row *** Name: editor_competence_product Type: InnoDB Row_format: Fixed Rows: 225198 ^^ Avg_row_length: 100 Data_length: 22626304 Max_data_length: NULL Index_length: 23199744 Data_free: 0 Auto_increment: NULL Create_time: NULL Update_time: NULL Check_time: NULL Create_options: Comment: kompetence technik#367; (kde co mohou opravovat); InnoDB free: 6144 kB; (zipid) REFER pmsystem2/zip(id) ON DELETE CASCADE; (brandid) REFER pmsystem2/brand(id) ON DELETE CASCADE; (editorid) REFER pmsystem2/editor(id) ON DELETE CASCADE 1 row in set (0.12 sec) mysql ALTER TABLE editor_competence_product TYPE=InnoDB; Query OK, 187654 rows affected (47.34 sec) Records: 187654 Duplicates: 0 Warnings: 0 mysql SHOW TABLE STATUS LIKE 'editor_competence_product'\G *** 1. row *** Name: editor_competence_product Type: InnoDB Row_format: Fixed Rows: 187265 ^^ Avg_row_length: 64 Data_length: 12124160 Max_data_length: NULL Index_length: 20021248 Data_free: 0 Auto_increment: NULL Create_time: NULL Update_time: NULL Check_time: NULL Create_options: Comment: kompetence technik#367; (kde co mohou opravovat); InnoDB free: 57344 kB; (zipid) REFER pmsystem2/zip(id) ON DELETE CASCADE; (brandid) REFER pmsystem2/brand(id) ON DELETE CASCADE; (editorid) REFER pmsystem2/editor(id) ON DELETE CASCADE 1 row in set (0.09 sec) Also, is there a way to *measure* the fragmentation of a table? If there is, how should the info be interpreted? -- If you cc me or remove the list(s) completely I'll most likely ignore your message.see http://www.eyrie.org./~eagle/faqs/questions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Custom/conditional grouping?
David Garamond [EMAIL PROTECTED] wrote: Can I do this with in pure SQL (instead of having to create logic in programming language)? What do you mean Custom/conditional grouping? Please, be more detailed. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Custom/conditional grouping?
That was an amazingly vage question. But first thoughts would be yes and no. You can do grouping, but you can't really do it conditionaly with out code. SQL dosn't support much in the way of logic. So, basicly you do the conditional part in code, then the grouping with SQL. If you want the data grouped depending on values in the data then you will probably need more then one SQL. An SQL result set is eigther grouped/ordered or its not. You can't have it both ways.. Hayides Ess Que what? sorry, my brain just zoned out for a second. - Original Message - From: Victoria Reznichenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 11:40 AM Subject: Re: Custom/conditional grouping? : David Garamond [EMAIL PROTECTED] wrote: : Can I do this with in pure SQL (instead of having to create logic in : programming language)? : : : What do you mean Custom/conditional grouping? Please, be more detailed. : : : -- : For technical support contracts, goto https://order.mysql.com/?ref=ensita : This email is sponsored by Ensita.net http://www.ensita.net/ :__ ___ ___ __ : / |/ /_ __/ __/ __ \/ /Victoria Reznichenko : / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] : /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net :___/ www.mysql.com : : : : : : -- : MySQL General Mailing List : For list archives: http://lists.mysql.com/mysql : To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] : : -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Control Center and InnoDB
Nelson Velasco [EMAIL PROTECTED] wrote: Hi, I am very new to MySQL. Does anyone know if InnoDB works with the tool MySQL Control Center?? Yes. If works, what I have to do? If does not work, how I create the InnoDB tables in MySQL? You can execute CREATE TABLE table_name (...) TYPE=InnoDB statement. You can also select table type InnoDB in the Creating table .. form. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Custom/conditional grouping?
David Garamond [EMAIL PROTECTED] wrote: CREATE TABLE `payments` ( `date` date NOT NULL, `payee` varchar(255), `amount` double ) The 'payments' table records the amount of money that should be paid to each person every month. But the actual cheque is only given when the total accumulated amount has reached $50 or more for that person. I want to list the amount of money that needs to be paid by cheques. Can I do this with in pure SQL (instead of having to create logic in programming language)? SELECT payee, amount FROM payments GROUP BY payee HAVING amount 49.995 Beware using 'double' for currency; it gives roundoff errors. Bruce Feist -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sub-selects
* fbeltran According to documentation, in MySQL version 4.0, it is possible to use sub-selects, but i haven't found the correct syntax... Like others have said, it's from version 4.1: URL: http://www.mysql.com/doc/en/ANSI_diff_Subqueries.html Does any one know how to do this? I need someting like this: Select a, (select sum(b) from T2 where T2.a=T1.a) as b from T1 A simple join and a GROUP clause should do it: SELECT T1.a, SUM(b) AS b FROM T1 LEFT JOIN T2 USING(a) GROUP BY T1.a -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Q: Moving a database
Has anyone ever had to move a database from one machine to another? I tried to just copy all the files from /var/lib/mysql/db1 to another machine into /var/lib/mysql/db2. Although I can see the tables using SHOW TABLES, I get an error 13 trying to access or repair them. Is it possible to move a database this way? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Q: Moving a database
On Wed, 24 Sep 2003 09:14:33 EDT [EMAIL PROTECTED] wrote: Has anyone ever had to move a database from one machine to another? I tried to just copy all the files from /var/lib/mysql/db1 to another machine into /var/lib/mysql/db2. Although I can see the tables using SHOW TABLES, I get an error 13 trying to access or repair them. Is it possible to move a database this way? #perror 13 Error code 13: Permission denied yes, it's possible. but you should not forget about proper rights on these files. --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL newbie: table gone after reboot
I am new to MySQL and have encountered a problem that probably happens to everyone, yet I have searched for a solution online and elsewhere without success. I created a database and table. Then I populated the table. Everything worked perfectly. Then I shutdown MySQL and rebooted my computer. After starting mysqld, I now find my database, but the table and all its contents seem to be gone. 1.) Can I recover this table? 2.) How can I make sure this does not happen again? What did I do wrong? Is there an FAQ?? Thanks, Avram This is what I did... CREATE DATABASE todo; #Create tasks table for To Do list database CREATE TABLE tasks ( taskVARCHAR(60) NOT NULL, date_enteredTIMESTAMP(16) NOT NULL, date_dueDATE NULL, date_completed DATE NULL, priorityENUM(SOMEDAY, NOW, SOON) NOT NULL, description VARCHAR(255) NULL, taskid INT UNSIGNED NOT NULL AUTO_INCREMENT, keywordsVARCHAR(100) NULL, PRIMARY KEY (taskid) ); INSERT INTO tasks ( task, date_due, priority, description, keywords) VALUES(set up database,2003-09-24, SOON, set up the database, database); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb foreign key
Hi, I have two innodb tables produced as show below CREATE TABLE `monogenic` ( `id` smallint(5) unsigned NOT NULL default '0', `exp_design` varchar(50) default NULL, `disease` varchar(50) default NULL, `omim` varchar(20) default NULL, `phenotype_ID` smallint(5) unsigned NOT NULL default '0', `pop` varchar(200) default NULL, `mut_type` varchar(50) default NULL, `mut_loc` varchar(50) default NULL, `gene_ID` smallint(5) unsigned NOT NULL default '0', PRIMARY KEY (`id`,`gene_ID`), KEY `phenotype_ID` (`phenotype_ID`), KEY `gene_ID` (`gene_ID`), CONSTRAINT `0_147` FOREIGN KEY (`id`) REFERENCES `reference` (`id`) ON DELETE CASCADE, ) TYPE=InnoDB CREATE TABLE `gene` ( `id` mediumint(8) unsigned NOT NULL auto_increment, `name` varchar(100) NOT NULL default '', `species` varchar(100) NOT NULL default '', PRIMARY KEY (`id`), KEY `id` (`id`) ) TYPE=InnoDB When trying to add a foreign key constraint as in: alter table monogenic add constraint foreign key (gene_ID) references gene (id) on delete cascade; the error below is produced: ERROR 1005: Can't create table './nugenob/#sql-4585_37.frm' (errno: 150) could anybody help? tia Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Usage Monitoring
Will this show usage per-database, or only for the server overall? - John Might try mytop (search google for it) .. jeremy z wrote it.. it works well for realtime monitoring.. On Tue, 23 Sep 2003, John May wrote: Is there any way to monitor which databases are being used the most heavily on a MySQL server? Thanks for any info! - John -- --- John May : President http://www.pointinspace.com Point In Space Internet Solutions [EMAIL PROTECTED] LPA Corporate Partner / FSA Associate / ACN Member Lasso 5 + 6 / PHP / MySQL / FileMaker Pro Hosting Now Available! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: innodb foreign key
One thing that stands out is the data types are different sizes. http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html -Original Message- From: R.Dobson [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 8:42 AM To: [EMAIL PROTECTED] Subject: innodb foreign key Hi, I have two innodb tables produced as show below CREATE TABLE `monogenic` ( `id` smallint(5) unsigned NOT NULL default '0', `exp_design` varchar(50) default NULL, `disease` varchar(50) default NULL, `omim` varchar(20) default NULL, `phenotype_ID` smallint(5) unsigned NOT NULL default '0', `pop` varchar(200) default NULL, `mut_type` varchar(50) default NULL, `mut_loc` varchar(50) default NULL, `gene_ID` smallint(5) unsigned NOT NULL default '0', PRIMARY KEY (`id`,`gene_ID`), KEY `phenotype_ID` (`phenotype_ID`), KEY `gene_ID` (`gene_ID`), CONSTRAINT `0_147` FOREIGN KEY (`id`) REFERENCES `reference` (`id`) ON DELETE CASCADE, ) TYPE=InnoDB CREATE TABLE `gene` ( `id` mediumint(8) unsigned NOT NULL auto_increment, `name` varchar(100) NOT NULL default '', `species` varchar(100) NOT NULL default '', PRIMARY KEY (`id`), KEY `id` (`id`) ) TYPE=InnoDB When trying to add a foreign key constraint as in: alter table monogenic add constraint foreign key (gene_ID) references gene (id) on delete cascade; the error below is produced: ERROR 1005: Can't create table './nugenob/#sql-4585_37.frm' (errno: 150) could anybody help? tia Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL newbie: table gone after reboot
Are the frm, myd and myi files located in the data directory? What is the data directory? -Original Message- From: Avram Aelony [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 8:43 AM To: [EMAIL PROTECTED] Subject: MySQL newbie: table gone after reboot I am new to MySQL and have encountered a problem that probably happens to everyone, yet I have searched for a solution online and elsewhere without success. I created a database and table. Then I populated the table. Everything worked perfectly. Then I shutdown MySQL and rebooted my computer. After starting mysqld, I now find my database, but the table and all its contents seem to be gone. 1.) Can I recover this table? 2.) How can I make sure this does not happen again? What did I do wrong? Is there an FAQ?? Thanks, Avram This is what I did... CREATE DATABASE todo; #Create tasks table for To Do list database CREATE TABLE tasks ( taskVARCHAR(60) NOT NULL, date_enteredTIMESTAMP(16) NOT NULL, date_dueDATE NULL, date_completed DATE NULL, priorityENUM(SOMEDAY, NOW, SOON) NOT NULL, description VARCHAR(255) NULL, taskid INT UNSIGNED NOT NULL AUTO_INCREMENT, keywordsVARCHAR(100) NULL, PRIMARY KEY (taskid) ); INSERT INTO tasks ( task, date_due, priority, description, keywords) VALUES(set up database,2003-09-24, SOON, set up the database, database); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb foreign key
whoops, yes, thanks, missed that Victor Pendleton wrote: One thing that stands out is the data types are different sizes. http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html -Original Message- From: R.Dobson [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 8:42 AM To: [EMAIL PROTECTED] Subject: innodb foreign key Hi, I have two innodb tables produced as show below CREATE TABLE `monogenic` ( `id` smallint(5) unsigned NOT NULL default '0', `exp_design` varchar(50) default NULL, `disease` varchar(50) default NULL, `omim` varchar(20) default NULL, `phenotype_ID` smallint(5) unsigned NOT NULL default '0', `pop` varchar(200) default NULL, `mut_type` varchar(50) default NULL, `mut_loc` varchar(50) default NULL, `gene_ID` smallint(5) unsigned NOT NULL default '0', PRIMARY KEY (`id`,`gene_ID`), KEY `phenotype_ID` (`phenotype_ID`), KEY `gene_ID` (`gene_ID`), CONSTRAINT `0_147` FOREIGN KEY (`id`) REFERENCES `reference` (`id`) ON DELETE CASCADE, ) TYPE=InnoDB CREATE TABLE `gene` ( `id` mediumint(8) unsigned NOT NULL auto_increment, `name` varchar(100) NOT NULL default '', `species` varchar(100) NOT NULL default '', PRIMARY KEY (`id`), KEY `id` (`id`) ) TYPE=InnoDB When trying to add a foreign key constraint as in: alter table monogenic add constraint foreign key (gene_ID) references gene (id) on delete cascade; the error below is produced: ERROR 1005: Can't create table './nugenob/#sql-4585_37.frm' (errno: 150) could anybody help? tia Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL newbie: table gone after reboot
I am unfamiliar with frm, myd, and myi files, yet perhaps is insightful? -Avram [aa:/usr/local/mysql] % ls -l data total 41032 -rw-rw 1 mysql wheel 6362 Sep 23 22:55 aa.local..err -rw-rw 1 mysql wheel 25088 Sep 8 00:43 ib_arch_log_00 -rw-rw 1 mysql wheel 5242880 Sep 23 22:55 ib_logfile0 -rw-rw 1 mysql wheel 5242880 Sep 8 00:43 ib_logfile1 -rw-rw 1 mysql wheel 10485760 Sep 23 22:54 ibdata1 drwxr-x--- 20 mysql wheel 680 Sep 8 00:37 mysql/ drwx-- 8 mysql wheel 272 Sep 22 23:59 sampdb/ drwxr-x--- 5 mysql wheel 170 Sep 10 17:18 test/ drwx-- 2 mysql wheel68 Sep 10 17:11 testdb1/ drwx-- 5 mysql wheel 170 Sep 23 00:56 todo/ [aa:/usr/local/mysql] % The aa.local..err file contains lengthy text about starts,stops to the server and errors, the other files appear to be binary.. On Wednesday, September 24, 2003, at 06:58 AM, Victor Pendleton wrote: Are the frm, myd and myi files located in the data directory? What is the data directory? -Original Message- From: Avram Aelony [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 8:43 AM To: [EMAIL PROTECTED] Subject: MySQL newbie: table gone after reboot I am new to MySQL and have encountered a problem that probably happens to everyone, yet I have searched for a solution online and elsewhere without success. I created a database and table. Then I populated the table. Everything worked perfectly. Then I shutdown MySQL and rebooted my computer. After starting mysqld, I now find my database, but the table and all its contents seem to be gone. 1.) Can I recover this table? 2.) How can I make sure this does not happen again? What did I do wrong? Is there an FAQ?? Thanks, Avram This is what I did... CREATE DATABASE todo; #Create tasks table for To Do list database CREATE TABLE tasks ( taskVARCHAR(60) NOT NULL, date_enteredTIMESTAMP(16) NOT NULL, date_dueDATE NULL, date_completed DATE NULL, priorityENUM(SOMEDAY, NOW, SOON) NOT NULL, description VARCHAR(255) NULL, taskid INT UNSIGNED NOT NULL AUTO_INCREMENT, keywordsVARCHAR(100) NULL, PRIMARY KEY (taskid) ); INSERT INTO tasks ( task, date_due, priority, description, keywords) VALUES(set up database,2003-09-24, SOON, set up the database, database); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL newbie: table gone after reboot
Can you post the contents from the todo directory? -Original Message- From: Avram Aelony [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 9:14 AM To: Victor Pendleton Cc: [EMAIL PROTECTED] Subject: Re: MySQL newbie: table gone after reboot I am unfamiliar with frm, myd, and myi files, yet perhaps is insightful? -Avram [aa:/usr/local/mysql] % ls -l data total 41032 -rw-rw 1 mysql wheel 6362 Sep 23 22:55 aa.local..err -rw-rw 1 mysql wheel 25088 Sep 8 00:43 ib_arch_log_00 -rw-rw 1 mysql wheel 5242880 Sep 23 22:55 ib_logfile0 -rw-rw 1 mysql wheel 5242880 Sep 8 00:43 ib_logfile1 -rw-rw 1 mysql wheel 10485760 Sep 23 22:54 ibdata1 drwxr-x--- 20 mysql wheel 680 Sep 8 00:37 mysql/ drwx-- 8 mysql wheel 272 Sep 22 23:59 sampdb/ drwxr-x--- 5 mysql wheel 170 Sep 10 17:18 test/ drwx-- 2 mysql wheel68 Sep 10 17:11 testdb1/ drwx-- 5 mysql wheel 170 Sep 23 00:56 todo/ [aa:/usr/local/mysql] % The aa.local..err file contains lengthy text about starts,stops to the server and errors, the other files appear to be binary.. On Wednesday, September 24, 2003, at 06:58 AM, Victor Pendleton wrote: Are the frm, myd and myi files located in the data directory? What is the data directory? -Original Message- From: Avram Aelony [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 8:43 AM To: [EMAIL PROTECTED] Subject: MySQL newbie: table gone after reboot I am new to MySQL and have encountered a problem that probably happens to everyone, yet I have searched for a solution online and elsewhere without success. I created a database and table. Then I populated the table. Everything worked perfectly. Then I shutdown MySQL and rebooted my computer. After starting mysqld, I now find my database, but the table and all its contents seem to be gone. 1.) Can I recover this table? 2.) How can I make sure this does not happen again? What did I do wrong? Is there an FAQ?? Thanks, Avram This is what I did... CREATE DATABASE todo; #Create tasks table for To Do list database CREATE TABLE tasks ( taskVARCHAR(60) NOT NULL, date_enteredTIMESTAMP(16) NOT NULL, date_dueDATE NULL, date_completed DATE NULL, priorityENUM(SOMEDAY, NOW, SOON) NOT NULL, description VARCHAR(255) NULL, taskid INT UNSIGNED NOT NULL AUTO_INCREMENT, keywordsVARCHAR(100) NULL, PRIMARY KEY (taskid) ); INSERT INTO tasks ( task, date_due, priority, description, keywords) VALUES(set up database,2003-09-24, SOON, set up the database, database); -- 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]
How to import Paradox data to MySQL?
Is there a painless way to import Paradox data and database structure to MySQL? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL newbie: table gone after reboot
Okay, I had to log in as root to do so. The data seems to be still extant in tasks.MYD . The other 2 files, tasks.MYI and tasks.frm, are binary. -Avram [aa:local/mysql/data] aelony# ls -l todo total 40 -rw-rw 1 mysql wheel 1220 Sep 23 18:11 tasks.MYD -rw-rw 1 mysql wheel 2048 Sep 23 18:46 tasks.MYI -rw-rw 1 mysql wheel 8819 Sep 23 00:56 tasks.frm On Wednesday, September 24, 2003, at 07:15 AM, Victor Pendleton wrote: Can you post the contents from the todo directory? -Original Message- From: Avram Aelony [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 9:14 AM To: Victor Pendleton Cc: [EMAIL PROTECTED] Subject: Re: MySQL newbie: table gone after reboot I am unfamiliar with frm, myd, and myi files, yet perhaps is insightful? -Avram [aa:/usr/local/mysql] % ls -l data total 41032 -rw-rw 1 mysql wheel 6362 Sep 23 22:55 aa.local..err -rw-rw 1 mysql wheel 25088 Sep 8 00:43 ib_arch_log_00 -rw-rw 1 mysql wheel 5242880 Sep 23 22:55 ib_logfile0 -rw-rw 1 mysql wheel 5242880 Sep 8 00:43 ib_logfile1 -rw-rw 1 mysql wheel 10485760 Sep 23 22:54 ibdata1 drwxr-x--- 20 mysql wheel 680 Sep 8 00:37 mysql/ drwx-- 8 mysql wheel 272 Sep 22 23:59 sampdb/ drwxr-x--- 5 mysql wheel 170 Sep 10 17:18 test/ drwx-- 2 mysql wheel68 Sep 10 17:11 testdb1/ drwx-- 5 mysql wheel 170 Sep 23 00:56 todo/ [aa:/usr/local/mysql] % The aa.local..err file contains lengthy text about starts,stops to the server and errors, the other files appear to be binary.. On Wednesday, September 24, 2003, at 06:58 AM, Victor Pendleton wrote: Are the frm, myd and myi files located in the data directory? What is the data directory? -Original Message- From: Avram Aelony [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 8:43 AM To: [EMAIL PROTECTED] Subject: MySQL newbie: table gone after reboot I am new to MySQL and have encountered a problem that probably happens to everyone, yet I have searched for a solution online and elsewhere without success. I created a database and table. Then I populated the table. Everything worked perfectly. Then I shutdown MySQL and rebooted my computer. After starting mysqld, I now find my database, but the table and all its contents seem to be gone. 1.) Can I recover this table? 2.) How can I make sure this does not happen again? What did I do wrong? Is there an FAQ?? Thanks, Avram This is what I did... CREATE DATABASE todo; #Create tasks table for To Do list database CREATE TABLE tasks ( taskVARCHAR(60) NOT NULL, date_enteredTIMESTAMP(16) NOT NULL, date_dueDATE NULL, date_completed DATE NULL, priorityENUM(SOMEDAY, NOW, SOON) NOT NULL, description VARCHAR(255) NULL, taskid INT UNSIGNED NOT NULL AUTO_INCREMENT, keywordsVARCHAR(100) NULL, PRIMARY KEY (taskid) ); INSERT INTO tasks ( task, date_due, priority, description, keywords) VALUES(set up database,2003-09-24, SOON, set up the database, database); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to import Paradox data to MySQL?
On Wed, 24 Sep 2003 18:19:30 +0400 [EMAIL PROTECTED] wrote: Is there a painless way to import Paradox data and database structure to MySQL? try to follow this advice: http://dbforums.com/arch/51/2003/5/766832 --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to import Paradox data to MySQL?
paradox for dos or paradox for windows? If you can write object pal then you can easily write an export to text I love Paradox, use to be an PAL developer when I was little. and an Object PAL contractor later, ah, the good old days :-) - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 3:19 PM Subject: How to import Paradox data to MySQL? Is there a painless way to import Paradox data and database structure to MySQL? -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb: storage requirements
Roman, the row count in SHOW TABLE STATUS is only an estimate based on 8 dives into the index tree. You had a typical symptom of a fragmented table: space usage much bigger than you would expect. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Roman Neuhauser [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 1:30 PM Subject: Re: innodb: storage requirements # [EMAIL PROTECTED] / 2003-09-23 21:56:55 +0300: From: Roman Neuhauser [EMAIL PROTECTED] A simple question regarding storage allocated / reported by MySQL/InnoDB. If BIGINT takes 8 bytes, and a table has exactly four BIGINT columns, why is the reported avg. row length 148 bytes? the table is probably very fragmented. The command ALTER TABLE pmsystem2.editor_competence_product TYPE=InnoDB; will defragment it. ok, that *did* help, but makes me wonder about the usefulness of the SHOW TABLE STATUS output: mysql SHOW TABLE STATUS LIKE 'editor_competence_product'\G *** 1. row *** Name: editor_competence_product Type: InnoDB Row_format: Fixed Rows: 225198 ^^ Avg_row_length: 100 Data_length: 22626304 Max_data_length: NULL Index_length: 23199744 Data_free: 0 Auto_increment: NULL Create_time: NULL Update_time: NULL Check_time: NULL Create_options: Comment: kompetence technik#367; (kde co mohou opravovat); InnoDB free: 6144 kB; (zipid) REFER pmsystem2/zip(id) ON DELETE CASCADE; (brandid) REFER pmsystem2/brand(id) ON DELETE CASCADE; (editorid) REFER pmsystem2/editor(id) ON DELETE CASCADE 1 row in set (0.12 sec) mysql ALTER TABLE editor_competence_product TYPE=InnoDB; Query OK, 187654 rows affected (47.34 sec) Records: 187654 Duplicates: 0 Warnings: 0 mysql SHOW TABLE STATUS LIKE 'editor_competence_product'\G *** 1. row *** Name: editor_competence_product Type: InnoDB Row_format: Fixed Rows: 187265 ^^ Avg_row_length: 64 Data_length: 12124160 Max_data_length: NULL Index_length: 20021248 Data_free: 0 Auto_increment: NULL Create_time: NULL Update_time: NULL Check_time: NULL Create_options: Comment: kompetence technik#367; (kde co mohou opravovat); InnoDB free: 57344 kB; (zipid) REFER pmsystem2/zip(id) ON DELETE CASCADE; (brandid) REFER pmsystem2/brand(id) ON DELETE CASCADE; (editorid) REFER pmsystem2/editor(id) ON DELETE CASCADE 1 row in set (0.09 sec) Also, is there a way to *measure* the fragmentation of a table? If there is, how should the info be interpreted? -- If you cc me or remove the list(s) completely I'll most likely ignore your message.see http://www.eyrie.org./~eagle/faqs/questions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
pseudo-trigger to update multiple tables?
Hello, I am just wondering how I can make a trigger to update multiple tables efficiently. (mysql db). RIght now I am thinking of making copys of the tables involved and update the copies to see the result. But it could be time consuming if the tables are huge. Any one has good ideas about it? Thank you for help ginger - Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software
RE: MySQL newbie: table gone after reboot
Does the user who you start the MySQL Server up with own the directory and the files? If the files are there you can try issuing a `show tables from todo`. If the tables appear try running `check table tasks` and let us know what the output is. -Original Message- From: Avram Aelony [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 9:37 AM To: Victor Pendleton Cc: [EMAIL PROTECTED] Subject: Re: MySQL newbie: table gone after reboot Okay, I had to log in as root to do so. The data seems to be still extant in tasks.MYD . The other 2 files, tasks.MYI and tasks.frm, are binary. -Avram [aa:local/mysql/data] aelony# ls -l todo total 40 -rw-rw 1 mysql wheel 1220 Sep 23 18:11 tasks.MYD -rw-rw 1 mysql wheel 2048 Sep 23 18:46 tasks.MYI -rw-rw 1 mysql wheel 8819 Sep 23 00:56 tasks.frm On Wednesday, September 24, 2003, at 07:15 AM, Victor Pendleton wrote: Can you post the contents from the todo directory? -Original Message- From: Avram Aelony [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 9:14 AM To: Victor Pendleton Cc: [EMAIL PROTECTED] Subject: Re: MySQL newbie: table gone after reboot I am unfamiliar with frm, myd, and myi files, yet perhaps is insightful? -Avram [aa:/usr/local/mysql] % ls -l data total 41032 -rw-rw 1 mysql wheel 6362 Sep 23 22:55 aa.local..err -rw-rw 1 mysql wheel 25088 Sep 8 00:43 ib_arch_log_00 -rw-rw 1 mysql wheel 5242880 Sep 23 22:55 ib_logfile0 -rw-rw 1 mysql wheel 5242880 Sep 8 00:43 ib_logfile1 -rw-rw 1 mysql wheel 10485760 Sep 23 22:54 ibdata1 drwxr-x--- 20 mysql wheel 680 Sep 8 00:37 mysql/ drwx-- 8 mysql wheel 272 Sep 22 23:59 sampdb/ drwxr-x--- 5 mysql wheel 170 Sep 10 17:18 test/ drwx-- 2 mysql wheel68 Sep 10 17:11 testdb1/ drwx-- 5 mysql wheel 170 Sep 23 00:56 todo/ [aa:/usr/local/mysql] % The aa.local..err file contains lengthy text about starts,stops to the server and errors, the other files appear to be binary.. On Wednesday, September 24, 2003, at 06:58 AM, Victor Pendleton wrote: Are the frm, myd and myi files located in the data directory? What is the data directory? -Original Message- From: Avram Aelony [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 8:43 AM To: [EMAIL PROTECTED] Subject: MySQL newbie: table gone after reboot I am new to MySQL and have encountered a problem that probably happens to everyone, yet I have searched for a solution online and elsewhere without success. I created a database and table. Then I populated the table. Everything worked perfectly. Then I shutdown MySQL and rebooted my computer. After starting mysqld, I now find my database, but the table and all its contents seem to be gone. 1.) Can I recover this table? 2.) How can I make sure this does not happen again? What did I do wrong? Is there an FAQ?? Thanks, Avram This is what I did... CREATE DATABASE todo; #Create tasks table for To Do list database CREATE TABLE tasks ( taskVARCHAR(60) NOT NULL, date_enteredTIMESTAMP(16) NOT NULL, date_dueDATE NULL, date_completed DATE NULL, priorityENUM(SOMEDAY, NOW, SOON) NOT NULL, description VARCHAR(255) NULL, taskid INT UNSIGNED NOT NULL AUTO_INCREMENT, keywordsVARCHAR(100) NULL, PRIMARY KEY (taskid) ); INSERT INTO tasks ( task, date_due, priority, description, keywords) VALUES(set up database,2003-09-24, SOON, set up the database, database); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: pseudo-trigger to update multiple tables?
Are you using innodb table types? Is this the type of updating you are in need of? -Original Message- From: ginger cheng [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 10:04 AM To: [EMAIL PROTECTED] Subject: pseudo-trigger to update multiple tables? Hello, I am just wondering how I can make a trigger to update multiple tables efficiently. (mysql db). RIght now I am thinking of making copys of the tables involved and update the copies to see the result. But it could be time consuming if the tables are huge. Any one has good ideas about it? Thank you for help ginger - Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DataDir question
I am using a portable drive with a few tables on it that I copied from a MySQL server. My intent is to be able to transport the portable drive home (with the large DB) and plug it into my Windows server to continue working. To do this, I need to change the datadir to the portable drive (in my case it's the L: drive). I've tried putting an entry in my c:\winnt\my.ini file under the [mysqld] line as follows: [mysqld] datadir=L:/dbdir ...but it refuses to startup. I have to admit that am more comfortable in Linux, so I could be missing the error or logfile. The only thing in the Event Log is: The MySql service terminated unexpectedly. It has done this 1 time(s). The following corrective action will be taken in 0 milliseconds: No action. I've also removed the my.ini file and specified --datadir L:/dbdir in the services window and that seems to start the server ok, but I can't see the database tables. Anyone know what I am doning wrong? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Lost Data with delayed insert / mysqlhotcopy
Description: mysqlhotcopy (but possible any other software) can cause delayed handlers to terminate with Delayed insert thread couldn't get requested lock for I havent worked out the exact conditions under wich mysqlhotcopy fails, but it seems that if mysqlhotcopy tries to get a lock (waiting for a query to finish) and you start another delayed insert (maybe involving several tables?), then it will fail the server The description below, extracts the fault without mysqlhotcopy and failed always on my system How-To-Repeat: you need two mysql client connection: (assuming a table tb with a column a int) 1 LOCK TABLES tb READ; 2 insert delayed into tb values(1); 1 flush tables; # the delayed handler dies here, the insert is not in the table 1 unlock tables Fix: Submitter-Id: [EMAIL PROTECTED] Originator: Organization: MySQL support: none Synopsis: data loss with delayed insert Severity: serious Priority: low Category: mysql Class: Release: mysql-4.0.15 (FreeBSD port: mysql-server-4.0.15) Server: /usr/local/bin/mysqladmin Ver 8.40 Distrib 4.0.13, for portbld-freebsd4.8 on i386 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.15 Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 8 days 23 hours 34 min 32 sec Threads: 6 Questions: 138727 Slow queries: 1 Opens: 209 Flush tables: 4 Open tables: 15 Queries per second avg: 0.179 C compiler:2.95.4 C++ compiler: 2.95.4 Environment: System: FreeBSD dionysus.london.hybyte.com 4.8-STABLE FreeBSD 4.8-STABLE #0: Tue Aug 12 12:52:01 BST 2003 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/DIONYSUS i386 Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Using builtin specs. gcc version 2.95.4 20020320 [FreeBSD] Compilation info: CC='cc' CFLAGS='-O1 -pipe -mcpu=i686 -D__USE_UNIX98 -D_REENTRANT -D_THREAD_SAFE -I/usr/local/include/pthread/linuxthreads -O3 -fno-omit-frame-pointer' CXX='cc' CXXFLAGS='-O1 -pipe -mcpu=i686 -D__USE_UNIX98 -D_REENTRANT -D_THREAD_SAFE -I/usr/local/include/pthread/linuxthreads -O3 -fno-omit-frame-pointer -felide-constructors -fno-rtti -fno-exceptions' LDFLAGS='' ASFLAGS='' LIBC: -r--r--r-- 1 root wheel 1224554 Aug 12 12:52 /usr/lib/libc.a lrwxr-xr-x 1 root wheel 9 Aug 12 12:52 /usr/lib/libc.so - libc.so.4 -r--r--r-- 1 root wheel 578880 Aug 12 12:52 /usr/lib/libc.so.4 Configure command: ./configure '--localstatedir=/var/db/mysql' '--without-debug' '--without-readline' '--without-bench' '--without-extra-tools' '--with-libwrap' '--with-mysqlfs' '--with-vio' '--with-low-memory' '--with-comment=FreeBSD port: mysql-server-4.0.15' '--enable-thread-safe-client' '--enable-assembler' '--with-berkeley-db' '--with-openssl' '--with-named-thread-libs=-DHAVE_GLIBC2_STYLE_GETHOSTBYNAME_R -D_THREAD_SAFE -I/usr/local/include/pthread/linuxthreads -L/usr/local/lib -llthread -llgcc_r' '--prefix=/usr/local' '--build=i386-portbld-freebsd4.8' 'CFLAGS=-O1 -pipe -mcpu=i686 -D__USE_UNIX98 -D_REENTRANT -D_THREAD_SAFE -I/usr/local/include/pthread/linuxthreads -O3 -fno-omit-frame-pointer' 'CXX=cc' 'build_alias=i386-portbld-freebsd4.8' 'CC=cc' 'CXXFLAGS=-O1 -pipe -mcpu=i686 -D__USE_UNIX98 -D_REENTRANT -D_THREAD_SAFE -I/usr/local/include/pthread/linuxthreads -O3 -fno-omit-frame-pointer -felide-constructors -fno-rtti -fno-exceptions' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Custom/conditional grouping?
Victoria Reznichenko wrote: David Garamond [EMAIL PROTECTED] wrote: Can I do this with in pure SQL (instead of having to create logic in programming language)? What do you mean Custom/conditional grouping? Please, be more detailed. Sorry, please refer to my other post. The first email was accidentally sent. -- dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Custom/conditional grouping?
Haydies wrote: That was an amazingly vage question. Yup, sorry for that. Accidentally sent :) -- dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Custom/conditional grouping?
Bruce Feist wrote: David Garamond [EMAIL PROTECTED] wrote: CREATE TABLE `payments` ( `date` date NOT NULL, `payee` varchar(255), `amount` double ) The 'payments' table records the amount of money that should be paid to each person every month. But the actual cheque is only given when the total accumulated amount has reached $50 or more for that person. I want to list the amount of money that needs to be paid by cheques. Can I do this with in pure SQL (instead of having to create logic in programming language)? SELECT payee, amount FROM payments GROUP BY payee HAVING amount 49.995 Not exactly what I want. Actually I need to do a SUM of 'amount' per payee and per 'period', where period is one or more months. If in one month a person has not collected = $50, then it will be accumulated and only paid when the amount has been sufficient. Another example: Suppose 'bruce' makes $30 in July, $80 in August, and $40 in Sep. Normally I would pay bruce's July earning in Aug. But seeing that bruce hasn't earned $50 in July, I hold his earning until Sep. In Sep I pay him Jul Aug's earnings ($30+$80 = $110). Note that I will not be paying bruce's Sep earning in Oct, since in Sep bruce only earns $40. I will need to see whether in Oct bruce makes = $10, in which case I'll need to pay him in Nov. So 'period' can differ depending on payee and amount. I hope I'm explaining it more clearly. Beware using 'double' for currency; it gives roundoff errors. Thanks for the tip. I do use DECIMAL for currencies/money. Sorry for the inappropriate type in the example. -- dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DataDir question
Have you tried using the blackslash? `\` instead of the forward slash `/`? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 10:34 AM To: [EMAIL PROTECTED] Subject: DataDir question I am using a portable drive with a few tables on it that I copied from a MySQL server. My intent is to be able to transport the portable drive home (with the large DB) and plug it into my Windows server to continue working. To do this, I need to change the datadir to the portable drive (in my case it's the L: drive). I've tried putting an entry in my c:\winnt\my.ini file under the [mysqld] line as follows: [mysqld] datadir=L:/dbdir ...but it refuses to startup. I have to admit that am more comfortable in Linux, so I could be missing the error or logfile. The only thing in the Event Log is: The MySql service terminated unexpectedly. It has done this 1 time(s). The following corrective action will be taken in 0 milliseconds: No action. I've also removed the my.ini file and specified --datadir L:/dbdir in the services window and that seems to start the server ok, but I can't see the database tables. Anyone know what I am doning wrong? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: pseudo-trigger to update multiple tables?
Have you enabled the update on cascade features for the innodb tables? -Original Message- From: ginger cheng [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 10:26 AM To: Victor Pendleton Subject: RE: pseudo-trigger to update multiple tables? Yes, I am using InnoDB tables. Victor Pendleton [EMAIL PROTECTED] wrote: Are you using innodb table types? Is this the type of updating you are in need of? -Original Message- From: ginger cheng [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 10:04 AM To: [EMAIL PROTECTED] Subject: pseudo-trigger to update multiple tables? Hello, I am just wondering how I can make a trigger to update multiple tables efficiently. (mysql db). RIght now I am thinking of making copys of the tables involved and update the copies to see the result. But it could be time consuming if the tables are huge. Any one has good ideas about it? Thank you for help ginger - Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software _ Do you Yahoo!? Yahoo! http://us.rd.yahoo.com/evt=10469/*http://sitebuilder.yahoo.com SiteBuilder - Free, easy-to-use web site design software
last_insert_id()
Hi, Is it possible to obtain the last_insert_id() for a particular column in a particular table? eg, say i wanted to obtain the last insert id of a column called id in table reference, something along the lines of: last_insert_id(reference.id) The reason I ask is because I want to initially insert values into two tables and then insert values into a third using the last_insert_id() from the first two tables. Obviously, the last_insert_id from the first insert is replaced by the last_insert_id from the second insert. This is all done in a perl script and I could store the first last_insert_id in a variable but I thought their might be a more elegant way round it? tia Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
2,000,000 rows in FULLTEXT
* MATCH ... AGAINST() in natural language mode now treats words that are present in more than 2,000,000 rows as stopwords. Arg! Is this really true only for natural mode? It seems to be doing it for boolean mode too. Did this change having anything to do with index creation? So I could downgrade back a version and have it still work OK? Sincerely, Steven Roussey http://Network54.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DataDir question
In a message dated 9/24/03 12:34:34 PM Eastern Daylight Time, [EMAIL PROTECTED] writes: Have you tried using the blackslash? `\` instead of the forward slash `/`? Same results. The manual states that even on Windows that the '/' should be used as the '\' is MySQL's escape char. Optionally, you can '\\' but all three methods produce the same result. :-/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Usage Monitoring
On Wed, Sep 24, 2003 at 09:54:12AM -0400, John May wrote: Will this show usage per-database, or only for the server overall? Overall. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 10 days, processed 388,648,665 queries (420/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database quotas?
On Wed, Sep 24, 2003 at 03:18:07PM +1000, [EMAIL PROTECTED] wrote: Yeah but when they make a nwe table, it will be owned by root.root wont it? First, don't run MySQL as root. Second, yes. It will be owned by whoever creates it. But a periodic cron job to chorwn them appropriately would be a reasonable solution, I suspect. Jeremy -- Jereme D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 10 days, processed 388,649,043 queries (420/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Innodb: CREATE INDEX
Hi, I'm working in MySQL with innodb tables, in Linux (Red Hat 9). I'm creating indexes in a table with 16 million rows (it's a fact table), and it takes a lot of time (2/3/4 hours), because my system is always swapping in/out (i think). At the start of the creating, it's fast (because my buffer pool is free), but when the buffer pool reaches at the end, the system slow down, and the swap in/out begins :/ I have 512MB of RAM. My configuration is: buffer pool size 256MB innobd_log_file 128MB innodb_log_buffer_size 8MB I tried innodb_flush_log_at_trx_commit with 1/0, innodb_flush_method with fdatasync/O_SYNC, innodb_log_buffer_size to 32MB, but nothing happens! I don't know if i have to re-build my tables... but i think that it would take a lot of time too (16 million rows...). What can i do? Thanks Alexis P.S.:i have only innodb tables on my DB. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Reading a table without knowing column names
Hi I would like to execute a select on a table for which i don't know the column names. IE I would like a command like select column(1) from table where ... Sure, if I ask, it is because I cannot use the desc command. I have another solution which causes again a problem : if I could execute a function on the result of desc table I explain myself : I access a mysql base through a fixed php script (which i can't modif), which takes for input a request in an html form, executes it and gives me back the numeric value of the first row, first column So when i know the field name and to retreive what I want, i can do select ascii(mid(col1,1,1)) from table where ... so doing this char by char I get the entire string. But the problem is that I haven't found the way to execute that ascii(mid(...)) function on the result of desc table or show tables etc Anyone sees something ? Philippe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL connection with PHP
hello,
i am learning PHP and MySQL recently, i use PHP to
connect to MySQL server,
however, an error occur:
Fatal error: Call to undefined function:
mysql_connect() in /var/... on line 2
Following is the PHP program:
1 ?php
2 $linkhandle = mysql_connect(192.168.0.1, john,
password);
3 $result = mysql_list_dbs($linkhandle);
4 $num_rows = mysql_num_rows($result);
5 while($db_data = mysql_fetch_row($result)) {
6 echo $db_data[0].br;
7 $result2 = mysql_list_tables($db_data[0]);
8 $num_rows2 = mysql_num_rows($result2);
9 while($table_data = mysql_fetch_row($result2)) {
10 echo --.$table_data[0].br;
11}
12echo == $num_row2 table(s) in .$db_data[0].
p;
13 }
14 ?
PHP version: 4.0.4
MySQL version: 3.23.36-1
But i use the same hostname, username and password
to connect MySQL at client side, it is no problem, so
i don't kown what's wrong in the program.
Can anyone solve my problem, any reply will be
appreciated, thanks.
Want to chat instantly with your online friends? Get the FREE Yahoo!
Messenger http://mail.messenger.yahoo.co.uk
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL connection with PHP
On Wed, 24 Sep 2003 18:44:06 +0100 (BST) John Cole [EMAIL PROTECTED] wrote: Fatal error: Call to undefined function: mysql_connect() in /var/... on line 2 This error means, that you PHP was built without MySQL support. You need to add MySQL support or to ask your admin to do it. PHP version: 4.0.4 MySQL version: 3.23.36-1 and it's time to update your PHP MySQL, definitely. --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: Automated MySql data exchange with Paradox databases
Hi everybody, We maintain both MySQL databases and Paradox databases and would like to build some automated data exchanger. Were in the process of replacing our Paradox with MySql; however it will take some time to rewrite all related software, approx 700 programs. Is there anybody in the group who has experience with both Paradox and MySql and has successfully created such a data exchanger? Im looking forwards to hear from you, Thanks in advance, Melle J. de Jong Atlas Business Software S/C Ltda. São Paulo, Brasil +5511 3082 6318 http://www.atlasbusinesssoftware.com http://www.atlasbusinesssoftware.com
Getting Non-Biased Standard Deviation with MySQL
Hello, I've read that the STD function in MySQL returns the biased standard deviation. Is there a way to get the non-biased SD using MySQL? Thanks for your help, Brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reading a table without knowing column names
I think by issuing a SELECT * FROM tblTableName will execute your query without knowing the column names. But you can also issue a SHOW FIELDS FROM tblTableName to get a list of the table columns. Hope this helps, Thanks Emery - Original Message - From: Philippe MAIRE [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 19:33 Subject: Reading a table without knowing column names Hi I would like to execute a select on a table for which i don't know the column names. IE I would like a command like select column(1) from table where ... Sure, if I ask, it is because I cannot use the desc command. I have another solution which causes again a problem : if I could execute a function on the result of desc table I explain myself : I access a mysql base through a fixed php script (which i can't modif), which takes for input a request in an html form, executes it and gives me back the numeric value of the first row, first column So when i know the field name and to retreive what I want, i can do select ascii(mid(col1,1,1)) from table where ... so doing this char by char I get the entire string. But the problem is that I haven't found the way to execute that ascii(mid(...)) function on the result of desc table or show tables etc Anyone sees something ? Philippe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: last_insert_id()
R.Dobson said: Hi, Is it possible to obtain the last_insert_id() for a particular column in a particular table? eg, say i wanted to obtain the last insert id of a column called id in table reference, something along the lines of: last_insert_id(reference.id) The reason I ask is because I want to initially insert values into two tables and then insert values into a third using the last_insert_id() from the first two tables. Obviously, the last_insert_id from the first insert is replaced by the last_insert_id from the second insert. This is all done in a perl script and I could store the first last_insert_id in a variable but I thought their might be a more elegant way round it? tia Rich last_insert_id is connection specific and contains the value of the auto-increment column for the last record you inserted using that connection. The sequence is - do insert of record with auto-increment column - get value of last_insterted_id (i.e., the value asigned to the auto-increment column to that record). There is only one column so there is no need for figuring out which column. If you do two inserts you must get the last_inserted_id BETWEEN the inserts. If the second table also has a auto-increment column you will have to get its value after that insert. Hope this helps. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reading a table without knowing column names
At 19:33 +0200 9/24/03, Philippe MAIRE wrote: Hi I would like to execute a select on a table for which i don't know the column names. IE I would like a command like select column(1) from table where ... Sure, if I ask, it is because I cannot use the desc command. You can use SELECT but you cannot use DESCRIBE? Can you use the result of: SELECT * FROM tbl_name WHERE 0 which returns the metadata for the result set, and an empty (zero-row) result set. I have another solution which causes again a problem : if I could execute a function on the result of desc table I explain myself : I access a mysql base through a fixed php script (which i can't modif), which takes for input a request in an html form, executes it and gives me back the numeric value of the first row, first column So when i know the field name and to retreive what I want, i can do select ascii(mid(col1,1,1)) from table where ... so doing this char by char I get the entire string. But the problem is that I haven't found the way to execute that ascii(mid(...)) function on the result of desc table or show tables etc Anyone sees something ? Philippe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: last_insert_id()
Don't forget that if you commit then last_insert_id will return 0 - Original Message - From: William R. Mussatto [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 6:56 PM Subject: Re: last_insert_id() : R.Dobson said: : Hi, : : Is it possible to obtain the last_insert_id() for a particular column in : a particular table? : eg, say i wanted to obtain the last insert id of a column called id in : table reference, something along the lines of: : : last_insert_id(reference.id) : : The reason I ask is because I want to initially insert values into two : tables and then insert values into a third using the last_insert_id() : from the first two tables. Obviously, the last_insert_id from the first : insert is replaced by the last_insert_id from the second insert. This is : all done in a perl script and I could store the first last_insert_id in : a variable but I thought their might be a more elegant way round it? : : tia : Rich : last_insert_id is connection specific and contains the value of the : auto-increment column for the last record you inserted using that : connection. The sequence is : - do insert of record with auto-increment column : - get value of last_insterted_id (i.e., the value asigned to the : auto-increment column to that record). There is only one column so there : is no need for figuring out which column. : : If you do two inserts you must get the last_inserted_id BETWEEN the : inserts. If the second table also has a auto-increment column you will : have to get its value after that insert. : : Hope this helps. : : William R. Mussatto, Senior Systems Engineer : Ph. 909-920-9154 ext. 27 : FAX. 909-608-7061 : : : : -- : MySQL General Mailing List : For list archives: http://lists.mysql.com/mysql : To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] : : -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL newbie: table gone after reboot
I have tried logging in as root ( mysql -p -u root todo ) and also as myself and both methods yield the same results, shown below: mysql SHOW TABLES FROM todo; ERROR 12: Can't read dir of './todo/' (Errcode: 13) mysql It continues to be curious... -A On Wednesday, September 24, 2003, at 08:22 AM, Victor Pendleton wrote: Does the user who you start the MySQL Server up with own the directory and the files? If the files are there you can try issuing a `show tables from todo`. If the tables appear try running `check table tasks` and let us know what the output is. -Original Message- From: Avram Aelony [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 9:37 AM To: Victor Pendleton Cc: [EMAIL PROTECTED] Subject: Re: MySQL newbie: table gone after reboot Okay, I had to log in as root to do so. The data seems to be still extant in tasks.MYD . The other 2 files, tasks.MYI and tasks.frm, are binary. -Avram [aa:local/mysql/data] aelony# ls -l todo total 40 -rw-rw 1 mysql wheel 1220 Sep 23 18:11 tasks.MYD -rw-rw 1 mysql wheel 2048 Sep 23 18:46 tasks.MYI -rw-rw 1 mysql wheel 8819 Sep 23 00:56 tasks.frm On Wednesday, September 24, 2003, at 07:15 AM, Victor Pendleton wrote: Can you post the contents from the todo directory? -Original Message- From: Avram Aelony [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 9:14 AM To: Victor Pendleton Cc: [EMAIL PROTECTED] Subject: Re: MySQL newbie: table gone after reboot I am unfamiliar with frm, myd, and myi files, yet perhaps is insightful? -Avram [aa:/usr/local/mysql] % ls -l data total 41032 -rw-rw 1 mysql wheel 6362 Sep 23 22:55 aa.local..err -rw-rw 1 mysql wheel 25088 Sep 8 00:43 ib_arch_log_00 -rw-rw 1 mysql wheel 5242880 Sep 23 22:55 ib_logfile0 -rw-rw 1 mysql wheel 5242880 Sep 8 00:43 ib_logfile1 -rw-rw 1 mysql wheel 10485760 Sep 23 22:54 ibdata1 drwxr-x--- 20 mysql wheel 680 Sep 8 00:37 mysql/ drwx-- 8 mysql wheel 272 Sep 22 23:59 sampdb/ drwxr-x--- 5 mysql wheel 170 Sep 10 17:18 test/ drwx-- 2 mysql wheel68 Sep 10 17:11 testdb1/ drwx-- 5 mysql wheel 170 Sep 23 00:56 todo/ [aa:/usr/local/mysql] % The aa.local..err file contains lengthy text about starts,stops to the server and errors, the other files appear to be binary.. On Wednesday, September 24, 2003, at 06:58 AM, Victor Pendleton wrote: Are the frm, myd and myi files located in the data directory? What is the data directory? -Original Message- From: Avram Aelony [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 8:43 AM To: [EMAIL PROTECTED] Subject: MySQL newbie: table gone after reboot I am new to MySQL and have encountered a problem that probably happens to everyone, yet I have searched for a solution online and elsewhere without success. I created a database and table. Then I populated the table. Everything worked perfectly. Then I shutdown MySQL and rebooted my computer. After starting mysqld, I now find my database, but the table and all its contents seem to be gone. 1.) Can I recover this table? 2.) How can I make sure this does not happen again? What did I do wrong? Is there an FAQ?? Thanks, Avram This is what I did... CREATE DATABASE todo; #Create tasks table for To Do list database CREATE TABLE tasks ( taskVARCHAR(60) NOT NULL, date_enteredTIMESTAMP(16) NOT NULL, date_dueDATE NULL, date_completed DATE NULL, priorityENUM(SOMEDAY, NOW, SOON) NOT NULL, description VARCHAR(255) NULL, taskid INT UNSIGNED NOT NULL AUTO_INCREMENT, keywordsVARCHAR(100) NULL, PRIMARY KEY (taskid) ); INSERT INTO tasks ( task, date_due, priority, description, keywords) VALUES(set up database,2003-09-24, SOON, set up the database, database); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb: CREATE INDEX
On Wed, Sep 24, 2003 at 06:15:19PM +0100, [EMAIL PROTECTED] wrote: Hi, I'm working in MySQL with innodb tables, in Linux (Red Hat 9). I'm creating indexes in a table with 16 million rows (it's a fact table), and it takes a lot of time (2/3/4 hours), because my system is always swapping in/out (i think). You should first verify if swap really is a problem or not. That could have a big impact on how you solve this problem... -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 10 days, processed 389,349,141 queries (418/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL connection with PHP
From my experience the php mailing lists newsgroups are quite actively
monitored and a lot of excellent advice is given there.
What I'd do is check error status after mysql calls (whether while dev'g
code or permanently to make your app stronger).
Check out mysql_error and mysql_errno. Also check return codes from mysql
operations.
Also...as the other fellow mentioned, you probably need to upgrade as mysql
libs are bundled w/my version of php (4.3.2).
I would say you probably don't have mysql libs in your php distro. Create a
php page w/the following:
?PHP
phpinfo();
?
and see what your configuration is.
mike
-Original Message-
From: John Cole [mailto:[EMAIL PROTECTED]
Sent: Wednesday, September 24, 2003 10:44 AM
To: [EMAIL PROTECTED]
Subject: MySQL connection with PHP
hello,
i am learning PHP and MySQL recently, i use PHP to
connect to MySQL server,
however, an error occur:
Fatal error: Call to undefined function:
mysql_connect() in /var/... on line 2
Following is the PHP program:
1 ?php
2 $linkhandle = mysql_connect(192.168.0.1, john,
password);
3 $result = mysql_list_dbs($linkhandle);
4 $num_rows = mysql_num_rows($result);
5 while($db_data = mysql_fetch_row($result)) {
6 echo $db_data[0].br;
7 $result2 = mysql_list_tables($db_data[0]);
8 $num_rows2 = mysql_num_rows($result2);
9 while($table_data = mysql_fetch_row($result2)) {
10 echo --.$table_data[0].br;
11}
12echo == $num_row2 table(s) in .$db_data[0].
p;
13 }
14 ?
PHP version: 4.0.4
MySQL version: 3.23.36-1
But i use the same hostname, username and password
to connect MySQL at client side, it is no problem, so
i don't kown what's wrong in the program.
Can anyone solve my problem, any reply will be
appreciated, thanks.
Want to chat instantly with your online friends? Get the FREE Yahoo!
Messenger http://mail.messenger.yahoo.co.uk
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL newbie: table gone after reboot
The user `root` in MySQL is not the same as the Unix `root` user. The user, mysql, or whatever you have selected needs to own the directory and files in the todo directory. chown -R mysql todo chgrp -R mysql todo ... Error 13 means that the user that started the MySQL server does not have permission to access the todo directory. -Original Message- From: Avram Aelony [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 1:33 PM To: Victor Pendleton Cc: [EMAIL PROTECTED] Subject: Re: MySQL newbie: table gone after reboot I have tried logging in as root ( mysql -p -u root todo ) and also as myself and both methods yield the same results, shown below: mysql SHOW TABLES FROM todo; ERROR 12: Can't read dir of './todo/' (Errcode: 13) mysql It continues to be curious... -A On Wednesday, September 24, 2003, at 08:22 AM, Victor Pendleton wrote: Does the user who you start the MySQL Server up with own the directory and the files? If the files are there you can try issuing a `show tables from todo`. If the tables appear try running `check table tasks` and let us know what the output is. -Original Message- From: Avram Aelony [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 9:37 AM To: Victor Pendleton Cc: [EMAIL PROTECTED] Subject: Re: MySQL newbie: table gone after reboot Okay, I had to log in as root to do so. The data seems to be still extant in tasks.MYD . The other 2 files, tasks.MYI and tasks.frm, are binary. -Avram [aa:local/mysql/data] aelony# ls -l todo total 40 -rw-rw 1 mysql wheel 1220 Sep 23 18:11 tasks.MYD -rw-rw 1 mysql wheel 2048 Sep 23 18:46 tasks.MYI -rw-rw 1 mysql wheel 8819 Sep 23 00:56 tasks.frm On Wednesday, September 24, 2003, at 07:15 AM, Victor Pendleton wrote: Can you post the contents from the todo directory? -Original Message- From: Avram Aelony [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 9:14 AM To: Victor Pendleton Cc: [EMAIL PROTECTED] Subject: Re: MySQL newbie: table gone after reboot I am unfamiliar with frm, myd, and myi files, yet perhaps is insightful? -Avram [aa:/usr/local/mysql] % ls -l data total 41032 -rw-rw 1 mysql wheel 6362 Sep 23 22:55 aa.local..err -rw-rw 1 mysql wheel 25088 Sep 8 00:43 ib_arch_log_00 -rw-rw 1 mysql wheel 5242880 Sep 23 22:55 ib_logfile0 -rw-rw 1 mysql wheel 5242880 Sep 8 00:43 ib_logfile1 -rw-rw 1 mysql wheel 10485760 Sep 23 22:54 ibdata1 drwxr-x--- 20 mysql wheel 680 Sep 8 00:37 mysql/ drwx-- 8 mysql wheel 272 Sep 22 23:59 sampdb/ drwxr-x--- 5 mysql wheel 170 Sep 10 17:18 test/ drwx-- 2 mysql wheel68 Sep 10 17:11 testdb1/ drwx-- 5 mysql wheel 170 Sep 23 00:56 todo/ [aa:/usr/local/mysql] % The aa.local..err file contains lengthy text about starts,stops to the server and errors, the other files appear to be binary.. On Wednesday, September 24, 2003, at 06:58 AM, Victor Pendleton wrote: Are the frm, myd and myi files located in the data directory? What is the data directory? -Original Message- From: Avram Aelony [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 8:43 AM To: [EMAIL PROTECTED] Subject: MySQL newbie: table gone after reboot I am new to MySQL and have encountered a problem that probably happens to everyone, yet I have searched for a solution online and elsewhere without success. I created a database and table. Then I populated the table. Everything worked perfectly. Then I shutdown MySQL and rebooted my computer. After starting mysqld, I now find my database, but the table and all its contents seem to be gone. 1.) Can I recover this table? 2.) How can I make sure this does not happen again? What did I do wrong? Is there an FAQ?? Thanks, Avram This is what I did... CREATE DATABASE todo; #Create tasks table for To Do list database CREATE TABLE tasks ( taskVARCHAR(60) NOT NULL, date_enteredTIMESTAMP(16) NOT NULL, date_dueDATE NULL, date_completed DATE NULL, priorityENUM(SOMEDAY, NOW, SOON) NOT NULL, description VARCHAR(255) NULL, taskid INT UNSIGNED NOT NULL AUTO_INCREMENT, keywordsVARCHAR(100) NULL, PRIMARY KEY (taskid) ); INSERT INTO tasks ( task, date_due, priority, description, keywords) VALUES(set up database,2003-09-24, SOON, set up the database, database); -- 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]
How to monitor indexing progress on large tables
Hello, I am indexing a decent sized table( ~400M rows, 20 gig), and am looking for a way to monitor the progress of the indexing. I imported the data with keying turned off (alter table disable keys), then did the enable keys command after I imported the data with load data infile. I of course expect this to take a couple of days, but I'd feel better if I had some way of monitoring the progress, so that I'd know if I had some sort of configuration error that was going to make it take weeks, or something crazy like that. I tried just listing the content of the database directory to see if the index file size was increasing, but it's just staying constant, even though the last updated time keeps going up. This worries me slightly! I considered timing an index generation from a subset of the data, but I am unfamiliar with the indexing processes, and I am not sure if the time would increase exponentially based on the number of records being indexed, or if it is a linear relationship. Also, does anyone know of a method to get the faster indexing method to work on large tables? I tried bumping up the myisam_max_sort_file_size in my.cnf, but it tops out at 4G, and even at that setting my show processlist is returning a repair with keycache, instead of the other, faster method which I can't recall the name of. Any tips to speed this process up, or at least see how long it's going to take would be greatly appreciated. I can understand it taking days, but I would like to not have to wait weeks I've attached the relevant parts of my my.cnf. The machine is an Athlon 2000XP with 1/2 gig of ram and a raid array. I will likely increase the RAM soon. It doesnt' seem to be maxing out the CPU all the time -- I think disk I/O is the bottleneck. Thanks very much for any help... Jeff Neuenschwander [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock skip-locking set-variable= key_buffer=350M set-variable= max_allowed_packet=1M set-variable= table_cache=256 set-variable= sort_buffer=4M set-variable= record_buffer=1M set-variable= myisam_sort_buffer_size=128M set-variable= thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=8 #log-bin server-id = 1 set-variable= myisam_max_sort_file_size=75000M -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
1000 rows in MySQLcc
Title: Mensagem Hi, everybody, I'm using MySQLcc version 0.9.2-beta... I want to recover more than 1000 rows in a select statement... What is the command toalter this setting ??? Thanks, ===José Oscar de Souza EduardoCoordenador de Projetostel: 3047-4541fax: 3047-4550[EMAIL PROTECTED]===
INSERT data into multiple tables
Hello, I have a question about INSERTing data into 2 different tables with one statement. Can you do this? INSERT INTO table1 (name,address,phone) VALUES ( ' USER ', ' USERADDY ',' USERPHONE') AND table2 (name) VALUES( ' USER '); -Dan
Re: INSERT data into multiple tables
On Wed, 24 Sep 2003 14:23:04 -0500 Dan J. Rychlik [EMAIL PROTECTED] wrote: Hello, I have a question about INSERTing data into 2 different tables with one statement. Can you do this? INSERT INTO table1 (name,address,phone) VALUES ( ' USER ', ' USERADDY ',' USERPHONE') AND table2 (name) VALUES( ' USER '); No. Use 2 queries for that: INSERT INTO table1 (name,address,phone) VALUES ( ' USER ', ' USERADDY ',' USERPHONE'); INSERT INTO table2 (name) VALUES( ' USER '); --- WBR, Antony Dovgal aka tony2001 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL newbie: table gone after reboot
Changing the ownership fixed everything. I did not realize there were multiple notions of 'root'. Thank-you, Avram On Wednesday, September 24, 2003, at 12:10 PM, Victor Pendleton wrote: The user `root` in MySQL is not the same as the Unix `root` user. The user, mysql, or whatever you have selected needs to own the directory and files in the todo directory. chown -R mysql todo chgrp -R mysql todo ... Error 13 means that the user that started the MySQL server does not have permission to access the todo directory. -Original Message- From: Avram Aelony [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 1:33 PM To: Victor Pendleton Cc: [EMAIL PROTECTED] Subject: Re: MySQL newbie: table gone after reboot I have tried logging in as root ( mysql -p -u root todo ) and also as myself and both methods yield the same results, shown below: mysql SHOW TABLES FROM todo; ERROR 12: Can't read dir of './todo/' (Errcode: 13) mysql It continues to be curious... -A On Wednesday, September 24, 2003, at 08:22 AM, Victor Pendleton wrote: Does the user who you start the MySQL Server up with own the directory and the files? If the files are there you can try issuing a `show tables from todo`. If the tables appear try running `check table tasks` and let us know what the output is. -Original Message- From: Avram Aelony [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 9:37 AM To: Victor Pendleton Cc: [EMAIL PROTECTED] Subject: Re: MySQL newbie: table gone after reboot Okay, I had to log in as root to do so. The data seems to be still extant in tasks.MYD . The other 2 files, tasks.MYI and tasks.frm, are binary. -Avram [aa:local/mysql/data] aelony# ls -l todo total 40 -rw-rw 1 mysql wheel 1220 Sep 23 18:11 tasks.MYD -rw-rw 1 mysql wheel 2048 Sep 23 18:46 tasks.MYI -rw-rw 1 mysql wheel 8819 Sep 23 00:56 tasks.frm On Wednesday, September 24, 2003, at 07:15 AM, Victor Pendleton wrote: Can you post the contents from the todo directory? -Original Message- From: Avram Aelony [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 9:14 AM To: Victor Pendleton Cc: [EMAIL PROTECTED] Subject: Re: MySQL newbie: table gone after reboot I am unfamiliar with frm, myd, and myi files, yet perhaps is insightful? -Avram [aa:/usr/local/mysql] % ls -l data total 41032 -rw-rw 1 mysql wheel 6362 Sep 23 22:55 aa.local..err -rw-rw 1 mysql wheel 25088 Sep 8 00:43 ib_arch_log_00 -rw-rw 1 mysql wheel 5242880 Sep 23 22:55 ib_logfile0 -rw-rw 1 mysql wheel 5242880 Sep 8 00:43 ib_logfile1 -rw-rw 1 mysql wheel 10485760 Sep 23 22:54 ibdata1 drwxr-x--- 20 mysql wheel 680 Sep 8 00:37 mysql/ drwx-- 8 mysql wheel 272 Sep 22 23:59 sampdb/ drwxr-x--- 5 mysql wheel 170 Sep 10 17:18 test/ drwx-- 2 mysql wheel68 Sep 10 17:11 testdb1/ drwx-- 5 mysql wheel 170 Sep 23 00:56 todo/ [aa:/usr/local/mysql] % The aa.local..err file contains lengthy text about starts,stops to the server and errors, the other files appear to be binary.. On Wednesday, September 24, 2003, at 06:58 AM, Victor Pendleton wrote: Are the frm, myd and myi files located in the data directory? What is the data directory? -Original Message- From: Avram Aelony [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 8:43 AM To: [EMAIL PROTECTED] Subject: MySQL newbie: table gone after reboot I am new to MySQL and have encountered a problem that probably happens to everyone, yet I have searched for a solution online and elsewhere without success. I created a database and table. Then I populated the table. Everything worked perfectly. Then I shutdown MySQL and rebooted my computer. After starting mysqld, I now find my database, but the table and all its contents seem to be gone. 1.) Can I recover this table? 2.) How can I make sure this does not happen again? What did I do wrong? Is there an FAQ?? Thanks, Avram This is what I did... CREATE DATABASE todo; #Create tasks table for To Do list database CREATE TABLE tasks ( taskVARCHAR(60) NOT NULL, date_enteredTIMESTAMP(16) NOT NULL, date_dueDATE NULL, date_completed DATE NULL, priorityENUM(SOMEDAY, NOW, SOON) NOT NULL, description VARCHAR(255) NULL, taskid INT UNSIGNED NOT NULL AUTO_INCREMENT, keywordsVARCHAR(100) NULL, PRIMARY KEY (taskid) ); INSERT INTO tasks ( task, date_due, priority, description, keywords) VALUES(set up database,2003-09-24, SOON, set up the database, database); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb
Gustavo, you have to raise the log sequence number with the insert trick I explained in my earlier message. Creating new log files does not help. I have now added checks to a future version of InnoDB. They monitor the lsn at the startup, shutdown, and every 2 seconds during mysqld operation. They should reveal if there is a bug which lowers the log sequence number sometimes. I have seen lsn's up to 2 TB. The bug, if it exists, cannot repeat very often. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL - Original Message - From: Gustavo A. Baratto [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, September 24, 2003 5:53 AM Subject: Re: innodb Even with brand new log files, I got the messages again: 030923 13:37:10 InnoDB: Error: page 2621 log sequence number 0 758479545 InnoDB: is in the future! Current system log sequence number 0 330424282. InnoDB: Your database may be corrupt. 030923 13:37:10 InnoDB: Error: page 2622 log sequence number 0 758440573 InnoDB: is in the future! Current system log sequence number 0 330424282. InnoDB: Your database may be corrupt. 030923 13:37:10 InnoDB: Error: page 2623 log sequence number 0 758358467 InnoDB: is in the future! Current system log sequence number 0 330424282. InnoDB: Your database may be corrupt. Heikki Tuuri wrote: Harald, 030923 15:10:14 InnoDB: Error: page 53 log sequence number 6 190415140 InnoDB: is in the future! Current system log sequence number 1 3864837242. InnoDB: Your database may be corrupt. what do you think is the correct log sequence number? How much do you have data? The pages have lsn about 16 GB or 24 GB, while the log files only have lsn about 8 GB. Please send me your whole .err log. That may contain clues of what has happened. Best regards, Heikki Innobase Oy http://www.innodb.com InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for MySQL Order MySQL support from http://www.mysql.com/support/index.html .. Heikki, many thanks for your reply. I do well understand that I must *never* touch logfiles or datafiles, and I did not do that. The only thing I did was the following: $ mysqladmin shutdown This was 4.0.14. The error log said: InnoDB: Starting shutdown... InnoDB: Shutdown completed /usr/sbin/mysqld: Shutdown Complete $ cp /usr/sbin/mysqld-4.0.15 /usr/sbin/mysqld This is bin/mysqld from mysql-standard-4.0.15-pc-linux-i686.tar.gz $ rcmysql start Now the error log said: 030923 15:10:12 mysqld started 030923 15:10:14 InnoDB: Error: page 45 log sequence number 6 193108436 InnoDB: is in the future! Current system log sequence number 1 3864837242. InnoDB: Your database may be corrupt. 030923 15:10:14 InnoDB: Error: page 52 log sequence number 6 190390477 InnoDB: is in the future! Current system log sequence number 1 3864837242. InnoDB: Your database may be corrupt. 030923 15:10:14 InnoDB: Error: page 53 log sequence number 6 190415140 InnoDB: is in the future! Current system log sequence number 1 3864837242. InnoDB: Your database may be corrupt. 030923 15:10:14 InnoDB: Error: page 54 log sequence number 4 1256304988 InnoDB: is in the future! Current system log sequence number 1 3864837242. InnoDB: Your database may be corrupt. 030923 15:10:14 InnoDB: Error: page 55 log sequence number 6 190440189 InnoDB: is in the future! Current system log sequence number 1 3864837242. InnoDB: Your database may be corrupt. 030923 15:10:14 InnoDB: Error: page 56 log sequence number 6 190464315 InnoDB: is in the future! Current system log sequence number 1 3864837242. InnoDB: Your database may be corrupt. 030923 15:10:14 InnoDB: Error: page 59 log sequence number 4 1253798302 InnoDB: is in the future! Current system log sequence number 1 3864837242. InnoDB: Your database may be corrupt. 030923 15:10:14 InnoDB: Error: page 60 log sequence number 4 1216946799 InnoDB: is in the future! Current system log sequence number 1 3864837242. InnoDB: Your database may be corrupt. 030923 15:10:14 InnoDB: Error: page 61 log sequence number 4 1253798302 InnoDB: is in the future! Current system log sequence number 1 3864837242. InnoDB: Your database may be corrupt. 030923 15:10:14 InnoDB: Started /usr/sbin/mysqld: ready for connections. Version: '4.0.15-standard-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Now I switched back to 4.0.14: $ mysqladmin shutdown $ cp /usr/sbin/mysqld-max /usr/sbin/mysqld This is the old 4.0.14 binary, compiled by MySQL AB. $ rcmysql start The error log
Re: DataDir question
...after finding a log in the directory that I was trying to use for my new DataDir, I've discovered my problem (and, surprise, it makes sense). The MySQL database table must also be in place in the directory, I suppose in order to keep track of the other databases and tables, etc. Cheer. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb disk geometry
Hi! Resending this, because this did not show up at http://lists.mysql.com/list.php?list=mysql#b Heikki - Original Message - From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 23, 2003 10:04 PM Subject: Re: Innodb disk geometry Sean, InnoDB-4.1.1 with multiple tablespaces already works in my computer, except that ALTER TABLE does not work yet. I also have to test symlinks, because you have to use them if you want to place databases on separate disks. Below you see how database 'test' now looks like. The .ibd files contain the data. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for MySQL [EMAIL PROTECTED]:~/data/test ls -l total 258717 -rw-rw1 heikki innodb 58720256 Sep 23 21:39 #sql-50d3_1f.ibd -rw-rw1 heikki innodb 9076 Sep 23 20:31 alex1.frm -rw-rw1 heikki innodb 28311552 Sep 23 21:39 alex1.ibd -rw-rw1 heikki innodb 9076 Sep 23 21:39 alex2.frm -rw-rw1 heikki innodb 245760 Sep 23 21:39 alex2.ibd -rw-rw1 heikki innodb 9076 Sep 23 21:39 alex3.frm -rw-rw1 heikki innodb 245760 Sep 23 21:39 alex3.ibd -rw-rw1 heikki innodb 8626 Sep 23 20:31 blobt3.frm -rw-rw1 heikki innodb 92274688 Sep 23 21:39 blobt3.ibd -rw-rw1 heikki innodb 8626 Sep 23 20:31 ibstest0.frm -rw-rw1 heikki innodb 15728640 Sep 23 21:39 ibstest0.ibd -rw-rw1 heikki innodb 8626 Sep 23 21:39 ibstest2.frm -rw-rw1 heikki innodb 131072 Sep 23 21:39 ibstest2.ibd -rw-rw1 heikki innodb 8722 Sep 23 19:34 ibtest0.frm -rw-rw1 heikki innodb 41943040 Sep 23 20:30 ibtest0.ibd -rw-rw1 heikki innodb 8722 Sep 23 20:31 ibtest09.frm - Original Message - From: sean peters [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Tuesday, September 23, 2003 5:59 PM Subject: Innodb disk geometry Hi all, i've been trying to follow the Innodb upgrade that will allow setting up multiple separate blocks of disk for different databases, where the admin has the ability to put databases in specific tablespaces. There has been a bit of talk here, but whenever i go to the MySQL or Innodb web sites, i find nothing regarding this issue. So, instead of posting the same question here once a month, and wasting all your time, i'd much rather be aware of where i can check the progress of this issue. If anyone can point me at any such information, it would save effort for both me and you !!! thanks much -- 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]
mySQL is slow with TCP/IP !!!
Hi, I wrote some queries to check the speed. If the client and server both are windows NT, retrieving large number of records is very slow but when the client is windows 98 and server is NT, it works almost 20 times faster. Probably the problem is caused by TCP/IP which is much slower over the lan in windows NT, 2000, and XP. Do you know any solution for this problem? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mySQL is slow with TCP/IP !!!
Unix or Linux.. Mojtaba Faridzad [EMAIL PROTECTED] 09/24/03 03:10PM Hi, I wrote some queries to check the speed. If the client and server both are windows NT, retrieving large number of records is very slow but when the client is windows 98 and server is NT, it works almost 20 times faster. Probably the problem is caused by TCP/IP which is much slower over the lan in windows NT, 2000, and XP. Do you know any solution for this problem? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Please Note The information in this E-mail message is legally privileged and confidential information intended only for the use of the individual(s) named above. If you, the reader of this message, are not the intended recipient, you are hereby notified that you should not further disseminate, distribute, or forward this E-mail message. If you have received this E-mail in error, please notify the sender. Thank you * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mySQL is slow with TCP/IP !!!
Use Linux! This is the solution. If you have to use windows will need to tweak your TCP settings Here is a tool that can help you. http://www.dslreports.com/front/drtcp.html - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Mojtaba Faridzad [mailto:[EMAIL PROTECTED] --Sent: Wednesday, September 24, 2003 1:10 PM --To: [EMAIL PROTECTED] --Subject: mySQL is slow with TCP/IP !!! -- --Hi, -- --I wrote some queries to check the speed. If the client and server both --are --windows NT, retrieving large number of records is very slow but when the --client is windows 98 and server is NT, it works almost 20 times faster. --Probably the problem is caused by TCP/IP which is much slower over the --lan --in windows NT, 2000, and XP. Do you know any solution for this problem? -- --Thanks -- -- --MySQL General Mailing List --For list archives: http://lists.mysql.com/mysql --To unsubscribe: --http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb: storage requirements
# [EMAIL PROTECTED] / 2003-09-24 17:41:29 +0300: the row count in SHOW TABLE STATUS is only an estimate based on 8 dives into the index tree. ok. You had a typical symptom of a fragmented table: space usage much bigger than you would expect. that doesn't answer my questions :) notice the numbers: mysql SHOW TABLE STATUS LIKE 'editor_competence_product'\G *** 1. row *** Name: editor_competence_product Type: InnoDB Row_format: Fixed Rows: 225198 ^^ here, InnoDB thinks the table has 225198 rows. mysql ALTER TABLE editor_competence_product TYPE=InnoDB; Query OK, 187654 rows affected (47.34 sec) Records: 187654 Duplicates: 0 Warnings: 0 here, 187654 rows is reported. mysql SHOW TABLE STATUS LIKE 'editor_competence_product'\G *** 1. row *** Name: editor_competence_product Type: InnoDB Row_format: Fixed Rows: 187265 ^^ yet another number. what I don't understand is why the second and third number differ. but this question is more important, and I'd really *love* to know an answer: Also, is there a way to *measure* the fragmentation of a table? If there is, how should the info be interpreted? -- If you cc me or remove the list(s) completely I'll most likely ignore your message.see http://www.eyrie.org./~eagle/faqs/questions.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mySQL is slow with TCP/IP !!!
Hooray for Linux! True the networking code in Linux has proven to have less overhead the windows. - Original Message - From: Dathan Vance Pattishall [EMAIL PROTECTED] To: 'Mojtaba Faridzad' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 3:40 PM Subject: RE: mySQL is slow with TCP/IP !!! Use Linux! This is the solution. If you have to use windows will need to tweak your TCP settings Here is a tool that can help you. http://www.dslreports.com/front/drtcp.html - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Mojtaba Faridzad [mailto:[EMAIL PROTECTED] --Sent: Wednesday, September 24, 2003 1:10 PM --To: [EMAIL PROTECTED] --Subject: mySQL is slow with TCP/IP !!! -- --Hi, -- --I wrote some queries to check the speed. If the client and server both --are --windows NT, retrieving large number of records is very slow but when the --client is windows 98 and server is NT, it works almost 20 times faster. --Probably the problem is caused by TCP/IP which is much slower over the --lan --in windows NT, 2000, and XP. Do you know any solution for this problem? -- --Thanks -- -- --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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB, Replication, and Data warehouse: Oil, Water, and little floating plastic men
Hmm, as I feared, this problem is so major that no one wants to touch it. Not that I blame anyone. -Original Message- From: Misao [mailto:[EMAIL PROTECTED] Sent: Monday, September 22, 2003 6:09 PM To: [EMAIL PROTECTED] Subject: InnoDB, Replication, and Data warehouse: Oil, Water, and little floating plastic men -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB, Replication, and Data warehouse: Oil, Water, and little floating plastic men
On Mon, Sep 22, 2003 at 06:09:22PM -0700, Misao wrote: What follows is a short story, all true and quite stressful. No database servers were harmed in the making of this server, but a couple were threatened with loose rack mount rails. We are trying to move over to InnoDB, but we have a few problems that we just can't figure out: First, for some reason, MySQL claims it can not claim more than .5Gigs of RAM from a system that has 4Gigs of RAM total and not being used for anything but MySQL. How does it make this claim? Secondly, MySQL replication leaves a problem for our data warehouse and replication. When you use MySQL binary replication, it has been my experience that it is all or nothing. You can't choose just one database to replicate. You can start or stop just one database on the slave from being replicated. If you stop one, they all stop. This is a problem, because what we need to do is stop replication at midnight, and then do a dump of the database. Just one of them. When we stop it now, they all stop so now we have databases that are idle and not getting up to date replication while this one database gets mysqldumped for hours. InnoDB hot backup is a swell thing, but it doesn't dump the database in a useable format for anything except bringing an entire server online. Have you considered running separate instances of MySQL, one for each database? Before, what we did with MyISAM was a crude but workable in house replication system that used the text file update logs to replicate to a slave. This allowed us to replicate by database, and in turn only affect that one database for replication and dumping. It also allowed us to attach a data warehouse program to the replication so that it could grab the information it needed. With MySQL binary replication, we can not do either of these activities. Sure you can. Use the mysqlbinlog tool. It has a -d argument that will only show queries from the given database. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 10 days, processed 392,383,279 queries (417/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mySQL is slow with TCP/IP !!!
Thanks for your helps. If I have a Linux on server and windows xp on client, do I still have slow connection? I think I will have because Linux is using TCP/IP for connection to windows clien. is that right? - Original Message - From: Dan J. Rychlik [EMAIL PROTECTED] To: Dathan Vance Pattishall [EMAIL PROTECTED]; 'Mojtaba Faridzad' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 4:54 PM Subject: Re: mySQL is slow with TCP/IP !!! Hooray for Linux! True the networking code in Linux has proven to have less overhead the windows. - Original Message - From: Dathan Vance Pattishall [EMAIL PROTECTED] To: 'Mojtaba Faridzad' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 3:40 PM Subject: RE: mySQL is slow with TCP/IP !!! Use Linux! This is the solution. If you have to use windows will need to tweak your TCP settings Here is a tool that can help you. http://www.dslreports.com/front/drtcp.html - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Mojtaba Faridzad [mailto:[EMAIL PROTECTED] --Sent: Wednesday, September 24, 2003 1:10 PM --To: [EMAIL PROTECTED] --Subject: mySQL is slow with TCP/IP !!! -- --Hi, -- --I wrote some queries to check the speed. If the client and server both --are --windows NT, retrieving large number of records is very slow but when the --client is windows 98 and server is NT, it works almost 20 times faster. --Probably the problem is caused by TCP/IP which is much slower over the --lan --in windows NT, 2000, and XP. Do you know any solution for this problem? -- --Thanks -- -- --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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mySQL is slow with TCP/IP !!!
Use Linux to Linux makes life easier, because Microsoft doesn't seem to like Linux (although some ms code is in it (dhcp)). Windows 98 out the box configures TCP usage for a 56K modem from what I've read. Use the program linked below to switch from modem settings to LAN settings. Mimic the Windows NT TCP stack settings by using the same tool on it. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: Mojtaba Faridzad [mailto:[EMAIL PROTECTED] --Sent: Wednesday, September 24, 2003 2:25 PM --To: [EMAIL PROTECTED] --Subject: Re: mySQL is slow with TCP/IP !!! -- --Thanks for your helps. If I have a Linux on server and windows xp on --client, --do I still have slow connection? I think I will have because Linux is --using --TCP/IP for connection to windows clien. is that right? -- -- --- Original Message - --From: Dan J. Rychlik [EMAIL PROTECTED] --To: Dathan Vance Pattishall [EMAIL PROTECTED]; 'Mojtaba --Faridzad' [EMAIL PROTECTED]; [EMAIL PROTECTED] --Sent: Wednesday, September 24, 2003 4:54 PM --Subject: Re: mySQL is slow with TCP/IP !!! -- -- -- Hooray for Linux! True the networking code in Linux has proven to have --less -- overhead the windows. -- -- - Original Message - -- From: Dathan Vance Pattishall [EMAIL PROTECTED] -- To: 'Mojtaba Faridzad' [EMAIL PROTECTED]; -- [EMAIL PROTECTED] -- Sent: Wednesday, September 24, 2003 3:40 PM -- Subject: RE: mySQL is slow with TCP/IP !!! -- -- -- Use Linux! This is the solution. -- -- If you have to use windows will need to tweak your TCP settings -- -- Here is a tool that can help you. -- -- http://www.dslreports.com/front/drtcp.html -- -- -- -- - Dathan Vance Pattishall -- - Sr. Programmer and mySQL DBA for FriendFinder Inc. -- - http://friendfinder.com/go/p40688 -- -- -- ---Original Message- -- --From: Mojtaba Faridzad [mailto:[EMAIL PROTECTED] -- --Sent: Wednesday, September 24, 2003 1:10 PM -- --To: [EMAIL PROTECTED] -- --Subject: mySQL is slow with TCP/IP !!! -- -- -- --Hi, -- -- -- --I wrote some queries to check the speed. If the client and server -- both -- --are -- --windows NT, retrieving large number of records is very slow but --when -- the -- --client is windows 98 and server is NT, it works almost 20 times -- faster. -- --Probably the problem is caused by TCP/IP which is much slower over -- the -- --lan -- --in windows NT, 2000, and XP. Do you know any solution for this -- problem? -- -- -- --Thanks -- -- -- -- -- -- --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] -- -- -- -- -- -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB, Replication, and Data warehouse: Oil, Water, and little floating plastic men
MySQL tells me at startup that it can not allocate more than 512MB of RAM. It will fail to start the server if I specify any further. As far as separate instances, we have not looked into doing that for the masters, but we may use that for the data warehouse copy. The idea of multiple MySQL master instances on the same box just doesn't sound friendly. Perhaps you have had better experience with this. Thanks for the tip on mysqlbinlog, but using that will require a lot of tweaking and development. It may prove useful though. What I did like about MySQL replication is that it is pretty simple. This simplicity however also causes the problem of wanting to replicate only one database at a time. Its not that we need separate replication, its that we want to be able to stop replication of one database and let the others continue while we do things to the stopped database. -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 2:11 PM To: Misao Cc: [EMAIL PROTECTED] Subject: Re: InnoDB, Replication, and Data warehouse: Oil, Water, and little floating plastic men On Mon, Sep 22, 2003 at 06:09:22PM -0700, Misao wrote: *snip* How does it make this claim? *snip* Have you considered running separate instances of MySQL, one for each database? *snip* Sure you can. Use the mysqlbinlog tool. It has a -d argument that will only show queries from the given database. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 10 days, processed 392,383,279 queries (417/sec. avg) -- 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]
Database corruption
Hi, Advice on the cause of the following table corruption would be much appreciated. Some background: This customer has been running MySQL for 3 or more years, and, for back up purposes, shuts MySQL down each night, takes a snapshot of the tables for backup, and restarts. We have sufferred a table corruption before, of the same table, when the table was an ISAM table under version 3.23.40. We have since upgraded to 4.0.13, and changed the table to MyISAM type. Searching the list archive for 'Wrong bytesec' on mysql.com returns no results. Google returns some, but mainly old ones. Maybe an appendix to the manual giving some information about the possible things that myisamchk can find/repair would be useful? Are there any known problems with shutting down and re-starting so regularly? Would a flush-tables followed by a check that there are no open tables achieve the same result. Is there a way of stopping new connections to a running server? mysql REPAIR TABLE tt_tickets_bck and # myisamchk -r tt_tickets_bck both fixed the issue, but I'm being asked why the corruption happened in the first place. Myisamchk -vvv resulted in 212424 lines of information. Thanks in advance Quentin Bennett # mysqladmin version mysqladmin Ver 8.40 Distrib 4.0.13, for dec-osf5.1 on alphaev67 (-- Binaries from mysql.com --) Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.13-max-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /data/mysql/mysql.sock Uptime: 1 hour 21 min 23 sec Threads: 37 Questions: 42387 Slow queries: 16 Opens: 258 Flush tables: 2 Open tables: 113 Queries per second avg: 8.681 mysql check table tt_tickets_bck; ++---+--+--- + | Table | Op| Msg_type | Msg_text | ++---+--+--- + | mercury.tt_tickets_bck | check | warning | Table is marked as crashed | | mercury.tt_tickets_bck | check | warning | 2 clients is using or hasn't closed the table properly| | mercury.tt_tickets_bck | check | warning | Size of datafile is: 290516700 Should be: 285712092 | | mercury.tt_tickets_bck | check | error| Unexpected byte: 5 at link: 285506400 | | mercury.tt_tickets_bck | check | error| Corrupt | ++---+--+--- + 5 rows in set (58.28 sec) # myisamchk -vvv -r tt_tickets_bck - recovering (with sort) MyISAM-table 'tt_tickets_bck' Data records: 5641601 - Fixing index 1 - Searching for keys, allocating buffer for 46268 keys Wrong bytesec: 31- 0- 0 at304; Skipped Wrong bytesec: 49- 55- 53 at320; Skipped Wrong bytesec: 5- 87- 50 at328; Skipped Wrong bytesec: 48- 53- 55 at 285506420; Skipped . . . Wrong bytesec: 4- 49- 53 at692; Skipped Wrong bytesec: 5- 87- 50 at700; Skipped Found block with impossible length 3289448 at 285506560; Skipped Wrong bytesec: 27- 0- 0 at 1080; Skipped Wrong bytesec: 56- 54- 48 at 1096; Skipped Wrong bytesec: 87- 50- 29 at 1108; Skipped . . . Wrong bytesec: 27- 0- 0 at 5080; Skipped Wrong bytesec: 53- 53- 55 at 5096; Skipped Wrong bytesec: 87- 50- 29 at 5108; Skipped Found block with too small length at 5116; Skipped Wrong bytesec: 27- 0- 0 at 5120; Skipped Wrong bytesec: 53- 53- 55 at 5136; Skipped . . . Wrong bytesec: 56- 50- 56 at5762080; Skipped Wrong bytesec: 87- 50- 29 at5762092; Skipped Delete link points outside datafile at 285512060 Wrong bytesec: 27- 0- 0 at5762148; Skipped Wrong bytesec: 54- 57- 57 at5762164; Skipped . . Wrong bytesec: 200- 3- 1 at 290516684; Skipped - Merging 5621599 keys - Last merge and dumping keys - Fixing index 2 - Searching for keys, allocating buffer for 139678 keys - Merging 5621599 keys - Last merge and dumping keys - Fixing index 3 - Searching for keys, allocating buffer for 104727 keys - Merging 5621599 keys - Last merge and dumping keys Data records: 5621599 # Quentin Bennett Senior Analyst Infinity Solutions Ltd PO Box 3323, Auckland Ph: 09 921 8146 Fx: 09 309 4142 www.infinitytransport.net The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. -- MySQL General Mailing List For list archives:
Re: InnoDB, Replication, and Data warehouse: Oil, Water, and little floating plastic men
On Wed, Sep 24, 2003 at 03:03:20PM -0700, Misaochankun wrote: MySQL tells me at startup that it can not allocate more than 512MB of RAM. It will fail to start the server if I specify any further. On what OS? Can we see the exact error? Have you checked things like ulimit? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 10 days, processed 393,361,824 queries (416/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Different DB directories against one MySQL
I'm trying to have MySQL connect to different filesystems (mostly in read-only mode). I've tried setting the read-only attribute of my files under my database directory under c:\mysql\data and MySQL seems to be able to SELECT it with no problem (I then tried doing an INSERT and it came up with an error message, which is perfect). Hopefully, this would also work if my database directory is in CDROM even if the MySQL binaries are in writable hard disk. My question is: Is it possible to have a single instance of MySQL where each connection might have a different data directory? For example, I have a user that needs to query data from a CDROM, and there's another one that needs to query some tape backup. If not, I am assuming it should be OK to run multiple instances of MySQL (on different ports) and just have them point to different data paths (unless you guys know of a more elegant solution). Thanks! Marlon
Re: Database quotas?
Its not, i ment to say mysql user. A cron will work, but it wont be up-to-date.. hmm, need to research it a bit more. - Original Message - From: Jeremy Zawodny [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, September 25, 2003 3:11 AM Subject: Re: Database quotas? On Wed, Sep 24, 2003 at 03:18:07PM +1000, [EMAIL PROTECTED] wrote: Yeah but when they make a nwe table, it will be owned by root.root wont it? First, don't run MySQL as root. Second, yes. It will be owned by whoever creates it. But a periodic cron job to chorwn them appropriately would be a reasonable solution, I suspect. Jeremy -- Jereme D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 10 days, processed 388,649,043 queries (420/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
300 seconds in sending data phase
Hello, I run a mod_perl/mysql website, and have been noticing increasingly often lately that queries will get stuck for a very long time in the sending data phase: 3652allpoetry localhost allpoetry Query 291 Sending dataSELECT amt,bid,bids.lid,bids.mid,owner,name,title,bids.type,bids.preferred,cat1 ,cat2,cat3,poems.mid as poemowner,poets.preferred as p_pref, poems.type as p_type, poems.image FROM bids left join poets on bids.owner = poets.mid left join poems on poems.lid = bids.lid WHERE remaining 0 ORDER BY amt DESC LIMIT 5 3653allpoetry localhost allpoetry Query 291 Sending dataSELECT amt,bid,bids.lid,bids.mid,owner,name,title,bids.type,bids.preferred,cat1 ,cat2,cat3,poems.mid as poemowner,poets.preferred as p_pref, poems.type as p_type, poems.image FROM bids left join poets on bids.owner = poets.mid left join poems on poems.lid = bids.lid WHERE remaining 0 ORDER BY amt DESC LIMIT 5 ... 10 more of the same thing ... Often these are the *only* queries shown running for 10-20-30 seconds in show full processlist, though other things are running, just very quickily. Eventually things start to wait for writes and it locks up and drags the system to slowness for 5-10 minutes. The long 'sending data' phase seems to happen for many different queries. The explain for the top query shows: + | table | type | possible_keys | key | key_len | ref| rows | +---++---+---+-++--- -- | bids | range | remaining | remaining | 2 | NULL | 192 | Using where; Using filesort | | poets | eq_ref | PRIMARY | PRIMARY | 3 | bids.owner | 1 | | | poems | eq_ref | PRIMARY | PRIMARY | 3 | bids.lid | 1 | | +---++---+---+-++--- --- Sure, it's a filesort, but only 192 rows so should be no big deal. (Side note: the 'remaining' key is on (remaining, amt), so shouldn't it be using it and not filesorting?). During these times apache doesn't seem to be using any more than usual processor power, and I've done lots of 'strace -p' but without really learning anything. Any ideas what could be causing such a long sending-data phase? Ram is a little short, but not dipping into swap. total:used:free: shared: buffers: cached: Mem: 650891264 636760064 141312000 7864320 347262976 Swap: 806068224 20066304 786001920 I'm running the latest 4.0x redhat rpm version (4.0.15-standard-log), with all myisam tables. My process list looks like: ## PID UID Size Share VSize Rss TTY St Command 1 12085 mysql22M 2.1M 132M 22M 34816 S mysqld 2 12087 mysql22M 2.1M 132M 22M 34816 S mysqld 3 12088 mysql22M 2.1M 132M 22M 34816 S mysqld .. repeat exactly until ... 49 12367 mysql22M 2.1M 132M 22M 34816 S mysqld 1 12056 root988K 820K 4.1M 988K 34816 S mysqld_safe Which is strange too because it has way more processes than apache can be using (should be only one persistent connection per process): # ps aux | grep /sbin/httpd | wc -l 22 # ps aux | grep mysql | wc -l 53 My relevant my.cnf variables are: set-variable= key_buffer=100M set-variable= max_allowed_packet=1M set-variable= table_cache=256 set-variable= sort_buffer=4M set-variable= record_buffer=1M set-variable= myisam_max_sort_file_size=600M set-variable= myisam_max_extra_sort_file_size=600M set-variable= myisam_sort_buffer_size=32M set-variable= thread_cache=40 set-variable= thread_concurrency=2 Thanks for any help you can suggest! Sorry for including so much information, I'm not sure what could be causing it. Ciao, Kevin Watt Community Manager, Allpoetry.com How do really small people call each other ? On Microphones How do you fix a broken chimp? With a monkey wrench -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Different DB directories against one MySQL
Hi Marlon, Under linux mysql will 'see' any databases you've got links in the folder you've configured it to look. So in my /var/lib/mysql I have a symlink to /db/Allpoetry, and it knows to look there for the data files. So each database can have its own directory. You could probably even symlink the tables to files on other odd places if you wanted too. I assume the same thing is possible under windows. I know cygwin does symlinks, but don't know if the windows mysql will follow them, or if it follows shortcuts otherwise anyways. Not sure what the efficiency problems of running a database from a cdrom would be either, but it'd be an interesting experiment :) Ciao, Kevin Watt Community Manager, Allpoetry.com What happened to the cow who went for a drive? He got a Moo_ving violation What do you call someone who is crazy about hot chocolate? A cocoa nut What do bees use to cut wood? Buzz saws Who eats at underwater resturants ? Suba diners How do really small people call each other ? On Microphones How do you fix a broken chimp? With a monkey wrench -Original Message- From: Baculio, Marlon [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 3:32 PM To: [EMAIL PROTECTED] Subject: Different DB directories against one MySQL I'm trying to have MySQL connect to different filesystems (mostly in read-only mode). I've tried setting the read-only attribute of my files under my database directory under c:\mysql\data and MySQL seems to be able to SELECT it with no problem (I then tried doing an INSERT and it came up with an error message, which is perfect). Hopefully, this would also work if my database directory is in CDROM even if the MySQL binaries are in writable hard disk. My question is: Is it possible to have a single instance of MySQL where each connection might have a different data directory? For example, I have a user that needs to query data from a CDROM, and there's another one that needs to query some tape backup. If not, I am assuming it should be OK to run multiple instances of MySQL (on different ports) and just have them point to different data paths (unless you guys know of a more elegant solution). Thanks! Marlon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB, Replication, and Data warehouse: Oil, Water, and little floating plastic men
OS: RedHat Enterprise ES 2.1 Current working InnoDB settings: innodb_data_home_dir = /home/mysql innodb_data_file_path = ibdata1:2M:autoextend innodb_log_group_home_dir = /home/mysql innodb_log_arch_dir = /home/mysql innodb_log_files_in_group=2 # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 512M innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 512M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 0 innodb_lock_wait_timeout = 50 Error(using 2.5G RAM out of 4G total): 030924 15:39:55 mysqld started Warning: Ignoring user change to 'mysql' because the user was set to 'mysql' earlier on the command line InnoDB: Fatal error: cannot allocate 2684370944 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 24482732 bytes. Operating system errno: 12 InnoDB: Cannot continue operation! InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. InnoDB: We now intentionally generate a seg fault so that InnoDB: on Linux we get a stack trace. mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=134217728 read_buffer_size=2093056 max_used_connections=0 max_connections=800 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 3404665 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. I talked with RedHat about any OS limitations, and they had me change the max shared to a suitable number, and still I am stuck at 512MB of RAM. Top output: 3:43pm up 23:24, 2 users, load average: 0.00, 0.00, 0.00 36 processes: 35 sleeping, 1 running, 0 zombie, 0 stopped CPU0 states: 0.0% user, 0.0% system, 0.0% nice, 100.0% idle CPU1 states: 0.0% user, 0.0% system, 0.0% nice, 100.0% idle CPU2 states: 0.0% user, 0.0% system, 0.0% nice, 100.0% idle CPU3 states: 0.0% user, 0.0% system, 0.0% nice, 100.0% idle Mem: 3943852K av, 165796K used, 3778056K free, 0K shrd, 40760K buff Swap: 2044072K av, 0K used, 2044072K free 48972K cached -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 3:28 PM To: Misaochankun Cc: [EMAIL PROTECTED] Subject: Re: InnoDB, Replication, and Data warehouse: Oil, Water, and little floating plastic men On Wed, Sep 24, 2003 at 03:03:20PM -0700, Misaochankun wrote: MySQL tells me at startup that it can not allocate more than 512MB of RAM. It will fail to start the server if I specify any further. On what OS? Can we see the exact error? Have you checked things like ulimit? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 10 days, processed 393,361,824 queries (416/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Different DB directories against one MySQL
Thanks Kevin! I see what you're saying. That would mean I would have a common 'mysql' database though (for the db, user, func tables, etc). I thinks that's acceptable in my case. (I assume I don't need to update these tables if I want to add a 'link' to a new database somewhere, and that 'USE' would auto-detect any new directory.) I'll probably do a little experimenting in Windows soon (since I'm trying to be portable, or maybe I should stop supporting Windows, hmm). Thanks again! Marlon -Original Message- From: Kevin [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 6:39 PM To: Baculio, Marlon; [EMAIL PROTECTED] Subject: RE: Different DB directories against one MySQL Hi Marlon, Under linux mysql will 'see' any databases you've got links in the folder you've configured it to look. So in my /var/lib/mysql I have a symlink to /db/Allpoetry, and it knows to look there for the data files. So each database can have its own directory. You could probably even symlink the tables to files on other odd places if you wanted too. I assume the same thing is possible under windows. I know cygwin does symlinks, but don't know if the windows mysql will follow them, or if it follows shortcuts otherwise anyways. Not sure what the efficiency problems of running a database from a cdrom would be either, but it'd be an interesting experiment :) Ciao, Kevin Watt Community Manager, Allpoetry.com What happened to the cow who went for a drive? He got a Moo_ving violation What do you call someone who is crazy about hot chocolate? A cocoa nut What do bees use to cut wood? Buzz saws Who eats at underwater resturants ? Suba diners How do really small people call each other ? On Microphones How do you fix a broken chimp? With a monkey wrench -Original Message- From: Baculio, Marlon [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 3:32 PM To: [EMAIL PROTECTED] Subject: Different DB directories against one MySQL I'm trying to have MySQL connect to different filesystems (mostly in read-only mode). I've tried setting the read-only attribute of my files under my database directory under c:\mysql\data and MySQL seems to be able to SELECT it with no problem (I then tried doing an INSERT and it came up with an error message, which is perfect). Hopefully, this would also work if my database directory is in CDROM even if the MySQL binaries are in writable hard disk. My question is: Is it possible to have a single instance of MySQL where each connection might have a different data directory? For example, I have a user that needs to query data from a CDROM, and there's another one that needs to query some tape backup. If not, I am assuming it should be OK to run multiple instances of MySQL (on different ports) and just have them point to different data paths (unless you guys know of a more elegant solution). Thanks! Marlon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB, Replication, and Data warehouse: Oil, Water, and little floating plastic men
On Wed, Sep 24, 2003 at 03:45:22PM -0700, Misaochankun wrote: OS: RedHat Enterprise ES 2.1 I talked with RedHat about any OS limitations, and they had me change the max shared to a suitable number, and still I am stuck at 512MB of RAM. It's clearly an OS bug of some kind. I've used well over 512MB on Linux, FreeBSD, and others (Solaris?) before. I'd bang harder on the RedHat support folks. It's their job to know this stuff, right? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 10 days, processed 394,237,705 queries (415/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Provider error '80040e37'
Will this help anyone? Set objRS = CreateObject(ADODB.RecordSet) =objRS.Open SELECT Questions, Answers, Picture FROM FAQ ORDER BY OrderID, Provider=MySQLProv;Data Source=DSN=database, 2 objRS.Close Set objRS = Nothing The Questions and Answers columns are the text/blobs. Picture is a varchar. OrderID is an int. Thanks again, Robert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database corruption
We had the same problem when running on linux with the 2.4.18 kernel. Upgraded to the 2.4.20 and no problems since. Don't know if that's your problem, since you didnt mention kernel versions. Cheers Terence - Original Message - From: Quentin Bennett [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 25, 2003 6:21 AM Subject: Database corruption Hi, Advice on the cause of the following table corruption would be much appreciated. Some background: This customer has been running MySQL for 3 or more years, and, for back up purposes, shuts MySQL down each night, takes a snapshot of the tables for backup, and restarts. We have sufferred a table corruption before, of the same table, when the table was an ISAM table under version 3.23.40. We have since upgraded to 4.0.13, and changed the table to MyISAM type. Searching the list archive for 'Wrong bytesec' on mysql.com returns no results. Google returns some, but mainly old ones. Maybe an appendix to the manual giving some information about the possible things that myisamchk can find/repair would be useful? Are there any known problems with shutting down and re-starting so regularly? Would a flush-tables followed by a check that there are no open tables achieve the same result. Is there a way of stopping new connections to a running server? mysql REPAIR TABLE tt_tickets_bck and # myisamchk -r tt_tickets_bck both fixed the issue, but I'm being asked why the corruption happened in the first place. Myisamchk -vvv resulted in 212424 lines of information. Thanks in advance Quentin Bennett # mysqladmin version mysqladmin Ver 8.40 Distrib 4.0.13, for dec-osf5.1 on alphaev67 (-- Binaries from mysql.com --) Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.13-max-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /data/mysql/mysql.sock Uptime: 1 hour 21 min 23 sec Threads: 37 Questions: 42387 Slow queries: 16 Opens: 258 Flush tables: 2 Open tables: 113 Queries per second avg: 8.681 mysql check table tt_tickets_bck; ++---+--+--- + | Table | Op| Msg_type | Msg_text | ++---+--+--- + | mercury.tt_tickets_bck | check | warning | Table is marked as crashed | | mercury.tt_tickets_bck | check | warning | 2 clients is using or hasn't closed the table properly| | mercury.tt_tickets_bck | check | warning | Size of datafile is: 290516700 Should be: 285712092 | | mercury.tt_tickets_bck | check | error| Unexpected byte: 5 at link: 285506400 | | mercury.tt_tickets_bck | check | error| Corrupt | ++---+--+--- + 5 rows in set (58.28 sec) # myisamchk -vvv -r tt_tickets_bck - recovering (with sort) MyISAM-table 'tt_tickets_bck' Data records: 5641601 - Fixing index 1 - Searching for keys, allocating buffer for 46268 keys Wrong bytesec: 31- 0- 0 at304; Skipped Wrong bytesec: 49- 55- 53 at320; Skipped Wrong bytesec: 5- 87- 50 at328; Skipped Wrong bytesec: 48- 53- 55 at 285506420; Skipped . . . Wrong bytesec: 4- 49- 53 at692; Skipped Wrong bytesec: 5- 87- 50 at700; Skipped Found block with impossible length 3289448 at 285506560; Skipped Wrong bytesec: 27- 0- 0 at 1080; Skipped Wrong bytesec: 56- 54- 48 at 1096; Skipped Wrong bytesec: 87- 50- 29 at 1108; Skipped . . . Wrong bytesec: 27- 0- 0 at 5080; Skipped Wrong bytesec: 53- 53- 55 at 5096; Skipped Wrong bytesec: 87- 50- 29 at 5108; Skipped Found block with too small length at 5116; Skipped Wrong bytesec: 27- 0- 0 at 5120; Skipped Wrong bytesec: 53- 53- 55 at 5136; Skipped . . . Wrong bytesec: 56- 50- 56 at5762080; Skipped Wrong bytesec: 87- 50- 29 at5762092; Skipped Delete link points outside datafile at 285512060 Wrong bytesec: 27- 0- 0 at5762148; Skipped Wrong bytesec: 54- 57- 57 at5762164; Skipped . . Wrong bytesec: 200- 3- 1 at 290516684; Skipped - Merging 5621599 keys - Last merge and dumping keys - Fixing index 2 - Searching for keys, allocating buffer for 139678 keys - Merging 5621599 keys - Last merge and dumping keys - Fixing index 3 - Searching for keys, allocating buffer for 104727 keys - Merging 5621599 keys - Last merge and dumping keys Data records: 5621599 # Quentin Bennett Senior Analyst Infinity Solutions Ltd PO Box 3323, Auckland Ph: 09 921 8146 Fx: 09 309 4142 www.infinitytransport.net The information contained in this email is
RE: Database corruption
Hi, Compaq Tru64 UNIX V5.1 (Rev. 732) so possibly not the same issue! Thanks Quentin Bennett Senior Analyst Infinity Solutions Ltd PO Box 3323, Auckland Ph: 09 921 8146 Fx: 09 309 4142 www.infinitytransport.net -Original Message- From: Terence [mailto:[EMAIL PROTECTED] Sent: Thursday, 25 September 2003 2:05 p.m. To: [EMAIL PROTECTED] Subject: Re: Database corruption We had the same problem when running on linux with the 2.4.18 kernel. Upgraded to the 2.4.20 and no problems since. Don't know if that's your problem, since you didnt mention kernel versions. Cheers Terence - Original Message - From: Quentin Bennett [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 25, 2003 6:21 AM Subject: Database corruption Hi, Advice on the cause of the following table corruption would be much appreciated. Some background: This customer has been running MySQL for 3 or more years, and, for back up purposes, shuts MySQL down each night, takes a snapshot of the tables for backup, and restarts. We have sufferred a table corruption before, of the same table, when the table was an ISAM table under version 3.23.40. We have since upgraded to 4.0.13, and changed the table to MyISAM type. Searching the list archive for 'Wrong bytesec' on mysql.com returns no results. Google returns some, but mainly old ones. Maybe an appendix to the manual giving some information about the possible things that myisamchk can find/repair would be useful? Are there any known problems with shutting down and re-starting so regularly? Would a flush-tables followed by a check that there are no open tables achieve the same result. Is there a way of stopping new connections to a running server? mysql REPAIR TABLE tt_tickets_bck and # myisamchk -r tt_tickets_bck both fixed the issue, but I'm being asked why the corruption happened in the first place. Myisamchk -vvv resulted in 212424 lines of information. Thanks in advance Quentin Bennett # mysqladmin version mysqladmin Ver 8.40 Distrib 4.0.13, for dec-osf5.1 on alphaev67 (-- Binaries from mysql.com --) Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.13-max-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /data/mysql/mysql.sock Uptime: 1 hour 21 min 23 sec Threads: 37 Questions: 42387 Slow queries: 16 Opens: 258 Flush tables: 2 Open tables: 113 Queries per second avg: 8.681 mysql check table tt_tickets_bck; ++---+--+- -- + | Table | Op| Msg_type | Msg_text | ++---+--+- -- + | mercury.tt_tickets_bck | check | warning | Table is marked as crashed | | mercury.tt_tickets_bck | check | warning | 2 clients is using or hasn't closed the table properly| | mercury.tt_tickets_bck | check | warning | Size of datafile is: 290516700 Should be: 285712092 | | mercury.tt_tickets_bck | check | error| Unexpected byte: 5 at link: 285506400 | | mercury.tt_tickets_bck | check | error| Corrupt | ++---+--+- -- + 5 rows in set (58.28 sec) # myisamchk -vvv -r tt_tickets_bck - recovering (with sort) MyISAM-table 'tt_tickets_bck' Data records: 5641601 - Fixing index 1 - Searching for keys, allocating buffer for 46268 keys Wrong bytesec: 31- 0- 0 at304; Skipped Wrong bytesec: 49- 55- 53 at320; Skipped Wrong bytesec: 5- 87- 50 at328; Skipped Wrong bytesec: 48- 53- 55 at 285506420; Skipped . . . Wrong bytesec: 4- 49- 53 at692; Skipped Wrong bytesec: 5- 87- 50 at700; Skipped Found block with impossible length 3289448 at 285506560; Skipped Wrong bytesec: 27- 0- 0 at 1080; Skipped Wrong bytesec: 56- 54- 48 at 1096; Skipped Wrong bytesec: 87- 50- 29 at 1108; Skipped . . . Wrong bytesec: 27- 0- 0 at 5080; Skipped Wrong bytesec: 53- 53- 55 at 5096; Skipped Wrong bytesec: 87- 50- 29 at 5108; Skipped Found block with too small length at 5116; Skipped Wrong bytesec: 27- 0- 0 at 5120; Skipped Wrong bytesec: 53- 53- 55 at 5136; Skipped . . . Wrong bytesec: 56- 50- 56 at5762080; Skipped Wrong bytesec: 87- 50- 29 at5762092; Skipped Delete link points outside datafile at 285512060 Wrong bytesec: 27- 0- 0 at5762148; Skipped Wrong bytesec: 54- 57- 57 at5762164; Skipped . . Wrong bytesec: 200- 3- 1 at
Re: 300 seconds in sending data phase
*** Is everyone else getting ~50% virus e-mails on the list?? :-( *** Hi Kevin, Don't know what's causing the hang-up on those queries, as they seem like they should be fast as you said (unless it's some mod_perl/DBI interaction -- no idea). :-/ Just responding to 2 other things... - Original Message - From: Kevin Sent: Wednesday, September 24, 2003 5:34 PM Subject: 300 seconds in sending data phase Hello, [ snip ] The long 'sending data' phase seems to happen for many different queries. The explain for the top query shows: + | table | type | possible_keys | key | key_len | ref| rows | +---++---+---+-++--- -- | bids | range | remaining | remaining | 2 | NULL | 192 | Using where; Using filesort | | poets | eq_ref | PRIMARY | PRIMARY | 3 | bids.owner | 1 | | | poems | eq_ref | PRIMARY | PRIMARY | 3 | bids.lid | 1 | | +---++---+---+-++--- --- Sure, it's a filesort, but only 192 rows so should be no big deal. (Side note: the 'remaining' key is on (remaining, amt), so shouldn't it be using it and not filesorting?). No, because the bids table is range type (because of WHERE remaining 0). It would need to be ref type to use the index for sorting (WHERE remaining=123 or something). This is because the index is only sorted by amt for each unique remaining prefix value. (Note that before v4, MySQL will not use the index for ORDER BY with queries of type ... WHERE key_part1=const ORDER BY key_part2 DESC. :-() Which is strange too because it has way more processes than apache can be using (should be only one persistent connection per process) Should it? Or is it one connection per MySQL user, per process? I'm not sure. Are you connecting with more than 1 MySQL user? BTW, this is why I don't like persistent connections. :-) Too many useless connections around that can't be used in a shared pool... Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
auto increment
I have a id_entry field that is doing auto increment on that field. Now, when i try to delete the numbers it still continues to count from where it left. ex: i have entrys 1|one 2|two once i deleted 1|one on the field or even 2|two it still counts to 3|three and so on And is it also possible to change the values, to 2003-01 and so on.. Im tired just using the numbers from 1 and so on.. - Louie http://www.axishift.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: auto increment
Hi, Check the behaviour of auto_increment in the change logs - it changed from using the highest available number to carrying on from where it left off some time ago, but I believe there is a was to reset it manually. Hope this helps. Quentin Bennett Senior Analyst Infinity Solutions Ltd PO Box 3323, Auckland Ph: 09 921 8146 Fx: 09 309 4142 www.infinitytransport.net -Original Message- From: Louie Miranda [mailto:[EMAIL PROTECTED] Sent: Thursday, 25 September 2003 2:58 p.m. To: [EMAIL PROTECTED] Subject: auto increment I have a id_entry field that is doing auto increment on that field. Now, when i try to delete the numbers it still continues to count from where it left. ex: i have entrys 1|one 2|two once i deleted 1|one on the field or even 2|two it still counts to 3|three and so on And is it also possible to change the values, to 2003-01 and so on.. Im tired just using the numbers from 1 and so on.. - Louie http://www.axishift.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql Problem in Windows 2000 advanced server
Hi, I installed mysql-4.0.15 in Windows 2000 Advanced server. Mysql-max-nt is working fine, except some problems. Sometimes when I try to use access web pages through php pages, mysql says Connection to server lost or Can't connect to mysql server or sometimes query error. Query is tested and works fine most of the time. And when I refresh web page again everything works fine. Mysql log says nothing. Is it problem related to mysql in Windows? Also I tested small C program to query mysql tables and it sometimes hangs. Is there any solution for this kind of problem? The following is the my.ini file: - [mysqld] skip-locking set-variable = connect_timeout=30 set-variable = net_retry_count=100 set-variable= key_buffer=384M set-variable= max_allowed_packet=1M set-variable= table_cache=3M set-variable= sort_buffer=2M set-variable= record_buffer=2M set-variable= thread_cache=8 set-variable= thread_concurrency=8 set-variable= myisam_sort_buffer_size=64M server-id = 1 set-variable= max_connections=5000 set-variable= query_cache_size=8M set-variable= query_cache_limit=8M set-variable= thread_cache_size=2M basedir=D:/mysql datadir=D:/mysql/data log=general.log log-update=update.log myisam-recover=FORCE [WinMySQLadmin] Server=D:/mysql/bin/mysqld-max-nt.exe user=root password=xxx - thanks in advance, Ganbold -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Provider error '80040e37'
Robert, ADO seems to prefer blobs to be at the end of the select. Try the following to see if it works: Select Picture, Questions, Answers FROM FAQ ORDER by OrderID Dennis -Original Message- From: Helpdesk3 ( Mid North Coast Internet ) [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 24, 2003 6:50 PM To: [EMAIL PROTECTED] Subject: Re: Provider error '80040e37' Will this help anyone? Set objRS = CreateObject(ADODB.RecordSet) =objRS.Open SELECT Questions, Answers, Picture FROM FAQ ORDER BY OrderID, Provider=MySQLProv;Data Source=DSN=database, 2 objRS.Close Set objRS = Nothing The Questions and Answers columns are the text/blobs. Picture is a varchar. OrderID is an int. Thanks again, Robert. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB, Replication, and Data warehouse: Oil, Water, and little floating plastic men
On Wed, 24 Sep 2003, Misaochankun wrote: Error(using 2.5G RAM out of 4G total): 030924 15:39:55 mysqld started Warning: Ignoring user change to 'mysql' because the user was set to 'mysql' earlier on the command line InnoDB: Fatal error: cannot allocate 2684370944 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 24482732 bytes. Operating system errno: 12 InnoDB: Cannot continue operation! If you set a smaller size and start mysql, so it is running successfully, how much memory does top and ps show mysqld as using? If your processes are getting close to 2 gigs of RAM total, that may be There are other parts of mysql that can be configured to use (sometimes a lot of) memory. the limit you are running into. By default on Linux, a process can only allocate about 1.9 gigs of memory using mmap(), which is what malloc() is probably using for large allocations. redhat may tweak these values in their kernels, but I don't know... that is the default limit on 2.4.x kernels anyway. You can tweak this to go higher, but only with modifying the kernel source. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
