MySQL InnoDB table row access

[Tobias Krüger]

Hi,

I want to access data from an InnoDB table. I know that I can do this using
the corresponding handler and ha_rnd_next() or ha_index_next().
My problem is that the original MySQL code is outperforming my
implementation even on simple projection queries, even though I use the same
functions.


The entry point for my own execution is in the sql_parce.cc file in the
function int mysql_execute_command(THD *thd).
~line 2600 res= execute_sqlcom_select(thd, all_tables); 

database:   http://dev.mysql.com/doc/employee/en/index.html
query:  select title from titles;

my code:  

while (!tables-table-file-ha_rnd_next(tables-table-record[0]))
{

result-send_data(thd-lex-select_lex.item_list);

}

handler-ha_index_or_rnd_end();

return;
result is of the type select_send

To evaluate the query, using this simple loop, takes around 20 percent
longer than the original MySQL code. I have used a debugger to step through
the code, but the code being executed
seems to be the same. That's why I think MySQL is doing some kind of
optimization on the table but I can't figure out where. I have also tried to
let my code run later in the evaluation process
of MySQL but the result stays the same.

Best regards,

Tobias Krueger

Re: Join with OR-condition and Indexes

[Tobias Schultze]

Thanks for your answers.

I found out, that MySQL behaves very strange in this situation.
I think this is a bug or important missing feature.

I would like to see how other DBMS behave in this situation, which I would
think is a common problem - whenever you want to join one column of a table
with several columns of another table.

MySQL uses an index_merge when I query for one specific athlete:

SELECT COUNT(*)
FROM matches m
WHERE (m.team1_player_id = 808884 OR m.team1_partner_id = 808884 OR
m.team2_player_id = 808884 OR m.team2_partner_id = 808884)

id  select_type table   typepossible_keys   key key_len
ref rowsExtra
1   SIMPLE  m   index_merge
team1_player_id_idx,team1_partner_id_idx,team2_player_id_idx,team2_partner_i
d_idx
team1_player_id_idx,team1_partner_id_idx,team2_player_id_idx,team2_partner_i
d_idx   5,5,5,5 NULL153 Using
union(team1_player_id_idx,team1_partner_id_idx,team2_player_id_idx,team2_par
tner_id_idx); Using where


But it doesn't use the index merge when joining like I originally tried,
although logically it should be able to use it, shouldn't it?
Of course, adding USE INDEX FOR JOIN (team1_player_id_idx,
team1_partner_id_idx, team2_player_id_idx, team2_partner_id_idx) cannot
convince MySQL to use the indexes.

SELECT a.id, COUNT(*)
FROM athletes a
FROM athletes a
LEFT JOIN matches m USE INDEX FOR JOIN (team1_player_id_idx,
team1_partner_id_idx, team2_player_id_idx, team2_partner_id_idx) ON (
m.team1_player_id = a.id OR
m.team1_partner_id = a.id OR
m.team2_player_id = a.id OR
m.team2_partner_id = a.id
)
WHERE a.gender = 'female'
GROUP BY a.id

Then Michael Dykman said, MySQL is restricted to one index per table per
query. So I thought, maybe I can help MySQL when I add a compound index for
all players. So it could use the one index to resolve the join.
ALTER TABLE `matches` ADD INDEX `players_idx` ( `team1_player_id` ,
`team1_partner_id` , `team2_player_id` , `team2_partner_id` ) ;

Explain now gives me:
id  select_type table   typepossible_keys   key key_len
ref rowsExtra
1   SIMPLE  a   ref gender_index_idxgender_index_idx
1   const   2193Using where; Using temporary; Using filesort
1   SIMPLE  m   index
team1_player_id_idx,team1_partner_id_idx,team2_pla...   players_idx 20
NULL46664   Using index

Hm, what does this mean? It uses the index (players_idx) but still evaluates
all 46664 rows? This does not make sense to me. 
Anyways, the query is still extremely slow (2400 rows in 3 min 5 sec). But
with IGNORE INDEX (players_idx) the query seems to be infinite (over 15
min).

So now it seems that UNIONS are the only options. But they are also much
slower than it should be. You version took 10 seconds.
My modified version with UNION ALL instead of DISTINCT takes 5.2 seconds -
only showing athletes with min. 1 match.

SELECT *, COUNT(am.id) AS number_matches
FROM (
SELECT a.id AS athlete_id, a.first_name, a.last_name, a.gender, m.*
FROM athletes a
INNER JOIN matches m ON (m.team1_player_id = a.id)
UNION ALL
SELECT a.id AS athlete_id, a.first_name, a.last_name, a.gender, m.*
FROM athletes a
INNER JOIN matches m ON (m.team1_partner_id = a.id)
UNION ALL
SELECT a.id AS athlete_id, a.first_name, a.last_name, a.gender, m.*
FROM athletes a
INNER JOIN matches m ON (m.team2_player_id = a.id)
UNION ALL
SELECT a.id AS athlete_id, a.first_name, a.last_name, a.gender, m.*
FROM athletes a
INNER JOIN matches m ON (m.team2_partner_id = a.id)
) AS am
GROUP BY am.athlete_id
ORDER BY number_matches DESC

But then I could also use the idea I had in the first post, which still
needs 3.8 seconds.

SELECT *, COUNT(am.id) AS number_matches
FROM (
(
SELECT team1_player_id AS player_id, teammatch_id, match_type,
team1_score, team2_score, team1_points, team2_points, no_fight 
FROM matches
WHERE team1_player_id IS NOT NULL  # reduced the query from 4.1 to 3.8
sec
) UNION ALL (
SELECT team1_partner_id, teammatch_id, match_type, team1_score,
team2_score, team1_points, team2_points, no_fight 
FROM matches
WHERE team1_partner_id IS NOT NULL
) UNION ALL (
SELECT team2_player_id, teammatch_id, match_type, team1_score,
team2_score, team1_points, team2_points, no_fight 
FROM matches
WHERE team2_player_id IS NOT NULL
) UNION ALL (
SELECT team2_partner_id, teammatch_id, match_type, team1_score,
team2_score, team1_points, team2_points, no_fight 
FROM matches
WHERE team2_partner_id IS NOT NULL
)
) AS am
INNER JOIN athletes a ON (am.player_id = a.id)
GROUP BY a.id
ORDER BY number_matches DESC


Changing the database schema doesn't seem to be usefull. Regarding my schema
is already normalized and over-normalization generally decreases
performances. I guess I would then run into other problems.


Greetings
Tobias


-Ursprüngliche Nachricht-
Von: Michael Dykman [mailto:mdyk

Join with OR-condition and Indexes

[Tobias Schultze]

Hello,

 

I'm working on an application for my bachelor thesis.

I'm having a performance problem with a SQL-Query in MySQL5.

I hoped someone can easily enlighten me in this issue.

 

The schema:

 

CREATE TABLE IF NOT EXISTS `athletes` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `last_name` varchar(20) NOT NULL,

  `first_name` varchar(20) NOT NULL,

  `gender` enum('male','female') NOT NULL,

  `birthday` date NOT NULL,

  `country` char(2) NOT NULL,

  `club_id` int(11) NOT NULL,

  `is_active` tinyint(1) NOT NULL,

  PRIMARY KEY (`id`),

  KEY `gender_index_idx` (`gender`),

  KEY `is_active_index_idx` (`is_active`),

  KEY `club_id_idx` (`club_id`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Sportler bzw Mitglieder des
Verbandes';

 

-- 

 

CREATE TABLE IF NOT EXISTS `matches` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `teammatch_id` int(11) NOT NULL,

  `match_type` varchar(10) NOT NULL,

  `team1_player_id` int(11) DEFAULT NULL,

  `team1_partner_id` int(11) DEFAULT NULL,

  `team2_player_id` int(11) DEFAULT NULL,

  `team2_partner_id` int(11) DEFAULT NULL,

  `team1_score` tinyint(3) unsigned DEFAULT NULL,

  `team2_score` tinyint(3) unsigned DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `teammatch_id_idx` (`teammatch_id`),

  KEY `team1_player_id_idx` (`team1_player_id`),

  KEY `team1_partner_id_idx` (`team1_partner_id`),

  KEY `team2_player_id_idx` (`team2_player_id`),

  KEY `team2_partner_id_idx` (`team2_partner_id`)

) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COMMENT='Spiele zwischen zwei oder
vier Sportlern (Einzel und Doppel)' AUTO_INCREMENT=46665 ;

 

 

I want to get all matches for each athlete and calculate statistics such as
number of matches etc.

The basic very simplified query is like

 

SELECT a.id, COUNT(*)

FROM athletes a

LEFT JOIN matches m ON ( 

m.team1_player_id = a.id OR 

m.team1_partner_id = a.id OR 

m.team2_player_id = a.id OR 

m.team2_partner_id = a.id

)

WHERE a.gender = 'female'

GROUP BY a.id

 

 

Now the problem is, that mysql uses a full table scan to retrieve the
matches for an athlete, so the execution takes many seconds or even worse.

An athlete can be referenced in any of the m.team1_player_id OR
m.team1_partner_id OR m.team2_player_id OR m.team2_partner_id. (That allows
doubles matches.)

Why is an full table scan necessary although there is an index on each of
these fields? So an index exists for each OR-part of the join condition...

 

Here the execution plan:

 


id 

select_type 

table 

type 

possible_keys 

key 

key_len 

ref 

rows 

Extra 


1

SIMPLE

a

ref

gender_index_idx

gender_index_idx

1

const

2193

Using where; Using temporary; Using filesort


1

SIMPLE

m

ALL

team1_player_id_idx,team1_partner_id_idx,team2_pla...

NULL

NULL

NULL

46664

 

 

 

Joining on each fields like the following is very fast and uses the index
but of course doesn't give me the expected result.

 

FROM athletes a

LEFT JOIN matches m ON (a.id = m.team1_player_id)

LEFT JOIN matches m2 ON (a.id = m2.team2_player_id)

 

 

Maybe I need to do a workaround using a UNION?

But this doesn't help either: (It takes 76 seconds)

 

FROM athletes a

LEFT JOIN (

(

SELECT team1_player_id AS player_id, teammatch_id, match_type, team1_score,
team2_score, team1_points, team2_points, no_fight 

FROM matches

) 

UNION 

(

SELECT team1_partner_id, teammatch_id, match_type, team1_score, team2_score,
team1_points, team2_points, no_fight 

FROM matches

)

UNION 

(

SELECT team2_player_id, teammatch_id, match_type, team1_score, team2_score,
team1_points, team2_points, no_fight 

FROM matches

)

UNION 

(

SELECT team2_partner_id, teammatch_id, match_type, team1_score, team2_score,
team1_points, team2_points, no_fight 

FROM matches

)

) m ON (a.id = m.player_id)

 

 

 

I hope someone can help me with this.

Thanks in advance.

 

Regards

Tobias

Question about triggers

[Tobias Stocker]

Hy there,

I'm planing to to build a small partial replication on MySQL 5.0 using the 
Spread Toolkit and the Message API for MySQL. Therefore I'll create a trigger 
(on insert) which fetches the row and sends it to the message group XY.

I was wondering if there is a way to tell MySQL in an SQL statement to not fire 
the trigger, for example if a message from Server B arrives on A - and 
contrariwise - I don't want the trigger to be launched to avoid loops and such.

I actually couldn't find anything about this topic. So, my question: is it 
possible at all?

Regards and thanks in advance,
Toby

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Bulk INSERT performance question

[Tobias Knaup]

List,

I am bulk inserting a huge amount of data into a MyISAM table (a  
wikipedia page dump).
Before I issued SOURCE filename.sql; I did an ALTER TABLE page DISABLE  
KEYS; LOCK TABLES page WRITE;
The dump consists of about 1,200 bulk INSERT statements with roughly  
12,000 tuples each.
For the first hour or so it performed pretty good, taking about 5  
seconds for each INSERT and using all the CPU it could get.

But a while later time consumption increased up to 10 minutes.
In the same time, mysqld is barely using any CPU (less than 2%), there  
is no significant I/O going on, and there is still unused memory.

Thoughts?

Thanks a lot

--
Tobi



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Memory limit?

[Tobias Asplund]

On Wed, 9 Feb 2005, Batara Kesuma wrote:

 Hi,

 I try to install MySQL 4.1.9 (official RPM from mysql.com). My machine
 is running linux 2.6.9, and it has 4GB of RAM. The problem is MySQL
 won't start if I set innodb_buffer_pool_size to = 2GB. Here is my
 ulimit.

Are you trying this on a 32-bit cpu machine?





 [EMAIL PROTECTED] mysql]# ulimit -a
 core file size  (blocks, -c) 0
 data seg size   (kbytes, -d) unlimited
 file size   (blocks, -f) unlimited
 pending signals (-i) 1024
 max locked memory   (kbytes, -l) 32
 max memory size (kbytes, -m) unlimited
 open files  (-n) 1024
 pipe size(512 bytes, -p) 8
 POSIX message queues (bytes, -q) 819200
 stack size  (kbytes, -s) 10240
 cpu time   (seconds, -t) unlimited
 max user processes  (-u) 63484
 virtual memory  (kbytes, -v) unlimited
 file locks  (-x) unlimited

 Here is the error message:

 050209 20:41:18  mysqld started
 050209 20:41:18 [Warning] Asked for 196608 thread stack, but got 126976
 050209 20:41:18  InnoDB: Fatal error: cannot allocate 2147500032 bytes
 of
 InnoDB: memory with malloc! Total allocated memory
 InnoDB: by InnoDB 34049176 bytes. Operating system errno: 12
 InnoDB: Cannot continue operation!
 InnoDB: Check if you should increase the swap file or
 InnoDB: ulimits of your operating system.
 InnoDB: On FreeBSD check you have compiled the OS with
 InnoDB: a big enough maximum process size.
 InnoDB: Fatal error: cannot allocate the memory for the buffer pool
 050209 20:41:18 [ERROR] Can't init databases
 050209 20:41:18 [ERROR] Aborting


 I have also checked the archive and it looks like other people also
 having similar problem, but I couldn't find the answer. Is there any
 memory limit when it is run on linux? What can I do so MySQL can run
 with = 2GB RAM? Thank you in advance.

 Regards,
 --bk



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: InnoDB crash and runaway rollback - help pls

