Re: Migration

[James Dey]

Thanks!

The line is:


/*!4 ALTER TABLE `jos_banner` */;
LOCK TABLES `jos_banner` WRITE;
INSERT INTO `jos_banner` VALUES (1,1,'banner','OSM 1',0,46,0,'osmbanner1.png
','http://www.opensourcematters.org','2004-07-07$
UNLOCK TABLES;
/*!4 ALTER TABLE `jos_banner` ENABLE KEYS */;

 For the record I had the same issue with another line, and removed the
line:

ENGINE=MyISAM DEFAULT CHARSET=latin1;

This got rid of the error

All the best!

On 2/10/06, Gabriel PREDA <[EMAIL PROTECTED]> wrote:
>
> Not the whole... maybe it's humongous... some lines above  line 20 and
> some
> beneath...
>
> --
> Gabriel PREDA
> Senior Web Developer
>
>
> On 2/10/06, sheeri kritzer <[EMAIL PROTECTED]> wrote:
> >
> > James,
> >
> > You're going to need to show us the contents of olddbname.sql.
> >
> > -Sheeri
> >
> > On 2/10/06, James Dey <[EMAIL PROTECTED]> wrote:
> > > > I am migrating a database from mySQL 4.0 to 4.1.16 and have the
> error
> > > > ERROR 1064 at line 21: You have an error in your SQL syntax.  Check
> > the
> > > > manual that corresponds to your MySQL server version for the right
> > syntax
> > > to
> > > > use near 'ALTER TABLE `jos_banner` */' at line 20
> > > >
> >
> >
>
>

Re: Byte Swapping

[David Godsey]

If I do it in C I need to do a user-defined function correct?

Anyway, I am infact storing the data raw.  Unfortunately byte swapping
before is not an option because I am getting a stream of raw data and
storing it in a blob field.  Then based on some configuration, I pull
parts of the data out of the BLOB, and do conversions on it.  So it a
specific element that I am looking for is defined as and Integer, I query
the BLOB data, do a substring (based on some offset and length) then
perform some conversion (Floats, Doubles, Signed, Unsigned).

You would think, hey why don't you break the data out and store it in the
correct form to make your life easier?  Well, I have to preserve the data
in Raw form, and also, the definitions of the data change (base on some
other engineers decisions).  So it isn't practical nor desirable to
convert the data before I put it in the database.

What I am really looking for (I know byte swapping in C is easy) is a
procedure or a Mysql function that does this.  If I do a UDF then I have
to compile it for several platforms, because this is also supposed to be
very portable.  If it was completely up to me, it wouldn't be this way,
but alas it is not.

David Godsey

> Hi David,
>
>> This might be an unusual question because mysql always stores in little
>> endian for all platforms (if I'm not mistaken).  However, I am receiving
>> data from several different platforms (custom boards with various
>> processors) in raw form.  I will have stored as a configuration option
>> what endianess it is.
>>
>> So what I need to do is write a procdure, or use a mysql function (if
>> there is one available) to byte swap the data (for integer types).  I'm
>> really just hoping someone has done this, or it is implemented as a
>> mysql
>> function and I just wasn't able to find it.
>
> This seems very strange that you're storing it in raw form.  Are you
> sure that you actually are?
>
> Anyway, swapping between big/little endian is not difficult in C.
>
> The code is all here:
>
>http://jcole.us/svn/libmygis/mygis.h
>
> e.g.:
>
> #define SWAP(x, y)   { (x) ^= (y) ^= (x) ^= (y); }
>
> #define MYGIS_SWAP4(pos)   { \
>SWAP(((byte *)(pos))[0], ((byte *)(pos))[3]); \
>SWAP(((byte *)(pos))[1], ((byte *)(pos))[2]); \
> }
>
> That's enough to swap 32-bits (4-bytes) of whatever is stored at any
> pointer address.
>
> I would encourage that you:
>
> a. make sure you really are storing the data raw -- it seems very odd
> b. stop storing the data raw... do the swap before storing
>
> Can you give more details about what you're doing?
>
> Regards,
>
> Jeremy
>
> --
> Jeremy Cole
> MySQL Geek, Yahoo! Inc.
> Desk: 408 349 5104
>


Accomplishing the impossible means only that the boss will add it to your
regular duties.

David Godsey


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

InnoDB per-table-space backup and move

[HOTorNOT Tech]

Hi,

We'd like to be able to move certain innodb databases between machines 
(some, but not all). This would help greatly with resyncing slaves and 
for fast backups. I know that we can create table-spaces on a per-table 
basis as described here.


http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html

It also says that we cannot freely move tables between database 
directories like in MyISAM, but we can restore backups of .ibd files. Is 
there any way to move the .ibd files from one machine to another? Is 
there dependency on the shared tablespace and the corresponding .ibd file?


i.e. can we copy a .ibd file to another machine and "import tablespace" 
on it?


Any input would be appreciated. thanks!

Brandon




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

Re: Byte Swapping

[Jeremy Cole]

Hi David,


This might be an unusual question because mysql always stores in little
endian for all platforms (if I'm not mistaken).  However, I am receiving
data from several different platforms (custom boards with various
processors) in raw form.  I will have stored as a configuration option
what endianess it is.

So what I need to do is write a procdure, or use a mysql function (if
there is one available) to byte swap the data (for integer types).  I'm
really just hoping someone has done this, or it is implemented as a mysql
function and I just wasn't able to find it.


This seems very strange that you're storing it in raw form.  Are you 
sure that you actually are?


Anyway, swapping between big/little endian is not difficult in C.

The code is all here:

  http://jcole.us/svn/libmygis/mygis.h

e.g.:

#define SWAP(x, y)   { (x) ^= (y) ^= (x) ^= (y); }

#define MYGIS_SWAP4(pos)   { \
  SWAP(((byte *)(pos))[0], ((byte *)(pos))[3]); \
  SWAP(((byte *)(pos))[1], ((byte *)(pos))[2]); \
}

That's enough to swap 32-bits (4-bytes) of whatever is stored at any 
pointer address.


I would encourage that you:

a. make sure you really are storing the data raw -- it seems very odd
b. stop storing the data raw... do the swap before storing

Can you give more details about what you're doing?

Regards,

Jeremy

--
Jeremy Cole
MySQL Geek, Yahoo! Inc.
Desk: 408 349 5104

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

Conditionals in group by

[Hunter Peress]

SELECT sum(if(eventtype='click',1,0)) as
clicks,sum(if(eventtype='view',1,0)) as view from xy group by somethingelse

note the conditional inside the sum. wheres a good source of information on
complex statistical queries like this

Migration toolkit

[Ed Reed]

I'm trying to use the Migration Toolkit to migrate a 4.1 system to a 5.0 
system. It doesn't appear that it can successfully migrate my usernames and 
privileges. Is it supposed to be able to and is there anything special I need 
to do to make it happen?
 
- Thanks

fulltext searches

[Octavian Rasnita]

Hi,

I have tried:

select title from table where match(title, body) against('IT' in boolean
mode);

The result was 0 records.

I have checked the min word lenght which is allowed with:

mysql> show variables like '%ft_min_word_len%';
+-+---+
 Variable_name   |  Value |
+-+---+
 ft_min_word_len |  2 |

So the word "IT" should be found, because if I search using ... like '% IT
%'... there are found some records.

Is "IT" a stop word? If yes, how can I make it be a normal word?

Thank you.

Teddy


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

Byte Swapping

[David Godsey]

This might be an unusual question because mysql always stores in little
endian for all platforms (if I'm not mistaken).  However, I am receiving
data from several different platforms (custom boards with various
processors) in raw form.  I will have stored as a configuration option
what endianess it is.

So what I need to do is write a procdure, or use a mysql function (if
there is one available) to byte swap the data (for integer types).  I'm
really just hoping someone has done this, or it is implemented as a mysql
function and I just wasn't able to find it.

Any help will be appreciated.  Thanks.

Accomplishing the impossible means only that the boss will add it to your
regular duties.

David Godsey


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

Re: Multiple many-to-many SELECT

[Jessica Yazbek]

Thanks for the replies guys. I am self-taught and did do a fair  
amount of reading up on database design before I created my database.  
I'm not sure, however, that I explained myself clearly enough; the  
table schemas I provided were supposed to show that I have a do  
relating table between each many-to-many relationship.


The tables are as follows:

movies
director
producer
director_movies (relates director.id to movies.id)
producer_movies (relates producer.id to movies.id)

I'm hoping for a way to select movies.*, director.* and producer.*  
using only the movies.id field. I have sucessfully done it using  
INNER JOINs if there is a both a related director and producer for  
the movie I am selecting, however, of course the INNER JOIN query  
returns zero results if either a director or producer is missing for  
a movie (which could very well be the case on a good number of the  
movies). When I tried changing INNER JOIN to LEFT JOIN in my query, I  
received a syntax error that I could not figure out.


Right now I have resorted to doing the query using separate SELECT  
statements for each table, which I don't believe is ideal because it  
involves a round-trip between the web server and the database server  
for each related table. I would prefer to do it with one SELECT  
statement if possible. Unless of course, I am mistaken,  and several  
small simple SELECT statements would be faster and less load on the  
server than a single more complicated one. The database schema I  
described above is a simplified version of the real database - I  
actually have 8 related tables (and corresponding relating tables for  
each of those). I assume doing 8 separate SELECTs would be more load  
on the server than one.


Any ideas?

Many Thanks,
Jessica

On Feb 10, 2006, at 6:03 AM, sheeri kritzer wrote:


This is where a theoretical education is great. . .Lots of us are
self-taught, some of us have had theoretical stuff.  I hated
theoretical stuff in college and grad school, because I just wanted to
sit and make something productive.  I'm coming to realize that that
kind of education is invaluable. . .just having gone through
relational algebra and calculus, and seeing how SQL is different from
that stuff

anyway.  I think a small bit of reading on the Entity-Relationship
model (ER model) will help.  A good page is:

http://www.utexas.edu/its/windows/database/datamodeling/dm/ 
erintro.html


Note the link at the bottom of that page to "Data Modeling as part of
Database Design".  That whole site is really good, actually.

-Sheeri

On 2/9/06, Rhino <[EMAIL PROTECTED]> wrote:

Jessica,

It think your first mistake is that you are trying to implement  
many-to-many
relationships directly. Although it is theoretically possible to  
do so, I've
never seen anyone do it myself. I believe the normal approach is  
to break
each many-to-many relationship into two one-to-many relationships  
with an
association table (sometimes called an intersection table) in the  
middle.
Then, you join the tables together as needed, using inner, left or  
right

joins as appropriate for your situation.

I spent several hours detailing most of this in emails on this  
list several
months back in response to a similar question and I _really_ don't  
want to
go through all that typing again :-) Instead, I'm going to refer  
you to the

MySQL archives. If you go to this page -
http://lists.mysql.com/mysql/171636 - you will see the beginning  
of a thread

where I was one of the participants. We were discussing many-to-many
designs. You probably don't need to read the whole thread but  
certainly read

the first few posts in the thread, especially
http://lists.mysql.com/mysql/171645, which is where I first  
describe how to
implement a many-to-many relationship between members (of a video  
club) and

the titles of the movies they rent.

That should get you started. You can read more in that thread to  
hear more
of the pros and cons of the issue but you may find this thread  
wanders

somewhat and is completely relevant to your concerns.

Another thing you could try, to see other discussions of many-to-many
implementations, is to go to http://lists.mysql.com/ and fill in  
the search

box as follows:
Search mailing lists for: intersection table
Within: MySQL General Discussion
Matching: all of the words
since: the beginning

You may find that other people explain the idea more clearly or  
convincingly

than I do ;-)

