Re: Random 'select permission denied' since upgrade to 5.0.18
[EMAIL PROTECTED] wrote: is this relevant ? We've been looking at connection graphs, but MySQL doesn't seem to reach that limit. However, these are timely based measurements, so it could've peaked in between, although highly unlikely. I'm not very comfortable tweaking these values in a production environment while I have no indication that it could solve the problem. Jorrit -- System Developer Infopact Network Solutions Hoogvlietsekerkweg 170 3194 AM Rotterdam Hoogvliet tel. +31 (0)88 - 4636700 fax. +31 (0)88 - 4636799 mob. +31 (0)6 - 14105968 [EMAIL PROTECTED] http://www.infopact.nl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Customer Recommendation Query
You haven't told how many members are there. Anyway... i've worked out something with a colleague at work... Given your tables: *CREATE TABLE `list_problem_members` ( `id` int(11) NOT NULL auto_increment, `member` varchar(11) NOT NULL default '', PRIMARY KEY (`id`) );* ** *CREATE TABLE `list_problem_actions` ( `id` int(11) NOT NULL auto_increment, `action` varchar(11) NOT NULL default '', PRIMARY KEY (`id`) );* ** *CREATE TABLE `list_problem_ma` ( `id` int(11) NOT NULL auto_increment, `member` int(11) unsigned NOT NULL default '0', `action` int(11) unsigned NOT NULL default '0', PRIMARY KEY (`id`) );* We came up with this: *SELECT b.`action`, count(b.`action`) FROM `list_problem_ma` AS a RIGHT JOIN `list_problem_ma` AS b ON b.`member`=a.`member` WHERE a.`action`=3 AND b.`action`!=3 GROUP BY b.`action`* ** This will give you a result as: *action - count(b.`action`) 1 - 3 2 - 1* This shows that action 1 was performed 3 times and action 2 one time ! *Is this what you wanted ?* In the area of optimizations... a key in *`list_problem_ma`* table on the columns *`member` *AND* `action`* will do fine because MySQL will not scan NR_OF_ROWS(`list_problem_ma`) * NR_OF_ROWS(`list_problem_ma`) but only NR_OF_ROWS(`list_problem_ma`) * 1 You will still get: Using where; Using index; Using temporary; Using filesort ... but i believe that's no way arround that ! -- Gabriel PREDA Senior Web Developer
Is mysql support ipv6?
I am very sorry for ask the question. I want to know mysql support ipv6 or not if support, which is the version from best regards! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is mysql support ipv6?
zhengjc wrote: I am very sorry for ask the question. I want to know mysql support ipv6 or not if support, which is the version from best regards! Here, this might help: http://www.ngn.euro6ix.org/IPv6/mysql/ Regards Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlcc temp table created for queries
Song Ken Vern-E11804 wrote:
It seems like different version are doing things differently.
This is the most normal thing in the world!
Looking through the newsgroups forums, I understand that that mysql5.0
will
prepend the tablename to each column.
But I don't understand what does 'test.1' refer to.
Test.1 is table 1 of the Database test.
database test
Table {1}
Table {anyname}
Table {insertnamehere}
Like: SELECT * FROM 1
i can't tell you what exactly is causing that error but i think
somewheer in that code there is a bug which causes this.
Regards
Barry
--
Smileys rule (cX.x)C --o(^_^o)
Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mac() from a subset
Sandy wrote: Hi code snippet select max(f1) as nf1 , max(f2) as nf2, max(f3) as nf3, max(f4) as nf4 from table /code How can I extract a max value from the 4 columns of the result ? ex: greatest(nf1,nf2,nf3,nf4) Thanks SELECT MAX (max(f1) as nf1 , max(f2) as nf2, max(f3) as nf3, max(f4) as nf4) AS maximum FROM table Not tested but should work :) -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple-Master Replication recovery
古雷 wrote: Hello: If I use Multiple-Master Replication with two mysql server, when one of them goes down(disk crashed) must I shutdown the good one to recover the Multiple-Master Replication ? I think yes. Depends on what you mean with recover the Multiple-Master Replication Do you want to have the server with the new disk behave again as the replicant? Normally you can switch it on demand but yeah i would prefer to shut it down and start it after you have set it up. Regards Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0.18 crashing on AMD64
Quoting Pete Harlan ([EMAIL PROTECTED]): MySQL version 5.0.18 | Mar 14 00:32:59 zwart mysqld[29820]: *** glibc detected *** double | free or corruption (!prev): 0x012b1ab0 *** You don't say which versions of glibc or the kernel you're running, but if you're running stock Debian Sarge, that's a problem because of its known-buggy glibc 2.3.2. Sorry. I forgot about that important information. The servers are running 2.6.15.x and Debian sid, updated to the newest libc6 (2.3.6) available. It does look like it was some problem with mysql's binaries on AMD64, since after upgrading to 5.0.19 it looks to be working again. It just apears weird to me that only this server would crash mysql :| The other AMD64 which has the same config, same kernel, same libc6, didn't crash. Though that mysql was only used for selects and not inserts updates and crazy select with joins, group by's etc. But what can the message indicate? All I got from Google was that it probably had to do with buggy memory. But i ran memtest86+ on the server and it had 17 passes with zero errors... :| HTH, Sander. -- | A balanced diet is a cookie in each hand. | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb errors on startup
Hello Heikki, can you email the complete .err log from the server to [EMAIL PROTECTED] the complete log is about 50 mb, since a lot of errors occur. I am interested in what caused the very first crash in the server. Now your database seems to be seriously corrupt, since the log sequence number in the log files is only 14 MB, while it is = 153 MB in a data file! We had a crash of the filesystem on one server so we moved all databases to a new server by copying the files. I didn't copy the ib-files because I didn't know that they are indispensable since I created all innodb-tables with innodb_file_per_table. When I restored some databases that didn't work correct, I simply removed the files from the databases-directory, which also wasn't a good idea as I know now, at least regarding innodb-table (most tables are thankfully in myisam-format). This server is hosting a few hundrets of databases (we are doing shared hosting), so it's not so easy to recover the ib-files and keep all databases running. Are you using some exotic file system? No, ext3. The output looks like ib_logfiles and data files from different servers would be mixed. Thats not exactly the way it happened, but it may have the same result. Since the dictionary only has problems with some certain databases, we simply created a new databases and restored the db-dump into it. So the databases are mostly working, but the errors on startup appear anyhow. Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random 'select permission denied' since upgrade to 5.0.18
Hi Jorrit, Jorrit Kronjee wrote: [EMAIL PROTECTED] wrote: is this relevant ? We've been looking at connection graphs, but MySQL doesn't seem to reach that limit. However, these are timely based measurements, so it could've peaked in between, although highly unlikely. I'm not very comfortable tweaking these values in a production environment while I have no indication that it could solve the problem. Jorrit It seems you are running in to Bug #7209: http://bugs.mysql.com/bug.php?id=7209 This is fixed in 5.0.19 now. Best regards Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? 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: innodb errors on startup
Marten, Marten Lehmann wrote: Hello Heikki, can you email the complete .err log from the server to [EMAIL PROTECTED] the complete log is about 50 mb, since a lot of errors occur. I am interested in what caused the very first crash in the server. Now your database seems to be seriously corrupt, since the log sequence number in the log files is only 14 MB, while it is = 153 MB in a data file! We had a crash of the filesystem on one server so we moved all databases to a new server by copying the files. I didn't copy the ib-files because I didn't know that they are indispensable since I created all innodb-tables with innodb_file_per_table. When I restored some databases ok, we need to stress this more in the manual. A few users have misunderstood that ibdata files would no longer be needed if one uses innodb_file_per_table. ib_logfiles are always needed. How else can InnoDB recover after a crash. that didn't work correct, I simply removed the files from the databases-directory, which also wasn't a good idea as I know now, at least regarding innodb-table (most tables are thankfully in myisam-format). This server is hosting a few hundrets of databases (we are doing shared hosting), so it's not so easy to recover the ib-files and keep all databases running. Are you using some exotic file system? No, ext3. The output looks like ib_logfiles and data files from different servers would be mixed. Thats not exactly the way it happened, but it may have the same result. Since the dictionary only has problems with some certain databases, we simply created a new databases and restored the db-dump into it. So the databases are mostly working, but the errors on startup appear anyhow. Regards Marten Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Random 'select permission denied' since upgrade to 5.0.18
Mark Leith wrote: Hi Jorrit, Jorrit Kronjee wrote: [EMAIL PROTECTED] wrote: is this relevant ? We've been looking at connection graphs, but MySQL doesn't seem to reach that limit. However, these are timely based measurements, so it could've peaked in between, although highly unlikely. I'm not very comfortable tweaking these values in a production environment while I have no indication that it could solve the problem. Jorrit It seems you are running in to Bug #7209: http://bugs.mysql.com/bug.php?id=7209 This is fixed in 5.0.19 now. Best regards Mark Mark, Apparently so, thanks for the hint! We'll try to upgrade as soon as possible. I'll supply the mailing list with the results of the upgrade. Jorrit -- System Developer Infopact Network Solutions Hoogvlietsekerkweg 170 3194 AM Rotterdam Hoogvliet tel. +31 (0)88 - 4636700 fax. +31 (0)88 - 4636799 mob. +31 (0)6 - 14105968 [EMAIL PROTECTED] http://www.infopact.nl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mac() from a subset
I have found the answer snip select max(greatest(f1,f2,f3 .)) from table /snip Sandy [EMAIL PROTECTED] a écrit dans le message de news: [EMAIL PROTECTED] Hi code snippet select max(f1) as nf1 , max(f2) as nf2, max(f3) as nf3, max(f4) as nf4 from table /code How can I extract a max value from the 4 columns of the result ? ex: greatest(nf1,nf2,nf3,nf4) 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 5.0.19-2 and repair problems...
Hello! I'm having a weird problem i'd like to hear your opinions about: | [18:44] [EMAIL PROTECTED]:~] # mysqlcheck v games | v.games | error: Table './v/games' is marked as crashed and should be repaired | [18:44] [EMAIL PROTECTED]:~] # mysqlcheck -r v games | v.gamesOK | [18:44] [EMAIL PROTECTED]:~] # mysqlcheck v games | v.games | error: Key in wrong position at page 108544 | error: Corrupt | [18:45] [EMAIL PROTECTED]:~] # mysqlcheck -f -r v games | v.gamesOK | [18:45] [EMAIL PROTECTED]:~] # mysqlcheck v games | v.games | error: Key in wrong position at page 108544 | error: Corrupt | [18:45] [EMAIL PROTECTED]:~] # /etc/init.d/mysql stop | Stopping MySQL database server: mysqld. | [18:45] [EMAIL PROTECTED]:~] # cd /var/lib/mysql/v | [18:45] [EMAIL PROTECTED]:/var/lib/mysql/v] # myisamchk -r games.MYI | - recovering (with sort) MyISAM-table 'games.MYI' | Data records: 10644 | - Fixing index 1 | - Fixing index 2 | [18:48] [EMAIL PROTECTED]:/var/lib/mysql/v] # echo REPAIR TABLE games | mysql v | Table Op Msg_typeMsg_text | v.games repair status OK | [18:48] [EMAIL PROTECTED]:/var/lib/mysql/v] # myisamchk games.MYI | Checking MyISAM file: games.MYI | Data records: 10644 Deleted blocks: 0 | - check file-size | - check record delete-chain | - check key delete-chain | - check index reference | - check data record references index: 1 | - check data record references index: 2 | myisamchk: error: Key in wrong position at page 108544 | - check record links | myisamchk: error: Keypointers and record positions doesn't match | MyISAM-table 'games.MYI' is corrupted | Fix it using switch -r or -o | [1]17564 exit 255 myisamchk games.MYI No matter WHAT i try, fixing with 'repair table' using mysqlcheck, myisamchk, -o, -r, -f, it seems like i can't repair this table. So what's going on? Does anyone have any clue? Regards, Sander. -- | Honk if you love peace and quiet. | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple-Master Replication recovery
any one of the servers could go down in many ways like 1) disk crash 2) replication failure 3) power failure 4) any hardware component failure 5) OS hang 6) Network failure 7) MYSQL bug 8) table corruption etc ... 9) or just scheduled donwtime in any case what really matters is the difference in status of the failed server from the point of failure to the point of recovery , so it all depends on how up-to-date your data is on the failed server.. here are some typical cases .. 1) you have everything intact ( data, bin logs, *.info files, etc ) then just bring back the failed server online and you are good to go 2) if you rebuild the server from scratch it depends on how current your backup is , in this take a snapshot of the main server ( either hot or cold depending on your setup) and start the multi-master replication .. In theory you really dont have to take the server offline unless it really demands , in the worst case as Barry said its lot easier to shut down the master (if you can afford some downtime) take a complete snapshot/backup/dump ( varies for MYISAM and INNODB) and bring back the failed server back online Hope this helps Kishore Jalleda On 3/28/06, 古雷 [EMAIL PROTECTED] wrote: Hello: If I use Multiple-Master Replication with two mysql server, when one of them goes down(disk crashed) must I shutdown the good one to recover the Multiple-Master Replication ? regards, gu lei
Re: MySQL 5.0.19-2 and repair problems...
That usually means the table is corrupt beyond repair and nothing is really fixing it or there is something one cannot easily comprehend ( this usually happens with inconsistency among deleted records and some kind of mismatch that occurs) -anyway what I would really advice in this case is to rebuild the table from a working/clean backup and start-over, if this is a slave then thats very easy to do, if not it depends on your latest clean backup available .. Just my 2c Kishore Jalleda On 3/29/06, Sander Smeenk [EMAIL PROTECTED] wrote: Hello! I'm having a weird problem i'd like to hear your opinions about: | [18:44] [EMAIL PROTECTED]:~] # mysqlcheck v games | v.games | error: Table './v/games' is marked as crashed and should be repaired | [18:44] [EMAIL PROTECTED]:~] # mysqlcheck -r v games | v.gamesOK | [18:44] [EMAIL PROTECTED]:~] # mysqlcheck v games | v.games | error: Key in wrong position at page 108544 | error: Corrupt | [18:45] [EMAIL PROTECTED]:~] # mysqlcheck -f -r v games | v.gamesOK | [18:45] [EMAIL PROTECTED]:~] # mysqlcheck v games | v.games | error: Key in wrong position at page 108544 | error: Corrupt | [18:45] [EMAIL PROTECTED]:~] # /etc/init.d/mysql stop | Stopping MySQL database server: mysqld. | [18:45] [EMAIL PROTECTED]:~] # cd /var/lib/mysql/v | [18:45] [EMAIL PROTECTED]:/var/lib/mysql/v] # myisamchk -r games.MYI | - recovering (with sort) MyISAM-table 'games.MYI' | Data records: 10644 | - Fixing index 1 | - Fixing index 2 | [18:48] [EMAIL PROTECTED]:/var/lib/mysql/v] # echo REPAIR TABLE games | mysql v | Table Op Msg_typeMsg_text | v.games repair status OK | [18:48] [EMAIL PROTECTED]:/var/lib/mysql/v] # myisamchk games.MYI | Checking MyISAM file: games.MYI | Data records: 10644 Deleted blocks: 0 | - check file-size | - check record delete-chain | - check key delete-chain | - check index reference | - check data record references index: 1 | - check data record references index: 2 | myisamchk: error: Key in wrong position at page 108544 | - check record links | myisamchk: error: Keypointers and record positions doesn't match | MyISAM-table 'games.MYI' is corrupted | Fix it using switch -r or -o | [1]17564 exit 255 myisamchk games.MYI No matter WHAT i try, fixing with 'repair table' using mysqlcheck, myisamchk, -o, -r, -f, it seems like i can't repair this table. So what's going on? Does anyone have any clue? Regards, Sander. -- | Honk if you love peace and quiet. | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb errors on startup
Well, ok, we need to stress this more in the manual. A few users have misunderstood that ibdata files would no longer be needed if one uses innodb_file_per_table. ib_logfiles are always needed. How else can InnoDB recover after a crash. but how can I repair my existing ib-files so that the error on startup doesn't appear any more? Regards Marten -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Workbench Docs?
Are there any documents, help files, tutorials, or anything on the MySQL Workbench program? I've played with it, and just can't figure out how to use some of it's features. Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Expiration date on users utilizing freeradius and mysql
I am using freeradius with MySql and what I would like to do is create in my radius table an user with attributes stating a start and stop date. I would like to be able to do a bulk entry (more than 1 at a time) or would love for this to be web based. Is this process out there? Thanks Dwane
Re: Is there anyway to return an array?
Well, I'm doing something stupid because that is what the bosses want. I appreciate the suggestions, I think the CSV string format is the way to go. David Godsey David Godsey wrote: I know, I know, sounds like something that should be done in the presentation layer, howerver if possible, I would like to provide common data presentation to multiple presentation layers (written in different languages). So is there anyway to return an array in mysql? Your aware your doing something stupid and want to do it anyway :-( Why not return the values from your user defined mysql function as a (properly quoted) ,comma seperated list. Since almost every application language now has a standard csv file handling library it should be easy to use across diverse display technologies. Urrgh Nigel Accomplishing the impossible means only that the boss will add it to your regular duties. David Godsey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SQL Question: alternative to crazy left joins?
Thanks Shawn, Believe you me, I share your reaction to this architecture...I had to spend 2 hours coding a ruby script to get the data into the kludgy form needed for the data import (though I do find that thing kind of fun...but it's not the best use of my time on the job). Fortunately the data limit doesn't seem to be imposed by the apps underlying db schema, only by the importing process, as contacts can be associated with an unlimited number of donations in the program but only 10 donations can be imported at a time. Very few database apps I've used seem to be free from at least a few architectural or interface flaws such as this one, and so often it seems one needs to reach a compromise in order to settle on a reasonably priced piece of software with an intuitive, powerful interface and decent engineering. Still, though, I wonder if there's way to solve this problem using straight SQL. Ethan -- View this message in context: http://www.nabble.com/SQL-Question%3A-alternative-to-crazy-left-joins--t1357877.html#a3654455 Sent from the MySQL - General forum at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql performance problems.
After a 23days of running mysql, I have a 3GB database. When I use an application called base(v.1.2.2) a web based intrusion detection analysis console, the mysqld utilization shoots up to over 90% and stays there until the application times out or is terminated. Question: Have I made some error in configuration? When I don't run the application base, mysqld utilization is between 30-50%. Question: What hardware do I need to speed up queries? Question: How do determine if the query is the problem? Data: I used my-large.cnf as the basis of my.cnf. Hardware and OS info: ... FreeBSD 6.0-RELEASE-p5 #0: ... CPU: Intel Pentium III (997.46-MHz 686-class CPU) Origin = GenuineIntel Id = 0x68a Stepping = 10 Features=0x383fbffFPU,VME,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE,MCA,CMOV,PAT,PSE36,MMX,FXSR,SSE real memory = 1073676288 (1023 MB) avail memory = 1041784832 (993 MB) Observations: Disk Space used: du -am /var/db/mysql | sort -nr | head -20 5259mysql/ 3055mysql/snort 2184mysql/snort_archive 1546mysql/snort_archive/data.MYD 1546mysql/snort/data.MYD 560 mysql/snort/acid_event.MYI 311 mysql/snort/acid_event.MYD 132 mysql/snort_archive/event.MYI 132 mysql/snort/event.MYI 116 mysql/snort_archive/iphdr.MYI 116 mysql/snort/iphdr.MYI 112 mysql/snort_archive/iphdr.MYD 112 mysql/snort/iphdr.MYD 74 mysql/snort_archive/event.MYD 74 mysql/snort/event.MYD 42 mysql/snort_archive/data.MYI 42 mysql/snort/data.MYI 40 mysql/snort_archive/icmphdr.MYI 40 mysql/snort/icmphdr.MYI 35 mysql/snort_archive/icmphdr.MYD ... snort is 3GB snort_archive is 2GB(snort_archive acid and base tables have not been built that is why snort archive is smaller) When the application searches the database, the mysqld utilization goes up to over 90% until the application times out. top last pid: 44263; load averages: 0.95, 0.89, 0.76 up 25+23:49:4416:07:17 49 processes: 2 running, 47 sleeping Mem: 173M Active, 608M Inact, 186M Wired, 29M Cache, 111M Buf, 1660K Free Swap: 2048M Total, 156K Used, 2048M Free PID USERNAME THR PRI NICE SIZERES STATETIME WCPU COMMAND 31890 mysql 15 200 103M 79032K kserel 768:38 93.46% mysqld 49138 www 1 40 17432K 12848K accept 0:23 0.00% httpd 46759 www 1 200 16584K 12084K lockf0:21 0.00% httpd 46764 www 1 40 16632K 12072K accept 0:21 0.00% httpd 46763 www 1 40 16580K 12012K accept 0:20 0.00% httpd 46760 www 1 40 17452K 12872K accept 0:19 0.00% httpd 46762 www 1 40 16568K 12000K accept 0:19 0.00% httpd 46761 www 1 40 16608K 12088K sbwait 0:17 0.00% httpd 68456 www 1 40 16572K 11980K accept 0:17 0.00% httpd 68457 www 1 40 16724K 11824K accept 0:17 0.00% httpd 68458 www 1 40 16980K 11920K accept 0:17 0.00% httpd Processes that run in the background: I run an update process in the background with hope that if I process the alerts from the snort table on a regular basis.o I won't have process a large number( 44,000) alerts first thing in the morning. The update process inserts records into the acid table that result from the join of certain fields from the snort tables. (Schema at http://www.andrew.cmu.edu/user/rdanyliw/snort/acid_db_er_v102.html ) rabid# cat /var/log/base-update.2006-03-28.log 2006-03-28, 00:05:00, Added 3808 alert(s) to the Alert cache 2006-03-28, 01:05:00, Added 5855 alert(s) to the Alert cache 2006-03-28, 02:05:00, Added 4096 alert(s) to the Alert cache 2006-03-28, 03:05:00, Added 4473 alert(s) to the Alert cache 2006-03-28, 04:05:00, Added 4378 alert(s) to the Alert cache 2006-03-28, 05:05:00, Added 4087 alert(s) to the Alert cache 2006-03-28, 06:05:00, Added 5163 alert(s) to the Alert cache 2006-03-28, 07:05:00, Added 4789 alert(s) to the Alert cache 2006-03-28, 08:05:00, Added 4411 alert(s) to the Alert cache 2006-03-28, 09:05:00, Added 4830 alert(s) to the Alert cache 2006-03-28, 10:05:00, Added 4739 alert(s) to the Alert cache 2006-03-28, 11:05:00, Added 5360 alert(s) to the Alert cache 2006-03-28, 12:05:00, Added 7305 alert(s) to the Alert cache 2006-03-28, 13:05:00, Added 8481 alert(s) to the Alert cache 2006-03-28, 14:05:00, Added 60731 alert(s) to the Alert cache 2006-03-28, 15:05:00, Added 44328 alert(s) to the Alert cache 2006-03-28, 15:50:00, Added 13742 alert(s) to the Alert cache 2006-03-28, 15:55:00, Added 607 alert(s) to the Alert cache 2006-03-28, 16:00:00, Added 938 alert(s) to the Alert cache 2006-03-28, 16:05:00, Added 1348 alert(s) to the Alert cache 2006-03-28, 16:10:02, Added 687 alert(s) to the Alert cache 2006-03-28, 16:15:00, Added 1168 alert(s) to the Alert cache 2006-03-28, 16:20:00, Added 1760 alert(s) to the Alert cache 2006-03-28, 16:25:00, Added 814 alert(s) to the Alert cache 2006-03-28, 16:30:01, Added 617 alert(s) to the Alert cache
Re: MySQL 5.0.19-2 and repair problems...
Quoting Kishore Jalleda ([EMAIL PROTECTED]): That usually means the table is corrupt beyond repair and nothing is really fixing it or there is something one cannot easily comprehend ( this usually happens with inconsistency among deleted records and some kind of mismatch that occurs) -anyway what I would really advice in this case is to rebuild the table from a working/clean backup and start-over, if this is a slave then thats very easy to do, if not it depends on your latest clean backup available .. Amazing. Amazing that even the tools can't tell me it's unfixable. I mean, i tried everything. :) I'll restore the databases from the master (this isn't a slave, but it's a machine i want to switch to when it finally becomes stable...) and see where we get from that... The machine didn't crash. Nor did mysql. Any clue what might cause this to happen? Disk looks fine too, no read or write errors whatsoever... Thanks, Sander. -- | Just remember -- if the world didn't suck, we would all fall off. | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0.19-2 and repair problems...
Have you tried the following myisamchk option: --extend-check, -e Check the table very thoroughly. This is quite slow if the table has many indexes. This option should only be used in extreme cases. Normally, myisamchk or myisamchk --medium-check should be able to determine whether there are any errors in the table. Keith In theory, theory and practice are the same; in practice they are not. On Wed, 29 Mar 2006, Sander Smeenk wrote: To: mysql@lists.mysql.com From: Sander Smeenk [EMAIL PROTECTED] Subject: Re: MySQL 5.0.19-2 and repair problems... Quoting Kishore Jalleda ([EMAIL PROTECTED]): That usually means the table is corrupt beyond repair and nothing is really fixing it or there is something one cannot easily comprehend ( this usually happens with inconsistency among deleted records and some kind of mismatch that occurs) -anyway what I would really advice in this case is to rebuild the table from a working/clean backup and start-over, if this is a slave then thats very easy to do, if not it depends on your latest clean backup available .. Amazing. Amazing that even the tools can't tell me it's unfixable. I mean, i tried everything. :) I'll restore the databases from the master (this isn't a slave, but it's a machine i want to switch to when it finally becomes stable...) and see where we get from that... The machine didn't crash. Nor did mysql. Any clue what might cause this to happen? Disk looks fine too, no read or write errors whatsoever... Thanks, Sander. -- | Just remember -- if the world didn't suck, we would all fall off. | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D -- 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 5.0.19-2 and repair problems...
Quoting [EMAIL PROTECTED] ([EMAIL PROTECTED]): Have you tried the following myisamchk option: --extend-check, -e Yup. But that won't even work at ALL: # myisamchk -e games.MYI Checking MyISAM file: games.MYI Data records: 10644 Deleted blocks: 0 - check file-size - check record delete-chain - check key delete-chain - check index reference - check data record references index: 1 - check data record references index: 2 myisamchk: error: Key in wrong position at page 715776 - check records and index references myisamchk: error: Keypointers and record positions doesn't match MyISAM-table 'games.MYI' is corrupted Fix it using switch -r or -o Strange, huh! -- | Don't hate yourself in the morning -- sleep till noon. | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0.19-2 and repair problems...
what's the result of SHOW CREATE TABLE games ?? I'd drop the indexes on the games table, and then run repair tables, and then put the indexes back in. Worth a shot. -Sheeri On 3/29/06, Sander Smeenk [EMAIL PROTECTED] wrote: Quoting [EMAIL PROTECTED] ([EMAIL PROTECTED]): Have you tried the following myisamchk option: --extend-check, -e Yup. But that won't even work at ALL: # myisamchk -e games.MYI Checking MyISAM file: games.MYI Data records: 10644 Deleted blocks: 0 - check file-size - check record delete-chain - check key delete-chain - check index reference - check data record references index: 1 - check data record references index: 2 myisamchk: error: Key in wrong position at page 715776 - check records and index references myisamchk: error: Keypointers and record positions doesn't match MyISAM-table 'games.MYI' is corrupted Fix it using switch -r or -o Strange, huh! -- | Don't hate yourself in the morning -- sleep till noon. | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D -- 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 5.0.19-2 and repair problems...
Quoting sheeri kritzer ([EMAIL PROTECTED]): what's the result of SHOW CREATE TABLE games It gives me the create statement for that table. All perfectly normal. MyISAM table, latin1 charset. I'd drop the indexes on the games table, and then run repair tables, and then put the indexes back in. Worth a shot. Funny, i haven't tried that yet, and lo-and-behold, after rebuilding the index (that's all i did): | [22:27] [EMAIL PROTECTED]:/var/lib/mysql/v] # myisamchk games.MYI | Checking MyISAM file: games.MYI | Data records: 10644 Deleted blocks: 0 | - check file-size | - check record delete-chain | - check key delete-chain | - check index reference | - check data record references index: 1 | - check data record references index: 2 | - check record links Aparently, what i heard from a friend, it's not done to binary copy tables from 32bit systems over to 64bit systems. Is it as bad to binary copy tables from 64bit to 32bit systems? It's vital in our current setup that binary copy of tables works. Or we need to set up replication right now ;) Lemmeknow and Thanks!! Sander. -- | Lead me not into temptation... I can find it myself. | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0.19-2 and repair problems...
According to the manual, 64 to 32 bit shouldn't matter, it should work fine as long as they're both little endian, or they're both big endian. I've copied MyISAM tables from 64 to 32 bit without a problem. BTW, I suggested that because in the error it said: myisamchk: error: Key in wrong position at page 715776 - check records and index references Did you by any chance symlink or move files, or not move the key files? My guess is that might cause that problem, otherwise, I have no idea what would cause it. -Sheeri On 3/29/06, Sander Smeenk [EMAIL PROTECTED] wrote: Quoting sheeri kritzer ([EMAIL PROTECTED]): what's the result of SHOW CREATE TABLE games It gives me the create statement for that table. All perfectly normal. MyISAM table, latin1 charset. I'd drop the indexes on the games table, and then run repair tables, and then put the indexes back in. Worth a shot. Funny, i haven't tried that yet, and lo-and-behold, after rebuilding the index (that's all i did): | [22:27] [EMAIL PROTECTED]:/var/lib/mysql/v] # myisamchk games.MYI | Checking MyISAM file: games.MYI | Data records: 10644 Deleted blocks: 0 | - check file-size | - check record delete-chain | - check key delete-chain | - check index reference | - check data record references index: 1 | - check data record references index: 2 | - check record links Aparently, what i heard from a friend, it's not done to binary copy tables from 32bit systems over to 64bit systems. Is it as bad to binary copy tables from 64bit to 32bit systems? It's vital in our current setup that binary copy of tables works. Or we need to set up replication right now ;) Lemmeknow and Thanks!! Sander. -- | Lead me not into temptation... I can find it myself. | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D -- 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 5.0.19-2 and repair problems...
Quoting sheeri kritzer ([EMAIL PROTECTED]): According to the manual, 64 to 32 bit shouldn't matter, it should work fine as long as they're both little endian, or they're both big endian. This friend of mine said he also had major problems moving 32-64. Crashing mysql's, but not corrupt tables. Well, i had crashing mysql's earlier (see my other post on this list about MySQL on AMD64) but that seems over now. Now i have corrupt tables. Or, better stated, all tables with indexes on them are corrupt. I've copied MyISAM tables from 64 to 32 bit without a problem. Great to hear. Have to try it out myself to be sure though, but it's good to hear you have no problems doing that. BTW, I suggested that because in the error it said: myisamchk: error: Key in wrong position at page 715776 - check records and index references Did you by any chance symlink or move files, or not move the key files? My guess is that might cause that problem, otherwise, I have no idea what would cause it. Nope. What i did is, in psuedocode: for database in datadir/* for table in datadir/database/* lock table flush table binary copy table* to tempdir unlock table } } What seems like a normal binary copy to me. All three files were copied, the .frm, the .MYI and the .MYD. Regards, Sander. -- | 't Gaat om 't spel, niet om de knikkers! | 1024D/08CEC94D - 34B3 3314 B146 E13C 70C8 9BDB D463 7E41 08CE C94D -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql performance problems.
Jacob, Raymond A Jr wrote: After a 23days of running mysql, I have a 3GB database. When I use an application called base(v.1.2.2) a web based intrusion detection analysis console, the mysqld utilization shoots up to over 90% and stays there until the application times out or is terminated. Question: Have I made some error in configuration? When I don't run the application base, mysqld utilization is between 30-50%. Question: What hardware do I need to speed up queries? Question: How do determine if the query is the problem? Data: I used my-large.cnf as the basis of my.cnf. Hardware and OS info: ... FreeBSD 6.0-RELEASE-p5 #0: ... CPU: Intel Pentium III (997.46-MHz 686-class CPU) Origin = GenuineIntel Id = 0x68a Stepping = 10 Features=0x383fbffFPU,VME,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE,MCA,CMOV,PAT,PSE36,MMX,FXSR,SSE real memory = 1073676288 (1023 MB) avail memory = 1041784832 (993 MB) Observations: Disk Space used: du -am /var/db/mysql | sort -nr | head -20 5259mysql/ 3055mysql/snort 2184mysql/snort_archive 1546mysql/snort_archive/data.MYD 1546mysql/snort/data.MYD 560 mysql/snort/acid_event.MYI 311 mysql/snort/acid_event.MYD 132 mysql/snort_archive/event.MYI 132 mysql/snort/event.MYI 116 mysql/snort_archive/iphdr.MYI 116 mysql/snort/iphdr.MYI 112 mysql/snort_archive/iphdr.MYD 112 mysql/snort/iphdr.MYD 74 mysql/snort_archive/event.MYD 74 mysql/snort/event.MYD 42 mysql/snort_archive/data.MYI 42 mysql/snort/data.MYI 40 mysql/snort_archive/icmphdr.MYI 40 mysql/snort/icmphdr.MYI 35 mysql/snort_archive/icmphdr.MYD ... snort is 3GB snort_archive is 2GB(snort_archive acid and base tables have not been built that is why snort archive is smaller) When the application searches the database, the mysqld utilization goes up to over 90% until the application times out. top last pid: 44263; load averages: 0.95, 0.89, 0.76 up 25+23:49:4416:07:17 49 processes: 2 running, 47 sleeping Mem: 173M Active, 608M Inact, 186M Wired, 29M Cache, 111M Buf, 1660K Free Swap: 2048M Total, 156K Used, 2048M Free PID USERNAME THR PRI NICE SIZERES STATETIME WCPU COMMAND 31890 mysql 15 200 103M 79032K kserel 768:38 93.46% mysqld 49138 www 1 40 17432K 12848K accept 0:23 0.00% httpd 46759 www 1 200 16584K 12084K lockf0:21 0.00% httpd 46764 www 1 40 16632K 12072K accept 0:21 0.00% httpd 46763 www 1 40 16580K 12012K accept 0:20 0.00% httpd 46760 www 1 40 17452K 12872K accept 0:19 0.00% httpd 46762 www 1 40 16568K 12000K accept 0:19 0.00% httpd 46761 www 1 40 16608K 12088K sbwait 0:17 0.00% httpd 68456 www 1 40 16572K 11980K accept 0:17 0.00% httpd 68457 www 1 40 16724K 11824K accept 0:17 0.00% httpd 68458 www 1 40 16980K 11920K accept 0:17 0.00% httpd Processes that run in the background: I run an update process in the background with hope that if I process the alerts from the snort table on a regular basis.o I won't have process a large number( 44,000) alerts first thing in the morning. The update process inserts records into the acid table that result from the join of certain fields from the snort tables. (Schema at http://www.andrew.cmu.edu/user/rdanyliw/snort/acid_db_er_v102.html ) rabid# cat /var/log/base-update.2006-03-28.log 2006-03-28, 00:05:00, Added 3808 alert(s) to the Alert cache 2006-03-28, 01:05:00, Added 5855 alert(s) to the Alert cache 2006-03-28, 02:05:00, Added 4096 alert(s) to the Alert cache 2006-03-28, 03:05:00, Added 4473 alert(s) to the Alert cache 2006-03-28, 04:05:00, Added 4378 alert(s) to the Alert cache 2006-03-28, 05:05:00, Added 4087 alert(s) to the Alert cache 2006-03-28, 06:05:00, Added 5163 alert(s) to the Alert cache 2006-03-28, 07:05:00, Added 4789 alert(s) to the Alert cache 2006-03-28, 08:05:00, Added 4411 alert(s) to the Alert cache 2006-03-28, 09:05:00, Added 4830 alert(s) to the Alert cache 2006-03-28, 10:05:00, Added 4739 alert(s) to the Alert cache 2006-03-28, 11:05:00, Added 5360 alert(s) to the Alert cache 2006-03-28, 12:05:00, Added 7305 alert(s) to the Alert cache 2006-03-28, 13:05:00, Added 8481 alert(s) to the Alert cache 2006-03-28, 14:05:00, Added 60731 alert(s) to the Alert cache 2006-03-28, 15:05:00, Added 44328 alert(s) to the Alert cache 2006-03-28, 15:50:00, Added 13742 alert(s) to the Alert cache 2006-03-28, 15:55:00, Added 607 alert(s) to the Alert cache 2006-03-28, 16:00:00, Added 938 alert(s) to the Alert cache 2006-03-28, 16:05:00, Added 1348 alert(s) to the Alert cache 2006-03-28, 16:10:02, Added 687 alert(s) to the Alert cache 2006-03-28, 16:15:00, Added 1168 alert(s) to the Alert cache 2006-03-28, 16:20:00, Added 1760 alert(s) to the Alert cache 2006-03-28, 16:25:00, Added 814 alert(s) to the Alert cache 2006-03-28, 16:30:01, Added
Weird M$ Pasting issue
Hello All, I am running across a very weird problem. Sometimes when a person paste text from a Worddoc into the text field of our webapp, the insert fails. Unfortunately I am not seeing the failure in the logs. There are a lot of factors to consider. The path to mysql looks like this. Firefox (OSX) - JDBC - Mysql (on linux) the field we are pasting to is a TEXT field. when I paste into an emacs editor. I see what looks like formatting code. Not sure why that is getting pasted into the text field and also not sure why the jdbc prepared statements are not making the text safe for insert. Anyone have a clue as to what might be happening? -- Ghetto Java: http://www.ghettojava.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb errors on startup
Marten, - Original Message - From: Marten Lehmann [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, March 29, 2006 7:52 PM Subject: Re: innodb errors on startup Well, ok, we need to stress this more in the manual. A few users have misunderstood that ibdata files would no longer be needed if one uses innodb_file_per_table. ib_logfiles are always needed. How else can InnoDB recover after a crash. but how can I repair my existing ib-files so that the error on startup doesn't appear any more? try: DROP TABLE db16041.intradv_cms_websites; if you do not have the .frm file for that table, use the trick explained at: http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting-datadict.html Regards Marten Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Weird M$ Pasting issue
If they are pasting from Word, there is a lot of Word-proprietary XML and formatting that is being pasted as well. Your insert statement may be failing because: 1) Because of the XML and formatting, the statement is going beyond the TEXT fields limit; 2) There are ' (single quotes) in the formatting forcing an SQL / JDBC error; 3) The JDBC is reading and interpreting the XML and causing a failure of some kind. Your best bet is to either ask the people pasting to not use Word, have them paste from Word into NOTEPAD, TextEdit or VI before pasting into your app, OR try converting your TEXT field into a LONGTEXT field. Just a few thoughts... J.R. -Original Message- From: Vinny [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 29, 2006 3:53 PM To: mysql@lists.mysql.com Subject: Weird M$ Pasting issue Hello All, I am running across a very weird problem. Sometimes when a person paste text from a Worddoc into the text field of our webapp, the insert fails. Unfortunately I am not seeing the failure in the logs. There are a lot of factors to consider. The path to mysql looks like this. Firefox (OSX) - JDBC - Mysql (on linux) the field we are pasting to is a TEXT field. when I paste into an emacs editor. I see what looks like formatting code. Not sure why that is getting pasted into the text field and also not sure why the jdbc prepared statements are not making the text safe for insert. Anyone have a clue as to what might be happening? -- Ghetto Java: http://www.ghettojava.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]
auto_increment and the value 0
I'm migrating a database from 4.0.12 on Solaris to 4.0.18-0 on Red Hat Linux. A few of the tables have a 0 (zero) in the auto_increment primary key column. However, when importing, the 0 in the insert is translated to the next available auto_increment value thus causing a duplicate key situation on the next value in the import. I've tried removing the 0 row from the export and adding it in manually afterwards, but that also translates the 0 to the next available auto_increment value. I've also tried creating the table with the table option AUTO_INCREMENT=0 and inserting the 0 row first. That also translated it to a value of 1 and caused duplicate keys. Is there a way to maintain the 0 value in an auto_increment column? Brian Stanton DBA, Belo 214-977-4087
Re: Weird M$ Pasting issue
Vinny [EMAIL PROTECTED] wrote on 03/29/2006 03:52:33 PM: Hello All, I am running across a very weird problem. Sometimes when a person paste text from a Worddoc into the text field of our webapp, the insert fails. Unfortunately I am not seeing the failure in the logs. There are a lot of factors to consider. The path to mysql looks like this. Firefox (OSX) - JDBC - Mysql (on linux) the field we are pasting to is a TEXT field. when I paste into an emacs editor. I see what looks like formatting code. Not sure why that is getting pasted into the text field and also not sure why the jdbc prepared statements are not making the text safe for insert. Anyone have a clue as to what might be happening? -- Ghetto Java: http://www.ghettojava.com -- Didn't you leave out an important component or two in your transfer chain? Doesn't the data actually take a route more like: Firefox (OSX) - Web Server - Server-side scripting language or CGI - JDBC - Mysql (on linux)? The fact that there is some processing stage at the server means that we can isolate the problem to either before or after it arrives at your server. What is the actual data that Firefox is sending to your server-side code? Verify that your server is receiving what you think you are pasting. If what you paste is not what you receive, then Firefox may be to blame. Check how your server-side code mangles the incoming information. You may be unintentionally changing the incoming data somehow. How are you setting up your JDBC connection to MySQL? I haven't used JDBC so I can't say if what you are doing is correct but someone on the list will surely pitch in and help. Are you trying to work with characters that fall outside the range of US-ASCII? When working with Unicode, UTF-8, and a whole slew of other charactersets, you have to ensure that all of the components of your data processing chain are using the same characterset and collation. These are just the first places I would look. Others will probably suggest more. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: auto_increment and the value 0
Stanton, Brian wrote: I'm migrating a database from 4.0.12 on Solaris to 4.0.18-0 on Red Hat Linux. A few of the tables have a 0 (zero) in the auto_increment primary key column. However, when importing, the 0 in the insert is translated to the next available auto_increment value thus causing a duplicate key situation on the next value in the import. I've tried removing the 0 row from the export and adding it in manually afterwards, but that also translates the 0 to the next available auto_increment value. I've also tried creating the table with the table option AUTO_INCREMENT=0 and inserting the 0 row first. That also translated it to a value of 1 and caused duplicate keys. You can either: - create the table without the auto_increment field, load the data, and add the auto_increment field, or - change all your zero values *now* ( and related fields in other tables ), back things up, and then move the data I would take the 2nd option. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment and the value 0
Are you saying just change the row with the 0 value as the PK, and change the FK's in the related tables to point to the new value instaed of 0? If so, would this move the row logically to the end of the table, if the 0 PK was replaced with the next auto_increment value? I suppose that would be alot easier than trying to bump the PK and related FK values of the whole table by 1, just to give the first row in the table the auto_increment value of 1? What about before migrating the database, just adding a new row to the end of the table, that would duplicate the data in the first row, then deleting the first row from the table? Would that work? Keith In theory, theory and practice are the same; in practice they are not. On Thu, 30 Mar 2006, Daniel Kasak wrote: To: Stanton, Brian [EMAIL PROTECTED], 'mysql@lists.mysql.com' mysql@lists.mysql.com From: Daniel Kasak [EMAIL PROTECTED] Subject: Re: auto_increment and the value 0 Stanton, Brian wrote: I'm migrating a database from 4.0.12 on Solaris to 4.0.18-0 on Red Hat Linux. A few of the tables have a 0 (zero) in the auto_increment primary key column. However, when importing, the 0 in the insert is translated to the next available auto_increment value thus causing a duplicate key situation on the next value in the import. I've tried removing the 0 row from the export and adding it in manually afterwards, but that also translates the 0 to the next available auto_increment value. I've also tried creating the table with the table option AUTO_INCREMENT=0 and inserting the 0 row first. That also translated it to a value of 1 and caused duplicate keys. You can either: - create the table without the auto_increment field, load the data, and add the auto_increment field, or - change all your zero values *now* ( and related fields in other tables ), back things up, and then move the data I would take the 2nd option. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au k -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment and the value 0
[EMAIL PROTECTED] wrote: Are you saying just change the row with the 0 value as the PK, and change the FK's in the related tables to point to the new value instaed of 0? Yes. If so, would this move the row logically to the end of the table, if the 0 PK was replaced with the next auto_increment value? Not really. The primary key isn't ( or at least shouldn't be ) used to determine 'position' in the record. If you are actually using the primary key to determine 'position', ie select * from table order by primary_key ... then yes, the position will change. But the record is still in there, and still linked to related records ( as long as you update their foreign key values to the new value as well ). So as long as you don't depend on the primary key in something like the above ( ie you're not using the primary key to determine a record's age ), then it doesn't really matter what primary key it has. If you are depending on this value in an 'order by' clause, then find some work-around - order by something else - before you change it. Also keep in mind that when you do a select without an order by clause, you aren't guaranteed of getting the records in any particular order anyway - the DB server is allowed to return records in any order if you don't specify an 'order by' clause. I suppose that would be alot easier than trying to bump the PK and related FK values of the whole table by 1, just to give the first row in the table the auto_increment value of 1? Yes. That sounds messy. What about before migrating the database, just adding a new row to the end of the table, that would duplicate the data in the first row, then deleting the first row from the table? Would that work? Yes but it would be no different to just updating the primary key of the existing record. Either way, you will have issues with restoring from backups if you keep an auto_increment column with a zero value around ( as you've discovered ), so what ever you do, you need to get rid of those zero values. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment and the value 0
On 30/03/2006 12:31 p.m., Daniel Kasak wrote: [EMAIL PROTECTED] wrote: I suppose that would be alot easier than trying to bump the PK and related FK values of the whole table by 1, just to give the first row in the table the auto_increment value of 1? Yes. That sounds messy. What about before migrating the database, just adding a new row to the end of the table, that would duplicate the data in the first row, then deleting the first row from the table? Would that work? Yes but it would be no different to just updating the primary key of the existing record. Either way, you will have issues with restoring from backups if you keep an auto_increment column with a zero value around ( as you've discovered ), so what ever you do, you need to get rid of those zero values. Another option would be to reassign the zero row to be -1 (if the column is not UNSIGNED). Assuming the current zero row has some kind of special significance, this may make more sense than just giving it the next unused auto_increment value. This would also keep it in the same place with an ORDER BY. -Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple-Master Replication recovery
Thanks a lot. I'll try. - Original Message - From: Kishore Jalleda [EMAIL PROTECTED] To: 古雷 [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Wednesday, March 29, 2006 11:53 PM Subject: Re: Multiple-Master Replication recovery any one of the servers could go down in many ways like 1) disk crash 2) replication failure 3) power failure 4) any hardware component failure 5) OS hang 6) Network failure 7) MYSQL bug 8) table corruption etc ... 9) or just scheduled donwtime in any case what really matters is the difference in status of the failed server from the point of failure to the point of recovery , so it all depends on how up-to-date your data is on the failed server.. here are some typical cases .. 1) you have everything intact ( data, bin logs, *.info files, etc ) then just bring back the failed server online and you are good to go 2) if you rebuild the server from scratch it depends on how current your backup is , in this take a snapshot of the main server ( either hot or cold depending on your setup) and start the multi-master replication .. In theory you really dont have to take the server offline unless it really demands , in the worst case as Barry said its lot easier to shut down the master (if you can afford some downtime) take a complete snapshot/backup/dump ( varies for MYISAM and INNODB) and bring back the failed server back online Hope this helps Kishore Jalleda On 3/28/06, 古雷 [EMAIL PROTECTED] wrote: Hello: If I use Multiple-Master Replication with two mysql server, when one of them goes down(disk crashed) must I shutdown the good one to recover the Multiple-Master Replication ? regards, gu lei
C API Prepared Statement Data types with unsigned int
mysql desc MSGDB_20060330; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | CREATETIME| char(14)| NO | | | | | MESSAGEUUID | char(33)| YES | MUL | | | | SMSSEQ| int(10) unsigned| NO | PRI | 0 | | | DESTTERMID| varchar(80) | YES | | | | | MSGOVER | smallint(6) | YES | | | | | ERRORCODE | int(11) | YES | | | | | MSGIDREPLY| bigint(20) unsigned | YES | MUL | | | | OVERTIME | char(14)| YES | | | | | REPLYOVER | smallint(6) | YES | | | | | REPLYOVERTIME | char(14)| YES | | | | | MSGSTATUS | varchar(20) | YES | | | | +---+-+--+-+-+---+ SMSSEQ is int unsigned. This is part of my code: unsigned int parm_smsSeq_; bindResParm_[parmNum].buffer_type = MYSQL_TYPE_LONG; bindResParm_[parmNum].buffer = (char*)parm_smsSeq_; bindResParm_[parmNum].is_null = 0; bindResParm_[parmNum].length = 0; When parm_smsSeq is grater than max value of signed int, I always get an zero value in that table after INSERT INTO SMSSEQ. Please help me. regards, gu lei 祝您工作顺利，身体健康，家庭和睦，一切吉祥。 古雷 中企动力科技集团 技术架构部
Compound Insert Statement
Hi folks. I come to the list with another compound question.
My middleware allows me to build any syntax for the actual sql
statement, so I'm trying to minimize the work done to insert several
records at one try. I currently have multiple insert statements, but
can't find any reference to multiple records added using one insert
statement. I now have:
-SQL = INSERT INTO mytable (myfield) VALUES ('alpha');INSERT INTO
mytable (myfield) VALUES ('bravo');INSERT INTO mytable (myfield) VALUES
('charlie');INSERT INTO mytable (myfield) VALUES ('delta');
What I would like to find is this:
-SQL = INSERT INTO mytable (myfield) VALUES ('alpha'), ('bravo'),
('charlie'), ('delta');
I am either looking in the wrong place in Dubois (Third) or it can't be
done.
Any recommendations?
Appreciate it.
Cheers
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Compound Insert Statement
That is, in fact, the exact correct syntax. What error are you
getting when you try to run that on the commandline? What version of
MySQL are you using?
-Sheeri
On 3/29/06, Rich [EMAIL PROTECTED] wrote:
Hi folks. I come to the list with another compound question.
My middleware allows me to build any syntax for the actual sql
statement, so I'm trying to minimize the work done to insert several
records at one try. I currently have multiple insert statements, but
can't find any reference to multiple records added using one insert
statement. I now have:
-SQL = INSERT INTO mytable (myfield) VALUES ('alpha');INSERT INTO
mytable (myfield) VALUES ('bravo');INSERT INTO mytable (myfield) VALUES
('charlie');INSERT INTO mytable (myfield) VALUES ('delta');
What I would like to find is this:
-SQL = INSERT INTO mytable (myfield) VALUES ('alpha'), ('bravo'),
('charlie'), ('delta');
I am either looking in the wrong place in Dubois (Third) or it can't be
done.
Any recommendations?
Appreciate it.
Cheers
--
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 add a new innodb data file~
Hi, Iam running a mysql server 5.0.15 over Redhat linux es4. My disk space has exhausted. so i need to add a new ibdata file to my /etc/my.cnf configuration. I followed the following procedure to do so. 1. I checked the ibdata1 file size. when i do a du -sh ibdata1 , i get the size to be 443M when i do a ls -ltr ibdata1 , i get the size to be 463470592 bytes 2. I edited my /etc/my.cnf to add the following: innodb_data_file_path = /mysql-system/mysql/data/ibdata1:443M;/mysql- system2/ibdata2:50M:autoextend i got the following error: 060330 01:48:42 mysqld started InnoDB: Error: data file /mysql-system/mysql/data/ibdata1 is of a different size InnoDB: 28288 pages (rounded down to MB) InnoDB: than specified in the .cnf file 28416 pages! InnoDB: Could not open or create data files. InnoDB: If you tried to add new data files, and it failed here, InnoDB: you should now edit innodb_data_file_path in my.cnf back InnoDB: to what it was, and remove the new ibdata files InnoDB created InnoDB: in this failed attempt. InnoDB only wrote those files full of InnoDB: zeros, but did not yet use them in any way. But be careful: do not InnoDB: remove old data files which contain your precious data! 060330 1:48:42 [Note] /mysql-system/mysql/bin/mysqld: ready for connections. Version: '5.0.15-standard-log' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL) 3. Then i tried adding the value from 'ls -ltr ibdata1' which is 463470592 bytes. I rounded the same to 464M . but still got the same error. Can anyone help me out on how to go about the same. Thanks in advance, Abdul. This email has been Scanned for Viruses! www.newbreak.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment and the value 0
You can override MySQL behaviour of generating a new value if you insert a 0 into an auton_increment field. Quoting from the manual: NO_AUTO_VALUE_ON_ZERO affects handling of AUTO_INCREMENT columns. Normally, you generate the next sequence number for the column by inserting either NULL or 0 into it. NO_AUTO_VALUE_ON_ZERO suppresses this behavior for 0 so that only NULL generates the next sequence number. This mode can be useful if 0 has been stored in a table's AUTO_INCREMENTcolumn. (Storing 0 is not a recommended practice, by the way.) For example, if you dump the table with *mysqldump* and then reload it, MySQL normally generates new sequence numbers when it encounters the 0 values, resulting in a table with contents different from the one that was dumped. Enabling NO_AUTO_VALUE_ON_ZERO before reloading the dump file solves this problem. *mysqldump* now automatically includes in its output a statement that enables NO_AUTO_VALUE_ON_ZERO, to avoid this problem. So you must alter the SQL-mode: Issue : mysqlSET @@session.sql_mode = NO_AUTO_VALUE_ON_ZERO; For altering the sessions sql_mode... now you will have to insert the dump with: mysqlSOURCE /path/to/dump.sql You can change it globally mysqlSET @@global.sql_mode = NO_AUTO_VALUE_ON_ZERO; But it's not recommend ... because until you change it back... MySQL will not generate auto increment values for your inserts if you use 0... only if you use NULL !!! The advantage is that you can import the dump from the command line. Another way is to add the statement: SET @@session.sql_mode = NO_AUTO_VALUE_ON_ZERO; into the dump itself... but... i think it's not that small... I believe this will do for you... but keep in mind that a value of 0 in an auto_increment column is not a good thing ... as everybody said before ! Good luck ! -- Gabriel PREDA Senior Web Developer
