post on mysql general discussion
Hello, I am looking to post stuff on the mysql community mailing list. Please let me know if there is any additional information you need.
Re: filter slowquerylog on specific user
Try maatkit mk-query-digest
mk-query-digest --filter '($event-{user} || ) =~ m/user/' mysql.slow.log
2010/9/28 Stefan Onken supp...@stonki.de
Hello,
is there any way to run a a slow query analyse with mysdumpslow only on
specific mysql user connections? We have multiply application accessing
the same server and sometimes even the same databases and tables. Now we
want to analyse only one specific application which uses a specific
username for login into the mysql DB.
The slow query looks like:
# u...@host: my_user[my_user] @ [192.168.111.111]
# Query_time: 0 Lock_time: 0 Rows_sent: 0 Rows_examined: 146
Thanks!
Stonki
--
www.stonki.de
www.proftpd.de
www.kbarcode.net
www.krename.net
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=maxb...@gmail.com
Re: Unique ID's across multiple databases
Server offset + increment works really well, is simple, and well documented and reliable - not sure why you would want to re-invent something that works so well :). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Fw: BIN LOG Error when use Begin Trans in Replication.
Hi Vikram, Add binlog_format=row to your my.ini master's conf file 2010/4/21 Vikram A vikkiatb...@yahoo.in Hi Hao Ding, I attached in my request mail it self. Please find the attachment. Thank you -- *From:* hao ding fire9di...@gmail.com *To:* Vikram A vikkiatb...@yahoo.in *Sent:* Wed, 21 April, 2010 7:25:05 PM *Subject:* Re: BIN LOG Error when use Begin Trans in Replication. Vikram, I don't find my.ini. On Wed, Apr 21, 2010 at 9:19 PM, Vikram A vikkiatb...@yahoo.in wrote: Hi, I have installed mysql 5.1.45 version on windows server 2003[standard edition] and windows xp. I am using WIN SERVER as MASTER and WIN XP as slave. And the default engine in both severs is INNODB. I am using visual basic for my front end. The replication system is running quite well. When i am using the rs.BeginTrans I am facing the following error when i am pointing my master, [MySQL][ODBC 5.1 Driver][mysqld-5.1.45-community-log]Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is not safe for binlog mode 'STATEMENT' If i point my slave as server[I have done for checking beginTrans]; it is running quite well. Can I have help from any one. It will be useful to me. I am attaching the my.ini for your reference. Thank you Vikram A -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=fire9di...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=maxb...@gmail.com
Re: Innodb and bulk writes
Hi Raj, Ananda, the schema is very simple, we don't have any blob or text column. I thought the same about the log files, so I tried with diferent sizes but nothing change. This is the output of iostat -x 1 when the performance is slow running a restore avg-cpu: %user %nice %system %iowait %steal %idle 0.000.000.000.000.00 100.00 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 sdd 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 sde 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 md0 0.00 0.00 0.00 3.92 0.0059.8015.25 0.000.00 0.00 0.00 sdh 0.00 0.00 0.00 0.98 0.0031.3732.00 0.000.00 0.00 0.00 sdi 0.00 0.00 0.00 0.98 0.0023.5324.00 0.000.00 0.00 0.00 sdj 0.00 0.00 0.00 1.96 0.00 4.90 2.50 0.000.00 0.00 0.00 sdk 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 avg-cpu: %user %nice %system %iowait %steal %idle 0.000.000.000.000.00 100.00 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda1 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 sdb 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 sdc 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 sdd 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 sde 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 md0 0.00 0.00 0.00 1.98 0.0017.82 9.00 0.000.00 0.00 0.00 sdh 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 sdi 0.00 0.00 0.00 0.99 0.0015.8416.00 0.000.00 0.00 0.00 sdj 0.00 0.00 0.00 0.99 0.00 1.98 2.00 0.000.00 0.00 0.00 sdk 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.000.00 0.00 0.00 2010/3/18 Raj Shekhar spa...@rajshekhar.net Max Bube maxbube at gmail.com writes: The problem starts when I run bulk writes like an alter table or a restore from mysqldump, its starts processing more than 5 rows/s but suddenly the ratio goes down to 100 rows /sec. and then its stucked at this ratio even if I restart MySQL. The only way to get good perfomance again is deleting all innodb files (ibdata, iblog files) and restoring the DB again. The DBs are relative small about 70M rows and 10Gb size. I can repeat this behavior all the time just running 2 restores of the same database. Another example when its stucked: I want to delete 1M rows delete from table where id IN (select id from ) deletes 100 rows / sec but if I run 1 Million delete from table where id = xxx deletes 1 rows / sec How busy are your disks when you start seeing slowdown in the delete process? Are there blobs or big varchars in the deletes that you are doing? Innodb might be filling up its log files and when you see a slow down, it might be flushing the log to the disk. One workaround for this is to not delete million rows, but to delete in batches of 1000 rows. My guess would be that if each row is of size B, and you delete in a batch size of [innodb_log_file_size (in bytes) - 100 MB (in bytes)]/B , you should not see a slowdown. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=maxb...@gmail.com
Innodb and bulk writes
Hi list, Im having problems with bulk writes (restores from mysqldumps, alters, delete in (select ...)) with innodb. The servers are at amazon EC2 instances w/ 15G ram and raid0 4disks EBS. The problem starts when I run bulk writes like an alter table or a restore from mysqldump, its starts processing more than 5 rows/s but suddenly the ratio goes down to 100 rows /sec. and then its stucked at this ratio even if I restart MySQL. The only way to get good perfomance again is deleting all innodb files (ibdata, iblog files) and restoring the DB again. The DBs are relative small about 70M rows and 10Gb size. I can repeat this behavior all the time just running 2 restores of the same database. Another example when its stucked: I want to delete 1M rows delete from table where id IN (select id from ) deletes 100 rows / sec but if I run 1 Million delete from table where id = xxx deletes 1 rows / sec The problem is just only with writes on innodb, I can perfectly run mysqldumps and bulk inserts on MyISAM. This is happening with all MySQL 5.1.x versions I tested. Any one have a clue about this issue?? Thanks in advance Max #INNODB Settings # innodb_file_per_table innodb_buffer_pool_size = 10G innodb_additional_mem_pool_size = 20M innodb_thread_concurrency = 8 innodb_support_xa = 0 innodb_thread_sleep_delay = 2000 innodb_flush_log_at_trx_commit = 0 innodb_log_file_size = 700M innodb_log_buffer_size = 8M innodb_lock_wait_timeout = 50 innodb_max_purge_lag = 10 innodb_max_dirty_pages_pct = 90 innodb_use_purge_thread = 4 innodb_extra_undoslots = 1 innodb_adaptive_checkpoint = estimate innodb_io_capacity = 500 innodb_read_io_threads = 4 innodb_write_io_threads = 4 and this is a innodb status when was running at low preformance mysql show engine innodb status\G *** 1. row *** Type: InnoDB Name: Status: = 100310 13:12:07 INNODB MONITOR OUTPUT = Per second averages calculated from the last 42 seconds -- BACKGROUND THREAD -- srv_master_thread loops: 4409 1_second, 4408 sleeps, 439 10_second, 399 background, 399 flush srv_master_thread log flush and writes: 4925 -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 37498, signal count 37467 Mutex spin waits 115051, rounds 948698, OS waits 24706 RW-shared spins 1458, OS waits 778; RW-excl spins 0, OS waits 11943 Spin rounds per wait: 8.25 mutex, 17.61 RW-shared, 358898.00 RW-excl FILE I/O I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (read thread) I/O thread 4 state: waiting for i/o request (read thread) I/O thread 5 state: waiting for i/o request (read thread) I/O thread 6 state: waiting for i/o request (write thread) I/O thread 7 state: waiting for i/o request (write thread) I/O thread 8 state: waiting for i/o request (write thread) I/O thread 9 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 70 OS file reads, 367420 OS file writes, 113414 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 13.12 writes/s, 2.02 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf: size 1, free list len 0, seg size 2, 0 inserts, 0 merged recs, 0 merges Hash table size 25499809, node heap has 5416 buffer(s) 98.74 hash searches/s, 1.43 non-hash searches/s --- LOG --- Log sequence number 34171430524 Log flushed up to 34171425746 Last checkpoint at 33506703349 Max checkpoint age1187902219 Checkpoint age target 1150780275 Modified age 664727175 Checkpoint age664727175 0 pending log writes, 0 pending chkp writes 37839 log i/o's done, 1.05 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 13205766144; in additional pool allocated 0 Internal hash tables (constant factor + variable factor) Adaptive hash index 292738424 (203998472 + 88739952) Page hash 12750664 Dictionary cache51124680 (51001072 + 123608) File system 90728 (82672 + 8056) Lock system 31876248 (31875512 + 736) Recovery system 0 (0 + 0) Threads 407416 (406936 + 480) Dictionary memory allocated 123608 Buffer pool size786431 Buffer pool size, bytes 12884885504 Free buffers1 Database pages 781014 Old database pages 288283 Modified db pages 36334 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 190597, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 70
Re: Innodb and bulk writes
This is an output from console when its performnace goes dows Query OK, 65469 rows affected (0.82 sec) Records: 65469 Duplicates: 0 Warnings: 0 Query OK, 65469 rows affected (0.78 sec) Records: 65469 Duplicates: 0 Warnings: 0 Query OK, 65469 rows affected (10 min 57.30 sec) Records: 65469 Duplicates: 0 Warnings: 0 Query OK, 65469 rows affected (10 min 56.58 sec) Records: 65469 Duplicates: 0 Warnings: 0 and the show full processlist is, I cut the insert its about 65k rows like you can see above *** 1. row *** Id: 1 User: event_scheduler Host: localhost db: NULL Command: Daemon Time: 4730 State: Waiting on empty queue Info: NULL *** 2. row *** Id: 3 User: root Host: localhost db: test Command: Query Time: 603 State: update Info: INSERT INTO `challenge` VALUES (95794260,2,0),(95794261,3,1),(95794262,2,1),(95794263,5,0) *** 3. row *** Id: 7 User: root Host: localhost db: NULL Command: Query Time: 0 State: NULL Info: show full processlist regards Max 2010/3/18 Ananda Kumar anan...@gmail.com when the writes are happening, please run show full processlist and let us know the out put. regards anandkl On Thu, Mar 18, 2010 at 9:09 PM, Max Bube maxb...@gmail.com wrote: Hi list, Im having problems with bulk writes (restores from mysqldumps, alters, delete in (select ...)) with innodb. The servers are at amazon EC2 instances w/ 15G ram and raid0 4disks EBS. The problem starts when I run bulk writes like an alter table or a restore from mysqldump, its starts processing more than 5 rows/s but suddenly the ratio goes down to 100 rows /sec. and then its stucked at this ratio even if I restart MySQL. The only way to get good perfomance again is deleting all innodb files (ibdata, iblog files) and restoring the DB again. The DBs are relative small about 70M rows and 10Gb size. I can repeat this behavior all the time just running 2 restores of the same database. Another example when its stucked: I want to delete 1M rows delete from table where id IN (select id from ) deletes 100 rows / sec but if I run 1 Million delete from table where id = xxx deletes 1 rows / sec The problem is just only with writes on innodb, I can perfectly run mysqldumps and bulk inserts on MyISAM. This is happening with all MySQL 5.1.x versions I tested. Any one have a clue about this issue?? Thanks in advance Max #INNODB Settings # innodb_file_per_table innodb_buffer_pool_size = 10G innodb_additional_mem_pool_size = 20M innodb_thread_concurrency = 8 innodb_support_xa = 0 innodb_thread_sleep_delay = 2000 innodb_flush_log_at_trx_commit = 0 innodb_log_file_size = 700M innodb_log_buffer_size = 8M innodb_lock_wait_timeout = 50 innodb_max_purge_lag = 10 innodb_max_dirty_pages_pct = 90 innodb_use_purge_thread = 4 innodb_extra_undoslots = 1 innodb_adaptive_checkpoint = estimate innodb_io_capacity = 500 innodb_read_io_threads = 4 innodb_write_io_threads = 4 and this is a innodb status when was running at low preformance mysql show engine innodb status\G *** 1. row *** Type: InnoDB Name: Status: = 100310 13:12:07 INNODB MONITOR OUTPUT = Per second averages calculated from the last 42 seconds -- BACKGROUND THREAD -- srv_master_thread loops: 4409 1_second, 4408 sleeps, 439 10_second, 399 background, 399 flush srv_master_thread log flush and writes: 4925 -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 37498, signal count 37467 Mutex spin waits 115051, rounds 948698, OS waits 24706 RW-shared spins 1458, OS waits 778; RW-excl spins 0, OS waits 11943 Spin rounds per wait: 8.25 mutex, 17.61 RW-shared, 358898.00 RW-excl FILE I/O I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (read thread) I/O thread 4 state: waiting for i/o request (read thread) I/O thread 5 state: waiting for i/o request (read thread) I/O thread 6 state: waiting for i/o request (write thread) I/O thread 7 state: waiting for i/o request (write thread) I/O thread 8 state: waiting for i/o request (write thread) I/O thread 9 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 70 OS file reads, 367420 OS file writes, 113414 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 13.12 writes/s, 2.02 fsyncs/s
Re: Default Date and Time
Hi Jason The DEFAULT value can't be an expression. 2009/6/26 Jason Todd Slack-Moehrle mailingli...@mailnewsrss.com Hi All, I want to create a table that defaults to current_date and current_time. I have: CREATE TABLE `personalevent`( `pevent` mediumint(10) NOT NULL, `eventid` mediumint(10) NOT NULL, `userid` mediumint(10) NOT NULL, `username` varchar(10) NOT NULL, `password` varchar(10) NULL, `country` varchar(45) NULL, `zipcode` varchar(5) NULL, `city` varchar(35) NULL, `hstate` varchar(45) NULL, `exclusive` varchar(7) NULL, `eventtime` time NULL DEFAULT current_time(), `eventdate` date NULL DEFAULT current_date(), `eventdura` varchar(35) NULL, `daysevent` varchar(10) NULL, `crowd` varchar(25) NULL, `venue` varchar(50) NULL, `activitytype` varchar(45) NULL, `actdetails` varchar(255) NULL, `encodedby` varchar(100) NULL, `curmo` varchar(2) NULL, `pageweb` varchar(50) NULL, PRIMARY KEY (`pevent`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; But this throws a syntax error. I have tried Now() as well. What am I doing wrong? Best, -Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=maxb...@gmail.com
Re: a possible group issue???
Try with GROUP_CONCAT(ScriptName)
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat
2009/6/12 bruce bedoug...@earthlink.net
Hi...
I have the following...
mysql INSERT INTO ParseScriptTBL VALUES
- ('auburnCourse.py',40,1,1),
- ('auburnFaculty.py',40,2,2),
- ('uofl.py',2,1,3),
- ('uky.py',3,1,4),
- ('ufl.py',4,1,5)
- ;
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql select * from ParseScriptTBL as p join universityTBL as u on
u.ID=p.CollegeID where u.ID=40;
+--+---+--+++
| ScriptName | CollegeID | pTypeID | ScriptID | ID |
+--+---+--+++
| auburnCourse.py | 40 | 1 | 1 | 40 |
| auburnFaculty.py | 40 | 2 | 2 | 40 |
+--+---+--+++
2 rows in set (0.00 sec)
i'd like to have a query that gives me both scripts for the college in the
same row...
keeping in mind that some colleges will have no scripts, some will have
only
one, and some will have both...
i've tried to do the query, and added a group by CollegeID' with no luck..
so how can i combine the two rows to get a single row??
thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=maxb...@gmail.com
corrupted query cache?
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=0xbfebfbffFPU,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=0x4400CNTX-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 0xf020-0xf021 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 0xf022-0xf023 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 0xf100-0xf1ff,0xf040-0xf05f,0xf800-0xfbff 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
RE: corrupted query cache?
What's your question? Heh, yah that might be important... Has anyone experienced this? Does it look like anything in the my.cnf could be altered to alleviate or assist me in diagnosing the problem? I do not get an indication of any problems in the log files or the bin-logs. How might I efficiently trouble shooting this, where might I begin? I can't figure out how to replicate it, it seems to be quite sporadic. What would corrupt query results, temporarily fixed with a mysqld restart? Max Thayer wrote: 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=0xbfebfbffFPU,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=0x4400CNTX-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 0xf020-0xf021 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 0xf022-0xf023 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 0xf100-0xf1ff,0xf040-0xf05f,0xf800-0xfbff irq 72 at device 1.0 on pci6 aac0: New comm. interface enabled aac0: Adaptec Raid Controller 2.0.0-1 aacp0: SCSI
RE: AW: [PHP] How to argue with ASP people...
QUOTE And if you believe ASP.NET only provides a few benefits, then you are adequately informed, my friend Freddie /QUOTE I agree completely! Max Michaels Systems Engineer Right Media LLC -Original Message- From: Freddie Sorensen [mailto:[EMAIL PROTECTED] Sent: Friday, December 31, 2004 10:02 AM To: [EMAIL PROTECTED] Cc: 'mail.pmpa'; mysql@lists.mysql.com Subject: AW: AW: [PHP] How to argue with ASP people... Shawn, It is worth it for performance reasons ! And yes, I am LOOKING at the code and DEBUGGING it every single day, also without the use of VS, what's the problem ? And it is global.asax, not global.asp I and my colleagues have never had a project which we didn't get right the first time, I mean in terms of having to reinstall anything, it was always very easy And if you believe ASP.NET only provides a few benefits, then you are adequately informed, my friend Freddie _ Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Gesendet: Freitag, 31. Dezember 2004 15:26 An: Freddie Sorensen Cc: 'mail.pmpa'; mysql@lists.mysql.com Betreff: Re: AW: [PHP] How to argue with ASP people... Have you actually LOOKED at the code (global.asp, *.asp, *.aspx, etc... ) that VS produces to make an ASP.NET application? Have you EVER had to be in the position to debug and modify that by hand (without the aid of VS)? I have. I ask that you try it before you continue your high praise for the product. I fully believe that M$ is so full of themselves for offering this as anything approaching efficient. It's designed in such a way that once you convert, you will have a very hard time getting away from it because it is SO convoluted in its execution. I feel that it's meant more to trap you into staying with their product than making life easier. I have tried (or assisted others) on more than one occasion to get ASP.NET functioning for full development support (server-side debugging, full VS integration, yadda yadda yadda) and have YET to see anyone get it right the first time. Usually we have to rip out and reinstall or update and upgrade which required us to compare and contrast several different explanations from MSDN about what may be wrong and how to fix it. Eventually, we made it work but it was never easy. IF you like it, fine. You may use it. I however recommend against it. Stick with PHP, ASP, PERL, or Python (whichever you prefer) but I personally don't think that ASP.NET is worth the effort for the few benefits it provides. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Freddie Sorensen [EMAIL PROTECTED] wrote on 12/31/2004 04:01:14 AM: Tony, Check out ASP.NET and you will want to forget everything about PHP ;-) Freddie -Ursprüngliche Nachricht- Von: mail.pmpa [mailto:[EMAIL PROTECTED] Gesendet: Freitag, 31. Dezember 2004 03:27 An: mysql@lists.mysql.com Betreff: RE: [PHP] How to argue with ASP people... Don't mean to start a discussion whatsoever, I love php, but one thing i can't do in php is Response.Redirect(page.asp) . Apart from that no complains so far :) * Happy New Year * Pedro Almeida. -Mensagem original- De: Tony Di Croce [mailto:[EMAIL PROTECTED] I am fairly new to PHP, but I am loving it... I have recently gotten involved in a business venture and I have been using PHP so far... Recently I have taken on a partner, and he is a big ASP guy... I am not totally against ASP, but it would have to be pretty good to get me to switch at this point (PHP seems to do everything I need)... But I will need to convince him of this... What points can I bring up in PHP's favor? In what areas does PHP trounce ASP? -- td -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- 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]
php mysql library and mysql 4.1.x
Hello all, I know the php manual warns that some functionality may not work if you use the regular mysql libraries with mysql versions greater than 4.1.x. I have found so far that MYSQL_ASSOC return types don't work, just wondering if anybody has found any other issues. I would just have the developers re-write the application using mysqli instead of mysql, but this is a very time sensitive issue. Any opinions/ideas appreciated? Regards, Max Max Michaels Right Media, LLC | 276 5th Avenue, Suite 605 | New York , NY 10001 xxx.xxx. | [EMAIL PROTECTED] | www.rightmedia.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
slow query issues
Hello all,
Recently, I have been seeing some strange behavior from a particular query on
my 4.0.21 mysql server. Here is the query in question:
SELECT size_id, sum(imps) imps, sum(clicks) clicks, sum(convs) convs,
sum(imp_revenue) imp_revenue, sum(click_revenue) click_revenue,
sum(conv_revenue) conv_revenue, sum(international_imps) international_imps,
sum(adjustments) / 60 adjs, sum(publisher_compensation) / 60 comp FROM
publisher_summary WHERE ymdh = '2004-11-01 05:00:00' AND ymdh '2004-12-01
05:00:00' AND is_ym_advertiser=0 GROUP BY size_id;
There is an index on ymdh, another on both is_ym_advertiser and ymdh, and
another on size_id. The query is using the index that is on both
is_ym_advertiser and ymdh and is scanning 7 million rows according to explain
select. Explain select also says that it is a query of type ref. The table it
is selecting from contains approximately 27 million rows.
Until a few days ago, the query would take about 2 minutes to complete. This is
a long time, but considering how often this query is run, it was acceptable.
Now this query is taking more than 30 minutes to return data and I have no idea
why. I did some testing and replicated the table to my testing environment
without the indexes. Here the query is taking the normal 2-3 minutes without
the indexes. Is it possible to determine if the index is corrupted? I also
tried to run this query adding an IGNORE INDEX to skip all the indexes in
production, but it still took forever. So this makes me think it isn't the
indexes. I don't really have a good window to run OPTIMIZE in so I just want to
see if the table or index is corrupted before I go ahead and schedule that
window. Better yet, is there a more efficient way to write this query?
Any help is greatly appreciated.
Regards,
Max
Max Michaels
Right Media, LLC | 276 5th Avenue, Suite 605 | New York , NY 10001
212.561.6475 | mmichaels {at} rightmedia.com | www.rightmedia.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Threads on FreeBSD 4.9
Can't create a new thread (errno 35). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug I am running FreeBSD 4.9 with Mysql 4.0.18 compiled with Linux Threads. I am running large inbound concurrency on Postfix which is forking several processes. How do I tune my mysql db servers to resolve this error? Thanks in advance, -Max -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Performance Tuning on FreeBSD
Hi all, I have a db that is connection heavy running on FreeBSD 4.9. The server was compiled with Linux Threads enabled. I am searching for performance tuning information. Outside of enabling Linux Threads I haven't been able to find much else. I am under the impression that I should be using innodb tables instead of myisam, and I am sure there are a slew of other things that I could tune. Is there a site/document dedicated to this? My copy of high performany mysql by Jeremy Zawodny is in the mail, so any help in the mean time would be greatly appreciated. I am getting cannot create new thread errors on my system when load gets extremely heavy, are there any additional things I can do to help this? Would FreeBSD 5.1 be any better? Thanks in advance, -Max -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: BETWEEN
Hello: I am trying to measure the difference between a single insert statement of 10,000 rows and 10,000 insert statements. It is easy for me to see the single statement takes about 2 seconds. However I can come up with no good way to get the total time for individual statements. Can anyone provide a suggestion? Thanks in advance. Try super-smack. It works great for this type of testing. http://jeremy.zawodny.com/mysql/super-smack/ Best Regards, Boyd E. Hemphill [EMAIL PROTECTED] Triand, Inc. www.triand.com O: (512) 248-2287 M: (713) 252-4688 -- 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: Strange Index Usage: select ... where foo = 90 on a varchar
On Apr 13, 2004, at 2:01pm, Michael Stassen wrote: You shouldn't be surprised. This is normal behavior. interchangeStatus is a varchar, so select fileName from outDocInterchange where interchangeStatus = 91; requires that interchangeStatus be converted to an int for each row so it can be compared to 91, rendering the index useless. On the other hand, select fileName from outDocInterchange where interchangeStatus = '91'; compares interchangeStatus to a string, which the index is designed to do. In general, an index on a column won't help if the column is input to a function. Shouldn't MySQL just cast the constant integer to a string instead? Perhaps this optimization isn't done. Also, I'm not completely sure, but I think this type of query was indexed in 3.23. Or more precisely, these queries didn't become slow until after I upgraded to 4.0.18 (from 3.23.40). Granted, ultimately I needed (and did) change the column type, but I'm curious to see if why the behavior changed. Could someone running 3.23 check the output of: explain select * from bar where foo=1 (1 without quotes) explain select * from bar where foo='1' (1 with quotes) (foo is an indexed varchar column) Is only one indexed, or are they both indexed? - Max -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange Index Usage: select ... where foo = 90 on a varchar
Lucy, you've got some EXPLAINing to do... (sorry, couldn't resist) A) select fileName from outDocInterchange where interchangeStatus = 91; B) select fileName from outDocInterchange where interchangeStatus = '91'; (A) Runs unindexed, (B) runs with the istat_date index. Can anyone explain why? My table (other columns/keys removed): Create Table: CREATE TABLE `outDocInterchange` ( `dateReceived` datetime default '-00-00 00:00:00', `interchangeStatus` varchar(20) default NULL, KEY `istat_date` (`interchangeStatus`,`dateReceived`), ) TYPE=MyISAM Obviously I need to change interchangeStatus to an int, but I was still suprised to see the results: mysql explain select fileName from outDocInterchange where interchangeStatus = 91; +---+--+---+--+-+-- +---+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+-- +---+-+ | outDocInterchange | ALL | istat_date| NULL |NULL | NULL | 37223 | Using where | +---+--+---+--+-+-- +---+-+ 1 row in set (0.08 sec) mysql explain select fileName from outDocInterchange where interchangeStatus = '91'; +---+--+---++- +---+--+-+ | table | type | possible_keys | key| key_len | ref | rows | Extra | +---+--+---++- +---+--+-+ | outDocInterchange | ref | istat_date| istat_date | 21 | const |1 | Using where | +---+--+---++- +---+--+-+ 1 row in set (0.08 sec) I'm using MySQL 4.0.18 for Solaris 8. Can anyone explain this? Or is this a bug (or missing optimization)? Thanks. - Max -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: varchar and java string in sql query ?
Jianping Zhu wrote:
I and using mysql-jdbc to do some program.
I can insert record to my db by following code:
-
stmt.executeUpdate(insert into apidbusers values('id',
'jp','zhu','em1','jian180'));
---
but
if i use
--
String str=id;
stmt.executeUpdate(insert into apidbusers values(str, 'jp',
'zhu','em1','jtan180'));
In Java you would write it like this:
stmt.executeUpdate(insert into apidbusers values('+ str +', 'jp', 'zhu','em1','jtan180'));
Max
mysql, query
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: License question
Otherwise, you may ask whether MySQL is an option or not. It means that if your logs and related information can go to other mean such as a text file then you are no need to buy license. In theory we can find option for any db appliance, let say, we can store data in regular files. However, if your program can't run without MySQL database, I think license will be required. I can for example, use PostgreSQL, but main key for me is to have multiplatform database. __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: License question
Otherwise, you may ask whether MySQL is an option or not. It means that if your logs and related information can go to other mean such as a text file then you are no need to buy license. In theory we can find option for any db appliance, let say, we can store data in regular files. However, if your program can't run without MySQL database, I think license will be required. I can for example, use PostgreSQL, but main key for me is to have multiplatform database. __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: License question
Thank you KH I think that statistics information might become important part of our system, so it is safer to buy license or have a look on others RDBMS. As far as I know, if you only require MySQL as an option or just an added features, your company do not need to purchase a license. KH -- __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
License question
My company develops commercial project which is close to well know ICQ but has slightly different appliance. Can I use MYSQL for storing list of users, collecting some statistics, logs and other related information or I have to buy license? __ Do You Yahoo!? Everything you'll ever need on one web page from News and Sport to Email and Music Charts http://uk.my.yahoo.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Connect to Mysql from java(linux)
vim m wrote: Hi: I am new to Mysql. I have installed mysql3.23.55 on linux RH7.1. Now mysql is running successfully on my system. When I try connecting thru java(mysql-connector-java-3.0.1) I am getting an Access Denied error. It says mysql@machinename access denied. Now i tried from command line: mysql -u mysql -p -h localhost. This works, but when i give:mysql -u mysql -p -h machinename it doesnt work. Why is that? The user mysql has all priviliges granted. You need to set proper hosts permissions for user. RTFM section 4 :-) Max - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Newbie: MySQL + Java + web?
Brynley wrote: Anyhow - basically it appears he writes some Java code that contains a MySQL query (which I'm still not sure how is done - but I think I can nut it out) and then calls that piece of Java code from within the browser (via HTML) - it is this part that has me bamboozled. Seach for Java Server Pages (JSP) and Servlets. Google and java.sun.com will help. Max - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Select Date Help
Thanks everyone for their help! Max Clark [EMAIL PROTECTED] wrote in message av2eai$8he$[EMAIL PROTECTED]">news:av2eai$8he$[EMAIL PROTECTED]... Hi all, I have a DATE column (CCYY-MM-DD), I would like to do a query like this: select * from table where date = '2003-01' What additional information do I need to provide for this query to work properly? Thanks in advance, Max - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Select Date Help
Hi all, I have a DATE column (CCYY-MM-DD), I would like to do a query like this: select * from table where date = '2003-01' What additional information do I need to provide for this query to work properly? Thanks in advance, Max - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
newbie - linking queries with dynamic where conditions
Hi- I am trying to write a sql query that will output (domain, transport, sum(count), sum(size)) from multiple tables for many records. When the domain field is dynamic based on the adminId passed to the query, how do I execute the second query at the same time? Thanks in advance, Max select a.domain, a.transport from transport as a, acl as b where a.id=b.transportId and b.adminId='1'; select sum(count), sum(size) from stats where email like '%a.domain'; - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: newbie - linking queries with dynamic where conditions
So I am trying to accomplish something like this: select a.domain, a.transport, sum(c.recipient_count) sum(c.recipient_size) from transport as a, acl as b, recipientstats as c where a.id=b.transportId and b.adminId='1' and c.recipient_email like '%a.domain'; But I know I am missing something because of the error. Can anyone point me in the right direction? Thanks in advance, Max Max Clark [EMAIL PROTECTED] wrote in message ataq2r$ev1$[EMAIL PROTECTED]">news:ataq2r$ev1$[EMAIL PROTECTED]... Hi- I am trying to write a sql query that will output (domain, transport, sum(count), sum(size)) from multiple tables for many records. When the domain field is dynamic based on the adminId passed to the query, how do I execute the second query at the same time? Thanks in advance, Max select a.domain, a.transport from transport as a, acl as b where a.id=b.transportId and b.adminId='1'; select sum(count), sum(size) from stats where email like '%a.domain'; - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Newbie- Help with query
Help! I am a new mysql user, I understand the basic syntax of the commands for different sql queries. I am trying to write a query and I can't seem to figure it out. I would like to select all domain,transport fields from the transport table where the transport id = a query agains the acl table (select transportId from acl where adminId='1'). When I try this I get a sql error, what do I need to do differently? Thanks in advance, Max mysql describe transport; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | id| smallint(5) unsigned | | PRI | NULL| auto_increment | | domain| char(50) | | UNI | || | transport | char(50) | | | || +---+--+--+-+-++ 3 rows in set (0.01 sec) mysql describe acl; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | smallint(5) unsigned | | PRI | NULL| auto_increment | | adminId | smallint(5) unsigned | | | 0 | | | transportId | smallint(5) unsigned | | | 0 | | | securityId | smallint(5) unsigned | | | 0 | | +-+--+--+-+-++ 4 rows in set (0.00 sec) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Newbie - group and tally help
Hi- I am trying to write a sql query that will select, group and tally records returned. select name, count from stats order by name; foo2 foo4 foo6 foo15 foo210 foo315 I would like the output to be like this: foo12 foo130 How do I accomplish this in mysql? Thanks in advance, Max - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Newbie - auto_increment multiple table insert help
Hi- I would like to run an insert query across two tables at the same time. The first table has a primary key that is auto_increment, the second table needs to insert the primary key from the first table as a reference? How do I auto-populate the tableId field with the correct entry from the first table insert? Thanks in advance, Max insert into table1 (name, desc) values (foo, foouser); id name desc 55 foo foouser insert into table2 (table1Id, text) values (?, some text); id table1Id text 69 55 some text - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Ok, i want to restart the mysql
Fajar Priyanto wrote: Hmm, thanks all.. But, before I remove the mysql installation, I want to restart it first so that it reads my.cnf file, and see if it corrects the problem. How do I restart the mysql from command prompt? I'm using RH6.2 without XWindows. /etc/init.d/mysqld restart Max - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Unable to connect to MySQL using java
Muhammad Farhan wrote: HI, I am trying to connect to MySQL using java. I have placed mm.mysql drivers in jakarta-tomcat/lib folder. but whenever i try to connect to mysql, i receive this error: Cannot connect to MySQL server on . Is there a MySQL server running on the machine/port you are trying to connect to? (java.security.AccessControlException) It doesn't look like MySQL issue (JDBC issue neither). Are you sure the security exception is thrown from JDBC driver? Plus, the error message suggests that you're specifying invalid connection parameters or are blocked eg. by firewall or some other security manager. Max - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
unprivileged user can create a database
Here is a (stripped) list of steps needed to recreate this problem.
---
# mysql -u root -p
..
mysql CREATE DATABASE `user_db`;
Query OK, 1 row affected (0.00 sec)
mysql USE mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
mysql INSERT INTO user (Host, User) VALUES ('localhost', 'user_db_mgr');
Query OK, 1 row affected (0.00 sec)
mysql INSERT INTO db (Host, User, Db, Create_priv) VALUES ('localhost',
'user_db_mgr', 'user_db', 'Y');
Query OK, 1 row affected (0.00 sec)
mysql FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
mysql \q
Bye
# mysql -u user_db_mgr
..
mysql CREATE DATABASE `user?db`;
Query OK, 1 row affected (0.00 sec)
---
From the above, you can see that this new unprivileged user was able to
create a database with a specific name. No other name was found to work.
This user should only be able to create _tables_ in the assigned database,
but it seems that the Create_priv permission somehow extends to allow this
user to create this database as well.
This is all done on MySQL 3.23.52/gcc 2.95.3/glibc-2.2.5/linux 2.4.19 (RH
7.3)
(please cc: me on replies -- I am not subscribed).
---max kalika
[EMAIL PROTECTED]
-lsit systems administrator
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: JDBC problems
mysql,query Lee Denny wrote: I'm trying to use the mm.mysql JDBC drivers on windows NT I've just downloaded the mysql-connector-java-2.0.14.zip file and extracted it into my jdk1.2.2/jre/lib/ext folder. You need to put .jar file (ie. mysql-connector-java-x.x.x-beta-bin.jar) into jre/lib/ext folder, not entire contents of zip file with subdirectories. Max - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL newbie installation question
CM Miller wrote: Very new to MySQL and I've done a search on google.com, but really couldn't find the answer that I am looking for. Where should I install MySQL? I'm running RH 7.3 and I've installed the tgz file under my directory /home/CMMiller, is this right, or anywhere else? I see it is also under /usr/share. Do as the readme says - unpack the distribution and follow instructions. Standard location for mysql distribution is /usr/local/mysql from which you should make symlinks to /usr/loca/bin etc. You can put your data files pretty much everywhere but be very careful about permissions (some directories should be wriable by root only, some has to be writable by mysql user - read error nessages if in doubt and pay attention to security issues). Test your setup by starting the daemon manually specifying installation directory, data directory and user (-u mysql). Then put a service file in /etc/init.d and make symlinks to appropriate /etc/rc.d directories. how do I login once I get it up and going, or change passwords, or do basic admin work? There is a text client called 'mysql' :-) Check it out. HTH, Max - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Compressed JDBC protocol
mysql,query Hello! Maybe I've missed something in the docs... how can I turn compression on using JDBC with Connector/J? Or is it turned on by default? If so, how can I check if it's on or off? Regards, Max - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ERROR 1146
hans schneidhofer wrote: hi list, trying to do my first steps with mysql, i have done the following : mysql USE mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql INSERT INTO mysql VALUES(localhost, sample_db, 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y'); ERROR 1146: Table 'mysql.mysql' doesn't exist With 'use' you're changing *databases*. You need to create a *table* to insert data (see 'CREATE TABLE...'). Max - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Hans Needs Help :D
The solution that works best with all broken rpm dependiences is to use --force --nodeps (verify with the man page). HTH, Max spam filter pass: sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql console buzzer problem
Hello! I can't find a way to turn off buzzer (PC speaker) in mysql console. Am I missing something or is it impossible and I should report user interface bug to bugs mailing list? mysql console version : 11.18 distribution 3.23.51 System: Windows 2000 Max - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql console buzzer problem
Mikhail Entaltsev wrote: What's wrong with your PC speaker in mysql console?! I am using the same version and have no problem with it. It's making loud noise on each error. It is *extremely* annoying. Max - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql console buzzer problem
Mikhail Entaltsev wrote: It's making loud noise on each error. It is *extremely* annoying. Try to use these ways: 1. -s, --silent option in mysql console. I don't know what does it mean (in doculmentation I found only Be more silent.), but may be it will help you. No change. 2. Generally, you can change settings of PC speaker in OS settings. No change. 3. Just turn off speaker totally (remove it from your PC :) In general it is useful when Mozilla beeps from time to time to inform me that new mail has arrived. I just don't want to annoy myself and all people around with long series of loud beeps when something goes wrong for some reason. Thanks, Max - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql console buzzer problem
Victoria Reznichenko wrote: Max, Friday, August 16, 2002, 11:36:19 AM, you wrote: MM I can't find a way to turn off buzzer (PC speaker) in mysql console. Am MM I missing something or is it impossible and I should report user MM interface bug to bugs mailing list? Option --no-beep for mysql command line client is supported since 4.0.2 OK, thank you very much! I think I'll try 4.x on monday :-) Max - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
next prev value not null
hi all, I'm in trouble trying to create a sql query to get the prev next value not null starting from a specified point ... I mean: table_a id 23 24 56 62 starting from id 56, I need the id 24 the id 62 ... I've trying several way mysql funct to do this job but w/o success :( anyone can help me, plz? many thanks in advance max - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL GROUP BY Anomaly?
Perhaps the time component of the date is different, hence you are getting back what looks like duplicate rows but they're not really? - Max Martin Anderson QA Engineer ProfitLogic Eleven Cambridge Center Cambridge, MA 02142 t: 617.218.1946 -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 09, 2002 12:01 PM To: [EMAIL PROTECTED] Subject: MySQL GROUP BY Anomaly? I have 2 tables with RecordDate in them, one has millions of records, one has hundreds of records. If I perform the following query on the table with hundreds; mysql select RecordDate - from tblInfo - WHERE RecordDate BETWEEN '2002-03-04' AND '2002-03-06' - GROUP BY RecordDate; I get three rows back in the result; ++ | RecordDate | ++ | 2002-03-04 | | 2002-03-05 | | 2002-03-06 | ++ but if I run this query against the table with millions of records I get (a small snippet of the results) | 2002-03-06 | | 2002-03-05 | | 2002-03-06 | | 2002-03-05 | | 2002-03-06 | | 2002-03-05 | | 2002-03-06 | | 2002-03-05 | | 2002-03-04 | ++ 34164 rows in set (17.78 sec) Can anyone enlighten me as to what is happening? Both tables are on the same machine, but the one with millions of records is a MERGE table. If I run the query against the individual tables in the merge, they return the proper (3 row) result. Thanks! Jay - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL GROUP BY Anomaly?
I don't want to doubt you :-) but can you show us a select including the
time component? I don't know how date comparison works in MySQL but here is
a test I ran in Oracle. I inserted some dates into a table using sysdate
which include time information and inserted more records using
to_date('2002-05-09', 'dd-mon-') which does not include time
information. These records are shown below. Now if you run the two queries
you mentioned you also get records back because date comparison that does
noy explicitly include a time assumes 12:00:00. I am thinking that if you
lool at your date fields closer you will find that some have different
timestamps.
I could be wrong but it's certainly a quick and easy comfirmation for you to
try.
1* select to_char(martin, 'dd-mon- hh:mi:ss') from martin
SQL /
TO_CHAR(MARTIN,'DD-M
09-may-2002 02:28:14
09-may-2002 02:28:19
09-may-2002 02:28:20
09-may-2002 02:28:20
09-may-2002 02:28:21
09-may-2002 12:00:00
09-may-2002 12:00:00
09-may-2002 12:00:00
09-may-2002 12:00:00
09-may-2002 12:00:00
10 rows selected.
SQL select * from martin where martin = '2002-05-09';
MARTIN
--
2002-05-09
2002-05-09
2002-05-09
2002-05-09
2002-05-09
SQL select * from martin where martin '2002-05-09';
MARTIN
--
2002-05-09
2002-05-09
2002-05-09
2002-05-09
2002-05-09
Martin Anderson
QA Engineer
ProfitLogic
Eleven Cambridge Center
Cambridge, MA 02142
t: 617.218.1946
-Original Message-
From: Jay Blanchard [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 09, 2002 1:23 PM
To: [EMAIL PROTECTED]
Subject: RE: MySQL GROUP BY Anomaly?
[snip]
Perhaps the time component of the date is different, hence you are getting
back what looks like duplicate rows but they're not really?
[/snip]
The time components are all the same. And if I say this in my query;
WHERE RecordDate = '2002-03-04' , it works (returns one row of data)
or if I do;
WHERE RecordDate '2002-03-04' , it works
I am stumped.
Jay
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Pretty Simple MAX Value question...
SELECT MAX(id) FROM (SELECT MAX(id) FROM table WHERE DATE = '20020509' LIMIT 100) Dunno if this works in MySQL, I can't access a MySQL database from here to test it out. This should return the value that you want but I would be interested to learn the usefulness of running such a query without restricting it further than simply using LIMIT 100. Do you not need to ORDER BY your data either? What if all your high IDs are after record 100? Of course, I don't know you application, just bein' nosey. - Max Martin Anderson QA Engineer ProfitLogic Eleven Cambridge Center Cambridge, MA 02142 t: 617.218.1946 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Most efficient query
SELECT main.id FROM MainTable main OUTER JOIN MapTable map ON main.id = map.id WHERE map.id = null Does this work? Give it a whirl. Another solution could be to do it like this: SELECT col1, col2, coln FROM MainTable WHERE id IN ( SELECT id FROM MainTable WHERE id NOT IN ( SELECT id FROM MapTable ) ); Does MySQL support a MINUS like command like Oracle? Try them both and report back on performance. Martin Anderson QA Engineer ProfitLogic Eleven Cambridge Center Cambridge, MA 02142 t: 617.218.1946 -Original Message- From: Augey Mikus [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 09, 2002 5:11 PM To: [EMAIL PROTECTED] Subject: Most efficient query I have two tables. One table, which (to protect the innocent :-)) we'll call MainTable, has a list of names with user info like address, etc.. and an auto-incrementing unique id for each. The other, which we'll call MapTable, is simply a key mapping table with ids from MainTable that just basically holds a list of MainTable ids to specify some sort of property (be it that they are disabled or whatever) Table: MainTable ID | NAME 1 Foo 2 Bar Table: MapTable ID --- 2 -- referring to the MainTable ID field I am trying to query the list of records in MainTable that are NOT mapped in MapTable. The query I am using is as follows: select MainTable.ID from MainTable,MapTable where MainTable.ID != MapTable.ID; this query works perfectly on a table with 10 records but when you get into the millions it takes quite a long time. Is there a faster way to get what I want? Thanks, Augey - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Columns
Hey all, Is it possible to copy the contents of one column to another column using mySQL? I just changed my table structure by adding a few more columns and I need to be able to move the data from the original column to 4 new columns and then drop the orginial. I know that the proper query for sql is that I added with ALTER and remove with DROP. But I can't find anything that would allow me to move the data from one column to another. Anything I can do? Max - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
show table status
Hi, my problem is that when i do a show table status like al% one of my innodb tables (named 'aluno') says to have 255 rows. But, in fact, it have 286 rows, by: select count (*) from aluno; Someone can help me ? __ Quer ter seu próprio endereço na Internet? Garanta já o seu e ainda ganhe cinco e-mails personalizados. DomíniosBOL - http://dominios.bol.com.br - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SupportWizard: bug in mysql: ALTER TABLE CHANGE COLUMN A can damage autoincrement column B!
Description:
I am using russian values(cp1251 charset) for ENUM items.
Here is an example how to damage ID field by query to change
F_Category Enum.
How-To-Repeat:
# Create tableA with ID field.
CREATE TABLE tableA (
ID int(11) NOT NULL auto_increment,
F_ReportID bigint(20) NOT NULL default '0',
F_Response_to varchar(99) NOT NULL default '',
F_More_reports mediumtext NOT NULL,
F_Linked_Reports mediumtext NOT NULL,
F_Responses mediumtext NOT NULL,
F_Summary varchar(99) NOT NULL default '',
F_Description mediumtext NOT NULL,
F_Solution mediumtext NOT NULL,
F_Category enum('Bug','Question','Documentation','Enhancement Request','Web
Error','User Error','Unknown') NOT NULL default 'Question',
F_Attached_file mediumtext NOT NULL,
F_Severity enum('Critical','High','Medium','Low') NOT NULL default 'Medium',
F_User varchar(99) NOT NULL default '',
F_EndUser varchar(99) NOT NULL default 'nobody',
F_FullName varchar(99) NOT NULL default '',
F_E_mail varchar(99) NOT NULL default '',
F_Telephone varchar(99) NOT NULL default '',
F_Group varchar(99) NOT NULL default '',
F_Internal_analysis mediumtext NOT NULL,
F_State enum('Open','Assigned','In-progress','In-testing','Closed') NOT NULL default
'Open',
F_Std_Solution enum('Yes','No') NOT NULL default 'No',
F_Accepted enum('Yes','No') NOT NULL default 'No',
F_Date datetime NOT NULL default '1990-01-01 00:00:00',
F_Thread_owner varchar(99) NOT NULL default '',
F_Creator_E_mail varchar(99) NOT NULL default '',
F_Modified_by varchar(99) NOT NULL default '',
F_Modification_date datetime NOT NULL default '1990-01-01 00:00:00',
F_Route mediumtext NOT NULL,
F_History mediumtext NOT NULL,
F_E_mail_History mediumtext NOT NULL,
F_CorrectSolutionCount bigint(20) NOT NULL default '0',
F_CorrectSolutionCount bigint(20) NOT NULL default '0',
F_AdjustedSolutionCount bigint(20) NOT NULL default '0',
F_Assign_date datetime NOT NULL default '1990-01-01 00:00:00',
UNIQUE KEY ID (ID),
KEY F_ReportID (F_ReportID),
KEY F_Category (F_Category),
KEY F_Severity (F_Severity),
KEY F_User (F_User),
KEY F_State (F_State),
KEY F_Std_Solution (F_Std_Solution),
KEY F_Accepted (F_Accepted),
KEY F_Date (F_Date),
KEY F_Thread_owner (F_Thread_owner),
KEY F_Modified_by (F_Modified_by),
KEY F_Modification_date (F_Modification_date),
KEY F_Assign_date (F_Assign_date),
KEY F_Assigned_to (F_Assigned_to,F_State)
) TYPE=MyISAM;
# It is to see that ID exists in this table
select ID from tableA;
# Try to alter F_Category field
ALTER TABLE tableA CHANGE COLUMN F_Category F_Category ENUM('Bug',
'tmp_sync_1005859699', 'Âîïðîñ ïîëüçîâàòåëÿ', 'Îáùèé âîïðîñ', 'Ðàñøèðåííûé çàïðîñ',
'Îøèáêà íà WEB ñåðâåðå', 'Îøèáêà ïîëüçîâàòåëÿ') NOT NULL DEFAULT 'Bug';
# It is to see what happens with ID
select ID from tableA;
Fix:
none
Submitter-Id: submitter ID
Originator:Max
Organization:
Integral Solutions Corp.
MySQL support: none
Synopsis: autoincrement ID field renamed during changing F_Category ENUM!
Severity: critical
Priority: high
Category: mysql
Class: sw-bug
Release: mysql-3.23.44 (Source distribution)
Environment:
machine, os, target, libraries (multiple lines)
System: Linux linux 2.4.14 #4 SMP 9 03:46:13 MSK 2001 i686 unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i486-suse-linux/2.95.3/specs
gcc version 2.95.3 20010315 (SuSE)
Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS=''
LIBC:
-rwxr-xr-x1 root root 1384168 20 07:52 /lib/libc.so.6
-rw-r--r--1 root root 25215580 20 07:28 /usr/lib/libc.a
-rw-r--r--1 root root 178 20 07:28 /usr/lib/libc.so
Configure command: ./configure --prefix=/usr/home/isc/mysql
--localstatedir=/usr/home/isc/mysql/data --with-client-ldflags=-all-static
--with-mysqld-ldflags=-all-static --with-berkeley-db --with-innodb --with-libwrap
--with-extra-charset=all --with-charset=cp1251
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
newibie mysqladmin host password
I have a linux redhat 7.2 system. I am trying to install bugzilla which uses mysql. I have installed apache and have completed mysql installation post tests successfully using the -p form of the commands. However, I never succeeded in getting mysql -u root -h 192.168.1.15 -p password actuallinuxrootpassword to successfully complete. It tells me that fs-linux cannot connect to MySQL host. fs-linux is believed to be the hostname of the linux box which I did not install. There is no dns setup for this box, as seems reasonable given the non-routing IP. It may be relevant to say the way I am connecting to the box: win2k-citrix-ssh-linux. It also seem relevant to me that I cannot find the /etc/my.cnf file. After this failure, I proceeded to install perl, data dump, and dbi successfully. However msql-mysql make test failed about 99% and I thought to go back to the above previous failed step. Please advise. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
european date to mysql date
Hi, I need to transform a europe date like 10/09/2001 (dd/mm/) in this format: 2001/09/10 (/mm/dd) ... how can I do using the mysql functions? many thanks in advance max - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Randomize column order
I'm sure I'm just missing something basic, but here goes... I need to create a table, populated with data, from an existing table. Easy enough: create table TEST select * from OLD_DATA Most cool. Now, let's say OLD_DATA has three columns: A, B, C. I want to create new table TEST, with all 3 columns from OLD_DATA, but.I want to totally randomize the order of column B. For example, I want to go from: FIRSTNAME LASTNAMEPID bob jones 1 marysmith 2 maddog brown 3 To this: FIRSTNAME LASTNAMEPID bob smith 1 marybrown 2 maddog jones 3 I guess what I'm looking for is something similar to this imaginary command: CREATE TABLE TEST SELECT FIRSTNAME, PID, (LASTNAME ORDER BY RAND()) FROM OLD_DATA; Any help is appreciated. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Randomize column order
There are only 8,000 rows. Unfortunately, I was really hoping for some sort of function within MySQL to do this. I'm not using PHP, but rather Perl. I could pretty much do the same thing in Perl, I guess. Is there any elegant solution or alternative within MySQL? On Friday, July 20, 2001, at 04:43 PM, Werner Stuerenburg wrote: How many rows do you have? I'd do it in php along those lines: read the rows from the old table in 3 arrays. randomize one or all of them (built in function in version 4, else do it yourself), then populate new table from those arrays. Sie schrieben am Freitag, 20. Juli 2001, 23:56:38: I'm sure I'm just missing something basic, but here goes... I need to create a table, populated with data, from an existing table. Easy enough: create table TEST select * from OLD_DATA Most cool. Now, let's say OLD_DATA has three columns: A, B, C. I want to create new table TEST, with all 3 columns from OLD_DATA, but.I want to totally randomize the order of column B. For example, I want to go from: FIRSTNAME LASTNAMEPID bob jones 1 marysmith 2 maddog brown 3 To this: FIRSTNAME LASTNAMEPID bob smith 1 marybrown 2 maddog jones 3 I guess what I'm looking for is something similar to this imaginary command: CREATE TABLE TEST SELECT FIRSTNAME, PID, (LASTNAME ORDER BY RAND()) FROM OLD_DATA; Any help is appreciated. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-ws=art- [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Herzlich Werner Stuerenburg _ ISIS Verlag, Teut 3, D-32683 Barntrup-Alverdissen Tel 0(049) 5224-997 407 · Fax 0(049) 5224-997 409 http://pferdezeitung.de - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
calculation bug
Description: MySQL lies about what ist grater than 0. How-To-Repeat: Imagine having a table... CREATE TABLE `Customer` ( `ID` int(10) unsigned NOT NULL default '0', `Seller` tinyint(3) default NULL, PRIMARY KEY (`ID`)); Seller is either 0, 1 or 2. Look at the following selects: mysql select ID,Seller from Customer limit 10; +++ | ID | Seller | +++ | 1 | 1 | | 2 | 2 | | 3 | 0 | | 4 | 2 | | 5 | 0 | | 6 | 1 | | 7 | 1 | | 8 | 1 | | 9 | 1 | | 10 | 0 | +++ 10 rows in set (0.01 sec) mysql select ID,Seller from Customer where Seller0 limit 10; +++ | ID | Seller | +++ | 1 | 1 | | 6 | 1 | | 7 | 1 | | 8 | 1 | | 9 | 1 | | 11 | 1 | | 14 | 1 | | 15 | 1 | | 17 | 1 | | 18 | 1 | +++ 10 rows in set (0.02 sec) mysql select ID,Seller from Customer where Seller!=0 limit 10; +++ | ID | Seller | +++ | 1 | 1 | | 2 | 2 | | 4 | 2 | | 6 | 1 | | 7 | 1 | | 8 | 1 | | 9 | 1 | | 11 | 1 | | 14 | 1 | | 15 | 1 | +++ 10 rows in set (0.01 sec) Has MySQL a problem comparing a tinyint value with a supplied int? I was able to reproduce this on other versions and other platforms, so what's this? Any Comments? Fix: Submitter-Id: none yet Originator:max Organization: MySQL support: none Synopsis: calculation bug Severity: serious Priority: Category: mysql Class: sw-bug Release: mysql-3.23.29-gamma (Official MySQL binary) Server: /usr/local/bin/mysqladmin Ver 8.0 Distrib 3.22.32, for -freebsd4.0 on i386 TCX Datakonsult AB, by Monty Server version 3.23.29-gamma Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 13 hours 37 min 56 sec Threads: 6 Questions: 417 Slow queries: 0 Opens: 50 Flush tables: 2 Open tables: 48 Queries per second avg: 0.008 Environment: System: FreeBSD fat.techno.net 4.0-RELEASE FreeBSD 4.0-RELEASE #0: Mon Mar 20 22:50:22 GMT 2000 [EMAIL PROTECTED]:/usr/src/sys/compile/GENERIC i386 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc GCC: Using builtin specs. gcc version 2.95.2 19991024 (release) Compilation info: CC='gcc' CFLAGS='-O3 -fomit-frame-pointer' CXX='gcc' CXXFLAGS='-O3 -fomit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' LIBC: -r--r--r-- 1 root wheel 1128450 Mar 20 2000 /usr/lib/libc.a lrwxr-xr-x 1 root wheel 9 May 9 00:41 /usr/lib/libc.so - libc.so.4 -r--r--r-- 1 root wheel 531444 Mar 20 2000 /usr/lib/libc.so.4 Configure command: ./configure --prefix=/usr/local/mysql '--with-comment=Official MySQL binary' --with-extra-charsets=complex --enable-assembler --with-named-z-libs=not-used --disable-shared Perl: This is perl, version 5.005_03 built for i386-freebsd - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Does MYODBC not support Lotus Approach
- Original Message - From: phil [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, June 27, 2001 6:06 PM Subject: Does MYODBC not support Lotus Approach I can't get it to work... does MYODBC not support Lotus Approach Hi Phil, I can confirm u that Approach can work with Mysql via MyODBC I think that is not good idea to use Approach as GUI for a mysql db/tables, but for my personal experience they work together max - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Error creating a fulltext
Hi all, ... running this query alter table item_dettagli add fulltext(categoria_prezzo,settore,movimento,soggetto,lingua) I get this error: 1005 - Can't create table '.\pickwick\#sql-3d0_2.frm' (errno: 140) Query: alter table item_dettagli add fulltext(categoria_prezzo,settore,movimento,soggetto,lingua) what does it means?? (vers. 3.23.37 on win2k box) many thanks in advance max - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
error 35 with my.cnf
I work for a web hosting company that has mysql 3.22.23 on one of our servers. The server is Free BSD unix version 3.5. It seems every few weeks it pops up with this error. System Error: DBI-connect(WWWThreads:localhost) failed: Can't create a new thread (errno 35). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug at modules/w3t.pm line 480 The code that access mysql is fine because out of the 8 servers it is on this server is the only one having problems and is the only one that has mysql on it. It is configured the exact same way as all the other server except it has mysql on it. I checked the config file for mysql and it looks fine to me. If this is something that isn't setup on the server, can anyone please point me in the right direction as to how to fix it? Here is the my.cnf file [client] #password = my_password port = 3306 #socket = /var/lib/mysql/mysql.sock [mysqld] port = 3306 #socket = /var/lib/mysql/mysql.sock #pid-file = /var/run/mysql.pid big-tables skip-locking skip-name-resolve skip-networking #log #log-update set-variable = max_allowed_packet=1M set-variable = thread_stack=128K set-variable = back_log=256 set-variable = key_buffer=5M set-variable = table_cache=64 set-variable = sort_buffer=5M set-variable = record_buffer=5M set-variable = max_connections=400 set-variable = join_buffer=5M skip-thread-priority [mysqldump] quick set-variable = max_allowed_packet=16M [mysql] no-auto-rehash [isamchk]set-variable = key_buffer=16M -Thank you. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
error 35 with my.cnf
I work for a web hosting company that has mysql 3.22.23 on one of our servers. The server is Free BSD unix version 3.5. It seems every few weeks it pops up with this error. System Error: DBI-connect(WWWThreads:localhost) failed: Can't create a new thread (errno 35). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug at modules/w3t.pm line 480 The code that access mysql is fine because out of the 8 servers it is on this server is the only one having problems and is the only one that has mysql on it. It is configured the exact same way as all the other server except it has mysql on it. I checked the config file for mysql and it looks fine to me. If this is something that isn't setup on the server, can anyone please point me in the right direction as to how to fix it? Here is the my.cnf file [client] #password = my_password port = 3306 #socket = /var/lib/mysql/mysql.sock [mysqld] port = 3306 #socket = /var/lib/mysql/mysql.sock #pid-file = /var/run/mysql.pid big-tables skip-locking skip-name-resolve skip-networking #log #log-update set-variable = max_allowed_packet=1M set-variable = thread_stack=128K set-variable = back_log=256 set-variable = key_buffer=5M set-variable = table_cache=64 set-variable = sort_buffer=5M set-variable = record_buffer=5M set-variable = max_connections=400 set-variable = join_buffer=5M skip-thread-priority [mysqldump] quick set-variable = max_allowed_packet=16M [mysql] no-auto-rehash [isamchk]set-variable = key_buffer=16M Can someone PLEASE help me. This is the third time I am sending it on the board. -Thank you. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Help.
Hello, I've been trying to make and ODBC connection with a remote database and I can't. What should I do? Something else, Can I have a connection between Visaul Basic and MySqlwith a remote database? Thank you. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
