Re: Total newbie asking for guidance
if you have a windows box that you can use as a front end, get a copy of corereader. i recommend it to you because, among other things, it's a teaching tool. it allows you to do (genuine) point and click queries. you can do complex queries simply by clicking on selections. furthermore, you'll want to embed sql statements in your code. when you get a query that you like in corereader, you can open its sql statement to cut and paste. it will query any data source, and so far seems to like mysql particularly well. corereader is totally free from http://corereader.com/ . since it's for a non-profit organization, i'll correspond directly to help you get started. data connections can be frustrating. please respond directly to me to avoid cluttering this list. tell me what organization it is to insure that i am not opposed to its objectives. Hello. I am starting a project that requires a simple database for use as a back-end to a web-accessible look-up facility. When I say simple I mean that the data is static and that the queries are done on single keys. The catch is that I have zero experience in any kind of database work. I have been doing software development for 20 years and am proficient in several programming languages but have never had the occasion to do any database programming. I've got some latitude as to what tools are use to achieve this web-accessible database goal, so I'm going with Apache + MySQL in a Linux (Red Hat 7.3) environment. (Cost is a high priority as this is being done for a poverty-stricken non-profit.) I'm very familiar with Linux, somewhat familiar with Apache (v1.3.x) and have no experience whatsoever with MySQL. Would some kind soul(s) please suggest books and/or websites for the MySQL newbie? I've perused the book list at www.mysql.com, but don't have a feel for the experience level that these books are oriented toward. Any pointers that you could provide to this beginner would be very appreciated. Thank you. -- John Ragan [EMAIL PROTECTED] http://www.CoreReader.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
JDBC J/Connect driver is seriously slow against InnoDB
Hi:
Thought I'd mention this (using the latest dev
J/connect):
A)
DatabaseMetaData.getColumns() does not return the last
4 columns (SCOPE_*) but it should according to the
API spec
B)
InnoDB, on their benchmark page, say that inserting
100,000 rows into the DB is about 5 seconds.
http://www.innodb.com/bench.html
I am finding this to be more like 300-400 seconds.
Try this:
CREATE TABLE T1
(A INT NOT NULL AUTO_INCREMENT, B INT, PRIMARY KEY(A))
TYPE=INNODB;
CREATE TABLE T2
(A INT NOT NULL, B INT, PRIMARY KEY(A)) TYPE=INNODB;
Now run the java driver (source shown at the end of
this
message).
This takes a long time. The innodb bench URL mentioned
above uses perl DBI has a test driver, the JDBC
*should* be as fast if I am not missing anything (but
it's not). Once it's finally done, if you now say
(from
the mysql client):
mysql insert into T2 select * from T1;
Query OK, 107825 rows affected (5.23 sec)
Records: 107825 Duplicates: 0 Warnings: 0
Note, this takes about 5 seconds, which shows that
the slowdown is not at the DB level but at the JDBC
driver level (mysql client even sets auto commit
to true by default, and it still takes 5 seoonds).
So why does the JDBC driver take so long ? I am
accessing the mysql machine over a private 100Mbps
connection so I don't think it's the network either.
Best regards,
--j
java driver --
import java.sql.*;
import java.util.*;
import java.io.*;
public class insertTiming {
/* Change these as appropriate */
static String user=CHANGE_ME;
static String password=CHANGE_ME;
static String url=CHANGE_ME;
public static void main(String[] args) throws
Exception
{
Class.forName(com.mysql.jdbc.Driver);
Connection con =
DriverManager.getConnection(url,user,password);
Statement stmt = con.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
/*
Make sure table T1 has been created prior to this
SQL command:
CREATE TABLE T1 (A INT NOT NULL AUTO_INCREMENT, B INT,
PRIMARY KEY(A)) TYPE=INNODB;
*/
con.setAutoCommit(false);
String sql =
INSERT INTO T1 VALUES (null, '1234567890');
for (int n = 0; n 10; n++) {
stmt.execute(sql);
}
con.commit();
} //~main
} //~class
--
__
Do you Yahoo!?
HotJobs - Search new jobs daily now
http://hotjobs.yahoo.com/
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql DATETIME substraction problem/?
Hello! I am working on an analysis that is very much dependent on calculating time differences in seconds. The most simple example: I have 3 variables: time_begin (DATETIME) time_end (DATETIME) and elapsed_ seconds (INT). The data in these DATETIMES looks fine -MM-DD HH:MM:SS as expected and actual differences between time_end and time_begin are usually on the order of 20-120 seconds. I run: UPDATE mytable SET elapsed_seconds=time_end-time_begin; When I browse the resulting data, the elapsed seconds often do, but do not always, agree with my own calculation of the elapsed seconds. For example, the first 12 rows look like: My calc elapsed_time 45 85 11 11 16 16 9 9 22 62 14 14 73 73 59 99 65 105 20 20 12 12 43 4083 That last one is a bit off!! There appeared to be a common problem with a 40-second difference, but obvioulsy that last one throws that pattern out the window! Anyhow, I have Googled quite a bit and have looked through http://www.mysql.com/doc/en/Date_and_time_functions.html several times and did not see a different function of syntax for the UPDATE that I want to do. That man page (in the section on DATE_SUB) says that as of MySQL 3.23 I can simply use +/- operators By way of troubleshooting, I created 6 other elapsed_time variables using small, medium and big int, and float, double and decimal to see if the result was different, but the result was the same for each. I'm guessing (and hoping!!) that I am missing something *very* basic in the structure of my UPDATE and will absolutely appreciate any pointers that could fix it. -- -*- Jim Hogan [EMAIL PROTECTED] Seattle, WA - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: JDBC J/Connect driver is seriously slow against InnoDB
j.random.programmer wrote: Hi: Thought I'd mention this (using the latest dev J/connect): A) DatabaseMetaData.getColumns() does not return the last 4 columns (SCOPE_*) but it should according to the API spec I'll look into fixing this right away. You should write Sun as well, as their JDBC compliance testsuite doesn't look for this :( B) InnoDB, on their benchmark page, say that inserting 100,000 rows into the DB is about 5 seconds. http://www.innodb.com/bench.html Can you point out where it says this? When I look at the benchmark page, I see that 100,000 inserts take 25 seconds, which JDBC can pretty much match (read on for more). Quoted (from the page in question): InnoDBMyISAM 100 000 inserts25 s. 40 s. 100 000 selects on primary key57 s. 58 s. 100 000 selects on secondary key 68 s. 95 s. I am finding this to be more like 300-400 seconds. Try this: I think something is broken with your network, or your machine, read on for more CREATE TABLE T1 (A INT NOT NULL AUTO_INCREMENT, B INT, PRIMARY KEY(A)) TYPE=INNODB; CREATE TABLE T2 (A INT NOT NULL, B INT, PRIMARY KEY(A)) TYPE=INNODB; Now run the java driver (source shown at the end of this message). This takes a long time. The innodb bench URL mentioned above uses perl DBI has a test driver, the JDBC *should* be as fast if I am not missing anything (but it's not). Once it's finally done, if you now say (from the mysql client): mysql insert into T2 select * from T1; Query OK, 107825 rows affected (5.23 sec) Records: 107825 Duplicates: 0 Warnings: 0 This isn't the same thing as what your test program is testing. This is optimized by the database, it is not even close to issuing 100,000 queries. It moves some rows from one place to another, and only has to issue _one_ query. Note, this takes about 5 seconds, which shows that the slowdown is not at the DB level but at the JDBC driver level (mysql client even sets auto commit to true by default, and it still takes 5 seoonds). So why does the JDBC driver take so long ? I am accessing the mysql machine over a private 100Mbps connection so I don't think it's the network either. I would double-check your network. I just ran your code here on my desktop runnin Linux, which is known not to have the fastest java support, to my test server (100 mbit private network), and it takes 30 seconds, which is 5 seconds longer than 100,000 inserts using DBI (which is using native code to access the database, btw), but is not long enough for HotSpot to actually optimize the code, either. I would guess you have I/O problems on one of your machines, or you're dropping a lot of packets somewhere, or you're not using the best JVM for your machine/OS combination. -Mark -- For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.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
What if binary log's suffix number exceeds 999?
Hi! Chung == Chung Ha-nyung [EMAIL PROTECTED] writes: Chung Dear, Chung By default, binary log file uses 3-digit number suffix as its counter. Chung Then, without reseting master logs suffix number may reach 999. If so, Chung what happens? Chung next suffix number is 1000 or 001? Otherwise error occurres? Next used suffix is 1000 and there should not be any problems when going from .999 to .1000 Regards, Monty -- For technical support contracts, goto https://order.mysql.com/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland ___/ www.mysql.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
explain bug?
Hi, I've tried an EXPLAIN of the SELECT below. Please can anyone tell me why it's Impossible WHERE noticed after reading const tables ? I've droped and created the table - but the error/message occurs. When I leave '(chetPosition = 1)' away, it works! A bug or ??? The statement works without explain! Thx a lot, Robert MySql 3.23.49 mysql explain - SELECT * FROM hmChartEntry - WHERE (ctId = 1) AND - (chSubId = 1) AND - (chetPosition = 1) - ; +-+ | Comment | +-+ | Impossible WHERE noticed after reading const tables | +-+ 1 row in set (0.00 sec) mysql desc hmChartEntry; +--+---+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+-+---+ | ctId | mediumint(8) unsigned | | PRI | 0 | | | chSubId | tinyint(3) unsigned | | PRI | 1 | | | chetPosition | tinyint(3) unsigned | | PRI | 0 | | | chetArtist | varchar(50) | YES | | NULL| | | chetTitle| varchar(80) | YES | | NULL| | | chetLabel| varchar(30) | YES | | NULL| | | coIdLabel| char(2) | YES | | NULL| | | chetDoLabelCheck | char(1) | | | Y | | | ctIdLabel| mediumint(8) unsigned | YES | | NULL| | +--+---+--+-+-+---+ 9 rows in set (0.00 sec) mysql mysql show index from hmChartEntry; +--++--+--+--+-- -+-+--++ -+ | Table| Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Com ment | +--++--+--+--+-- -+-+--++ -+ | hmChartEntry | 0 | PRIMARY |1 | ctId | A | 67 | NULL | NULL | | | hmChartEntry | 0 | PRIMARY |2 | chSubId | A | 71 | NULL | NULL | | | hmChartEntry | 0 | PRIMARY |3 | chetPosition | A |1208 | NULL | NULL | | +--++--+--+--+-- -+-+--++ -+ 3 rows in set (0.00 sec) mysql - 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
FW: Select from mulitple tables:
Helllo Im trying to run a select from 5 different tables, depending on the key in the main one like this: select JOB_ASS.JOB_ASS_ID, JOB_ASS.START_DATE, JOB_ASS.STOP_DATE, JOB_ASS.JOB_NAME, JOB_ASS.LOCATION, JOB_ASS.NOTE, JOB_ASS.ORDER_ID, JOB_ASS.CONTRACT_VALUE, USER.SURNAME, USER.FIRSTNAME, PROJECT.PROJECT_NUM, PROJECT.SUBMIT_DATE, COMITENT.NAME, INVESTOR.NAME from JOB_ASS left join PROJECT using(JOB_ASS.JOB_ASS_ID = PROJECT.JOB_ASS_ID) left join USER using(JOB_ASS.USER_ID = USER.USER_ID) left join INVESTOR using(JOB_ASS.INVESTOR_ID = INVESTOR.INVESTOR_ID) left join COMITENT using(JOB_ASS.COMITENT_ID = COMITENT.COMITENT_ID); I did try using only join and that didn't work, so some guy pointed me to this method. But it doesn't work!!! I'm a sort of a newbie to SQL and really don't know how to fix this (nor does my book help me I). Thanx, Luka Birsa - 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
tricky problem with two db-sites
Hi, I'll soon run in a probaply pretty common problem with 2 site replication. We currently hold our customer and buisiness data in one mysql server. Soon we'll get another site which basically will work on a regional separate project. While the set of potential costomers that overlap will be limited, there still will be customers that are related to both sites. The new site will only mind operational acquisition stuff and project related PR. Invoice management will be on the main site. While doing that, the 2nd site will report incoming orders to the main site and of course there will be additions and changes to shared costomer records - like adresses, contact person, phone numbers - that have to be synchronized both ways. There is no online-connection planned. The synchronisation should be made via queries that extract the changed recorddata that'll be transfered perhaps by email or via ssh remote login. How could I identify, ex- and import such changed data ? Maybe there are concurrent changes to be catched ? Like site1 changes field1 and site2 field2 of the same record. The whole point is to share as much data as possible so both sites profit on additions or corrections. Any pointer to documentation in that matter would be appreciated. regards ... Andreas - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Help with JOIN, please!
Hi, I'm having some real problems figuring out a seemingly simple join statement. I've looked through some of the mailing list posts, but I can't seem to find what I'm looking for. Here's what I have - 2 tables: table1 (id, stimulus_number, stimulus_type) table 2 (id, stimulus_number, stimulus_type) Basically, all I want to do is to determine how many matches there are, where the stimulus_number stimulus_type are the same in both tables, based on a certain id. Here's a verbose example: table 1: contains 2 entries for id=2 -- stimulus_number=23, stimulus_type='word' -- stimulus_number=34, stimulus_type='image' table 2: contains 2 entries for id=2 -- stimulus_number=23, stimulus_type='word' -- stimulus_number=34, stimulus_type='word' The desired result, should display the following: -- 23, word ... since that was the only entry that was common to both tables, for id=2 ... note, both stimulus_number stimulus_type have to match in both tables (not just one or the other). Here's what I've tried: -- select distinct a.stimulus_number, a.stimlus_type from table_1 as a left join table_2 as b on a.stimulus_type=b.stimulus_type and a.stimulus_number=b.stimulus_number where id=2; ... this looked ok to me, but it spits out any results that have common stimulus_numbers, OR common stimulus_types. Any help would be greatly appreciated, Thanks in advance, Ken (sql) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Help with simple JOIN
Hi, I'm having some real problems figuring out a seemingly simple join statement. I've looked through some of the mailing list posts, but I can't seem to find what I'm looking for. Here's what I have - 2 tables: table1 (id, stimulus_number, stimulus_type) table 2 (id, stimulus_number, stimulus_type) Basically, all I want to do is to determine how many matches there are, where the stimulus_number stimulus_type are the same in both tables, based on a certain id. Here's a verbose example: table 1: contains 2 entries for id=2 -- stimulus_number=23, stimulus_type='word' -- stimulus_number=34, stimulus_type='image' table 2: contains 2 entries for id=2 -- stimulus_number=23, stimulus_type='word' -- stimulus_number=34, stimulus_type='word' The desired result, should display the following: -- 23, word ... since that was the only entry that was common to both tables, for id=2 ... note, both stimulus_number stimulus_type have to match in both tables (not just one or the other). Here's what I've tried: -- select distinct a.stimulus_number, a.stimlus_type from table_1 as a left join table_2 as b on a.stimulus_type=b.stimulus_type and a.stimulus_number=b.stimulus_number where id=2; ... this looked ok to me, but it spits out any results that have common stimulus_numbers, OR common stimulus_types. Any help would be greatly appreciated, Thanks in advance, Ken (sql) - 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 with simple JOIN
Ken, Don't you want ... ... INNER JOIN table_2 as b ... ... PB - I'm having some real problems figuring out a seemingly simple join statement. I've looked through some of the mailing list posts, but I can't seem to find what I'm looking for. Here's what I have - 2 tables: table1 (id, stimulus_number, stimulus_type) table 2 (id, stimulus_number, stimulus_type) Basically, all I want to do is to determine how many matches there are, where the stimulus_number stimulus_type are the same in both tables, based on a certain id. Here's a verbose example: table 1: contains 2 entries for id=2 -- stimulus_number=23, stimulus_type='word' -- stimulus_number=34, stimulus_type='image' table 2: contains 2 entries for id=2 -- stimulus_number=23, stimulus_type='word' -- stimulus_number=34, stimulus_type='word' The desired result, should display the following: -- 23, word ... since that was the only entry that was common to both tables, for id=2 ... note, both stimulus_number stimulus_type have to match in both tables (not just one or the other). Here's what I've tried: -- select distinct a.stimulus_number, a.stimlus_type from table_1 as a left join table_2 as b on a.stimulus_type=b.stimulus_type and a.stimulus_number=b.stimulus_number where id=2; ... this looked ok to me, but it spits out any results that have common stimulus_numbers, OR common stimulus_types. Any help would be greatly appreciated, Thanks in advance, Ken (sql) - 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
help create new user
Does anyone know why? create database visitor; grant all on visitor.* to visitor_user identified by 'visitor'; use visitor; mysql -u visitor_user -p I get ERROR 1045: Access denied for user: 'visitor_user@localhost' (Using password: YES) From Mozilla and GNU/Linux - 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 create new user
On Mon, Nov 04, 2002 at 12:37:12AM -0500, Bob Lockie wrote: Does anyone know why? create database visitor; grant all on visitor.* to visitor_user identified by 'visitor'; use visitor; mysql -u visitor_user -p I get ERROR 1045: Access denied for user: 'visitor_user@localhost' (Using password: YES) Try: grant all on visitor.* to visitor_user@'localhost' identified by 'visitor'; Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 89 days, processed 1,867,398,195 queries (240/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
Re: help create new user
Jeremy Zawodny wrote: On Mon, Nov 04, 2002 at 12:37:12AM -0500, Bob Lockie wrote: Does anyone know why? create database visitor; grant all on visitor.* to visitor_user identified by 'visitor'; use visitor; mysql -u visitor_user -p I get ERROR 1045: Access denied for user: 'visitor_user@localhost' (Using password: YES) Try: grant all on visitor.* to visitor_user@'localhost' identified by 'visitor'; Jeremy That fixed it, thanks. What did it use since I didn't specify an @'localhost'? -- From Mozilla and GNU/Linux - 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 create new user
On Mon, Nov 04, 2002 at 12:54:17AM -0500, Bob Lockie wrote: That fixed it, thanks. What did it use since I didn't specify an @'localhost'? It used '%' which matches everyhing *except* localhost. The manual explains this. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 3.23.51: up 89 days, processed 1,867,573,876 queries (240/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
