We are experiencing a certain anomaly here on our db server. The problem had occurred about 3 weeks ago. We ran diagnostics on the hardware over a 48+hour period with no failures or indications of problems with hardware. ( at least the memory.) We rebooted the server, and had not experienced anymore issues until yesterday, it repeated itself.
The issue is as follows.... We run a series of views, sp's and user defined functions for a period of time. During application development we run these from command line, from MySQL query browser and from a series of PHP and Perl Scripts (of which some are executed from web apps others from cli ). At some point, the data returned to the user appears corrupted. Bogus data is returned, whether it be from a query to the actual table or to a view. Upon rebooting the server the data is returned to normal (ie. the data tables written to hdd are fine, it appears only the cached results are fubar). I can point out also if it matters that all my tables are INNODB.... We are currently developing on a semi-production DB....( i know...hands need to be slapped ) We are using Apache 2.2 on a similar BSD server. Server specs follow... ************************************************************************ ************* FreeBSD 6.1 Release #0 mysql-server-5.0.45_1 mysql-client-5.0.45_1 ************************************************************************ ************* Hardware: Copyright (c) 1992-2006 The FreeBSD Project. Copyright (c) 1979, 1980, 1983, 1986, 1988, 1989, 1991, 1992, 1993, 1994 The Regents of the University of California. All rights reserved. FreeBSD 6.1-RELEASE #0: Sun May 7 04:42:56 UTC 2006 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/SMP Timecounter "i8254" frequency 1193182 Hz quality 0 CPU: Intel(R) Xeon(TM) CPU 3.06GHz (3065.81-MHz 686-class CPU) Origin = "GenuineIntel" Id = 0xf29 Stepping = 9 Features=0xbfebfbff<FPU,VME,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE ,MCA ,CMOV,PAT,PSE36,CLFLUSH,DTS,ACPI,MMX,FXSR,SSE,SSE2,SS,HTT,TM,PBE> Features2=0x4400<CNTX-ID,<b14>> Logical CPUs per core: 2 real memory = 2146959360 (2047 MB) avail memory = 2095874048 (1998 MB) ACPI APIC Table: <PTLTD APIC > FreeBSD/SMP: Multiprocessor System Detected: 4 CPUs cpu0 (BSP): APIC ID: 0 cpu1 (AP): APIC ID: 1 cpu2 (AP): APIC ID: 6 cpu3 (AP): APIC ID: 7 ioapic0 <Version 2.0> irqs 0-23 on motherboard ioapic1 <Version 2.0> irqs 24-47 on motherboard ioapic2 <Version 2.0> irqs 48-71 on motherboard ioapic3 <Version 2.0> irqs 72-95 on motherboard ioapic4 <Version 2.0> irqs 96-119 on motherboard kbd1 at kbdmux0 acpi0: <PTLTD RSDT> on motherboard acpi0: Power Button (fixed) Timecounter "ACPI-fast" frequency 3579545 Hz quality 1000 acpi_timer0: <24-bit timer at 3.579545MHz> port 0x1008-0x100b on acpi0 cpu0: <ACPI CPU> on acpi0 cpu1: <ACPI CPU> on acpi0 cpu2: <ACPI CPU> on acpi0 cpu3: <ACPI CPU> on acpi0 pcib0: <ACPI Host-PCI bridge> port 0xcf8-0xcff on acpi0 pci0: <ACPI PCI bus> on pcib0 pci0: <unknown> at device 0.1 (no driver attached) pcib1: <ACPI PCI-PCI bridge> at device 2.0 on pci0 pci1: <ACPI PCI bus> on pcib1 pci1: <base peripheral, interrupt controller> at device 28.0 (no driver attached) pcib2: <ACPI PCI-PCI bridge> at device 29.0 on pci1 pci2: <ACPI PCI bus> on pcib2 pci1: <base peripheral, interrupt controller> at device 30.0 (no driver attached) pcib3: <ACPI PCI-PCI bridge> at device 31.0 on pci1 pci3: <ACPI PCI bus> on pcib3 em0: <Intel(R) PRO/1000 Network Connection Version - 3.2.18> port 0x3000-0x303f mem 0xf0200000-0xf021ffff irq 28 at device 2.0 on pci3 em0: Ethernet address: 00:30:48:2f:13:7c em1: <Intel(R) PRO/1000 Network Connection Version - 3.2.18> port 0x3040-0x307f mem 0xf0220000-0xf023ffff irq 29 at device 2.1 on pci3 em1: Ethernet address: 00:30:48:2f:13:7d pcib4: <ACPI PCI-PCI bridge> at device 3.0 on pci0 pci4: <ACPI PCI bus> on pcib4 pci4: <base peripheral, interrupt controller> at device 28.0 (no driver attached) pcib5: <ACPI PCI-PCI bridge> at device 29.0 on pci4 pci5: <ACPI PCI bus> on pcib5 pci4: <base peripheral, interrupt controller> at device 30.0 (no driver attached) pcib6: <ACPI PCI-PCI bridge> at device 31.0 on pci4 pci6: <ACPI PCI bus> on pcib6 aac0: <Adaptec SCSI RAID 2020ZCR> mem 0xf1000000-0xf1ffffff,0xf0400000-0xf05fffff,0xf8000000-0xfbffffff irq 72 at device 1.0 on pci6 aac0: New comm. interface enabled aac0: Adaptec Raid Controller 2.0.0-1 aacp0: <SCSI Passthrough Bus> on aac0 aacp1: <SCSI Passthrough Bus> on aac0 uhci0: <Intel 82801CA/CAM (ICH3) USB controller USB-A> port 0x2000-0x201f irq 16 at device 29.0 on pci0 uhci0: [GIANT-LOCKED] usb0: <Intel 82801CA/CAM (ICH3) USB controller USB-A> on uhci0 usb0: USB revision 1.0 uhub0: Intel UHCI root hub, class 9/0, rev 1.00/1.00, addr 1 uhub0: 2 ports with 2 removable, self powered uhci1: <Intel 82801CA/CAM (ICH3) USB controller USB-B> port 0x2020-0x203f irq 19 at device 29.1 on pci0 uhci1: [GIANT-LOCKED] usb1: <Intel 82801CA/CAM (ICH3) USB controller USB-B> on uhci1 usb1: USB revision 1.0 uhub1: Intel UHCI root hub, class 9/0, rev 1.00/1.00, addr 1 uhub1: 2 ports with 2 removable, self powered uhci2: <Intel 82801CA/CAM (ICH3) USB controller USB-C> port 0x2040-0x205f irq 18 at device 29.2 on pci0 uhci2: [GIANT-LOCKED] usb2: <Intel 82801CA/CAM (ICH3) USB controller USB-C> on uhci2 usb2: USB revision 1.0 uhub2: Intel UHCI root hub, class 9/0, rev 1.00/1.00, addr 1 uhub2: 2 ports with 2 removable, self powered pcib7: <ACPI PCI-PCI bridge> at device 30.0 on pci0 pci7: <ACPI PCI bus> on pcib7 pci7: <display, VGA> at device 1.0 (no driver attached) isab0: <PCI-ISA bridge> at device 31.0 on pci0 isa0: <ISA bus> on isab0 atapci0: <Intel ICH3 UDMA100 controller> port 0x1f0-0x1f7,0x3f6,0x170-0x177,0x376,0x2060-0x206f at device 31.1 on pci0 ata0: <ATA channel 0> on atapci0 ata1: <ATA channel 1> on atapci0 pci0: <serial bus, SMBus> at device 31.3 (no driver attached) acpi_button0: <Power Button> on acpi0 atkbdc0: <Keyboard controller (i8042)> port 0x60,0x64 irq 1 on acpi0 atkbd0: <AT Keyboard> irq 1 on atkbdc0 kbd0 at atkbd0 atkbd0: [GIANT-LOCKED] psm0: <PS/2 Mouse> irq 12 on atkbdc0 psm0: [GIANT-LOCKED] psm0: model IntelliMouse Explorer, device ID 4 sio0: <16550A-compatible COM port> port 0x3f8-0x3ff irq 4 flags 0x10 on acpi0 sio0: type 16550A sio1: <16550A-compatible COM port> port 0x2f8-0x2ff irq 3 on acpi0 sio1: type 16550A fdc0: <floppy drive controller> port 0x3f0-0x3f5,0x3f7 irq 6 drq 2 on acpi0 fdc0: [FAST] fd0: <1440-KB 3.5" drive> on fdc0 drive 0 ppc0: <ECP parallel printer port> port 0x378-0x37f,0x778-0x77f irq 7 drq 3 on acpi0 ppc0: SMC-like chipset (ECP/EPP/PS2/NIBBLE) in COMPATIBLE mode ppc0: FIFO with 16/16/9 bytes threshold ppbus0: <Parallel port bus> on ppc0 plip0: <PLIP network interface> on ppbus0 lpt0: <Printer> on ppbus0 lpt0: Interrupt-driven port ppi0: <Parallel I/O> on ppbus0 pmtimer0 on isa0 orm0: <ISA Option ROMs> at iomem 0xc0000-0xc7fff,0xc8000-0xc8fff,0xc9000-0xcd7ff,0xe0000-0xe3fff on isa0 sc0: <System console> at flags 0x100 on isa0 sc0: VGA <16 virtual consoles, flags=0x300> vga0: <Generic ISA VGA> at port 0x3c0-0x3df iomem 0xa0000-0xbffff on isa0 Timecounters tick every 1.000 msec acd0: CDROM <CD-224E-N/1.AA> at ata1-master UDMA33 aacd0: <RAID 1 (Mirror)> on aac0 aacd0: 69974MB (143307008 sectors) ses0 at aacp0 bus 0 target 6 lun 0 ses0: <SUPER GEM318 0> Fixed unknown SCSI-2 device ses0: 3.300MB/s transfers ses0: SAF-TE Compliant Device pass0 at aacp0 bus 0 target 0 lun 0 pass0: <SEAGATE ST373454LC 0003> Fixed unknown SCSI-3 device pass0: 3.300MB/s transfers pass1 at aacp0 bus 0 target 1 lun 0 pass1: <SEAGATE ST373454LC 0003> Fixed unknown SCSI-3 device pass1: 3.300MB/s transfers SMP: AP CPU #3 Launched! SMP: AP CPU #1 Launched! SMP: AP CPU #2 Launched! em0: link state changed to UP ************************************************************************ ****************** my.cnf ************************************************************************ ****************** [mysqld] port = 3306 socket = /tmp/mysql.sock back_log = 50 #skip-networking max_connections = 200 max_connect_errors = 10 table_cache = 2048 #external-locking max_allowed_packet = 16M binlog_cache_size = 1M max_heap_table_size = 64M sort_buffer_size = 8M join_buffer_size = 8M thread_cache_size = 8 thread_concurrency = 8 query_cache_size = 64M query_cache_limit = 2M ft_min_word_len = 4 #memlock default_table_type = INNODB thread_stack = 192K transaction_isolation = SERIALIZABLE tmp_table_size = 64M log-bin=mysql-bin #log_slave_updates #log #log_warnings log_slow_queries long_query_time = 2 log_long_format #tmpdir = /tmp server-id = 1 #server-id = 2 #master-host = <hostname> #master-user = <username> #master-password = <password> #master-port = <port> #read_only key_buffer_size = 32M read_buffer_size = 2M read_rnd_buffer_size = 16M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_max_extra_sort_file_size = 10G myisam_repair_threads = 1 myisam_recover skip-bdb innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 1G innodb_data_file_path = ibdata1:10M:autoextend #innodb_data_home_dir = /usr/local/mysql/ innodb_file_io_threads = 4 #innodb_force_recovery=1 innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 1 #innodb_fast_shutdown innodb_log_buffer_size = 8M innodb_log_file_size = 256M innodb_log_files_in_group = 3 #innodb_log_group_home_dir innodb_max_dirty_pages_pct = 90 #innodb_flush_method=O_DSYNC innodb_lock_wait_timeout = 120 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [myisamchk] key_buffer = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M [mysqlhotcopy] interactive-timeout [mysqld_safe] open-files-limit = 8192