I have to dash but if you have followup questions, post them in  
the list and

I, or someone else, will likely be able to answer.

--
Rhino


- Original Message -
From: "Jessica Yazbek" <[EMAIL PROTECTED]>
To: 
Sent: Thursday, February 09, 2006 5:28 PM
Subject: Multiple many-to-many SELECT


Hello,

I apologize if this is a common question; I have been working with it
and googling for days, and can't seem to find anyone who has been
trying to do the same thing that I am. Maybe I'm using the wrong
keywords. In an

Re: InnoDB and locking

[Patrick Duda]

At 12:54 PM 2/10/2006, Mark Matthews wrote:

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Patrick Duda wrote:
> I guess I don't understand this locking stuff.  I have a InnoDB table
> that has one thing in it, a counter.  All I want to do is have multiple
> instances of the code read this counter and increment it.  I want to
> make sure that each one is unique.
>
> Here is what I am doing in java:
>
> c.setAutoCommit(false);
> ...
> rs = statement.executeQuery("select request_id from requestid_innodb for
> update");
> ...
> String updateQuery = "update requestid_innodb set request_id=";
>  updateQuery = updateQuery + nextRequestId;
> tempStatement = c.createStatement();
> tempStatement.executeUpdate(updateQuery);
> ...
> c.commit();
> c.setAutoCommit(true);
>
> If I have multiple instances of this code running I end up with
> duplicate keys.  I thought this was suppose to lock the table so that
> would not happen.
>
> What am I not doing right?  What am I not understanding about locking?
>
> Thanks
>
> Patrick
>
>

Patrick,

Are you sure the table is using the InnoDB storage engine? What does the
output of "SHOW CREATE TABLE " for the table in question say?

-Mark



ysql> show create table requestid_innodb;
+--+-+
| Table| Create 
Table 
|

+--+-+
| requestid_innodb | CREATE TABLE `requestid_innodb` (
  `request_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`request_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+--+-+
1 row in set (0.00 sec)



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

Re: (Windows ODBC/MySQL Debug) EMERGENCY HELP NEEDED

[Michael Joyner]

gerald_clark wrote:

Michael Joyner wrote:


(OT) (Windows ODBC/MySQL Debug) EMERGENCY HELP NEEDED
--


MyODBC does not connect to Sybase.


Correct. Merely trying to use it's debug features to "snatch" the dba 
password so that I can get into the db and do a hash replacement
on the software's system user so that I can undo the damage caused by a 
vendor locking us out of the software.


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

Re: InnoDB and locking

[Mark Matthews]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Patrick Duda wrote:
> I guess I don't understand this locking stuff.  I have a InnoDB table
> that has one thing in it, a counter.  All I want to do is have multiple
> instances of the code read this counter and increment it.  I want to
> make sure that each one is unique.
> 
> Here is what I am doing in java:
> 
> c.setAutoCommit(false);
> ...
> rs = statement.executeQuery("select request_id from requestid_innodb for
> update");
> ...
> String updateQuery = "update requestid_innodb set request_id=";
>  updateQuery = updateQuery + nextRequestId;
> tempStatement = c.createStatement();
> tempStatement.executeUpdate(updateQuery);
> ...
> c.commit();
> c.setAutoCommit(true);
> 
> If I have multiple instances of this code running I end up with
> duplicate keys.  I thought this was suppose to lock the table so that
> would not happen.
> 
> What am I not doing right?  What am I not understanding about locking?
> 
> Thanks
> 
> Patrick
> 
> 

Patrick,

Are you sure the table is using the InnoDB storage engine? What does the
output of "SHOW CREATE TABLE " for the table in question say?

-Mark
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.1 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFD7OF/tvXNTca6JD8RAjS0AJwKoXIpZUVY3Z0g/vqcUbyxm6JzBwCeIC7Z
f1Tgh6gQde3w7KtGRCU3H/0=
=j0x0
-END PGP SIGNATURE-

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

Re: (Windows ODBC/MySQL Debug) EMERGENCY HELP NEEDED

[gerald_clark]

Michael Joyner wrote:


(OT) (Windows ODBC/MySQL Debug) EMERGENCY HELP NEEDED
--

The Scenario:
~~
We out locked out of our door lock database.
The database is Sybase Sql Anywhere version 7.0
The software uses a hard coded DBA password that is *not* the word 'sql'

A Maybe Solution:
~~
The software does *not* do a check on it's ODBC DSN to see
what type of driver is being used.
I can successfully have the software use the MyODBC debug dll
which, because it's password is unknown to the MySQL server
fails with an authentication error.
The debug version version of the shipped MyODBC plugin does
not log the password.
I need a copy of the MyODBC debug plugin which *WILL* log the
password.

*** PLEASE HELP US FIX OUR DOOR LOCK SYSTEM! ***

~

Michael Joyner
System Administrator / 904-470-8170
Edward Waters College
1658 Kings Road
Jacksonville, FL 32209


MyODBC does not connect to Sybase.


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

Re: MySQL 5.0.x and Delphi

[Martijn Tonies]

Hi,

First of all: you're sending a message with an enormous quote of someone
elses message, this really is "not done" on an e-mail list. Start your own
topic.

> I'm  having problems upgrading my database from 4.0.x to 5.0.x, specially
> with date fields
> 
> For example with 4.0.x I do: "select startDate from TEvent" and in my
> application it is displayed ok(managed with controls), but the same code,
> same odbc, same control in delphi, etc with mysql 5.0.x it displays: byte
> instance of the date storaged in my table

What datatype is being returned for this column if you try and use it
in Delphi with the ODBC driver?

Did you try the v5 driver already?
http://dev.mysql.com/downloads/connector/odbc/5.0.html

> My versions are:
> Delphi 5
> MyODBC-3.51.11-2-win
> MySQL 5.0.18
> MySQL 4.0.26

Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com

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

Re: InnoDB and locking

[Patrick Duda]

At 10:52 AM 2/10/2006, [EMAIL PROTECTED] wrote:

Patrick Duda <[EMAIL PROTECTED]> wrote on 10/02/2006 16:28:56:

> I guess I don't understand this locking stuff.  I have a InnoDB table
that
> has one thing in it, a counter.  All I want to do is have multiple
> instances of the code read this counter and increment it.  I want to
make
> sure that each one is unique.
>
> Here is what I am doing in java:
>
> c.setAutoCommit(false);
> ...
> rs = statement.executeQuery("select request_id from requestid_innodb for

> update");
> ...
> String updateQuery = "update requestid_innodb set request_id=";
>   updateQuery = updateQuery + nextRequestId;
> tempStatement = c.createStatement();
> tempStatement.executeUpdate(updateQuery);
> ...
> c.commit();
> c.setAutoCommit(true);
>
> If I have multiple instances of this code running I end up with
duplicate
> keys.  I thought this was suppose to lock the table so that would not
happen.
>
> What am I not doing right?  What am I not understanding about locking?

I think this problem is explained in detail at
http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html

Alec




Yes, I know that, I have read that and that is why I am asking what it is 
that I am not doing right.  It talks about a counter specifically:
"2) read the counter first with a lock mode FOR UPDATE, and increment after 
that. The latter approach can be implemented as follows:


SELECT counter_field FROM child_codes FOR UPDATE;
UPDATE child_codes SET counter_field = counter_field + 1;

A SELECT … FOR UPDATE reads the latest available data, setting exclusive 
locks on each row it reads. Thus, it sets the same locks a searched SQL 
UPDATE would set on the rows. "

Isn't that what my Java code is doing?

I start a transaction by turning off autocommit, I then do a select for 
update.  Then I do the update and I commit.  From the way I read this, no 
one else should be able to read the table until I commit.


Yet, that is not what I am seeing.  When I start several instances of the 
program running I get lots and lots of:


Error inserting records into database [Caused by: Duplicate entry '152' for 
key 1]


That is what has me confused.  I thought I was doing things they way the 
manual said to.


Thanks

Patrick



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

MySQL 5.0.x and Delphi

[ISC Edwin Cruz]

Hi all!
I'm  having problems upgrading my database from 4.0.x to 5.0.x, specially
with date fields

For example with 4.0.x I do: "select startDate from TEvent" and in my
application it is displayed ok(managed with controls), but the same code,
same odbc, same control in delphi, etc with mysql 5.0.x it displays: byte
instance of the date storaged in my table

My versions are:
Delphi 5
MyODBC-3.51.11-2-win
MySQL 5.0.18
MySQL 4.0.26


Regards!




-Mensaje original-
De: Peter Brawley [mailto:[EMAIL PROTECTED] 
Enviado el: Viernes, 10 de Febrero de 2006 11:01 a.m.
Para: Patrick Duda
CC: mysql@lists.mysql.com
Asunto: Re: question about locking


Patrick,

 >I thought, that if I turned off autocommit I would enter a transaction. 
 >Then, by using the "select...for update", that I would take and hole a 
lock on the table.

With a transaction-capable table, eg InnoDB, otherwise neither setting 
autocommit off nor adding FOR UPDATE has any effect.

PB

-

Patrick Duda wrote:
> Hi,
>
> I am running MySQL 4.0.1 with j/connector 3.1 and I am having problems
> trying to figure out why I am not getting the results I am expecting.
>
> I have a table that is used for generating primary keys.  It only has
> one item, an int that is incremented each time a key is needed.  This 
> is not my code or my design so using something like auto_incrament is 
> not an option.
>
> The code runs under a container and our desire is to have several
> different containers running at the same time, all accessing the same 
> database.  Each container is independent so the controls need to be on 
> the database side.
>
> The solution also needs to be portable to other databases so I am
> trying to stay with standard JDBC or SQL options.
>
> The code for generating a new key is this:
>
> try {
> c = DatabaseSetup.getDBConnection();
>
> c.setAutoCommit(false);
>
> statement =
> c.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
> ResultSet.CONCUR_UPDATABLE);
>
> rs = statement.executeQuery("select transfer_id from transferid for
> update");
>
> if (!rs.next())  {
> nextTransferId = nextTransferId + 1;
> StringBuffer query = new StringBuffer();
> query.append("insert into transferid(transfer_id)
> values (");
> query.append(nextTransferId);
> query.append(")");
> tempStatement = c.createStatement();
> // Now Update the old value with new value
> tempStatement.executeUpdate(query.toString());
> } else {
> rs.previous();
> while( rs != null && rs.next() ) {
> nextTransferId = rs.getInt(1);
> // Get the transfer Id and increment it instead of 
> using
> // Db Specific sequence
> nextTransferId = nextTransferId + 1;
> // Now Update the old value with new value
> tempStatement = c.createStatement();
> tempStatement.executeUpdate("update transferid set "+
> "transfer_id= "+ nextTransferId);
> }
> }
> } catch (SQLException e) {
>
> if( c != null )
> {
> try
> {
> c.rollback();
> c.setAutoCommit(true);
> }
> catch( SQLException ex )
> {
> }
> }
> throw new DBException(i18n.getMessage("dbInsertErr"),
> e);
> } finally {
> try {
> c.commit();
> c.setAutoCommit(true);
> if (statement != null) {
> statement.close();
> }
> if (tempStatement != null) {
> tempStatement.close();
> }
> if (rs != null) {
> rs.close();
> }
> if (c != null) {
> DatabaseSetup.returnDBConnection(c);
> }
> } catch (SQLException sql) {
> logger.warn(i18n.getMessage("dbStatementErr"), sql);
> }
> }
> return nextTransferId;
> }
>
> I thought, that if I turned off autocommit I would enter a
> transaction.  Then, by using the "select...for update", that I would 
> take and hole a lock on the table.  That no other transaction would be 
> able to read the table until I released the lock.  However, this is 
> not what I am seeing when I run some tests.  I start up a number of 
> containers and then fire off a bunch of jobs to each.  Each of these 
> jobs will hit the above code.  The problem is that every so often I 
> see the following error message.
>
>  Error inserting records into database [Caused by: Duplicate entry
> '131' for key 1]
>
> What am I doing wrong?
>
> How am I suppose to be doing this via JDBC?  I know it should work...
>
> Thanks
>
>


-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Vers

(Windows ODBC/MySQL Debug) EMERGENCY HELP NEEDED

[Michael Joyner]

(OT) (Windows ODBC/MySQL Debug) EMERGENCY HELP NEEDED
--

The Scenario:
~~
We out locked out of our door lock database.
The database is Sybase Sql Anywhere version 7.0
The software uses a hard coded DBA password that is *not* the word 'sql'

A Maybe Solution:
~~
The software does *not* do a check on it's ODBC DSN to see
what type of driver is being used.
I can successfully have the software use the MyODBC debug dll
which, because it's password is unknown to the MySQL server
fails with an authentication error.
The debug version version of the shipped MyODBC plugin does
not log the password.
I need a copy of the MyODBC debug plugin which *WILL* log the
password.

*** PLEASE HELP US FIX OUR DOOR LOCK SYSTEM! ***

~

Michael Joyner
System Administrator / 904-470-8170
Edward Waters College
1658 Kings Road
Jacksonville, FL 32209






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

Re: question about locking

[Peter Brawley]

Patrick,

>I thought, that if I turned off autocommit I would enter a transaction. 
>Then, by using the "select...for update", that I would take and hole a 
lock on the table.


With a transaction-capable table, eg InnoDB, otherwise neither setting 
autocommit off nor adding FOR UPDATE has any effect.


PB

-

Patrick Duda wrote:

Hi,

I am running MySQL 4.0.1 with j/connector 3.1 and I am having problems 
trying to figure out why I am not getting the results I am expecting.


I have a table that is used for generating primary keys.  It only has 
one item, an int that is incremented each time a key is needed.  This 
is not my code or my design so using something like auto_incrament is 
not an option.


The code runs under a container and our desire is to have several 
different containers running at the same time, all accessing the same 
database.  Each container is independent so the controls need to be on 
the database side.


The solution also needs to be portable to other databases so I am 
trying to stay with standard JDBC or SQL options.


The code for generating a new key is this:

try {
c = DatabaseSetup.getDBConnection();

c.setAutoCommit(false);

statement = 
c.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,

ResultSet.CONCUR_UPDATABLE);

rs = statement.executeQuery("select transfer_id from transferid for 
update");


if (!rs.next())  {
nextTransferId = nextTransferId + 1;
StringBuffer query = new StringBuffer();
query.append("insert into transferid(transfer_id) 
values (");

query.append(nextTransferId);
query.append(")");
tempStatement = c.createStatement();
// Now Update the old value with new value
tempStatement.executeUpdate(query.toString());
} else {
rs.previous();
while( rs != null && rs.next() ) {
nextTransferId = rs.getInt(1);
// Get the transfer Id and increment it instead of 
using

// Db Specific sequence
nextTransferId = nextTransferId + 1;
// Now Update the old value with new value
tempStatement = c.createStatement();
tempStatement.executeUpdate("update transferid set "+
"transfer_id= "+ nextTransferId);
}
}
} catch (SQLException e) {

if( c != null )
{
try
{
c.rollback();
c.setAutoCommit(true);
}
catch( SQLException ex )
{
}
}
throw new DBException(i18n.getMessage("dbInsertErr"),
e);
} finally {
try {
c.commit();
c.setAutoCommit(true);
if (statement != null) {
statement.close();
}
if (tempStatement != null) {
tempStatement.close();
}
if (rs != null) {
rs.close();
}
if (c != null) {
DatabaseSetup.returnDBConnection(c);
}
} catch (SQLException sql) {
logger.warn(i18n.getMessage("dbStatementErr"), sql);
}
}
return nextTransferId;
}

I thought, that if I turned off autocommit I would enter a 
transaction.  Then, by using the "select...for update", that I would 
take and hole a lock on the table.  That no other transaction would be 
able to read the table until I released the lock.  However, this is 
not what I am seeing when I run some tests.  I start up a number of 
containers and then fire off a bunch of jobs to each.  Each of these 
jobs will hit the above code.  The problem is that every so often I 
see the following error message.


 Error inserting records into database [Caused by: Duplicate entry 
'131' for key 1]


What am I doing wrong?

How am I suppose to be doing this via JDBC?  I know it should work...

Thanks





--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.15.5/256 - Release Date: 2/10/2006


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

Re: InnoDB and locking

[Alec . Cawley]

Patrick Duda <[EMAIL PROTECTED]> wrote on 10/02/2006 16:28:56:

> I guess I don't understand this locking stuff.  I have a InnoDB table 
that 
> has one thing in it, a counter.  All I want to do is have multiple 
> instances of the code read this counter and increment it.  I want to 
make 
> sure that each one is unique.
> 
> Here is what I am doing in java:
> 
> c.setAutoCommit(false);
> ...
> rs = statement.executeQuery("select request_id from requestid_innodb for 

> update");
> ...
> String updateQuery = "update requestid_innodb set request_id=";
>   updateQuery = updateQuery + nextRequestId;
> tempStatement = c.createStatement();
> tempStatement.executeUpdate(updateQuery);
> ...
> c.commit();
> c.setAutoCommit(true);
> 
> If I have multiple instances of this code running I end up with 
duplicate 
> keys.  I thought this was suppose to lock the table so that would not 
happen.
> 
> What am I not doing right?  What am I not understanding about locking?

I think this problem is explained in detail at
http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html

Alec 



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

InnoDB and locking

[Patrick Duda]

I guess I don't understand this locking stuff.  I have a InnoDB table that 
has one thing in it, a counter.  All I want to do is have multiple 
instances of the code read this counter and increment it.  I want to make 
sure that each one is unique.


Here is what I am doing in java:

c.setAutoCommit(false);
...
rs = statement.executeQuery("select request_id from requestid_innodb for 
update");

...
String updateQuery = "update requestid_innodb set request_id=";
 updateQuery = updateQuery + nextRequestId;
tempStatement = c.createStatement();
tempStatement.executeUpdate(updateQuery);
...
c.commit();
c.setAutoCommit(true);

If I have multiple instances of this code running I end up with duplicate 
keys.  I thought this was suppose to lock the table so that would not happen.


What am I not doing right?  What am I not understanding about locking?

Thanks

Patrick


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

Re: MySQL PDA Synch

[Imran Chaudhry]

> Thoughts, comments and advice very appreciated

Adrian, just in case your question is out of scope for this crowd...
MySQL have a number of forums and one is 'Embedded MySQL'
http://forums.mysql.com/list.php?58

Hope that helps,

Imran Chaudhry
--
http://www.ImranChaudhry.info
MySQL Database Management & Design Services

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

[ANNOUNCE] MySQL Connector/PHP for MySQL 4.1.18 released

[Lenz Grimmer]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Dear MySQL users,

MySQL AB announces an updated release of the "MySQL Connector/PHP for
MySQL and PHP", the Windows downloads for connecting MySQL to PHP, as
MySQL AB has released MySQL 4.1.18 [1].

The MySQL Connector/PHP consists of the mysql/mysqli PHP extensions
built against the latest version of the MySQL client libraries for
Microsoft Windows.

The advantages on top of the mysql/mysqli PHP extensions currently
available from php.net are that they contain the latest bug fixes to the
MySQL Client Library, and make all the features in the MySQL server
versions 4.1.18 and 5.0.18 available.

They are now available in binary format from the MySQL Connector/PHP
download page at http://dev.mysql.com/downloads/connector/php/ and
mirror sites (note that not all the mirror sites may be up to date at
this point in time - if you cannot find this version on a mirror, please
try again later or choose another mirror site).

This binary release contains the mysql/mysqli extensions as well as the
MySQL PDO driver for PHP 5.1.2, with support for MySQL server versions
4.1.18 and 5.0.18. Note that only the mysqli extension for PHP 5.0, but
not the older mysql extension, supports the full functionality of MySQL
versions greater than 4.1.x, such as Stored Procedures, Triggers, Views,
and much more.

For questions not answered within the Download FAQ, please visit the
MySQL PHP forums at http://forums.mysql.com/list.php?52.

Bye,
LenZ

[1] Release announcement for MySQL 4.1.18: http://lists.mysql.com/announce/344

- -- 
 Lenz Grimmer <[EMAIL PROTECTED]>
 Community Relations Manager, EMEA
 MySQL GmbH, http://www.mysql.de/, Hamburg, Germany
 MySQL Users Conference 2006 (Santa Clara CA, 24-27 April) - 
http://www.mysqluc.com/
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.2 (GNU/Linux)
Comment: For info see http://quantumlab.net/pine_privacy_guard/

iD8DBQFD7LjxSVDhKrJykfIRAptgAJ98fkaWO7YwXsVNTdTglLgk71xlDQCfaz7e
qKHajYjXab0RUzzIlkQypMs=
=GQEw
-END PGP SIGNATURE-

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

Re: Innodb table locked on delete

[Ady Wicaksono]

> Innodb is not row-level locking -- it's memory-page-level-locking.  A
> memory page is usually small, so it's almost like row-level locking,
> but not quite.  Perhaps you're running up against that?
>
> What does the query log say which queries were running?  How do you
> know it's the delete that's taking the lock, and not an update (the
> error message said an update or a delete)?

Please see InnoDB status on this URL

http://forums.mysql.com/read.php?22,68977,68977#msg-68977

You'll see
--TRANSACTION 0 476648265, ACTIVE 530 sec, process no 13965, OS thread id
3152999360 updating or deleting, thread declared inside InnoDB 293
mysql tables in use 1, locked 1

> Locked 1 :)


11090 lock struct(s), heap size 634176, undo log entries 930711
MySQL thread id 16831, query id 11504020 localhost 127.0.0.1 root updating
delete from t_outgoing_sms_3t where month(out_sms_time)=1


>
> -Sheeri
>
> On 2/10/06, Ady Wicaksono <[EMAIL PROTECTED]> wrote:
>> So where's the row locking?
>>
>> I configure my database with Innodb + READ COMMITED, by this
>> configuration
>> by using autocommit=1, delete should be done on data commited to disk.
>> Other thread should be able to insert/update.
>>
>> CMIIW
>>
>>
>> > Tables are locked on delete because, like an update, they are changing
>> > data.
>> >
>> > Imagine issuing a REPLACE statement after a DELETE statement.  If the
>> > DELETE locks the table, then the REPLACE happens AFTER the DELETE, as
>> > you wanted.  If the DELETE does not lock the table, then it's possible
>> > the REPLACE will happen before the DELETE, and the DELETE will delete
>> > the row you just replaced.
>> >
>> > -Sheeri
>> >
>> > On 2/9/06, Ady Wicaksono <[EMAIL PROTECTED]> wrote:
>> >> Why table locked on delete?
>> >>
>> >
>> >
>>
>>
>
>


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

Re: Migration

[Gabriel PREDA]

Not the whole... maybe it's humongous... some lines above  line 20 and some
beneath...

--
Gabriel PREDA
Senior Web Developer


On 2/10/06, sheeri kritzer <[EMAIL PROTECTED]> wrote:
>
> James,
>
> You're going to need to show us the contents of olddbname.sql.
>
> -Sheeri
>
> On 2/10/06, James Dey <[EMAIL PROTECTED]> wrote:
> > > I am migrating a database from mySQL 4.0 to 4.1.16 and have the error
> > > ERROR 1064 at line 21: You have an error in your SQL syntax.  Check
> the
> > > manual that corresponds to your MySQL server version for the right
> syntax
> > to
> > > use near 'ALTER TABLE `jos_banner` */' at line 20
> > >
>
>

Re: Innodb table locked on delete

[Greg Whalin]

Innodb is indeed row level locking.  You are likely thinking of BDB
which uses memory page level locking.

gw

sheeri kritzer wrote:
> Innodb is not row-level locking -- it's memory-page-level-locking.  A
> memory page is usually small, so it's almost like row-level locking,
> but not quite.  Perhaps you're running up against that?
> 
> What does the query log say which queries were running?  How do you
> know it's the delete that's taking the lock, and not an update (the
> error message said an update or a delete)?
> 
> -Sheeri
> 
> On 2/10/06, Ady Wicaksono <[EMAIL PROTECTED]> wrote:
>> So where's the row locking?
>>
>> I configure my database with Innodb + READ COMMITED, by this configuration
>> by using autocommit=1, delete should be done on data commited to disk.
>> Other thread should be able to insert/update.
>>
>> CMIIW
>>
>>
>>> Tables are locked on delete because, like an update, they are changing
>>> data.
>>>
>>> Imagine issuing a REPLACE statement after a DELETE statement.  If the
>>> DELETE locks the table, then the REPLACE happens AFTER the DELETE, as
>>> you wanted.  If the DELETE does not lock the table, then it's possible
>>> the REPLACE will happen before the DELETE, and the DELETE will delete
>>> the row you just replaced.
>>>
>>> -Sheeri
>>>
>>> On 2/9/06, Ady Wicaksono <[EMAIL PROTECTED]> wrote:
 Why table locked on delete?

>>>
>>
> 


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

Re: Migration

[sheeri kritzer]

James,

You're going to need to show us the contents of olddbname.sql.

-Sheeri

On 2/10/06, James Dey <[EMAIL PROTECTED]> wrote:
> Sure, I just use "mysql db name < olddbname.sql
>
> Regards,
>
> James Dey
>
> tel +27 11 704-1945
> cell+27 82 785-5102
> fax +27 11 388-8907
> mail[EMAIL PROTECTED]
>
>
> -Original Message-
> From: Martijn Tonies [mailto:[EMAIL PROTECTED]
> Sent: 10 February 2006 11:58 AM
> To: mysql@lists.mysql.com
> Subject: Re: Migration
>
>
> > I am migrating a database from mySQL 4.0 to 4.1.16 and have the error
> >
> >
> >
> > ERROR 1064 at line 21: You have an error in your SQL syntax.  Check the
> > manual that corresponds to your MySQL server version for the right syntax
> to
> > use near 'ALTER TABLE `jos_banner` */' at line 20
> >
> >
> >
> > Could anyone perhaps let me know how I can remedy this,
>
> Care to show the full statement?
>
> Martijn Tonies
> Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
> Server
> Upscene Productions
> http://www.upscene.com
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.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]

Re: Innodb table locked on delete

[sheeri kritzer]

Hrm, I could be wrongthe MySQL site says innodb uses row-level
locking, and BDB uses page-level locking.  But I remember reading that
Innodb uses memory page level locking.  Am I wrong?

At any rate, even if InnoDB is page-level locking, you still get the
benefits on a page-level, which is very close to row-level locking. 
And no, they're not misrepresented, it's just easier for most MySQL
users to understand "row-level" rather than "memory page level" when
they're "close enough".  Many MySQL users are self-taught.

And of course, I could be wrong, it could be true row-level locking. 
You haven't posted your version of MySQL, nor your "SHOW CREATE TABLE"
on the table in question.  Those would be helpful.

-Sheeri

On 2/10/06, George Law <[EMAIL PROTECTED]> wrote:
> I have also seen the table locking on deletes and even on large selects
> with INNODB.  I had converted to INNODB strictly for the "row level
> locking" that is the biggest selling point of using INNODB.
>
> So all the avantages of INNODB that are advertised (ie - row level
> locking) are mis-represented?
>
> Right from the mysql website:
>
>
> " 14.2.1. InnoDB Overview
>
> InnoDB provides MySQL with a transaction-safe (ACID compliant) storage
> engine that has commit, rollback, and crash recovery capabilities.
> InnoDB does locking on the row level and also provides an Oracle-style
> consistent non-locking read in SELECT statements. These features
> increase multi-user concurrency and performance. There is no need for
> lock escalation in InnoDB because row-level locks fit in very little
> space."
>
>
>
> > -Original Message-
> > From: sheeri kritzer [mailto:[EMAIL PROTECTED]
> > Sent: Friday, February 10, 2006 9:52 AM
> > To: Ady Wicaksono
> > Cc: mysql@lists.mysql.com
> > Subject: Re: Innodb table locked on delete
> >
> > Innodb is not row-level locking -- it's
> > memory-page-level-locking.  A memory page is usually small,
> > so it's almost like row-level locking, but not quite.
> > Perhaps you're running up against that?
> >
> > What does the query log say which queries were running?  How
> > do you know it's the delete that's taking the lock, and not
> > an update (the error message said an update or a delete)?
> >
> > -Sheeri
> >
> > On 2/10/06, Ady Wicaksono <[EMAIL PROTECTED]> wrote:
> > > So where's the row locking?
> > >
> > > I configure my database with Innodb + READ COMMITED, by this
> > > configuration by using autocommit=1, delete should be done
> > on data commited to disk.
> > > Other thread should be able to insert/update.
> > >
> > > CMIIW
> > >
> > >
> > > > Tables are locked on delete because, like an update, they are
> > > > changing data.
> > > >
> > > > Imagine issuing a REPLACE statement after a DELETE statement.  If
> > > > the DELETE locks the table, then the REPLACE happens AFTER the
> > > > DELETE, as you wanted.  If the DELETE does not lock the
> > table, then
> > > > it's possible the REPLACE will happen before the DELETE, and the
> > > > DELETE will delete the row you just replaced.
> > > >
> > > > -Sheeri
> > > >
> > > > On 2/9/06, Ady Wicaksono
> > <[EMAIL PROTECTED]> wrote:
> > > >> Why table locked on delete?
> > > >>
> > > >
> > > >
> > >
> > >
> >
> > --
> > 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: Problems Starting MySQL

[Rob Lacey]

Running as root.

RobL

On Friday 10 February 2006 14:55, sheeri kritzer wrote:
> Are you running the startup script as root, or is it setuid?
>
> -Sheeri
>
> On 2/10/06, Rob Lacey <[EMAIL PROTECTED]> wrote:
> > Wondering if anyone can shed some light on a problem I am experiencing.
> >
> > I am running MySQL 4.1 on a Red Hat Enterprise Linux ES release 4 (Nahant
> > Update 2) machine.
> >
> > I have been trying to get the default /etc/init.d/mysqld script to
> > restart the server but I keep running into the same problems. The MySQL
> > server itself is running at the moment and has a number of live databases
> > on it.
> >
> > 060209 18:40:43  mysqld started
> > 060209 18:40:43 [Warning] Can't create test
> > file /var/lib/mysql/thirdeye.lower-test
> > /usr/libexec/mysqld: Can't change dir to '/var/lib/mysql/' (Errcode: 13)
> > 060209 18:40:43 [ERROR] Aborting
> >
> > 060209 18:40:43 [Note] /usr/libexec/mysqld: Shutdown complete
> >
> > 060209 18:40:43  mysqld ended
> >
> > I can't see where the thirdeye.lower-test file creation is coming into
> > this at all. Since as far as can see the startup script doesn't seem to
> > call any script to create test databases. Is this part of mysqld_safe?
> >
> > I can also see that /var/lib/mysql is already owned by mysql.mysql with
> > 755 permissions. Indeed the RedHat startup script makes this so.
> > Therefore the permissions error seems extremely odd as they seem fine to
> > me.
> >
> > I can start the server using the following directly on the command line,
> > which is what the startup script ultimately runs anyway.
> >
> > /usr/bin/mysqld_safe  --defaults-file=/etc/my.cnf
> > --pid-file= /var/run/mysqld/mysqld.pid > /dev/null 2>&1 &
> >
> > Can anyone give me something further to go on?
> >
> > Many thanks.
> >
> > Rob
> >
> > --
> > 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: Innodb table locked on delete

[George Law]

I have also seen the table locking on deletes and even on large selects
with INNODB.  I had converted to INNODB strictly for the "row level
locking" that is the biggest selling point of using INNODB.

So all the avantages of INNODB that are advertised (ie - row level
locking) are mis-represented?

Right from the mysql website:


" 14.2.1. InnoDB Overview

InnoDB provides MySQL with a transaction-safe (ACID compliant) storage
engine that has commit, rollback, and crash recovery capabilities.
InnoDB does locking on the row level and also provides an Oracle-style
consistent non-locking read in SELECT statements. These features
increase multi-user concurrency and performance. There is no need for
lock escalation in InnoDB because row-level locks fit in very little
space."



> -Original Message-
> From: sheeri kritzer [mailto:[EMAIL PROTECTED]
> Sent: Friday, February 10, 2006 9:52 AM
> To: Ady Wicaksono
> Cc: mysql@lists.mysql.com
> Subject: Re: Innodb table locked on delete
>
> Innodb is not row-level locking -- it's
> memory-page-level-locking.  A memory page is usually small,
> so it's almost like row-level locking, but not quite. 
> Perhaps you're running up against that?
>
> What does the query log say which queries were running?  How
> do you know it's the delete that's taking the lock, and not
> an update (the error message said an update or a delete)?
>
> -Sheeri
>
> On 2/10/06, Ady Wicaksono <[EMAIL PROTECTED]> wrote:
> > So where's the row locking?
> >
> > I configure my database with Innodb + READ COMMITED, by this
> > configuration by using autocommit=1, delete should be done
> on data commited to disk.
> > Other thread should be able to insert/update.
> >
> > CMIIW
> >
> >
> > > Tables are locked on delete because, like an update, they are
> > > changing data.
> > >
> > > Imagine issuing a REPLACE statement after a DELETE statement.  If
> > > the DELETE locks the table, then the REPLACE happens AFTER the
> > > DELETE, as you wanted.  If the DELETE does not lock the
> table, then
> > > it's possible the REPLACE will happen before the DELETE, and the
> > > DELETE will delete the row you just replaced.
> > >
> > > -Sheeri
> > >
> > > On 2/9/06, Ady Wicaksono
> <[EMAIL PROTECTED]> wrote:
> > >> Why table locked on delete?
> > >>
> > >
> > >
> >
> >
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:   
> http://lists.mysql.com/[EMAIL PROTECTED]
>
> 

Re: Problems Starting MySQL

[sheeri kritzer]

Are you running the startup script as root, or is it setuid?

-Sheeri

On 2/10/06, Rob Lacey <[EMAIL PROTECTED]> wrote:
> Wondering if anyone can shed some light on a problem I am experiencing.
>
> I am running MySQL 4.1 on a Red Hat Enterprise Linux ES release 4 (Nahant
> Update 2) machine.
>
> I have been trying to get the default /etc/init.d/mysqld script to restart the
> server but I keep running into the same problems. The MySQL server itself is
> running at the moment and has a number of live databases on it.
>
> 060209 18:40:43  mysqld started
> 060209 18:40:43 [Warning] Can't create test
> file /var/lib/mysql/thirdeye.lower-test
> /usr/libexec/mysqld: Can't change dir to '/var/lib/mysql/' (Errcode: 13)
> 060209 18:40:43 [ERROR] Aborting
>
> 060209 18:40:43 [Note] /usr/libexec/mysqld: Shutdown complete
>
> 060209 18:40:43  mysqld ended
>
> I can't see where the thirdeye.lower-test file creation is coming into this at
> all. Since as far as can see the startup script doesn't seem to call any
> script to create test databases. Is this part of mysqld_safe?
>
> I can also see that /var/lib/mysql is already owned by mysql.mysql with 755
> permissions. Indeed the RedHat startup script makes this so. Therefore the
> permissions error seems extremely odd as they seem fine to me.
>
> I can start the server using the following directly on the command line, which
> is what the startup script ultimately runs anyway.
>
> /usr/bin/mysqld_safe  --defaults-file=/etc/my.cnf
> --pid-file= /var/run/mysqld/mysqld.pid > /dev/null 2>&1 &
>
> Can anyone give me something further to go on?
>
> Many thanks.
>
> Rob
>
> --
> 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: Innodb table locked on delete

[sheeri kritzer]

Innodb is not row-level locking -- it's memory-page-level-locking.  A
memory page is usually small, so it's almost like row-level locking,
but not quite.  Perhaps you're running up against that?

What does the query log say which queries were running?  How do you
know it's the delete that's taking the lock, and not an update (the
error message said an update or a delete)?

-Sheeri

On 2/10/06, Ady Wicaksono <[EMAIL PROTECTED]> wrote:
> So where's the row locking?
>
> I configure my database with Innodb + READ COMMITED, by this configuration
> by using autocommit=1, delete should be done on data commited to disk.
> Other thread should be able to insert/update.
>
> CMIIW
>
>
> > Tables are locked on delete because, like an update, they are changing
> > data.
> >
> > Imagine issuing a REPLACE statement after a DELETE statement.  If the
> > DELETE locks the table, then the REPLACE happens AFTER the DELETE, as
> > you wanted.  If the DELETE does not lock the table, then it's possible
> > the REPLACE will happen before the DELETE, and the DELETE will delete
> > the row you just replaced.
> >
> > -Sheeri
> >
> > On 2/9/06, Ady Wicaksono <[EMAIL PROTECTED]> wrote:
> >> Why table locked on delete?
> >>
> >
> >
>
>

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

MySQL PDA Synch

[Adrian Bruce]

hi

I have an online registration system made with PHP and MySQL that i 
would like to make available for use with PDA's.  We have a wireless 
network that covers only certain parts of our area and when the PDA's 
can connect there will be no problem as I have PDA style sheets set up.  
When the wireless network is not available i would like to run a watered 
down version of the application on the PDA and then sync up to the main 
system during the next connection.


Is there a way of using MySQL on a PDA or embedding it someway into an 
application that will work on a PDA?  I can see a huge potential if it 
is possible


Thoughts, comments and advice very appreciated


Ade

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

Re: Connection problem after long idle periods

[Mark Matthews]

Michael Lai wrote:

David Logan wrote:


Michael Lai wrote:

I am currently using MySQL 5.0.15 with mysql-connector-java-3.1.11.  
I can access the database from my JSPs with no problem except for one 
small issue.  After a long delay (usually overnight), when someone 
first tries to access the database, I would get the following error:


Communications link failure due to underlying exception: ** BEGIN 
NESTED EXCEPTION ** java.net.SocketException MESSAGE: Broken pipe 
STACKTRACE: java.net.SocketException: Broken pipe at 
java.net.SocketOutputStream.socketWrite0(Native Method) at 
java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:92) 
at java.net.SocketOutputStream.write(SocketOutputStream.java:136) at 
java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:65) 
at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:123) 
at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2690) at 
com.mysql.jdbc.MysqlIO.send(MysqlIO.java:2619) at 
com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1552) at 
com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666) at 
com.mysql.jdbc.Connection.execSQL(Connection.java:2978) at 
com.mysql.jdbc.Connection.execSQL(Connection.java:2902) at 
com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:933) 
at 
com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1027) 
at 
org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92) 
at 
org.apache.jsp.processLogin_jsp._jspService(org.apache.jsp.processLogin_jsp:81) 
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97) 
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802) at 
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:322) 
at 
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:291) 
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241) 
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802) at 
org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252) 
at 
org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173) 
at 
org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:213) 
at 
org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178) 
at 
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126) 
at 
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105) 
at 
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107) 
at 
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148) 
at 
org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:856) 
at 
org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:744) 
at 
org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:527) 
at 
org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80) 
at 
org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684) 
at java.lang.Thread.run(Thread.java:595) ** END NESTED EXCEPTION ** 
Last packet sent to the server was 0 ms ago.


