RE: slave to master
Master-master with load balancer would be best Jason Mallory, MySQL/SQL Server DBA p: +1.480.752.1198 | m: +1.480.823.7771 | f: +1. 480.752.1105 | www.iridium.com The information contained in this email is strictly confidential and may be legally privileged and protected from disclosure by law. This email is intended for use by the addressee only. Notice is hereby given that any disclosure, use or copying of the information by anyone other than the intended recipient is strictly prohibited and may be illegal. If you have received this email in error, please destroy all electronic and other copies of this message and contact the sender or Iridium at em...@iridium.com -Original Message- From: Thomas [mailto:thomasit...@gmail.com] Sent: Thursday, April 28, 2016 2:21 PM To: mysql@lists.mysql.com Subject: slave to master Hi, I have setup an master slave replication. This works fine. I have running an Apache webserver and some other programms accessing the master. Whats the standard pocedure if master fail? I want to start up the programms on the slave by hand and then they are accessing the mySQL slave. Can they write to the slave or do I have to change something before in the mysql slave configuration? thanks Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Audit Table storage for Primary Key(s)
I'll ask the dumb question. Why not create individual history tables corresponding to your 'main' tables? So, if you have an 'address' table, then the original record could be written to an 'address_his' table via an update or delete trigger (depending on whether you allow deletions or not) when a change is made...and the updated address record would be in the 'address' table. The address_his table would really only need two additional fields to track your data - a user field and a journal date/time. Not sure how you're planning on writing to the changes to your audit table, but this would allow the database to do the work instead of having to write application code to do it. Note: This is based on how I see things for the current application where I work. Doesn't mean that it's right or wrong...it just works for us. -Original Message- From: Neil Tompkins [mailto:neil.tompk...@googlemail.com] Sent: Friday, May 31, 2013 3:43 AM To: [MySQL] Subject: Fwd: Audit Table storage for Primary Key(s) Any advice anyone ? -- Forwarded message -- From: Neil Tompkins neil.tompk...@googlemail.com Date: Thu, May 30, 2013 at 8:27 AM Subject: Audit Table storage for Primary Key(s) To: [MySQL] mysql@lists.mysql.com Hi, I've created a Audit table which tracks any changed fields for multiple tables. In my Audit table I'm using a UUID for the primary key. However I need to have a reference back to the primary key(s) of the table audited. At the moment I've a VARCHAR field which stores primary keys like 1 1|2013-05-29 2|2013-05-29 2 3 1|2|2 etc Is this the best approach, or should I have a individual field in the audit table for all primary keys. At the moment I think the max number of primary keys on any given table is 3 Thanks Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: update a row only if any column has changed, in a very large table
If'n it were my nickel, here is how I would solve the problem (at a somewhat high level). That is, assuming I had an ETL tool available. 1. Create landing tables for your source data. 2. Load data from the source table(s) to your new landing table(s). 3. Perform lookups from the new landing table to target to identify: inserts, updates, deletes, do nothings. 4. Write these status back to the landing table. (Want to separate the inserts from the updates from the deletions.) 5. Load the 'inserts' to your target table. 6. Load the 'updates' to your target table. 7. Perform the 'deletes' on your target table. And, one other thing that I would do is to log counts and times...so I could go back over time and evaluate performance. But then again, I work with ETL tools...so, that is my proverbial hammer. And given that, everything pretty much looks like a nail. -Original Message- From: Rajeev Prasad [mailto:rp.ne...@yahoo.com] Sent: Saturday, April 06, 2013 3:57 PM To: mysql list Subject: update a row only if any column has changed, in a very large table hello, I have a table with around 2,000,000 records (15 columns). I have to sync this from an outside source once everyday. not all records are changed/removed /new-added everyday. so what is the best way to update only those which have changed/added/or deleted? i can use update_or_create but that will update (re-write the row) even if nothing has changed in the row/record. wont that be an overhead? how can i escape that? what would be the fastest and least resources consuming way to do this table update? I also have another table with 500,000 rows and i wish to implement the same solution to that too. I earlier posted this on DBIx list, as i thought i could use DBIx tools to manage this. but based on response, it seems that MySQL tools would be more helpful in doing it in most efficent way. Plz. advice how can i address this. I also considered to delete and simply recreate the table each day. but chnages/add and delete are not too many (may be a few hundreds.. max) ty. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Basic SELECT help
Having watched responses go back and forth, I'll throw my cave-man approach into the mix. select id from (select distinct id, count(*) from my_table where type in (2,5) group by id having count(*) = 2)a; And addressing one of your concerns about more than two variables...in this example,you would have to update the values in the where clause and the count. It ain't the prettiest...and not ideal from a performance perspective, but it does work. I guess it kind of depends on how far the real-world problem strays from this small example. -Original Message- From: Neil Tompkins [mailto:neil.tompk...@googlemail.com] Sent: Thursday, November 22, 2012 8:30 AM To: [MySQL] Subject: Basic SELECT help Hi, I'm struggling with what I think is a basic select but can't think how to do it : My data is id,type 1000,5 1001,5 1002,2 1001,2 1003,2 1005,2 1006,1 From this I what to get a distinct list of id where the type equals 2 and 5 Any ideas ? Neil -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Additional Software to Download and Install
I'm a fan of Toad for MySQL. http://toadformysql.com/index.jspa -Original Message- From: AndrewMcHorney [mailto:andrewmchor...@cox.net] Sent: Friday, October 14, 2011 1:12 PM To: mysql@lists.mysql.com Subject: Additional Software to Download and Install Hello I just downloaded the MySql server software. I am now looking for software that is gui based and will allow me to easily define a database, create tables and to do updates of records within the tables. It would be fantastic if the software had report generating capabilities and also would allow me to create and execute sql commands and to write stored procedures to process the data. The tables are going to be fairly simple. Thanks Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=jason.trebilc...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Periodic slow performance with Confluence application
Thank you in advance ... (I have paid support with Confluence and I have also posted my question with them.) Our installation will run for hours or days without issues, and then CPU usage quickly spikes to nearly 100%, with mysqld taking 90% or more of the CPU. Bouncing Confluence and Mysql fixes the problem. We are running on 2 virtualized CPUs with 2 GB of free RAM (2 GB of the 4 GB total is taken by Confluence). The host is used for Confluence and its Mysql database only. $ uname -a Linux dvprwiki1.den.ofi.com 2.6.18-238.5.1.el5 #1 SMP Mon Feb 21 05:52:39 EST 2011 x86_64 x86_64 x86_64 GNU/Linux $ mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9895 Server version: 5.0.77 Source distribution $ cat /etc/my.cnf [mysqld] default-character-set=utf8 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 max_allowed_packet=3200 transaction_isolation=READ-COMMITTED default-storage-engine=INNODB default-table-type=INNODB [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid The following snapshot was taken during very high CPU usage: mysql SHOW INNODB STATUS\G *** 1. row *** Status: = 110825 20:16:15 INNODB MONITOR OUTPUT = Per second averages calculated from the last 5 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 41103624, signal count 20688109 --Thread 1177348416 has waited at mem0pool.c line 335 for 0.00 seconds the semaphore: Mutex at 0xafaf3d8 created file mem0pool.c line 205, lock var 0 waiters flag 0 Mutex spin waits 0, rounds 15308404368, OS waits 26685967 RW-shared spins 44387784, OS waits 10227732; RW-excl spins 29194377, OS waits 2702826 TRANSACTIONS Trx id counter 0 1985318 Purge done for trx's n:o 0 1985271 undo n:o 0 0 History list length 4 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 1985271, not started, process no 4295, OS thread id 1175484736 MySQL thread id 109756, query id 23573855 localhost 127.0.0.1 confluence ---TRANSACTION 0 1985191, not started, process no 4295, OS thread id 1097603392 MySQL thread id 109754, query id 23572572 localhost 127.0.0.1 confluence ---TRANSACTION 0 1985172, not started, process no 4295, OS thread id 1178413376 MySQL thread id 109752, query id 23572650 localhost 127.0.0.1 confluence ---TRANSACTION 0 0, not started, process no 4295, OS thread id 1092864320 MySQL thread id 109736, query id 23573900 localhost root SHOW INNODB STATUS ---TRANSACTION 0 1985124, not started, process no 4295, OS thread id 1092598080 MySQL thread id 109705, query id 23572568 localhost 127.0.0.1 confluence ---TRANSACTION 0 1985281, not started, process no 4295, OS thread id 1174686016 MySQL thread id 109700, query id 23573825 localhost 127.0.0.1 confluence ---TRANSACTION 0 1985269, not started, process no 4295, OS thread id 1176815936 MySQL thread id 109681, query id 23573859 localhost 127.0.0.1 confluence ---TRANSACTION 0 1985309, not started, process no 4295, OS thread id 1093396800 MySQL thread id 109615, query id 23573897 localhost 127.0.0.1 confluence ---TRANSACTION 0 1980073, not started, process no 4295, OS thread id 1179212096 MySQL thread id 107223, query id 23442072 localhost 127.0.0.1 wikiuser ---TRANSACTION 0 1945259, not started, process no 4295, OS thread id 1100269888 MySQL thread id 107222, query id 22573280 localhost 127.0.0.1 wikiuser ---TRANSACTION 0 1980691, not started, process no 4295, OS thread id 113648 MySQL thread id 107221, query id 23459487 localhost 127.0.0.1 wikiuser ---TRANSACTION 0 1945238, not started, process no 4295, OS thread id 1093929280 MySQL thread id 107220, query id 22572932 localhost 127.0.0.1 wikiuser ---TRANSACTION 0 1945245, not started, process no 4295, OS thread id 1094195520 MySQL thread id 107219, query id 22573051 localhost 127.0.0.1 wikiuser ---TRANSACTION 0 1980082, not started, process no 4295, OS thread id 1093130560 MySQL thread id 104880, query id 23442447 localhost 127.0.0.1 wikiuser ---TRANSACTION 0 1945257, not started, process no 4295, OS thread id 1176017216 MySQL thread id 104879, query id 22573321 localhost 127.0.0.1 wikiuser ---TRANSACTION 0 1985317, ACTIVE 1 sec, process no 4295, OS thread id 1097070912 fetching rows, thread declared inside InnoDB 298 mysql tables in use 1, locked 0 MySQL thread id 109692, query id 23573899 localhost 127.0.0.1 confluence Sending data select page0_.CONTENTID as CONTENTID, page0_.CHILD_POSITION as CHILD_P13_, page0_.PARENTID as PARENTID, page0_.SPACEID as SPACEID, page0_.TITLE as TITLE, page0_.VERSION as VERSION, page0_.CREATOR as CREATOR, page0_.CREATIONDATE as CREATION6_,
Re: Any table visualization tools with wires connecting the actual columns?
Toad for MySQL can do the diagramming piece...but, it looks and feels like you might have some of the same frustrations with it as well. But, another tool worth exploring nonetheless. On Thu, Apr 7, 2011 at 2:17 PM, Daevid Vincent dae...@daevid.com wrote: Does anyone have any suggestions on this? I've written to SQL Maestro twice and they've not replied either. From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Friday, April 01, 2011 4:27 PM To: mysql@lists.mysql.com Subject: Any table visualization tools with wires connecting the actual columns? I am evaluating various tools for diagram generating of existing databases on some smaller databases (9 tables or so) first. The two I've tried so far are these: http://dev.mysql.com/downloads/workbench/ http://www.sqlmaestro.com/download/#mysql Both _seem_ robust and cosmetically polished, but feel to me lacking the most obvious and key component of the whole purpose to make an EER diagram. I don't understand in workbench, why it creates new keys for me on existing tables. Maestro doesn't do this nonsense. It isn't the tools business where I have keys, it only needs to be concerned with what links to what -- that I tell it to. It's further exacerbated by the fact that the documentation indicates these aren't even REAL keys, they are cosmetic only! WTF? Why add confusion guys? 1. Neither one seem to be smart enough to automatically know that columns of the same name should be linked, and furthermore they should be linked from all tables to the one where that column name is the PK. my tables don't have true InnoDB FKs setup. And some tables are MYISAM (as they're significantly faster). But I do use keys and I do have sane naming conventions, so I don't understand why they can't use the names, and if there are multiple tables (for some unlikely reason) then just prompt me which table to use. Which leads me to the second and third problems... So I manually have started to draw the connections, but: 2. How can I make the wires stick to a column on the left or right edge, so that I can have a direct visual link between the columns. Right now, it seems they float around the edge of the table box. That's sort of useless isn't it? it's like saying, well, something in this table points to something in that table.?! I would think that two programs with such high version numbers would have this feature. Maybe I'm missing a configuration or some way I'm supposed to do it? 3. Some of my databases point to tables in other databases on the same server. It would be useful if I could make a wire that indicates this. Are there other (better) options out there for this? I really don't want to do this in Visio or make a printout of the table boxes and tape string to my walls to visualize all the databases, tables and columns. -Daevid. _ No virus found in this message. Checked by AVG - www.avg.com Version: 10.0.1209 / Virus Database: 1500/3544 - Release Date: 04/01/11 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=dae...@daevid.com _ No virus found in this message. Checked by AVG - www.avg.com Version: 10.0.1209 / Virus Database: 1500/3544 - Release Date: 04/01/11 _ No virus found in this message. Checked by AVG - www.avg.com Version: 10.0.1209 / Virus Database: 1500/3557 - Release Date: 04/07/11
Surge 2010 Early Registration ends Tuesday!
Early Bird Registration for Surge Scalability Conference 2010 ends next Tuesday, August 31. We have a killer lineup of speakers and architects from across the Internet. Listen to experts talk about the newest methods and technologies for scaling your Web presence. http://omniti.com/surge/2010/register This year's event is all about the challenges faced (and overcome) in real-life production architectures. Meet the engineering talent from some of the best and brightest throughout the Internet: John Allspaw, Etsy Theo Schlossnagle, OmniTI Bryan Cantrill, Joyent Rasmus Lerdorf, creator of PHP Tom Cook, Facebook Benjamin Black, fast_ip Christopher Brown, Opscode Artur Bergman, Wikia Baron Schwartz, Percona Paul Querna, Cloudkick Surge 2010 takes place at the Tremont Grand Historic Venue on Sept 30 and Oct 1, 2010 in Baltimore, MD. Register NOW for the Early Bird discount and guarantee your seat to this year's event! -- Jason Dixon OmniTI Computer Consulting, Inc. jdi...@omniti.com 443.325.1357 x.241 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Register now for Surge 2010
Registration for Surge Scalability Conference 2010 is open for all attendees! We have an awesome lineup of leaders from across the various communities that support highly scalable architectures, as well as the companies that implement them. Here's a small sampling from our list of speakers: John Allspaw, Etsy Theo Schlossnagle, OmniTI Rasmus Lerdorf, creator of PHP Tom Cook, Facebook Benjamin Black, fast_ip Artur Bergman, Wikia Christopher Brown, Opscode Bryan Cantrill, Joyent Baron Schwartz, Percona Paul Querna, Cloudkick Surge 2010 focuses on real case studies from production environments; the lessons learned from failure and how to re-engineer your way to a successful, highly scalable Internet architecture. The conference takes place at the Tremont Grand Historic Venue on Sept 30 and Oct 1, 2010 in Baltimore, MD. Register now to enjoy the Early Bird discount and guarantee your seat to this year's event! http://omniti.com/surge/2010/register Thanks, -- Jason Dixon OmniTI Computer Consulting, Inc. jdi...@omniti.com 443.325.1357 x.241 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Last day to submit your Surge 2010 CFP!
Today is your last chance to submit a CFP abstract for the 2010 Surge Scalability Conference. The event is taking place on Sept 30 and Oct 1, 2010 in Baltimore, MD. Surge focuses on case studies that address production failures and the re-engineering efforts that led to victory in Web Applications or Internet Architectures. You can find more information, including suggested topics and our current list of speakers, online: http://omniti.com/surge/2010 The final lineup should be available on the conference website next week. If you have questions about the CFP, attending Surge, or having your business sponsor/exhibit at Surge 2010, please contact us at su...@omniti.com. Thanks! -- Jason Dixon OmniTI Computer Consulting, Inc. jdi...@omniti.com 443.325.1357 x.241 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
CFP for Surge Scalability Conference 2010
A quick reminder that there's one week left to submit your abstract for this year's Surge Scalability Conference. The event is taking place on Sept 30 and Oct 1, 2010 in Baltimore, MD. Surge focuses on case studies that address production failures and the re-engineering efforts that led to victory in Web Applications or Internet Architectures. Our Keynote speakers include John Allspaw and Theo Schlossnagle. We are currently accepting submissions for the Call For Papers through July 9th. You can find more information, including suggested topics and our current list of speakers, online: http://omniti.com/surge/2010 I'd also like to urge folks who are planning to attend, to get your session passes sooner rather than later. We have limited seating and we are on track to sell out early. For more information, including the CFP, sponsorship of the event, or participating as an exhibitor, please visit the Surge website or contact us at su...@omniti.com. Thanks, -- Jason Dixon OmniTI Computer Consulting, Inc. jdi...@omniti.com 443.325.1357 x.241 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
CFP for Surge Scalability Conference 2010
We're excited to announce Surge, the Scalability and Performance Conference, to be held in Baltimore on Sept 30 and Oct 1, 2010. The event focuses on case studies that demonstrate successes (and failures) in Web applications and Internet architectures. Our Keynote speakers include John Allspaw and Theo Schlossnagle. We are currently accepting submissions for the Call For Papers through July 9th. You can find more information, including our current list of speakers, online: http://omniti.com/surge/2010 If you've been to Velocity, or wanted to but couldn't afford it, then Surge is just what you've been waiting for. For more information, including CFP, sponsorship of the event, or participating as an exhibitor, please contact us at su...@omniti.com. Thanks, -- Jason Dixon OmniTI Computer Consulting, Inc. jdi...@omniti.com 443.325.1357 x.241 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: select daily random
At 08:59 PM 2/27/2010, you wrote: Hello everyone, How would I select a random row that changes daily? Thanks The common way would be to do: select * from table order by rand() limit 1; You can of course add a Where clause to select only those rows that were added today. select * from table where Log_Date=Date(Now()) order by rand() limit 1; This works fine as long as there are not too many dates to sort. Otherwise you will need to use an autoinc column and choose one of those randomly. This is not as easy as it looks because the sequence may have holes in it and may not be in the proper sequence. Mike Thanks for the reply Mike but the common way you mentioned didn't do what I wanted. I did some searching on Google and found the following PHP/MySQL code which seems to do what I want... $query = SELECT * FROM table ORDER BY rand( . date(Ymd) . ) LIMIT 1; ...It selects a random row that changes on a daily bases. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
select daily random
Hello everyone, How would I select a random row that changes daily? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: select daily random
...I am using PHP 5.2 Hello everyone, How would I select a random row that changes daily? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=ja...@jasoncarson.ca -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Exporting the result of a Query into excel
-Original Message- From: ishaq gbola [mailto:ishaq...@yahoo.co.uk] Sent: Tuesday, January 05, 2010 7:18 AM To: mysql@lists.mysql.com Subject: Exporting the result of a Query into excel Hi all, I would like to know if there is a tool or command in mySQL that allows one to export the result of query into excel formart If'n you can download and install Toad for MySQL, then the steps might go like this: 1. Write the query 2. Run the query 3. Click the Export data to an Excel file button/icon (if you have Excel 2007 installed, it might throw a warning at you) 4. Note where the file was created to. 5. ? 6. Profit! The only caveat to the above is to be aware of how many rows you want to export and the corresponding column/row limitations of whatever version of Excel you have installed. As an additional caveat, you could export to html or csv formats as well which would allow you to work around any size limitations imposed by Excel. Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Query help
Depending on whether you just need to count or the transaction numbers, one of the following three should get you where you want/need to be: To identify the count for comp_id = 675: select count(distinct trans_no) from trans where comp_id = 675 and result = 'o'; To identify the transactions: select distinct trans_no from trans where comp_id = 675 and result = 'o'; To identify the transactions and the individual counts: select trans_no, count(*) from trans where comp_id = 675 and result = 'o' group by trans_no; -Original Message- From: Richard Reina [mailto:rich...@rushlogistics.com] Sent: Sunday, December 13, 2009 12:37 PM To: mysql@lists.mysql.com Cc: rich...@rushlogistics.com Subject: Query help I was wondering if someone could lend a hand with the following query. I have table. SEARCHES |ID |trans_no|comp_id|result 13 | 455| 675| o 15 | 302| 675| o 16 | 455| 675| o 12 | 225| 629| y SELECT count(*) FROM SEARCHES WHERE comp_id=675 AND result='o' GROUP BY trans_no; gives me a count of 3. However, what I need is a count for how many different (unique) transactions company number 675 got a result 'o' which would be 2 (455 302). I have tried different group by columns but to no avail. Can someone help? Thanks, Richard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=jason.trebilc...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Natural join problem
Methinx you need a GROUP BY in there. See below. -Original Message- From: John Meyer [mailto:john.l.me...@gmail.com] Sent: Thursday, September 10, 2009 6:48 PM To: mysql@lists.mysql.com Subject: Natural join problem Two tables: USERS: USER_ID (PK) . . .etc TWEETS: TWEET_ID (PK) USER_ID (FK) Trying to get the user information and the number of tweets each person has: SELECT USERS.USER_NAME, COUNT(TWEETS.TWEET_ID) AS 'TWEETCOUNT' FROM TWEETS NATURAL JOIN USERS; select u.user_name, count(t.tweet_id) from users u, tweets t where u.user_id = t.user_id group by u.user_name -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: MySQL Windows version
-Original Message- From: russbucket [mailto:russbuc...@nwi.net] Sent: Friday, July 10, 2009 11:09 AM To: mysql@lists.mysql.com Subject: MySQL Windows version I have been using the Linux version of MySQL for five years, also used it on a Windows ME system even though documents said you couldn't. Recent a friend asked me to help him get it up and running on a Windows Vista system. I was looking for the system configuration requirements but could not find them in the manual (or I missed them)? We want to use localhost. Do you need Apache and PHP? Is there a Windows application that works like phpMyAdmin? I tried MySQLAdmin on my Linux system, but I could not cut and paste SQL Commands into the editor. [Jason Trebilcock] Toad for MySQL would be another option. Anything pointing to the above would be helpful. Thanks in advance. -- --- --- OpenSUSE 11.1 KDE 4.1.3, Intel DX48BT2 Core 2 Dual E7200. 4 GB DDR III GeForce 8400 GS, 320GB Disc (2) --- Russ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Default Date and Time
Hi All, I want to create a table that defaults to current_date and current_time. I have: CREATE TABLE `personalevent`( `pevent` mediumint(10) NOT NULL, `eventid` mediumint(10) NOT NULL, `userid` mediumint(10) NOT NULL, `username` varchar(10) NOT NULL, `password` varchar(10) NULL, `country` varchar(45) NULL, `zipcode` varchar(5) NULL, `city` varchar(35) NULL, `hstate` varchar(45) NULL, `exclusive` varchar(7) NULL, `eventtime` time NULL DEFAULT current_time(), `eventdate` date NULL DEFAULT current_date(), `eventdura` varchar(35) NULL, `daysevent` varchar(10) NULL, `crowd` varchar(25) NULL, `venue` varchar(50) NULL, `activitytype` varchar(45) NULL, `actdetails` varchar(255) NULL, `encodedby` varchar(100) NULL, `curmo` varchar(2) NULL, `pageweb` varchar(50) NULL, PRIMARY KEY (`pevent`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; But this throws a syntax error. I have tried Now() as well. What am I doing wrong? Best, -Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Duplicate key name when importing mysql dump file
Hi, I'm trying to import a dumpfile like so: cat aac.sql | mysql -u root AAC It all runs fine until I get something like: ERROR 1061 (42000) at line 5671: Duplicate key name 'FK_mediaZip_to_zipSet' Is there a way I can tell it to ignore or replace the key? Thanks, Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: IDE - SQLYog
I think we've got almost all of the big ones listed. To complete the list (or at least grow the list by one), let me offer up Toad for MySQL: http://www.toadsoft.com/toadmysql/ On Wed, Jun 10, 2009 at 1:55 PM, Isart Montane isart.mont...@gmail.comwrote: I've been using phpmyadmin as a MySQL GUI for some time and worked great for me. www.*phpmyadmin*.net Isart On Tue, Jun 9, 2009 at 8:57 PM, Daevid Vincent dae...@daevid.com wrote: SQLYog by Webyog is the best mySQL GUI client for Windows. Hands down. not even a question. I've used them all I think. http://webyog.com/en/ There's even a free community version, but honestly it's worth purchasing the extended one for all the added features. They also release new ones all the time so it's very actively developed. http://daevid.com -Original Message- From: Mosaed zamil [mailto:mzamils...@gmail.com] Sent: Tuesday, June 09, 2009 9:41 AM To: mysql@lists.mysql.com Subject: IDE Hello all, I used Database Workbench on trial bases. It is nice. I plan to purchase an IDE. Is is it the best around. Your feed back is appreciated. yours mosaed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=isart.mont...@gmail.com
Re: Start MySQL with --intit-file?
Hi Guys, i am still lost here: GRANT ALL ON mysql.* TO 'root'@'localhost' IDENTIFIED BY 'XXX'; GRANT ALL ON mysql.* TO 'root'@'173.8.172.53' IDENTIFIED BY 'XXX'; FLUSH PRIVILEGES; Here is the command that I am executing: [r...@server1 ~]# /etc/init.d/mysqld restart --init-file=~/cloudsql.sql Stopping MySQL:[ OK ] Starting MySQL:[ OK ] [r...@server1 ~]# In cloudsql.sql I have: GRANT ALL ON mysql.* TO 'root'@'localhost' IDENTIFIED BY 'xxx'; GRANT ALL ON mysql.* TO 'root'@'173.8.172.53' IDENTIFIED BY 'xxx'; GRANT ALL ON mysql.* TO 'root'@'67.23.34.37' IDENTIFIED BY 'xxx'; FLUSH PRIVILEGES; commit; When I try to connect from my machine in my apartment I get: Unable to connect to host 67.23.34.37. Be sure that the address is correct and that you have the necessary privileges. MySQL said: Host '173.8.172.53' is not allowed to connect to this MySQL server Can I start over some how or how do I fix? I have never had this much trouble, but I guess historically for me, I have not done a setup from scratch. -Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Start MySQL with --intit-file?
OK, I have done this: [r...@server1 ~]# mysqld_safe A mysqld process already exists [r...@server1 ~]# /etc/init.d/mysqld stop Stopping MySQL:[ OK ] [r...@server1 ~]# mysqld_safe --init-file=~/cloudsql.sql Starting mysqld daemon with databases from /var/lib/mysql STOPPING server from pid file /var/run/mysqld/mysqld.pid 090429 20:12:30 mysqld ended [r...@server1 ~]# mysqld_safe stop Starting mysqld daemon with databases from /var/lib/mysql STOPPING server from pid file /var/run/mysqld/mysqld.pid 090429 20:12:54 mysqld ended [r...@server1 ~]# /etc/init.d/mysqld start Starting MySQL:[ OK ] [r...@server1 ~]# I tried to connect and still same error message of: Unable to connect to host 67.23.34.37. Be sure that the address is correct and that you have the necessary privileges. MySQL said: Host '173.8.172.53' is not allowed to connect to this MySQL server I must be a dunce, I know you guys know what you are talking about! -Jason On Apr 29, 2009, at 12:28 PM, mark konetchy wrote: i dont think that the init.d script will accept the argument. you need to run /usr/bin/mysqld_safe --init-file=clouds.sql (or whatever the path to mysqld_safe is) 2009/4/29 Jason Todd Slack-Moehrle mailingli...@mailnewsrss.com Hi Guys, i am still lost here: GRANT ALL ON mysql.* TO 'root'@'localhost' IDENTIFIED BY 'XXX'; GRANT ALL ON mysql.* TO 'root'@'173.8.172.53' IDENTIFIED BY 'XXX'; FLUSH PRIVILEGES; Here is the command that I am executing: [r...@server1 ~]# /etc/init.d/mysqld restart --init-file=~/ cloudsql.sql Stopping MySQL:[ OK ] Starting MySQL:[ OK ] [r...@server1 ~]# In cloudsql.sql I have: GRANT ALL ON mysql.* TO 'root'@'localhost' IDENTIFIED BY 'xxx'; GRANT ALL ON mysql.* TO 'root'@'173.8.172.53' IDENTIFIED BY 'xxx'; GRANT ALL ON mysql.* TO 'root'@'67.23.34.37' IDENTIFIED BY 'xxx'; FLUSH PRIVILEGES; commit; When I try to connect from my machine in my apartment I get: Unable to connect to host 67.23.34.37. Be sure that the address is correct and that you have the necessary privileges. MySQL said: Host '173.8.172.53' is not allowed to connect to this MySQL server Can I start over some how or how do I fix? I have never had this much trouble, but I guess historically for me, I have not done a setup from scratch. -Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Start MySQL with --intit-file?
Hi Mark, I am all set now, The log said it could not find the sql file, so I put it in /tmp and hard coded the path to /tmp/cloudsql.sql and it works now! Weird because I had used ~/cloudsql.sql and /root/cloudsql.sql as paths too Thank you so much for your help! John and Andy too! -Jason On Apr 29, 2009, at 1:20 PM, mark konetchy wrote: Jason, It looks like mysql is erroring out when you try to start it from the command line. What does the error log say? 2009/4/29 Jason Todd Slack-Moehrle mailingli...@mailnewsrss.com OK, I have done this: [r...@server1 ~]# mysqld_safe A mysqld process already exists [r...@server1 ~]# /etc/init.d/mysqld stop Stopping MySQL:[ OK ] [r...@server1 ~]# mysqld_safe --init-file=~/cloudsql.sql Starting mysqld daemon with databases from /var/lib/mysql STOPPING server from pid file /var/run/mysqld/mysqld.pid 090429 20:12:30 mysqld ended [r...@server1 ~]# mysqld_safe stop Starting mysqld daemon with databases from /var/lib/mysql STOPPING server from pid file /var/run/mysqld/mysqld.pid 090429 20:12:54 mysqld ended [r...@server1 ~]# /etc/init.d/mysqld start Starting MySQL:[ OK ] [r...@server1 ~]# I tried to connect and still same error message of: Unable to connect to host 67.23.34.37. Be sure that the address is correct and that you have the necessary privileges. MySQL said: Host '173.8.172.53' is not allowed to connect to this MySQL server I must be a dunce, I know you guys know what you are talking about! -Jason On Apr 29, 2009, at 12:28 PM, mark konetchy wrote: i dont think that the init.d script will accept the argument. you need to run /usr/bin/mysqld_safe --init-file=clouds.sql (or whatever the path to mysqld_safe is) 2009/4/29 Jason Todd Slack-Moehrle mailingli...@mailnewsrss.com Hi Guys, i am still lost here: GRANT ALL ON mysql.* TO 'root'@'localhost' IDENTIFIED BY 'XXX'; GRANT ALL ON mysql.* TO 'root'@'173.8.172.53' IDENTIFIED BY 'XXX'; FLUSH PRIVILEGES; Here is the command that I am executing: [r...@server1 ~]# /etc/init.d/mysqld restart --init-file=~/ cloudsql.sql Stopping MySQL:[ OK ] Starting MySQL:[ OK ] [r...@server1 ~]# In cloudsql.sql I have: GRANT ALL ON mysql.* TO 'root'@'localhost' IDENTIFIED BY 'xxx'; GRANT ALL ON mysql.* TO 'root'@'173.8.172.53' IDENTIFIED BY 'xxx'; GRANT ALL ON mysql.* TO 'root'@'67.23.34.37' IDENTIFIED BY 'xxx'; FLUSH PRIVILEGES; commit; When I try to connect from my machine in my apartment I get: Unable to connect to host 67.23.34.37. Be sure that the address is correct and that you have the necessary privileges. MySQL said: Host '173.8.172.53' is not allowed to connect to this MySQL server Can I start over some how or how do I fix? I have never had this much trouble, but I guess historically for me, I have not done a setup from scratch. -Jason
Start MySQL with --intit-file?
I am trying to start MySQL with --init-file but i get that it is an invalid option. the 'man' page and --help dont help me decide what is. Here is what I am doing: r...@server1 ~]# mysql start --init-file = cloudsql.txt mysql: unknown option '--init-file' Thoughts? -Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Start MySQL with --intit-file?
Hi,
I am trying:
[r...@server1 ~]# /etc/init.d/mysqld restart --init-file=cloudsql.sql
Stopping MySQL:[ OK ]
Starting MySQL:[ OK ]
the cloudsql.sql file contains:
UPDATE mysql.user SET Password=PASSWORD('xxx') WHERE User='root';
FLUSH PRIVILEGES;
GRANT ALL ON mysql.* TO 'root'@'localhost';
FLUSH PRIVILEGES;
GRANT ALL ON mysql.* TO 'root'@'173.8.172.53';
commit;
But I still get that I cannot connect from 173.8.172.53
I still dont see what I am doing wrong...
-Jason
On Apr 28, 2009, at 5:34 PM, mark konetchy wrote:
hey jason,
you need to restart the *server* with the init-file option, have a
look at:
http://dev.mysql.com/doc/refman/5.0/en/server-options.html
2009/4/28 Jason Todd Slack-Moehrle mailingli...@mailnewsrss.com
I am trying to start MySQL with --init-file but i get that it is an
invalid
option. the 'man' page and --help dont help me decide what is.
Here is what I am doing:
r...@server1 ~]# mysql start --init-file = cloudsql.txt
mysql: unknown option '--init-file'
Thoughts?
-Jason
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=markkonet...@gmail.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Resetting MySQL Root Password
Hi All, CentOS 5.3 I installed MySQL Server via yum and started it. I tried entering: mysqladmin -u root password yourrootsqlpassword mysqladmin -h server1.example.com -u root password yourrootsqlpassword But I get: r...@server1 ~]# /usr/bin/mysqladmin -u root -h localhost password mypassword /usr/bin/mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)' How can I reset this and allow Root access, otherwise nobody has access! Thanks, -Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
DB crashing while dumping
Hello, I have been trying mysqldump with different switches to get my DB to dump. All give me the same error (after dumping 218gigs) : http://papernapkin.org/pastebin/view/4447/ Here is the command I'm currently trying.. mysqldump -A -q -v --lock-all-tables -u root -p --skip-extended-insert --master-data /root/sql_dump1.sql Can anyone advice on what to do? Thank You Very Much, Jason Davis
Re: DB crashing while dumping
On Sun, Feb 15, 2009 at 4:07 PM, Walter Heck li...@olindata.com wrote: http://bugs.mysql.com/bug.php?id=26081 Walter, Thanks for replying. I read the bug and the comments. This seems to be a problem on 64bit AMD hardware. Is this a correct assessment? I'm using 2x Xeon quad core 64bit. Thanks, jd
Re: DB crashing while dumping
On Sun, Feb 15, 2009 at 1:43 PM, Martin Gainty mgai...@hotmail.com wrote: Jason- can we see the schema and a few data rows for `soapware_charts_xmldocumentitems` says something about invalid pointers? Martin Ok, I hope this is what you are wanting, I'm a newb. schema: http://papernapkin.org/pastebin/view/4451/ two rows of data: http://papernapkin.org/pastebin/view/4452/ Thanks, Jason
grabbing even addresses?
Hello! I was wondering if something was possible, I have an excel file right now of US mailing addresses, and what I need to do is select all the odd numbered addresses on one road, is there an easy way I can do that from MySQL? the addresses could contain 3, 4 or 5 numbers per addresses such as: 123 Main 1232 Main 1233 Main 1234 Main 12345 Main and what I want out of those would be: 1232 Main 1234 Main Any ideas? Thanks for looking! :) -- Jason Pruim japr...@raoset.com 616.399.2355
Re: grabbing even addresses?
On Feb 2, 2009, at 1:41 PM, Christoph Boget wrote: I was wondering if something was possible, I have an excel file right now of US mailing addresses, and what I need to do is select all the odd numbered addresses on one road, is there an easy way I can do that from MySQL? the addresses could contain 3, 4 or 5 numbers per addresses such as: 123 Main 1232 Main 1233 Main 1234 Main 12345 Main and what I want out of those would be: 1232 Main 1234 Main Any ideas? Thanks for looking! :) Well, if this is something you will be doing a lot, the most efficient way to store the addresses would be to have separate columns for the house number and the street name. Doing that will allow you to run a query as simple as: SELECT * FROM Addresses WHERE (house_number % 2) == 0; If you can't (or don't want to) have separate columns, you can use a regular expression to pull out the house number then operating on it as above.You can read more about mysql and regular expressions here: http://dev.mysql.com/doc/refman/5.0/en/regexp.html Hi Chris, Thanks for the info, I had thought about splitting it but wanted to see if there was a better/different way. Right now this is a one time thing, but if it works well it's something that could turn into a regular thing for me to do. Thanks again! -- Jason Pruim japr...@raoset.com 616.399.2355
Re: need help with query...
On Dec 17, 2008, at 2:56 PM, Lamp Lists wrote:
I hate when somebody put in Subject line something like I just did
but after 15 minutes to try to be specific just with one short
sentence - I gave up. So, you can hate me - I understand (though,
help with my problem too) :-)
I have let say 3 tables people, organization, addresses. and they
are linked to each other with column person_id.
to select person by specified person_id it's not big deal, really
simple. though, I wonder how can I select records from these three
tables but somehow marked which table it belongs?
e.g.
select p.first_name, p.last_name, p.status, p.date_registered,
o.org_id, o.org_name, o.org_department, a.addres1, a.address2,
a.city, a.state, a.zip
from people p, organization o, addresses a
where p.person_id=123 and o.person_id=p.person_id and
a.person_id=p.person_id
I need somehow, together with result data, info which table data
belogs?
e.g.
not exactly this way but something like this:
$data = array(
'people' = array('lamp', 'lists', 1, '2008-12-12'),
'organization' = array(56, 'Lamp List', 'web'),
'addresses' = array('123 Main St.', 'Toon Town', 'NY', '12345')
}
thanks for any help.
-afan
It may not be the best option, but when I've done that type of stuff
in the past, I add another field to the database and call it like
List and put People in the people database. and then you could
just query the field List and display it how ever you needed.
--
Jason Pruim
japr...@raoset.com
616.399.2355
Re: Zip Codes with Leading Zeros
Hi Keith, I don't know how to fix it in MySQL, but if you import them into Excel, select the zip code field, go under format rows under the Special option is Zip Code. If you use that it will add a leading zero to any field that does NOT have at least 5 characters. Also.. If you have addresses to go with the zip codes, I work at a presorted mailing company and we could run your list through our software which would check, and verify the address, as well as the zip code. It's a very modest charge and we usually have a quick turn around. E-mail me off list if you are interested. Thanks! On Aug 21, 2008, at 11:07 PM, Keith Spiller wrote: Thanks for all your suggestions Tom. The mixed 5 digit zip code and 10 digit zip+4 code data set are in a varchar(20) field. I don't recall if the data was identical in both the CSV and Excel files, but I do remember I had the same problem. It's been many months since I imported the original data into MySQL and I still need to repair the damage zip codes before we attempt another export to Excel. I believe your final suggestion is my necessary route. Thanks again for your help Tom. Keith - Original Message - From: Kralidis,Tom [Burlington] [EMAIL PROTECTED] To: Keith Spiller [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Thursday, August 21, 2008 8:28 PM Subject: RE: Zip Codes with Leading Zeros Keith (I'm not very familiar with phpMyAdmin): what is the underlying datatype of your zip code field? On the command line, if I use: mysql select * into outfile '/tmp/file.txt' fields terminated by ',' optionally enclosed by '' lines terminated by '\n' from tmp; (note that the default output, if not specified is tab-separated) ...I get csv style output in the output file for columns of type varchar(100), for example, with records containing leading zeros. Are the outputs identical in CSV and Excel (not sure whether Excel is not showing the leading zeroes as part of the column formatting defaults). Of course, you could write a post-processing script that adds the leading zeros to records not long enough. ..Tom -Original Message- From: Keith Spiller [mailto:[EMAIL PROTECTED] Sent: Thu 21-Aug-08 22:11 To: mysql@lists.mysql.com Subject: Zip Codes with Leading Zeros Hi, RE: Zip Codes with Leading Zeros We need to export a MySQL table with a zip code field to Excel. We currently use PhpMyAdmin to export to CSV or Excel files. We have had problems with zip codes with leading zeros. The leading zeros are removed so that we are left with incomplete codes. Can you help us learn the correct procedure for dealing with, exporting and importing zip codes? Thank you very much for all your help. Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 11287 James St Holland, MI 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: looking for ready made address management
On Aug 12, 2008, at 2:30 AM, robert rottermann wrote: Hi there, I am about to create tools to maintain addresses (companies, persons, groups) As this is probably done allredy a million times over I would like to ask if somebody could point me from where I migth download the database structure for such a feat or whether someone of you could provide me one. this would not only spare me some time and errors designing it but I migth lern some tricks of the trade. Hi Robert, It sounds like you are looking for a CRM... There are lots of options out there (I'm assuming open source since I don't have time to check) google is your friend! :) -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 11287 James St Holland, MI 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Tracking changes in large datasets over time
I'm having trouble working through a data problem. Any tips or keywords that might clue me into a known pattern would be incredibly appreciated! I have about 500,000 users in my system. Each user has a points balance, updated by a system over which I have no control. I'd like to track changes to each user's point balance over time by taking timestamped snapshots of their balance, and saving it into a new table. It's easy to take the snapshot: insert into balances (userid, points) select userid, points from users; This quickly takes the points field from my users table, and saves it into a balances table, which saves the data along with a timestamp. I can run that query on a regular basis without overly taxing my system. The first time its run, I get 500,000 rows of data. That's fine. But the next time I run a query, I only want to save the differences in balance. Anyone have any tips? Best, Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Tracking changes in large datasets over time
Hi Rob --- MySQL 5. On Fri, Jul 18, 2008 at 3:01 PM, Rob Wultsch [EMAIL PROTECTED] wrote: On Fri, Jul 18, 2008 at 2:00 PM, Jason Yergeau [EMAIL PROTECTED] wrote: I'm having trouble working through a data problem. Any tips or keywords that might clue me into a known pattern would be incredibly appreciated! I have about 500,000 users in my system. Each user has a points balance, updated by a system over which I have no control. I'd like to track changes to each user's point balance over time by taking timestamped snapshots of their balance, and saving it into a new table. It's easy to take the snapshot: insert into balances (userid, points) select userid, points from users; This quickly takes the points field from my users table, and saves it into a balances table, which saves the data along with a timestamp. I can run that query on a regular basis without overly taxing my system. The first time its run, I get 500,000 rows of data. That's fine. But the next time I run a query, I only want to save the differences in balance. Anyone have any tips? Best, Jason What version of mysql do you need this to work with? -- Rob Wultsch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Selecting my data first
It looks like you can use multiple order by's... SELECT * FROM files ORDER BY owner_id, file_id or something like that... The documentation is really good :) On May 12, 2008, at 2:36 PM, Jake Conk wrote: Hello, I have a table with 2 columns, file_id and owner_id. I want to select all the files and order by file_id but I want the ones that belong to me to show up first then everyone elses. Is this possible and how? This is what I'm trying to accomplish: SELECT * FROM whiles WHERE owner_id=my_id first THEN SELECT * FROM files ORDER by file_id I would suspect this can be accomplished by a sub query somehow but I don't know how. Thanks, - Jake -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem attempting to use load data into
Hi Everyone, I am attempting to use this command: load data infile '/volumes/raider/ elks.test.txt' into table elksCurrent fields terminated by '\t' lines terminated by '\n'; My table is created as such: | elksCurrent | CREATE TABLE `elksCurrent` ( `FName` varchar(40) default NULL, `LName` varchar(40) default NULL, `Add1` varchar(50) default NULL, `Add2` varchar(50) default NULL, `City` varchar(50) default NULL, `State` varchar(20) default NULL, `Zip` varchar(14) default NULL, `XCode` varchar(50) default NULL, `Reason` varchar(20) default NULL, `Record` mediumint(11) NOT NULL auto_increment, PRIMARY KEY (`Record`) ) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 | The error that I'm getting is: | Level | Code | Message| +-+-- ++ | Warning | 1366 | Incorrect integer value: 'Record' for column 'Record' at row 1 The row it's choking on is this: FIRST NAME LAST NAME ALT ADD ADD CITYST ZIP XCODE Reason Record First Name Last Name 123 Main St Holland MI 49424 1 \t \t \n (Yes I did change the name to protect the innocent! But all data is the correct type in each row) Any Ideas? -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED]
Re: Problem attempting to use load data into
On Apr 14, 2008, at 2:01 PM, Rob Wultsch wrote: On Mon, Apr 14, 2008 at 10:47 AM, Rob Wultsch [EMAIL PROTECTED] wrote: It is probably trying to insert a string of no length into the not null field. Try it with: SET SQL_MODE = ''; Above should read into an int field, while the server is in strict mode. Hi Rob, Where would I set that? I tried to add it to the load data infile line and it didn't like that... Should I try it before I do the indata? -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem attempting to use load data into
Hi again everyone, After taking the advice of someone offlist I tried the IGNORE 1 LINES and that didn't help... Same result. I've tried a tab delimited file, and a comma separated file. Same result with both. Any other ideas? :) On Apr 14, 2008, at 1:29 PM, Jason Pruim wrote: Hi Everyone, I am attempting to use this command: load data infile '/volumes/ raider/elks.test.txt' into table elksCurrent fields terminated by '\t' lines terminated by '\n'; My table is created as such: | elksCurrent | CREATE TABLE `elksCurrent` ( `FName` varchar(40) default NULL, `LName` varchar(40) default NULL, `Add1` varchar(50) default NULL, `Add2` varchar(50) default NULL, `City` varchar(50) default NULL, `State` varchar(20) default NULL, `Zip` varchar(14) default NULL, `XCode` varchar(50) default NULL, `Reason` varchar(20) default NULL, `Record` mediumint(11) NOT NULL auto_increment, PRIMARY KEY (`Record`) ) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 | The error that I'm getting is: | Level | Code | Message| +-+-- ++ | Warning | 1366 | Incorrect integer value: 'Record' for column 'Record' at row 1 The row it's choking on is this: FIRST NAME LAST NAME ALT ADD ADD CITYST ZIP XCODE Reason Record First Name Last Name 123 Main St Holland MI 49424 1 \t \t \n (Yes I did change the name to protect the innocent! But all data is the correct type in each row) Any Ideas? -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem attempting to use load data into
On Apr 14, 2008, at 3:29 PM, Daniel Brown wrote: On Mon, Apr 14, 2008 at 1:29 PM, Jason Pruim [EMAIL PROTECTED] wrote: Hi Everyone, I am attempting to use this command: load data infile '/volumes/raider/elks.test.txt' into table elksCurrent fields terminated by '\t' lines terminated by '\n'; [snip!] The error that I'm getting is: | Level | Code | Message | +-+-- ++ | Warning | 1366 | Incorrect integer value: 'Record' for column 'Record' at row 1 That's because it's attempting to insert the name of the columns from your CSV into MySQL --- and 'Record' is not a valid INT. Replaced field name with 0 and had the same end result... Just no error. But I get the first row included! Which is just field names and a 0 for good measure :) Any other ideas Master Brown? :) ***Before I get yelled at for not showing respect please note that I know Dan from another list and I am allowed to give him crap like this no matter what he says :P -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem attempting to use load data into
On Apr 14, 2008, at 4:37 PM, Daniel Brown wrote: On Mon, Apr 14, 2008 at 3:45 PM, Daniel Brown [EMAIL PROTECTED] wrote: Does your file actually have the characters \t \t \n at the end of each row like that? Send it to me as an attachment off-list and I'll help you figure it out and then post back here for the MySQL archives. Sorry, got sidetracked with the day job and the pre-wife nagging me. ;-P Anyway, as I suspected, you did have literal \t and \n characters. I wrote a script to fix it, and I'll link you to the updated CSV file. Run that with the IGNORE 1 ROWS command and you should be set. Just to complete the archives, This did fix it. Make sure you don't try and put literal tab values \t and new line values \n into your data and it should work just fine! So thank you Dan for your help! And everyone else as well! -- /Daniel P. Brown Ask me about: Dedicated servers starting @ $59.99/mo., VPS starting @ $19.99/mo., and shared hosting starting @ $2.50/mo. Unmanaged, managed, and fully-managed! -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: relational tables
See below... -Original Message- From: John Taylor-Johnston [mailto:John.Taylor- [EMAIL PROTECTED] Sent: Thursday, March 20, 2008 2:17 PM To: Sebastian Mendel; mysql@lists.mysql.com Subject: Re: relational tables DROP TABLE IF EXISTS `person`; CREATE TABLE `person` ( `person_id` int(11) NOT NULL auto_increment, `name` varchar(255) default NULL, `email` varchar(255) default NULL, PRIMARY KEY (`person_id`), KEY `email` (`email`), KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; INSERT INTO `person`( `person_id`, `name`, `email` ) VALUES ( 1, 'Name', '[EMAIL PROTECTED]' ) ; INSERT INTO `person`( `person_id`, `name`, `email` ) VALUES ( 2, 'second Name', '[EMAIL PROTECTED]' ) ; DROP TABLE IF EXISTS `shopping`; CREATE TABLE IF NOT EXISTS `shopping` ( `shopping_id` int(11) NOT NULL, `email` varchar(255) default NULL, `name` varchar(255) default NULL, PRIMARY KEY (`shopping_id`), UNIQUE KEY `email` (`email`), UNIQUE KEY `name` (`name`), FOREIGN KEY (`email`) REFERENCES `person` (`email`), FOREIGN KEY (`name`) REFERENCES `person` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Something doesn't smell right with the 'shopping' table. Rather than using what appears to be the same values (aside from the person_id as opposed to the shopping_id), wouldn't it be more effective to have the shopping table have 'shopping_id' and 'person_id' fields? With that approach, you could get away from having the same data in two tables. But, you'll have to start building ways to look up the person_id based on name and email values in order to populate the shopping table. Took a flyer at an updated version of the above (not knowing what your intent is): DROP TABLE IF EXISTS `person`; CREATE TABLE `person` ( `person_id` int(11) NOT NULL auto_increment, `name` varchar(255) default NULL, `email` varchar(255) default NULL, PRIMARY KEY (`person_id`), KEY `email` (`email`), KEY `name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; INSERT INTO `person`( `person_id`, `name`, `email` ) VALUES ( null, 'Name', '[EMAIL PROTECTED]' ) ; -- updated the key value to allow for being created automatically INSERT INTO `person`( `person_id`, `name`, `email` ) VALUES ( null, 'second Name', '[EMAIL PROTECTED]' ) ; -- same thing here DROP TABLE IF EXISTS `shopping`; CREATE TABLE IF NOT EXISTS `shopping` ( `shopping_id` int(11) NOT NULL, `person_id` int(11) NOT NULL, PRIMARY KEY (`shopping_id`), FOREIGN KEY (`person_id`) REFERENCES `person` (`person_id`), ) ENGINE=InnoDB DEFAULT CHARSET=latin1; The syntax of the above might not be 100%...but it looks to be close enough. No virus found in this outgoing message. Checked by AVG. Version: 7.5.519 / Virus Database: 269.21.7/1335 - Release Date: 3/19/2008 9:54 AM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question about reading info from another table.
Hi Everyone, I am attempting to write a PHP application that reads info from a MySQL database, and I'm wondering if I can set up a column in one table that gets it's info from a field in another table automatically? Ie: Table1: field1 field2 field3 Table2: field4 field5 field6 = field1 Does that make sense? Would that be a join? Or maybe a primary key? I'm new to MySQL programming so RTFM's are appreciated as long as M is defined :) -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about reading info from another table.
On Feb 28, 2008, at 1:29 PM, Rob Wultsch wrote: On Thu, Feb 28, 2008 at 10:59 AM, Jason Pruim [EMAIL PROTECTED] wrote: Hi Everyone, I am attempting to write a PHP application that reads info from a MySQL database, and I'm wondering if I can set up a column in one table that gets it's info from a field in another table automatically? Ie: Table1: field1 field2 field3 Table2: field4 field5 field6 = field1 Does that make sense? Would that be a join? Or maybe a primary key? I'm new to MySQL programming so RTFM's are appreciated as long as M is defined :) -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] Tip for future questions: Figure out the simplest way to present the question and include the SQL to create the relevant tables. Next explain what you want, any non working sql you have, and lastly give an example result of correct output. Hi Rob, I will do this in the future, thank you. And to that end: CREATE TABLE `current` ( `customerName` varchar(30) default NULL, `customerBusiness` varchar(30) default NULL, `loginName` varchar(30) default NULL, `loginPassword` varchar(32) default NULL, `tableName` varchar(20) default NULL, `email` varchar(50) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 CREATE TABLE `adminAll` ( `dispalyTableName` varchar(20) default NULL, `adminLevel` int(10) default NULL, `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP ) ENGINE=MyISAM DEFAULT CHARSET=latin1 What I want, is displayTableName on table adminAll to grab it's info from tableName in current. Does that make more sense? Example: So lets say I have two tables: CREATE TABLE `t1` ( `t1_id` int(10) NOT NULL auto_increment, `t1_data` varchar(255) NOT NULL default '', `t2_id` int(10) NOT NULL default '0', PRIMARY KEY (`t1_id`), KEY `t2_id` (`t2_id`) ); CREATE TABLE `t2` ( `t2_id` int(10) NOT NULL auto_increment, `t2_data` varchar(255) NOT NULL default '', PRIMARY KEY (`t2_id`) ); I want to show all the information in t1 and any information in t2 where the t1.t2_id is equal to t2.t2_id. Output should be like: t1_id, t1_data, t2_data *Answer* I really am not sure what you were asking, but take a look at this query for the table structure above. SELECT t1_id, t1_data, t2_data FROM t1 INNER JOIN t2 USING(t2_id) *Better answer* Go buy an introductory book on sql. Read through a couple examples. ( http://www.w3schools.com/sql/default.asp is also very good) I have been working with MySQL in various degrees for the past few years, I've just never needed to grab info from another table and import it to a different table. My Main area of expertise is in web design (mostly HTML and CSS) and some PHP. From the above question you probably do not know enough to tread water in the very excellent MySQL manual. From my original post: Does that make sense? Would that be a join? Or maybe a primary key? I'm new to MySQL programming so RTFM's are appreciated as long as M is defined :) -- Rob Wultsch -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Question about reading info from another table.
On Feb 28, 2008, at 2:06 PM, Rob Wultsch wrote: What you are probably wanting is a join, but how does adminAll relate to current? adminAll will be for the administrators of my program to log into so instead of getting redirected automatically to a certain table (current.tableName in this case) they get a list of available tables in the database (adminAll.displayTableName) so that I don't have to have a separate admin login for each database I setup with my program. Generally it is a good idea to have the column that relates the tables (read http://en.wikipedia.org/wiki/Foreign_key if you want to work at a much high level ) have the same column name if possible (IMHO). the column name won't be an issue since I'm writing it all from scratch :) making it the same name to help improve readability between the tables in the database I'm assuming? In the example I sent I had a column in both tables name t2_id. Take a look at http://www.w3schools.com/sql/sql_join.asp for simple joins. The syntax in the first couple examples (without using JOIN) is a good idea to avoid (also IMHO) . I will look at those as soon as I'm done sending this e-mail! Thank you The new table you sent is good, but it is more ideal to remove unnecessary columns and make the table /column names generic. Can I ask why? So far, everything I have done with MySQL would seem to suggest setting column names so it makes sense what info is stored in it? (IE: First Name would go into FName or firstname or namefirst or something like that) On Thu, Feb 28, 2008 at 11:49 AM, Jason Pruim [EMAIL PROTECTED] wrote: On Feb 28, 2008, at 1:29 PM, Rob Wultsch wrote: On Thu, Feb 28, 2008 at 10:59 AM, Jason Pruim [EMAIL PROTECTED] wrote: Hi Everyone, I am attempting to write a PHP application that reads info from a MySQL database, and I'm wondering if I can set up a column in one table that gets it's info from a field in another table automatically? Ie: Table1: field1 field2 field3 Table2: field4 field5 field6 = field1 Does that make sense? Would that be a join? Or maybe a primary key? I'm new to MySQL programming so RTFM's are appreciated as long as M is defined :) -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] Tip for future questions: Figure out the simplest way to present the question and include the SQL to create the relevant tables. Next explain what you want, any non working sql you have, and lastly give an example result of correct output. Hi Rob, I will do this in the future, thank you. And to that end: CREATE TABLE `current` ( `customerName` varchar(30) default NULL, `customerBusiness` varchar(30) default NULL, `loginName` varchar(30) default NULL, `loginPassword` varchar(32) default NULL, `tableName` varchar(20) default NULL, `email` varchar(50) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 CREATE TABLE `adminAll` ( `dispalyTableName` varchar(20) default NULL, `adminLevel` int(10) default NULL, `date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP ) ENGINE=MyISAM DEFAULT CHARSET=latin1 What I want, is displayTableName on table adminAll to grab it's info from tableName in current. Does that make more sense? Example: So lets say I have two tables: CREATE TABLE `t1` ( `t1_id` int(10) NOT NULL auto_increment, `t1_data` varchar(255) NOT NULL default '', `t2_id` int(10) NOT NULL default '0', PRIMARY KEY (`t1_id`), KEY `t2_id` (`t2_id`) ); CREATE TABLE `t2` ( `t2_id` int(10) NOT NULL auto_increment, `t2_data` varchar(255) NOT NULL default '', PRIMARY KEY (`t2_id`) ); I want to show all the information in t1 and any information in t2 where the t1.t2_id is equal to t2.t2_id. Output should be like: t1_id, t1_data, t2_data *Answer* I really am not sure what you were asking, but take a look at this query for the table structure above. SELECT t1_id, t1_data, t2_data FROM t1 INNER JOIN t2 USING(t2_id) *Better answer* Go buy an introductory book on sql. Read through a couple examples. ( http://www.w3schools.com/sql/default.asp is also very good) I have been working with MySQL in various degrees for the past few years, I've just never needed to grab info from another table and import it to a different table. My Main area of expertise is in web design (mostly HTML and CSS) and some PHP. From the above question you probably do not know enough to tread water in the very excellent MySQL manual. From my original post: Does that make sense? Would that be a join? Or maybe a primary key? I'm new to MySQL programming so RTFM's are appreciated as long as M is defined :) -- Rob Wultsch -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424-9337 www.raoset.com [EMAIL PROTECTED] -- Rob Wultsch (480)223-2566 [EMAIL PROTECTED] (email/google im) wultsch (aim) [EMAIL PROTECTED] (msn
Re: import from exel into mysql
Hi Hiep, Here's what I do when I need to do that. First save it as a .csv file and then follow this: load data infile '/path/to/file' into table MyTable fields terminated by '\t' lines terminated by 'w' that has always worked for me. Hope it helps! On Jan 14, 2008, at 10:51 AM, Hiep Nguyen wrote: hi everyone, i have a large ms excel data (text) file that i need to import to my table in mysql. does any one have a suggestion how to do this? i'm try to export to csv file, then import to my table, but i have so much problems with delimeters thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Red Hat EL and Datbase Setup
Hi, I am looking for a little advice in setting up Red Hat and MySQL for a large database (at least I consider it to be large). My database will contain 2 large tables that are updated daily. The first table currenly has 19 million records and ~70 columns largely made up of varchar(20), char(5) and integers. It has a natural primary key and a composite index on 3 of the character columns. The second table currently has 400 million records and ~30 columns again made up of varchar(20), char(5) and integers. This table's primary key is defined using 2 columns and also has a composite index on the same 3 columns as the first table. Lastly, I will frequently join the two tables in my queries. My system has 4gb ram, 500 gb hard drive (result of a RAID 5 configuration) and 2 dual core Intel 64 bit procs. I have chosen to use Red Hat EL5. Here are the questions I have to help optimize the performance: * Should I continue with the RAID 5? I am not too concerned of recovery. I am more concerned about I/O performance. * Is there a hard drive partition scheme that would help the performance (separate the large db schema /var/lib/mysql/schema_name)? * Should I partition the tables? There is a natural partition for the 400m table by date; there is not a natural partition for the other. Should I make one up? * Are there specific additions to the /etc/my.cnf that I should add to maximize the systems capabilities? * Please let me know of other things I should consider. Thanks in advance, Jason
Re: Query help, please..
On Dec 11, 2007, at 10:46 AM, Rob Wultsch wrote: On Dec 11, 2007 8:38 AM, Anders Norrbring [EMAIL PROTECTED] wrote: I'm looking at a situation I haven't run into before, and I'm a bit puzzled by it. I have this table structure: Table USERS: userid, class Table OBJECT: userid, class, result Now I want to query the database for a certain user's result in a specified class, which is very, very easy. No problems. But, I also want to find out the user's position relative to others depending on the result. So, if the specified user's result is the 9:th best of all of the users, I want to have a reply from the DB query that say he has position number 9. I really can't figure out how to do that... Somehow I have to make MySQL calculate the position based on the value in the result column. Take a look at http://arjen-lentz.livejournal.com/55083.html . Very similar ideas in play, though you also have a join. The basic idea is that you do a count on the number of users that have a lower score. Is there any reason you wouldn't want to count the people in front of you and add 1 to get your place in line? It seems like depending on where you are, that may be a shorter number to count :) But I don't know anything about how to do stuff off of separate tables yet still trying to grasp that :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
I'm actually planning the application first instead of coding first!!! :)
Hi Everyone, So having learned my lesson with the last application, I am trying to plan out the addition of a feature to my database application. Basically, some of my customers go south for the winter (Snow Birds) what I would like to do is have away of storing both their addresses in the database, and have it so that the people administering the list can choose between wether they are up north or down south without having to erase the old address. For that I was thinking creating a second table SnowBirds and list their southern addresses in there and then when the list admin clicks on the edit button for their name, it would also be able to pull up a list of the the addresses stored and associated with that person. I'm also considering adding a date range for the addresses so that if they know they'll be south from November to March it will check the date and switch between the record accordingly BEFORE exporting to excel. Now... I haven't really asked a question yet but gave some background into what I want to do. So... Here's the question, does anyone have any advice on the best way to do it? Am I right in thinking that a second table is required? Would it be called a Relational database? Or have I missed the terminology? Any help would be greatly appreciated! Thanks for looking! ohhh... and in case it makes a difference it's MySQL 5.* and I'll be writing the stuff to access that database with php 5. -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED]
privileges
Hi everyone, Just getting into database stuff a little bit, and wanted to double check something with you guys. I have a database, which has 2 tables, I have created a user using this syntax: grant select, update, delete on dbname.table to 'me'@'localhost' identified by 'mypass'; then I also added access to another table: grant select on dbname.othertable to 'me'@'localhost' identified by 'mypass'; the other table is used to store local accounts to a online database, and my php script uses SELECT to check to see if they are listed and if so grant them access. No one is accessing the database directly except for me, and I would like to keep it that way. Given the user that I created, is there anything else I need to worry about? I only want local requests to be able to interface with the database. I don't need Joe Blow for indiana to have direct access :) Oh, and do I specifically have to disallow certain privileges if all I want them to be able to do is delete, select or update records? -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED]
Reset a auto increment field?
Is there away to reset an auto incrementing field count? I have a database that currently has 935 records in it but because I have deleted a few the current number used for NEW records is 938 :) How can I get it to count the records and assign a record number based on the total count? Hope that makes sense! Thanks for looking! :) -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED]
Re: Reset a auto increment field?
If I understand you correctly, if my table is MyISAM, after I did a delete query I could just: ALTER TABLE t2 AUTO_INCREMENT=1; and that would cause the auto increment value to be set to 901 (Assuming 900 total current records) on the next insert? On Aug 29, 2007, at 1:48 PM, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: To change the value of the AUTO_INCREMENT counter to be used for new rows, do this: ALTER TABLE t2 AUTO_INCREMENT = value; You cannot reset the counter to a value less than or equal to any that have already been used. For MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT column, the value is reset to the current maximum plus one. For InnoDB, you can use ALTER TABLE ... AUTO_INCREMENT = value as of MySQL 5.0.3, but if the value is less than the current maximum value in the column, no error message is given and the current sequence value is not changed. Ed -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 29, 2007 11:02 AM To: Jason Pruim Cc: MySQL List Subject: Re: Reset a auto increment field? Is there away to reset an auto incrementing field count? I have a database that currently has 935 records in it but because I have deleted a few the current number used for NEW records is 938 :) How can I get it to count the records and assign a record number based on the total count? Hope that makes sense! Thanks for looking! :) -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] AFAIK, you need to drop and then recreate the auto-increment field, otherwise you'll get holes when you delete a record. David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reset a auto increment field?
On Aug 29, 2007, at 2:30 PM, Shawn Green wrote: Hi Jason, Jason Pruim wrote: Is there away to reset an auto incrementing field count? I have a database that currently has 935 records in it but because I have deleted a few the current number used for NEW records is 938 :) How can I get it to count the records and assign a record number based on the total count? Hope that makes sense! Thanks for looking! :) Actually, it doesn't make sense and for the very reason you are trying to use it. At some point in history you had a record # 936. Because that record once existed, there may have been one or several things associated with it. Imagine the confusion that would ensue if the Social Security administration recycled an already issued number just as soon as the person using it died. The safest thing to do is to pretend that the auto-incrementing field is an internal, non-editable field. Should you have gaps in your auto-inc values treat them as normal conditions of having an active database. For another instance, assume that you are auto-incrementing the serial numbers to various items in an inventory control system. If an item is destroyed or taken out of use, you probably want to move that record from an activeitems table to some other location. Would you want to re-issue those numbers to newly purchased items just to fill in the gaps in the activeitems table? Of course not. Now, with the understanding that doing this on a regular basis would be wrong, here is how to do it anyway: Use the auto_increment= option to an ALTER TABLE statement like this ALTER TABLE mydata AUTO_INCREMENT=936; (alter table) http://dev.mysql.com/doc/refman/5.0/en/alter-table.html (for the definition of table option) http://dev.mysql.com/doc/refman/5.0/en/create-table.html I see what you are getting at with this, and have decided that mucking around with auto incrementing values doesn't exactly fit in with the way databases were designed to work. Somehow though, I still need to supply this whether I end up adding a Record number field in the database, and then through php (The way the database is going to be accessed) assigned a record number to that field based on the total rows, and display that number rather then the internal record number. This is getting complicated :) -- Shawn Green, Support Engineer MySQL Inc., USA, www.mysql.com Office: Blountville, TN __ ___ ___ __ / |/ /_ __/ __/ __ \/ / / /|_/ / // /\ \/ /_/ / /__ /_/ /_/\_, /___/\___\_\___/ ___/ Join the Quality Contribution Program Today! http://dev.mysql.com/qualitycontribution.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database architecture and security
Hi Everyone, Just had a quick question about a database I'm working on. I am planning on having the database open to customers of mine to store their mailing addresses on-line, and be able to manage the records. Is it safe, to have 1 database with lots of tables? Or am I safer setting up separate databases for everyone? I should mention, no one will be accessing the database directly, it'll be through a web interface and php to display it. Any info would be greatly appreciated! -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED]
Re: Database architecture and security
On Aug 23, 2007, at 11:44 AM, Gary Josack wrote:
I'd never have a separate database for everyone or even a separate
table for everyone. Here's a rough idea of how I'd do it
mysql CREATE TABLE customer (
- `custid` INT NOT NULL AUTO_INCREMENT,
- `lastname` VARCHAR(25) not null,
- `firstname` VARCHAR(25) NOT NULL,
- PRIMARY KEY(custid)
- );
Query OK, 0 rows affected (0.03 sec)
mysql CREATE TABLE address (
- `addressid` INT NOT NULL AUTO_INCREMENT,
- `custid` INT NOT NULL,
- `address` VARCHAR(100) NOT NULL,
- `city` VARCHAR(50),
- `state` CHAR(2) NOT NULL,
- `zip` MEDIUMINT(5) NOT NULL,
- PRIMARY KEY(addressid)
- );
Query OK, 0 rows affected (0.00 sec)
mysql INSERT INTO customer (lastname, firstname) VALUES ('Bolton',
'Mike'), ('Vader', 'Darth');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql SELECT * FROM customer;
++--+---+
| custid | lastname | firstname |
++--+---+
| 1 | Bolton | Mike |
| 2 | Vader| Darth |
++--+---+
2 rows in set (0.00 sec)
mysql INSERT INTO address (custid, address, city, state, zip) VALUES
- (1, '123 house drive.', 'Davie', 'FL', 33314),
- (1, '54325 awesome way', 'Sunrise', 'FL', 33521),
- (2, 'The Death Star', 'SPACE', 'NA', 6);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql SELECT * FROM address;
+---++---+-+---+---+
| addressid | custid | address | city| state | zip |
+---++---+-+---+---+
| 1 | 1 | 123 house drive. | Davie | FL| 33314 |
| 2 | 1 | 54325 awesome way | Sunrise | FL| 33521 |
| 3 | 2 | The Death Star| SPACE | NA| 6 |
+---++---+-+---+---+
3 rows in set (0.00 sec)
mysql SELECT lastname, firstname, address, city, state, zip FROM
customer JOIN address USING (custid);
+--+---+---+-+---+---+
| lastname | firstname | address | city| state | zip |
+--+---+---+-+---+---+
| Bolton | Mike | 123 house drive. | Davie | FL| 33314 |
| Bolton | Mike | 54325 awesome way | Sunrise | FL| 33521 |
| Vader| Darth | The Death Star| SPACE | NA| 6 |
+--+---+---+-+---+---+
3 rows in set (0.01 sec)
mysql SELECT address, city, state, zip FROM customer JOIN address
USING (custid) WHERE (lastname, firstname) = ('Bolton', 'Mike');
+---+-+---+---+
| address | city| state | zip |
+---+-+---+---+
| 123 house drive. | Davie | FL| 33314 |
| 54325 awesome way | Sunrise | FL| 33521 |
+---+-+---+---+
Now each customer/person can have multiple addresses listed.
I really like the idea of being able to have multiple addresses, some
of our customers right now have lots of seasonal addresses... But
that's a little bit out of my comfort zone right now... I'll add it
to the feature list though and keep your e-mail to reference :)
Thanks! :)
--
Jason Pruim
Raoset Inc.
Technology Manager
MQC Specialist
3251 132nd ave
Holland, MI, 49424
www.raoset.com
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database architecture and security
On Aug 23, 2007, at 11:28 AM, Rolando Edwards wrote: Think about how your going to make backups. 1) Would you backup one database with all the mailing lists together ? If I went the route of 1 database, Many tables, I would just backup the entire database and all the tables in one shot. Unless Im misunderstanding how MySQL handles the backups, I would think that it would preserve the individual tables? 2) Would you keep the backups of each user separate ? I probably should, but hadn't thought that far ahead yet. Still working on getting the PHP Scripts and everything else set. 3) Could users ask you to restore mailing lists from the past ? The only reason I could see them asking for that is if they went through and deleted the entire database, which has to be done manually, meaning you have to hit delete on each record to delete it. I would hope they would realize what they were doing before going through the entire database... You could make one mysqldump for everybody from one database if no one ever asks for restoration of past mailing lists. You could create a database for each user. Then, backup (mysqldump) each database for each user. Should they request a restore, it's becomes an easy thing. How you answer the three questions I posed should help you decide. I am starting to lean towards the multiple databases with 1 table even though it makes more files on my server, I think it gives me the most flexibility/security. That way I can also edit 1 database and not screw it up for the entire user base, just the one user :) - Original Message - From: Jason Pruim [EMAIL PROTECTED] To: MySQL List mysql@lists.mysql.com Sent: Thursday, August 23, 2007 10:58:52 AM (GMT-0500) America/ New_York Subject: Database architecture and security Hi Everyone, Just had a quick question about a database I'm working on. I am planning on having the database open to customers of mine to store their mailing addresses on-line, and be able to manage the records. Is it safe, to have 1 database with lots of tables? Or am I safer setting up separate databases for everyone? I should mention, no one will be accessing the database directly, it'll be through a web interface and php to display it. Any info would be greatly appreciated! -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- Rolando A. Edwards MySQL DBA SWMX, Inc. 1 Bridge Street Irvington, NY 10533 (914) 406-8406 (Main) (201) 660-3221 (Mobile) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database architecture and security
On Aug 23, 2007, at 11:50 AM, David T. Ashley wrote: On 8/23/07, Jason Pruim [EMAIL PROTECTED] wrote: I am planning on having the database open to customers of mine to store their mailing addresses on-line, and be able to manage the records. Is it safe, to have 1 database with lots of tables? Or am I safer setting up separate databases for everyone? I should mention, no one will be accessing the database directly, it'll be through a web interface and php to display it. Assuming that the web server runs on the same box as the MySQL daemon ... you want to firewall the server so that nobody can connect to the MySQL daemon directly from outside the box. It is also a bad idea to allow the users to have shell accounts on that box unless you have taken additional security precautions (specifically, being sure the MySQL userid/ password you're using are secure from all but the web server UID/GID, and that no other userid/passwords have access to the database you're using). Once that is done, all access to the database is controlled by the PHP scripts, and there is no security advantage to having multiple databases. I'm assuming that users have to log in individually (jsmith, bjones, etc.) and that the PHP scripts then carefully control what each user is allowed to modify. I'm also going to assume that you've handled all the obvious technology issues, such as: a)Database transactions/atomic actions. b)Terminating TCP connections and ensuring that each PHP script runs to completion, anyway, and that the database isn't left in an indeterminate state due to this. Dave. The server is currently firewalled to block all but the necessary ports from outside the local network. No user, other then myself, and a few admins on the server will have shell access... The MySQL userid/password will be changed once I go live with it, or get into the final testing. What do you mean by b? If all the connections come from the local box how could I configure that to make sure it's all set up so it won't leave the database all messed up? -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: date query
On Aug 14, 2007, at 8:38 AM, Christian High wrote: On 8/14/07, Baron Schwartz [EMAIL PROTECTED] wrote: Hi, Christian High wrote: I have a table that includes a date and a scale reading like datescale_reading 2007-08-01 150 2007-08-02 125 these reading may or may not be taken everyday. I need to develop a query that will subtract the scale reading on one day from the scale reading on the next most recent reading. any ideas? This may explain what you're looking for: http://www.xaprb.com/blog/2007/02/19/how-to-subtract-in-sql-over- samples-that-wrap/ Baron Baron, Very nice article that I no doubt can make use of. But in this instance, unless I am missing it, it doesn't help. My problem seems to be that the data is not necessarily entered in order. For example the users may enter July 7th data then enter July 5th data. I can get around this by ordering on the date. The problem that I cannot seem to get around is they may not have any data for a particular date. when I tried test the suggestions in the article against my data if there was a hole in the date column, which is what i joined on because it needs to subtract one day from the next most recent, it excluded these instances because it did not meet the join criteria. t1 is a select * view ordered by date on the above mentioned table select t1.date_column, t1.reading_column, p.date_column, p.reading_column join t1 p on (t1.date_column = p.date_column + 1). If I am missing something I would appreciate it if someone could point it out. I think what I need is a way to find the most recent date as commpared with a given date whether that date is the day before or 3 days before. Thanks, cj Hi Christian, I don't know if you are in control of the data, But would it be possible to add a column to the database something like Read and have the value either 1 or 0? then do something like: Select * from view ordered by date where Read=1;?That way you wouldn't have any gaps for fields and could then just do the math fairly easily I think... But I'm just starting out with MySQL so I may have made a huge mistake :) In fact... It's quite probable :) -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import file into MySQL Database..
On Aug 8, 2007, at 5:19 PM, Gary Josack wrote: Try: |load data local infile '|/volumes/raider/aml.master.8.6.07.|csv' into table test fields terminated by ',' enclosed by '' lines terminated by '\n' |ignore 1 lines |(First, Last, Add1, Add2, City, State, Zip, XCode, Reason, Date); If that doesn't work could you please provide more output from the csv file in question? | The current load file command that I've been trying is: load data local infile /volumes/raider/aml.master.dos.csv into table test fields terminated by , lines terminated by (First, Last, Add1, Add2, City, State, Zip, XCode, Reason, Date); I tried adding \r\n to the file and it didn't work, here is some of the lines from my csv file: First,Last,Add2,Add1,City,State,Zip,Date,Xcode,Reason A. DREW,MILES,,1583 Jerome St,Holland,MI,49423-6703,,P, Aaron,Davis,,14796 Creek Edge Dr,Holland,MI,49424,05/17/07,S, Aaron,Kraai,,3710 88th Ave,Zeeland,MI,49464-9716,05/17/04,S, Aaron,Shuck,,288 W 13th St,Holland,MI,49423-3452,04/28/03,, Adam,Hecht,,177 Cambridge Ave,Holland,MI,49423,03/22/05,S, ADE,VLIENSTRA,,304 W 30th St,Holland,MI,49423-6971,,, Adrienne,Smith,,6795 Ransom Rd,Zeeland,MI,49464,03/31/06,A, Anthony,Lewis,,10257 Summerwood Dr,Zeeland,MI,49464,07/30/07,S,Typo AL,HOLTROP,,4850 Dellview Ct,Hudsonville,MI,49426-1664,,, Alan,Russell,,3624 Diamond Dr,Hamilton,MI,49419-9707,,, If anyone wants to see the result that I get goto: http://raoset.com/ tests/legion/index.php Any help is greatly appreciated! Thanks! -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import file into MySQL Database..
On Aug 9, 2007, at 10:11 AM, Edward Kay wrote: -Original Message- From: Jason Pruim [mailto:[EMAIL PROTECTED] Sent: 09 August 2007 14:16 To: Gary Josack Cc: mysql@lists.mysql.com Subject: Re: Import file into MySQL Database.. On Aug 8, 2007, at 5:19 PM, Gary Josack wrote: Try: |load data local infile '|/volumes/raider/aml.master.8.6.07.|csv' into table test fields terminated by ',' enclosed by '' lines terminated by '\n' |ignore 1 lines |(First, Last, Add1, Add2, City, State, Zip, XCode, Reason, Date); If that doesn't work could you please provide more output from the csv file in question? | The current load file command that I've been trying is: load data local infile /volumes/raider/aml.master.dos.csv into table test fields terminated by , lines terminated by (First, Last, Add1, Add2, City, State, Zip, XCode, Reason, Date); I tried adding \r\n to the file and it didn't work, here is some of the lines from my csv file: First,Last,Add2,Add1,City,State,Zip,Date,Xcode,Reason A. DREW,MILES,,1583 Jerome St,Holland,MI,49423-6703,,P, Aaron,Davis,,14796 Creek Edge Dr,Holland,MI,49424,05/17/07,S, Aaron,Kraai,,3710 88th Ave,Zeeland,MI,49464-9716,05/17/04,S, Aaron,Shuck,,288 W 13th St,Holland,MI,49423-3452,04/28/03,, Adam,Hecht,,177 Cambridge Ave,Holland,MI,49423,03/22/05,S, ADE,VLIENSTRA,,304 W 30th St,Holland,MI,49423-6971,,, Adrienne,Smith,,6795 Ransom Rd,Zeeland,MI,49464,03/31/06,A, Anthony,Lewis,,10257 Summerwood Dr,Zeeland,MI,49464,07/30/07,S,Typo AL,HOLTROP,,4850 Dellview Ct,Hudsonville,MI,49426-1664,,, Alan,Russell,,3624 Diamond Dr,Hamilton,MI,49419-9707,,, If anyone wants to see the result that I get goto: http://raoset.com/ tests/legion/index.php Any help is greatly appreciated! Thanks! First off, to me it looks like your data is in the format ... state,zip,date,xcode,reason but your field list is ... state,zip,xcode,reason,date I have a cron job that updates one of my tables with a CSV file. This uses the following command which always works well: load data infile table.csv into table table_name fields terminated by ',' enclosed by '' lines terminated by '\r\n' starting by '' ignore 1 lines; The CSV file has Windows line endings and is imported by MySql on Linux. You wouldn't need the enclosed by bit for your data though. HTH, Edward I have tried this many different reasons, and all the possibilities I can think of... the only thing I can figure is it's something to do with my actual file... But I have saved it as a tab separated, csv, both dos and windows line endings... and I just get get it to do it reliably. It throws the data all over the place. I'm about to give up and retype the whole thing... All 900+ records of it... Just so that it gets done... Anyone have any other ideas? (Sorry... Just getting frustrated) -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import file into MySQL Database..
The Extra commas at the end of some of the lines need to be there to keep everything in the right order, they represent empty fields... Now, I went through on a few of them and added \n to the end of the line, then tried to load the file again with the LINES TERMINATED BY \n and it looks like it went just fine for the ones I did that too... So now I need to add that to the rest... I could have sworn I had done this before and it didn't work though On Aug 9, 2007, at 11:22 AM, Jerry Schwartz wrote: Could the commas at the end of your data lines be causing a problem? (I've never loaded a CSV file.) Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -Original Message- From: Jason Pruim [mailto:[EMAIL PROTECTED] Sent: Thursday, August 09, 2007 10:54 AM To: Edward Kay Cc: mysql@lists.mysql.com Subject: Re: Import file into MySQL Database.. On Aug 9, 2007, at 10:11 AM, Edward Kay wrote: -Original Message- From: Jason Pruim [mailto:[EMAIL PROTECTED] Sent: 09 August 2007 14:16 To: Gary Josack Cc: mysql@lists.mysql.com Subject: Re: Import file into MySQL Database.. On Aug 8, 2007, at 5:19 PM, Gary Josack wrote: Try: |load data local infile '|/volumes/raider/aml.master.8.6.07.|csv' into table test fields terminated by ',' enclosed by '' lines terminated by '\n' |ignore 1 lines |(First, Last, Add1, Add2, City, State, Zip, XCode, Reason, Date); If that doesn't work could you please provide more output from the csv file in question? | The current load file command that I've been trying is: load data local infile /volumes/raider/aml.master.dos.csv into table test fields terminated by , lines terminated by (First, Last, Add1, Add2, City, State, Zip, XCode, Reason, Date); I tried adding \r\n to the file and it didn't work, here is some of the lines from my csv file: First,Last,Add2,Add1,City,State,Zip,Date,Xcode,Reason A. DREW,MILES,,1583 Jerome St,Holland,MI,49423-6703,,P, Aaron,Davis,,14796 Creek Edge Dr,Holland,MI,49424,05/17/07,S, Aaron,Kraai,,3710 88th Ave,Zeeland,MI,49464-9716,05/17/04,S, Aaron,Shuck,,288 W 13th St,Holland,MI,49423-3452,04/28/03,, Adam,Hecht,,177 Cambridge Ave,Holland,MI,49423,03/22/05,S, ADE,VLIENSTRA,,304 W 30th St,Holland,MI,49423-6971,,, Adrienne,Smith,,6795 Ransom Rd,Zeeland,MI,49464,03/31/06,A, Anthony,Lewis,,10257 Summerwood Dr,Zeeland,MI,49464,07/30/07,S,Typo AL,HOLTROP,,4850 Dellview Ct,Hudsonville,MI,49426-1664,,, Alan,Russell,,3624 Diamond Dr,Hamilton,MI,49419-9707,,, If anyone wants to see the result that I get goto: http://raoset.com/ tests/legion/index.php Any help is greatly appreciated! Thanks! First off, to me it looks like your data is in the format ... state,zip,date,xcode,reason but your field list is ... state,zip,xcode,reason,date I have a cron job that updates one of my tables with a CSV file. This uses the following command which always works well: load data infile table.csv into table table_name fields terminated by ',' enclosed by '' lines terminated by '\r\n' starting by '' ignore 1 lines; The CSV file has Windows line endings and is imported by MySql on Linux. You wouldn't need the enclosed by bit for your data though. HTH, Edward I have tried this many different reasons, and all the possibilities I can think of... the only thing I can figure is it's something to do with my actual file... But I have saved it as a tab separated, csv, both dos and windows line endings... and I just get get it to do it reliably. It throws the data all over the place. I'm about to give up and retype the whole thing... All 900+ records of it... Just so that it gets done... Anyone have any other ideas? (Sorry... Just getting frustrated) -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Import file into MySQL Database..
Okay, so I have been going crazy trying to figure this out... All I want to do is load a excel file (Which I can convert to just about anything) into a MySQL database... Should be easy right? Here is the command that I have tried: LOAD DATA LOCAL INFILE '/ volumes/raider/AML.master.txt' INTO TABLE current FIELDS TERMINATED BY '\t' ENCLOSED BY '' LINES TERMINATED BY '\n'; and here is the error I am getting: | Warning | 1264 | Out of range value adjusted for column 'Record' at row 1 | What do I need to change to get this to work? Or what other info do you need to be able to help me? :) Thanks! -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED]
Re: Import file into MySQL Database..
A... the one thing I can't do... I don't have Access to well... Access... :) Did some more testing, made a new table and matched the field names, now it will load it without any errors, it's just only importing the first row... Not the rest of the 934 records... On Aug 8, 2007, at 1:20 PM, Stephen Sunderlin wrote: If you can import your excel doc into MS Access I'd suggest: http://www.mysql.com/products/tools/migration-toolkit/ I just started using and love it - easy intutitive GUI tool for importing data into nySQL databases. Good luck. -Original Message- From: Jason Pruim [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 08, 2007 1:15 PM To: mysql@lists.mysql.com Subject: Import file into MySQL Database.. Okay, so I have been going crazy trying to figure this out... All I want to do is load a excel file (Which I can convert to just about anything) into a MySQL database... Should be easy right? Here is the command that I have tried: LOAD DATA LOCAL INFILE '/ volumes/raider/AML.master.txt' INTO TABLE current FIELDS TERMINATED BY '\t' ENCLOSED BY '' LINES TERMINATED BY '\n'; and here is the error I am getting: | Warning | 1264 | Out of range value adjusted for column 'Record' at row 1 | What do I need to change to get this to work? Or what other info do you need to be able to help me? :) Thanks! -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import file into MySQL Database..
First line of my .csv file is: First,Last,Add1,Add2,City,State,Zip,Date,Xcode,Reason DESCRIBE is: mysql describe test; ++-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | ++-+--+-+-+---+ | First | varchar(20) | YES | | NULL| | | Last | varchar(20) | YES | | NULL| | | Add1 | varchar(50) | YES | | NULL| | | Add2 | varchar(50) | YES | | NULL| | | City | varchar(20) | YES | | NULL| | | State | varchar(10) | YES | | NULL| | | Zip| varchar(20) | YES | | NULL| | | XCode | varchar(20) | YES | | NULL| | | Reason | varchar(50) | YES | | NULL| | | Date | varchar(20) | YES | | NULL| | ++-+--+-+-+---+ 10 rows in set (0.09 sec) I've also tried adding the filed names at the end of my load data command but that didn't help... As it sits right now this is the command I'm attempting to use: mysql LOAD DATA LOCAL INFILE '/volumes/raider/aml.master. 8.6.07.csv' INTO TABLE test FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES; Which displays this: Query OK, 0 rows affected (0.01 sec) Records: 0 Deleted: 0 Skipped: 0 Warnings: 0 if I remove the IGNORE 1 LINES; from the end then I get this added into the table: A. DREW | Last | Add1 | Add2 | City | State | Zip | Date | Xcode | Reason Which is a combination of the first address and the column names. On Aug 8, 2007, at 3:34 PM, Gary Josack wrote: Jason Pruim wrote: Okay, so I have been going crazy trying to figure this out... All I want to do is load a excel file (Which I can convert to just about anything) into a MySQL database... Should be easy right? Here is the command that I have tried: LOAD DATA LOCAL INFILE '/ volumes/raider/AML.master.txt' INTO TABLE current FIELDS TERMINATED BY '\t' ENCLOSED BY '' LINES TERMINATED BY '\n'; and here is the error I am getting: | Warning | 1264 | Out of range value adjusted for column 'Record' at row 1 | What do I need to change to get this to work? Or what other info do you need to be able to help me? :) Thanks! -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] What is the first line in your text file? Also, can you provide a DESCRIBE of the table you're trying to insert into? -- Jason Pruim Raoset Inc. Technology Manager MQC Specialist 3251 132nd ave Holland, MI, 49424 www.raoset.com [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange Bursts of Gtime Syscall
Hello, I've got a strange issue and was curious if anyone might be able to shed some light on the issue. About 2-3 times an hour (not predictable) we see huge bursts of syscall behavior (150,000-200,000 over a 2 second interval). This is on MySQL-5.1.14 on a Solaris 10 AMD64 box. Using DTrace we've identified that the system call being invoked is gtime and its being called on an INSERT. The bizarre thing is we don't have a high number of INSERTs being shown in SHOW PROCESSLIST. I've included the backtrace around the function that's calling gtime. Any advise is greatly appreciated. libc.so.1`__time+0xa mysqld`_Z17mysql_lock_tablesP3THDPP8st_tablejjPb+0x2ab mysqld`_Z11lock_tablesP3THDP13st_table_listjPb+0x1c2 mysqld`_Z20open_and_lock_tablesP3THDP13st_table_list+0x6a mysqld`_Z12mysql_insertP3THDP13st_table_listR4ListI4ItemERS3_IS5_ES6_S6_15enum_duplicatesb+0x246 mysqld`_Z21mysql_execute_commandP3THD+0x2191 mysqld`_Z11mysql_parseP3THDPcj+0x168 mysqld`_Z16dispatch_command19enum_server_commandP3THDPcj+0x7d3 mysqld`_Z10do_commandP3THD+0x1e1 mysqld`handle_one_connection+0x2e4 libc.so.1`_thr_setup+0x67 libc.so.1`_lwp_start Best Regards, Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Changing Table Collation Doesn't Take Effect
Hello, I've got a table that originally was using UTF8 charset and collation. However, I upgraded one of my applications which is hardcoded to Latin1_General_CI collation in its queries. As a result, I altered the table and any specifically set columns to use Latin1 as the charset and Latin1_General_Ci as the collation. However, whenever I run a query against the table that specifies COLLATE Latin1_General_CI I still receive this error: COLLATION 'latin1_general_ci' is not valid for CHARACTER SET 'utf8' I'm a bit at wits end as I have also changed the server's default charset/collation to Latin1 and the database's charset/collation to Latin1. I've also tried doing a CREATE TABLE new LIKE old; INSERT INTO new SELECT * FROM old; to attempt to create table fresh. Also, creating a table from scratch defaults to Latin1, but produces the same error when trying to run a query against it that specifies COLLATE. Any help is greatly appreciated. Thank you very much in advance. Best Regards, Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Figuring out the difference in value between 2 fields
Okay, so I have been gooling all over trying to figure this out. I'm sure it's easy enough to do, but I can't seem to find it. All I want to do is figure out the difference between 2 fields. IE: Field 1= 20 Field 2 =10 Difference between Field 1 2 is: 10 Any ideas? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: We would like to link to you
Yes. :) And the one before it. On May 29, 2007, at 2:14 PM, Glen Barber wrote: Um... Did everyone get this message? Quoting: Dear Sirs, We contacted you last week, because we would like to exchange links with you. We understand that there is often not enough time in the day for everything, but, I am sure you appreciate that getting targeted links to your website is “key” to high search engine rankings. I am sure you will agree that this is a very good trade for both our companies as it will improve both of our rankings in the search engines. Go to: http://www.offshorebusinessportal.com/link-to-us--add-url- c170.html Your link will be active on our website within 24 hours from your reply. Regards Matt Silicia (Webmaster) -- Glen Barber -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select with like not working...
Hi all, I'm new to the list so please excuse me if I make some newbie mistakes, I am having trouble figuring out why a select statement won't work, Here's the statement: SELECT 'FName' FROM `current` WHERE `FName` like '%jason%';. if I run select 'FName' FROM current; then I get 6 rows that say 'jason'. but nothing is showing up when I use 'like'. Any ideas? Thanks for looking! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select with like not working...
I have tried %jason%, %jason, jason% all with the same result... Do you need to have an index of the column? Currently I didn't intentionally make one so I'm not sure if it's automatic or not... On May 25, 2007, at 1:26 PM, Mike Lockhart wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Have you tried using 'jason%' instead of '%jason%'? Also, do you have an index on that column? Jason Pruim wrote: Hi all, I'm new to the list so please excuse me if I make some newbie mistakes, I am having trouble figuring out why a select statement won't work, Here's the statement: SELECT 'FName' FROM `current` WHERE `FName` like '%jason%';. if I run select 'FName' FROM current; then I get 6 rows that say 'jason'. but nothing is showing up when I use 'like'. Any ideas? Thanks for looking! - -- Mike Lockhart Information Engineer ChoiceMed, Inc Email: [EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (Darwin) iD8DBQFGVxxcgCgDHkdt5m8RAqTOAKCAdoL4JSjUzsKG1Y9/wdcTCeSdswCffwOy YO0ALUVivjv7ZDFfXUAbn1M= =J52W -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql-bin.index just went poof on Master while Master Running
Hello, I have a master that's been running since the 13th of January. Since the same date I've had a slave running against it, without any errors or serious lag. Suddenly, this morning replication broke with duplicate entry errors. My master claimed to be on mysql-bin.14 with a significant LOG_POS. The slave however, claimed to be back on mysql-bin.01. Trying to skip forward, the slave would skip forward as far as mysql-bin.003, still having duplicate entry errors (assumedly because it had already been this far). I then tried to set it to something much further forward such as mysql-bin.10 at which it gave me this error: 070129 14:00:12 [ERROR] Error reading packet from server: Could not find first log file name in binary log index file ( server_errno=1236) 070129 14:00:12 [ERROR] Got fatal error 1236: 'Could not find first log file name in binary log index file' from master when reading data from binary log On the master, the file does in fact exist, and the permissions are correct. However, I did note that the mysql-bin.index has not been updated since mysql-bin.01, and the time stamp on the .index file is on the 14th of January. I tried to manually add the missing entries, but to no avail, the slave still couldn't find them. Then I restarted the slave, and still the same problem. Its as if the slave was moving right along, and then suddenly decided to forget where it was and go back to mysql-bin.01. Has anyone seen this before? Its a first for me. BTW, the MySQL on both boxes is 5.0.27-debug on Solaris 10. The binaries are the MySQL-built binaries. Thank you in advance for your help. Best Regards, Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange InnoDB Deadlock Behavior
Hi Juan, Just wanted to touchbase and see if you had any suggestions based on the my.cnf and machine config. Thank you in advance. Best Regards, Jason On 1/15/07, Juan Eduardo Moreno [EMAIL PROTECTED] wrote: Jason, Send me a my.cnf in order to view your configuration ( using innodb storage engine). Send me a configuration of your machine ( CPU and Memory). Regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange InnoDB Deadlock Behavior
Hello All, I have an issue that is seemingly hard to troubleshoot. Every so often transactions/queries on InnoDB tables will stack up such that all the queries appear to be waiting for others to execute. The problem is that the others never finish executing. If you try to kill the Updating threads/queries they never die. Restarting the program executing the queries also doesn't help. The only way to break out is to kill -9 mysql. The update load on the MySQL server is fairly constant. I've been monitoring this for a couple of days and every time I check (the most recent being last night) there are no outstanding queries in the PROCESSLIST or SHOW INNODB STATUS. Also, even during this issue there are no deadlocks listed/recognized by InnoDB. I've included the full SHOW PROCESSLIST and SHOW INNODB STATUS outputs from during the event. Whatever causes this, it seems to come upon the server fairly quickly. Any help is greatly appreciated. Thank you in advance! Best Regards, Jason ---SHOW PROCESS LIST--- *** 1. row *** Type: InnoDB Name: Status: = 070114 22:09:05 INNODB MONITOR OUTPUT = Per second averages calculated from the last 23 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 16008, signal count 15657 Mutex spin waits 0, rounds 0, OS waits 0 RW-shared spins 29962, OS waits 15019; RW-excl spins 1188, OS waits 989 TRANSACTIONS Trx id counter 0 1472286 Purge done for trx's n:o 0 1472274 undo n:o 0 0 History list length 15 Total number of lock structs in row lock hash table 1 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, OS thread id 189 MySQL thread id 1280, query id 330126 localhost root SHOW INNODB STATUS ---TRANSACTION 0 0, not started, OS thread id 188 waiting in InnoDB queue mysql tables in use 1, locked 1 MySQL thread id 1276, query id 330118 10.1.58.43 istat update insert into token_data(uid, token, spam_hits, innocent_hits, last_hit) values(11, '8563800928955791106', 1, 0, current_date()) ON DUPLICATE KEY UPDATE last_hit = current_date(), spam_hits = greatest(0, spam_hits + 1), innocent_hits = greatest(0, innocent_hits - 0) ---TRANSACTION 0 0, not started, OS thread id 187 waiting in InnoDB queue mysql tables in use 1, locked 1 MySQL thread id 1275, query id 330117 10.1.58.42 istat update insert into token_data(uid, token, spam_hits, innocent_hits, last_hit) values(433, '3228125312310094225', 1, 0, current_date()) ON DUPLICATE KEY UPDATE last_hit = current_date(), spam_hits = greatest(0, spam_hits + 1), innocent_hits = greatest(0, innocent_hits - 0) ---TRANSACTION 0 0, not started, OS thread id 186 waiting in InnoDB queue mysql tables in use 1, locked 1 MySQL thread id 1274, query id 330116 10.1.58.43 istat update insert into token_data(uid, token, spam_hits, innocent_hits, last_hit) values(2443, '15435722262529763403', 1, 0, current_date()) ON DUPLICATE KEY UPDATE last_hit = current_date(), spam_hits = greatest(0, spam_hits + 1), innocent_hits = greatest(0, innocent_hits - 0) ---TRANSACTION 0 0, not started, OS thread id 185 waiting in InnoDB queue mysql tables in use 1, locked 1 MySQL thread id 1273, query id 330115 10.1.58.43 istat update insert into token_data(uid, token, spam_hits, innocent_hits, last_hit) values(398, '15160676287902524852', 1, 0, current_date()) ON DUPLICATE KEY UPDATE last_hit = current_date(), spam_hits = greatest(0, spam_hits + 1), innocent_hits = greatest(0, innocent_hits - 0) ---TRANSACTION 0 0, not started, OS thread id 184 waiting in InnoDB queue mysql tables in use 1, locked 1 MySQL thread id 1272, query id 330114 10.1.58.42 istat update insert into token_data(uid, token, spam_hits, innocent_hits, last_hit) values(3966, '14528798056022965668', 1, 0, current_date()) ON DUPLICATE KEY UPDATE last_hit = current_date(), spam_hits = greatest(0, spam_hits + 1), innocent_hits = greatest(0, innocent_hits - 0) ---TRANSACTION 0 0, not started, OS thread id 183 waiting in InnoDB queue mysql tables in use 1, locked 1 MySQL thread id 1271, query id 330113 10.1.58.43 istat update insert into token_data(uid, token, spam_hits, innocent_hits, last_hit) values(693, '5421332010014503916', 1, 0, current_date()) ON DUPLICATE KEY UPDATE last_hit = current_date(), spam_hits = greatest(0, spam_hits + 1), innocent_hits = greatest(0, innocent_hits - 0) ---TRANSACTION 0 0, not started, OS thread id 182 waiting in InnoDB queue mysql tables in use 1, locked 1 MySQL thread id 1270, query id 330112 10.1.58.42 istat update insert into token_data(uid, token, spam_hits, innocent_hits, last_hit) values(1386, '16292377253775848661', 1, 0, current_date()) ON DUPLICATE KEY UPDATE last_hit = current_date(), spam_hits = greatest(0, spam_hits + 1), innocent_hits = greatest(0, innocent_hits - 0) ---TRANSACTION 0 0, not started, OS thread id 181 waiting in InnoDB queue mysql tables in use 1, locked 1
Strange InnoDB Deadlock Behavior
Hi Juan, Could the update log purging lagging behind due to a high UPDATE load cause this behavior? I was reading up here: http://dev.mysql.com/doc/refman/5.0/en/innodb-multi-versioning.html If so, would using innodb_max_purge_lag help? Thank you again so much. Best Regards, Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Extracting transactional data from InnoDB log files
Hello, Is it possible to extract transactional data from InnoDB log files? InnoDB kept crashing and trying to insert the same record (replayed from the log after the crash I assume). I'd like to try and extract the record from log to reconstruct the query and try to break it again in case it caused the corruption in the ibdata file. Any help is greatly appreciated. Best Regards, Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange Crashing Error - Assertion failed: fixed == 1, file item.h, line 1601
Hi Mark, Thank you very much for replying! I did open a bug for this last night after I e-mailed: http://bugs.mysql.com/?id=25460 As for reproducing, we're working on that at the moment. This happened on a production system, so we worked first to stop the hemorrhaging. Currently, we moved the high volume tables to InnoDB as a stop gap. I suppose an important piece of information is that we're running MySQL on Solaris 10. We moved the DB from a SPARC box to Solaris 10 X64 box and it happened again. We're running on top of ZFS, and that appears to be sucking down a majority of the system RAM, so we're wondering if that is causing the problem. Though at the time of crash there's typically 500MB or so free out of 8GB. I was reading that whereas Linux will allow overcommittment of RAM, Solaris returns a NULL to a malloc when there's no more memory. Could this cause the behavior? We will turn on the core file. The funny thing is this happened again, and we didn't get any debugging information outside of the usual friendly message that MySQL had crashed and the output of a few memory-related my.cnf settings. This was despite having --with-debug on. We did run --with-debug=full on and had some serious performance issues. So we're going to try and repro on a dev system by exhausting its RAM. This database is stuffing e-mails into itself, so I'm wondering if it could be a strange character that's not properly escaped. We'll get y'all a core and a stack trace. Thank you again. Any help is very much appreciated. Best Regards, Jason On 1/8/07, Mark Leith [EMAIL PROTECTED] wrote: Hi Jason, Jason J. W. Williams wrote: Hello, We've been getting random crashes on our MySQL servers running MyISAM tables for the last month, its gotten very bad in the last two weeks. This has occurred on both 5.0.27, 5.1.11 and 5.1.15-nightly20070103. It crashes the tables with high queries per second. We've fixed the issue on one of the servers by changing its tables to InnoDB. We can't do that however on another server, which we turned debugging on instead. It appears to be an assertion failure, the error message from the MySQL debugging code is: Assertion failed: fixed == 1, file item.h, line 1601 Any help is greatly appreciated. Should we report this as a bug? Any crashing is most certainly a bug, so if you could gather as much information on this as possible and report a bug that would be great. Please include: o The full section of the error log for the time of the crash o If there is a stacktrace reported, the resolved trace following: o http://dev.mysql.com/doc/refman/5.0/en/using-stack-trace.html o If you could turn on core files and upload the core file, and mysqld binary used to create it, as tar.gz to: o ftp://ftp.mysql.com/pub/mysql/upload o Link this in the bug report as well Do you have any way to reproduce this as yet? Cheers, Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange Crashing Error - Assertion failed: fixed == 1, file item.h, line 1601
Hello, We've been getting random crashes on our MySQL servers running MyISAM tables for the last month, its gotten very bad in the last two weeks. This has occurred on both 5.0.27, 5.1.11 and 5.1.15-nightly20070103. It crashes the tables with high queries per second. We've fixed the issue on one of the servers by changing its tables to InnoDB. We can't do that however on another server, which we turned debugging on instead. It appears to be an assertion failure, the error message from the MySQL debugging code is: Assertion failed: fixed == 1, file item.h, line 1601 Any help is greatly appreciated. Should we report this as a bug? Best Regards, Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bizarre InnoDB Error Message ( trx-active_trans == 0, but trx-conc_state != TRX_NOT_STARTED)
Hi Heikki,
Yes indeed. We have a uid field that is AUTO INC. Is the error more
an issue of the auto inc code in InnoDB not setting its error codes
correctly on a rollback than the auto increment code initiating an
error? Thank you in advance.
Best Regards,
Jason
On 12/31/06, Heikki Tuuri [EMAIL PROTECTED] wrote:
Jason,
I am Cc:ing the MySQL General mailing list, so that others who bump into
this bug can find this discussion.
Jason J. W. Williams wrote:
Mr. Tuuri,
We have a high degree of UPDATE/INSERT concurrency along with high
SELECTs. It causes a deadlock about once every 24 hours. In this case
a deadlock was associated with this event.
ha_innodb.cc in 5.0:
int
convert_error_code_to_mysql(
/**/
/* out: MySQL error code */
int error, /* in: InnoDB error code */
THD*thd)/* in: user thread handle or NULL */
{
if (error == DB_SUCCESS) {
return(0);
} else if (error == (int) DB_DUPLICATE_KEY) {
return(HA_ERR_FOUND_DUPP_KEY);
} else if (error == (int) DB_RECORD_NOT_FOUND) {
return(HA_ERR_NO_ACTIVE_RECORD);
} else if (error == (int) DB_ERROR) {
return(-1); /* unspecified error */
} else if (error == (int) DB_DEADLOCK) {
/* Since we rolled back the whole transaction, we must
tell it also to MySQL so that MySQL knows to empty the
cached binlog for this transaction */
if (thd) {
ha_rollback(thd);
}
return(HA_ERR_LOCK_DEADLOCK);
...
/*
Frees a possible InnoDB trx object associated with the current THD. */
static
int
innobase_close_connection(
/*==*/
/* out: 0 or error number */
THD*thd)/* in: handle to the MySQL thread of the user
whose resources should be free'd */
{
trx_t* trx;
trx = (trx_t*)thd-ha_data[innobase_hton.slot];
ut_a(trx);
if (trx-active_trans == 0
trx-conc_state != TRX_NOT_STARTED) {
sql_print_error(trx-active_trans == 0, but trx-conc_state !=
TRX_NOT_STARTED);
}
if (trx-conc_state != TRX_NOT_STARTED
global_system_variables.log_warnings)
sql_print_warning(MySQL is closing a connection that has an
active
InnoDB transaction. %lu row modifications
will
roll back.,
(ulong)trx-undo_no.low);
innobase_rollback_trx(trx);
trx_free_for_mysql(trx);
return(0);
}
Hmm... I need to check that the auto-increment code in ha_innodb.cc sets
trx-active_trans correctly. I guess you have an auto-inc column in your
table?
The deadlock output from
SHOW INNODB STATUS was so long, that it was truncated the SHOW
INNODB STATUS information somewhere in the middle of the deadlocked
rows output. The current transactions setting was completely missing
due to the truncation. I don't have access to the my.cnf from where I
am now, but I will send it on Monday once I get access. Lastly, there
were no errors printed to the .err log prior to the errors I sent.
Thank you so much for writing back. I do truly appreciate it! It is
very relieving to know it is not dangerous.
Best Regards,
Jason
Regards,
Heikki
On 12/30/06, Heikki Tuuri [EMAIL PROTECTED] wrote:
Jason,
Jason J. W. Williams wrote:
Hello Mr. Tuuri,
I'm sorry to bother you directly about this. I have had very little
luck finding anything on this in the forums or on Google and was
hoping you could help me understand a strange error message I received
from InnoDB (5.0.27). Any help would be very much appreciated. Thank
you in advance!
Best Regards,
Jason
---ERROR MESSAGE---
061228 19:02:55 [ERROR] trx-active_trans == 0, but trx-conc_state !=
TRX_NOT_STARTED
061228 19:02:55 [Warning] MySQL is closing a connection that has an
active InnoDB transaction. 0 row modifications will roll back.
the error itself does not sound dangerous.
But do you have an idea how you got this?
What is your my.cnf like?
Are there any other warnings or errors printed to the .err log prior to
this?
Best regards,
Heikki
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bizarre InnoDB Error Message ( trx-active_trans == 0, but trx-conc_state != TRX_NOT_STARTED)
Hello, I noticed the error messages below in my MySQL error log and found them a bit perplexing. Can't find anything on them in the MySQL documentation. If anyone has any clue what they mean it is greatly appreciated. As a sidenote, SHOW INNODB STATUS completes, but only shows through the DEADLOCK section. It almost appears as if the deadlock listing is so long that it runs out of buffer and doesn't get to the TRANSACTIONS or other status sections. Thank you in advance! Best Regards, Jason ---ERROR MESSAGE--- 061228 19:02:55 [ERROR] trx-active_trans == 0, but trx-conc_state != TRX_NOT_STARTED 061228 19:02:55 [Warning] MySQL is closing a connection that has an active InnoDB transaction. 0 row modifications will roll back. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Befuddled Why This Locks
Hi Dan, I guess I'm curious why this query acquires a read lock. Is it because its in a transaction? Thank you very much in advance! -J On 12/22/06, Dan Nelson [EMAIL PROTECTED] wrote: In the last episode (Dec 21), Jason J. W. Williams said: If someone could suggest some advice/guidance I would be very grateful. I'm trying to determine why the following SELECT query table locks the bad_behavior table referenced the query. 'bad_behavior' is MyISAM 'c' is InnoDB 'a' is InnoDB Query: select item_p from (select inet_ntoa(ip) as item_p,sum(if(class_factor0.75,1,0)) as info,count(*) as count from c join a on c.mid=a.mid where c.date subdate(now(),interval 6 hour) ip not in (select address from bad_behavior where score = 6 ) group by ip) as t1 where info = 5 info/count = 0.75 The befuddling part is that the bad_behavior table is table locked (preventing updates/inserts) until the query above ends. The version of MySQL is 5.0.27. I don't see anything wrong here. bad_behavior is a MyISAM table which uses table locks, so when your select is running, it grabs a read lock on the table and blocks other writers. See the chapters at http://dev.mysql.com/doc/refman/5.0/en/locking-issues.html for more detail and some tips on how to insert data even on read-locked tables. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Befuddled Why This Locks
Hi All, If someone could suggest some advice/guidance I would be very grateful. I'm trying to determine why the following SELECT query table locks the bad_behavior table referenced the query. 'bad_behavior' is MyISAM 'c' is InnoDB 'a' is InnoDB Query: select item_p from (select inet_ntoa(ip) as item_p,sum(if(class_factor0.75,1,0)) as info,count(*) as count from c join a on c.mid=a.mid where c.date subdate(now(),interval 6 hour) ip not in (select address from bad_behavior where score = 6 ) group by ip) as t1 where info = 5 info/count = 0.75 The befuddling part is that the bad_behavior table is table locked (preventing updates/inserts) until the query above ends. The version of MySQL is 5.0.27. Any help is greatly appreciated. Thank you in advance. Best Regards, Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Performance Degrades Significantly Over Time
Hi Daniel, We were using a software RAID-5 on top of hardware RAID-5 across 3 4-disk volume groups. (1 LUN from each array volume group built the software RAID-5). So we were able to lose 3 disks in a worst case scenario. It seems to me that neither RAID-1 or RAID-5 can lose more than one disk without losing data, please correct me if I'm wrong. Our data is 70% write/30% read, so the write latency is important. The filesystem is ZFS. Thanks again. Best Regards, Jason On 12/4/06, Daniel da Veiga [EMAIL PROTECTED] wrote: On 12/4/06, Jason J. W. Williams [EMAIL PROTECTED] wrote: Hi Daniel, Thank you very much for your help and advice. After some examination, we discovered a couple of things. It looks like our storage array layout was really bad for the IOPS MySQL was throwing at it, as a result the InnoDB transactions started to back-up under heavy load. Changing the array layout from RAID-5 to RAID-1 as well as moving the logs to their own spindles corrected the issue. Also, moving the InnoDB fsync log flushing interval from every commit to a 2 second interval helped dramatically. We found the storage was the problem by looking at SHOW INNODB STATUS while looking at the SCSI IOP latency. Does this sound reasonable to you? Disk IO is one of innodb's bottleneck anyway, but I doubt this could hurt performance as you suggested, making it unusable. You're the one with access to the system, and thus the only one who can test it and be sure ;) . Making a RAID 5 should increase read performance (if you calculate the best segment size), but the write operations would be not as fast as with a RAID 1, and you're risking data loss if more than one of your disks go away. I never trade security for speed, and if I were you I would check for another option. What's the most frequent operation (read/write) on your tables? Anyway, glad you solved your problem. Just out of curiosity, what is your filesystem? -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Performance Degrades Significantly Over Time
Hi Daniel, Thank you very much for your help and advice. After some examination, we discovered a couple of things. It looks like our storage array layout was really bad for the IOPS MySQL was throwing at it, as a result the InnoDB transactions started to back-up under heavy load. Changing the array layout from RAID-5 to RAID-1 as well as moving the logs to their own spindles corrected the issue. Also, moving the InnoDB fsync log flushing interval from every commit to a 2 second interval helped dramatically. We found the storage was the problem by looking at SHOW INNODB STATUS while looking at the SCSI IOP latency. Does this sound reasonable to you? Best Regards, Jason On 11/27/06, Daniel da Veiga [EMAIL PROTECTED] wrote: On 11/27/06, Jason J. W. Williams [EMAIL PROTECTED] wrote: Hi, We're running MySQL 5.0.27 under Solaris 10 on both Opteron and UltraSparc T1 machines. The performance on both boxes starts out great when the process is fresh, however over the course of a week of heavy use the performance degrades to the point where its nearly unusable. The Opteron has 2GB of RAM and the T1 has 8GB. A little stumped as to what to look for that might cause performance to degrade over time. Any pointers are greatly appreciated. On a side note, when the Opteron is a slave of the T1, when the T1 has heavy load the Opteron slave falls behind on its replication duties. The whole thing is kind of strange. Thank you again in advance. First, enable (if you don't have it already) logging, without any warnings or errors its kinda complicated to check for a real problem. From what you say, I can assume your server is probably eating memory on dead process or its trying to launch multiple threads to answer requests. Check the logs, check process (show processlist at mysql), check threads (ps on *ix), if there are dead process on the list, check your applications (web or standalone) and see if the connections are being closed correctly, decrease the wait_timeout and interactive_timeout variables to automatically clean this process, but be careful with those options, as they may kill your idle clients too fast. If there are many threads, check the variables that deal with thread launching, and your OS for limits on memory or cpu time. Also, while you're at it: http://www.mysql.com/news-and-events/newsletter/2004-01/a000301.html http://dev.mysql.com/books/hpmysql-excerpts/ch06.html http://www.mysql.com/news-and-events/on-demand-webinars/mysql-performance-tuning.php Go for it. -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- 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]
Selecting Disk Layouts for Logs DB Files
Hello, I'm hitting a performance wall on my MySQL primarily I believe because the bin log and the InnoDB logs are on the same volume group as another MySQL server. In reality, I have four MySQL servers, two per server (in Solaris Containers). All four are sharing the same volume group to maximize the spindle count. Unfortunately, that's driving the seek time crazy. Optimally, I would give each MySQL server two volume groups (one for its logs and another for its databases). That would ensure that the logs and databases each had dedicated disks in the array. Unfortunately, given my array configuration I'm limited to 6 volume groups (unlimited LUNs inside those VGs), and would need 8 VGs to give each of the 4 servers two VGs. The issue is complicated by the fact that the second 2 MySQL servers are slaves of the first two. So the data written to one server is identically written a few seconds later to the slave. So my question is, which would be the better trade-off: 1.) Put the logs for two master MySQL servers on one VG, and then the databases for those master MySQL servers on a second VG. or 2.) Put the logs for a master and slave MySQL server on the same VG, and then put the databases for the slave and master on a second VG. Or is there a better way of splitting the IO for different disks? Any help is greatly appreciated. Best Regards, Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Performance Degrades Significantly Over Time
Hi, We're running MySQL 5.0.27 under Solaris 10 on both Opteron and UltraSparc T1 machines. The performance on both boxes starts out great when the process is fresh, however over the course of a week of heavy use the performance degrades to the point where its nearly unusable. The Opteron has 2GB of RAM and the T1 has 8GB. A little stumped as to what to look for that might cause performance to degrade over time. Any pointers are greatly appreciated. On a side note, when the Opteron is a slave of the T1, when the T1 has heavy load the Opteron slave falls behind on its replication duties. The whole thing is kind of strange. Thank you again in advance. Best Regards, Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL statement work in mysql4 but not mysql5
I am going to upgrade my database from version 4 to 5. However I found some of my web application doesn't work on MySQL5 e.g following statement works in 4 but not 5 SELECT f.*, c.id as cat_id, c.position as cat_position, c.state as cat_state, c.name as cat_name, c.description as cat_desc, c.image, c.url, m.member_name as mod_name, m.member_id as mod_id, m.is_group, m.group_id, m.group_name, m.mid FROM ibf_forums f, ibf_categories c LEFT JOIN ibf_moderators m ON (f.id=m.forum_id) WHERE c.id=f.category ORDER BY c.position, f.position Error: Unknown column 'f.id' in 'on clause' The alias seem not working? What should I do, I dont want to rewrite all my sql statement Thanks. Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5 SP question: can I use parameter in LIMIT clause?
I want to write a sp return paging of recordset. CREATE PROCEDURE `sp_GetJobsDetails`(Page INT, PageSize INT) BEGIN DECLARE RecordBegin INT; DECLARE tmpPageSize INT; SET RecordBegin = Page * PageSize - PageSize; SET tmpPageSize = PageSize + 1; SELECT JOB_ID FROM JOB LIMIT RecordBegin, tmpPageSize; - this line cause error, does it supported? END -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UPDATE from one server to another
Daniel da Veiga wrote: On 6/6/06, Jason Dimberg [EMAIL PROTECTED] wrote: I am working on an application where data will be collected on laptops and then uploaded to a central database once the laptop is able to connect to the network after being in the field. I was initially thinking of using MS Access as a front end with linked tables through MySQL ODBC. I am now considering running WAMP on each machine with a web interface because there will be no interoperability issues if MySQL is the db server on both ends, but I am 1.) looking for any recommendations for the laptop interface (MS Access/WAMP or whatever other options might be available) and 2.) want to know what is the actual command for updating a table across two servers (this is NOT replication, but merely updating new data to an existing table). Data transfered will include binary objects and possibly GIS data. For example, Laptop 1 might have 10 rows of data from todays activities that need to be added to the main Server. Laptop 2 might have 30 rows of data that need to be added to the main Server. Neither laptop needs to have the data from the other, but the Server will contain data from both Laptops at the end of the day. The Server will then offer the data through a web interface. Server: Linux/MySQL 5.0 Laptops MS Access 2003 OR MySQL 5.0/ PHP 5/ Apache 2 Don't use ACCESS to deal with MySQL Data, you'll probably meet inconsistencies, along with bugs and problems with field types, besides, MS sucks... To sync the laptop with the actual database, you can use a Web Interface, or simply upload data via ftp or any other protocol (check for security) and write a script to add this data to MySQL. Are you sure you're not better served using the filesystem to store the data and simply indexing names, sizes, maybe a hash for security reasons (md5 or whatever) and timestamps so you can use the database to quickly search or organize files but offer them via HTTP or FTP? A simple app can get info about the file, upload it to the server and add a row to MySQL with the info and the location of the file in the filesystem. You would get something like: filesystem: /home/ftp/file1.bin mysql: name: file1.bin location: /home/ftp size: 1024 bytes hash: 78687hhg89686578h786 uploaded: 06/06/2006 14:30 from: laptop1 A simple web interface written in PHP or whatever can search this database, filter data, sort stuff and simply offer links to the ftp site... Just a suggestion... Daniel, Thanks for the on-topic response! Binary data will be a small portion of the data collected in the field (it will be checklists and data entry, mostly), but I am glad to hear what you said about Access. I have some fears about integrating it with MySQL and I think I am going to set up MySQL on each laptop to avoid those issues altogether and work in an environment I am familiar with (PHP/MySQL). Thanks for your suggestion. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UPDATE from one server to another
I am working on an application where data will be collected on laptops and then uploaded to a central database once the laptop is able to connect to the network after being in the field. I was initially thinking of using MS Access as a front end with linked tables through MySQL ODBC. I am now considering running WAMP on each machine with a web interface because there will be no interoperability issues if MySQL is the db server on both ends, but I am 1.) looking for any recommendations for the laptop interface (MS Access/WAMP or whatever other options might be available) and 2.) want to know what is the actual command for updating a table across two servers (this is NOT replication, but merely updating new data to an existing table). Data transfered will include binary objects and possibly GIS data. For example, Laptop 1 might have 10 rows of data from todays activities that need to be added to the main Server. Laptop 2 might have 30 rows of data that need to be added to the main Server. Neither laptop needs to have the data from the other, but the Server will contain data from both Laptops at the end of the day. The Server will then offer the data through a web interface. Server: Linux/MySQL 5.0 Laptops MS Access 2003 OR MySQL 5.0/ PHP 5/ Apache 2 Thank you. -- Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Fun with Dates and Incentives.
I think doing a sort by date with limit 10 should get you the first ten. I believe the now() function uses the server time, so no need to do date/time calcs really. Good luck, Jason Brian Menke wrote: I'm hoping for some general advice on an approach for the following scenario: I have a customer who wants to put an incentive program in place for students taking learning modules and then completing tests. The concept is simple. Award the first 10 people who complete a test with a score of 100%... that type of thing. Students are allowed to take test more than once. Track each time the student takes the test and show the latest score ect. You get the idea. I have the database tables and relationships already all set up for the tests, but it's the tracking of the dates and times that I don't have and it got me thinking. I need to track down to the day/hour/minute level. Okay, that should be easy (I think). I'm going to need to do a lot of date/time calculations. Would it be best just to have a default of CURRENT_TIMESTAMP set for a TIMESTAMP field? Or, is their something else I should be using? I have limited experience having to munge and crunch date/time info and I want to make sure I have the flexibility to do what I need in the future. The next gotcha I thought up is what about different time zones. Obviously without this consideration, people on the East coast would have an unfair 3 hour advantage over people on the west coast. I guess I can have a time zone field in my student table so I could derive the time difference. Any suggestions on a good time zone approach? Here are my two tables as they stand now. I'm wondering if these are set up in a way to allow me to do all this date time crunching I'm going to need to do in the future? Any suggestions are greatly appreciated :-) CREATE TABLE `students` ( `store_id` varchar(6) NOT NULL, `email` varchar(64) NOT NULL, `fname` varchar(32) NOT NULL, `lname` varchar(32) NOT NULL, `role` char(2) NOT NULL default '5', `password` varchar(8) NOT NULL, `phone` varchar(24) default NULL, `reg_date` date default NULL, PRIMARY KEY (`email`), UNIQUE KEY `email` (`email`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `completed_modules` ( `module_id` char(2) NOT NULL default '', `email` varchar(64) NOT NULL, `score` int(2) NOT NULL default '0', `time` timestamp NOT NULL default CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Brian Menke Visual Matter, Inc 1445 Foxworthy Ave., Suite 50-215 San Jose, CA 95118 408 375 9969 San Jose ~ Los Angeles www.visualmatter.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
OOC: Reply / Return Address of this List
1. Please always reply to the List. Who runs this list? Could it please be configured to send replies back to the list rather than the individual? It's really annoying to keep ending up with a personal address - it would make things so much easier, and is, to my knowledge, standard practice for mailing lists to have replies automatically go to the list itself. -- Jason Teagle [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Reply / Return Address of this List
The battle has been fought before - and the list administrator has given his reasons why he has not made the requested change. The way the list currently behaves is not an accident or omission, but a deliberate decision. I do not recall the grounds for that decision - maybe RFCs or the behaviour of certain email clients (which probably does not include your own), or maybe the fact that an individual reply is often very difficult if the default is group reply but no the other way round. But I would have thought that 99% of replies should go to the list, and personal replies are the exception - as has been pointed out here, and many times on other lists, a public answer benefits many people. However, if the subject has been addressed and the decision made, then there's just no point in this topic. I guess those of us that don't like it, or don't like people inadvertently posting personal replies thanks to that decision, should simply find another list. -- Jason Teagle [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Error on T_echo ?? what is this ?
I believe you are missing the trailing semicolon from the previous line of
code, before the //confirm comment. It's a rather cryptic way of saying it
found echo when it expected something else first. Gotta love those PHP
error messages.
--
Jason Teagle
[EMAIL PROTECTED]
-Original Message-
From: Brian E Boothe [mailto:[EMAIL PROTECTED]
Sent: 13 April 2006 05:13
To: mysql@lists.mysql.com
Subject: Error on T_echo ?? what is this ?
i,m getting the following error on my MySQL Code inserting data into a
database, .?
*Parse error*: parse error, unexpected T_ECHO in
c:\inetpub\wwwroot\projects\testsum\adddata.php on line 30
LINE 30 is echo
Query Finished;
--here is ALL my code --
?
//--php/Mysql Code by Brian E Boothe //
//throw data from form into MySQL database routine //
//initilize Adddatta to mysql database, //
//if($_POST['submit']) //If submit is hit
//{
//then connect as user
//change user and password to your mySQL name and password
mysql_connect(localhost,root,goobers);
//select which database you want to edit
mysql_select_db(test);
//convert all the posts to variables:
$value1 = $_POST['value1'];
$value2 = $_POST['value2'];
$sumfield = $_POST['sumfield'];
//Insert the values into the correct database with the right fields
//mysql table = news
//table columns = id, title, message, who, date, time
//post variables = $title, $message, '$who, $date, $time
// $result=MYSQL_QUERY(INSERT INTO orders
(id,title,message,who,date,time).
$result=MYSQL_QUERY(INSERT INTO addvalue (`value1`, `value2`,
`sumfeild`).
VALUES ('$value1', '$value2', '$sumfield')
//INSERT INTO `orders` (`OrderNo`, `CompanyName`, `BillingAddress`,
`City`, `StateOrProvince`, `PostalCode`, `PhoneNumber`, `FaxNumber`,
`WebPage`, `ContactFirstName`, `ContactLastName`, `EmailAddress`,
`Notes`, `Customer`, `Startdate`, `Completedate`, `Biddate`,
`Bidamount`, `ProjectInfo`, `ElecProjCost`, `ElecProjBill`, `ElecRem`,
`CtrlProjCost`, `CtrlProjBill`, `CtrlRem`, `OthrProjCost`,
`OthrProjBill`, `OthrRem`, `BondAm`, `BondBill`, `BondRem`)
//confirm
echo Query Finished;
?
--
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: error with java
I don't think so, since even with telnet localhost 3306 or telnet 127.0.0.1 3306 I'm able to see the server prompt.. and I'm using the standard MySQL port for sure (it's a clean installation); and there are no firewall active... any other suggestion!? thanks :) Wild stab in the dark here - Java I/O permissions not set correctly on your machine to allow it? If I recall, Java's sandbox feature means you have to supply a permissions file for I/O. Perhaps that file already exists on the other machine that works? -- Jason Teagle [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AlterTable Structure Across Multiple DBs
I have about 25 databases with the same structure and occasionally need to update the table structure. For example, I recently found a mistake in a field that was of type SET and needed to be VARCHAR. I will now need to edit each table. Is there an easy method to alter table structure across multiple dbs as opposed to editing each one individually? In retrospect I should have combined them into one db and may consider doing that. All dbs start with 'pm_' and have identically named tables MySQL 5.0.18 Windows 2003 Thank you, -- *Jason Dimberg* -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Timestamp problem in mysql5.0.18
See, for the UK, on that date, 2am to 2:59 inclusive do not officially
exist - hence, 3am to 3:59 for GMT+1, etc. I couldn't quite figure why 3am
was being rejected until now. I'm impressed that MySQL knows that {:v)
--
Jason Teagle
[EMAIL PROTECTED]
-Original Message-
From: Ricardas.S [mailto:[EMAIL PROTECTED]
Sent: 21 March 2006 14:31
To: [EMAIL PROTECTED]
Subject: Re: Timestamp problem in mysql5.0.18
Yes, I think you are right, it should be the main reason of
insert failure.
Thank you for good idea.
Ricka
- Original Message -
From: Jason Teagle [EMAIL PROTECTED]
To: Ricardas.S [EMAIL PROTECTED]
Sent: Tuesday, March 21, 2006 14:55
Subject: RE: Timestamp problem in mysql5.0.18
All other date or hour values I tried, works good, but this one
is not accepted. UPDATE statement behaves the same.
Server time zone is GMT+2.
I tried 5.0.18nt and two linux versions, result is the same.
I tried to change time zone, and noticed that mysql server does
not accept 2006-03-26 date with hour values which are equals GMT
offset + 1.
When I tried with ALLOW_INVALID_DATES option enabled, then server
simply changes hour upward to 04.
Is it mysql bug?
I believe that the clocks go forward in the UK (and possibly other
countries) on the 26th - 2am suddenly becomes 3am. I wonder if
this is part
of the problem? Seems a bit of a coincidence, especially as you
say it went
to 4am.
--
Jason Teagle
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql++1.7.1 vc++6 compile errors
the example program compiles and runs. when i go to make my own project i use the wizard and create a basic dialog MFC app. at the top of the main cpp file i add #include mysql++ and then in the program call Connection con(login,localhost,root,abc); in the mysql++ download there is many lib and include folders, i have copyied the contents of these in the the vc++6 include and lib folders. when come to compile i get many errors as seen below. please help ...snip... Generating Code... Linking... mysqlDlg.obj : error LNK2001: unresolved external symbol public: __thiscall MysqlConnection::~MysqlConnection(void) (??1MysqlConnection@@[EMAIL PROTECTED]) mysqlDlg.obj : error LNK2001: unresolved external symbol public: __thiscall MysqlConnection::MysqlConnection(char const *,char const *,char const *,char const *,bool) (??0MysqlConnection@@[EMAIL PROTECTED]@Z) Debug/mysql.exe : fatal error LNK1120: 2 unresolved externals Error executing link.exe. mysql.exe - 3 error(s), 14 warning(s) Have you included mysql++.lib in your list of libraries to link to within your project? (Project - Settings - 'All Configurations' from 'Settings for:' combo, Link tab, 'Input' from 'Category' combo) -- Jason Teagle [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