[Tobias Asplund]

On Tue, 8 Feb 2005, Heikki Tuuri wrote:

 You should upgrade to 4.1.9. That version commits ALTER TABLE at every 10
 000 rows, and a runaway rollback can no longer happen.

This is very nice!
Are there any plans for the same with INSERT ... SELECT -type statements?

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: REPLACE INTO //add or update?

[Tobias Asplund]

On Mon, 7 Feb 2005, Bjorn van der Neut wrote:

 Hello Everyone,

 Can someone tell me If you can find out if the replace into function has
 done an insert or an update?

It actually never does an update, it always INSERTs.

It either does an insert or delete(s) followed by an insert.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Query problem

[Tobias Rydberg]

Hi!
I have a problem with a query. I have a given amount of values (id:s) 
(for example 1,2,6,12,77,78,79,122,123,124), these are related to a 
table, pref, and belongs to a specific category, pref_cat-table. So, 
1,2,6,12 are connected to id 1 in pref_cat, 77,78,79 to id 2 and 
122,123,124 to id 3 in pref_cat.

Table: pref

| id| bigint(20) unsigned |  
| user_id | bigint(20) unsigned |  
| category_id   | bigint(20) unsigned |
| option_id   | bigint(20) unsigned |   


Each user have X nr of rows in this table. From the given id:s I want 
match them with this table in the way that one id (option_id) from the 
given values in each category_id (ex 1,2 and 3) should be in this table. 
So, if a user X have id 1, 77, 122 in this table he should be a match. 
Below I have query that doesn't work, the reason it doesn't work is 
because it tries to match to the same id in pref.

SELECT DISTINCT u.id FROM user u
INNER JOIN pref p ON p.user_id = u.id AND
p.option_id IN ( 1,2,6,12 ) AND
p.option_id IN ( 77,78,79) AND
p.option_id IN ( 122,123,124 )
What I rather want to do is to match all p.option_id:s for the specific 
user with the ones in the IN. For example if a user have the id: 1, 77 
AND 122 I want to to a match like this:

SELECT DISTINCT u.id FROM user u
INNER JOIN pref p ON p.user_id = u.id AND
if any of (1,77,122) exist IN (1,2,6,12) AND
if any of (1,77,122) exist IN (77,78,79) AND
if any of (1,77,122) exist IN (122,123,124 )
Is this in some way possible to do or does someone have an other 
solution in mind?
(Maybe I should tell you that I'm using mySQL 4.1)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Mysqldump unusable, bugged?

[Tobias Asplund]

On Tue, 1 Feb 2005, matt_lists wrote:

  now we have this added on a few tables in the dump
 
  DATA DIRECTORY='E:\mysql\data\campbell\' INDEX
  DIRECTORY='E:\mysql\data\campbell\'
 
  DATA DIRECTORY='E:\\mysql\\data\\campbell\\' INDEX
  DIRECTORY='E:\\mysql\\data\\campbell\\'
 
 Anybody know?

 Is there some option I'm not setting which causes the data directory and
 index directory to not be correct?

 Why are there not hundreds of people with this problem, I would think it
 affects anyone on 4.x running on a windows machine, I hope they dont
 learn the hard way when a restore fails to work!

Noone has found a way to reproduce it with 100% certainity.
If you have a way to always reproduce, feel free to re-open my bug at:
http://bugs.mysql.com/bug.php?id=6660

(Or send me the test-case and I can do it).

I haven't gotten the bug approved since I can't reproduce it (but it does
happen quite often, just haven't been able to find out why, not even by
replaying binary logs).

cheers,
Tobias

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Mysqldump unusable, bugged?

[Tobias Asplund]

On Thu, 27 Jan 2005, matt_lists wrote:

 Having repeatable problems doing restores, 4.1.8 and 4.1.9 both do the
 same error

 Is there some setting I'm missing?

 mysqldump -u username-pPassword  --all-databases --quote-names 
 /intranet/backup/backup.sql


 E:\intranet\backupmysql -u xotech -pDaredevil22 -f  backup.sql
 ERROR at line 2153: Unknown command '\m'.
 ERROR at line 2153: Unknown command '\m'.
 ERROR at line 2153: Unknown command '\m'.
 mysql: Out of memory (Needed 626767192 bytes)
 mysql: Out of memory (Needed 626763096 bytes)

 E:\intranet\backup

I've seen this quite a few times, but never been able to reproduce it
properly.
I'm assuming you're running on Windows, correct?

I'd search your dump-file for DATA DIRECTORY the problem on Windows is that
it uses \ instead of / in the path names there, which makes it use it as an
escape character. Since dATA/INDEX DIRECTORY in CREATE TABLE is ignored on
Windows when importing anyways, it's safe to remove those clauses.

If you can find a way to reproduce it, feel free to add it to my old bug
report about this at:
http://bugs.mysql.com/bug.php?id=6660

cheers,







-- 
Tobias Asplund [EMAIL PROTECTED]
Trainer and Consultant, Sweden
MySQL AB   http://www.mysql.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: .MYD, .MYI files don't exist... but queries WORK??????

[Tobias Asplund]

If you installed MySQL 4.1.7 on Windows with the new installer, it will
automatically use InnoDB tables as the default unless you specify different.
(This is different from earlier versions), so the error was probably not in
the Query Browser, but in the server settings in this case.



On Wed, 3 Nov 2004, Anders Green wrote:

 Sequence of events:
 -had an MS Access db
 -converted it to MySQL 4.1.7 with a utility
 -everything worked
 -using MySQL Query Browser 1.0.1, added three tables
 -everything worked
 -comes time to deploy to another machine for testing,
   copy directory of database over to other machine
 -any queries involving the three new tables fail:
   mysql select * from admin;
   ERROR 1016 (HY000): Can't open file: 'admin.InnoDB' (errno: 1)

 Now, the first thing to do is go looking for files.
 What I find shocks and amazes me! There are missing
 files! *grin*

 For each of the three new tables, there is a .frm
 file, but no .myd or myi file. Okay. transfer
 aborted or something I'll go back to the first
 development machine and copy them over.

 WAIT A SECOND! They don't exist on the first
 development machine either! And yet, queries
 on the first development machine are working,
 and those files are nowhere to be found on the
 entire drive.

 Ok, I was about to add some info about the
 table types... and with the query browser
 on the first machine, I saw that they were
 all InnoDB instead of MyISAM. Changing those
 to MyISAM created the files. Copied those
 over to the other machine, restarted the
 server there, and they work.

 So now my question (which might be Why does
 the query browser not select MyISAM as the
 table type since it describes it as default,
 or might be why is my junk not working?)
 is Where does InnoDB data get stored?

 That is, in what file and what location?
 Cause now I'm curious. :)

 Cheers,
 Anders
 +===+
 |Anders Green Email: [EMAIL PROTECTED]   |
 |  Home: 919.303.0218   |
 |Off Road Rally Racing Team: http://LinaRacing.com/ |
 +===+




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: INTERSECT in mysql

[Tobias Asplund]

On Wed, 3 Nov 2004, Chaitra Yale wrote:

 ...how can union be the same as intersect..iam trying to get the names
 of comapnies that are in both queries.for example the first query
 gives me companies A, B AND C and the second query gives A , B..i want
 the intersect of these 2 queriesso i get companies A and B...if i
 did a union i will get A, B and C

Could use the union in a subquery together with a HAVING COUNT(*) on the
outside.

SELECT * FROM (
SELECT DISTINCT col1 FROM t1 WHERE...
UNION ALL
SELECT DISTINCT col1 FROM t1 WHERE...
) AS tbl
GROUP BY tbl.col1 HAVING COUNT(*) = 2

Or something... not sure how well that would performe, however.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: .MYD, .MYI files don't exist... but queries WORK??????

[Tobias Asplund]

On Wed, 3 Nov 2004, Anders Green wrote:

 Tobias Asplund wrote:
 If you installed MySQL 4.1.7 on Windows with the new installer

 Yes I did.

 it will automatically use InnoDB tables as the default

 Ah ha. Thanks. :)


 That just leaves this:

   So now my question Where does InnoDB data get stored?


You'll probably see some files in your data\ directory under your MySQL
directory named something like 'ibdata1'.

InnoDB stores all its data in a tablespace, so there won't (by default) be
more than the .frm file per table (as of 4.1 you can override this with
--innodb-file-per-table)

You can read more about this here:
http://dev.mysql.com/doc/mysql/en/InnoDB.html



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: More on MySQL server is taking all my hardrive space

[Tobias Asplund]

On Thu, 21 Oct 2004, C.F. Scheidecker Antunes wrote:

 Hello,

 On a further inspection and by reading Paul DuBois' I guess m y system
 has to many bin logs. How can I get ride of them?

Assuming you don't need them for replication or point-in-time recovery, you
can use the PURGE command.

http://dev.mysql.com/doc/mysql/en/PURGE_MASTER_LOGS.html