But afterwards, on the second and subsequent attempts, the database 
runs fine.  It is only the first try after a long idle period that I 
get this error message.  I am not sure how to fix this.  I am 
guessing that the database connection is down after a long idle 
period and have to recreate a connection first after the first try.



Hi Michael,

Try looking at
http://dev.mysql.com/doc/refman/4.1/en/server-system-variables.html more
specifically the connect-timeout variable. You should use SHOW VARIABLES
LIKE "%connect%"; and check the value.

It maybe this and you can stretch it out further if you need to.

Regards



I tried lengthening the connection timeout but it doesn't seem to be 
working.  Here is my ROOT.xml configuration:


reloadable="true" >

   


Is there any way to keep a connection alive indefinitely?  I thought the 
autoReconnect would do the trick.




Michael,

There really is no way to keep a connection alive indefitely, and I'm 
not aware of compelling reasons to do so for many reasons. First, it 
consumes resources that aren't needed if the connection is sitting idle, 
and second, it increases the risk that network issues, or server 
restarts will cause your application to crash.


It takes very little time (on the order of a few milliseconds) to create 
a JDBC connection to MySQL, so you should set your connection pool to 
only let connections stay idle for a few minutes.


Most of the high-volume applications I've seen that use Java with MySQL 
don't let connections stay idle more than 10 minutes or so.


