Re: mysql_upgrade then mysqld. I feel very ID-10-T PEBKAC today
Subject: Re: mysql_upgrade then mysqld. I feel very ID-10-T PEBKAC today > 2018-02-23T14:02:33.962240Z 0 [ERROR] [MY-010735] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. > How do I run mysql_upgrade if the mysqld server won't start? Please don't bother to answer, I did figure it out, I'm sorry, for a bad question and worse followup. Apparently I'm a spammer as well. Apparently the error was caused by something else besides plugins in my configuration. After carefully running --initialize from scratch and modifying my.ini I was able to get mysqld.exe up and running just fine on Windows 64 for MySQL 8.0 RC. After that mysql_upgrade worked a treat. I did have a couple issues since it was my first time *NOT* using --initialize_insecure in this configuration but it all works if you do it right. As per usual. Sorry for the noise here, Robert Eby
mysql_upgrade then mysqld. I feel very ID-10-T PEBKAC today
2018-02-23T14:02:33.962240Z 0 [ERROR] [MY-010735] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. How do I run mysql_upgrade if the mysqld server won't start? Man how stupid am I? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: mysql Digest 12 Aug 2015 13:08:20 -0000 Issue 5317
converting from MyISAM to innodb would certainly pose problems, I guess the main question would be is MyISAM functionality a strict sub-set of innodb? I'm not sure, but maybe someone else here knows better. Good luck, Robert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Sequence Numbering
If the return value of the UPDATE stmt is zero, the following stmt is ran: By return value I assume you mean rows affected ? This is working great, and has been for many years; however, today I noticed it was not working on a particular MySQL server. By not working what exactly is the failure mode? Not getting 0 rows affected? 0 rows affected not triggering subsequent INSERT? Something else? Note: It would make me uneasy to use LAST_INSERT_ID() where there is no AUTO_INCREMENT column myself... Good luck, Robert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: get row inserted time or modified time
if you define a column with any name and a type of timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP Thanks Lucio, This is much better advice than the NOW() built-in function I would have suggested. Kind Regards, -Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Fresh install of MySQL 5.6.23 fails to start on CentOS 7.0
Cameron, Did you run the recommended configuration script manually? mysql_install_db This is run once after install to put the stuff in your datadir= location which I pointed out may be missing (and was empty upon your inspection). Thanks, Robert
Re: Merging multiple SQL requests
Hi Learner, You might want to try reading about SQL JOIN and/or CLIENT_MULTI_STATEMENTS. Using databases is all about conglomerating results, but it helps to know the environment first.
Re: Fresh install of MySQL 5.6.23 fails to start on CentOS 7.0
Hi Cameron, I've seen a similar error running on windows 7. When you look in your datadir= specified in my.ini what is there exactly (hopefully not empty)? Do you have a mysql folder containing plugin.* files at this location? (say .MYI etc) It sounds like your data folders were either not copied to the correct place or not properly configured before starting the server. It's been a while, but I recall there being an install step to getting the correct basic database files into your datadir specified in my.ini. Good Luck, Robert Eby
Re: mysql Digest 15 Aug 2014 12:32:06 -0000 Issue 5209
Wouldn't you run mysql_upgrade *before* upgrading? (ie: copy new stuff onto old DB, *then* mysql_upgrade *then* start running new software?) So I went to google, and realised that I’d probably upgraded without running mysql_upgrade. So I try.. [~] john@server% (872) mysql_upgrade -u root -p mysql More details I'm sure, but just my initial thought. Thanks, Bob On Fri, Aug 15, 2014 at 8:32 AM, mysql-digest-h...@lists.mysql.com wrote: mysql Digest 15 Aug 2014 12:32:06 - Issue 5209 Topics (messages 230517 through 230517) Incorrect Infomation in tables_priv.frm 230517 by: John McIntyre Administrivia: To unsubscribe from the digest, e-mail: mysql-digest-unsubscr...@lists.mysql.com or click: http://lists.mysql.com/mysql-digest?unsub=##L@##H To post to the list, e-mail: mysql@lists.mysql.com -- -- Forwarded message -- From: John McIntyre joh98@gmail.com To: mysql@lists.mysql.com Cc: Date: Fri, 15 Aug 2014 13:31:29 +0100 Subject: Incorrect Infomation in tables_priv.frm Hi, I have MySQL 5.1.73 running on a CentOS 6.5 Linux system. Recently, I was adding a couple of users, and when I tried to FLUSH PRIVILEGES, I got this … (root@root.server [(none)] 13:24:19 (1) $ FLUSH PRIVILEGES ; ERROR 1033 (HY000): Incorrect information in file: './mysql/tables_priv.frm’ So I went to google, and realised that I’d probably upgraded without running mysql_upgrade. So I try.. [~] john@server% (872) mysql_upgrade -u root -p mysql Enter password: Looking for 'mysql' as: mysql Looking for 'mysqlcheck' as: mysqlcheck Running 'mysqlcheck with default connection arguments Running 'mysqlcheck with default connection arguments 0wn3lou4.oc_appconfig OK 0wn3lou4.oc_clndr_calendarsOK That for a lot of lines, and then … mysql.help_relationOK mysql.help_topic OK mysql.host OK mysql.innodb_index_stats Error: Incorrect information in file: './mysql/innodb_index_stats.frm' error: Corrupt mysql.innodb_table_stats Error: Incorrect information in file: './mysql/innodb_table_stats.frm' error: Corrupt mysql.ndb_binlog_index OK mysql.plugin OK mysql.proc Error: Incorrect information in file: './mysql/proc.frm' error: Corrupt mysql.procs_priv Error: Incorrect information in file: './mysql/procs_priv.frm' error: Corrupt mysql.proxies_priv Error: Incorrect information in file: './mysql/proxies_priv.frm' error: Corrupt .. for loads of lines, and then … ERROR 1033 (HY000) at line 659: Incorrect information in file: './mysql/event.frm' ERROR 1033 (HY000) at line 661: Incorrect information in file: './mysql/event.frm' ERROR 1033 (HY000) at line 663: Incorrect information in file: './mysql/event.frm' ERROR 1033 (HY000) at line 666: Incorrect information in file: './mysql/event.frm' ERROR 1033 (HY000) at line 669: Incorrect information in file: './mysql/event.frm' ERROR 1033 (HY000) at line 672: Incorrect information in file: './mysql/event.frm' ERROR 1033 (HY000) at line 675: Incorrect information in file: './mysql/event.frm' ERROR 1033 (HY000) at line 678: Incorrect information in file: './mysql/event.frm' ERROR 1033 (HY000) at line 681: Incorrect information in file: './mysql/event.frm' ERROR 1033 (HY000) at line 684: Incorrect information in file: './mysql/event.frm' ERROR 1033 (HY000) at line 686: Incorrect information in file: './mysql/event.frm' ERROR 1033 (HY000) at line 714: Incorrect information in file: './mysql/tables_priv.frm' FATAL ERROR: Upgrade failed There are four databases on that server, two of them critical, production databases. I can get around the privileges problem by stopping and starting the database out of office hours, but even then, when I do … 140815 13:28:29 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql 140815 13:28:29 InnoDB: Initializing buffer pool, size = 8.0M 140815 13:28:29 InnoDB: Completed initialization of buffer pool 140815 13:28:29 InnoDB: Started; log sequence number 4 2002382636 140815 13:28:29 [ERROR] /usr/libexec/mysqld: Incorrect information in file: './mysql/tables_priv.frm' 140815 13:28:29 [ERROR] /usr/libexec/mysqld: Incorrect information in file: './mysql/event.frm' 140815 13:28:29 [ERROR] Cannot open mysql.event 140815 13:28:29 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler. 140815 13:28:29 [Note] /usr/libexec/mysqld: ready for connections. The user changes are taken into account, however. Needless to say, mysql_dump doesn’t work either. Any advice gratefully received. John.
Re: Help with cleaning up data
delete b from icd9x10 a join icd9x10 b on a.icd9 = b.icd9 and a.id b.id ... CREATE TABLE `ICD9X10` ( ... id icd9 icd10 25 29182 F10182 26 29182 F10282 ... Good luck, Bob
RE: Unable to connect to foreign data source
CONNECTION = 'mysql://root:root@*stripped*:3306/Prelude_copy/test001'; Should be more like: CONNECTION = 'mysql://root:stripped_password@localhost/penrepository/test001'; Just seems word if you're showing us your password is root but not host... I ran your example just fine against localhost on MySQL 5.6 Win7-64bit. -Bob
Recovery from a MySQL dump is too long !
Hello *, My INNODB database has a size of 80GO. I've a replication setup on 3 slaves and I backup my db from them. If a problem occurs on the database, a recovery from a dump takes around 6H ! That's too long for us. 2 ideas : 1. Stop the slave and rsync the folder /var/lib/mysql to another server 2. Stop the slave, take a dump and when it's finish, pull the dump in another MySQL instance daily. Do you have another ideas ? What's your advices ? Thanks in advance,
Re: Why is Host option Failing?
On Jul 5, 2010, at 10:04 AM, Michael Satterwhite wrote: [...snip...] On 5 July 2010 03:35, Michael Satterwhite mich...@weblore.com wrote: On Sunday, July 04, 2010 06:36:00 pm you wrote: [...snip...] I still can't connect via mysql -h photon -u michael -p?? On my home computer I entered the following (note there is no space between the -p and the password): $ mysql -h www.my_abc_xyz.com -u myUserNm -pMyPasswrd The remote computer let me into mysql and I typed: mysql use mysql mysql select host, user, Password, Select_priv from user; +--+--+---+-+ | host | user | Password | Select_priv | +--+--+---+-+ | localhost| myUserNm | *ABC8C800D9A264876A32F5175DE21C1A0B89XYZ | Y | | %| myUserNm | *ABC8C800D9A264876A32F5175DE21C1A0B89XYZ | Y | +--+--+---+-+ Your results should be similar. HTH, Bob
Re: C API Function for count(*)
You might get closer to what you want if you put your command in a text file and run it from the command line. On a Mac OS X, I put a similar command: select count(*) from testTable; into a small text file: testCount.txt and ran this command from the Terminal: mysql -u username -ppassword /Users/myname/Documents/testCount.txt The result was: COUNT(*) 12 without the decorations. Bob On May 14, 2010, at 11:35 PM, Dan Nelson wrote: In the last episode (May 14), Tim Johnson said: I have MySQL version 5.0.84 on linux slackware 13.0 32-bit. I am working with a relatively new API written in a programming language with a small user base (newlisp). The newlisp API imports a number of C API functions from the system MySQL shared object. If I were to issue a count(*) query from my monitor interface: Example: mysql select count(*) from clients; +--+ | count(*) | +--+ | 16 | +--+ If select count(*) from clients is issued from the newlisp API, is there a a C API function that would return '16'? You can't do it with one function call, but you can do it, since the MySQL cli was able to print 16 in your example above, and it was written in C. Take a look at mysql_store_result(), mysql_num_fields(), mysql_field_count(), mysql_fetch_row(), and mysql_fetch_lengths(). There's a simple code fragment to print a resultset on this page: http://dev.mysql.com/doc/refman/5.1/en/mysql-fetch-row.html -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=bobc...@earthlink.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Count Query question
Keith: Does this work? SELECT products_date_available, COUNT(products_quantity) FROM products WHERE products_quantity 0 GROUP BY products_date_available Hope this helps, Bob On May 12, 2010, at 3:06 PM, Keith Clark wrote: On Wed, 2010-05-12 at 10:13 -0400, Keith Clark wrote: Chris, Here is my full table definition: CREATE TABLE `products` ( `products_id` int(15) NOT NULL AUTO_INCREMENT, `products_quantity` int(4) NOT NULL, `products_model` varchar(15) NOT NULL DEFAULT '', `products_image` varchar(64) DEFAULT NULL, `products_price` decimal(15,4) DEFAULT NULL, `products_date_added` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `products_last_modified` datetime DEFAULT '2008-10-01 00:00:00', `products_date_available` datetime DEFAULT '2008-10-01 00:00:00', `products_weight` decimal(5,2) DEFAULT '0.50', `products_status` tinyint(1) NOT NULL DEFAULT '1', `products_tax_class_id` int(11) DEFAULT '1', `manufacturers_id` int(11) DEFAULT NULL, `products_ordered` int(11) DEFAULT '0', `products_format` varchar(20) DEFAULT NULL, `abebooks_price` decimal(15,4) DEFAULT NULL, PRIMARY KEY (`products_id`,`products_model`), UNIQUE KEY `products_model` (`products_model`), KEY `idx_products_date_added` (`products_date_added`), KEY `manufacturers_id` (`manufacturers_id`) ) ENGINE=MyISAM AUTO_INCREMENT=17418 DEFAULT CHARSET=latin1 So, I'd like to create a report that grouped by products_date_available, counts all records before products_date_available with a products_quantity0. I don't think I'm asking this question properly. For every date in products_date_available in the table, I'd like to know the count of items available with products_quantity0 up until that date. So if there are 500 days in the table, there should be 500 rows in the report. Each showing the products available as of that date in time. I hope that clarifies it. I can write a query to do so for each individual date, just not a report for all dates at the same time. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=bobc...@earthlink.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Count Query question
Kevin: I assumed the following data: products_id products_date_available products_quantity 11 2010-05-01 1 11 2010-05-02 0 11 2010-05-03 3 11 2010-05-04 3 11 2010-05-05 3 11 2010-05-06 1 11 2010-05-07 0 11 2010-05-08 3 11 2010-05-09 3 11 2010-05-10 3 11 2010-05-11 3 11 2010-05-12 3 22 2010-05-01 1 22 2010-05-02 2 22 2010-05-03 0 22 2010-05-04 3 22 2010-05-05 3 22 2010-05-06 1 22 2010-05-07 0 22 2010-05-08 3 22 2010-05-09 0 22 2010-05-10 3 22 2010-05-11 3 22 2010-05-12 3 33 2010-05-01 1 33 2010-05-02 2 33 2010-05-03 3 33 2010-05-04 3 33 2010-05-05 3 33 2010-05-06 0 33 2010-05-07 0 33 2010-05-08 3 33 2010-05-09 3 33 2010-05-10 0 33 2010-05-11 3 33 2010-05-12 3 and used the following query: SELECT products_date_available, COUNT(products_quantity), SUM(products_quantity) FROM products WHERE products_quantity 0 GROUP BY products_date_available and got the following results: products_date_available COUNT SUM 2010-05-01 00:00:00 3 3 2010-05-02 00:00:00 2 4 2010-05-03 00:00:00 2 6 2010-05-04 00:00:00 3 9 2010-05-05 00:00:00 3 9 2010-05-06 00:00:00 2 2 2010-05-08 00:00:00 3 9 2010-05-09 00:00:00 2 6 2010-05-10 00:00:00 2 6 2010-05-11 00:00:00 3 9 2010-05-12 00:00:00 3 9 One line for each day except that 2010-05-07 is missing because each product had 0 quantity on that day. For example, on 2010-05-01, there were 3 products (each with a quantity of 1) for a total quantity of 3. I wonder if I am representing your situation correctly. What am I missing? Bob On May 12, 2010, at 8:00 PM, Keith Clark wrote: Hi Bob, No, actually it does not. I'm looking for the count of items. From your query example I only get two rows. This table has over 2 1/2 years of daily sales data. Maybe I'm not stating my question correctly...h Thanks for responding though, greatly appreciated. Keith On Wed, 2010-05-12 at 19:46 -0500, Bob Cole wrote: Keith: Does this work? SELECT products_date_available, COUNT(products_quantity) FROM products WHERE products_quantity 0 GROUP BY products_date_available Hope this helps, Bob On May 12, 2010, at 3:06 PM, Keith Clark wrote: On Wed, 2010-05-12 at 10:13 -0400, Keith Clark wrote: Chris, Here is my full table definition: CREATE TABLE `products` ( `products_id` int(15) NOT NULL AUTO_INCREMENT, `products_quantity` int(4) NOT NULL, `products_model` varchar(15) NOT NULL DEFAULT '', `products_image` varchar(64) DEFAULT NULL, `products_price` decimal(15,4) DEFAULT NULL, `products_date_added` timestamp NULL DEFAULT CURRENT_TIMESTAMP, `products_last_modified` datetime DEFAULT '2008-10-01 00:00:00', `products_date_available` datetime DEFAULT '2008-10-01 00:00:00', `products_weight` decimal(5,2) DEFAULT '0.50', `products_status` tinyint(1) NOT NULL DEFAULT '1', `products_tax_class_id` int(11) DEFAULT '1', `manufacturers_id` int(11) DEFAULT NULL, `products_ordered` int(11) DEFAULT '0', `products_format` varchar(20) DEFAULT NULL, `abebooks_price` decimal(15,4) DEFAULT NULL, PRIMARY KEY (`products_id`,`products_model`), UNIQUE KEY `products_model` (`products_model`), KEY `idx_products_date_added` (`products_date_added`), KEY `manufacturers_id` (`manufacturers_id`) ) ENGINE=MyISAM AUTO_INCREMENT=17418 DEFAULT CHARSET=latin1 So, I'd like to create a report that grouped by products_date_available, counts all records before products_date_available with a products_quantity0. I don't think I'm asking this question properly. For every date in products_date_available in the table, I'd like to know the count of items available with products_quantity0 up until that date. So if there are 500 days in the table, there should be 500 rows in the report. Each showing the products available as of that date in time. I hope that clarifies it. I can write a query to do so for each individual date, just not a report for all dates at the same time. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
failed-mysql-bugreport
mysql.sock doesn't exist on the hard drive. Originator:Charlie Organization: organization of PR author (multiple lines) MySQL support: [none | licence | email support | extended email support ] Synopsis: synopsis of the problem (one line) Severity: [ non-critical | serious | critical ] (one line) Priority: [ low | medium | high ] (one line) failed-mysql-bugreport SEND-PR: -*- send-pr -*- SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as SEND-PR: will all comments (text enclosed in `' and `'). SEND-PR: From: root To: mysql@lists.mysql.com Subject: [50 character or so descriptive subject here (for reference)] Description: precise description of the problem (multiple lines) How-To-Repeat: code/input/activities to reproduce the problem (multiple lines) Fix: how to correct or work around the problem, if known (multiple lines) Submitter-Id: submitter ID Originator:Charlie Organization: organization of PR author (multiple lines) MySQL support: [none | licence | email support | extended email support ] Synopsis: synopsis of the problem (one line) Severity: [ non-critical | serious | critical ] (one line) Priority: [ low | medium | high ] (one line) Category: mysql Class: [ sw-bug | doc-bug | change-request | support ] (one line) Release: mysql-6.0.7-alpha (FreeBSD port: mysql-client-6.0.7) C compiler:cc (GCC) 4.2.1 20070719 [FreeBSD] C++ compiler: c++ (GCC) 4.2.1 20070719 [FreeBSD] Environment: machine, os, target, libraries (multiple lines) System: FreeBSD kenny.tech39.net 7.0-RELEASE FreeBSD 7.0-RELEASE #0: Sun Feb 24 19:59:52 UTC 2008 [EMAIL PROTECTED]:/usr/obj/usr/src/sys/GENERIC i386 Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Using built-in specs. Target: i386-undermydesk-freebsd Configured with: FreeBSD/i386 system compiler Thread model: posix gcc version 4.2.1 20070719 [FreeBSD] Compilation info (call): CC='cc' CFLAGS='-O2 -fno-strict-aliasing -pipe ' CXX='c++' CXXFLAGS='-O2 -fno-strict-aliasing -pipe -O2 -fno-strict-aliasing -pipe -felide-constructors -fno-rtti -fno-exceptions' LDFLAGS='' ASFLAGS='' Compilation info (used): CC='cc' CFLAGS=' -O2 -fno-strict-aliasing -pipe ' CXX='c++' CXXFLAGS=' -O2 -fno-strict-aliasing -pipe - O2 -fno-strict-aliasing -pipe -felide-constructors -fno-rtti -fno- exceptions -fno-implicit-templates -fno-exceptions -fno-rtti -DMYSQLD_NET_RETRY_COUNT=100 ' LDFLAGS=' ' ASFLAGS='' LIBC: -r--r--r-- 1 root wheel 1026692 Feb 24 2008 /lib/libc.so.7 -r--r--r-- 1 root wheel 2127736 Feb 24 2008 /usr/lib/libc.a lrwxrwxrwx 1 root wheel 14 Aug 26 13:53 /usr/lib/libc.so - /lib/libc. so.7 Configure command: ./configure '--localstatedir=/var/db/mysql' '- -infodir=/usr/local/info' '--without-debug' '--without-readline' '--without-libedit' '--with-libwrap' '--with-mysqlfs' '--with-low- memory' '--with-comment=FreeBSD port: mysql-client-6.0.7' '--enable- thread-safe-client' '--with-plugins=max-no-ndb' '--enable-assembler' '--with-named-thread-libs=-pthread' '--without-server' '--prefix=/usr/local' '--mandir=/usr/local/man' '--infodir=/usr/local/info/' '--build=i386- portbld-freebsd7.0' 'build_alias=i386-portbld-freebsd7.0' 'CC=cc' 'CFLAGS=-O2 -fno-strict-aliasing -pipe ' 'CXX=c++' 'CXXFLAGS=-O2 -fno-strict-aliasing -pipe -O2 -fno-strict-aliasing -pipe -felide- constructors -fno-rtti -fno-exceptions' kenny# ./mysqladmin create databasename /mysqladmin: connect to server at 'localhost' failed error: 'Can't connect to local MySQL server through socket '/tmp/mysql. sock' (2)' Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists! kenny# find / -name mysql.sock kenny# [1]Done find / -name mysql.sock kenny# Nothing, zip, nada, the file doesn't exist on the hard drive. How do I create it? lot of other users are having the same problem, I haven't found a solution yet. Thanks Bob === EASY and FREE access to your email anywhere: http://Mailreader.com/ === -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQLIntegrityConstraintViolationException
I am working on a project where I have have several processes writing records to an InnoDB table. There are two separate queries doing the inserts. In maybe one out of a couple 100 inserts, I get an integrity constraint violation error, complaining about a duplicate entry for key 1. The first is a Java class, and the query is simple. The query passed to PreparedStatement is: INSERT INTO buildsteps (buildid, workflowstepid, inputlocation, creationtime) VALUES (?, ?, ?, NOW()); There are roughly 20 different threads doing these inserts. The second is a MySql trigger on a different table, that writes to buildsteps. This query is a little more complicated: AFTER INSERT ON `builds` FOR EACH ROW INSERT INTO buildsteps (buildid, workflowstepid, inputlocation, creationtime) SELECT builds.id, workflowsteps.id, builds.initiallocation, builds.creationtime FROM builds, workflowsteps, workflowsteprelationships WHERE builds.id = NEW.id AND workflowsteps.workflowid = NEW.workflowid AND workflowsteps.id = workflowsteprelationships.nextworkflowstepid AND workflowsteprelationships.previousworkflowstepid IS NULL If I generate builds records 10 at a time while the Java threads are running, as I said, about one in every 100 or so inserts seems to generate this error. None of the specified fields (buildid, workflowstepid, inputlocation, creationtime) are unique keys in buildsteps. The primary key of buildsteps is an auto increment field. The values that the integrity constraint error complains about are primary key values in buildsteps, and there is only one key on that table. I get the error both when I invoke the trigger (I get an error on the mysql command line) and when the Java query runs (I get a MySQLIntegrityConstraintViolationException thrown). The error seems to be an interaction between the two queries, that is, it only seems to occur when both processes are writing to the table, but I am not positive about that. I do not have any transaction code in place in the Java class, because I understand it is unnecessary for single queries. Do you have any ideas about what might be going on? Thanks for your help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SOLVED: Problem with *very* slow replication, FreeBSD 6.2
Running on our systems, we have had the replica load data and then started. The longest delta was about 28 hours behind the master. The slave status faithfully reported how far behind the master it was, when the slave was started, even as it was loading its relay-logs from the master which if I remember correctly took a couple of hours that first time. During that first part when the relay logs were loading, the seconds behind would increase and when the relay log caught up the delta started decreasing rapidly to zero. We are running x86-64bit hardware with RH Linux and a 1Gbit ethernet link between master and slave (nothing exotic). The load on the link never seems to an issue, so we have never monitored it closely. So, if you are happy with the situation then it is solved. Cheers, Bob Bankay Baron Schwartz wrote: Christopher E. Brown wrote: On Sat, 3 Nov 2007, bob b wrote: So, a slave is down for 8hrs. It comes online and pulls the binlog in 120 seconds. The seconds behind master does not reflect 8hrs, but how many seconds (at current processing rate) before the slave finishes the relay logs. The seconds behind master value is really seconds until currency with the relay logs and should prolly be documented as such. This is incorrect. In most circumstances, it's basically the difference between the timestamp of the binlog event the SQL thread is currently processing, and the master's current timestamp (as fetched by the I/O thread). So it really is what it sounds like: the seconds behind the master. If it says 100, it means the slave is processing an event that took place 100 seconds ago on the master. You can read the source code in show_master_info() in sql/slave.cc. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Slow query involving ORDER BY
Couple of things to read that may help:
http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html
http://hackmysql.com/case3
On another note, you should really change all of those ip address columns
from varchar to int with the ip encoded as 4 bytes. You will save A LOT of
space in both your index and table. And you should reduce the other varchar
columns to the smallest amount possible.
-Original Message-
From: Mark Ponthier [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 14, 2007 10:52 AM
To: mysql@lists.mysql.com
Subject: RE: Slow query involving ORDER BY
Sorry,
That was really hard to read. Here it is again:
mysql explain select fsys.facility, fsys.severity, h.host, fsys.src_ip,
fsys.src_time, fsys.content, fsys.tag
- from fs_syslog fsys, hosts h
- where fsys.src_ip=h.ip
- and h.status 4
- and h.hostid in
(80,75,81,78,79,76,24,25,39,33,34,35,16,60,37,38,54,26,27,28,29,30,31,32
,51,59,58,13,40,41,45,46,87,82,88,84,85,86,83,73,68,74,70,71,72,69,61,66
,63,67,64,65)
- AND h.host
in('FSBOS2950','FSBOSDC','FSBOSFireScope','FSBOSFirewall','FSBOSRTR','FS
BOSSQL','CA_CoLoc','CA_LAB_PIX','FS_CoLo_2950','FSDALPIX','FSDALSWT01','
FSDALSWT02','FSDEV01_10_11','fsdev_Oracle','FSTESTPIX','FSWIN2K3-IIS','F
SWINSRV01','FSWINSRV02','FS_Dallas_2950','Phone Switch','RedMoon
Gateway','XO
Gateway','FS_CoLo_2950','FS_Dallas_2950','FS_Dev1014','FS_Offc_Printer',
'FS_Offc_ScanPrnter','Demo-Cisco-1','Demo-Cisco-2','Demo-Linux','Demo-pi
x','Demo-Switch-1','Demo-Switch-2','Demo-Windows','Phone
Switch','RedMoon Gateway','XO
Gateway','FSDEV01_10_11','FS_Dallas_2950','FS_Dev1014','FS_Offc_Printer'
,'FS_Offc_ScanPrnter','FSDTC2950','FSDTCDC','FSDTCFireScope','FSDTCIntra
net','FSDTCPIX','FSDTCRTR','FSDTCSQL','FSENG3560','FSENGDC','FSENGFireSc
ope','FSENGIntranet','FSENGPIX','FSENGRTR','FSENGSQL','FSSALES01','FSSAL
ESDB','FSSALESFW','FSSALESPhone','FSSALESSWITCH','FSSALESWEB')
- AND fsys.src_time = DATE_SUB(CURRENT_TIMESTAMP(),INTERVAL 6
HOUR)
- ORDER BY fsys.src_time DESC
- limit 0,10;
++-+---+---+-+--
---+-+--+---+---
---+
| id | select_type | table | type | possible_keys | key
| key_len | ref | rows | Extra
|
++-+---+---+-+--
---+-+--+---+---
---+
| 1 | SIMPLE | fsys | range | fs_syslog_1,fs_syslog_2 |
fs_syslog_1 | 5 | NULL | 23664 | Using where; Using temporary;
Using filesort |
| 1 | SIMPLE | h | ALL | PRIMARY,hosts_1,hosts_2 | NULL
| NULL| NULL |96 | Using where
|
++-+---+---+-+--
---+-+--+---+---
---+
2 rows in set (0.00 sec)
Thanks,
Mark Ponthier
-Original Message-
From: Mark Ponthier [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 14, 2007 9:47 AM
To: mysql@lists.mysql.com
Subject: RE: Slow query involving ORDER BY
I've added both indexes but don't see an improvement in speed. Below are
the the descriptions of each table and the explain plan:
CREATE TABLE `hosts` (
`hostid` bigint(20) unsigned NOT NULL default '0',
`host` varchar(64) NOT NULL default '',
`dns` varchar(64) NOT NULL default '',
`useip` int(11) NOT NULL default '1',
`ip` varchar(15) NOT NULL default '127.0.0.1',
`port` int(11) NOT NULL default '10050',
`status` int(11) NOT NULL default '0',
`disable_until` int(11) NOT NULL default '0',
`error` varchar(128) NOT NULL default '',
`available` int(11) NOT NULL default '0',
`errors_from` int(11) NOT NULL default '0',
PRIMARY KEY (`hostid`),
KEY `hosts_1` (`host`),
KEY `hosts_2` (`status`),
KEY `hosts_3` (`ip`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `fs_syslog` (
`id` bigint(20) NOT NULL auto_increment,
`facility` int(11) default NULL,
`severity` int(11) default NULL,
`host` varchar(255) default NULL,
`src_ip` varchar(255) default NULL,
`src_time` timestamp NULL default NULL,
`srv_ip` varchar(255) default NULL,
`srv_time` timestamp NULL default NULL,
`tag` varchar(255) default NULL,
`content` varchar(255) default NULL,
PRIMARY KEY (`id`),
KEY `fs_syslog_1` (`src_time`),
KEY `fs_syslog_2` (`src_ip`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Below is the explain plan:
idselect_typetable type possible_keys
key key_lenref rows
Extra
1 SIMPLE fsysrange
fs_syslog_1,fs_syslog_2 fs_syslog_15
{null} 17680Using where; Using temporary; Using
filesort
1 SIMPLE h ALL
PRIMARY,hosts_1,hosts_2 {null} {null}
{null} 96Using where
RE: a Linux -csh script to refresh test with production
well, you'd want to come up with your specifics but it's pretty simple using ssh... put this is your shell... $ mysqldump db-name | mysql -h remote.box.com db-name $ mysqldump db-name | ssh [EMAIL PROTECTED] mysql db-name $ mysqldump db-name foo | ssh [EMAIL PROTECTED] mysql bar hope this helps bb -Original Message- From: Brown, Charles [mailto:[EMAIL PROTECTED] Sent: Tue 3/27/2007 12:17 PM To: mysql@lists.mysql.com Subject: a Linux -csh script to refresh test with production Hello all. Does anyone out there (in mysql world) have a Linux -csh script to refresh test with production data. My developers would like their test database to be refreshed nightly with production data. The production and test mysql servers do not run in the same box. They run on different boxes. Therefore there is some ftp or scp required Thanks This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you. -- 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: Searching for Dates
To you all,
Thanks for your input. The TO_DAYS(date_col)%4 = TO_DAYS('2006-4-17')%
4 where statement worked perfectly. And, thanks for the reference to
the manual. It can be a bit daunting for a newbie.
Bob Cooper
Dan:
Thanks!
Jim
In the last episode (Mar 18), Jim Ginn said:
In the last episode (Mar 16), Bob Cooper said:
I am working with MySQL ver 5.1 on a Ubuntu Linux x86_64. I am
new
to both SQL and MySQL. I have been able to query out most of the
data I need from my tables without any issues but his one has
stumped me.
I am trying to query data associated with specific dates. The
dates are not sequential but somewhat sporadic. I would like to
query out data/dates that are every 4 days from a starting date.
2006-4-17, 2006-4-21, etc.
I have tried ADDDATE('2006-4-14',interval 4 day)=Date_col but
it
give me only the next date 2006-4-21.
You could do it b
y converting to a daynumber (the number of days
since year 0) and doing modulo arithmetic:
WHERE TO_DAYS(date_col)%4 = TO_DAYS('2006-4-17')%4
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_to-days
I've used the TO_DAYS on a SELECT statement ie:
SELECT * FROM properties WHERE
(TO_DAYS(NOW()) - TO_DAYS(CreationDate) = 1) ORDER BY id DESC
however it didn't seem to take advantage or use the index on that
field
(ie. CreationDate) ...
Right; mysql needs CreationDate all by itself on one side of a
comparison operator to be able to use an index. In your case, try
WHERE CreationDate = CURDATE() - INTERVAL 1 DAY
, assuming CreationDate is a 'date' field type. If it's a datetime,
you'll need to use a BETWEEN operator and cover the time range from
midnight to midnight on your target day.
--
Dan Nelson
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Searching for Dates
Hi,
I am working with MySQL ver 5.1 on a Ubuntu Linux x86_64.
I am new to both SQL and MySQL.
I have been able to query out most of the data I need from my tables
without any issues but his one has stumped me.
I am trying to query data associated with specific dates.
The dates are not sequential but somewhat sporadic. I would like to
query out data/dates that are every 4 days from a starting date.
2006-4-17, 2006-4-21, etc.
I have tried ADDDATE('2006-4-14',interval 4 day)=Date_col
but it give me only the next date 2006-4-21.
any help would be greatly appreciated,
Coop.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Can a row be refered using row number?
Why would you want to do this? As data moves around within the table the updates will be in error. Wouldn't it be easier to assign a unique key to each row, search for the key or unique set of information and update the resulting row? Bob -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, August 10, 2006 4:43 AM To: mysql@lists.mysql.com Subject: Can a row be refered using row number? Hi All, Is there any way to refer a row based on the row number or row count? I just the effect as below... E.g. UPDATE TBL_NAME SET COL_NAME = xyz WHERE ROW_NO=4 Or SELECT COL_NAME FROM TBL_NAME WHERE ROW_NO=5 Regards, Ravi K The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL service startup time
Considering there are a number of pieces that are involved in the connection being ready why not create a small program that tries to connect and checks the return value. If it fails sleep for 500ms to 1 sec and try again for up to X-times before aborting altogether? Bob -Original Message- From: Rob Desbois [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 05, 2006 11:51 AM To: mysql@lists.mysql.com Subject: MySQL service startup time Hi, I have an application self-installer program which also installs MySQL and sets it up. This is all on Windows. I have a problem in that when the installer runs 'net start MySQL', it returns immediately but the MySQL daemon is not ready for connections immediately. As the next step in the installation is to create the application database, I need to wait until I can connect. What's the best way to achieve this? At the moment I have a rather crude 5 second 'sleep', but that isn't always long enough. Any ideas? --Rob __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ -- 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: New to the group
If you will excuse my ignorance. I have no immediate need for this, but have often asked what the pros/cons there are writing a WEB based interface in PHP vs. say Perl. Do you have any insight into that? Thanks Bob -Original Message- From: mos [mailto:[EMAIL PROTECTED] Sent: Thursday, June 22, 2006 3:39 PM To: mysql@lists.mysql.com Subject: Re: New to the group At 08:46 AM 6/22/2006, Nicholas Vettese wrote: Hello, My name is Nick, and I am a new MySQL user. My hope is not to become a PITA, so I will make sure that any question is straight and to the point with the information needed to answer the question. My skill in MySQL is pretty low, and I am looking to build a website for myself that will take information and save it to a database. At this time, I have a login, registration, change/lost password functionality working from a book that I read, but I am looking to expand my knowledge into more robust site. I am not looking to become the master programmer, just someone with enough knowledge and skill to accomplish his goals. Thanks, Nick Welcome Nick, You've come to the right place. There are a couple of books on MySQL that are quite good and I'd like to recommend. MySQL 3rd Edition by Paul Dubois and MySQL Cookbook by Paul Dubois (I think these guys are relatedvbg) If you are using PHP to build your website I found PHP and MySQL for Dynamic Web Sites : Visual QuickPro Guide (2nd Edition) (Visual Quickpro Guide) to be quite good and gets you going quite fast. There's not a lot of reading to do and they have you writing PHP code the first day. If you want a more thorough book on PHP MySQL there is: PHP and MySQL Web Development (3rd Edition) (Developer's Library) (Paperback) by Luke Welling, Laura Thomson There are also PHP/Mysql tutorials on the web but I don't know how good they are. You'll get up to speed faster by getting some of these books. Of course if you're not using PHP, then someone else can jump in with some reading suggestions. Mike -- 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: Is This A Redundant Info Example?
Bad design that violates basic DB design checks. Redundant information will become a major problem for you going forward. Unless you have hard and fast performance issues they require it, just don't do it:-) Create a single table that contains customer info and reference the information using Foreign keys in your other tables that require customer information. Bob -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Friday, May 19, 2006 8:57 AM To: Mark Sargent; mysql@lists.mysql.com Subject: RE: Is This A Redundant Info Example? [snip] Why have customer info in both? Delivery and Billing info makes sense, but why the redundant info in both? Anyone got views on this? Do/would you do it differently, and could you tell us why? Cheers. [/snip] It is bad database design IMHO. -- 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]
duration query: how to ?
Hi, My clients can put their articles on my website. They pay for the real utilisation of the site. If a client has used 2 slots for all year, then he pays for them. Now my articles table has a starddate (when the client has started to sell his product) and an enddate (when the article is sold). I'd like to retrieve the total amount of time the articles have been in the site from 2005-01-01 to 2005-01-31 and divide them by 365 (or 366 if leap year). if an article has been online from 2004-10-01 to null (meaning the article hasn't been sold yet) then I may count 365 days How to do so with a query ? fields are client.idclient, article.idclient, article.idarticle, article.startdate, article.enddate. Please help me, I don't know how to retrieve those values and I need them to get payed. Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unknown column 'testcase_root.Test' in 'order clause'
I am receiving an error Unknown column 'testcase_root.Test' in 'order clause' I do not understand why the error is pointing to this as an error, nor if it means anything that the name of the column is correctly identified in the query below as testcase_root.TestID. I have checked the DB and the column is present. Can someone help point out what I am missing and or steps to debug the problem. SELECT testplans.SubTestCaseKey, testcase_root.ID, testplans.testcasesuffix_name_FK, testcase_root.TestID, testcase_root.TestDesc, testplans.FSAGA, testplans.Priority, testplans.tester_list_Name_FK FROM testcase_root LEFT JOIN testplans ON testcase_root.ID = testplans.testcase_root_ID_FK WHERE (((testplans.plantriggers_ID_FK)=76530)) OR (((testplans.plantriggers_ID_FK) Is Null)) ORDER BY testcase_root.TestID; Robert M. Bartis Lucent Technologies, Inc Tel: +1 732 949 4565 Mail: [EMAIL PROTECTED] Pgr: [EMAIL PROTECTED]
RE: Unknown column 'testcase_root.Test' in 'order clause'
Sorry:-\ Meant to say I do not understand why mySQL is pointing to this as an error Bob -Original Message- From: Bartis, Robert M (Bob) [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 01, 2006 5:30 PM To: 'mysql' Subject: Unknown column 'testcase_root.Test' in 'order clause' I am receiving an error Unknown column 'testcase_root.Test' in 'order clause' I do not understand why the error is pointing to this as an error, nor if it means anything that the name of the column is correctly identified in the query below as testcase_root.TestID. I have checked the DB and the column is present. Can someone help point out what I am missing and or steps to debug the problem. SELECT testplans.SubTestCaseKey, testcase_root.ID, testplans.testcasesuffix_name_FK, testcase_root.TestID, testcase_root.TestDesc, testplans.FSAGA, testplans.Priority, testplans.tester_list_Name_FK FROM testcase_root LEFT JOIN testplans ON testcase_root.ID = testplans.testcase_root_ID_FK WHERE (((testplans.plantriggers_ID_FK)=76530)) OR (((testplans.plantriggers_ID_FK) Is Null)) ORDER BY testcase_root.TestID; Robert M. Bartis Lucent Technologies, Inc Tel: +1 732 949 4565 Mail: [EMAIL PROTECTED] Pgr: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: General DB Design Question - How to avoid redundancy in table relationships
Scott Klarenbach wrote: These are the tables in question: RFQ (Request for Quote) Part Inventory Inventory items ALWAYS have a partID. RFQ items ALWAYS have a partID. However, sometimes, RFQ items have an inventoryID as well. Now, we have a redundancy problem. Because, in those instances when the RFQ has an inventoryID, the partID should be derived from the inventoryID. If there is no inventoryID, then the partID needs to be stored directly in the RFQ table. We don't want to have both the inventoryID and the partID in the RFQ table, because it opens up data integrity issues. ie, what if the RFQ item shows inventoryID 2, and partID 1...but inventoryID 2 is associated to partID 2. Now which partID is correct? They can't both be right. Since there have not been a lot of responses I decided to jump in. It sounds to me like we have real-world object behavior mixed up with the data model. Example RFQ items ALWAYS have a partID If there is no inventoryID, then the partID needs to be stored directly in the RFQ table. Why? Who or what is enforcing this? Can we look at overall object behavior, then come up with a model that supports the behavior with no preconceptions of table structure. I assume that an RFQ item is a document (paper or eletronic). What does one look like? From your description it will always have a partID and may have an inventoryID. Who populates these fields? Why is there a redundancy in the first place? Who checks to see that the direct partID matches the derived partID? How about leaving partID and inventoryID out of the RFQ table, and adding an association table that relates a RFQ to either a partID or an inventoryID. An attribute of this table would distinguish partID from an inventoryID. Business logic would ensure that only one entry gets into this table per RFQ, and could also validate that the direct partID matches the derived partID I'm sure this type of problem is run up against all the time, and I'm wondering what the best practice methodology is from experienced DBA's. This was a simple example; however, we are running into the problem system wide. For example, a quote table has an OPTIONAL RFQ ID, and a mandatory contactID. The RFQ table has a mandatory contactID. If the quote table has an RFQID, we want to derive the contactID from the RFQID. If the quote has NO RFQID, then we need to store the contactID directly in the quote table. In those instances where there IS an RFQID in the quote table, we end up storing the contactID twice. Once in the quote table, and once in the association between the RFQ/Contact table. Same problem as above: integrity and poor overall design. Same issue here. Remove the IDs from the quote and RFQ table and create another association table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to select data if not in both tables?
Michael Stassen wrote: Bob Gailer wrote: Peter Brawley wrote: Grant, If I want to select all the products that are in the product_table, but not in the sale_table, how to make the query? The product_table has all the products, but the sale table is a subset of the product_table. SELECT * FROM product_table p LEFT JOIN sale_table s USING (prod_id) WHERE s.prod_id IS NULL; I have not tested that but I don't think it will work. Try: Why not? This is the classic LEFT JOIN solution. It will work in all versions of mysql. Oops. I'm red-faced. Good humbling for my first appearance on this list. It can only get better? This is a good lesson in SQL for me. I did not know that a where clause could apply to rows in the result. I haver searched in vain to find a clear definition of WHERE that explains this behavior. Any pointers? SELECT item_name FROM product_table WHERE prod_id not in (select prod_id from sale_table); This will work only in mysql 4.1+, and will almost certainly be slower, because mysql's optimizer tends to treat the subquery as dependent, meaning it will be rerun for each and every row of the product_table. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to select data if not in both tables?
Peter Brawley wrote: Grant, If I want to select all the products that are in the product_table, but not in the sale_table, how to make the query? The product_table has all the products, but the sale table is a subset of the product_table. SELECT * FROM product_table p LEFT JOIN sale_table s USING (prod_id) WHERE s.prod_id IS NULL; I have not tested that but I don't think it will work. Try: SELECT item_name FROM product_table WHERE prod_id not in (select prod_id from sale_table); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Returning values from an INSERT
Hi everyone, im new to SQL and have a question that someone can hopefully answer
If i am inserting a new record into a table that has an auto_increment field
in it, is it possible to get the value of that field returned automatically
instead of having to do a SELECT..
For example my user table has:
userid -- auto_increment and primary key
username
So if i:
Insert into user (username) VALUES ('Test User');
i dont know what userid was assigned to that particular user and are having
to:
SELECT userid FROM user where username = 'Test User';
to get the id.
Is that the only way to do it?
hope it isnt a stupid question and thanks for any help in advance!
Cheers
Bob
-
Yahoo! Photos NEW, now offering a quality print service from just 8p a photo.
Selecting more than one property (req help)
Hi all, Can anyone throw some suggestions at me for this problem? | id | model | service_id | ||---|| | 1 | 500 | 1 | | 2 | 500 | 3 | | 3 | 500 | 10 | | 4 | 600 | 1 | From this table i want to extract all distinct models which have service_id=1 *AND* service_id=3 What's the best possible (speed) solution for this - I have a lot of records to juggle about! I have tried inner joining it within it's self but this method could get rather complicated. I have also tried making a count of service_id's captured and HAVING count == 2 but this query seems slow. Any suggestions or pointers would be great! Thanks Membob
RE: What is a schema?
A schema is a the database design. Sometimes textual, sometimes visual definition of the database structure (tables, field types, defaults etc). The database is the physical implementation of the schema that holds the data. Bob -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 03, 2005 10:21 AM To: mysql@lists.mysql.com Subject: What is a schema? What is a schema? How is different from a database? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newb learner question
I am learning MySQL from an older book, and some of the examples it give do not work in MySQL, so I am going to ask for help on those. select cust_contact from Customers where cust_contact like '[JM]%'; returns Empty set (0.00 sec) What is the right way to do this? Likewise: mysql select prod_name from Products where not vend_id = 'DLL01' order by prod_name; Empty set (0.00 sec) Bob Rea Dragon Networks 770-458-1350 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Phone Number Storage
That may be true, but I don't think the augments provided by Joerg necessitate a single column or multiple columns. His points, leading zeros, sorting, etc go more to the native data type that should be used and are valid in either case. Bob -Original Message- From: Sujay Koduri [mailto:[EMAIL PROTECTED] Sent: Monday, July 25, 2005 4:23 PM To: Joerg Bruehe; mysql@lists.mysql.com Cc: Asad Habib Subject: RE: Phone Number Storage I guess anywhere we have 3 levels of hierarchies for a phone number. (Country code, Area code and the actual number). The advantage of seperating them into different columns(Either an integer or a string) is that he can group different phone numbers based on area code or country code. sujay -Original Message- From: Joerg Bruehe [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 26, 2005 1:34 AM To: mysql@lists.mysql.com Cc: Sujay Koduri; Asad Habib Subject: Re: Phone Number Storage Hi! Sujay Koduri wrote (re-ordered): -Original Message- From: Asad Habib [mailto:[EMAIL PROTECTED] Sent: Monday, July 25, 2005 11:53 PM To: mysql@lists.mysql.com Subject: Phone Number Storage Is it better to store phone numbers as strings or as integers? Offcourse, storing them as integers saves space but this requires extra processing of the user's input (i.e. CPU time). Are there any other advantages/disadvantages of doing it one way or the other? - Asad I think it is better to store the phone numbers as strings only. As phone numbers may also include '-', if you allow entering international numbers, it is good to store them as strings only. Or you can ask the area code and the actual number seperately and store them seperately in two columns as integers. IMO, this is quite an USA-centric view in the answer: In general, phone numbers will also contain a country code. Outside the USA, it is quite common that codes (area or country) may begin with a leading 0 which any numeric type would drop as not significant, so you _must_ use strings for these. Also: A telephone number is no numeric value, arithmetic operations do not make sense on it. Think of extensions: phone numbers 1234-0 and 1234-56 are related, so you would order them (if at all) as strings and not as numeric values. The same applies to postal codes, social security numbers, part numbers etc. While you may use a numeric type for some ID value you want to generate yourself (using autoincrement), IMO this is on the borderline of correct modeling. For phone numbers, you should use strings. HTH, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Seriously.. When are we going to get subqueries?!
Folks...please This has really degraded. I seriously think its time everyone sign up for charm school or better yet share these emails with your grandmother who will more than likely crack you all upside your head and knock some minimal level of common decadency into you all. Its an email alias. You're asking for help from people you don't even know. You should therefore present your needs clearly and concisely. You should expect there to be questions. You should expect to not always get timely information. you should expect to get wrong answers from time-to-time. Its the nature of the beast. You should also get a feel if you follow the list that you will also, more often than not get the help you need or at a minimum pointers to help you along. I have and continue to be impressed with the level of help I have received. Its often on par with paid services. If I get called a Bone-head, than I have the choice to clarify my question or move on, but coming back again and again serves no-one. Lets stick to the technical issues and hopefully all become better because of it. For those of you that can't.there's always grandma:-)) Bob Bartis -Original Message- From: George L. Sexton [mailto:[EMAIL PROTECTED] Sent: Thursday, June 09, 2005 2:26 PM To: mysql@lists.mysql.com Subject: Re: Seriously.. When are we going to get subqueries?! You obviously don't understand the limitations of timestamps. Another limitation in MySQL is that you can only have one timestamp column with a default of CURRENT_TIMESTAMP. It's not an issue I don't know. It's an issue I'd like to see fixed so that I can list MySQL as a supported database along side PostgreSQL Oracle SQL Server Sybase SQL Anywhere Microsoft Access IBM DB2 That's what I'm after. On Thursday 09 June 2005 10:53, Jeff Smelser wrote: On Thursday 09 June 2005 11:47 am, George Sexton wrote: I'm working in that direction. I first posted to the regular mysql list, and then I posted again to the internals list. I guess the next step is to talk to the MySQL people. We answered you I thought.. Whats the issue you dont know? Jeff -- 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: max_seeks_for_key in InnoDB
Thanks for your reply. I guess the root of my problem is that MySQL is making a poor choice of index, which I presume is based on the cardinality numbers of each index. When I run ANALYZE TABLE, these values can fluctuate wildly -- between 16 and 26,000, for example. According to the manual, ANALYZE TABLE counts cardinality by doing 10 random dives into each of the index trees. (this is up from 8 random dives in 4.1.11, which is good!) Maybe the accuracy of this measurement decreases as tables reach millions of rows? I would really like to avoid rewriting all of my queries to add USE INDEX and STRAIGHT JOIN, since some of them are quite complicated and I would prefer to leave the job to MySQL. My questions are: 1) Can the 10 random dives be made configurable? I would like to do an analyze table with 100 random dives if it would produce a more accurate count. 2) Is there some reason that my index trees would not be uniform? Is there anything I can do about this? 3) For InnoDB tables, does it make sense to always keep max_seeks_for_key at a low value (1, 100, ?) Many thanks in advance, -Bob - Original Message - From: [EMAIL PROTECTED] To: Bob O'Neill [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Friday, June 03, 2005 3:20 PM Subject: Re: max_seeks_for_key in InnoDB Hi, you can use a hint to force specific index usage : http://lib.hutech.edu.vn/ebookonline/ebook1/0596003064/hpmysql-CHP-5-SECT-4.html But this is not a good idea since data change and index selectivity can become bad. Also, if the index scan + the table scan is bigger than a full table scan, even you will prefer FTS. So, according to selectivity, usage of an index can be a very bad idea. Thsi depends on how many rows your query retreives among the count(*) of the table. Mathias Selon Bob O'Neill [EMAIL PROTECTED]: I am having problems with MySQL inconsistently choosing the wrong index, or no index at all, for queries on tables with 20 million rows. Would it be a good idea for me to set max_seeks_for_key to 1 (or something less than 4 billion), in order to force MySQL to use an index? We are using InnoDB. Since InnoDB has clustered indexes, is there ever a good reason for MySQL to prefer a table scan? Thanks, -Bob -- 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]
max_seeks_for_key in InnoDB
I am having problems with MySQL inconsistently choosing the wrong index, or no index at all, for queries on tables with 20 million rows. Would it be a good idea for me to set max_seeks_for_key to 1 (or something less than 4 billion), in order to force MySQL to use an index? We are using InnoDB. Since InnoDB has clustered indexes, is there ever a good reason for MySQL to prefer a table scan? Thanks, -Bob
RE: View
I believe 5.0 does. -Original Message- From: Andreas Ahlenstorf [mailto:[EMAIL PROTECTED] Sent: Thursday, June 02, 2005 2:55 PM To: Jerry Swanson Cc: mysql@lists.mysql.com Subject: Re: View Am 02.06.2005 um 20:31 schrieb Jerry Swanson: Does Mysql 4 supports views? No. Regards, A. -- 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]
Subquery question
I have a problem where I need to use a subquery in combination with a Left Join. The SQL statement below works fine until I introduce the subquery portion. Specifically, WHERE testplans.plantriggers_ID_FK IN (SELECT plantriggers.ID FROM plantriggers WHERE (((plantriggers.testplan_intro_PlanID_FK)=1))) Can someone point out where the error is. I double checked the column name/tables names in the above portion and as I said the main body worked prior to introduction of the subquery. I've also tried replacing the IN operand with an = with no luck. Full SQL statement: SELECT testplans.SubTestCaseKey, testcase_root.ID, testplans.testcasesuffix_name_FK, testcase_root.TestID, testcase_root.TestDesc, testplans.FSAGA, testplans.Priority, testplans.tester_list_Name_FK FROM testcase_root LEFT JOIN testplans ON testcase_root.ID = testplans.testcase_root_ID_FK WHERE testplans.plantriggers_ID_FK IN (SELECT plantriggers.ID FROM plantriggers WHERE (((plantriggers.testplan_intro_PlanID_FK)=1))) ORDER BY testcase_root.TestID; Robert M. Bartis Lucent Technologies, Inc Tel: +1 732 949 4565 Mail: [EMAIL PROTECTED] Pgr: [EMAIL PROTECTED]
RE: Subquery question
Apologize for not including the error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT plantriggers.ID FROM plantriggers WHERE (((plantrigger I'm running MySQL 4.0.2-standard -Original Message- From: Bartis, Robert M (Bob) [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 25, 2005 6:29 PM To: 'mysql' Subject: Subquery question I have a problem where I need to use a subquery in combination with a Left Join. The SQL statement below works fine until I introduce the subquery portion. Specifically, WHERE testplans.plantriggers_ID_FK IN (SELECT plantriggers.ID FROM plantriggers WHERE (((plantriggers.testplan_intro_PlanID_FK)=1))) Can someone point out where the error is. I double checked the column name/tables names in the above portion and as I said the main body worked prior to introduction of the subquery. I've also tried replacing the IN operand with an = with no luck. Full SQL statement: SELECT testplans.SubTestCaseKey, testcase_root.ID, testplans.testcasesuffix_name_FK, testcase_root.TestID, testcase_root.TestDesc, testplans.FSAGA, testplans.Priority, testplans.tester_list_Name_FK FROM testcase_root LEFT JOIN testplans ON testcase_root.ID = testplans.testcase_root_ID_FK WHERE testplans.plantriggers_ID_FK IN (SELECT plantriggers.ID FROM plantriggers WHERE (((plantriggers.testplan_intro_PlanID_FK)=1))) ORDER BY testcase_root.TestID; Robert M. Bartis Lucent Technologies, Inc Tel: +1 732 949 4565 Mail: [EMAIL PROTECTED] Pgr: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Subquery question
That might explain it:-) The really said part is I remember running into the same issue some months back and completely forgot. Thanks -Original Message- From: Dan Nelson [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 25, 2005 6:54 PM To: Bartis, Robert M (Bob) Cc: 'mysql' Subject: Re: Subquery question In the last episode (May 25), Bartis, Robert M (Bob) said: Apologize for not including the error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT plantriggers.ID FROM plantriggers WHERE (((plantrigger I'm running MySQL 4.0.2-standard 4.0 doesn't support subqueries. You'll need to upgrade to 4.1. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DB design question
Something like this would make more sense to me and provide greater flexibility; student student_id name age address --- address_id street_name city state zip phone_num -- phone_num_id num extension type (cell, home, etc) primaryNumber (yes/no) student_info --- student_id_FK phone_num_id_FK address_id_FK Spent all of 10 mins on this so its not perfect. Bottom line is I would not include the student_id in the address and phone tables. It precludes a student having multiple phones or addresses with out duplicate data. The addition of the student_info table provide the 1:1 or 1:N mapping you're looking for I believe. The only thing you need to ensure is properly set the Cascade on update and restrict on delete options to ensure data integrity. My gut tells me it may be a better implementation to map the student/phone and student/address separately and then create the student_info using keys from these intermediate tables, but it more complicated and it not clear what the constraints on your problem is. Bob Bartis -Original Message- From: Koon Yue Lam [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 24, 2005 1:34 PM To: mysql@lists.mysql.com Subject: DB design question Hi, here is the case: one student may have more than one address, and one student may have more than one phone number so the db would be: student student_id name age address --- address_id student_id street_name phone_num -- student_id num extension the key of 3 tables are student_id the problems is, when I want to query both student, address and phone num, the sql will be select * from student s, address a, phone_num n where s.student_id = a.sudent_id and s.student_id = n.student_id it won't provide a nice result as data of student are repeated in every row, address and phone_num's data are repeated in certain rows The output is not suitable for reporting and may I ask what is the better way of design to handle the above case ? any help would be apreciated Regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: a question about MySQL installation
Suggest you review the MySQL documentation http://dev.mysql.com/doc/mysql/en/installing.html and in particular 2.3.13. Testing The MySQL Installation and 2.3.14. Troubleshooting a MySQL Installation Under Windows Bob -Original Message- From: Terry Leung [mailto:[EMAIL PROTECTED] Sent: Sunday, May 22, 2005 7:12 AM To: mysql@lists.mysql.com Subject: a question about MySQL installation Dear, I have installed MySQL4.1. But why it can not startup when I open my computer? Also, how can I test MySQL can run or not? Thanks for your advice. Best Regards, Terry -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Single vs Multiple primary keys
I have a table, see below, that contains a single primary key (SubTestCaseKey )
and a number of foreign keys
* plantriggers_ID_FK ,
* testcase_root_ID_FK
* testcasesuffix_name_FK
What I want to ensure is that there are no duplicate records when considering
the three foreign keys above. Would it be appropriate to remove the single
primary and replace with three multiple primary keys? Is there a performance
impact when doing this. this seems overly complex and wonder if I should be
breaking the table up to simplify? Any suggestions would be appreciated.
Bob
CREATE TABLE testplans (
SubTestCaseKey INTEGER NOT NULL AUTO_INCREMENT,
plantriggers_ID_FK INTEGER UNSIGNED NOT NULL,
testcase_root_ID_FK INTEGER NOT NULL,
testcasesuffix_name_FK VARCHAR(20) NULL,
FSAGA ENUM('FSA1','FSA2','GA') NULL DEFAULT 'GA',
Priority ENUM('P1','P2','P3','Do Not Run') NULL DEFAULT 'Do Not Run',
DateMod TIMESTAMP NULL,
tester_list_Name_FK VARCHAR(50) NULL,
PRIMARY KEY(SubTestCaseKey),
INDEX testplans_FKIndex1(tester_list_Name_FK),
INDEX testplans_FKIndex2(testcasesuffix_name_FK),
INDEX testplans_FKIndex3(testcase_root_ID_FK),
INDEX testplans_FKIndex4(plantriggers_ID_FK),
FOREIGN KEY(tester_list_Name_FK)
REFERENCES tester_list(Name)
ON DELETE RESTRICT
ON UPDATE CASCADE,
FOREIGN KEY(testcasesuffix_name_FK)
REFERENCES testcasesuffix(name)
ON DELETE RESTRICT
ON UPDATE CASCADE,
FOREIGN KEY(testcase_root_ID_FK)
REFERENCES testcase_root(ID)
ON DELETE RESTRICT
ON UPDATE CASCADE,
FOREIGN KEY(plantriggers_ID_FK)
REFERENCES plantriggers(ID)
ON DELETE RESTRICT
ON UPDATE CASCADE
)
TYPE=InnoDB;
Robert M. Bartis
Lucent Technologies, Inc
Tel: +1 732 949 4565
Mail: [EMAIL PROTECTED]
Pgr: [EMAIL PROTECTED]
RE: Write to a mysql table from Excel
One problem I've encountered in the past creating CSV files from Excel is with fields that exceed 256 or 258 characters. The fields end up truncated in the CSV file. A script to directly access the data in Excel and move it to MySQL is appropriate, but can be a lot of work if you only need to do the import one or twice. Although this is somewhat convoluted it works for me. Start MS Access and create linked tables to you MySQL DB. Then import the data from Excel into Access. They're integrated fairly well so the import is basically a couple of button clicks. Then you're done. Bob -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Friday, April 22, 2005 3:12 PM To: Huang, Ou; mysql@lists.mysql.com Subject: RE: Write to a mysql table from Excel [snip] Thank you for your reply. Can you provide more details on how to write to a MySQL table from Excel? I am a newbie to MySQL. Thanks. [/snip] You must be new to mailing lists too... http://catb.org/~esr/faqs/smart-questions.html You can save the excel as a CSV file and import that into MySQL, you can write a program that will do it for you using any one of several programming or scripting languages, you can use an ODBC connection. -- 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: Changed Number
Sounds like the type for the field you're storing the number is not capable of holding a number sized as 16996941. What's the field type you're storing the number in? Bob -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 05, 2005 9:31 PM To: mysql@lists.mysql.com Subject: Changed Number I have loaded a large *.csv spreadsheet into mysql and one number, the grand total, changes from 16996941 on the Excel spreadsheet to 8388607 in the mysql database. The numbers surrounding this number are correct at all stages. I have reloaded, checked formatting and done various other things without success. Then I went to the text (*.sql) file in which I had dumped the mysql table, manually changed the number to 16996941 and put the text file on the server. However, the number that showed up on the web page table and the number in the database on the server is 8388607. Note that the smaller number is just under half of the larger number. This makes no sense. Any solutions? Ken -- 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: CSV-to-SQL?
Maybe a review of http://dev.mysql.com/doc/mysql/en/numeric-type-overview.html will shed some light? -Original Message- From: Renato Golin [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 05, 2005 9:48 PM To: Richard Miller; Mysql Subject: Re: CSV-to-SQL? You could use the CSV table type: http://dev.mysql.com/doc/mysql/en/csv-storage-engine.html Or use the LOAD DATA INFILE to import all data: http://dev.mysql.com/doc/mysql/en/LOAD_DATA.html --rengolin --- Richard Miller [EMAIL PROTECTED] wrote: I have a dozen, very large CSV files that I would like to put into a MySQL database, with 1 table per file. Does anyone know of a PHP (or other) script that can read the first few lines of a CSV file and create an appropriate CREATE TABLE statement based on the data it finds? (Even better, it could import the file afterwards!) I'm not picky about data types here; I'd simply like to get this data into tables so I can work with it more easily. Thanks, Richard Miller -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador agora! http://br.acesso.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Changed Number
Maybe a review of http://dev.mysql.com/doc/mysql/en/numeric-type-overview.html will help? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, April 05, 2005 9:31 PM To: mysql@lists.mysql.com Subject: Changed Number I have loaded a large *.csv spreadsheet into mysql and one number, the grand total, changes from 16996941 on the Excel spreadsheet to 8388607 in the mysql database. The numbers surrounding this number are correct at all stages. I have reloaded, checked formatting and done various other things without success. Then I went to the text (*.sql) file in which I had dumped the mysql table, manually changed the number to 16996941 and put the text file on the server. However, the number that showed up on the web page table and the number in the database on the server is 8388607. Note that the smaller number is just under half of the larger number. This makes no sense. Any solutions? Ken -- 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: [Q] Database design
Sounds like you need a 1:N relationship table to hold userInfo separate from either the user or group table. Adding a infoIdentifier would allow the number of rows added for a specific user to be sized based on the specific user needs. This is effect would be the key part of a key-value pair, normally associated with associative arrays. The userSpecificInformation would hold the value portion of the information. UserInfo table: id (pk) user_id (fk) infoIdentifier userSpecificInformation user table: id (pk) name any other user info only dependant on the user group table: id (pk) name usergroup table: user_id (pk) group_id (pk) any info specific to individual user/group combo UserInfo table: id (pk) user_id (fk) infoIdentifier userSpecificInformation -Original Message- From: Eric Gorr [mailto:[EMAIL PROTECTED] Sent: Saturday, April 02, 2005 2:59 PM To: Tom Crimmins Cc: mysql@lists.mysql.com Subject: Re: [Q] Database design Tom Crimmins wrote: user table: id (pk) name any other user info only dependant on the user group table: id (pk) name any other group info only dependant on the group usergroup table: user_id (pk) group_id (pk) any info specific to individual user/group combo Yes, if I understand what you are saying here correctly, I considered this. However, the problem is that the columns corresponding to any info specific to individual user/group combo is not guaranteed to be consistent across groups. Well, to be more precise, the type for each column will be the same, but the number of required columns (call this number N) will be different. It is for this reason that it seemed necessary to have a separate table per group. Now, if I could decide what the maximum number of required columns would be, then I could see using this design, but this is simply not possible. I am, of course, limited by the maximum number of columns (call this number X) allowed within a mySQL database. The required number of columns for a particular group could be anywhere between 1 and X. However, it just seemed like a bad idea to use that large of a table when the vast majority of it would go unused and much of it would likely never be used at all. But, perhaps I am wrong and it would simply not be an issue. I suppose it would be possible to dynamically size 'usergroup table' based on the current max N across all groups. Basically, if N changes for a particular group, look at the value of N for all groups, take the max and size 'usergroup table' accordingly. Is this what you would do? -- 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: needs some info about MySql
I am certainly no expert, not even a DB admin:-\ My non scientific algorithm when selection of a DB was a question for a small team was as follows: 1) Go to local book store 2) Find section on DB, browse 3) Look for performance tuning of Oracle 4) Find chair quickly when you realize there are libraries on tuning alone! 5) Look for performance tuning of MySQL 6) Select a book and pay 7) Leave store In all seriousness I was struck by the apparent complexity in tuning Oracle when looking through the available literature. In one case I found a series of 7-books. MySQL had a number of book available, all of which seemed reasonable straight forward and none of which came in a set. As I said, non scientific and I am not a DB Admin, but for a small group such as mine it was critical to find something with low overhead. Bob -Original Message- From: Joan Hsieh [mailto:[EMAIL PROTECTED] Sent: Thursday, March 31, 2005 3:38 PM To: mysql@lists.mysql.com Subject: needs some info about MySql Hi, I'd like to know with a very extensive oracle dba experience, how's the learnig curve for a new MySql server, I don't have any mysql experience or knowledge at all. We have one existing mysql server want us to take over for adminsitrative and maintances. How often mysql needs to be patching, upgrade? and how it could be for the performance tunig compare it to ORACLE tuning? thanks a lot, Joan -- 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]
lock tables and sql cache
If I try to read table 'b' after locking table 'a', I expect to get
the error message Table 'b' was not locked with LOCK TABLES.
However, if my query that accesses table b is stored in the query
cache, I don't get the error. This causes a problem in the following
scenario:
User 1:
LOCK TABLES a
SELECT SQL_CACHE COUNT(*) FROM b
(assume it was already cached)
User 2:
INSERT b VALUES('value');
SELECT SQL_CACHE COUNT(*) FROM b
(the SELECT puts the query back into the cache)
User 1:
SELECT SQL_CACHE COUNT(*) FROM b
(now he gets a different result)
UNLOCK TABLES
User 1 thinks that everything he's doing is safe inside of an
emulated transaction. But the data in table b has changed between
the LOCK and the UNLOCK, and User 1 isn't notified that he is doing
anything wrong.
I think an appropriate fix would be to force User 1 to lock table b
even though the results of that query are stored in the query cache.
Is this possible?
Thanks,
-Bob
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trouble performing an update
I am trying to update a link table (table with two primary keys) where I want to update all rows where the first primary key is a set value (for example, change key1 from 10 to 20), but I only want to update these where the resulting primary key does not already exist in the table (otherwise an error is thrown on a duplicate key and the remaining rows are not updated). Using other databases, I am able to perform a subquery in the filter for the update such as the following: UPDATE mytable SET key1 = 20 WHERE key2 NOT IN (SELECT key2 FROM mytable WHERE key1 = 20) Unfortunately, MySQL does not allow you to use a table in a subquery which is being updated. If anyone can offer any assistance with this, I would greatly appreciate it. Thanks, Bob Dankert Envision Information Technologies Associate [EMAIL PROTECTED] v. 608.256.5680 f. 608.256.3780
Slow queries only the first time
Hello. I am wondering why some of my queries are slow on the first run, but speedy on subsequent runs. They are not being query cached, as I have query_cache_type set to DEMAND. Is it something as simple as pulling the data into RAM from disk, or is there something else going on? Here's a simple example: mysql select count(*) from foo; +--+ | count(*) | +--+ | 1374817 | +--+ 1 row in set (3.60 sec) mysql select count(*) from foo; +--+ | count(*) | +--+ | 1374817 | +--+ 1 row in set (0.92 sec) mysql show variables like 'query_cache_type'; +--++ | Variable_name| Value | +--++ | query_cache_type | DEMAND | +--++ 1 row in set (0.00 sec) I am running MySQL 4.1.10 with InnoDB on RHEL 2.1 (kernel 2.4.9-e.49enterprise). Binaries are Linux x86 glibc static gcc RPMs from mysql.com. Thanks, -Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: remove trailing character
try updating the table like this... update TABLE set COLUMN = replace(COLUMN, ,); -Original Message- From: Scott Haneda [mailto:[EMAIL PROTECTED] Sent: Thursday, March 10, 2005 3:30 PM To: MySql Subject: remove trailing character I managed to mess up and email storage addresses are in the format of [EMAIL PROTECTED] How I can strip off the , it does not exist on all, only some. thanks -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED] Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do I ... SQL question
Return only four rows beginning at second row: SELECT count(*) AS count, name, year FROM a GROUP BY name, year ORDER BY count DESC, name ASC LIMIT 4 OFFSET 1; count name year --- -- -- 3 joe2004 s,e,e 2 bob2003 w,e 2 kim2003 s,s 2 sue2004 s,w Select only places visited included in LIMITed query: SELECT DISTINCT place FROM a ; Put the results of the LIMITed query into a temporary table and re-query. CREATE TEMPORARY TABLE tmpStep1 ( freq int , name varchar(25) , year int ); INSERT tmpStep1 (freq, name, year) SELECT count(*) AS count, name, year FROM a GROUP BY name, year ORDER BY count DESC, name ASC LIMIT 4 OFFSET 1; select distinct a.place from tmpStep1 ts1 INNER JOIN a on a.name = ts1.name; Then you should get the list: place --- south west east Thanks to all who responded. The inner join does what I wanted. -Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Import Excel data into table
I think you mean that you want to import .txt or .csv data into an mysql table... http://phpmyadmin.net can do that via a web form to upload plus has many other good admin features. -Original Message- From: Steve Grosz [mailto:[EMAIL PROTECTED] Sent: Thursday, January 13, 2005 1:56 PM To: mysql@lists.mysql.com Subject: Import Excel data into table Can anyone tell me a good way to import individual column data into a table? Is there a tool to assist with this? Thanks, Steve -- 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: Importing data from MS Access
Its my understanding you need InnoDB table types, but I do not know of the single foreign key per table. I have a DB with multiple foreign keys per table and its seems to work fine. Bob -Original Message- From: Paun [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 11, 2005 5:19 PM To: mysql@lists.mysql.com Subject: Importing data from MS Access I use MySQL-Front 2.5 (freeware) for importing data from MSAccess databaase in MySQL 4.1.7. Of course, there is no possibility to import foreign keys from MSAccess. Question: If I read properly documentation in MYSql is possible to use only one foreing key per table, and fields with foreign key must be on ordered on the same way in booth tables, and only in InnoDB type of tables. I have tables with many foreign key connectons (e.g. members / streets, members/occupations, members/state of membership) between tables, and it was easier way to keep data in database in proper order with less programing. Is that is not possible in MYSQL? Of course, I am very new in MYSQL. Thanks. -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.296 / Virus Database: 265.6.9 - Release Date: 1/6/2005 -- 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: AW: [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 :)
Um, as I understand it this is simple to do in php. Just use:
header(Location: $somestring);
Here's the ASP code I found when I looked up what response.redirect does:
%
u_location=request.form(u_location)
if u_location then
response.redirect (u_location)
end if
%
form method=post action=redirect_varible.asp
select size=1 name=u_location
option value=http://www.goto.com;GoTo.com/option
option value=http://www.priceline.com;Priceline.com/option
option value=http://www.alladvantage.com;AllAdvantage.com/option
/select
input type=submit value=Submit
/form
Here's how I'd code the same thing is php:
?php
if isdefined($_POST[u_location]
{
$newpage = $_POST[u_location];
header(Location: $newpage);
}
form method=post action=redirect_varible.php
select size=1 name=u_location
option value=http://www.goto.com;GoTo.com/option
option value=http://www.priceline.com;Priceline.com/option
option value=http://www.alladvantage.com;AllAdvantage.com/option
/select
input type=submit value=Submit
/form
As I understand it, all response.redirect does is tell the browser to go
to another page. That's all the header function does too. I use this
all the time if people aren't authenticate to push them to the login
page or if they aren't using https to connect, to push them to the https
url.
Am I missing something here?
The only snage with the header function is that you must not print or
echo anything to the browser before you use it. In other words you
can't do this:
html
body
pI moved you to another page./p
?php header(Location: http://someotherpage.com;); ?
/body
/html
Whoops. Just realized that this was coming through the mysql lists, so
it's OT. Original poster can e-mail me off list with questions.
Bob
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: synchronizing mysql database in two different places
As suggested by Karam, please refer to http://dev.mysql.com/doc/mysql/en/Replication_Implementation.html for additional information. -Original Message- From: Karam Chand [mailto:[EMAIL PROTECTED] Sent: Sunday, October 31, 2004 1:29 PM To: Eko Budiharto; [EMAIL PROTECTED] Subject: Re: synchronizing mysql database in two diferrent places Hello, You might try MySQL's inbuilt replication feature. If you want a more simple approach, might take a look at SQLyog's Data Sync Wizard. More information can be found at http://www.webyog.com I think they have a article on the same subject in SitePoint. You might google it. Karam --- Eko Budiharto [EMAIL PROTECTED] wrote: Hi, I a facing a situation where I have two database server in two different places that has exactly the same database. The thing is if the database is modified in either one of the places, both database has to be updated lively. Can someone tell me who done this already? Please help. I am looking forward to a favorable reply from you. Thank you. __ Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish. http://promotions.yahoo.com/new_mail -- 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: detailed summary of data, average, min, max
Thanks, that was it exactly. bob At 02:25 PM 10/28/2004, Michael Stassen wrote: Something like SELECT uid, AVG(number_grade) AS average_grade, SUM(IF(letter_grade = 'A', 1, 0)) AS A_count, SUM(IF(letter_grade = 'B+', 1, 0)) AS B+_count, SUM(IF(letter_grade = 'B', 1, 0)) AS B_count, SUM(IF(letter_grade = 'B-', 1, 0)) AS B-_count, SUM(IF(letter_grade = 'C', 1, 0)) AS C_count, FROM grades_table GROUP BY uid; should do. Michael Bob Ramsey wrote: Hi, I have a table of grades like this: title, section, instructor, letter_grade, number_grade, uid With data that would look like this: English, 1, Smith, B, 88, 1 English, 1, Smith, B, 86, 1 English, 1, Smith, B+, 89, 1 Math, 1, Jones, A, 95, 2 Math, 1, Jones, B, 85, 2 Math, 2, Smith, C, 75, 3 Math, 2, Smith, B-, 82, 3 I want a query that will give me something like this: uid, average_grade, A_count, B+_count, B_count, B-_count, C_count 1, 87.67, 0,1, 2,0, 0 2, 90, 1,0, 1,0, 0 3, 78.50,0, 0,1, 1 I can do this is a separate query for each grade, but that makes a lot of little queries. Is there a way to do this in one query? Or am I just going to have to break the average out and do the counts in one query and the average in another? Thanks, bob == Bob Ramsey SYSTEMS ADMINISTRATION AND SYSTEMS PROGRAMMING III MA, Management of Information Systems 2004 MA, English Literature, 1992 ph: 1(319)335-9956 187 Boyd Law Building fax: 1(319)335-9019 University of Iowa College of Law mailto:[EMAIL PROTECTED]Iowa City, IA 52242-1113 For Hardware and Software questions, call 5-9124 == == Bob Ramsey SYSTEMS ADMINISTRATION AND SYSTEMS PROGRAMMING III MA, Management of Information Systems 2004 MA, English Literature, 1992 ph: 1(319)335-9956 187 Boyd Law Building fax: 1(319)335-9019 University of Iowa College of Law mailto:[EMAIL PROTECTED]Iowa City, IA 52242-1113 For Hardware and Software questions, call 5-9124 == -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
detailed summary of data, average, min, max
Hi, I have a table of grades like this: title, section, instructor, letter_grade, number_grade, uid With data that would look like this: English, 1, Smith, B, 88, 1 English, 1, Smith, B, 86, 1 English, 1, Smith, B+, 89, 1 Math, 1, Jones, A, 95, 2 Math, 1, Jones, B, 85, 2 Math, 2, Smith, C, 75, 3 Math, 2, Smith, B-, 82, 3 I want a query that will give me something like this: uid, average_grade, A_count, B+_count, B_count, B-_count, C_count 1, 87.67, 0,1, 2,0, 0 2, 90, 1,0, 1,0, 0 3, 78.50,0, 0,1, 1 I can do this is a separate query for each grade, but that makes a lot of little queries. Is there a way to do this in one query? Or am I just going to have to break the average out and do the counts in one query and the average in another? Thanks, bob == Bob Ramsey SYSTEMS ADMINISTRATION AND SYSTEMS PROGRAMMING III MA, Management of Information Systems 2004 MA, English Literature, 1992 ph: 1(319)335-9956 187 Boyd Law Building fax: 1(319)335-9019 University of Iowa College of Law mailto:[EMAIL PROTECTED]Iowa City, IA 52242-1113 For Hardware and Software questions, call 5-9124 == -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql 4.1.5 source
Aman Raheja wrote: Latest realease is 4.1.7 in 4.1.x but I am looking for 4.1.5 source download. Pointers will be helpful. Thanks Aman http://downloads.mysql.com/archives.php?p=mysql-4.1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Guest login permissions
I have been working to configure MS Access 2000 as a front-end to an application running MySQL 4.0.20-standard. Our thinking was to have a guest login with minimal privileges (Select only) setup as the default on each users PC. This would allow anyone on the team to access the DB using the front-end (Read-only). We also present a login screen for those power users. Allowing them to login and make changes to the data. The login screen is really to confirm that the user name/password provided is recognized by MySQL (Open/Close connection). From that point forward any operation that results in an update would be done by opening a connection using the userid/password provided and execute the command based on this users privileges. I have shown that if I set the ODBC connection using my ID/password and then login as myself using the front-end everything works as expected. I have also verified that if I login is as guest everything works as expected. The problem comes when I set the ODBC connection up as guest. I use the Test Data Source button to confirm I've provided a valid Guest/password. I then login into the front end as myself or guest and click on a button that results in a small query being run. Instead or the expected results I receive an error that [EMAIL PROTECTED] access denied with password YES. This is followed by the ODBC connector screen being presented at which point if I click the Test Data Source button the test fails, unlike the first time. The permissions for guest are set up as %.lucent.com and with Select privileges. Any idea on what I am doing wrong and or if the approach taken is problematic? Should I be following a different method? I've attached the trace log of from the ODBC connector in which I first tested the data source using guest as a login, then connected via the MS Access front end logged in as guest, which succeeded and then attempted to run a small query. Saw the access denied message and again attempted to test the data source using the ODBC connector screen which failed this time. Not sure where the Admin login request in the trace is coming from. I checked my code and nowhere do I attempt a login in as Admin??? SQL.LOG Robert M. Bartis Lucent Technologies, Inc Tel: +1 732 949 4565 Mail: [EMAIL PROTECTED] Pgr: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Finding records not in a set
Thanks for the tip. I'm still facing an issue where I think I have the right syntax and I'm receiving an ODBC failure. Do you have any suggestions on how to go about understanding why the failure and hence how to correct it? Bob -Original Message- From: Martin Gainty [mailto:[EMAIL PROTECTED] Sent: Saturday, October 09, 2004 9:30 AM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Finding records not in a set Keep in mind EXISTS/NOT EXISTS are for SQL (Parent) Heavy queries Otherwise you should use IN/NOT IN Martin Gainty 617-852-7822 Man1: In my next life I want to get paid for solving problems Man2: You sound like a misguided capitalist! Man1: But how do I pay my bills?? Man2: You can always beg on the street..problem solved From: Bartis, Robert M (Bob) [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: RE: Finding records not in a set Date: Sat, 9 Oct 2004 08:53:19 -0400 MIME-Version: 1.0 Received: from lists.mysql.com ([213.136.52.31]) by mc7-f3.hotmail.com with Microsoft SMTPSVC(5.0.2195.6824); Sat, 9 Oct 2004 05:54:52 -0700 Received: (qmail 20728 invoked by uid 109); 9 Oct 2004 12:53:26 - Received: (qmail 20709 invoked from network); 9 Oct 2004 12:53:26 - Received: pass (lists.mysql.com: domain of [EMAIL PROTECTED] designates 192.11.226.161 as permitted sender) X-Message-Info: JGTYoYF78jEAJ70xKNiMjuhlQYGFj9no Mailing-List: contact [EMAIL PROTECTED]; run by ezmlm List-ID: mysql.mysql.com Precedence: bulk List-Help: mailto:[EMAIL PROTECTED] List-Unsubscribe: mailto:[EMAIL PROTECTED] List-Post: mailto:[EMAIL PROTECTED] List-Archive: http://lists.mysql.com/mysql/173887 Delivered-To: mailing list [EMAIL PROTECTED] Message-ID: [EMAIL PROTECTED] X-Mailer: Internet Mail Service (5.5.2657.72) X-Virus-Checked: Checked Return-Path: [EMAIL PROTECTED] X-OriginalArrivalTime: 09 Oct 2004 12:54:52.0865 (UTC) FILETIME=[2B57B310:01C4ADFF] I did find a reference to EXIST/NOT EXISTS clause in the SQL manual, but I get an ODBC Failed call when I run the following simplified query SELECT * FROM main_db WHERE NOT EXISTS (SELECT * FROM featureenable WHERE featureenable.FeatureKey = main_db.FeatureKey); Bob -Original Message- From: Bartis, Robert M (Bob) [mailto:[EMAIL PROTECTED] Sent: Saturday, October 09, 2004 8:48 AM To: [EMAIL PROTECTED] Subject: Finding records not in a set I have a query, call qry_AssociatedFeatures, that finds all features associated with a given plan SELECT functionalsubgroup.Functional, functionalsubgroup.SubGroup, main_db.Feature, main_db.FeatureKey, functionalsubgroup.FSKey, featureenable.PlanName FROM featureenable INNER JOIN (main_db INNER JOIN functionalsubgroup ON main_db.FSKey = functionalsubgroup.FSKey) ON featureenable.FeatureKey = main_db.FeatureKey WHERE (((featureenable.PlanName)=[forms]![switchboard].[planname])); So far so good. Now I want to find the inverse, qry_UnassociatedFeatures, or all the features not associated with a plan. Complication here is the feature enable table can have the same feature key associated with multiple plans. I assumed if I took the table containing the unique set of features and query for those records whose feature key is not present in the qry_AssociatedFeatures query I would get what I wanted SELECT DISTINCT main_db.Feature, main_db.FeatureKey FROM main_db, qry_AssociatedFeatures WHERE (((main_db.FeatureKey)[qry_AssociatedFeatures].[FeatureKey])); Unfortunately, I still see records in the qry_UnassociatedFeatures that are also present in the qry_AssociatedFeatures. Any suggestions? -- 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] _ Check out Election 2004 for up-to-date election news, plus voter tools and more! http://special.msn.com/msn/election2004.armx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ODBC Call failed - Query appears to be corrupt?
I have a query that I have put together. The query is an attempt to retrieve records from one table, main_db, whose keys are not present in another, featureenable. I am using the NOT EXISTS keywords and continue to receive an ODBC---Call Fail error. I traced the ODBC calls and see something very odd. It appears from the trace that the query being passed from an MS Access front-end to the ODBC connector is being corrupted I've extracted the lines of interest shown below. Please note the MS2 referenced in the 2nd line. These are not in the original query I passed in. Why is the query being passed to the ODBC connector changed? The full queries are also shown. .EXISTS (SELECT* FROM featureenable WHERE main_db.FeatureKey = featureenable.FeatureKey); .EXISTS (SELECT `MS2`.`ID` FROM `featureenable` `MS2` WHERE (`MS2`.`FeatureKey Original Query == SELECT DISTINCT [functionalsubgroup].[Functional], [functionalsubgroup].[SubGroup], [main_db].[Feature], [main_db].[FeatureKey], [functionalsubgroup].[FSKey] FROM featureenable INNER JOIN (functionalsubgroup INNER JOIN main_db ON ([functionalsubgroup].[FSKey]=[main_db].[FSKey]) AND ([functionalsubgroup].[FSKey]=[main_db].[FSKey]) AND ([functionalsubgroup].[FSKey]=[main_db].[FSKey]) AND ([functionalsubgroup].[FSKey]=[main_db].[FSKey])) ON [featureenable].[FeatureKey]=[main_db].[FeatureKey] WHERE NOT EXISTS (SELECT* FROM featureenable WHERE main_db.FeatureKey = featureenable.FeatureKey); ODBC Call Trace STRDB-v2.0b14 83c-218 ENTER SQLExecDirectW HSTMT 08DC1C30 WCHAR * 0x0F271F40 [ -3] SELECT DISTINCT `functionalsubgroup`.`Functional` ,`functionalsubgroup`.`SubGroup` ,`main_db`.`Feature` ,`main_db`.`FeatureKey` ,`functionalsubgroup`.`FSKey` FROM `main_db`,`functionalsubgroup`,`featureenable` `MS1` WHERE ((`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) AND (`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) ) AND (`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) ) AND (`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) ) AND (`MS1`.`FeatureKey` = `main_db`.`FeatureKey` ) ) AND NOT(EXISTS (SELECT `MS2`.`ID` FROM `featureenable` `MS2` WHERE (`MS2`.`FeatureKey` = `main_db`.`FeatureKey` ) )) ) \ 0 SDWORD-3 STRDB-v2.0b14 83c-218 EXIT SQLExecDirectW with return code -1 (SQL_ERROR) HSTMT 08DC1C30 WCHAR * 0x0F271F40 [ -3] SELECT DISTINCT `functionalsubgroup`.`Functional` ,`functionalsubgroup`.`SubGroup` ,`main_db`.`Feature` ,`main_db`.`FeatureKey` ,`functionalsubgroup`.`FSKey` FROM `main_db`,`functionalsubgroup`,`featureenable` `MS1` WHERE ((`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) AND (`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) ) AND (`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) ) AND (`functionalsubgroup`.`FSKey` = `main_db`.`FSKey` ) ) AND (`MS1`.`FeatureKey` = `main_db`.`FeatureKey` ) ) AND NOT(EXISTS (SELECT `MS2`.`ID` FROM `featureenable` `MS2` WHERE (`MS2`.`FeatureKey` = `main_db`.`FeatureKey` ) )) ) \ 0 SDWORD-3 DIAG [37000] [MySQL][ODBC 3.51 Driver][mysqld-4.0.20-standard]You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXISTS (SELECT `MS2`.`ID` FROM `featureenable` `MS2` WHERE (`M (1064) Robert M. Bartis Lucent Technologies, Inc Tel: +1 732 949 4565 Mail: [EMAIL PROTECTED] Pgr: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Finding records not in a set
I have a query, call qry_AssociatedFeatures, that finds all features associated with a given plan SELECT functionalsubgroup.Functional, functionalsubgroup.SubGroup, main_db.Feature, main_db.FeatureKey, functionalsubgroup.FSKey, featureenable.PlanName FROM featureenable INNER JOIN (main_db INNER JOIN functionalsubgroup ON main_db.FSKey = functionalsubgroup.FSKey) ON featureenable.FeatureKey = main_db.FeatureKey WHERE (((featureenable.PlanName)=[forms]![switchboard].[planname])); So far so good. Now I want to find the inverse, qry_UnassociatedFeatures, or all the features not associated with a plan. Complication here is the feature enable table can have the same feature key associated with multiple plans. I assumed if I took the table containing the unique set of features and query for those records whose feature key is not present in the qry_AssociatedFeatures query I would get what I wanted SELECT DISTINCT main_db.Feature, main_db.FeatureKey FROM main_db, qry_AssociatedFeatures WHERE (((main_db.FeatureKey)[qry_AssociatedFeatures].[FeatureKey])); Unfortunately, I still see records in the qry_UnassociatedFeatures that are also present in the qry_AssociatedFeatures. Any suggestions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Finding records not in a set
I did find a reference to EXIST/NOT EXISTS clause in the SQL manual, but I get an ODBC Failed call when I run the following simplified query SELECT * FROM main_db WHERE NOT EXISTS (SELECT * FROM featureenable WHERE featureenable.FeatureKey = main_db.FeatureKey); Bob -Original Message- From: Bartis, Robert M (Bob) [mailto:[EMAIL PROTECTED] Sent: Saturday, October 09, 2004 8:48 AM To: [EMAIL PROTECTED] Subject: Finding records not in a set I have a query, call qry_AssociatedFeatures, that finds all features associated with a given plan SELECT functionalsubgroup.Functional, functionalsubgroup.SubGroup, main_db.Feature, main_db.FeatureKey, functionalsubgroup.FSKey, featureenable.PlanName FROM featureenable INNER JOIN (main_db INNER JOIN functionalsubgroup ON main_db.FSKey = functionalsubgroup.FSKey) ON featureenable.FeatureKey = main_db.FeatureKey WHERE (((featureenable.PlanName)=[forms]![switchboard].[planname])); So far so good. Now I want to find the inverse, qry_UnassociatedFeatures, or all the features not associated with a plan. Complication here is the feature enable table can have the same feature key associated with multiple plans. I assumed if I took the table containing the unique set of features and query for those records whose feature key is not present in the qry_AssociatedFeatures query I would get what I wanted SELECT DISTINCT main_db.Feature, main_db.FeatureKey FROM main_db, qry_AssociatedFeatures WHERE (((main_db.FeatureKey)[qry_AssociatedFeatures].[FeatureKey])); Unfortunately, I still see records in the qry_UnassociatedFeatures that are also present in the qry_AssociatedFeatures. Any suggestions? -- 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: field that does not contain text between symbols - solved
Thanks for the replies. This appears to be the right answer: where page_body regexp '.*img .*.*' and page_body not regexp '.*img .* alt= .*.*'; bob == Bob Ramsey SYSTEMS ADMINISTRATION AND SYSTEMS PROGRAMMING III MA, Management of Information Systems 2004 MA, English Literature, 1992 ph: 1(319)335-9956 187 Boyd Law Building fax: 1(319)335-9019 University of Iowa College of Law mailto:[EMAIL PROTECTED]Iowa City, IA 52242-1113 For Hardware and Software questions, call 5-9124 == -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
filed that does not contain text between symbols
Hi, I have some web pages in a database and I want to check to make sure that all of the images have alt tags in them. So what I need to do is ask something like this in psuedocode: select page_name from web_pages where page_body does not contain 'alt=' between 'img' and ''; But I just can't figure out the right syntax. Any ideas? Thanks, bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: filed that does not contain text between symbols
Chris Blackwell wrote: not sure you can do this just with mysql, I think your gunna need to select the html from the db then send it to something like perl or php and use a regex parser on it. Yeah, that's what I was afraid of. Now all I have to do is decide between PERL and PHP for the scripting language. ;) Thanks, Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Student request for assistance
A friend of mine, who is not a member of the MySQL email aliases, is working toward her Bachelors degree. She is currently taking a database design course. She mentioned her current assignment to me and I suggested she allow me to forward this request to this email alias. I understand this is not the intent of the alias and I apologize to those who feel its inappropriate. I am simply looking for someone with experience as a database administrator, who is willing to help. The assignment is shown below. The research/assignment must before this coming Saturday. The result of an accelerated program she is enrolled in. Please contact Marlene directly at [EMAIL PROTECTED] if your willing to offer your insights. Assignment: Your goal is to find advice or an anecdote that shows the importance of analyzing business system information requirements before you begin building a database. Robert M. Bartis Lucent Technologies, Inc Tel: +1 732 949 4565 Mail: [EMAIL PROTECTED] Pgr: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: grant tables update backward compatibility
Hi Eric,: Thanks for responding. Preliminary tests indicate no problems, although it does of necessity make assumptions about which of the new privileges existing users should have when upgrading, but they were fairly safe. Regards, -Bob I would assume that you can since mysql probably does an internal select col, col1 to get the grant information and the new tables contain everything that the old ones do plus some extra privileges. The only thing I would worry about would be passwords changing. Make a small test case and let everybody know. :) -Eric On Fri, 3 Sep 2004 09:43:28 -0700 (GMT-07:00), [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi there, I have been using mysql 3.23.58, and I want to upgrade to 4.0.20. My question is this: after I run the script to upgrade the grant tables to support the new privilgeges, can I then revert back to 3.23.58 seamlessly or will I need to readjust the grant tables. Thanks in advance. -Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Eric Bergen [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Update MySQL row using URL link?
I currently update MySQL rows using phpMyAdmin. For example, dropping the following into the phpMyAdmin GUI: UPDATE mysql_db SET publish = 1 WHERE Date = 'Sunday, August 15, 2004 21:04:32' Since I get the update info in an e-mail send whenever the form is submitted, I'd like to turn querys like the above encoded into a URL - bypassing phpMyAdmin - which when clicked, will update the row. I found this article last night: Make SQL Queries over HTTP with XML with VS.NET (http://www.aspfree.com/c/a/ASP/Make-SQL-Queries-over-HTTP-with-XML-with-VSNET/ ) SELECT CustomerId, CompanyName FROM Customer http://localhost/sql?sql=select%20CustomerId,%20CompanyName%20from%20Customers%20FOR%20XML%20AUTO = The above looks very much like what I have in mind, but for MySQL. Anybody know how to do this? Many thanks in advance, -Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slow Insert into MyISAM table from Oracle Stored Procedure
Note: I installed MySQL on my labtop (a Windows XP machine) which is located at the same site as the Oracle server, just to compare the insert times with the remote MySQL database. Inserts which were taking over 4 minutes were now completing in 90 seconds or less. Approximately 1 second per insert is not great, but it is a lot better than what we were seeing before. So, we are planning on moving our MySQL database server to the same site as the Oracle database. Any other performance tuning suggestions would be be appreciated. Thanks! Bob [EMAIL PROTECTED] 19-Jul-2004 11:35 EST To: [EMAIL PROTECTED] cc: Subject:Slow Insert into MyISAM table from Oracle Stored Procedure I've developed an Oracle PL/SQL stored procedure that takes information from an Oracle 9.2.0.5 database and inserts this information into a MySQL 4.0.17 MyISAM table. The insert takes over 3 minutes to insert approximately 90 records based on an Oracle SQL Trace. A few things that may be factors in the slow performance I use MySQL ODBC 3.5.1 to connect Oracle to MySQL. Are there any parameters MySQL ODBC parameters that can be tuned to improve performance? I've tried to turn on tracing, but don't know if I am doing it correctly because I am not getting any .trc files. Are .trc files only generated on errors? The table in question has 98 columns with 3 text fields. It appears the insert statements actually inserts all non-TEXT fields first and then updates the record with the TEXT field data. I believe this is the expected behavior, but it is slowing things down a bit. It would be nice if I could trick the MySQL database into thinking it's inserting into a VARCHAR or CHAR field. The MySQL server resides a couple of hundred miles away from the Oracle server so Network latency is a factor. However, we do have a 786KB/s line with 70 ms latency which isn't bad. The only parameter/variable I've changed from the default on the MySQL server is ascii. There are probably some memory variables that could be tuned, but I'm not looking at high volumes yet, so I don't think that this would be the bottleneck. Any suggestions/recommendations would be much appreciated. Thanks, Bob Runion -- Here's the Oracle SQL Trace of the INSERT statement? INSERT INTO [EMAIL PROTECTED] VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42, :43, :44, :45, :46, :47, :48, :49, :50, :51, :52, :53, :54, :55, :56, :57, :58, :59, :60, :61, :62, :63, :64, :65, :66, :67, :68, :69, :70, :71, :72, :73, :74, :75, :76, :77, :78, :79, :80, :81, :82, :83, :84, :85, :86, :87, :88, :89, :90, :91, :92, :93, :94, :95, :96, :97, :98) call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 97 1.12 23.37 0 0 0 0 Execute 97 0.23 157.27 0 0 097 Fetch0 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total 194 1.35 180.64 0 0 097 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 18 (recursive depth: 1) * ** This e-mail message is intended only for the personal use of the recipient(s) named above. This message is confidential. If you are not an intended recipient, you may not review, copy or distribute this message. If you have received this communication in error, please notify the sender immediately by e-mail and delete the original message. **
Slow Insert into MyISAM table from Oracle Stored Procedure
I?ve developed an Oracle PL/SQL stored procedure that takes information from an Oracle 9.2.0.5 database and inserts this information into a MySQL 4.0.17 MyISAM table. The insert takes over 3 minutes to insert approximately 90 records based on an Oracle SQL Trace. A few things that may be factors in the slow performance I use MySQL ODBC 3.5.1 to connect Oracle to MySQL. Are there any parameters MySQL ODBC parameters that can be tuned to improve performance? I?ve tried to turn on tracing, but don?t know if I am doing it correctly because I am not getting any .trc files. Are .trc files only generated on errors? The table in question has 98 columns with 3 text fields. It appears the insert statements actually inserts all non-?TEXT? fields first and then updates the record with the ?TEXT? field data. I believe this is the expected behavior, but it is slowing things down a bit. It would be nice if I could ?trick? the MySQL database into thinking it?s inserting into a VARCHAR or CHAR field. The MySQL server resides a couple of hundred miles away from the Oracle server so Network latency is a factor. However, we do have a 786KB/s line with 70 ms latency which isn?t bad. The only parameter/variable I?ve changed from the default on the MySQL server is ?ascii. There are probably some memory variables that could be tuned, but I?m not looking at high volumes yet, so I don?t think that this would be the bottleneck. Any suggestions/recommendations would be much appreciated. Thanks, Bob Runion -- Here?s the Oracle SQL Trace of the INSERT statement? INSERT INTO [EMAIL PROTECTED] VALUES (:1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42, :43, :44, :45, :46, :47, :48, :49, :50, :51, :52, :53, :54, :55, :56, :57, :58, :59, :60, :61, :62, :63, :64, :65, :66, :67, :68, :69, :70, :71, :72, :73, :74, :75, :76, :77, :78, :79, :80, :81, :82, :83, :84, :85, :86, :87, :88, :89, :90, :91, :92, :93, :94, :95, :96, :97, :98) call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse 97 1.12 23.37 0 0 0 0 Execute 97 0.23 157.27 0 0 097 Fetch0 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total 194 1.35 180.64 0 0 097 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 18 (recursive depth: 1) ** This e-mail message is intended only for the personal use of the recipient(s) named above. This message is confidential. If you are not an intended recipient, you may not review, copy or distribute this message. If you have received this communication in error, please notify the sender immediately by e-mail and delete the original message. **
different kind of nested selects
Let's say I have two tables: T1: Name --- apple banana cherry T2: value| name -|-- 1 | apple 2 | banana 3 | banana 4 | cherry 5 | apple 6 | apple I want to get a result that looks like this: name| all_values apple| 1,5, 6 banana| 2,3 cherry| 4 In my head, the select statement looks like this: select t1.name, (select t2.value from t2 where t2.name=t1.name) as all_values from t1; Is there a way to do this with just one sql statement? Thanks, bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Document Upload Facility
Michael Mason wrote: I'm new to MySQL and already very impressed with it's flexibility, speed and functionality. This in mind, I am looking for a way to allow users to upload documents to the server for later retrieval by an administrator. Can this be done or will I have to find a nasty third party tool...? You should be able to do this, but be aware that storing arbitrary binary data might have security implications. Basically, what you end up doing is having a table something like this: create table user_files(user_name varchar(255) not null, user_file blob); Then you use your html code on a web page to let users upload a file. You take that file and insert it into the database. I've done it before and it works ok. Just make sure to check that if someone uploads an executable file you don't accidentally execute at some point. I don't have code handy to share, but if you need some I could probably knock something out quickly. bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Cost of joins?
I have a question that may be similar to the one which Margaret asked recently concerning the Cost of Joins. I have a DB with numerous tables and have inserted keys to relate one table to another. The method minimizes the data I store, but results in me joining multiple tables, sometimes 10 at a time to retrieve information needed to satisfy a given search request. A simple version of my DB would be: Table one oneKey a c b Table two twoKey oneKey-Foreign key e f g Table three threeKey twoKey-Foreign key x y z If I want to collect data concerning x, y, z and its relation to 'a' I need to join tables one, two and three. It seems to me this is the most efficient storage of information. It also, assuming the resulting queries return a large number of records, is the most efficient for end users when moving from record to record. Conversely, it also seems like it will be the most inefficient while waiting for the query results to be calculated? I've noticed another solution proposed by some is to carry forward Foreign Keys. For instance: Table one oneKey a c b Table two twoKey oneKey-Foreign key e f g Table three threeKey oneKey-Foreign key twoKey-Foreign key x y z In this case collecting the same information (x, y, z and its relation to 'a') I need only join tables one and three or just three and do look-ups into table one. Obviously, the issue scales if you add 10 tables into the equation. This method appears less efficient from a data storage perspective and complicates the application. I need to store multiple Foreign keys each time a record is added to a given table. The time to return query results would appear to be very short as each query would only return a single record, but the record to record movement would result in a new query each time. What advantages or disadvantages are there to one method vs. another? Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: debug
On 06/30/2004 02:46 PM Andrew Pattison spoke: The way I do this is within PHP is to echo the value stored in mysql_error after each SQL statement. If you're not using PHP then this probably doesn't help though ;-) I'm not using PHP. I'm loading tons of data with SQL statements from the command line. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [PHP-DB] mysql limit
Moved from a PHP list to a MySQL list. :-) On 06/30/2004 09:55 PM John W. Holmes spoke: Bob Lockie wrote: If I select rows with a limit clause I need to know if there are more rows than the limit. Either do a SELECT COUNT(*) prior to your LIMIT query to see how many total rows there are, or use SQL_CALC_FOUND_ROWS and FOUND_ROWS() (more info here: http://dev.mysql.com/doc/mysql/en/Information_functions.html) Is found_rows standard SQL? I want to easily portable code. Maybe I should use a select count first. :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Installing innoDB
On 07/01/2004 11:48 PM [EMAIL PROTECTED] spoke: I have MySQL 3.23 installed, which comes with RedHat ES3. I find that innoDB is not enabled. Anybody knows can I enable it? Thanks, Joseph Recompile the binary or upgrade to one that includes it. I don't think there is a runtime switch. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Error 1005 when adding a Foreign Key
I recently added a column and Index to an existing table. I wanted to also add a Foreign Key. I have done this before defining the commands outside MySQL and souring the file in for new tables, but would prefer to not have to dump the current table just for the modification. I tried to add one based on the users guide, see command/response below, without success. Obviously I am missing something. Any suggestions? mysql alter table runload_list add foreign key (PlanName) references testplans (PlanName) on update cascade on delete restrict; ERROR 1005: Can't create table './mydb/#sql-3ebd_430.frm' (errno: 150) Robert M. Bartis Lucent Technologies ¢ Room HO 1C-413A (HO) / 1B-304 (WH) ( 732.949.4565 (HO) / 973.386.7426 (WH) * [EMAIL PROTECTED]
debug
I'm running a ton of sql statements to load data. Is there a way to not display successes: Query OK, 1 row affected (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0 and display failures and the query statement that failed? There are 60 000+ of these and I'd ideally like to debug the inserts without actually doing them. It fails on duplicate keys but I have no idea where the data is flawed. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: OUTFILE to CVS with headers
well, you can easily output into a comma separated file but the INTO OUTFILE option will not allow you to retain the headers. You will need to build the logic into a perl script or language of your choice to get the headers there. Sounds like your fields records are all on one line becausing you're not ending lines with a new line... try this. SELECT Fields FROM databasename WHERE (criteria) INTO OUTFILE 'pathtofile/filename.csv' FIELDS TERMINATED BY '\,' OPTIONALLY ENCLOSED BY '\' LINES TERMINATED BY '\n'; i hope this helps... -- From: Mike Koponick Sent: Thursday, June 24, 2004 5:51 PM To: [EMAIL PROTECTED] Subject: OUTFILE to CVS with headers Hello, I'm working on a project where I must export to a text file and the text file needs to be formed into columns so that it can be imported into a WORD mail merge document. So, the format of the output file has to have the headers for each column and the lines must have a CR at the end of each line in order to keep the columns formatted. Here is what I'm doing now: select created, status,user, comment1, comment7, comment8, trouble from tbl WHERE customer = 'customer' AND created BETWEEN '2004-05-31' AND '2004-06-25' ORDER BY created, status into outfile 'test5.txt' fields terminated by ',' optionally enclosed by '' escaped by '\\'; All the lines run together so it makes it impossible to import. Thanks in advance for your help. Mike This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. Please report errors to [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql control center documentation
Had the same issue. I've bookmarked it, but its not clear why its so hard to find. Its good stuff man put it out front:-) -Original Message- From: Michael Stassen [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 22, 2004 12:13 PM To: Chris Stevenson Cc: [EMAIL PROTECTED] Subject: Re: mysql control center documentation The MySQL web site was recently reorganized. For some reason, when they did so, the buried the manual 4 clicks away in the Developer Zone. It's not clear to me how anyone new is supposed to find it there. In any case, the URL is http://dev.mysql.com/doc/mysql/en/index.html. Michael Chris Stevenson wrote: Is there a user guide available anywhere? I can't seem to find anything on mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
opposite query
I have: select name from a, b where a.type='X' and a.id=b.id; I want a query to return all the rows that were NOT found by the above query. I can't simply do: select name from a, b where a.type!='X' and a.id=b.id; because there is more than one row in b for each type!='X' but there is only one row in b for each type='X' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: opposite query
On 06/21/2004 04:26 PM [EMAIL PROTECTED] spoke: I don't understand. You want every other record except .what? By flipping the equality the way you did, you should see all of the records where a.id = b.id (regardless of what the b.type value is) where a.type was not 'X' which is one reasonably valid solution to the statement all of the rows NOT found by the above query The more details you give us the better we can help you. If you can give us your SHOW CREATE TABLE statements and possibly some sample data, we should be able to get at exactly the data you want to see. Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine drop table a; create table a ( id int auto_increment, name varchar(20), primary key (id) ); drop table b; create table b ( id int auto_increment, a_id int not null, name char(20), type char, primary key (id) ); insert into a (name) values (row1); insert into b (a_id, name, type) values(1, 1b, X); insert into b (a_id, name, type) values(1, 2b, Y); insert into b (a_id, name, type) values(1, 3b, Y); insert into b (a_id, name, type) values(1, 4b, Z); insert into a (name) values (row2); insert into b (a_id, name, type) values(2, 1a, X); insert into b (a_id, name, type) values(2, 2a, Y); insert into b (a_id, name, type) values(2, 3a, Y); insert into b (a_id, name, type) values(2, 4a, Z); insert into a (name) values (row3); insert into b (a_id, name, type) values(3, 2a, Y); insert into b (a_id, name, type) values(3, 3a, Y); insert into b (a_id, name, type) values(3, 4a, Z); select a.name from a, b where b.type='X' and a.id=b.a_id; returns row1 and row1 from a. Now I need a select that will return row3 but not rows 1 or 2. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: opposite query
On 06/21/2004 05:02 PM Brent Baisley spoke: The opposite of the query would be a.type!='X' and there is no related record in table b. Not sure if that is what you what It isn't what I want because there could be other a.type other than 'X'. Oops, that should be b.type I need to return the a records that have no b records of type 'X' but there may be b records of other types. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do you deal with URL's?
Personally, I'd split that into 2 fields. I think that's a better way
to model the data unless there's something I don't know.
Otherwise, try this:
mysql select * from url;
+---+
| url |
+---+
| a href=http://www.alabama.gov;Alabama/a |
| a href=http://access.wa.gov;Washington/a |
+---+
2 rows in set (0.00 sec)
mysql select substring(url,locate('',url)+2,
char_length(url)-locate('',url
)-5) as state from url;
++
| state |
++
| Alabama|
| Washington |
++
2 rows in set (0.00 sec)
mysql
What I had to to was to have mysql take the string:
a href=http://www.alabama.gov;Alabama/a
and give me the parts between and /a. First, I had to find the
position of and then add 2 to it. The substring function in mysql
takes the parameters string, starting_position, and length. Using
locate, I got the starting postion and added 2 to it. For length, I had
to use locate again; locating gives me the position of the in .
Subtracting 5 gives me the right length after discounting the /a and
the 2 positions I'm off from .
Someone more experienced that I can tell you if there's a more effecient
way. My inclination would be that for best results, you should split
the field in two and build your webpage like this:
a href=$URL$STATE/a
Hope this helps.
bob
David Blomstrom wrote:
Suppose I have a field with the names of states,
linked to their home pages:
a href=http://www.alabama.gov/;Alabama/a
a href=http://access.wa.gov/;Washington/a
If I display this on a webpage, I'll get the names of
the states, linked to their home pages. But is there a
simple strategy that will let me to display the names
UNLINKED on another page, or do I have to create a
second field that lists simple state names, with no
URL's?
Thanks.
__
Do you Yahoo!?
New and Improved Yahoo! Mail - 100MB free storage!
http://promotions.yahoo.com/new_mail
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: pattern matching - but in reverse
Oh, I think I know this one.
Copied from my console:
mysql select * from test;
++-+
| id | myvalue |
++-+
| 1 | 12 |
| 2 | 15 |
| 3 | 3 |
| 4 | 10 |
| 5 | 10 |
| 6 | 10 |
++-+
6 rows in set (0.04 sec)
mysql select * from test where myvalue = REPLACE('-1-2-','-','');
++-+
| id | myvalue |
++-+
| 1 | 12 |
++-+
1 row in set (0.06 sec)
In this example, the user did a search for '-1-2-' and I told mysql to
give me all records that equaled the result of the replace function that
replaced all '-' with nothing. So it matched 12 from the user input -1-2-.
If that works for you, let me know.
bob
Luke Majewski wrote:
Hi everyone,
ok, so I know how to use RLIKE to match regular expressions. However,
let's say I have an isbn number of:
0-06-430022-6
saved in the database but someone wants to search for it by entering:
0064300226
or even
006-430-0226
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: pattern matching - but in reverse
Whoops. I was thinking about how I have my isbn table stored. ;) I
prefer to remove all formatting from numbers like this(isbn, phone
numbers, social security numbers, etc) before storing them. Anyway,
here's something that should work. I'm not sure if it is the most
efficient way to do this, but it works:
mysql select * from t2;
+--+
| isbn |
+--+
| 12345|
| 123-45 |
| 123-4-5 |
| 123-4-56 |
| 123-4-57 |
| 123-4-58 |
| 123-3-58 |
| 123-3-58 |
+--+
8 rows in set (0.00 sec)
mysql select * from t2 where
replace(isbn,'-','')=replace('1-2-3-4-5','-','');
+-+
| isbn|
+-+
| 12345 |
| 123-45 |
| 123-4-5 |
+-+
3 rows in set (0.00 sec)
mysql
Note that in my example, I had three entries with essentially the same
isbn number, just formatted differently. This is basically the same
idea as before except now we are replacing the '-' in the data in both
the table and the user input string.
This seemed simpler than trying to first strip all of the '-' from the
user string and then re-insert them in the right places. You can do
this, but it's a lot clunkier and I wouldn't recommend it. If you are
curious, the sql statement is:
mysql select * from t2 where isbn=
- concat(
- substring(
- replace('1-2-3-4-5','-',''),
- 1,
- 3),
- '-',
- substring(
- replace('1-2-3-4-5','-',''),
- 4,
- 5)
- );
++
| isbn |
++
| 123-45 |
++
1 row in set (0.01 sec)
What I did here as to first strip all of the '-' out of the user string,
since we weren't sure where or if they were there. Then I rebuilt the
string to match the pattern ###-## using concat and substring. As I
said, this just seems far too clunky to deal with even if it does work.
Bob
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Recommendation on god MySQL books
I'm looking for suggestions on books that would help me to improve my understanding of MySQL operations, admin operations, replication etc. I'm new to MySQL and am about to embark on supporting a database for my team to use in recording test results. Any suggestions and recommendations ones to stay away from? Thanks in advance Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Erro on Query
Luiz Rafael Culik Guimaraes wrote: Dear Friends. when Creating the follow table I got an Erro when defining Column DESC(same error even if DESC is between ) DESC is a reserved word, try something else. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
string compare
Where is the syntax error in this? select name from users where name like '%' and strcmp( substr(name from 1 for 3), 'abc' )=0; Why should I use strcmp since MySQL automatically converts numbers to strings as necessary, and vice versa.? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unable to connect to DB
I have been using a local copy of the current production version of MySQL in a windows environment while we evaluate porting a MS Access front-end to make use of MySQL. We are ready to go prime-time within my team and as part of this exercise installed a LINIX version of MySQL on another machine. We can create a DB, import files etc, etc while on the local LINIX box, but are unable to connect via a remote machine. Any suggestions on how to debug this issue? Networking is not an issue as we can ping the machine. Thanks in advance for you help Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Unable to connect to DB
Thanks for the pointers. We can connect from the Linux box to my PC so we're convinced
the network is not an issue, but still cannot connect from my PC to the new MySQL
server installed on the Linux box. When we monitor the packets coming in we can see
the request to connect and to MySQL at port 3306 followed by the request for an ICMP
ping instead of the expected ACK. The ports are enabled in the etc/services file so
we're at a loss. Is there a setting to allow remote connections in Linux?
Still lost:-|
Bob
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Friday, June 11, 2004 3:30 PM
To: Bartis, Robert M (Bob)
Cc: [EMAIL PROTECTED]
Subject: Re: Unable to connect to DB
Check to make sure the linux box has its port open (do a MySQL ping).
From the windows box, telnet to your linux box on port 3306 (or whatever
you set your linux server to listen on in your my.cnf file) you should see
the version# of the server and a bunch of non-text information. If that
fails, your linux box is not listening. Change your server's config file to
open a port.
Another issue about mixing platforms if you are trying to access your
MySQL server through the ODBC driver (the most current version I can find
is 3.x) and your server is version 4.1 or better, you will have to
downgrade your password.
Log into the MySQL with admin permissions (root) and run this command:
update mysql.user set password=old_password('your ODBC pwd here') where
user = 'your ODBC user name here';
Also, make sure you have GRANT-ed the appropriate permission for your user
acct to the new tables.
HTH,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Bartis, Robert M
(Bob) To: [EMAIL PROTECTED]
[EMAIL PROTECTED]cc:
m Fax to:
Subject: Unable to connect to DB
06/11/2004 03:19
PM
I have been using a local copy of the current production version of MySQL
in a windows environment while we evaluate porting a MS Access front-end to
make use of MySQL. We are ready to go prime-time within my team and as part
of this exercise installed a LINIX version of MySQL on another machine. We
can create a DB, import files etc, etc while on the local LINIX box, but
are unable to connect via a remote machine. Any suggestions on how to debug
this issue? Networking is not an issue as we can ping the machine.
Thanks in advance for you help
Bob
--
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]