If you do not need the binary logs for anything, and you're not running
replication you can remove all of them and restart at binlog.01 with the
command RESET MASTER (I'd suggest using the PURGE, though).






-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: InnoDB deadlock problem

[Tobias Asplund]

On Wed, 6 Oct 2004, David Edwards wrote:

 I've got a deadlock problem using InnoDB tables

(...)

 Transaction 1:
 START TRANSACTION;
 DELETE FROM results WHERE id_job = 25920;
 INSERT INTO results(result,id_job) VALUES (31.461937,25920);
 COMMIT;

 Transaction 2:
 START TRANSACTION;
 DELETE FROM results WHERE id_job = 25919;
 INSERT INTO results(result,id_job) VALUES (25.388607,25919),(22.650234,25919);
 COMMIT;

I think this manual page might explain what's happening:

http://dev.mysql.com/doc/mysql/en/InnoDB_Next-key_locking.html







-- 
Tobias Asplund [EMAIL PROTECTED]
Trainer and Consultant, Sweden
MySQL AB   http://www.mysql.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Field Type Problem

[Tobias Asplund]

On Tue, 14 Sep 2004, Sheraz wrote:

 when I try following:

 CREATE TABLE `MyTable` (
   `SNumber` char(32) NOT NULL,
   `UserID` char(32) NOT NULL default '0',
   PRIMARY KEY  (`SNumber`)
 ) TYPE=MyISAM;

 It created the exactly correct table, But when i add
 any varchar field in the above sql..then generated
 table is like it has all the fields of type Varchar

This behaviour is documented (although slightly annoying) here:

http://dev.mysql.com/doc/mysql/en/Silent_column_changes.html

cheers,
Tobias

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL speed

[Tobias Asplund]

 Can anyone tell me what's going on? Are there MySQL parameters that can
 improve things?

To know if you need to tune something, you can send us the copy of SHOW
STATUS; and SHOW VARIABLES;


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Fulltext doesn't seem to find certain text

[Tobias Asplund]

On Tue, 14 Sep 2004, joe mcguckin wrote:

 If I perform a fulltext search for 'foo', it won't match text like
 'foo, inc'.

In addition to the other suggestions, make sure our ft_min_word_len isn't
more than 2, because it won't index words shorter than that many characters.


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: INSERT on duplicate UPDATE?

[Tobias Asplund]

On Tue, 14 Sep 2004, Yves Goergen wrote:

 Hi,
 I can vaguely remember there was something like INSERT... on duplicate
 key UPDATE... in MySQL, but the documentation search is almost as
 useful as I'm used to - it cannot tell me anything about this. Can you
 please? How does this work, what's the syntax? Is this ANSI-SQL standard
 (or compatible to other DBMS)? Or is it even documented?

It's covered on the INSERT page:

http://dev.mysql.com/doc/mysql/en/INSERT.html


If you specify the ON DUPLICATE KEY UPDATE clause (new in MySQL 4.1.0), and
a row is inserted that would cause a duplicate value in a UNIQUE index or
PRIMARY KEY, an UPDATE of the old row is performed. For example, if column a
is declared as UNIQUE and already contains the value 1, the following two
statements have identical effect:

mysql INSERT INTO table (a,b,c) VALUES (1,2,3)
- ON DUPLICATE KEY UPDATE c=c+1;

mysql UPDATE table SET c=c+1 WHERE a=1;

Note: If column b is unique too, the INSERT would be equivalent to this
UPDATE statement instead:

mysql UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;

If a=1 OR b=2 matches several rows, only one row is updated! In general, you
should try to avoid using the ON DUPLICATE KEY clause on tables with multiple
UNIQUE keys.

As of MySQL 4.1.1, you can use the VALUES(col_name) function in the UPDATE
clause to refer to column values from the INSERT part of the INSERT ...
UPDATE statement. In other words, VALUES(col_name) in the UPDATE clause
refers to the value of col_name that would be inserted if no duplicate-key
conflict occurred. This function is especially useful in multiple-row
inserts. The VALUES() function is meaningful only in INSERT ... UPDATE
statements and returns NULL otherwise.

Example:

mysql INSERT INTO table (a,b,c) VALUES (1,2,3),(4,5,6)
- ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

That statement is identical to the following two statements:

mysql INSERT INTO table (a,b,c) VALUES (1,2,3)
- ON DUPLICATE KEY UPDATE c=3;
mysql INSERT INTO table (a,b,c) VALUES (4,5,6)
- ON DUPLICATE KEY UPDATE c=9;

When you use ON DUPLICATE KEY UPDATE, the DELAYED option is ignored.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Tricky Date Query

[Tobias Asplund]

On Tue, 7 Sep 2004, Lee Denny wrote:

 Hello,

 I need to perform a select that compares two dates

 I need to return all records that haven't had date_2 set after a given
 number of days since date_1.



... WHERE date_2  date_1 + INTERVAL X DAY

Where X is the number of days.

Assuming this is what you asked, giving more information is always good.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Slave I/O thread dies, fatal error 1236

[Tobias Asplund]

On Tue, 7 Sep 2004, matt ryan wrote:

 Still have not got this fixed, I'm all out of idea's, the slave has been
 reloaded again today

I forgot, did you have multiple slaves on multiple machines? If so, do they
have identical hardware/drivers?


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Can't start server: Bind on TCP/IP port: Address already in use

[Tobias Bohlin]

Hi!
I have restarted the Linux server. Typing
mysqld
result:
040607 11:32:47  Can't start server: Bind on TCP/IP port: Address 
already in use
040607 11:32:47  Do you already have another mysqld server running on 
port: 3306 ?
040607 11:32:47  Aborting

040607 11:32:47  mysqld: Shutdown Complete
This isn't correct I think, as
ps
yields:
  PID TTY  TIME CMD
 9855 pts/100:00:00 bash
10363 pts/100:00:00 ps
No sign of another mysql daemon running there.
What to do? It used to work flawlessly. Am I hacked?
Cheers
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

ERROR 2026: SSL connection error

[Tobias Gunkel]

Hello!

I'm using MySQL 4.0.18 on a SuSE 9.0 machine.
I compiled it with

./configure \
--with-vio  \
--with-openssl  \
--without-debug \
--without-bench \
--enable-assembler

I've created the certs  keys with the following commands:

openssl req -new -x509 -keyout $PRIV/cakey.pem -out $DIR/cacert.pem -config
$DIR/openssl.cnf openssl req -new -keyout $DIR/server-key.pem -out
$DIR/server-req.pem -days 730 -config $DIR/openssl.cnf openssl rsa -in
$DIR/server-key.pem -out $DIR/server-key.pem openssl ca  -policy
policy_anything -out $DIR/server-cert.pem -config $DIR/openssl.cnf -infiles
$DIR/server-req.pem openssl req -new -keyout $DIR/client-key.pem -out
$DIR/client-req.pem -days 730 -config $DIR/openssl.cnf openssl rsa -in
$DIR/client-key.pem -out $DIR/client-key.pem openssl ca  -policy
policy_anything -out $DIR/client-cert.pem -config $DIR/openssl.cnf -infiles
$DIR/client-req.pem

My /etc/my.cnf looks like this:

--- /etc/my.cnf 
[client]
ssl-ca=/etc/mysql/cacert.pem
ssl-cert=/etc/mysql/client-cert.pem
ssl-key=/etc/mysql/client-key.pem
socket=/dat1/srv/chroot/mysqld/tmp/mysql.sock

[mysqld]
port=3306
user=mysql
safe-show-database
skip-symlink
safe-user-create
log=/var/log/mysql/mysql.log
log-slow-queries=/var/log/mysql/slow_queries.log
ssl
ssl-ca=/etc/mysql/cacert.pem
ssl-cert=/etc/mysql/server-cert.pem
ssl-key=/etc/mysql/server-key.pem
set-variable=local-infile=0
-- EOF 

SSL connections worked fine for a cuple of hours, but then I get this SSL
error:

ERROR 2026: SSL connection error

When I delete the certs  keys and creating them again, all works fine for
some time.
But then the same error occurs.

What did I wrong???
It seems, that the certs  keys are vaild only for some hours.

Maybe someone could help me.
Thanks.

Tobias Gunkel
Germany



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

how to change a Bit of a SET Value

[tobias]

Hi all,

I use a SET Value to keep track of my Replication Process on the 
Main-Daemon.
So for every Daemon, how collects Data from a Branch, a have a Flags
as a SET ('daemon_on', 'slave_working', ...)

Now I wont to toggle only one Bit and lave the rest at it is.
Example:
Now I would like to toggle Bit 1 (slave_working) without to check first 
if Bit 0 (daemon_on)
is set or not.
(I know the Slave can't work if the daemon is off, but there are more 
Options)

Is there a way to set/reset only one Bit in a SET Value.
I couldn't find something useful in the Docs.
Maybe I can link the Update Values with an XOR and a Mask, but how must 
I do it in MySQL?

Up to now a havent figured it out how to UPDATE a SET Value with a 
binary Value.
I use only the Strings or the Decimal Value.

Thanks if someone is a little bit more familiar with SET Values and 
posts a short comment.

MfG,
Tobias


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: ORDER BY WITH NO PRINT

[Tobias Asplund]

On Fri, 26 Mar 2004, Seena Blace wrote:

 Select group,hostname,details from table1 order by group;

 I want the output should be like
 Group   hostname   details
 aa   abababa
   abababababab
   anannanananna
 bbllololololool

Hi Seena,

Try this:

SELECT IF(@a != group, @a := group, '') AS group, hostname, details FROM
table1 ORDER BY group

Cheers,
Tobias

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysqld_multi

[tobias]

Date: Wed, 14 Apr 2004 13:26:10 -0400
To: [EMAIL PROTECTED]
From: Pingouin Team [EMAIL PROTECTED]
Subject: mysqld_multi
Message-Id: [EMAIL PROTECTED]
Hi,

I need help. I have my old mysql server running in my web server and I
can't stop it for make the test of the new version (3.28 to 4.0). I
have installed with compilation of the source code and configure my
socket, var and port. I have make a config file with the --example:
[mysqld_multi]
mysqld = /home/benoit/mysql/bin/safe_mysqld
mysqladmin = /home/benoit/mysql/bin/mysqladmin
user = multi_admin
password = multipass
[mysqld2]
socket = /home/benoit/tmp/mysql.sock
port = 3310
pid-file = /home/benoit/mysql/var/pingouin.pid
datadir = /home/benoit/mysql/var
language = /home/benoit/mysql/share/mysql/english
And I start my MySQL with ./mysqld_multi
--config-file=/home/benoit/mysql/etc/my.cnf start 2
but when I execute that command line MySQL start and stop. In the log, I
can see just that:
mysqld_multi log file version 2.2; run: Wed Apr 14 13:01:02 2004
Starting MySQL servers

Starting mysqld daemon with databases from /home/benoit/mysql/var
040414 13:01:05 mysqld ended
Why the server do not stay online?

Thank you,
Benoit Tremblay
-- NerdzTeam [EMAIL PROTECTED]
###

Servus,

to know what stops your MySQL Daemon you must look in your Log-File of 
this one.
I don't know where it is in your system but it helps to add a separate 
one in
[mysqld2]
err-log = /var/log/mysql2d/mysqld.log

and then look for the real problem (probably permissions ;-)

But maybe you should wait an moment, because I have with this 
mysqld_multi a problem
who can give you some trouble, too!

As soon as I start the second Daemon the Socket of the first (main) 
Daemon disappears!!
You can connect over TCP, but by default it tries the Socket at localhost.
I have MySQL 5.0 running and maybe you haven't got this problem, but 
think about it.

I posted about my Problem to the List
(mysqld_multi kills socket from previous Daemon from 2004-04-07),
but up to now I haven't got an answer and still don't know whats going 
on with my Sockets!
(Has someone an Idea ??)

Best regards,
Tobias
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

mysqld_multi kills socket from previous Daemon

[Tobias]

Hello all,

I would like to run diverent MySQL-Daemons on the same Linux machine, so 
I use mysqld_multi to start the different processes.
The Config looks right to me, I have different Database Directorys, 
Pid-Files, Ports, IDs usw. and different Socket-Files and all over it 
seems to work.
But!
as soon as I start the sekond Daemon my Socket-'File' from the first 
one, who ist started at the boot time (like RedHat Config), disappears! 
Both Daemons are running still fine, but the first one without a Socket. 
I have to connect over the TCP Port.
The same happens if i stop the first Daemon and then start it with 
mysqld_multi. The Socket is here, untill I start the next Daemon with 
mysqld_multi.

Has anybody a Idee where I can look and find a way to keep my Sockets ???

Before I say good by, I would like to apologies about the bat sentens 
construction and the writing mistakes in English!

Best regards,
   Tobias
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: What exactly is happening with this table create?

[Tobias Asplund]

On Mon, 15 Mar 2004, Boyd E. Hemphill wrote:

 Using a tool to generate a data model I go the following statement:

 Create table StateN (
   StateId Int NOT NULL AUTO_INCREMENT,
   StateNm Char(50) NOT NULL DEFAULT '',
   StateCd Char(7) NOT NULL DEFAULT '',
   SortInt Int NOT NULL DEFAULT 0,
   UNIQUE (StateId),
   UNIQUE (StateCd),
  Primary Key (StateId),
  UNIQUE Index ak_State (StateCd)
 ) TYPE = InnoDB
 ROW_FORMAT = Default;

 The last four statements seem to be redundant in that the
 UNIQUE(StateId) is a proper subset of the Primary Key(StateId) statement
 and is StateCd to its index.

All four statements aren't redundant, only two of them are.


 My question is, what is happening in terms of the objects I am creating?
 That is:  Am I creating for indexes or two?  Are they the pk and ak that
 I want (last two statements) or is the server only creating the first
 two?

All four indexes are created in this case.
You have one primary key on the StateId column,
one unique index on the StateId column called StateId (-- redundant)
one unique index on the StateCd column called StateCd and
one unique index on the StateCd column called ak_State.

The primary key should be removed if you want StateId to contain NULL values,
but most likely you will want to remove the unique index on this column
(since a primary key in itself is unique).

Then you will remove one of the two indexes on the StateCd column.

for example:
DROP INDEX StateId ON StateN;
DROP INDEX StateCd ON StateN;

That would leave you with the Primary Key on StateId and a unique index
called ak_State on the StateCd column.

cheers,
Tobias

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: problem with wildcards in host field .

[Tobias Asplund]

On Fri, 6 Feb 2004, Alvaro Avello wrote:

 the questions is , the wildcard in host field doesn't
 involve  localhost o a machine host's ? Which kind of permissions we
 have to put in host fields to have a mobility and not to be afraid to
 move our servers for an emergency ?

When the host field is localhost, it is checked against when connecting
through a local socket, when not it's checked when connecting through
TCP/IP.
You can force a TCP/IP socket over a Unix socket when connecting from the
local computer by instead of connecting to localhost in your application
connect to 127.0.0.1 or the computer's IP.
This however will slightly decrease performance, since a Unix socket is
faster.

A fix if you have to emergency move the server could be to just change the
fields from localhost to '%'.

cheers,
Tobias

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Can't connect URGENT!

[Tobias Asplund]

On Sun, 8 Feb 2004 [EMAIL PROTECTED] wrote:

 the only way I can connect to the server is typing

 mysql -h localhost -u root



make sure you don't have a line skip-networking in your /etc/my.cnf

cheers,
Tobias

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: 5.0.0-alpha: can't create column 'Found'

[Tobias Asplund]

On Fri, 30 Jan 2004 [EMAIL PROTECTED] wrote:

 Description:
 When creating a table, mysql 5.0.0-alpha (binary package)
 does not accept column name 'Found'.

 How-To-Repeat:
 create table log (Found INT UNSIGNED NOT NULL);



FOUND is a reserved word. Use within backticks, ie `Found`

http://www.mysql.com/doc/en/Reserved_words.html


cheers,
Tobias

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Question on sub-selects

[Tobias Asplund]

On Wed, 28 Jan 2004, Deven Phillips wrote:

 Hello,

 There is a web site associated with the web-radio. Users of the web site
 can rate songs which are contained in the database. The rating system
 works such that users can rate songs from +3 to -2. Now, what I would
 like to accomplish is to create a query that allows me to randomly
 select a song from the database to add to the queue while taking into
 account the ratings. For example:

 There are 2400 songs listed
 One song has been rated three times as follows:
   +3
   +1
   -1
 I would like that song to have a 3/2400 chance of being selected for the
 queue.

You can easily come into a situation where this isn't feasible.
I assume the 3 in 3/2400 is the sum of the votes.
Imagine you have 2 songs and 10 users.
5 users vote 3 for one of the songs and 5 users vote 2 for the same song.
Then you'd have 25/2 chance of having that song selected.


 Secondly, I need the database to store who voted for waht so that users
 cannot continually vote +3 over and over for their favorite song.

That is easily accomplished, just use a table like:

CREATE TABLE votes (
  userid INT UNSIGNED NOT NULL,
  songid INT NOT NULL,
  vote TINYINT NOT NULL,
  PRIMARY KEY  (userid, songid)
)

Then when your web application inserts or updates a vote you can use
REPLACE INTO votes (userid, songid, vote) VALUES(1, 1, 2);
That will take care of the problem of someone voting for their favorite
song more than once.



One suggestion (doesn't have the percentages that you specify above, but
that is easily implementable) could be to use the votes table above and
the songs table:

CREATE TABLE songs (
  songid INT UNSIGNED NOT NULL AUTO_INCREMENT,
  artistid INT UNSIGNED NOT NULL,
  songname char(75) NOT NULL,
  PRIMARY KEY  (songid)
)


Then you could use a query like:

SELECT songname, RAND() * (AVG(votes.vote) + 10) AS rnd
FROM songs
JOIN votes ON votes.songid = songs.songid
GROUP BY songs.songid
ORDER BY rnd DESC
LIMIT 1

This will generate random numbers between 0 and 8-13 depending on what the
average vote for the song is, you can change the + 10 to any number to
weigh them differently (although, if you use 0-2 votes with low averages
might never be played since their values will always be negative).

Might give you some new ideas if nothing else

cheers,
Tobias

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: stored proc containing subquery crashes mysqld-nt.exe

[Tobias Asplund]

On Fri, 23 Jan 2004, Rob Kemmer wrote:

 Hi, MySQL Team!

 I've downloaded and successfully installed v5.0 win2k, and am happily using stored 
 procs, but seem to be encountering problems with subqueries in stored procs.  When I 
 run a stored proc containing a subquery, the first pass works, but a second 
 consecutive call to the proc crashes the server.

 Other than that, I haven't encountered any other problems.


This sounds very similiar to bug #2460:

http://bugs.mysql.com/bug.php?id=2460

cheers,
Tobias

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Ignore Replication Temp Tables

[Tobias Asplund]

On Tue, 20 Jan 2004, Todd Burke wrote:

 Is there any way to disable replication of all temp tables using
 replicate-ignore-table or some other means?  The names of the temp tables
 are generated randomly by a script.  Thanks


If you could have all temporary tables starting with tmp or something
like that (since the script generates the names it should be easy just to
tag something at the start or end that makes them stand out), then you can
use:

(from manual: http://www.mysql.com/doc/en/Replication_Options.html)

--replicate-wild-ignore-table=db_name.table_name
Tells the slave thread to not replicate a query where any table
matches the given wildcard pattern. To specify more than one table to
ignore, use the directive multiple times, once for each table. This will
work for cross-database updates. Please read the notes that follow this
option list. Example: --replicate-wild-ignore-table=foo%.bar% will not do
updates to tables in databases that start with foo and whose table names
start with bar. Note that if you do --replicate-wild-ignore-table=foo%.%
then the rule will be propagated to CREATE DATABASE and DROP DATABASE,
that is, these two statements will not be replicated if the database name
matches the database pattern (foo% here) (this magic is triggered by %
being the table pattern). Escaping wildcard characters _ and %: see notes
in the description of replicate-wild-do-table just above.

cheers,
Tobias

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Function in Where clause

[Tobias Asplund]

On Wed, 21 Jan 2004, Yves Arsenault wrote:

 Thanks for your response,

 I'm using ColdFusion along side of MySQL to server dynamic content to some
 websites.

 When I run the page that this code is in, I get an SQL syntax error:
 Syntax error or access violation: You have an error in your SQL syntax near
 '(theDate) = '31' and month(theDate) = '9' and year(theDate) = '2003''

 The right values are displayed from ColdFusion server, but I keep getting
 the syntax error.

In 3.23 you have to use DAYOFMONTH(), DAY() isn't available til 4.1.1


cheers,
Tobias

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Reading securely the value of an autoincrement field used as PK

[Tobias Asplund]

On Tue, 20 Jan 2004, Marco Paci wrote:

 Since the process of inserting a new record and reading its PK field
 value is a two step process implemented by:
 1) insert into tablename (columnnames) values()
 2) select last_insert_id()
 ,and since because of the architecture of my application I cannot
 prevent that 2 concurrent insertions won't be done