-Mark

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

Re: Innodb table locked on delete

[Ady Wicaksono]

So where's the row locking?

I configure my database with Innodb + READ COMMITED, by this configuration
by using autocommit=1, delete should be done on data commited to disk.
Other thread should be able to insert/update.

CMIIW


> Tables are locked on delete because, like an update, they are changing
> data.
>
> Imagine issuing a REPLACE statement after a DELETE statement.  If the
> DELETE locks the table, then the REPLACE happens AFTER the DELETE, as
> you wanted.  If the DELETE does not lock the table, then it's possible
> the REPLACE will happen before the DELETE, and the DELETE will delete
> the row you just replaced.
>
> -Sheeri
>
> On 2/9/06, Ady Wicaksono <[EMAIL PROTECTED]> wrote:
>> Why table locked on delete?
>>
>
>


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

Re: I need Query Help

[Gabriel PREDA]

UPDATE `table1` SET `gender` = IF('f'=`gender`, 'm', 'f');
If you have NULL columns you might want to make another sublevel in IF to
leave it NULL !

I believe this should do it... you might also take into consideraion
removing the possibility of a NULL in the `gender` column... because it
allows the `gender` not to be specified... to be null !

--
Gabriel PREDA
Senior Web Developer

On 2/10/06, Veerabhadrarao Narra <[EMAIL PROTECTED]>
wrote:
>
>
> I have a table named table1 structure is
>
> ++---+--+-+-+---+
> | Field  | Type  | Null | Key | Default | Extra |
> ++---+--+-+-+---+
> | name   | varchar(50)   | NO   | PRI | |   |
> | gender | enum('f','m') | YES  | | NULL|   |
> ++---+--+-+-+---+
>
> And Values like
>
> +--++
> | name | gender |
> +--++
> | 1| m  |
> | 2| m  |
> | 3| m  |
> | 4| m  |
> | 5| m  |
> | 6| m  |
> | 7| m  |
> | 8| m  |
> | 9| m  |
> | 91   | f  |
> | 92   | f  |
> | 93   | f  |
> | 94   | f  |
> | 95   | f  |
> | 96   | f  |
> | 97   | f  |
> | 98   | f  |
> | 99   | f  |
> +--++
>
> Now i want to change the values in gender column
> from 'm' to 'f' as well as 'f' to 'm' in single UPDATE statement. How can
> we write this. And i have check constraint it accept only 'f' or 'm'.
> (Means name 1 to 9 gender have to change 'f' and 91 to 99 'm')
>

