Re: Looking for a Mysql Guru/DBA
Kevin Hunter wrote: At 1:43p -0400 on Wed, 21 May 2008, bruce wrote: The basic goal of the project is to be able to track the sites that I'm visiting via a Firefox extension. I want to be able to implement something like the breadcrumbs extension, but I want to be able to go a lot further. If this is for a one-per-person kind thing (i.e. only a single Firefox installation will use a single DB instance at any time), MySQL may be overkill. You may want to look towards something smaller and embedded, like SQLite[1]. It'll be much less overhead, in both installation for users and memory overhead for your extension. In fact, Firefox 3.0 already includes SQLite for the smart url bar they've got going on. If you're interested, and you're reasonably good at mysql, and devising database structures/schema then let's talk! You will still want to talk to someone about getting the right schema in place, however. Kevin [1] http://www.sqlite.org/ Hey Kevin, One thought on that, there are a lot of existing WAMP installs out there. :) But overall I think you are probably right. This might be worth a look http://www.freebyte.com/programming/database/ Thanks, Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: very simple but slow query
We use a sub select on a 8M+ row table because it takes better advantage of indexes. SELECT startip,endip FROM geodb a WHERE a.startip = (SELECT max(startip) FROM geodb WHERE b.startip = 3250648033) AND a.endip = 3250648033; startip and endip are INT(10) unsigned and unique keys. This returns, on a fairly crappy old system in milliseconds after the table is loaded. Carlo, What do your tables look like exactly, and what are you considering to be poor performance? Look up the profiling flag, if you set that, you can get a detailed breakdown on the time spent in each query. mysql set profiling=1; Query OK, 0 rows affected (0.00 sec) mysql select count(*) from visitor; +--+ | count(*) | +--+ | 152 | +--+ 1 row in set (0.00 sec) mysql show profile; ++--+ | Status | Duration | ++--+ | (initialization) | 0.08 | | checking query cache for query | 0.000232 | | Opening tables | 0.48 | | System lock| 0.25 | | Table lock | 0.000125 | | init | 0.62 | | optimizing | 0.34 | | executing | 0.000314 | | end| 0.19 | | query end | 0.12 | | storing result in query cache | 0.000245 | | freeing items | 0.3 | | closing tables | 0.23 | | logging slow query | 0.11 | ++--+ 14 rows in set (0.01 sec) http://www.futhark.ch/mysql/122.html is a good tut on joining a table on itself which might be where you are going. Don't use cross joins. Just do some googling as to why. Thanks, Eric Ananda Kumar wrote: in mysql sub queries dont perform well. You can could try this SELECT a.ID FROM ven_tes a, ven_tes b where a.id=b.id and b.id_ven=6573 . On 5/20/08, Wakan [EMAIL PROTECTED] wrote: Hi, can someone could explain where are problems in this query: EXPLAIN SELECT ID FROM ven_tes WHERE ID IN (SELECT ID FROM ven_tes WHERE ID_ven=6573) +++-+-++-+-+--+--+--+ | id | select_type| table | type| possible_keys | key | key_len | ref | rows | Extra| +++-+-++-+-+--+--+--+ | 1 | PRIMARY| ven_tes | index | NULL | PRIMARY | 4 | NULL | 6573 | Using where; Using index | | 2 | DEPENDENT SUBQUERY | ven_tes | unique_subquery | PRIMARY,ID_ven | PRIMARY | 4 | func |1 | Using index; Using where | +++-+-++-+-+--+--+--+ as you can see, it doesn't use absolutely indexes on ven_tes (ID is the primary key, ID_ven is index) Thanks in advance Carlo -- 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: Query execution time - MySQL
Neil Tompkins wrote:
Thanks for your help. In the end I've decided to use GetTickCount()
Neil
Date: Wed, 14 May 2008 13:44:22 +0100 From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: Query execution time - MySQL Hi Neil, If your using Linux then you have to install the glib RPM's
in the usual way. I don't know about other platforms, but I am sure there will be a version of glib out there... Also ensure the correct include and link directives are in your Makefile, which you can get (on Linux) using the
commands: # glib-config --cflags # glib-config --libs Ben Neil Tompkins wrote: Thanks Ben, but I don't appear to have the header file glib.h in my libraries.Neil
Date: Wed, 14 May 2008 12:39:09 +0100 From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] CC: [EMAIL
PROTECTED]; mysql@lists.mysql.com Subject: Re: Query execution time - MySQL If you us
ing C++ then you can use this: http://developer.gimp.org/api/2.0/glib/glib-Timers.html I use this in my code, does an excelent job. Also you may want to look at the 'slow log' in mysql which will show, to the nearest second, the length of queries Ben Neil Tompkins wrote:Hi Craig, Thanks for your detailed reply. Basically what I'm trying to extract is the time taken from when I execute the mysql query in my C++ Builder program until the time the query has finished. So my question is can I build in to my SQL query SELECT Name FROM Customers the time the query actually took or do I need to do this outside of my query. RegardsNeil Date: Wed, 14 May 2008 07:21:04 -0400From: [EMAIL PROTECTED]: [EMAIL PROTECTED]: Re: Query execution time - MySQLCC: [EMAIL PROTECTED] Niel,Not much
detail there (but I'll go off what you provided...). Some people limit the actual MySQL system for times it TAKES MySQL to execute queries. For THIS to be accomplished, MySQL has built-in functionality to measure the time is takes queries to take place so it can ... limit them. So, in essence, I guess we can extract that data and get it back to you for whatever usage statistic you are looking to measure. ( See: http://www.bigresource.com/MYSQL-what-is-execution-time-of-a-query-based-on-was-a-mysql-question--0PxW0B3P.html ) or for usage in JDBC by calling the setQueryTimeout() function of a Statement object...and so forth.HOWEVER - Just so you know, if you execute the query MANUALLY via the command-line of MySQL it will tell you how long the query took. Just use normal SQL syntax, execute the query on the table and VOILA! Your answer:mysql queryormysqlrun the query (use the below quoted/threaded example as a starting place to
write your own query...?)Take a look at this thread (it basically explains the answer with a bit more detail on what the output will
be):http://forums.mysql.com/read.php?108,51989,142404#msg-142404SELECT * FROM user_log; 15113 rows fetched in 5.3274s (0.1498s) SELECT
BENCHMARK(1, RAND()); 1 row fetched in 0.0505s (13.2676s) I believe the results are the following: The first number is the time it took MySQL server
to send the result set to the client. The second number (in parens) is the time it took MySQL server to execute the query itself.TOTAL
TIME will EQUAL A + B (for total time it took on your server/P.C. or wherever you are running the query...). Many things come into factoring why it takes
longer or shorter. So this is why I asked if you are attempting to optimize or what not, but that is whole new story. (( What Operating System are
you running? This would be helpful to give you the step-b
y-step, so to speak. Or perhaps provide us with a bit more information***Also, if you are looking to perhaps make it so queries take shorter times (optimization effort) to execute
a little bit more about your MySQL database setup and machine(s) would be beneficial to us as well. ))Let me know if you have any questions.Standing by and I hope this
helped you.Sincerely,Craig Huffstetlerxq on FreeNode #mysql | #apacheOn Wed, May 14, 2008 at 6:13 AM, Neil Tompkins [EMAIL PROTECTED] wrote:
Hi,When performing a SQL query like SELECT Name FROM Customers. How do I obtain the time in which the query took to execute like 1.5 seconds
etcThanks,Neil_All new Live Search at Live.comhttp://clk.atdmt.com/UKM/go/msnnkmgl001006ukm/direct/01/
_Great deals on almost anything at eBay.c
o.uk. Search, bid, find and win on eBay today!http://clk.atdmt.com/UKM/go/msnnkmgl001004ukm/direct/01/ -- MySQL
General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL
Re: History of changed rows
C K wrote: Hi all. How can we manage the history of changed rows in the database. I have some idea but not yet implemented. By using triggers to make a copy of the row being changed to the another table in the same db. Is there any way to only save the changed fields data and field name? Any other idea? Thanks CPK How about mysqlbinlog? :) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Performance problem - MySQL at 99.9% CPU
-Original Message- From: Per Jessen [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 02, 2008 7:51 AM To: mysql@lists.mysql.com Subject: Re: Performance problem - MySQL at 99.9% CPU Gunnar R. wrote: I am thinking about buying a new dual core box (with IDE disks?), but I have to make sure this really is a hardware issue before I spend thousands of bucks. I think you've got an application problem somewhere which you should look into first. Hardware-wise I think you're doing fine, except you could probably increase overall performance with more memory. MySQL is pretty good at query-caching. Just for general info I tested Heap tables vs the query cache, query cache one and it makes a lot of sense why once I saw that. Even in-memory tables can't be as fast(giving queries in the cache) because of the cost of parsing and optimization of the query. The query cache being basicly a fast in memory hash lookup. However, if you have a system that doesn't have a lot of repetative queries, the Heap table would win again that just makes sense, but my little test proved the query cache is pretty good for most things. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Cross database joins
Hi, I found one thread on this that included some people's opinions, but I haven't been able to find anyone who has actually done some performance testing to see if there is a cost and what that cost is to doing cross database joins. I do tend to want to keep everything in one DB, but it gets hard when you have databases that do cross over at times, but rarely. Of course I am being somewhat lazy in doing this post, but only because I think someone here *must* have already done some testing between cross db joins and inside db joins. Another point of interest is if DBI actually opens another connection or not. I saw one mention of a worry about that, but as I understand it, you can refer to any table in any database from any mysql connection that has permission to access that DB and table, so you should be able to access any DB from any initial connection. Thanks, Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Unusual sort
Hi, One thought, it might a good idea to make a trigger/procedure that inserts the seprate index field, so you can forget about it from here on. -Original Message- From: Martin Gainty [mailto:[EMAIL PROTECTED] Sent: Saturday, November 24, 2007 11:18 AM To: Jim; mysql@lists.mysql.com Subject: RE: Unusual sort Hi Jim it seems that you cannot create an index with a function soyou will need to establish a separate 12 character column which has all of the URL entries insertedalphabetically in ascending order (fully padded with www. prefix)backup your DBALTER TABLE table ADD TwelveCharacterURL CHAR(12), ADD FOREIGN KEY 12CharacterIndex (TwelveCharacterURL); UPDATE TABLE TABLE set TwelveCharacterURL=(LPAD(OldURLColumn,12,'www.')); Anyone else? Martin __Disclaimer and confidentiality noteEverything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. From: [EMAIL PROTECTED] To: mysql@lists.mysql.com Subject: Unusual sort Date: Fri, 23 Nov 2007 16:29:50 -0700 I have a table containing web site host names, most of them having both a name.com and www.name.com version, that I'd like sorted in the following manner: axxx.com www.axxx.com bxxx.com www.bxxx.com wxxx.com www.wxxx.com zxxx.com www.zxxx.com Any way to do this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Your smile counts. The more smiles you share, the more we donate. Join in. www.windowslive.com/smile?ocid=TXT_TAGLM_Wave2_oprsmilewlhmtagline -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Giant database vs unlimited databases
Mohammad wrk wrote:
Hi Eric,
In the case of a yes answer to the second question below, can't we
still use something like VPD (Virtual Private Database) in MySQL?
Thanks,
Mohammad
Hi,
I don't know much about Oracle, but I looked this up. MySQL can't do it,
that I know of. Here is the final example of such a setup that I found
at http://www.oracle-base.com/articles/8i/VirtualPrivateDatabases.php
CONNECT user1/[EMAIL PROTECTED];
INSERT INTO schemaowner.user_data (column1, user_id)
VALUES('User1', 1);
INSERT INTO schemaowner.user_data (column1, user_id)
VALUES('User2',2);
COMMIT;
CONNECT user2/[EMAIL PROTECTED]
INSERT INTO schemaowner.user_data (column1, user_id)
VALUES ('User 1', 1);
INSERT INTO schemaowner.user_data (column1, user_id)
VALUES ('User 2', 2);
COMMIT;
CONNECT schemaowner/[EMAIL PROTECTED]
SELECT * FROM schemaowner.user_data;
CONNECT user1/[EMAIL PROTECTED];
SELECT * FROM schemaowner.user_data;
CONNECT user2/[EMAIL PROTECTED]
SELECT * FROM schemaowner.user_data;
Notice that:
* When connected to USER1, only the first insert will work.
* When connected to USER2, only the second insert will work.
* The failing inserts produce the error:
ORA-28115: policy with check option violation
You can setup column level privileges on MySQL, but I wonder if it would
be buggy considering I have never heard of anyone doing this before.
Plus from what I understand the above example is a lot more than column
privileges. user1 can only insert data if the insert statement's data
sets user_id to 1, for example. Pretty cool, but scary in a way. I find
this much logic in the DB to be scary(esp if not well documented), but
then I use MySQL :)
So as to which way you should go is most defiantly a matter of opinion I
think. But, going back to my opinion(which is not at all informed as to
all the details), if question 2 is a YES, then I would tend to go with
separate DBs.
Thanks for the Oracle lesson :)
Thanks,
Eric
- Original Message
From: Eric Frazier [EMAIL PROTECTED]
To: Mohammad wrk [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Sent: Monday, November 19, 2007 7:42:13 AM
Subject: Re: Giant database vs unlimited databases
Mohammad wrk wrote:
Hi,
I'm working on a web 2.0 project that targeting small to medium size
companies for providing business services. Companies simply register to
the site and then start their business by loading their data, sharing
and discussing them with others.
The design/architectural decision now we are facing from database
perspective is how we should store companies' specific data? One way is
to put all of them in a single database and partition them by company-id
and the other one is to create, on the fly, a new database per company
. The justification for the latter is that MySQL is not powerful enough
(compare to Oracle or DB2) to handle large amount of data and concurrent
users.
I'm new to MySQL and don't know that much about it and this is why
I'd like to discuss this concern here.
Funny, I thought you asked the question, should I separate my customers
into their own databases, or use one big DB? Not MySQL sucks, Oracle is
better. :)
Issues I would ask about on this:
1. Is there a chance that given their separation, these DBs will ever
diverge in design because of differences between customers?
2. Could they ever need to be separated for legal reasons? (like one bad
query causing customer data be compromised)
3. Is there any other reason you may do something vastly different from
one customer to another?
If you answer yes to any of these, then you might be best off separating
dbs. But, if you never want to, or expect for any of these things to
happen, you will just be creating headaches for yourself. Backup,
replication, and the need for cross DB queries, will all be a pain in
comparison to a single DB.
I am sure there is more to consider, but these are the points that come
to my mind right away.
Thanks,
Eric
Instant message from any web browser! Try the new * Yahoo! Canada
Messenger for the Web BETA*
http://ca.messenger.yahoo.com/webmessengerpromo.php
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Giant database vs unlimited databases
Russell E Glaue wrote: No one probably wants to go through the trouble to code this solution but it is possible to use MySQL Proxy to filter the SQL statements and results. MySQL Proxy sits in between MySQL Server and the MySQL Client. It can read queries, modify them, send queries to the server or deny them all together, and even read results and modify them as well, or deny the results to be sent back to the client. Perhaps if you can resolve to a less complicated set up, but still lean towards the VPD idea, MySQL Proxy might work for you. I just wanted to throw this solution out in case it was useful. -RG Hi Russel, That sounds like a cool idea and makes sense. That is what made me feel oogy about the idea of trying to do something like this with MySQL privileges. I read tons of things that say the real auth layer should be separate. And that VPD example was a good example of how fine grained and therefore complex auth schemes can get. I would guess that following your idea further, it could end up being more scalable(sorry I hate that word it is so overused) that is easy to change and upgrade. I am interested in the many dbs vs one big db issue because I followed the many db choice at one time. It did make sense because I could answer all three of the questions in my previous post a big YES. But, it was a lot of extra work, esp over time. I also discovered you can do cross DB joins, but that makes some DBAs shriek in horror :)As it should. Thanks, Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Giant database vs unlimited databases
Mohammad wrk wrote: Hi, I'm working on a web 2.0 project that targeting small to medium size companies for providing business services. Companies simply register to the site and then start their business by loading their data, sharing and discussing them with others. The design/architectural decision now we are facing from database perspective is how we should store companies' specific data? One way is to put all of them in a single database and partition them by company-id and the other one is to create, on the fly, a new database per company . The justification for the latter is that MySQL is not powerful enough (compare to Oracle or DB2) to handle large amount of data and concurrent users. I'm new to MySQL and don't know that much about it and this is why I'd like to discuss this concern here. Funny, I thought you asked the question, should I separate my customers into their own databases, or use one big DB? Not MySQL sucks, Oracle is better. :) Issues I would ask about on this: 1. Is there a chance that given their separation, these DBs will ever diverge in design because of differences between customers? 2. Could they ever need to be separated for legal reasons? (like one bad query causing customer data be compromised) 3. Is there any other reason you may do something vastly different from one customer to another? If you answer yes to any of these, then you might be best off separating dbs. But, if you never want to, or expect for any of these things to happen, you will just be creating headaches for yourself. Backup, replication, and the need for cross DB queries, will all be a pain in comparison to a single DB. I am sure there is more to consider, but these are the points that come to my mind right away. Thanks, Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication and AUTO_INCREMENT; is it safe?
On 10/24/07, Eric Frazier [EMAIL PROTECTED] wrote: js wrote: Hi list, Reading How AUTO_INCREMENT Handling Works in InnoDB[1] makes me wonder how is it possible to replicate AUTO_INCREMENTed value to slaves. According to the doc, If you specify an AUTO_INCREMENT column for an InnoDB table, the table handle in the InnoDB data dictionary contains a special counter called the auto-increment counter that is used in assigning new values for the column. This counter is stored only in main memory, not on disk. Let's say there are two server, A and B. A replicates its data to B, the slave. A and B has a table that looks like(column 'id' is auto_increment field) id value 1 a 2 b 3 c 4 d If After delete from table where id = 4 and restart mysqld on server B, insert into table (value) values(e) is executed on server A. In this case, because A's internal counter is 4, table on A would be 1 a 2 b 3 c 5 e But B's would be different because restarting mysqld flushed InnoDB's internal counter. 1 a 2 b 3 c 4 e Is this correct? or MySQL is smart enough to handle this problem? Thanks. [1]http://dev.mysql.com/doc/refman/4.1/en/innodb-auto-increment-handling.html http://dev.mysql.com/doc/refman/5.0/en/faqs-general.html See 28.1.5 But there are more reasons to avoid auto-increment in mysql. I haven't run into the problem above, but I have had such problems when restoring backups. Make your data make sense, a mindless counting number just to make a table unique doesn't every make any sense. Session ids, timestamps, combinations of fields all make much better primary keys and it is safer overall to implement a counter function in your app than to trust mysql's js wrote: Thank you for your reply. But I couldn't under stand how --auto-increment-increment and --auto-increment-offset helps me avoid my problem. Could you please explain? Restarting the server doesn't reset autoinc.. But that can happen when you restore a backup, I don't remember what to avoid of the top of my head, but look into mysqldump and do some tests. Best way to understand But, you can avoid any problem with autoinc by just not using it. If you must use it for replication it is quite safe to use it if you are only replicating to a slave write only, so the slave is not also another master(you are not doing inserts/updates on the slave as well), or if you need to replicate in a circle use auto-increment-increment etc. I think it is not a bad idea to use these even if your slave is just a slave. Bottom line, if you are designing a DB, for max safety avoid autoinc entirely. It will save you headaches for a little extra work to start. This is one area where MySQL still deserves some jeering because Postgress had this figured out a long time ago with proper sequences that are a lot easier to mange. With all of the features and cool stuff MySQL has added in the last few years, I don't get why they haven't fixed autoinc or added a true sequence type. Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Saving space disk (innodb)
Dan Rogart wrote: OPTIMIZE TABLE should reclaim that space, but be aware that it could take a while to run (locking your table all the while) since it just maps to an ALTER TABLE statement which creates a new copy of the table. Depends on how big your tables are. Doc: http://dev.mysql.com/doc/refman/4.1/en/optimize-table.html He has InnoDB tables and that doesn't reclaim tablespace. He wants to get back disk space from his data files. As of 4.1.3, |OPTIMIZE TABLE| is mapped to |ALTER TABLE|, which rebuilds the table to update index statistics and free unused space in the clustered index But that just means he has empty space in his tablespace :) At least that is how I read it, so Baron's suggestion makes the most sense. Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Saving space disk (innodb)
Andrew Carlson wrote: If you do what Baron suggests, you may want to set Innodb to create a file-per-table - that way, in the future, you could save space when tables are dropped, or you could recreate innodb tables individually to save space, not have to dump all your innodb tables at one time. I think this is a fantastic idea. So you would - do your DB dump(horrible with hundreds of Gigs.) - reset your my.cnf setting to include: [mysqld] innodb_file_per_table - stop the db - kill off the existing tablespace files - restart the DB - recreate the database and import your dump. http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html So the only other question is what is the cost if any? It is a good idea because often there are just a few tables that get really big and this is a nice way to deal with them separately like you would with MyISAM. Eric On 10/10/07, Baron Schwartz [EMAIL PROTECTED] wrote: Hi, Tiago Cruz wrote: Hello guys, I have one monster database running on MySQL 4.0.17, using InnoDB: 270GB Oct 10 14:35 ibdata1 I've deleted a lot of register of then, and I've expected that the size can be decreased if 50% (135 GB) but the ibdata was the same value than before clean... How can I force to save this space? You must dump your data to files, shut down MySQL, delete your current InnoDB tablespace and log files, reconfigure the server, restart MySQL and let InnoDB create new (empty) files. Then reload the data. You should probably save your current data and tablespace files until you are sure you complete this successfully. It's an annoying procedure but there is no other way. Baron -- 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: DB Schema Comparison Utility ?
Daevid Vincent wrote: This has been asked for many many times on this list, not sure why mySQL AB doesn't just release a command line tool like a 'mysql diff' and also a 'mysql lint'. The lint one should be totally trivial for them to do, as they already have a SQL parser! I can't tell you how many times our daily build was broken by a missing semi-colon or some other SQL syntax error. We run all commits through php -l and ruby's checker, but mysql is the only one we have to sweat over. While I'm glad that pretty GUI tools like Upscene's exist, that doesn't do us any good on a linux build system where it does an svn checkout, runs automated BVT tests, compiles code, uploads to a daily build directory, etc. We need command line tools that run on linux. :( This is not quite what you were asking for, but I found this yesterday: http://sourceforge.net/projects/mysqltoolkit I think the guy has done a lot of really good work. Thanks, Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slave Bin Log Question
Boyd Hemphill wrote: I have executed a strategy for backup where I stop a slave and do a mysqldump with --master-data. Both master and slave are 4.1.20 My assumption was that the log coordinates in the dump file would provide me with the place to replay the log for a point in time recovery. What I learned today however is that it appears the binary log only captures statements run directly on the slave. Any SQL run by the SQL thread of replication seems only to go in the relay log. This effectively renders the --master-data useless for my purpose. So, I have two questions. 1. Can someone verify that the binary log on the slave is not capturing SQL from the replication SQL thread. It sounds like you need --log-slave-updates http://dev.mysql.com/doc/refman/5.0/en/replication-options.html 2. If the above is really true, what strategies are you using to backup InnoDB without InnoDB hot backup? Thanks for your time! Peace Boyd CONFIDENTIALITY NOTICE: This email attached documents may contain confidential information. All information is intended only for the use of the named recipient. If you are not the named recipient, you are not authorized to read, disclose, copy, distribute or take any action in reliance on the information and any action other than immediate delivery to the named recipient is strictly prohibited. If you have received this email in error, do not read the information and please immediately notify sender by telephone to arrange for a return of the original documents. If you are the named recipient you are not authorized to reveal any of this information to any other unauthorized person. If you did not receive all pages listed or if pages are not legible, please immediately notify sender by phone. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Where to find advice on database structure/design?
It might also help to at least try to understand why you normalize. Don't just try to follow the rules, there is some art to it as well. My favorite book on this is Database Design for Mere Mortals Thanks, Eric At 09:11 PM 10/22/03 -0700, olinux wrote: Read up on database normalization. (do a search on your favorite search engine) It will give you an appreciation of storing related pieces of info in different tables. Though you shouldn't need a series of forms to access the data (most of the time) - this has more to do with your programming logic. With a strong understanding of normalization you'll be able to structure your forms to get the info you want. Here's a great site with examples of different data models. Not all are complete, but a great start and a great source for ideas. http://www.databaseanswers.com/ olinux --- Apollo (Carmel Entertainment) [EMAIL PROTECTED] wrote: I have moved our database from Access to MySQL, but I did leave same structure. Problem is that in our business we deal with companies that have multiple branches so having it like we have now with sub queries and subforms that have to look up info from 3 tables to give me one record of contact information just does not work anymore. Too many forms with subforms create huge performance problems. Anyone can point me to a good reading material (on the web or in print) that would give me ideas how to deal with this overcomplicated way of handling client data? Thanx, Apolinaras Apollo Sinkevicius Carmel Music Entertainment, LLC web-site: http://carmelme.com Having an event in Chicago, or would you like to bring Chicago entertainment to your event? Give Carmel Music Entertainment a call for the finest entertainment available in Chicago. - Visit CARMEL MUSIC ENTERTAINMENT website http://carmelme.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] (250) 655 - 9513 (PST Time Zone) Inquiry is fatal to certainty. -- Will Durant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication from 2 Master
Hi, That makes a lot of sense, in fact we are using two servers as Masters replicating to each other in a circle, but one has an extra slave which is in our office. So in effect that does what this guy was looking for, without doing anything weird and strange. Thanks, Eric At 02:42 PM 8/24/03 -0700, Jeremy Zawodny wrote: On Sun, Aug 24, 2003 at 02:02:06PM -0400, Eric Frazier wrote: Sounds very biblical. :) Yeah, I have it etched on a pair of stone tablets around here somewhere... :-) Wouldn't there be a way to do this with two copies of mysql that share a common data dir? Maybe. But that's not what he's asking about. It's a bit tricky to do correctly, doesn't work in all cases, and is often more trouble that it's worth. I don't know if you could do that with InnoDB, but I wonder if you could with myisam? You can do it with MyISAM, but not InnoDB or BDB. At least if you had a system where the two sets of tables came from a different master, and there was no overlap, or if the slave was just functioning as a backup, maybe it would be possible and not lead to too much horror? Wel, that's the trick. What I've found is that in order to understand the possible horrors, you end up having to bump into numerous problems along the way. In the end you realize that it probably would have been better to look at the problem a bit different, such as chaining together the two masters, or running completely separate instances of MySQL on the slave machine rather than trying to mix and match the data. I guess that what it comes down to is this. MySQL's replication was designed for relatively simple master/slave setups with 1 master and 1 or more slaves. By taking advantage of the simplicity of MyISAM tables and really knowing how replication works, MySQL does locking, and so on... you can often use it in ways that were not intended. The problem with doing so is that you *are* using it ways that were not intended. That may cause strange problems down the line. Now I've done more than my fair share of abusing MySQL in strange configurations. Some have worked quite well and others have not. I'm not saying don't do this but it's not something to simply dive into either. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 23 days, processed 1,073,046,344 queries (536/sec. avg) (250) 655 - 9513 (PST Time Zone) Inquiry is fatal to certainty. -- Will Durant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication from 2 Master
Sounds very biblical. :) Wouldn't there be a way to do this with two copies of mysql that share a common data dir? I don't know if you could do that with InnoDB, but I wonder if you could with myisam? At least if you had a system where the two sets of tables came from a different master, and there was no overlap, or if the slave was just functioning as a backup, maybe it would be possible and not lead to too much horror? Thanks, Eric At 01:59 PM 8/24/03 -0700, Jeremy Zawodny wrote: On Sun, Aug 24, 2003 at 09:03:16PM +, [EMAIL PROTECTED] wrote: Hi, I am running MySQL V4.0.14 with replication. I want to replicate specified databases from 2 different masters into one slave. Is this possible? No. A slave may only have one master. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 23 days, processed 1,072,854,858 queries (536/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] (250) 655 - 9513 (PST Time Zone) Inquiry is fatal to certainty. -- Will Durant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mast-Master Replication
Hi, I wish I could use that more, but load data from master locks everything all at once, then you have to wait for the transfer, making it not a great idea to use on a busy live/big database. I can do a back up localy and then transfer the data with a lot less locked time. Still, the times I have used load data from master, it is pretty cool how fast it goes. If the database isn't live and large :) it is defiantly a cool way to do your setup. Thanks, Eric At 01:33 PM 8/20/03 -0500, Hans van Harten wrote: Jeremy Zawodny wrote: On Tue, Aug 19, 2003 at 01:52:26PM -0700, Sanya Shaik wrote: I am unable to find any information about master-master replication. I need to replicate 1 mysql server over to other as a standby master server. It's named circular master-slave ... http://www.mysql.com/doc/en/Replication_Features.html If the second server is merely standby, you probably want master/slave rather than master/master. Having a master available while rebuilding the primairy server allows 'load data from master' to rebuild the db. HansH -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] This email has been scanned for all viruses by the MessageLabs Email Security System. For more information on a proactive email security service working around the clock, around the globe, visit http://www.messagelabs.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] (250) 655 - 9513 (PST Time Zone) Inquiry is fatal to certainty. -- Will Durant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP or Perl?
The only and best careful way to use a global is not to use one at all!! At 11:38 AM 8/18/03 +0900, Joel Rees wrote: The only advantage of PHP is that it runs faster than Perl which may be important if a lot of people are accessing your web page. Using mod_perl vs. mod_php? or perl with the CGI interface vs. mod_php? (I understand that using mod_perl introduces persistence problems when globals are not carefully used, and I have never seen those problems when using PHP, but that is less about speed than about programming.) -- Joel Rees, programmer, Systems Group Altech Corporation (Alpsgiken), Osaka, Japan http://www.alpsgiken.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] (250) 655 - 9513 (PST Time Zone) Inquiry is fatal to certainty. -- Will Durant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Configure prob with FreeBSD/Linuxthreads
Hi, I don't know if this is all old news now, but I got the exact same error that Jesse did when I ran a copy and paste from your example. When I both switched to sh from csh, and made that big paragraph into a single line, then it worked. So I got though the configure. But on the make I got an error. (yes, I did install linuxthreads from ports) pe -march=pentiumpro -D__USE_UNIX98 -D_REENTRANT -D_THREAD_SAFE -DHAVE_BROKEN_REALPATH -I/usr/local/include/pthread/linuxthreads -felide-constructors -fno-rtti -fno-exceptions -fno-implicit-templates -fno-exceptions -fno-rtti -DMYSQLD_NET_RETRY_COUNT=100 -DHAVE_BROKEN_REALPATH -c -o sql_map.o `test -f sql_map.cc || echo './'`sql_map.cc source='mysqld.cc' object='mysqld.o' libtool=no depfile='.deps/mysqld.Po' tmpdepfile='.deps/mysqld.TPo' depmode=gcc /bin/sh ../depcomp cc -DMYSQL_SERVER -DDEFAULT_MYSQL_HOME=\/usr/local/mysql\ -DDATADIR=\/usr/local/mysql/var\ -DSHAREDIR=\/usr/local/mysql/share/mysql\ -DHAVE_CONFIG_H -I. -I. -I.. -I../innobase/include -I./../include -I./../regex -I. -I../include -I. -O3 -DDBUG_OFF -O -pipe -march=pentiumpro -D__USE_UNIX98 -D_REENTRANT -D_THREAD_SAFE -DHAVE_BROKEN_REALPATH -I/usr/local/include/pthread/linuxthreads -felide-constructors -fno-rtti -fno-exceptions -fno-implicit-templates -fno-exceptions -fno-rtti -DMYSQLD_NET_RETRY_COUNT=100 -DHAVE_BROKEN_REALPATH -c -o mysqld.o `test -f mysqld.cc || echo './'`mysqld.cc mysqld.cc: In function `int main(int, char **)': mysqld.cc:1961: implicit declaration of function `int pthread_setprio(...)' *** Error code 1 Stop in /usr/local/mysql-4.0.9-gamma/sql. *** Error code 1 I have gcc version 2.95.4 20020320 [FreeBSD] # uname -a FreeBSD local.host 4.7-RELEASE FreeBSD 4.7-RELEASE #0: Wed Oct 9 15:08:34 GMT 2002 But now, I find that sysctl hw.ncpu says *ONE* not 2 so my problems are a little bigger than just compiler stuff. Thanks, Eric At 11:36 PM 1/28/03 -0800, Jeremy Zawodny wrote: On Tue, Jan 28, 2003 at 04:28:53PM -0500, Jesse Sheidlower wrote: Out of curiosity, which version of gcc are you using? As I posted a few lines up, it's gcc 2.95.4 ;-) Oh, err. Hm. Right. I can read. Yeah. After I sent the original message, I tried to play around with the configure variables, and discovered that it only worked by eliminating the entire '--with-named-thread-libs=-DHAVE_GLIBC2_STYLE_GETHOSTBYNAME_R\ -D_THREAD_SAFE -DHAVE_BROKEN_REALPATH\ -L/usr/local/lib \ -llthread -llgcc_r' group; I tried removing each one individually and it failed each time with the checking size of char... configure: error: cannot compute sizeof (char), 77 error. Hmm. I've since given up, installed with the exact configure line shown in the MySQL docs, FreeBSD section, and it worked perfectly, so I'm worrying about moving my grant tables from 3.23.49 and so forth, instead of getting Linuxthreads to workBut I'd be happy to try to get this fixed, especially if it will help others. If you're interested, I could make my FreeBSD binary availale just to see if it runs on your system. I wouldn't expect you to run it for real--just see if it starts. I'm a little curious now to see if my builds even work on a non-Yahoo version of FreeBSD. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.8: up 1 days, processed 36,740,584 queries (336/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php (250) 655 - 9513 (PST Time Zone) Inquiry is fatal to certainty. -- Will Durant - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Configure prob with FreeBSD/Linuxthreads -- path to linuxthreads includes?
Hi, One other problem came up, the configure script looks for LinuxThreads in /usr/include where of course FreeBSD stores them in /usr/local/include/linuxthreads so the flag that gets set for linuxthreads doesn't, at least not in my version of mysql 4.09 Thanks, Eric (250) 655 - 9513 (PST Time Zone) Inquiry is fatal to certainty. -- Will Durant - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
slave pukes with hostname change
Hi, Ok, I am begging now. Has no one ran into this problem? I can't believe it would not come up, it is the only thing that mysql does wrong with a hostname change. All of the log files for example just are rewritten with the new hostname. I know one way I could fix it, RESET MASTER on the master RESET SLAVE on the slave, but that is a little gross. Thanks, Eric = How can I prevent this and what causes it? I am using mysql 4.02 on FreeBSD. This is running as a slave. When I change my machine's hostname mysql starts up fine, but when I slave start replication I get an unable to initial Master.info error. If I change the hostname back to the old hostname I can start the slave. I have been doing this for some time, when I have to reboot(not often but...), I have to change my hostname back to the mysql happy hostname, shut it down with the rc.mysql script, then restart mysql then change my hostname back so that it is correct and so my mail can get out. Please help, this is getting to be a pain. Thanks, Eric (250) 655 - 9513 (PST Time Zone) Inquiry is fatal to certainty. -- Will Durant - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: slave pukes with hostname change
Hi, sorry, I didn't realize that. The slave's hostname. Thanks, Eric At 08:01 PM 12/15/02 -0800, Jeremy Zawodny wrote: On Sun, Dec 15, 2002 at 07:34:49PM -0500, Eric Frazier wrote: Hi, Ok, I am begging now. Has no one ran into this problem? I can't believe it would not come up, it is the only thing that mysql does wrong with a hostname change. All of the log files for example just are rewritten with the new hostname. I know one way I could fix it, RESET MASTER on the master RESET SLAVE on the slave, but that is a little gross. You never even told us which hostname changed: the master or the slave? -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 0 days, processed 34,028,450 queries (413/sec. avg) (250) 655 - 9513 (PST Time Zone) Inquiry is fatal to certainty. -- Will Durant - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Removal of Primary Key in Mysql
Hi, This is a why questionk, which may be somewhat pointless, but. Why is this under alter table instead of drop index? Thanks, Eric At 01:10 AM 11/5/02 -0600, Paul DuBois wrote: At 12:08 +0530 11/5/02, Uma Shankari T. wrote: Hello, I have set one of my field in the mysql table as primary key..no i want to remove that primary key setting in mysql..Can anyone please tell me how to do that ??? Regards, Uma ALTER TABLE tbl_name DROP PRIMARY KEY; - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php (250) 655 - 9513 (PST Time Zone) Inquiry is fatal to certainty. -- Will Durant - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
About the Changelog for 4.0.3
Big cleanup in replication code (less logging, better error messages, etc..) I have been having periodic problems with lost connections with my 4.0.2 slave and master. No errors, other than lost connection, retrying etc. But sometimes the connection doesn't come back even though show slave status indicates that the slave is still running. A slave stop, slave start gets things going again. But I was wondering if these changes mentioned above would help me in any way, and would I be able to benifit from them just by upgrading my slave machine? Thanks, Eric below is a bit of my error log I am using InnoDB.. 021015 14:06:44 InnoDB: Out of memory in additional memory pool. InnoDB: InnoDB will start allocating memory from the OS. InnoDB: You may get better performance if you configure a bigger InnoDB: value in the MySQL my.cnf file for InnoDB: innodb_additional_mem_pool_size. 021016 8:41:12 Error reading packet from server: (server_errno=1159) 021016 8:41:12 Slave I/O thread killed while reading event 021016 8:41:12 Slave I/O thread exiting, read up to log 'www200-bin.008', position 7684721 021016 8:41:12 Error reading relay log event: slave SQL thread was killed 021016 8:41:12 Slave SQL thread exiting, replication stopped in log 'www200-bin.008' at position 7684721 021016 8:41:15 Slave SQL thread initialized, starting replication in log 'www200-bin.008' at position 7684721, relay log './s142-17-103-3-relay-bin.047' position: 1034333 021016 8:41:15 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'www200-bin.008' at position 7684721 021016 17:17:51 Error reading packet from server: (server_errno=1159) 021016 17:17:51 Slave I/O thread killed while reading event 021016 17:17:51 Slave I/O thread exiting, read up to log 'www200-bin.008', position 8248472 021016 17:17:51 Error reading relay log event: slave SQL thread was killed 021016 17:17:51 Slave SQL thread exiting, replication stopped in log 'www200-bin.008' at position 8248472 021016 17:17:54 Slave SQL thread initialized, starting replication in log 'www200-bin.008' at position 8248472, relay log './s142-17-103-3-relay-bin.047' position: 1598084 021016 17:17:54 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'www200-bin.008' at position 8248472 021016 22:36:47 Error reading packet from server: Lost connection to MySQL server during query (server_errno=2013) 021016 22:36:47 Slave I/O thread: Failed reading log event, reconnecting to retry, log 'www200-bin.008' position 8481953 021016 22:36:47 Slave: connected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'www200-bin.008' at position 8481953 % (250) 655 - 9513 (PST Time Zone) Inquiry is fatal to certainty. -- Will Durant - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Are there ANY terminal-based frontends for Linux?
Hi, Lots of people will end up wanting to forget about the terminal part of your question and just say MS Access. I would not love to have to deal with a large number of people using Access, even with mySQL behind it. Maybe you can find something that uses the ncurser lib? That would be my first suggestion. One thing about your question, it makes me think also of all of the, well just use the web people. That is simple right? But then how much crap goes behind making a decent interface for the web and also running it on computers that are new enough to support it. A ncurser type system would be able to run until the hardware dies. However, when I look at things like http://search.cpan.org/author/WPS/Curses-1.06/Curses.pm I see a lot of problems and more complexity that I would have hoped for. It makes me think of AS/400's and the fact that that simple looking green screen, isn't really so simple, and it is VERY limiting in that it will be harder to find people to work on the interface, than if you had used HTML. Maybe some good old HTML 1.1 and Lynx would do what you need? Eric mysql sql blah blah... Stupid filters. At 10:55 PM 10/13/02 -0400, Chip Rose wrote: Are there ANY MySQL terminal-based frontends (for Linux) that will allow inputting data via forms, queries,reports? There are a lot of administration tools - that's not what I want. How do I set something up for inputting and simple queries that takes advantage of the *relational* database? The things I've seen all look like flat-file stuff. How do most people input and display their data? Doing it via a terminal, the output is skewed and doesn't line up because too much info on a line, etc. I see programs that the banks use, that OfficeMax uses, my car repair place - they all input data and have displays all set up on their computers - simple terminal apps of some kind. Just a simple terminal based program like that would do. I thought about PHP/Apache, but is there anything other than that? I want to set up a database and *easy* interface for a multi-faceted client history transaction tracker. Help/Thanks! Chip Rose - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php (250) 655 - 9513 (PST Time Zone) Inquiry is fatal to certainty. -- Will Durant - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: A FAQ type question, but not in the FAQ - How to change my hostname, but not make replication puke
Hi, The mysql master wasn't restricting the slave by ip. But thanks, Eric At 10:28 AM 9/30/02 -0500, gerald_clark wrote: You need to grant privileges on the master to the new slave machine. http://www.kwinternet.com/eric (250) 655 - 9513 (PST Time Zone) Inquiry is fatal to certainty. -- Will Durant - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: A FAQ type question, but not in the FAQ - How to change my hostname, but not make replication puke
Hi, I didn't have a new mysql master, just the slave name was changed. Yes on the second question too. Thanks, Eric At 09:09 AM 9/30/02 -0500, gerald_clark wrote: Did you make the changes to master.onfo to point to the new master? Did mysql own master.info when you were through? Eric Frazier wrote: Hi, I changed my hostname(The DNS change was taken care of elseware) in FreeBSD 4.6 in the rc.conf file, rebooted. Mysql had no problems starting, but it failed to start the slave. In the new error log I saw: 020927 10:15:58 mysqld started 020927 10:15:59 InnoDB: Started 020927 10:15:59 Could not find target log during relay log initialization 020927 10:15:59 Warning: Can't create threads to handle slave /usr/local/mysql/libexec/mysqld: ready for connections 020927 10:16:15 Could not find target log during relay log initialization and I got a 'check permissions on master.info' sort of error when I tried to run 'slave start' I see that error was related to a bug from a long time ago, but I am running 4.0.2 so I doubt that it is a bug issue. Thanks, Eric Leading Edge Marketing Inc. 250-360-2992 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php http://www.kwinternet.com/eric (250) 655 - 9513 (PST Time Zone) Inquiry is fatal to certainty. -- Will Durant - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Rephrasing a question - RESET SLAVE
Hi, I was having problems because I had to rename my slave sever. Now I think I understand the problem better. I looked in slave.cc and found my error message and can see that it only applies to the slave. So I guess that brings up two issues. 1. Why can't the relay-log deal with the name change that the binary log has no problem with? The new binary log files just get created on restart after I change the hostname. 2. I have a running slave, what should I do to be carefull and safe and not mess up any data before I run RESET SLAVE? This command scares me. I don't like the sound of forgeting the binlog position. Does that mean if I do that without clearing out my relay-log that I will end up replaying the whole thing? And last of all, would it be best to run reset slave after I have my new hostname? FreeBSD 4.6 mysql 4.0.2 slaving off of a remote server. The remote server is only binloging one database, and the slave is only replicate_do that one database. The Master has many many databases. Thanks, Eric http://www.kwinternet.com/eric (250) 655 - 9513 (PST Time Zone) Inquiry is fatal to certainty. -- Will Durant - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
A FAQ type question, but not in the FAQ - How to change my hostname, but not make replication puke
Hi, I changed my hostname(The DNS change was taken care of elseware) in FreeBSD 4.6 in the rc.conf file, rebooted. Mysql had no problems starting, but it failed to start the slave. In the new error log I saw: 020927 10:15:58 mysqld started 020927 10:15:59 InnoDB: Started 020927 10:15:59 Could not find target log during relay log initialization 020927 10:15:59 Warning: Can't create threads to handle slave /usr/local/mysql/libexec/mysqld: ready for connections 020927 10:16:15 Could not find target log during relay log initialization and I got a 'check permissions on master.info' sort of error when I tried to run 'slave start' I see that error was related to a bug from a long time ago, but I am running 4.0.2 so I doubt that it is a bug issue. Thanks, Eric Leading Edge Marketing Inc. 250-360-2992 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqlbinlog - doesn't work on some log files
Hi, I tried using the mysqlbinlog from 3.23.52 MAX with no difference in the output at all from 4.0.2 It seems that whatever happened truely caused the log files to become corrupted So my next question would be, is there a way to repair the log files? What are the formating rules for the log files? It is frustrating to be able to look at my data in an editor, but not be able to restore it. Also it sounds like from Heikki Tuuri's response that he thought this could have been somehow caused by a replication related bug. Thanks, Eric - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysqlbinlog - doesn't work on some log files
Hi, I deleted a db I didn't mean to. I am using mysql 4.0.1 Alpha and some innodb and some myisam tables. I have the log files I need to restore my data since I have been running since the last backup with binlog enabled in my.cnf. The problem is that mysqlbinlog shows some log files, but other log files choke. In VI I can see this at the top of one of one log file: þbin¢.=.K.. 0010 00 01 00 00 00 00 00 02 00 34 2e 30 2e 31 2d 61 .4.0.1-a 0020 6c 70 68 61 2d 6c 6f 67 00 00 00 00 00 00 00 00 lpha-log 0030 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 0040 00 00 00 00 00 00 00 00 00 00 00 a2 3e 06 3d 77 ...¢.=w 0050 40 06 3d 02 01 00 00 00 f2 00 00 00 02 00 00 00 @.=.ò... 0060 00 00 c1 1b 00 00 00 00 00 00 0a 00 00 73 65 6e ..Á..sen 0070 73 69 74 69 6c 6c 65 00 55 50 44 41 54 45 20 75 sitille.UPDATE u 0080 61 5f 70 72 69 6d 61 72 79 5f 68 69 74 73 20 53 a_primary_hits S 0090 45 54 20 31 31 64 61 79 20 3d 20 28 31 31 64 61 ET 11day = (11da 00a0 79 20 2b 20 31 29 2c 20 31 31 75 5f 64 61 79 20 y + 1), 11u_day 00b0 3d 20 28 31 31 75 5f 64 61 79 20 2b 20 31 29 2c = (11u_day + 1), 00c0 20 4d 6f 6e 74 68 5f 54 6f 74 61 6c 20 3d 20 28 Month_Total = ( 00d0 4d 6f 6e 74 68 5f 54 6f 74 61 6c 20 2b 20 31 29 Month_Total + 1) 00e0 2c 20 4d 6f 6e 74 68 5f 55 6e 69 71 75 65 20 3d , Month_Unique = 00f0 20 28 4d 6f 6e 74 68 5f 55 6e 69 71 75 65 20 2b (Month_Unique + but mysqlbinlog only shows this for this 4.8M file: root@www194:/home/back# mysqlbinlog www194-bin.001 # at 4 #020611 11:17:06 server id 1 Start: binlog v 1, server v created 691231 16:00:00 # at 73 #030419 19:03:44 server id 104888125 Query thread_id=15859712 exec_time=131072 use ; LOAD DATA INFILE '' REPLACE INTO TABLE nsitille OPTIONALLY ENCLOSED BY '\0' ESCAPED BY '\0' LINES STARTING BY '\0'; I had been running this machine as a Master to a remote slave. And some of the logs are large because I ran LOAD DATA FROM MASTER a few times from the slave machine. So I have the data I need but I can't access it. I have a total of 8 log files, only two work with mysqlbinlog. The rest return very shortly like above. This is really freaking me out, if this doesn't work, then I will have to start not trusting the log at all, which would suck. I have been able to restore data before, in the same way, from this same database and config. Thanks, Eric - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Replication with INNODB
Hi, I am confused. I just tried replication with 4.0.2 on master and slave, and it appeared to work with the InnoDB tables on the Master.. What is the expected issue, or error that happens that causes the manual to say that Replication doesn't work yet with InnoDB at least not when you use LOAD DATA FROM MASTER? That is how I updated my slave, and as I said it seemed to work fine. I need to Replicate InnoDB tables, but I am worried that the manual says LOAD DATA FROM MASTER won't work. So is Replication ok with InnoDB, once you get the servers synced? Thanks, Eric At 12:48 AM 8/16/02 -0400, Serge Paquin wrote: Hello, I am trying to setup replication to be used basicaly as a hot backup. My production database uses INNODB tables. I would like my Replication database to just my MyISAM since I do not need transactions on that one. Just a copy of the data. I followed the process to create the slave but since I am using INNODB tables tar'ing up the mysql directory and moving it to the slave does not work. What must I do? Would I be best to export everything to a file using mysqldump then reimport it? Thanks, Serge. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php http://www.kwinternet.com/eric (250) 655 - 9513 (PST Time Zone) Inquiry is fatal to certainty. -- Will Durant - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
DBI weird and unheard of issues with mySQL?
Hi, This is a problem of the sort that is starting to make me a little supersituous. I am using mySQL various versions, one of which is 4.01 max on FreeBSD 4.5 stable, DBI 1.30 and I believe the latest DBD::mysql module. Well the weird thing that is happening is that on this particular machine when I use fetchall_arrayref, I end up missing the last column that I should return. I tested this over and over with different queries and databases. Then I moved some things over to a Slackware machine and found no problems. So I thought, oh it must be a FreeBSD thing, somehow. So I took my queries home to my other FreeBSD 4.5 stable and tested, fetchall_arrayref works as expected, but I thought, oh I have 3.x not 4.x, so I installed 4.02 Max and still all is well. So now I am thinking, What do I do with this other machine? Do I upgrade to 4.02? Downgrade to 3.x? Install FreeBSD 4.6 and hope that the gods are pleased? This is just so weird and I don't really know who to turn to. I have posted to the FreeBSD newsgroup misc, and the DBI mailing list, no responses so far. Maybe people just think I am nuts? :) I wouldn't blame them. But if the problem had anything to do with my test code it would not have worked on the Slackware machine and my home FreeBSD machine. The scary thing is the home machine is identical to my work machine except that it has been worked on a lot more, and so a few more perl modules are hanging around, and the work machine has 4.01, the home machine now has 4.02. Thanks, Eric http://www.kwinternet.com/eric (250) 655 - 9513 (PST Time Zone) Inquiry is fatal to certainty. -- Will Durant - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: help me out here guys... you gotta have a primary key
Hi, Is it such a big deal to use more than one field for a primary key? Two field keys are only a little slower than single field Primary keys for selects. Often in linking tables you want to have duplicates of the two foreign keys right? I just wish people would stop making 100 field tables, then I would be happy. Ever do an insert on a 78 row table that has all fields set to NOT NULL, yet you only need to insert about 15 columns worth of data? It isn't pretty.. And BTW, do people not ever read the mySQL site? Ever heard of InnoDB, Berkely DB? Yes, foreign keys, even cascade deletes. Is this going to become one of those Perl sucks because it is CGI kind of things? mySQL truly rocks, it keeps getting better and better. Actualy I started a project recently where I thought I would go ahead be a good boy, and use foreign keys, it ends up that I just can't think of a good reason for it in my particular situation. I was kind of disappointed really.. But I am very happy to know that at any time, if I want transactions and foreign keys, it is a few keystrokes away. Eric PS why oh why do people make 100 field tables! At 11:35 PM 7/27/02 -0500, Dave Dutcher wrote: Well, there are people who feel that tables should be linked by foreign keys to ensure referential integrity. Everyone who uses MySQL gets by without them though. Its up to your coding to make sure referential integrity is not violated. Although foreign keys are left out of MySQL for performance reasons, and there is ussualy not much performance hit for having a primary key and most of the time there is probably a performance gain. So I think generally a primary key is a good thing, unless you have a strange situation where for performance reasons (speed or size) a primary key doesn't make sense. Dave -Original Message- From: Desmond Lee [mailto:[EMAIL PROTECTED]] Sent: Saturday, July 27, 2002 9:27 PM To: [EMAIL PROTECTED] Subject: help me out here guys... you gotta have a primary key k This is seems ridiculous to me,.. but i'll let you tell me if i'm just being stubborn. So, one of my associates has made a linking table (some people also call it intersection table, cross tab table, but i believe that the propper way to model a many to many relationship is via a linking table). in the linking table, there is no primary key defined. I believe that every table must have a primary key. It is absolutely essential, otherwise you'll get tons of problems including redundancy, and inconsistency. However, my associate believes that our coding will ensure that such problems will be avoided and that it's okay for a table to have no primary key defined. I totally disagree. Even if our code is perfect, a primary key must be defined. So, am i correct in being concerned, or am i just being close minded? If, i'm totally wrong, in what situations is it a good idea, okay, or benificial to not have a primary key defined for a table? Thanks Desmond (sql) _ Send and receive Hotmail on your mobile device: http://mobile.msn.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php http://www.kwinternet.com/eric (250) 655 - 9513 (PST Time Zone) Inquiry is fatal to certainty. -- Will Durant - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
JDBC Date Fields, can't seem to INSERT with setDate or setTimestamp
Hi,
I looked at the Date example that comes with the mm.mysql driver, but I
still can't get this to work.
When I run the code below, I have no errors, but I only end up with nulls
in my table.
This is getting weird..
Thanks,
Eric
==
import java.sql.*;
import java.util.*;
import java.util.Date;
public class mysqldatetest {
public static void main(String[] args) {
// Try to load the JData2_0 driver
try {
//Driver drv = (Driver)
Class.forName(JData2_0.sql.$Driver).newInstance();
Class.forName(org.gjt.mm.mysql.Driver).newInstance();
}
catch (Exception e) {
System.out.println(Cannot load the driver, reason:+e.toString());
System.out.println(Most likely the Java class path is incorrect.);
}
try {
// Change MyDSN, myUsername and myPassword to your specific DSN
//Connection c
=java.sql.DriverManager.getConnection(jdbc:JDataConnect://216.17.163.114/test);
Connection c
=java.sql.DriverManager.getConnection(jdbc:mysql://192.168.0.12/CustomerService?user=userpassword=password);
c.setAutoCommit(true);
Statement stmt = c.createStatement();
stmt.executeUpdate(DROP TABLE date_test);
stmt.executeUpdate(CREATE TABLE date_test (datefield datetime
,datefield2 datetime ));
PreparedStatement pStmt = c.prepareStatement(INSERT INTO date_test
(datefield,datefield2) VALUES(datefield=?, datefield2=?));
// make a date type
java.sql.Date date = new java.sql.Date(new Date().getTime());
java.sql.Date date2 = new java.sql.Date(2002,3,22);
Timestamp time = new Timestamp(date.getTime());
long startDate = 101768400L;
System.out.println(Date:+ time);
//for (int i = 0; i 10; i++) {
pStmt.setTimestamp( 1, new Timestamp( date2.getTime()) );
pStmt.setTimestamp( 2, new Timestamp(startDate) );
pStmt.executeUpdate();
//}
pStmt.close();
}
catch (Exception e) {
System.out.println(Error connecting or reading table:+e.getMessage());
e.printStackTrace(System.out);
}
}
}
Leading Edge Marketing Inc.
250-360-2992
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: AUTO_INCREMENT with Replication
Hi, That kind of bothers me. But I can see how it might be better in some way than using a time function. My idea was to use the perl Time::HiRes to make a unique key adjusted by timezone. As long as the clocks of the two machines are fairly in since, it should work :) I guess I could also add in a unique machine id like time integer key + M for master or S for slave S2 S3 etc. Does that sound insane? Thanks, Eric mysql is good At 05:16 PM 2002-07-03 -0700, you wrote: to somewhata simulate auto increments, each slave (and master) could be periodically assigned a chunk of keys to use, with the stipulation that only that server can use those keys. for instance at time 0 (arbitrarily) server 1 gets: 0-999 server 2 gets:1000-1999 server 3 gets:2000-2999 etc you'll have to keep track of this info on each server, whether in a table or file. then when that chunk is used up, that client can request another chunk of keys. (you could also update those key chunks daily, weekly, etc. instead.) The size of the chunks would want to be determined by the application, frequency of communication between master slave, and volume of records going in. I have *not* implemented such a system, but this could work, depending on your app. sean - Original Message - From: Eric Frazier [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, July 03, 2002 2:32 PM Subject: Re: AUTO_INCREMENT with Replication Well. Good to know. So I guess the only alternative would be to generate keys by date/time? I was hoping to avoid that. I am still worried about the timestamp type not having good enough resolution. Seconds are pretty broad. Thanks, Eric You're asking for trouble. :-) AUTO_INCREMENTS are not safe for use in a mutli-master environment. The scenario you painted will result in a primary key violation on the master when it reads the value inserted on the slave. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.51: up 34 days, processed 779,275,123 queries (258/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Leading Edge Marketing Inc. 250-360-2992 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Leading Edge Marketing Inc. 250-360-2992 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
BigINT inserts
You can always store an exact integer value in a BIGINT column by storing it as a string. In this case, MySQL will perform a string-to-number conversion that involves no intermediate double representation. I don't understand this, does this mean that the fastest way to insert bigint values will always be to insert them as strings, =2423423 vs =3242342? Why is there a double representation, what is its perpose? Thanks, Eric - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
AUTO_INCREMENT with Replication
From the manual 4.10.4 Replication will be done correctly with AUTO_INCREMENT, LAST_INSERT_ID(), and TIMESTAMP values. I am somewhat fearful and curious about how this works. Say we have a master web database that gets replicated back to the office slave over the Internet. A person on the web puts in an order to the master web db, another person in the office enters a phone order, but that order goes into the slave because orders get shipped based on information in the office slave. How would I not at some point end up with replication errors because of duplicate auto_inc values? Would setting up replication as a circle help? Or would timing issues still cause a problem? (The insert on the Master beats the insert on the slave that was getting sent at the time) I am using 4.0.2 alpha so I am most concerned with how that version is affected. Thanks, Eric sql,querysql,querysql,querysql,querysql,querysql,querysql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: AUTO_INCREMENT with Replication
Well. Good to know. So I guess the only alternative would be to generate keys by date/time? I was hoping to avoid that. I am still worried about the timestamp type not having good enough resolution. Seconds are pretty broad. Thanks, Eric You're asking for trouble. :-) AUTO_INCREMENTS are not safe for use in a mutli-master environment. The scenario you painted will result in a primary key violation on the master when it reads the value inserted on the slave. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.51: up 34 days, processed 779,275,123 queries (258/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Leading Edge Marketing Inc. 250-360-2992 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Undo query in mysql
Hi, Kind of a quick answer huh? If he had binary logging enabld and the last inserts or updates are still in the log, he can get his data back. mysqlbinlog and some greping and seding. I dropped a database and while it wasn't super easy to do, I did get it back. Eric At 09:17 AM 6/16/02 -0500, Jason Englehardt wrote: On Sun, 16 Jun 2002, mohamadally wrote: Hi all, I accidently deleted some values in table using mysql . Is there any way to undo the query ? No, there is not, unless the table was transactional and you were not in autocommit mode. This is where backups come in handy. http://www.mysql.com/doc/A/N/ANSI_diff_Transactions.html Regards, Jason [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php http://www.kwinternet.com/eric (250) 655 - 9513 (PST Time Zone) Inquiry is fatal to certainty. -- Will Durant - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Slave logs, and defaults
Hi, I had an odd thing happen yesterday. I had replication running, the master being on a server farm, the slave being in our office. I tested it early in the morning by creating a table on the master, it showed up on the slave right away. Of course as soon(a few hours later) as I went to show my boss how cool replication is, it didn't work. I created a table on the master and it didn't show up on the slave. The next day, I came in and found the table was sitting on the slave as it should be. Looking in the slave log file I found the below: 020604 9:08:58 Slave: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'www194-bin.001' at position 946 020604 17:48:22 Error reading packet from server: Lost connection to MySQL server during query (read_errno 22,server_errno=2013) 020604 17:48:22 Slave: Failed reading log event, reconnecting to retry, log 'foobar-bin.001' position 43408 020604 17:48:27 Slave: connected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'foobar-bin.001' at position 43408 I didn't lose the connection to the server farm since I was sshed in at the time too. But still it seems to have just stopped for a time because of this error. That brings up the other question of what is the default connection retry? slave_net_timeout Number of seconds to wait for more data from a master/slave connection before aborting the read. I read this above, but could not find out what the default value is. Thanks, Eric - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysqldump -A dump.txt
Hi, I didn't get that deeply into why exactly, but it seems that when I removed the mysql database from the file, that the rest went fine with mysql -u -p dumpfile I got an error about the column_prv field already existing I believe. Sorry, I should have been much more specific. I appreciate your answering even though I was vague. I think mostly I learned my lesson, and I should export one database at a time, with --tab= Thanks, Eric At 01:52 AM 6/3/02 +0200, Benjamin Pflugmann wrote: Hi. Which version of mysqldump did you use and what error message do you get? I have never used mysqldump this way but from what I read in the help of mysqldump 3.23.31, mysqldump should insert the necessary SQL statements regarding changing and creating the databases (if they do not already exist). Bye, Benjamin. http://www.kwinternet.com/eric (250) 655 - 9513 (PST Time Zone) Inquiry is fatal to certainty. -- Will Durant - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysqldump -A dump.txt
Hi, How do I deal with the import of this file? Every example I see involves a dump of a single database, or else uses a command line like my subject, but with no corresponding mysqlimport. If I use mysql dump.txt then I end up with errors that stop the process. With mysqlimport I can use --force, but I don't have that option with mysql dump.txt Thanks, Eric - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: A COMPANY RELEASES A NON-FINAL VERSION OF SOME PRODUCT NON-PREMIUM EDITION
Hi, This is complete crap. One of the tools I use is because of an email I saw on this list that might have been called spam. I say that this type of email is on topic. Maybe they could have eased up on the BS tone of the email, but I would still want to know about a new product. The only other question that comes up is would it be worthwhile to setup a mySQL announce list for 3rd party tools? I don't exactly see dozens of these types of email a day right now though. Eric At 08:44 AM 10/18/01 -0700, Jonathan Hilgeman wrote: The harm doesn't come from you offering something beneficial to the MySQL community. It comes when you abuse the privileges of being on this list and send spam. http://www.kwinternet.com/eric (250) 655 - 9513 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL vs. AS/400
You forgot to metion the great new feature becoming available. Secure telnet, it never existed before very recently for AS/400. Secure huh? Yeah. There are companies making web apps for the AS/400 that are advertising that you can use the web and it is more secure than the traditional 5240 clients because of SSL. Eric At 11:30 PM 9/11/01 -0400, Lance Rochelle wrote: OK mine are more respected :) j/k another point of view. 1st You are assuming this only runs on Intel machines. I have MySQL running on a Sun E-4500 (8 x400mhz procs / 8GB RAM) running Solaris 8 on a financial institution on a very mission critical application. 2nd IBM support is piss poor at best unless you are on their 'call list' in which is it is almost as good as Microsoft's, plus the amount of money you would pay for support I better get a Full Time SE on-site. 3rd You are assuming that everyone runs this on a ISP budget. We have ours attached to an EMC array snapshot backups, well you got the idea. three way mirror with another machine that is attached the EMC the third mirror breaks we back it up then put the 3rd mirror back in-line. 4th a full TCP stack don't you mean a full IP stack. 5th Java is now available, I have been using that for almost 2 1/2 years now. 6th Security is best left up to the Security person (a good SA can secure a system) but then again the only true secure system is one that is turned off and locked in a closet. Where everyone who had a key to the closet melted it down and it requires a retina scan from JFK. (wait that might be to much) I do have to give IBM so credit some of their apps are Open System 'like'. http://www.kwinternet.com/eric (250) 655 - 9513 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Multi primary keys
Hi, I just discovered with mysql 3.22.32 that it is possible to make a table with more than one primary key. Shouldn't that be impossible? Thanks, Eric Frazier Consulting http://www.kwinternet.com/eric (250) 655 - 9513 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Compatable SQL for indexes
Hi,
I am tring to find the most compatable way to write CREATE TABLE statments
between mySQL and Postgress.
Postgress has a CREATE INDEX as does mySQL, but I would like to be able to
create Primary Keys and Indexes in the CREATE TABLE statment.
The problem I am running into is that Postgress doesn't have a INDEX
[index_name] (index_col_name,...) option, it only has
CONSTRAINT constraint_name ]
{ UNIQUE ( column_name [, ... ] ) |
with the mysql version being below
INDEX [index_name] (index_col_name,...)
orUNIQUE [index_name] (index_col_name,...)
When you use UNIQUE in postgress it automaticly creates an index as a
result. But it looks to me that this would not happen in mySQL.
Is that correct?
I am guessing that I am best off creating primary keys as a part of the
table create statement, but making indexes later on with CREATE INDEX
which is mostly the same between the two DBs
Does that make the most sense?
Thanks,
Eric
Frazier Consulting
http://www.kwinternet.com/eric
(250) 655 - 9513
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: XML support under mySQL
One thing I want. A Java way to save a data structure and recover it later. Easy in perl, not so easy in Java. But XML would be a great way to do it in Java. Eric At 10:42 AM 2/22/01 +1000, Opec Kemp \( Ozemail \) wrote: *big snip* I agree with Cal, the XML module should really be sperated from the RDMB. XML is really great but, lets face it not everyone is going to use it so why force it down thier troat?. The really great thing about Open Source is that you do have a choice (unlike MS , Oracle). :) You have to choice to install external XML modules if you wish, if not why would you use it? I'm sure if you write the XML modules as an extension to MySQL in C or C++, it'll be just as fast as if it is built in. Not to maintion the fact that it'll be far easier of MySQL developer to put in other really "useful" RDMB related features like ForeinKeys etc etc. instead of "cool" but not critical features XML. And the code base for MySQL wouldn't be bloated either which means we as the users won't have to download 200MB RDMB servers :):) My $0.02 But you've yet to make a case for extending a database engine to do something it's not originally designed to do and something that I argue does not belong in a RDBMS engine. First, while I agree that XML is a great solution for 2 applications to exchange data, it is not a - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Frazier Consulting http://www.kwinternet.com/eric (250) 655 - 9513 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
varchar and sql92
Hi, I am writing a program to do queries on a lot of different databases. I have run into something with varchar I didn't expect. With mySQL varchar is limited to 255 with postgress it is unlimited it seems. I only know because it seems to work, not because their stupid documentation told me, that you can have any length varchar. For instance I have a varchar 4000 in a postgress database. My question is, how standard is mySQL being by limiting varchar to 255 ? I will have to make some provision for this because I want my queries to work with postgress as well, but I wondered too if I should look into other issues like this with lengths of fields? Thanks, Eric Frazier Consulting http://www.kwinternet.com/eric (250) 655 - 9513 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL vs Access; you'd *think* the choice is obvious...
Hi, One thing I have always wondered, how do you deal with table locking if you have a number of people using mySQL with ODBC? Does ODBC handle it? Does Access do it? Can mySQL do it with Berkeley? I am esp talking if you are using mySQL from many different locations and working on the same table. Thanks, Eric At 05:50 PM 2/8/01 -0500, James Treworgy wrote: Actually there is no reason to expect MySQL to perform better than Access for a nominally sized database and everything running on one PC. Access was designed and optimized for JET whereas ODBC is a general-purpose API. Also, the connection method impacts this significantly, as do the complexity of the query. For a simple test I did comparing connection methods from Access check http://www.trewtech.com/sqltest.html However, to see Access flounder horribly, simply put your MDB backend at the other end of an ethernet connection from your frontend. Now, for even more pain, add a few more users connecting to it at the same time. Access is _not_ a server and the kiss of death for an access database is to try to use it as a backend over a network. It's amazing how inefficient a query is when the "server" is actually the network filesystem. Add in absurdly long-running bugs that Microsoft denies exist - well, rather, since it's extremely difficult to actually report a bug to Microsoft without paying them for the privilege, perhaps they just don't know - such as randomly corrupting memo fields - and it's essentially useless for anything but a very small database with a very small number of users, or only running on one PC with no network. Jamie Regards, Jamesmailto:[EMAIL PROTECTED] Thursday, February 08, 2001, 5:02:59 PM, you wrote: Q We've got a server app that does a lot of 'small' database reads and Q writes. We were originally using MS Access via DAO (Jet Engine) and we Q wanted to tighten up DB performance, so we've written a general ODBC Q database wrapper object, but mainly just to connect to MySQL. I figured Q there'd be ODBC overhead, but its a lot worse than I imagined. Q I want to know: does it make sense that our original system, connecting via Q 'Jet-engine' to Access, is actually much faster than connecting to MySQL Q via ODBC? This seems to be what's happened. Q I'm wondering if it has to do with the overhead of connecting to a Q server-based database via a tcp socket (even on localhost) rather than the Q direct-to-disk Jet engine; maybe because we do so many small reads/updates Q it's actually faster with Access? Any thoughts? Is it worth my time to Q look into using MySQL directly instead of thru ODBC? Q I'm obviously working on Windows (NT), connecting at ODBC version 2.0 to Q MySQL server 3.23, using a database converted directly from Access to MySQL Q using the cool (but unstable) DBTools GUI, which kindly retained all keys Q and indexes (which have been reviewed for speed). - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Frazier Consulting http://www.kwinternet.com/eric (250) 655 - 9513 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Version distribution
Hi, I wonder if any of the mySQL people have any guesses or real stats about the number of people using each version of mySQL. I am wondering if I develop a tool that only supports the latest 3.23 version will I cut out a whole lot of people? How long does it tend to take for most people to upgrade? I know my own upgrades tend to come when a get a chance, after a new version is considered stable. I would like to use Transactions with the mm.mysql driver, but it only supports that for 3.23. Thanks, Eric Frazier Consulting http://www.kwinternet.com/eric (250) 655 - 9513 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Sub selects working around
Hi, I am guessing this is the kind of problem that would be easier to solve with a sub select SELECT simpleparts.category,simpleparts.partnumber,simpleparts.manufacturer,simplep arts.descrp,ABS(packagesParts.pri) as abPRI,categories.sortorder FROM ((packagenames LEFT JOIN packagesParts ON packagenames.packageID = packagesParts.packageID) LEFT JOIN simpleparts ON packagesParts.partnumber = simpleparts.partnumber) LEFT JOIN categories ON simpleparts.category = categories.category WHERE simpleparts.configurator 0 AND categories.onconfig 0 AND packagenames.packagename = $system_name AND simpleparts.category = ? ORDER BY abPRI DESC This in one case returns --++--+-+--+ ---+ | category | partnumber | manufacturer | descrp | pri | sortorder | +--++--+-+-- +---+ | Game Controllers | 234232 | Jumbo Video | Video card |1 | 24 | | Hard Drives| 78544 | HTH | a Drive controller |1 |12 | | Floppy Drives | HP-4p | HP | 4P - 48bit |1 |13 | | CPUs | int-550c | Intel| Celeron 500MHZ 128K | 1 | 1 | | Memory | mem-102| who knows| 256M 120pin |1 | 3 | | Scanners | 9955 | Acer | 10 ISA |0 |22 | | Game Controllers | game-01| Atari| Simple stick| 0 |24 | | CPUs | INT550 | INTEL| PIII 550 CPU |0 | 1 | | Memory | mem-101| who knows| 128M |0 | 3 | | Modems | mod-124| USR | sportster 28.8 |0 | 9 | +--++--+-+-- +---+ But I have one more constraint that I would like to be able to include in the query and not deal with in code. I want to not return anything in the case where none of the packagesParts.pri are = 0 this is hard because I do what those packagesParts.pri =0 where there is a packagesParts.pri = 1 in the result set. Is there a way to cram this into my query with mysql as it stands? I saw some people here do some pretty fancy stuff, so I thought it was worth asking. Thanks, Eric Frazier Consulting http://www.kwinternet.com/eric (250) 655 - 9513 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