http://www.mysql.com/doc/en/LOCK_TABLES.html

What will happen is:

   |Write-lock on table
   |Insert a new record
   | Write-lock on table (waiting...)
   |
   |Read the value of PK field
   |Unlocking table(s)
   | Write-lock gotten
   | Insert new record
   | Read the value of PK field
   | Unlock table(s)
   |
   |
   |
   v
 TimeThread 1   Thread 2
 Axis


cheers,
Tobias

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Stumped on a query

[Tobias Asplund]

On Tue, 20 Jan 2004, Chris Boget wrote:

 The data I'm working with looks like this:

 table1.columnA = '1;3;4;6;8;9;12;13;14;15';

 table2.columnA = '3';
 table2.columnB = 'this';

 I need to write a query that will do something along these lines:

 SELECT * FROM table1, table2 WHERE
   table2.columnB = 'this'
 AND
   table1.columnA LIKE CONCAT( '%', table2.columnA, '%' );


 AND
INSTR(table1.columnA, table2.columnA)

You can find more info about the INSTR function at:
http://www.mysql.com/doc/en/String_functions.html#IDX1189


cheers,
Tobias

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Stumped on a query

[Tobias Asplund]

On Tue, 20 Jan 2004, Chris Boget wrote:

   The data I'm working with looks like this:
   table1.columnA = '1;3;4;6;8;9;12;13;14;15';
   table2.columnA = '3';
   table2.columnB = 'this';
   I need to write a query that will do something along these lines:
   SELECT * FROM table1, table2 WHERE
 table2.columnB = 'this'
   AND
 table1.columnA LIKE CONCAT( '%', table2.columnA, '%' );
   AND
  INSTR(table1.columnA, table2.columnA)

 Well, the only problem with going this route is that if table1.columnA 's value
 was, instead, '1,4,6,8,9,13,14,15', that row would still match (when it really
 shouldn't) because of the '13' within the string.  The '3' from table2.columnA
 is part of the table1.columnA string.

Oops

INSTR(a, CONCAT(',', b, ',')) OR
INSTR(a, CONCAT(b, ',')) = 1 OR
INSTR(a, CONCAT(',', b)) = CHAR_LENGTH(a) - CHAR_LENGTH(b)

might work better, sorry about that (replace a and b with appropriate
columns).

The first line checks if ,column, exists, which will be in all cases when
it's not either first or last.
The second line checks if it exists first in the commaseparated list, and
the last line checks if it exists last.
There's probably an easier way to do this, but since I submitted a faulty
reply I should atleast make up for it ;)

cheers,
Tobias

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Increment value

[Tobias Asplund]

On Tue, 20 Jan 2004, Mike Mapsnac wrote:

 In the table value login_count is int(4). For example if value login_count
 equal to 3 and each time user login I  want to increment by one.

 update customer set login_count='how? where id=12121212;

SET login_count = login_count + 1


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: table info

[Tobias Asplund]

On Mon, 19 Jan 2004, Mike Mapsnac wrote:

 I think that main disadvantage of this command is thah it works for the
 database but not for specific table. So if a database has 200 tables, find
 result for specific table is not an easy task.

SHOW TABLE STATUS LIKE 'tablename'\G