Re: Innodb table locked on delete

[sheeri kritzer]

Tables are locked on delete because, like an update, they are changing data.

Imagine issuing a REPLACE statement after a DELETE statement.  If the
DELETE locks the table, then the REPLACE happens AFTER the DELETE, as
you wanted.  If the DELETE does not lock the table, then it's possible
the REPLACE will happen before the DELETE, and the DELETE will delete
the row you just replaced.

-Sheeri

On 2/9/06, Ady Wicaksono <[EMAIL PROTECTED]> wrote:
> Why table locked on delete?
>

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

Re: Multiple many-to-many SELECT

[sheeri kritzer]

This is where a theoretical education is great. . .Lots of us are
self-taught, some of us have had theoretical stuff.  I hated
theoretical stuff in college and grad school, because I just wanted to
sit and make something productive.  I'm coming to realize that that
kind of education is invaluable. . .just having gone through
relational algebra and calculus, and seeing how SQL is different from
that stuff

anyway.  I think a small bit of reading on the Entity-Relationship
model (ER model) will help.  A good page is:

http://www.utexas.edu/its/windows/database/datamodeling/dm/erintro.html

Note the link at the bottom of that page to "Data Modeling as part of
Database Design".  That whole site is really good, actually.

-Sheeri

On 2/9/06, Rhino <[EMAIL PROTECTED]> wrote:
> Jessica,
>
> It think your first mistake is that you are trying to implement many-to-many
> relationships directly. Although it is theoretically possible to do so, I've
> never seen anyone do it myself. I believe the normal approach is to break
> each many-to-many relationship into two one-to-many relationships with an
> association table (sometimes called an intersection table) in the middle.
> Then, you join the tables together as needed, using inner, left or right
> joins as appropriate for your situation.
>
> I spent several hours detailing most of this in emails on this list several
> months back in response to a similar question and I _really_ don't want to
> go through all that typing again :-) Instead, I'm going to refer you to the
> MySQL archives. If you go to this page -
> http://lists.mysql.com/mysql/171636 - you will see the beginning of a thread
> where I was one of the participants. We were discussing many-to-many
> designs. You probably don't need to read the whole thread but certainly read
> the first few posts in the thread, especially
> http://lists.mysql.com/mysql/171645, which is where I first describe how to
> implement a many-to-many relationship between members (of a video club) and
> the titles of the movies they rent.
>
> That should get you started. You can read more in that thread to hear more
> of the pros and cons of the issue but you may find this thread wanders
> somewhat and is completely relevant to your concerns.
>
> Another thing you could try, to see other discussions of many-to-many
> implementations, is to go to http://lists.mysql.com/ and fill in the search
> box as follows:
> Search mailing lists for: intersection table
> Within: MySQL General Discussion
> Matching: all of the words
> since: the beginning
>
> You may find that other people explain the idea more clearly or convincingly
> than I do ;-)
>
> I have to dash but if you have followup questions, post them in the list and
> I, or someone else, will likely be able to answer.
>
> --
> Rhino
>
>
> - Original Message -
> From: "Jessica Yazbek" <[EMAIL PROTECTED]>
> To: 
> Sent: Thursday, February 09, 2006 5:28 PM
> Subject: Multiple many-to-many SELECT
>
>
> Hello,
>
> I apologize if this is a common question; I have been working with it
> and googling for days, and can't seem to find anyone who has been
> trying to do the same thing that I am. Maybe I'm using the wrong
> keywords. In any event, I am desperate for help. Here is my problem:
>
> I have a database with several tables related on a many-to-many
> basis. Here is a simplified description:
>
> TABLE: movies
> +-+-+--+-+-+---+
> | Field   | Type| Null | Key | Default | Extra |
> +-+-+--+-+-+---+
> | id  | int(11) |  | PRI | 0   |   |
> | catalog_description | text| YES  | | NULL|   |
> | title   | text| YES  | | NULL|   |
> | website_url | text| YES  | | NULL|   |
> +-+-+--+-+-+---+
>
> TABLE: director
> +-+-+--+-+-
> ++
> | Field   | Type| Null | Key | Default |
> Extra  |
> +-+-+--+-+-
> ++
> | id  | int(11) |  | UNI | NULL|
> auto_increment |
> | director_first_name | text|  | PRI |
> ||
> | director_last_name  | text|  | PRI |
> ||
> +-+-+--+-+-
> ++
>
> TABLE: producer
> +-+-+--+-+-
> ++
> | Field   | Type| Null | Key | Default |
> Extra  |
> +-+-+--+-+-
> ++
> | id  | int(11) |  | UNI | NULL|
> auto_increment |
> | producer_first_name | text|  | PRI |
> ||
> | producer_last_name  | text|  | PRI |
> ||
> +-+-+

