Re: Duplicate Key problem (UPS software)
On Tue, 20 Jun 2006, css wrote: } When someone voids a shipment in the UPS software, it sends another } INSERT to the database, with the same data as the original row, the only } difference being the void column is now Y; } } This of course doesn't work because Duplicate entry 'BLAHBLAH' for key } 1 We use Worldship with MySQL via MyODBC and FileMaker, so I have a good amount of expierence using this software. What I did to avoid this issue was I created a MySQL table that had a different primary key than the UPS tracking number. Eg: I just made a simple auto-inc ID column that counts upward from 1. Using the Worldship's ODBC mapping preference, I made sure that *no* UPS field was mapped to this ID column, so that way when Worldship sends an insert, MySQL will auto-populate the primary key. This avoids the duplicate error messages you are getting, and makes it quite simple to figure out voided transactions from real transactions. If this didn't make sense, feel free to e.mail me and I can give more details off list, and include some screenshots from Worldship showing how I set it up to talk to MySQL. /vjl/ -- Vince J. LaMonica Knowledge is knowing a street is one way. [EMAIL PROTECTED] * Wisdom is still looking in both directions. When there's nothing else to read: http://w3log.vjl.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1 day left: 75% discount on MySQL/Firebird/InterBase/Oracle/SQL Server developer tool!
On Fri, 3 Mar 2006, David Griffiths wrote: } Definitely give this tool a try if you haven't - it has some very powerful } features - schema diffs (and the ability to create patches, etc - very } powerful if you maintain development, quality-assurance, and production } databases). } } The ability to sort connections, etc, by machine, by database, etc, is very } powerful. Those features sound very nice, indeed, but alas, there is no Mac OS X or Linux version. Are there other products [free/not free] that can do what this tool does, but run under UNIX flavors? /vjl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
using replace() to remove tab chars
Hi all, I am trying to use the replace() function in MySQL 4.1.13a to find and remove tab characters. I'm not sure how to signify the tab char, however. I've tried, \t to no avail: update products_description set products_description = replace(`products_description`,\t, ) where products_id = 33; Does anyone have any suggestions? The mysql server [and client] are running under Linux. The products_description field is a 'text' type. If there's further info you need, please let me know. Thanks in advance, /vjl/ -- Vince J. LaMonica Knowledge is knowing a street is one way. [EMAIL PROTECTED] * Wisdom is still looking in both directions. When there's nothing else to read: http://w3log.vjl.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: using replace() to remove tab chars
On Wed, 1 Mar 2006 [EMAIL PROTECTED] wrote: } [snip] } Does anyone have any suggestions? The mysql server [and client] are } running under Linux. The products_description field is a 'text' type. If } } there's further info you need, please let me know. } [/snip] } } Are you using REPLACE(column_name, '\t', ' ') ? I thought I had put an example of the SQL syntax in my e.mail, but yes, I have tried it with both single and double quotes: update products_description set products_description = replace(`products_description`,\t, ) where products_id = 37; and update products_description set products_description = replace(`products_description`,'\t',' ') where products_id = 37; For right now, I am only doing this on one record, for testing, but when I get the syntax right, the where clause will be removed. Currently, mysql reports: Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 regardless if I use single quotes or double quotes in the replace() call. Thanks for your quick reply! /vjl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: using replace() to remove tab chars
On Wed, 1 Mar 2006, Vince LaMonica wrote: } update products_description set products_description = } replace(`products_description`,'\t',' ') where products_id = 37; [snip] I should have also stated that in the above example, both the table name and the column name are the same - that's not a typo. /vjl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: using replace() to remove tab chars
On Wed, 1 Mar 2006, Price, Randall wrote: } Try the following: } } REPLACE(products_description, CHAR(9), ) Thanks, Randall, that did it. And I feel quite stupid, as well, because I re-checked my two test entries [33 and 37] and both had had their tabs removed at some point. So it turns out that my original, replace(products_description, '\t', ' ') *did* work, I was just testing it on two rows that had multiple spaces in a row, but not tabs. But for the archives and future posts - the CHAR(9) worked as did the single quoted \t. Thanks for all your help, everyone. Much appreciated! /vjl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
using a function to define default col value?
Hi all, I'm curious if this is possible in 4.10: I have a table: +--+-+--+-+---+--+ | Field| Type| Null | Key | Default | Extra| +--+-+--+-+---+--+ | tracking_id | int(12) | | PRI | NULL | auto_increment | ups_tracking | varchar(64) | | | | | | order_number | varchar(64) | | | | | | time_added | timestamp | YES | | CURRENT_TIMESTAMP | | | aba_order_number | varchar(96) | | | | | +--+-+--+-+---+--+ A sample value for the order_number field looks like this: ABA-123456 I would like to make the aba_order_number field reflect just what comes after the ABA- part. So, is there a way I can assign a default value to aba_order_number to this: IF(LEFT(order_number,4) = 'ABA-',REPLACE(order_number, 'ABA-',''),'') Currently this table is populated via ODBC. Only the ups_tracking and order_number fields are populated through UPS' WorldShip software [it simply performs an export of those two fields each time a new tracking number is generated]. I can run this: update example_table set aba_order_number=IF(LEFT(order_number,4) = 'ABA-',REPLACE(order_number, 'ABA-', ''),'') ; and I get the result I am looking for. But running this: alter table example_table alter column aba_order_number set default IF(LEFT(order_number,4) = 'ABA-',REPLACE(order_number, 'ABA-', ''),''); doesn't seem to make a difference, as it assigns the string IF(LEFT... as the default text, instead of seeing it as a function. The reason I am using an IF() is because some order_number values will not contain a leading ABA- string. I'm using 4.1.13, FWIW. Thanks for any tips y'all might have. /vjl/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
left joining on 3 tables
Hi all, I've got a SQL query I can't seem to generate, and am hoping someone out there has some ideas. I can not use subselects [using mysql 3.23, which doesn't support subselects]. I have 3 tables, laid out as follows: mysql desc authors; +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-++ | id | smallint(3) | | PRI | NULL| auto_increment | | last_name | varchar(60) | | | || | first_name | varchar(60) | YES | | NULL|| | middle_name | varchar(60) | YES | | NULL|| +-+-+--+-+-++ mysql desc papers; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | smallint(3) unsigned | | PRI | NULL| auto_increment | | year| varchar(4) | | | || | title | varchar(255) | YES | | NULL|| [snip] mysql desc paper_authors; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | paper_id | smallint(3) unsigned | | | 0 | | | author_id | smallint(3) unsigned | | | 0 | | | priority | tinyint(1) unsigned | | | 0 | | +---+--+--+-+-+---+ The last table, paper_authors, is mainly to be used to join the first two tables together. The priority column contains a number from 1 to 5. So basicly, a paper can have 1-5 authors, and I need to keep track of which author is #1, #2, #3, etc. I need to create a SQL statement that will output something like this: --+---+--+--+-+-+--+ id| author1 | author2 | author3 | author4 | author5 | year | --+---+--+--+-+-+--+ 4| last_name | ... 1999 Note that the columns labeled author2-author5 may be NULLs. Not all papers have 5 authors. I've created a sql statement attempts to produce the above results: SELECT papers.id, a1.last_name as author1, a2.last_name as author2, a3.last_name as author3, a4.last_name as author4, a5.last_name as author5, papers.year FROM paper_authors, papers, authors AS a1 LEFT JOIN authors AS a2 ON (a2.id = paper_authors.author_id AND paper_authors.paper_id = papers.id AND paper_authors.priority = '2') LEFT JOIN authors AS a3 ON (a3.id = paper_authors.author_id AND paper_authors.paper_id = papers.id AND paper_authors.priority = '3') LEFT JOIN authors AS a4 ON (a4.id = paper_authors.author_id AND paper_authors.paper_id = papers.id AND paper_authors.priority = '4') LEFT JOIN authors AS a5 ON (a5.id = paper_authors.author_id AND paper_authors.paper_id = papers.id AND paper_authors.priority = '5') WHERE a1.id = paper_authors.author_id AND paper_authors.priority = '1' AND paper_authors.paper_id = papers.id ORDER BY year ASC The above query performs a join on author where priority = '1' [because every paper will have at least 1 author], and then 4 left joins on the priorities 2-5. However, the result of this query produces only accurate results for the join [eg: all the author '1's are right, but author2-5 are all NULL]. What am I doing incorrectly? I know it's something simple I'm not seeing. Any help you can provide would be most appreciated. Thanks! /vjl/ -- Vince LaMonica UC Irvine, School of Social Ecology W3 Developer * 116 Social Ecology I, Irvine, CA 92697 [EMAIL PROTECTED] http://www.seweb.uci.edu/techsupport Microsoft asks you where you want to go. UNIX gets you there. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB + fulltext + search + support
On Fri, 15 Nov 2002 [EMAIL PROTECTED] wrote: } P.S. I have to take this decision very fast. } Because I don't have time to search the mail list for this decision I need } quick and short well explanation. You might want to check the MySQL documentation on transactions: http://www.mysql.com/doc/en/ANSI_diff_Transactions.html It's a well written explanation on how MySQL deals with transactions. For a more detailed look, check out: http://www.mysql.com/doc/en/InnoDB_transaction_model.html HTH, /vjl/ -- Vince LaMonica UC Irvine, School of Social Ecology W3 Developer * 116 Social Ecology I, Irvine, CA 92697 [EMAIL PROTECTED] http://www.seweb.uci.edu/techsupport Negotiations and love songs are often mistaken for one and the same. - Paul Simon - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
simple query turned ugly
Hi all, I've been training someone on how to use MySQL, and apparently I'm suffering from brain fade big time. The user has created two tables; an authors table and a publications table. The authors table has 4 cols: id [primary/smallint/autoincrement], last_name, middle_name, first_name. The publications table has several cols, but the ones most important to this question are: id [primary/smallint/autoincrement], author1 [smallint, foreign key to authors.id/default NULL], author2 [same], author3 [same], author4 [same], author5 [same], and year [char(4)]. The user has created multiple author cols in the publications table because the order of the author matters [eg: it is better for someone to be an author1 than an author2 or a dreaded author5]. Some publications have 1 author, some have up to five. Putting together a simple query to find out the names of the author[s] for each publication: SELECT author1, author2, author3, author4, author5, year FROM `papers` GROUP BY papers.id ORDER BY `year` ASC This produces a nice 'table' of each publication's 1-5 authors, listed by their id. How do I alter the query to replace their id with authors.last_name? I'm a php coder, but in this case, the user wishes to do this with straight sql queries. And for whatever reason, I can't come up with a solution to this. If anyone has an idea or two to throw my way, I would really appreciate it! /vjl/ -- Vince LaMonica UC Irvine, School of Social Ecology W3 Developer * 116 Social Ecology I, Irvine, CA 92697 [EMAIL PROTECTED] http://www.seweb.uci.edu/techsupport If it be now, 'tis not to come; if it be not to come, it will be now; if it be not now, yet it will come: the readiness is all. -- William Shakespeare, Hamlet. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: simple query turned ugly
On Thu, 14 Nov 2002, Roger Baklund wrote: } * Vince LaMonica [snip] } The user has created multiple author cols in the publications table } because the order of the author matters [eg: it is better for someone to } be an author1 than an author2 or a dreaded author5]. Some publications } have 1 author, some have up to five. } } hm... This is not good db design... I would use a third table: } 'paper_authors' with the columns 'paper_id', 'author_id' and 'prio', where } 'prio' is a tinyint with the values 1-5. You are correct, thanks. I added a paper_authors table with the above cols [except I called 'prio' 'priority' instead. Applying your left join [as well as Jon Frisby's suggestion] works great for the non-normalized version of this small database. But now that I have the 'join table' [paper_authors] above, I'm not so certain how to produce the same results. I'm currently attempting this by doing [only trying priority 1 and 2 right now]: SELECT papers.id, a1.last_name as auth1, a2.last_name as auth2, papers.year FROM paper_authors, papers, authors AS a1 left join authors AS a2 ON (a2.id = paper_authors.author_id AND paper_authors.paper_id = papers.id AND paper_authors.priority = '2') WHERE a1.id = paper_authors.author_id AND paper_authors.priority = '1' AND paper_authors.paper_id = papers.id ORDER BY year ASC Here's the 3 tables: mysql desc authors; +-+-+--+-+-++ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+-++ | id | smallint(3) | | PRI | NULL| auto_increment | | last_name | varchar(60) | | | || | first_name | varchar(60) | YES | | NULL|| | middle_name | varchar(60) | YES | | NULL|| +-+-+--+-+-++ mysql desc papers ; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | smallint(3) unsigned | | PRI | NULL| auto_increment | | year| varchar(4) | | | || | title | varchar(255) | YES | | NULL|| [snip] [i took the author1 - author5 cols out] mysql desc paper_authors; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | paper_id | smallint(3) unsigned | | | 0 | | | author_id | smallint(3) unsigned | | | 0 | | | priority | tinyint(1) unsigned | | | 0 | | +---+--+--+-+-+---+ Running the above query produces a the correct priority 1 author, but the priority 2 author rows are all NULL. Running the query with all 4 left joins results in the same NULL values in the auth2, auth3, etc, cols. I've got to be missing something basic here, right? Any further assistance would be most appreciated. /vjl/ -- Vince LaMonica UC Irvine, School of Social Ecology W3 Developer * 116 Social Ecology I, Irvine, CA 92697 [EMAIL PROTECTED] http://www.seweb.uci.edu/techsupport Tower: Delta Zulu Romeo, turn right now and report your heading. Pilot: Wilco. 341, 342, 343, 344, 345... - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ERROR 2002: Can't connect to local MySQL server through socket'/tmp/mysql.sock' (111)
On Fri, 15 Nov 2002, Edwin Raj wrote: } [root@edwinraj bin]# /usr/local/mysql/bin/mysqladmin -u root password } 'simple' } /usr/local/mysql/bin/mysqladmin: connect to server at 'localhost' failed } error: 'Can't connect to local MySQL server through socket } '/tmp/mysql.sock' (111)' } Check that mysqld is running and that the socket: '/tmp/mysql.sock' exists! Looks like the error message is pretty clear - mysqld is not running. MySQL is a client/server application - there is a server that needs to run, and then the client can connect to that server. You need to make sure that mysqld is running. Since it appears you're on a linux box, you can run this command: ps aux |grep mysql If nothing comes up, then you need to start mysqld. It may reside in /usr/sbin. You should probably check the MySQL manual: http://www.mysql.com/doc/en/Post-installation.html HTH, /vjl/ -- Vince LaMonica UC Irvine, School of Social Ecology W3 Developer * 116 Social Ecology I, Irvine, CA 92697 [EMAIL PROTECTED] http://www.seweb.uci.edu/techsupport No matter what happens, somebody will find a way to take it too seriously. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
compiling client only on SunOS 4.1.3
Hi all, Attempting to compile the mysql client only as a non-root user [install locally at $HOME/bin]. I grabbed the source from: http://www.mysql.com/Downloads/MySQL-3.23/mysql-3.23.52.tar.gz uname -a reports: SunOS ka 4.1.3_U1 14 sun4m configure is run as such: ./configure --without-server --disable-shared --prefix=/oops/ka/vjl/bin and then, after it successfully [no errors] configures the files, I run a simple, make. make errors out with: echo timestamp llstr.lo gcc -DDEFAULT_CHARSET_HOME=\/oops/ka/vjl/bin\ -DDATADIR=\/oops/ka/vjl/bin/var\ -DSHAREDIR=\/oops/ka/vjl/bin/share/mysql\ -DUNDEF_THREADS_HACK -DDONT_USE_RAID -I./../include -I../include -I./.. -I.. -I.. -O3 -DDBUG_OFF -c conf_to_src.c conf_to_src.c: In function `main': conf_to_src.c:45: `EXIT_FAILURE' undeclared (first use this function) conf_to_src.c:45: (Each undeclared identifier is reported only once conf_to_src.c:45: for each function it appears in.) conf_to_src.c:64: `EXIT_SUCCESS' undeclared (first use this function) conf_to_src.c: In function `print_arrays_for': conf_to_src.c:124: `EXIT_FAILURE' undeclared (first use this function) *** Error code 1 make: Fatal error: Command failed for target `conf_to_src.o' Current working directory /oops/ka/vjl/tmp/mysql-3.23.52/libmysql *** Error code 1 make: Fatal error: Command failed for target `all-recursive' Current working directory /oops/ka/vjl/tmp/mysql-3.23.52 *** Error code 1 make: Fatal error: Command failed for target `all-recursive-am' I used gtar to untar the tarball, fwiw. I'm not sure what the above error is refering to; any incite into getting the above to work, or a source for a binary compile of SunOS 4.1.3 [client only] would be most appreciated. Thanks, /vjl/ -- Vince LaMonica UC Irvine, School of Social Ecology W3 Developer * 116 Social Ecology I, Irvine, CA 92697 [EMAIL PROTECTED] https://www.seweb.uci.edu/~vjl If Bill Gates had a nickel for every time Windows crashed... ... oh wait, never mind. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