cheers,
Tobias







 From: Victoria Reznichenko [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Subject: Re: table info
 Date: Mon, 19 Jan 2004 14:10:37 +0200
 
 Mike Mapsnac [EMAIL PROTECTED] wrote:
 
   Is it possible to find out when the table was created? How to get
 information about the table?
 
 SHOW TABLE STATUS command provides info about table:
  http://www.mysql.com/doc/en/SHOW_TABLE_STATUS.html
 
 
 --
 For technical support contracts, goto https://order.mysql.com/?ref=ensita
 This email is sponsored by Ensita.net http://www.ensita.net/
 __  ___ ___   __
/  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
   / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
 /_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
 ___/   www.mysql.com
 
 
 
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 

 _
 Rethink your business approach for the new year with the helpful tips here.
 http://special.msn.com/bcentral/prep04.armx




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Expressions

[Tobias Asplund]

On Mon, 19 Jan 2004, Ian O'Rourke wrote:

 Regarding the following query:

 SELECT ID,Author,DATE_FORMAT(EntryDate,'%d %m
 %y'),SectionID,Title,Summary,Content  FROM articles
 ORDER BY EntryDate
 DESC LIMIT 10

 Okay, I've looked in the manually up and down, as I know how to do it in
 Access, but I can't find it. I want to set an expression so I can give the
 Date_Format function a handy name - so it returns the name of the column as
 ArticleDate, for instance.

 I'm missing something simple :)


SELECT ID,Author,DATE_FORMAT(EntryDate,'%d %m %y') AS something,
SectionID, Title, Summary, Content
FROM articles
ORDER BY EntryDate DESC
LIMIT 10

(The AS isn't mandatory, you can just state the alias if you want, the AS
can be good for readability, however)

cheers,
Tobias

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: dynamic no of columns

[Tobias Asplund]

On Sun, 18 Jan 2004, Nitin Mehta wrote:

 I need to select a column's value as different columns and other fields grouped by 
 those values.

 I want to select distinct values of reseller column as individual columns and other 
 data grouped on these values.

Since the approach of tables usually are that the columns are more static
than the number of rows, and the result you're striving for is that the
aggregations are more static than the number of resellers, wouldn't it
make more sense to use one row per reseller and one column per grouping?
You could easily accomplish this with the SELECT ... CASE ... control flow
function as documented at:
http://www.mysql.com/doc/en/Control_flow_functions.html

cheers,
Tobias

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: table info

[Tobias Asplund]

On Sat, 17 Jan 2004, Mike Mapsnac wrote:

 Is it possible to find out when the table was created? How to get information about 
 the table?


You can use the unix command `stat`, the last of the 4 dates will be the
creation date (in the order of Last Accessed, Last Modified, Last Inode
modification, Birth Date of the file).

Ex.

[EMAIL PROTECTED]:/var/mysql/world# stat City.frm
1042 50097 -rw-rw 1 mysql mysql 230984 8702 Dec 21 17:00:23 2003
Dec 21 17:00:23 2003 Dec 21 17:00:23 2003 Dec 21 17:00:23 2003 16384
20 City.frm

In this case it shows that the table was created Dec 21.

stat -f %B

will give you only this data in a unixtime format, if that would suit you
better.

cheers,
Tobias

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: ibdata1

[Tobias Asplund]

On Sun, 18 Jan 2004, Hassan Shaikh wrote:

 Hi,

 How do I resize (shrink  expand) InnoDB file?


You can read about the various InnoDB specific startup options here:
http://www.mysql.com/doc/en/InnoDB_start.html

cheers,
Tobias

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: newbie - connect error

[Tobias Asplund]

On Fri, 16 Jan 2004, tait sanders wrote:

 yep I've already done this.
 everything I do comes back with the same error:
 ERROR 2002: Can't connect to local MySQL server through socket
 '/tmp/mysql.sock' (2)

 I even deleted the mysql.sock and used 'mysql_config --socket' to
 recreate it.
 this produces a new mysql.sock but then trying to start mysql produces
 the error of before.

 can i uninstall mysql rather than deleting it. I've tried the 'rpm'
 utility but it's not on my os.

 thanks heaps for all your help.

You can see if MySQL is actually running with ps.
If it is running you can see what socket it's using with `netstat -a |
grep mysql`
You can try to connect through tcp/ip instead of the socket by using -h
127.0.0.1 at the commandline.

If the server isn't up, the info should be in the error log why it doesn't
like to start.

cheers,
Tobias

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: load data loses connection

[Tobias Asplund]

On Thu, 15 Jan 2004, Jamie Murray wrote:

 Hi Guys,
 after waiting about a minute I get

 ERROR 2013 (HY000): Lost connection to MySQL server during query


See if changing any of the
SHOW VARIABLES LIKE 'net%timeout';
helps.

Not sure why the crash popup comes up, however.

cheers,
Tobias

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: File_priv syntax?

[Tobias Asplund]

On Fri, 16 Jan 2004, Jough P wrote:

 Greetings all, I'm trying to grant a user the file privilege and am
 getting error messages.  Here's my SQL statement

 mysql GRANT file ON bs.table1 TO [EMAIL PROTECTED] IDENTIFIED BY
 'password123';

 It gets the following error:

 ERROR 1144: Illegal GRANT/REVOKE command. Please consult the manual
 which privileges can be used.

the FILE privilege is a global privilege, and as such needs to be in the
*.* context. It does not work on a db-level.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: why: mysqldump and mysqlimport?

[Tobias Asplund]

On Tue, 13 Jan 2004, EP wrote:

 I am wondering:

 I can see the MySQL data files for my various databases.

 What technically prevents me from simply copying those files and using copies
 - to move my database to another file structure or server
 - to back-up my current db

Copying will not work across certain versions, will also not work between
system archtechtures, port to other RDBMS or if you do not have physical
access to the files.

For the cases above mysqldump works fine.

cheers,
Tobias

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: setting a variable

[Tobias Asplund]

On Wed, 14 Jan 2004, Ugo Bellavance wrote:

 mysql 4.0.17 on redhat 9 or debian 3.0

 mysql show variables like 'log';
 +---+---+
 | Variable_name | Value |
 +---+---+
 | log   | ON|
 +---+---+
 1 row in set (0.00 sec)

 mysql set global log=on;
 ERROR 1193: Unknown system variable 'log'

 What am I doing wrong?

Currently you cannot start logging while the server is running.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Mysql 4.1.1a

[Tobias Asplund]

On Mon, 12 Jan 2004, Viktor wrote:

 Hello mysql,

   Table-level privileges do not work at all... (on Windows)


Works fine for me:

4.1.1a-alpha-max-nt:tmp GRANT SELECT ON tmp.tmp TO [EMAIL PROTECTED]
IDENTIFIED BY 'aaa';
Query OK, 0 rows affected (0.18 sec)

4.1.1a-alpha-max-nt:tmp \q
Bye

C:\mysql\4.1-tree\bin.\mysql -u tmp -p -P 3308
Enter password: ***
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.1.1a-alpha-max-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

4.1.1a-alpha-max-nt:(none) SELECT * FROM tmp.tmp;
+--+
| a|
+--+
|1 |
+--+
1 row in set (0.42 sec)

4.1.1a-alpha-max-nt:(none) SELECT * FROM tmp.meep;
ERROR 1142 (42000): select command denied to user: 'tmp'@'localhost' for
table 'meep'
4.1.1a-alpha-max-nt:(none) 


cheers,
Tobias

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SQL_NO_CACHE

[Tobias Asplund]

On Tue, 13 Jan 2004, Priyanka Gupta wrote:

 Hi,

 I am trying to do some performance analysis by trying different indexing
 schemes and testing how long it takes. To get consistent results, I would
 like to use something like SQL_NO_CACHE. However, the mysqld version that I
 have installed does not seem to support it..its 4.0.16

 Could anyone suggest any other way of achieving this.


What does
SHOW VARIABLES LIKE 'query_cache_type';
show?

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Resetting Auto_Increment

[Tobias Asplund]

On Mon, 12 Jan 2004, Hassan Shaikh wrote:

 Hi,
 The following does not work for InnoDB tables. The manual says The next 
 AUTO_INCREMENT value you want to set for your table (MyISAM). 
 ALTER TABLE table_name AUTO_INCREMENT = new_value;

 Any suggestions for InnoDB?

Insert a row with a custom value, then delete it, the next value insrted
will be value+1 of the value you juse inserted.

Example below:

[EMAIL PROTECTED]:tmp  CREATE TABLE ai (num INT UNSIGNED NOT NULL AUTO_INCREMENT 
PRIMARy KEY) TYPE=INNODB;
Query OK, 0 rows affected (0.01 sec)

[EMAIL PROTECTED]:tmp  INSERT INTO ai VALUES (NULL), (NULL), (NULL);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

[EMAIL PROTECTED]:tmp  SELECT * FROM ai;
+-+
| num |
+-+
|   1 |
|   2 |
|   3 |
+-+
3 rows in set (0.00 sec)

[EMAIL PROTECTED]:tmp  INSERT INTO ai VALUES(10);
Query OK, 1 row affected (0.00 sec)

[EMAIL PROTECTED]:tmp  DELETE FROM ai WHERE num=10;
Query OK, 1 row affected (0.01 sec)

[EMAIL PROTECTED]:tmp  INSERT INTO ai VALUES(NULL);
Query OK, 1 row affected (0.00 sec)

[EMAIL PROTECTED]:tmp  SELECT * FROM ai;
+-+
| num |
+-+
|   1 |
|   2 |
|   3 |
|  11 |
+-+
4 rows in set (0.00 sec)

[EMAIL PROTECTED]:tmp  \t

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: field reference question

[Tobias Asplund]

On Mon, 12 Jan 2004, Ed Lazor wrote:

 Is there an abstract way to refer to field names in a result set?
 select ID, Name, Price from Products order by field(1)
 where field(1) would be the abstract way of referring to the specific field to sort 
 on.

ORDER BY #
Where # is the number of the column counting from left to right starting
at 1.

cheers,
Tobias

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: backup my database

[Tobias Asplund]

On Mon, 12 Jan 2004, Alaios wrote:
 Hi there. Do u know how can i backup my database?
 (create  insert)

http://www.mysql.com/doc/en/Backup.html

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: my.ini file for two instances of MySql - need help

[Tobias Asplund]

 Hello,

 What do I need to add to the my.ini file?  Can anyone post a typical setup
 of this as an example?

I install with mysqld-nt-max --install servicename
servicename in those cases are MysQL40, MySQL41 and MySQL50

Those are the relevant rows:

[mysqld]


[mysql40]
basedir = C:/mysql/4.0-tree/
datadir = C:/mysql/4.0-tree/data
port= 3307


[mysql41]
basedir = C:/mysql/4.1-tree
datadir = C:/mysql/4.1-tree/data
port= 3308

[mysql50]
basedir = C:/mysql/5.0-tree
datadir = C:/mysql/5.0-tree/data
port= 3309



cheers,
Tobias

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Strange ORDER BY question

[Tobias Asplund]

On Mon, 12 Jan 2004, Lewis, Jason wrote:

 Okay I have a field in my db called MemberLevel in this field you can be one of 5 
 levels.

 Platinum
 Gold
 Silver
 Paying
 Non-Paying


 now my question is, how would I ORDER BY MemberLevel and get it to come out in the 
 above order? I have been racking my brains for a week on this one and any help will 
 be appreciated.


Can think of 3 options offhand.

1. ENUM() and order by enumcol, this will order them in the order you
specify them in the ENUM('col1', ...) order.

2. Break it out into another table andgive them ids in the order they
should be sorted

3. use a CASE statement:
SELECT col, CASE col WHEN 'Platinum' THEN 1 WHEN 'Gold' THEN 2 ... END AS
sortby ORDER BY sortby


cheers,
Tobias

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Pulling numbers out of a column for a query

[Tobias Asplund]

On Mon, 5 Jan 2004, Hal Vaughan wrote:

 I've found an odd problem in queries.

 I have a lot of data regarding vehicle speeds in a survey.  All the data is in
 the form: xx/yy, for example 43/55 means that vehicle was clocked at 43 miles
 per hour in a 55 miles per hour zone.  80/55 means we have a serious speed
 demon, going 80 in a 55 zone.

 I need to be able to pull out data depending on speeds in relation to the
 speed limit, for example, find out how many are less than 55/55 or 45/45, or
 how many are more than 5 miles over (like 61/55 or 32/25).

mysql SELECT SUBSTRING_INDEX(speed, '/', 1) AS clocked,
-   SUBSTRING_INDEX(speed, '/', -1) AS zone, ...
- WHERE SUBSTRING_INDEX(speed, '/', 1)  SUBSTRING_INDEX(speed, '/',
-1) AS zone


That is for finding all the speeders, for example.

cheers,
Tobias

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL 4.1 Production Release

[Tobias Asplund]

On Mon, 5 Jan 2004, Allen Weeks wrote:
 Hi All,

 Just a quick question, does anyone have a good estimate of when ver 4.1 will
 go production.

When known bugs are fixed.
You can read up on MySQL's release policy here:
http://www.mysql.com/doc/en/Release_philosophy.html

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Current server charset?

[Tobias Asplund]

On Tue, 6 Jan 2004, Martijn Tonies wrote:

 Hi,

 In MySQL 4.1, is there a function to know what the
 current default server-wide characterset is?

You can find all those with:
SHOW VARIABLES LIKE 'character_set%'

cheers,
Tobias

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: MySQL 4.1 Production Release

[Tobias Asplund]

On Tue, 6 Jan 2004, Knepley, Jim wrote:

 Any chance that there's a quarterly strategic roadmap published
 somewhere?

 I have projects that sometimes depend on a feature in the next rev' or
 some such, and I need to plan out for my organization... Difficult to
 answer my boss when the dependencies are released when they're ready.


This is a snip from the 5.0.0 release message:

THe MySQL 4.1 branch seams to be relatively stable and we will, if we
don't find any new unexpected hard bugs that will require new design
decisions, make a beta release of 4.1 in January followed by a gamma
release ASAP.


cheers,
Tobias

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Reference to a command that I can not find AND Foriegn Key information

[Tobias Asplund]

On Tue, 6 Jan 2004, Luc Foisy wrote:

 There was a user comment under the Foriegn Key section of the documentation reading:
 To restore from a mysqldump file that uses foreign keys:

 mysql SET FOREIGN_KEY_CHECKS = 0;
 mysql SOURCE your_dump_file;
 mysql SET FOREIGN_KEY_CHECKS = 1;



 The command I am looking for is the call SOURCE. Where is this in the documentation?

http://www.mysql.com/doc/en/Batch_Commands.html


 Also, will this FOREIGN_KEY_CHECKS ignore the definitions while creating the tables, 
 as in not create them, or just merely not check for its consitancy?

Just doesn't check if there's a valid value in the parent table that it
refers to and will not give an error.


 Is any of the above limited to the InnoDB type?

Both

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: struggling newbie - datetime or timestamp problem

[Tobias Asplund]

On Mon, 5 Jan 2004, Matthew Stuart wrote:

 I am trying to create a couple of columns (one createddate and one
 updateddate) in a MySQL table that are DATETIME or TIMESTAMP values,
 but I am having trouble understanding how it works. I want both columns
 to auto add a date and time when a record is first inserted, but only
 the updateddate column to update when the record is updated.

I would use a DATETIME for the created date, and a TIMESTAMP for the
changed.
The reason being that the creation date is only inserted once, and it
won't change, and changed using TIMESTAMP will automaticly update when the
record is changed, so no complex application logic here (well, maybe not
very complex, but I won't have to think about it, it's MAGIC!).


 Could someone give me the code for these two columns please? This is
 what I have:

 CREATE TABLE all_articles (
 id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
 createddate DATETIME DEFAULT -00-00 00:00:00
 updateddate DATETIME DEFAULT -00-00 00:00:00
 article TEXT
 );

CREATE TABLE all_articles (
  id SMALLINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  createddate DATETIME DEFAULT '-00-00 00:00:00',
  updateddate TIMESTAMP,
  article TEXT
);

Should work.

 Obviously these aren't going to auto add/update because I am not stating
 a NOW() or NULL value anywhere, but I thought this would be the cleanest
 way to give it to you. The way the zeros, hyphens and colons are typed
 is how I would like to store my dates if at all possible.

The only issue here is that when you insert a new article you need to set
the createdate column to NOW().

ie,
INSERT INTO all_articles (createddate, article) VALUES (NOW(), 'Article
text goes here');

cheers,
Tobias

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: insert: auto increment field

[Tobias Asplund]

This bug was fixed to 4.0.17, it happened when you inserted a negative
value manually into an auto_increment column

for more info see:
http://bugs.mysql.com/bug.php?id=1366



On Mon, 5 Jan 2004, Donald Henson wrote:
 Please post your table schema. As to why bignbr rather than zero, I'll
 have to defer to the experts.

 Don Henson

 On Sun, 2004-01-04 at 16:52, Mike Mapsnac wrote:
  I use both metods and they works  But when I make another insert I receive a
  message
  ERROR: 1062 Duplicate entry '2147483647' for key 1.
 
  Why id (primaty , and auto_increment) start from 2147483647 and not from 0
  or 1
 
  Thanks

cheers,
Tobias

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Best Method for Learning mysql

[Tobias Asplund]

On Mon, 5 Jan 2004, Marc Dver wrote:

 Based on the collective experiences of the members of this group, what
 are the best methods for learning mysql, both from the perspective of
 certification and of learning enough to excel in the production
 environment?  My interests include both the specifics of mysql and the
 general concepts behind the use and administration of databases in
 general.

I really like Paul DuBois' book; MySQL
The Core certification is a pretty good way to test yourself, and quite a
few I've talked to that has taken it said that just by cramming for the
actual certification exam they learned lots of new things and little tips.

As for general concepts C.J Date's 'An Introduction to Database Systems'
is by many considered the bible for relational databases.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Get counts of col=value with an GROUP BY clause?

[Tobias Asplund]

On Mon, 5 Jan 2004, Greg Owen wrote:

 I tried (you can laugh here) to do it this way, but failed miserably:

 mysql select class,count(questnum),count(difficulty='0'),
count(difficulty='1'),count(difficulty='2'),
count(in_use='0'),count(in_use='1') from Questions
group by class;


You can use SELECT class, COUNT(questnum), SUM(IF(difficulty = 0, 1,
0)), SUM(IF(difficulty = 1, 1, 0)), ...


cheers,
Tobias

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL certification

[Tobias Asplund]

On Mon, 5 Jan 2004, Douglas Sims wrote:

 The test was a bit harder than I anticipated.  I should have paid more
 attention to column types and database name, among other things.  But I
 did pass - at least, the preliminary report said pass, but also said
 that the exam will be reviewed and If you have met the passing score,
 you will receive your official certificate by mail.  This puzzles me,
 because this exam isn't beta any more?

It's the Professional exam that is in beta.

Once you say you're done reviewing you should get a PASS or FAILED on the
screen imediately.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Replicating Table Schema

[Tobias Asplund]

On Fri, 2 Jan 2004, Roger Baklund wrote:

 * Gohaku
  I was just curious if there's a shorthand way of replicating a Table
  Schema.
  I use the following to create a new Table with the same schema.
   create table new_table ( select * from table);
   delete from new_table;

 You can avoid the DELETE by specifying a non-true WHERE clause:

 CREATE TABLE new_table SELECT * FROM table WHERE 1=0

 URL: http://www.mysql.com/doc/en/CREATE_TABLE.html 


Do, however, note that this does not copy the index-structure, nor
attributes like auto_increment.

You can use CREATE TABLE table_copy LIKE table_original

Or if you want the whole create statement just to store can use mysqldump
from outside the client or use SHOW CREATE TALE table_original



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Time series

[Tobias Asplund]

http://www.mysql.com/doc/en/example-Maximum-column-group-row.html

might help you do what you're looking for.




On Thu, 1 Jan 2004, Schulman, Michael wrote:

 As far as I know min(price) and max(price) will return the lowest and higest
 price, not the  first and last in the group.  Again I know first and last
 break the paradaigm of SQL's bucket mentality but it is crucial to doing
 timeseries analysis.  And timeseries aggregation as the query I gave is
 trying to do.

 Thanks again,
   Mike

 -Original Message-
 From: Fredrick Bartlett [mailto:[EMAIL PROTECTED]
 Sent: Thursday, January 01, 2004 10:12 PM
 To: Schulman, Michael; [EMAIL PROTECTED]
 Subject: Re: Time series


 Is Hour a DateTime? If so, will this work...

 SELECT ticker, DATE_FORMAT(Hour,'%H' ), min(price), max(price)
 from pricedata
 order by DATE_FORMAT(Hour,'%H' )
 group by DATE_FORMAT(Hour,'%H' )


 - Original Message -
 From: Schulman, Michael [EMAIL PROTECTED]
 To: 'Fredrick Bartlett' [EMAIL PROTECTED];
 [EMAIL PROTECTED]
 Sent: Thursday, January 01, 2004 6:59 PM
 Subject: RE: Time series


  That only returns one number.. what we are really looking for is something
  like
 
  SELECT ticker, hour, first(price), last(price) from pricedata group by
 hour
 
  Sorry for the confusion.
 
  Thanks,
  Mike
 
  -Original Message-
  From: Fredrick Bartlett [mailto:[EMAIL PROTECTED]
  Sent: Thursday, January 01, 2004 9:57 PM
  To: Schulman, Michael; [EMAIL PROTECTED]
  Subject: Re: Time series
 
 
  Hmmm...
  First: select * from table1 order by field1 asc limit 1
  Last: select * from table1 order by field1 desc limit 1
 
 
  - Original Message -
  From: Schulman, Michael [EMAIL PROTECTED]
  To: [EMAIL PROTECTED]
  Sent: Thursday, January 01, 2004 6:47 PM
  Subject: Time series
 
 
   Hi,
  
   I work for a large financial instituition.  We are currently evaluating
   databases to store intraday stock data.  These are large tables with 40
   million rows per day.  We've done some initial testing with MySQL and
 have
   been extremely impressed with its speed and ease of use.  I know that it
   goes agains the SQL standard but adding a FIRST,LAST aggregate function
   along with some other time series functions would allow mysql to compete
   with just about any timeseries database, and open up mysql to a huge
  market
   of financial firms.  I know my firm would most likely purchase it.
 Has
   anyone developed anyhting like this as an add on?
  
   Thanks,
   Mike
  
 
  --
  
   This message is intended only for the personal and confidential use of
 the
   designated recipient(s) named above.  If you are not the intended
  recipient of
   this message you are hereby notified that any review, dissemination,
   distribution or copying of this message is strictly prohibited.  This
   communication is for information purposes only and should not be
 regarded
  as
   an offer to sell or as a solicitation of an offer to buy any financial
   product, an official confirmation of any transaction, or as an official
   statement of Lehman Brothers.  Email transmission cannot be guaranteed
 to
  be
   secure or error-free.  Therefore, we do not represent that this
  information is
   complete or accurate and it should not be relied upon as such.  All
   information is subject to change without notice.
  
  
   --
   MySQL General Mailing List
   For list archives: http://lists.mysql.com/mysql
   To unsubscribe:
  http://lists.mysql.com/[EMAIL PROTECTED]
  
 
 
 
  --
 
  This message is intended only for the personal and confidential use of the
  designated recipient(s) named above.  If you are not the intended
 recipient of
  this message you are hereby notified that any review, dissemination,
  distribution or copying of this message is strictly prohibited.  This
  communication is for information purposes only and should not be regarded
 as
  an offer to sell or as a solicitation of an offer to buy any financial
  product, an official confirmation of any transaction, or as an official
  statement of Lehman Brothers.  Email transmission cannot be guaranteed to
 be
  secure or error-free.  Therefore, we do not represent that this
 information is
  complete or accurate and it should not be relied upon as such.  All
  information is subject to change without notice.
 


 --
 This message is intended only for the personal and confidential use of the
 designated recipient(s) named above.  If you are not the intended recipient of
 this message you are hereby notified that any review, dissemination,
 distribution or copying of this message is strictly prohibited.  This
 communication is for information purposes only and should not be regarded as
 an offer to sell or as a solicitation of an offer 

Re: Multiple Roles

[Tobias Asplund]

On Fri, 2 Jan 2004, Caroline Jen wrote:

 In case that a user has multiple roles; for example,
 John Dole is both author and editor,

 1. I should have two rows for John Dole?

John Dole author
John Dole editor

or. I should have only one row and use comma ',' to

separate the roles?

John Dole author, editor

 2. How do I create the table for the second case (see
 below)?

   create table user_roles (
   user_name varchar(15) not null,
   role_name varchar(15) not null, varchar(15) null
   );


If the roles will not be very dynamic and could be hardcoded you might be
able to use the SET datatype which is described here:
http://www.mysql.com/doc/en/SET.html

If you will add/update/change/delete roles often, then you should go for
one of the other methods suggested instead, but if the roles are static,
this might work better for you.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Change from loop to single query

[Tobias Asplund]

On Fri, 2 Jan 2004, Jonathan Villa wrote:

 I have a loop which is similar to the following:

 while(array contains elements) {
 UPDATE users SET status = no WHERE name = array[i]
 }
 great, it works but the query runs many times. I want to make only one
 call to the database and have all the elements in the array be included
 in the query


UPDATE users SET status = no WHERE name IN('name1', 'name2', 'name3', ...,
'namen')

you might still have to loop to remake your array to a commaseparated list
of strings, but it will still be boatloads more efficient.

If you have several hundred thousands or more of names and get an error,
try upping your max_allowed_packet variable.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: One Slave Many Master

[Tobias Asplund]

On Mon, 29 Dec 2003, Leo wrote:
 I know someone already ask this,
 and the answer generally 'NO YOU CANT' :b

 but, is there any work around so i can make a backup server (slave),
 from many other server (master) through replication?

You could run a server instance per database replicated and use the same
data-dir (be careful of conflicting writes, however, you probably want to
enable-external-locking, especially if they share any data).

 oh, one more thing, can the replication run both way?
 what ever happened on machine A, replicated on machine B and vice versa..

That works fine, just be aware of the problems that can arise if this is
not taken into consideration since the replication is asynchronous

read the Q: What issues should I be aware of when setting up two-way
replication? part at
http://www.mysql.com/doc/en/Replication_FAQ.html


 thanks...

 --
 Regards
 Leonardus Setiabudi
 IT Project Coordinator
 PT Bina San Prima, www.binasanprima.com



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Replication Question

[Tobias Asplund]

On Mon, 22 Dec 2003, Jeff McKeon wrote:

 Is it possible to have 2 database on one server replicating from the
 same Master server?
yes.

 In other words.  DB01 is the Master on System01,
 System02 has DB01_rep1 and DB01_rep2, each with their own replication
 from DB01.
Shouldn't be a problem.

 I need to do this to have a development copy of DB01 on System02.  I
 have production software that pulls data
 from DB01_rep1 and I need to set up DB01_rep2 to do some development
 work.

 When I copy the DB01 snapshot into a new database (DB01_rep2) on
 System02 and then log into that database and do a Show slave status it
 shows me the slave status information that was set up for DB01_rep1.
Just make sure that you're not running against the same logfiles/data, so
you can have different relay-logs and master.info files.


 Is replication System dependant or database dependant?
Neither, it's server instance dependant

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: can't start the server

[Tobias Asplund]

Can the user the mysqld process run as (usually the mysql user) read/write
the /var/lib/data directory without problems?
Does your /etc/my.cnf file contain a
datadir = /var/lib/mysql
or is it pointing somewhere else?
Did adding skip-innodb to your my.cnf solve anything?



On Fri, 19 Dec 2003, landon kelsey wrote:

 I run safe_mysqld out of /usr with ./bin/safe_mysqld 

 /var/log/mysqld.log

 868 031219 20:52:52  mysqld started
 869 Cannot initialize InnoDB as 'innodb_data_file_path' is not set.
 870 If you do not want to use transactional InnoDB tables, add a line
 871 skip-innodb
 872 to the [mysqld] section of init parameters in your my.cnf
 873 or my.ini. If you want to use InnoDB tables, add to the [mysqld]
 874 section, for example,
 875 innodb_data_file_path = ibdata1:10M:autoextend
 876 But to get good performance you should adjust for your hardware
 877 the InnoDB startup options listed in section 2 at
 878 http://www.innodb.com/ibman.html
 879 031219 20:52:52  /usr/libexec/mysqld: Can't find file:
 './mysql/host.frm' (errno: 13)
 880 031219 20:52:52  /usr/libexec/mysqld: Normal shutdown
 881
 882 031219 20:52:52  mysqld ended

 I've even tried moving host.frm around.

 The manual says that mysql_install_db may not have run or to try

 ./bin/safe_mysqld  

 I've tried all combinations of the above.

 ps -ef | grep -i sql  shows nothing so the server is NOT running

 mysql does not work
 ERROR 2002: Can't connect to local MySQL server through socket
 '/var/lib/mysql/mysql.sock' (2)

 .. PREVIOUS HISTORY
 (1) performed a standard install of Redhat Linux 9 months ago
 (I found mysql 3.23.52 installed)
 I executed mysql_install_db successfully (manual says it is safe to
 rerun)
 installing all prepared tables
 031219  0:44:16  /usr/libexec/mysqld: Shutdown Complete


 To start mysqld at boot time you have to copy
 support-files/mysql.server
 to the right place for your system

 PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
 This is done with:
 /usr/bin/mysqladmin -u root  password 'new-password'
 /usr/bin/mysqladmin -u root -h landon  password 'new-password'
 See the manual for more instructions.

 NOTE:  If you are upgrading from a MySQL = 3.22.10 you should run
 the /usr/bin/mysql_fix_privilege_tables. Otherwise you will not be
 able to use the new GRANT command!

 You can start the MySQL daemon with:
 cd /usr ; /usr/bin/safe_mysqld 

 You can test the MySQL daemon with the benchmarks in the 'sql-bench'
 directory:
 cd sql-bench ; run-all-tests

 Please report any problems with the /usr/bin/mysqlbug script!

 The latest information about MySQL is available on the web at
 http://www.mysql.com
 Support MySQL by buying support/licenses at https://order.mysql.com


 (2) safe_mysqld   produces
 landon (root) /usr/bin [311]safe_mysqld 
 [1] 3247
 landon (root) /usr/bin [312]Starting mysqld daemon with databases
 from
 /var/lib/mysql
 031219 00:25:56  mysqld ended

 [1]+  Donesafe_mysqld

 (3) tried debug
 landon (root) /usr/bin [316]!305
 cat /var/lib/mysql/xxx.log
 /usr/libexec/mysqld, Version: 3.23.52-log, started with:
 Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
 Time Id CommandArgument

 (4) here is what mysql.sock looks like
 landon (root) /usr/bin [303]lsa /var/lib/mysql
 total 20
 drwxr-xr-x4 mysqlmysql4096 Dec 18 23:42 ./
 drwxr-xr-x   29 root root 4096 Jul  8 18:31 ../
 drwx--2 root root 4096 Dec 18 21:21 mysql/
 srwxrwxrwx1 mysqlmysql   0 Dec 18 23:42 mysql.sock=
 drwx--2 root root 4096 Dec 18 21:21 test/
 -rw-rw1 mysqlmysql 156 Dec 18 22:52 xxx.log

 (5) also tried ./bin/safe_mysqld --user=mysql 
 same result

 (6) tried
 safe_mysqld --debug 
 couldn't find the mysqld.trace file

 (7) mysql doesn't work
 ERROR 2002: Can't connect to local MySQL server through socket
 '/var/lib/mysql/mysql.sock' (2)

 _
 Grab our best dial-up Internet access offer: 6 months @$9.95/month.
 http://join.msn.com/?page=dept/dialup




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: auto increment using even numbers

[Tobias Asplund]

On Fri, 19 Dec 2003, K Q-B wrote:
 I am creating a table and would like to use auto
 increment, but I would like one column to increment in
 only odd numbers 1,3,5... and another column of the
 same table to increment in even numbers 2,4,6...

 Is it possible to do this?

Not without a little tinkering.
You can use a separate table for just the counters, when you need a new id
generated, you can insert a value into it, then when inserting into the
real table you can use
INSERT INTO table SET id_col  = LAST_INSERT_ID() * 2 ...
(or INSERT INTO table (id_col, ...) VALUES (LAST_INSERT_ID() * 2, ...); )


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: SEQUENCES

[Tobias Asplund]

On Mon, 15 Dec 2003, Peter Lovatt wrote:
 Try

 Insert INTO `table` ( `inc_field` ) values (10)

 the auto inc field will then generate the next sequential numbers

 HTH

 Peter

Or just use

ALTER TABLE table AUTO_INCREMENT=10

That way you don't have to enter a record just to set the AUTO_INCREMENT
value.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: LOAD DATA INFILE..

[Tobias Asplund]

On Mon, 15 Dec 2003, Graham Little wrote:

doing selective quoting below.

   LOAD DATA INFILE D:\mysql\sql\CountryData.txt
   INTO TABLE cou (id, country);

See how you try to load from a file into the columns id and country in the
cou table?

 The table the data is being inserted into is:

   +--+-+--+-+-+---+
   | Field| Type| Null | Key | Default | Extra |
   +--+-+--+-+-+---+
   | cou_id   | int(11) |  | PRI | 0   |   |
   | cou_name | text|  | | |   |
   +--+-+--+-+-+---+


And those columns doesn't exist.

 I am getting the following error message and i am not
 sure why?

   Error 1054: Unknown column 'id' in 'field list'


 Any help would be appreciated.


Hope that helps.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Exporting data

[Tobias Asplund]

On Mon, 15 Dec 2003, Roberts, Mark (Tulsa) wrote:

 I have an order taking system where the tables are store in a MySql database. I need 
 to develop a select statement to output all new orders to a .csv formatted file.

 Is this possible to do in MySql. I would try looking this up, however, I am not even 
 sure what to look for in the documentation. Any help that I could get to lead me in 
 the right direction would be appreciated.  Thanks.

 Mark Roberts




http://www.mysql.com/doc/en/SELECT.html

Check out the bit about INTO OUTFILE.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: MySQL or MaxDB or PostgreSQL or Interbase/Firebird or ?

[Tobias Asplund]

 Sven Köhler wrote:

  I set the isolation level to READ_REPEATABLE and use mysqldump |
  bzip2 to get the result. I've tested the restore and it's fine!
 
 
  So how does mysqldump handle binary data?
 
  If it does embed the data into the SQL-statement somehow, that's crap,
  since SQL-Statements are limited in length.

On Tue, 16 Dec 2003, Chris Nolan wrote:

 Are they? Shoving in rows that are several meg in size didn't pose any
 problems. The restore procedure looked like this:

 bunzip2 dumpfile | mysql -u db_grunt -p projectdb

 May I ask where the limitation you mentioned is documented? Maybe the
 situations we were using it in didn't come close to the limit.

 Regards,

 Chris


This is limited by the max_packet_size variable.
In 3.23.x it's limited to 16Mb, in 4.0+ it is limited by 2Gb or the amount
of physical memory the machine has, whichever is less.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Best way to get value of autoincriment after inserting NULL?

[Tobias Asplund]

On Mon, 15 Dec 2003, Paul Fine wrote:

 If I have a table like with a column being the PK for the table and being an
 Auto Increment value, what is the best way to return this value to my
 script?

If you insert a row LAST_INSERT_ID() will return the primary key value in
this setup.

The other way is if you're not inserting a record, but just want to know
the next value that will be used.
SHOW TABLE STATUS LIKE 'tablename';


 It is possible that additional rows may have been added during the
 small wait.

It is possible another value is inserted in between the query for the next
number and an insert afterwards.
To stop this from happening you can place a read-lock on the table, this
won't allow any other processes to insert rows until you release the lock.

LOCK TABLE tablename READ;
Get Auto-increment value

Do your stuff...

UNLOCK TABLES;

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: replication/binary log

[Tobias Asplund]

On Tue, 9 Dec 2003, Mayuran Yogarajah wrote:

 Diana Soares wrote:

 Use PURGE {MASTER|BINARY} LOGS TO 'log_name' instead of RESET
 MASTER.
 From the manual:
 
 
 Deletes all the binary logs listed in the log index that are strictly
 prior to the specified log or date. The logs also are removed from this
 list recorded in the log index file, so that the given log now becomes
 the first.
 (...)
 You must first check all the slaves with SHOW SLAVE STATUS to see which
 log they are reading, then do a listing of the logs on the master with
 SHOW MASTER LOGS, find the earliest log among all the slaves (if all the
 slaves are up to date, this will be the last log on the list), backup
 all the logs you are about to delete (optional) and purge up to the
 target log.
 
 http://www.mysql.com/doc/en/PURGE_MASTER_LOGS.html
 
 
 

 That worked quite nicely, thank you :) Is there some reason why
 MySQL keeps these log files ? Why wouldn't it delete them as a
 new one got created?

 thanks


You can also use those to restore a database that is lost somehow.
Just run all the binary logs and pipe them into mysql from the last
snapshot you have taken a backup from.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Setting MySql Port

[Tobias Asplund]

On Wed, 10 Dec 2003, Gavin Dimmock wrote:

 Hi All,

 I want to change the default port on mysql server from 3306 to 5 (for
 example). The server is NT. Has anyone done this before?

 Any help really appreciated,


Can either modify the service to start with the --port parameter or add in
your my.cnf (or my.ini, whichever you're using) the line
port = 5
under the [mysqld] section.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: = not working?

[Tobias Asplund]

You have a few ways to do this.
What's happening here is that you do a comparison in a string context,
which means that it will sort according to the ascii values, and 1 comes
before 8.
To sort the way you want you need to specify to MySQL that you want to do
it in a numeric context.
You have two simple ways to do this.

Add 0 to the string number to force it into numeric mode.

AND squarefeet = '$squarefeet' + 0

use the CAST() function, it's described more at:
http://www.mysql.com/doc/en/Cast_Functions.html


On Mon, 8 Dec 2003, Ed Curtis wrote:


 I've got an entry in a table where the value is 875. If I run a query on
 that table with the clause AND sqaurefeet = '$squarefeet' and $squarefeet
 has a value of say 1000 the row with the squarefeer value of 875 will
 appear. The column is of type varchar(10) and using the binary flag.

 What am I doing wrong?

 Thanks,

 Ed





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Once again, three queries, same result, huge speed difference

[Tobias Asplund]

On Thu, 4 Dec 2003, Uros Kotnik wrote:

 I posted this few days ago, but with no answer, also posted it to
 benchmark list..

 Executing this SQL, takes ~5 sec.

 select artists.name , cds.title , tracks.title  from artists, tracks,
 cds
 where artists.artistid = tracks.artistid and cds.cdid = tracks.cdid
 and MATCH (artists.name) AGAINST ('madonna')
 and MATCH (cds.title) AGAINST ('music')
 and MATCH (cds.title) AGAINST ('mix')
 and MATCH (cds.title) AGAINST ('2001')

 and this, ~40 sec.

 select artists.name , cds.title, tracks.title from artists, tracks, cds
 where artists.artistid = tracks.artistid and cds.cdid = tracks.cdid
 and MATCH (artists.name) AGAINST ('madonna' IN BOOLEAN MODE)
 and MATCH (cds.title) AGAINST ('music mix 2001' IN BOOLEAN MODE)

 and executing this takes less than 1 sec.

 select artists.name , cds.title, tracks.title from artists, tracks, cds
 where artists.artistid = tracks.artistid and cds.cdid = tracks.cdid
 and artists.name like '%madonna%'
 and cds.title like '%music mix 2001%'


 Same result but the speed difference is quite a different, why is that ?

 This is only on test DB, I didn't try it on real life DB where I have
 ~14 mil. rows in tracks table.


 Regards



Can you post EXPLAIN SELECT of those queries as well, please?

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: can't find FULLTEXT index

[Tobias Asplund]

If you do a fulltext search on multiple columns at once, there must be a
combined fulltext index on this exact set of columns. Just having an index
on them individually will not work.



On Wed, 3 Dec 2003, Mirza wrote:

 Hi,

 I have error 1191 can't find fulltext index matching the column list,
 but I am certain that all columns in the list are present in my Fulltext
   index. Does anybody else had this problem? I am using 4.1.0.

 thanks,
 Mirza





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: How to 'customize' GROUP BY?

[Tobias Asplund]

I think that
http://www.mysql.com/doc/en/example-Maximum-column-group-row.html
covers your problem here.

You either have to solve it with Temporary tables, the MAX-Concat trick
(in the url above) or a subquery (which will be more inefficient than the
other two options).


On Thu, 20 Nov 2003, Yves Goergen wrote:

 Hi again...
 yet another question to this list that maybe someone can easily answer me...

 When I do a GROUP BY on a column of my query, I'll get one random row from
 the entire group. But how can I tell MySQL to, i.e., give me the row with
 the highest value of another column or so? I mean something like

 SELECT id, grp FROM tbl GROUP BY grp ORDER BY id

 but with the ORDER BY relating to the GROUP... I don't know how to express
 this in SQL since it doesn't seem to be possible?



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Missing mysql.sock

[Tobias Asplund]

On Sat, 8 Nov 2003, Michael Satterwhite wrote:

 This has got to be a common question, but I'd really appreciate a little help.

 I recently reinstalled my Linux (SuSE 8.2). I *KNOW* I don't have a cron run
 that deletes this.

 When I try to start mysql, I get the message

 Can't connect to local MySQL server through socket
 '/var/lib/mysql/mysql.sock' (2)

 Using locate, I see that, indeed, it is not there. I'm using MySQL 4.1.0

 I've tried running mysqld_safe. It tries to start the mysqld, then I
 immediately get the message that mysqld ended. It doesn't start.

 How do I get it going again?


If your server is not running (check with a `ps -aux` call) then check
your error logs.

If the server IS running however, it's likely that your client tries to
connect through /var/lib/mysql/mysql.sock while the server listens
somewhere else.
To find out if that is the case, run `netstat -u | grep mysql.sock` and
see where the server is listening.
If the server is listening on, for example /tmp/mysql.sock , then you can
make your client use that by editing your ~/.my.cnf (or /etc/my.cnf, or
any of the other locations possible) with the row
socket = /tmp/mysql.sock
under the [client] group (or change the server's by entering the same row
with the other path under the [mysqld] group).

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: mysql max

[Tobias Asplund]

It depends on your filesystem's and OS's max-size of a file in its
filesystem.

HOWEVER, there are a few ways you can get around this.

You can use InnoDB tables, or read about RAID types here:
http://www.mysql.com/doc/en/CREATE_TABLE.html (almost at the bottom of the
page).
You can also use MERGE tables:
http://www.mysql.com/doc/en/MERGE.html

On Fri, 17 Oct 2003, nm wrote:

 Hi

 what's the max table size we can use with mysql-max version?

 Suggestions?

 Thanks.






-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Question on SELECT support

[Tobias Asplund]

MySQL doesn't support TOP, however, there's a LIMIT syntax for MySQL that
roughly does the same thing:
http://www.mysql.com/doc/en/SELECT.html for a brief explanation.

In your example what you are looking for is:
 SELECT DateCreatedField
 FROM my_table
 ORDER BY DateCreatedField DESC
 LIMIT 10;


On Fri, 17 Oct 2003, Dale Hans wrote:

 Hi,

 I am trying to select the first 10 rows of a table by the date the
 record was created. I have a date field in the table and I tried using
 the TOP syntax of SELECT, but I keep getting syntax error.

 SELECT TOP 10 DateCreatedField
 FROM my_table
 ORDER BY DateCreatedField DESCENDING

 Does MySQL not support TOP?

 If it does, can somebody tell me what is wrong with that syntax?

 If it doesn't, can someone please tell how I can do it without getting
 ALL records and extracting the first 10 programmatically?

 Thanks,
 Dale Hans





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: natural sorting

[Tobias Asplund]

If you have 4.0.2 or later you can use the CAST() function, if you have
earlier you should be able to emulate it with the BINARY keyword for the
ORDER BY clause, examples below:

[EMAIL PROTECTED]:tmp  CREATE table sort (
-   num int
- );
Query OK, 0 rows affected (0.00 sec)

[EMAIL PROTECTED]:tmp  INSERT sort (num) VALUES (1), (2), (3), (10), (11);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

[EMAIL PROTECTED]:tmp  SELECT num FROM sort ORDER BY num;
+--+
| num  |
+--+
|1 |
|2 |
|3 |
|   10 |
|   11 |
+--+
5 rows in set (0.00 sec)

[EMAIL PROTECTED]:tmp  SELECT num FROM sort ORDER BY BINARY num;
+--+
| num  |
+--+
|1 |
|   10 |
|   11 |
|2 |
|3 |
+--+
5 rows in set (0.00 sec)

[EMAIL PROTECTED]:tmp  SELECT num FROM sort ORDER BY CAST(num AS CHAR);
+--+
| num  |
+--+
|1 |
|   10 |
|   11 |
|2 |
|3 |
+--+
5 rows in set (0.00 sec)


On Thu, 16 Oct 2003, Michael Winston wrote:

 Is there any way to do natural sorting in MySQL?  That is:
 Chapter 1
 Chapter 2
 Chapter 10

 instead of

 Chapter 1
 Chapter 10
 Chapter 2

 ?

 If not, are there any plans for this feature in future releases?
 Would be pretty helpful...

 Michael



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Command line fails mysql mydb mufile.sql

[Tobias Asplund]

http://www.mysql.com/doc/en/Option_files.html
Check the example halfway down the page, it shows how you can supply a
password through your my.cnf file.


 I can log in as -u administrator when I use the -p option.  I need to
 make it work
 from  command line  because I am planning to put this command in
 a script to make it automatic to insert records.

 Nestor :-)

 Nestor A. Florez


  Tobias Asplund [EMAIL PROTECTED] 10/15/2003 12:02:21 PM 

  c:\mysql mydb  myfile.sql
  ERROR 1044: Access denied for user: '@localhost' to database 'mydb'

 try mysql -u administrator mydb  myfile.sql




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Repairing a large table takes weeks!

[Sebastian Tobias Mendel genannt Mendelsohn]

About two weeks ago I received The table Worklist is full error. Since
what type of table? MyISAM?
how big is 'full'?
If you don't specify any of the above options, the maximum size for a 
table will be 4G (or 2G if your operating systems only supports 2G tables).

i dont know how big files can be on NTFS/WinXP.

--
Sebastian Mendel
www.sebastianmendel.de
www.tekkno4u.de
www.nofetish.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Suboptimal index-usage with FULLTEXT-search

[Tobias Lind]

in most situations. Then we would use another index if it would result in
less rows than 1000 (not so many in my large table). If that assumption
would be incorrect, the worst thing that could happen would be that another
index (with less that 1000 possible rows) would be used instead even though
the fulltext-index would have been better. But that's not so bad - we would
scan maximum 999 rows with the use of another index - no problem! ...not
compared to my 100.000 rows scans that's the result of picking a
fulltext-index when it gives a lot of matches...

The best solution would of course be that MySQL could estimate the number of
possible rows from the boolean fulltest-search. I guess that this is
impossible.
The next best thing would be to be able to manually configure the minimum
estimated number of rows from a boolean search as a hint to the MySQL
optimizer... Right now this estimation is always 1, I'd like to set it
higher!!

Best regards,
Tobias Lind



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SubQueries and IN

[Sebastian Tobias Mendel genannt Mendelsohn]

Andy Hall wrote:

Hi,

I have just started using MySQL from MSSQL 7. I need to port the following
into MySQL from an existing (working) query on MSSQL Server:
SELECT product_id, name, description
FROM products
WHERE product_id NOT IN (SELECT product_id FROM sales WHERE customer_id =
10)
i.e. get all the products that a particular customer has not already bought

This errors, and I have since read that the MySQL IN does not allow
sub-queries, but also seen examples of it done. Is it only supported in a
later version? We are running v. 3.23.3.
subqueries requieres 4.x

try

SELECT product_id, name, description
FROM products
LEFT JOIN sales
ON products.product_id = sales.product_id
WHERE NOT sales.customer_id = 10

I have also tried:

SELECT product_id, name, description, sales.sale_id
FROM products LEFT JOIN sales ON products.product_id = sales.product_id
WHERE sales.customer_id = 10 AND sales.sale_id IS NULL
This does not return any records as it seems to ignoring the LEFT JOIN part
when I stick on the WHERE sales.customer_id = 10.
(pretty sure this query would work in MS-SQL)
this seems a bit different then this before

but should work, does

SELECT *
FROM sales
WHERE sales.customer_id = 10
AND sales.sale_id IS NULL
return any results?


There must be a way to do this, but I dont seem to be able to put my finger
on it and I would appreciate any help!


--
Sebastian Mendel
www.sebastianmendel.de
www.tekkno4u.de
www.nofetish.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SubQueries and IN

[Sebastian Tobias Mendel genannt Mendelsohn]

SELECT product_id, name, description, sales.sale_id
FROM products LEFT JOIN sales ON products.product_id = sales.product_id
WHERE sales.customer_id = 10 AND sales.sale_id IS NULL


This query should return no rows, because if you retrieve rows where sales.sale_id is NULL, customer_id for these rows also will be NULL, not 10.


you are wrong, or do you know the table-structure?
sales.sale_id can be NULL while customer_id can be 10 !
--
Sebastian Mendel
www.sebastianmendel.de
www.tekkno4u.de
www.nofetish.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

It is secure to access MySQL thru internet?

[Flavio Tobias]

I need to access a database thru internet. It is secure to do this using MySql?


Thanks 
Flavio Tobias

Re: It is secure to access MySQL thru internet?

[Flavio Tobias]

What do you mean with secure connection, ssh?
How to configure this on MySql?

- Original Message -
From: Fortuno, Adam [EMAIL PROTECTED]
To: 'Flavio Tobias' [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Friday, August 29, 2003 2:17 PM
Subject: RE: It is secure to access MySQL thru internet?


 Sure. If you use a secured connection.

 A$

 -Original Message-
 From: Flavio Tobias [mailto:[EMAIL PROTECTED]
 Sent: Friday, August 29, 2003 1:11 PM
 To: [EMAIL PROTECTED]
 Subject: It is secure to access MySQL thru internet?


 I need to access a database thru internet. It is secure to do this using
 MySql?


 Thanks
 Flavio Tobias

 --
 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 excution time

[Tobias Schittkowski]

If you want a simple solution for windows, check
RSSmyperfmon
www.rss-software.de/en

 MTop (MySQL Top) is a really nice little utility:
 http://mtop.sourceforge.net/

 Shows what queries are running, and elapsed time.

 Daivd
 - Original Message -
 From: Prem Soman [EMAIL PROTECTED]
 To: mysql groups [EMAIL PROTECTED]
 Sent: Saturday, June 21, 2003 12:02 AM
 Subject: Query excution time


  hi !
 
  what is the best way to find query excution time in
  MySql
 
  plz help me!
 
  
  Want to chat instantly with your online friends?  Get the FREE Yahoo!
  Messenger http://uk.messenger.yahoo.com/
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]

 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

ssh problem with mysql_real_connect - repost

[Tobias Schittkowski]

I think I need a MySQL code guru...

I am using the 4.0.12 client library on a Win2k host to
connect to a 4.0.13 linux server.

If I connect directly to the server using mysql_real_connect, everything
runs fine.

However, I want to use SSH tunneling.

So I set up a SSHv2 tunneling connection from localhost:3306 to server:3306.

If I use the command line, i.e. mysql -u foo -pbar -h localhost, I can connect to the
remote server via SSH. The same is, if I use tools like SQLyog. But in my program which
call the mysql C API, the program freezes.

I enable the dbug, here is the output. The program hangs at the last line...

Any ideas how to avoid this problem?
Thanks in advance,
Tobias

my_init
| my_win_init
| my_win_init
| exit: home: '(null)'
my_init
libmysql_init
libmysql_init
my_malloc
| my: Size: 544  MyFlags: 48
| exit: ptr: 4d144c8
my_malloc
mysql_real_connect
| enter: host: 127.0.0.1  db: rssvertrieb  user: rss
| info: Server name: '127.0.0.1'.  Named Pipe: MySQL
| error: host: '127.0.0.1'  socket: ''  named_pipe: 0  have_tcpip: 1
| info: Server name: '127.0.0.1'.  TCP sock: 3306
| vio_new
| | enter: sd=1140
| | my_malloc
| | | my: Size: 84  MyFlags: 16
| | | exit: ptr: 4d124b0
| | my_malloc
| | vio_reset
| | | enter: type=1  sd=1140  localhost=0
| | vio_reset
| vio_new
| my_net_init
| | my_malloc
| | | my: Size: 8199  MyFlags: 16
| | | exit: ptr: 4d14720
| | my_malloc
| | vio_fastsend
| | | exit: 0
| | vio_fastsend
| my_net_init
| vio_keepalive
| | enter: sd=1140, set_keep_alive=1
| vio_keepalive
| vio_is_blocking
| | exit: 1

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

strange SSH/libmysql.dll problem - this time with dbug infos

[Tobias Schittkowski]

I think I need a MySQL code guru...

I am using the 4.0.12 client library on a Win2k host to
connect to a 4.0.13 linux server.

If I connect directly to the server using mysql_real_connect, everything
runs fine.

However, I want to use SSH tunneling.

So I set up a SSHv2 tunneling connection from localhost:3306 to server:3306.

If I use the command line, i.e. mysql -u foo -pbar, I can connect to the
remote server via SSH. The same is, if I use tools like SQLyog.

I enable the dbug, here is the output. The program hangs at the last line...

my_init
| my_win_init
| my_win_init
| exit: home: '(null)'
my_init
libmysql_init
libmysql_init
my_malloc
| my: Size: 544  MyFlags: 48
| exit: ptr: 4d144c8
my_malloc
mysql_real_connect
| enter: host: 127.0.0.1  db: rssvertrieb  user: rss
| info: Server name: '127.0.0.1'.  Named Pipe: MySQL
| error: host: '127.0.0.1'  socket: ''  named_pipe: 0  have_tcpip: 1
| info: Server name: '127.0.0.1'.  TCP sock: 3306
| vio_new
| | enter: sd=1140
| | my_malloc
| | | my: Size: 84  MyFlags: 16
| | | exit: ptr: 4d124b0
| | my_malloc
| | vio_reset
| | | enter: type=1  sd=1140  localhost=0
| | vio_reset
| vio_new
| my_net_init
| | my_malloc
| | | my: Size: 8199  MyFlags: 16
| | | exit: ptr: 4d14720
| | my_malloc
| | vio_fastsend
| | | exit: 0
| | vio_fastsend
| my_net_init
| vio_keepalive
| | enter: sd=1140, set_keep_alive=1
| vio_keepalive
| vio_is_blocking
| | exit: 1


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]