Re: For help

[sheeri kritzer]

If you mean a slave that replicates more than one master, that is not
possible --

ie, this is not possible:

Master1 --
  |--> Slave
Master 2 -

However, if you mean:

Master 1 <---> Master2

Then all you have to do is set up replication so Master2 is a slave of
Master1, and then set up Master1 to be a slave of Master2, using the
regular instructions:

http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html

Note that this is potentially dangerous because of the possibility of
duplicating unique keys.

On 2/10/06, 罗新荣 <[EMAIL PROTECTED]> wrote:
> hello ！
>   Has anybody used of Multiple-master replication?
>
>   How to set up?
>
>   How to modify the my.cnf file?
>
>Thank you very much!!!
>
>
>
> [EMAIL PROTECTED]
> 2006-02-10
>

Problems Starting MySQL

[Rob Lacey]

Wondering if anyone can shed some light on a problem I am experiencing.

I am running MySQL 4.1 on a Red Hat Enterprise Linux ES release 4 (Nahant  
Update 2) machine.

I have been trying to get the default /etc/init.d/mysqld script to restart the 
server but I keep running into the same problems. The MySQL server itself is 
running at the moment and has a number of live databases on it.

060209 18:40:43  mysqld started
060209 18:40:43 [Warning] Can't create test 
file /var/lib/mysql/thirdeye.lower-test
/usr/libexec/mysqld: Can't change dir to '/var/lib/mysql/' (Errcode: 13)
060209 18:40:43 [ERROR] Aborting

060209 18:40:43 [Note] /usr/libexec/mysqld: Shutdown complete

060209 18:40:43  mysqld ended

I can't see where the thirdeye.lower-test file creation is coming into this at 
all. Since as far as can see the startup script doesn't seem to call any 
script to create test databases. Is this part of mysqld_safe?

I can also see that /var/lib/mysql is already owned by mysql.mysql with 755 
permissions. Indeed the RedHat startup script makes this so. Therefore the 
permissions error seems extremely odd as they seem fine to me.

I can start the server using the following directly on the command line, which 
is what the startup script ultimately runs anyway.

/usr/bin/mysqld_safe  --defaults-file=/etc/my.cnf 
--pid-file= /var/run/mysqld/mysqld.pid > /dev/null 2>&1 &

Can anyone give me something further to go on?

Many thanks.

Rob

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

strange problem ( for me..)

[Riccardo Roasio]

Hi,
i have a little mistake during an interrogation...

if i do

SELECT Cell_line FROM Mutations Where Cell_line='NCI-H63';

it returns a lot of results , all NCIH63 , and this is correct...



but if i do :

SELECT DISTINCT Cell_line FROM Mutations Where Cell_line='NCI-H63';

it return empty result, while i espect 1 row with NCI-H63;


Is my fault?

thanks,Riccardo

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

For help

[罗新荣]

hello ！
  Has anybody used of Multiple-master replication? 

  How to set up?

  How to modify the my.cnf file?
 
   Thank you very much!!! 


　　
[EMAIL PROTECTED]
　　2006-02-10

RE: Surviving MySQL crash

[Logan, David (SST - Adelaide)]

That sounds an even better idea!

Regards


---
** _/ **  David Logan 
***   _/ ***  ITO Delivery Specialist - Database
*_/*  Hewlett-Packard Australia Ltd
_/_/_/  _/_/_/    E-Mail: [EMAIL PROTECTED]
   _/  _/  _/  _/     Desk:   +618 8408 4273
  _/  _/  _/_/_/  Mobile: 0417 268 665
*_/   **
**  _/    Postal: 148 Frome Street,
   _/ **  Adelaide SA 5001
  Australia 
invent   
---

-Original Message-
From: Foo Ji-Haw [mailto:[EMAIL PROTECTED] 
Sent: Friday, 10 February 2006 6:57 PM
To: Dan Nelson
Cc: David Logan; Heikki Tuuri; mysql@lists.mysql.com; Logan, David (SST
- Adelaide)
Subject: Re: Surviving MySQL crash


>>15 * * * * /usr/local/mysql/bin/mysql -u root -pxx -e 'FLUSH
TABLES'
>>
>>This would run a FLUSH TABLES once every hour at 15 minutes past. If
you 
>>are using Windows, sorry I'm not sure how to do it there.
>>
>>
>
>Easier to just set flush_time=900 in my.cnf :)
>  
>
Thanks guys. Will give it a shot.


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

I need Query Help

[Veerabhadrarao Narra]

I have a table named table1 structure is

++---+--+-+-+---+
| Field  | Type  | Null | Key | Default | Extra |
++---+--+-+-+---+
| name   | varchar(50)   | NO   | PRI | |   |
| gender | enum('f','m') | YES  | | NULL|   |
++---+--+-+-+---+

And Values like

+--++
| name | gender |
+--++
| 1| m  |
| 2| m  |
| 3| m  |
| 4| m  |
| 5| m  |
| 6| m  |
| 7| m  |
| 8| m  |
| 9| m  |
| 91   | f  |
| 92   | f  |
| 93   | f  |
| 94   | f  |
| 95   | f  |
| 96   | f  |
| 97   | f  |
| 98   | f  |
| 99   | f  |
+--++

Now i want to change the values in gender column
from 'm' to 'f' as well as 'f' to 'm' in single UPDATE statement. How can
we write this. And i have check constraint it accept only 'f' or 'm'.
 (Means name 1 to 9 gender have to change 'f' and 91 to 99 'm')



-- 
Thanks & Regards,
veerabhadrarao narra,
+91-988-556-5556


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

RE: Migration

[James Dey]

Sure, I just use "mysql db name < olddbname.sql

Regards,

James Dey

tel +27 11 704-1945
cell+27 82 785-5102
fax +27 11 388-8907
mail[EMAIL PROTECTED]


-Original Message-
From: Martijn Tonies [mailto:[EMAIL PROTECTED] 
Sent: 10 February 2006 11:58 AM
To: mysql@lists.mysql.com
Subject: Re: Migration


> I am migrating a database from mySQL 4.0 to 4.1.16 and have the error
>
>
>
> ERROR 1064 at line 21: You have an error in your SQL syntax.  Check the
> manual that corresponds to your MySQL server version for the right syntax
to
> use near 'ALTER TABLE `jos_banner` */' at line 20
>
>
>
> Could anyone perhaps let me know how I can remedy this,

Care to show the full statement?

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.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]

Re: Migration

[Martijn Tonies]

> I am migrating a database from mySQL 4.0 to 4.1.16 and have the error
>
>
>
> ERROR 1064 at line 21: You have an error in your SQL syntax.  Check the
> manual that corresponds to your MySQL server version for the right syntax
to
> use near 'ALTER TABLE `jos_banner` */' at line 20
>
>
>
> Could anyone perhaps let me know how I can remedy this,

Care to show the full statement?

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
Upscene Productions
http://www.upscene.com
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com


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

Migration

[James Dey]

Hi Guys,

 

I am migrating a database from mySQL 4.0 to 4.1.16 and have the error

 

ERROR 1064 at line 21: You have an error in your SQL syntax.  Check the
manual that corresponds to your MySQL server version for the right syntax to
use near 'ALTER TABLE `jos_banner` */' at line 20

 

Could anyone perhaps let me know how I can remedy this,


Thanks a lot!

 

James Dey

 

tel   +27 11 704-1945

cell  +27 82 785-5102

fax   +27 11 388-8907

mail  [EMAIL PROTECTED]

 

myGUS / SLT retains all its intellectual property rights in any information
contained in e-mail messages (or any attachments thereto) which relates to
the official business of myGUS / SLT or of any of its associates. Such
information may be legally privileged, is to be treated as confidential and
myGUS / SLT will take legal steps against any unauthorised use. myGUS / SLT
does not take any responsibility for, or endorses any information which does
not relate to its official business, including personal mail and/or opinions
by senders who may or may not be employed by myGUS / SLT. In the event that
you receive a message not intended for you, we request that you notify the
sender immediately, do not read, disclose or use the content in any way
whatsoever and destroy/delete the message immediately. While myGUS / SLT
will take reasonable precautions, it cannot ensure that this e-mail will be
free of errors, viruses, interception or interference therewith. myGUS / SLT
does not, therefore, issue any guarantees or warranties in this regard and
cannot be held liable for any loss or damages incurred by the recipient
which have been caused by any of the above-mentioned factors.

 

Re: Surviving MySQL crash

[Foo Ji-Haw]

15 * * * * /usr/local/mysql/bin/mysql -u root -pxx -e 'FLUSH TABLES'

This would run a FLUSH TABLES once every hour at 15 minutes past. If you 
are using Windows, sorry I'm not sure how to do it there.
   



Easier to just set flush_time=900 in my.cnf :)
 


Thanks guys. Will give it a shot.


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

Re: Surviving MySQL crash

[Dan Nelson]

In the last episode (Feb 10), David Logan said:
> Foo Ji-Haw wrote:
> >Heikki Tuuri wrote:
> >>if the OS crashes do not corrupt files, then InnoDB tables would
> >>survive an OS crash without a problem.
> >
> >Thanks for the reply Heikki. Let me rephrase my problem: the data
> >remains intact, but I suspect the indexes are corrupted. Basically I
> >have to run myisamcheck and mysqlcheck to get my tables operational
> >again. No data loss though.
> >
> >Logan (thanks Logan) suggested a periodic flush call. I am wondering
> >if auto flushing is available?
> 
> You could set that up as a small cron job (Are you using unix/Linux?) 
> like so
> 
> 15 * * * * /usr/local/mysql/bin/mysql -u root -pxx -e 'FLUSH TABLES'
> 
> This would run a FLUSH TABLES once every hour at 15 minutes past. If you 
> are using Windows, sorry I'm not sure how to do it there.

Easier to just set flush_time=900 in my.cnf :)

-- 
Dan Nelson
[EMAIL PROTECTED]

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