txns with INNDOB

[TO]

Hi -- 

I am trying to understand txns under INNODB.
I'm running 4.0.16-nt-log.
Here's my test code, all run in one MySQLYog window.


-- step 1 
use scratch;
-- drop table foo;
-- create table foo (x int);
truncate table scratch.foo; 

-- step 2
use scratch;
set autocommit=1;

-- step 3
begin work;  -- start TXN

-- step 4
insert into scratch.foo (x) values (2);

-- step 5
select * from scratch.foo;

-- step 6
commit;

-- step 7
select * from scratch.foo;

Step 4 does not insert a row.  Why not?
I would have thought it would, though the
row would be invisible to other cxns until Step 6.  
Even after the txn finishes, the row is still 
not there in Step 7.

If I remove the 'begin work', the row goes in immediately,
of course, but then there is no txn.

Advice very much welcomed!!

Thanks

TO






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

nested transactions

[TO]

Does INNODB support nested transactions?  If so, what version?
Thanks.





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

multiple databases: design question

[TO]

What are the advantages and disadvantages of using multiple databases, versus 
placing all tables in one uber-database? 

I understand and appreciate the organizational value of multiple databases,
but what other issues are involved?

I ask this because I'm considering  moving from tables across multiple
DBs (on one server) to all tables in one DB (on one server.)

I don't want to do this, but may need to given the constraints of perl's
Class::DBI and Ima::DBI.  Specifically, I am stuck on successfully 
handling transactions and rollback across multiple databases, because
Ima::DBI creates a different handle (eg connection) to each database,
which stymies rollback (as the work is happening thru different cxns).

I'd welcome any suggestions about structuring databases -- one vs. many --
and if anyone has advice about the perl issues, that'd be great too.

Thanks!


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

Problems with clean NET STOP MYSQL on Win2000 server after adding INNODB space

[TO]

Help!

We're running MySQl on Win2000 server.

The other day our INNODB database filled up, so we
stopped the server, backed up the data, and adjusted
the innodb_data_file_path from
ibdata1:500M;ibdata2:500M;ibdata3:500M; 
to
ibdata1:500M;ibdata2:500M;ibdata3:500M;ibdata4:500M;ibdata5:500M;

Late each night, a scheduled task shuts down the server
(NET STOP mysql) so our tape backup can back it all up,
and a few hours later another scheduled task restarts the server
(NET START mysql).

Since we've extended the DB, the "stop" doesn't seem to work --
the database stops, but somehow (perhaps) still holds the port.

That is, when we try to restart the server from the command line,
we get an error about the port already being in use.

(However when we run openports, however, 3306 is not seen as being used.)

*Rebooting* the server frees everything up and mysql will restart
happily.

What have we done wrong?  Why might NET STOP MYSQL not be completely
closing the process cleanly, so that NET START MYSQL can later start it?
Rebooting each morning is not an option, clearly.

Thanks for your advice!

-

Here's our my.ini file:


# generic params for all sql clients
[client]
port=3306

# The MySQL server
[mysqld]
port=3306
skip-locking
set-variable= key_buffer=16M
set-variable= max_allowed_packet=1M
set-variable= table_cache=64
set-variable= sort_buffer=512K
set-variable= net_buffer_length=8K
set-variable= myisam_sort_buffer_size=8M
server-id   = 1

# log updates
log-bin=e:/data/mysql/binarylog

# nonstandard locations of MySQL bin and data
basedir = e:/apps/mysql/
datadir = e:/data/mysql/data


# NOT using BDB tables so skip
skip-bdb

# INNODB STUFF ref manual p 557
innodb_data_home_dir = e:\data\mysql\ibdata
innodb_data_file_path = 
ibdata1:500M;ibdata2:500M;ibdata3:500M;ibdata4:500M;ibdata5:500M;
set-variable = innodb_mirrored_log_groups=1
innodb_log_group_home_dir = e:\data\mysql\iblogs
innodb_log_arch_dir = e:\data\mysql\iblogs
set-variable = innodb_log_files_in_group=3
set-variable = innodb_log_file_size=50M
set-variable = innodb_log_buffer_size=75M
innodb_flush_log_at_trx_commit=1
innodb_log_archive=0
set-variable = innodb_buffer_pool_size=200M
set-variable = innodb_additional_mem_pool_size=4M
set-variable = innodb_file_io_threads=4
set-variable = innodb_lock_wait_timeout=50

# MYSQLDUMP ref manual p. 347

[mysqldump]
quick
add-locks
add-drop-table
all-databases
extended-insert
allow-keywords
lock-tables
set-variable= max_allowed_packet=16M

# MYSQL ref manual p. 336
[mysql]
no-auto-rehash
# maybe remove this later... see ref manual p. 340 
safe-updates

# IASMCHK 
[isamchk]
set-variable= key_buffer=20M
set-variable= sort_buffer=20M
set-variable= read_buffer=2M
set-variable= write_buffer=2M

[myisamchk]
set-variable= key_buffer=20M
set-variable= sort_buffer=20M
set-variable= read_buffer=2M
set-variable= write_buffer=2M

[mysqlhotcopy]
interactive-timeout

# for the admin tool
[WinMySQLAdmin]
Server=E:/apps/mysql/bin/mysqld-max-nt.exe

QueryInterval=10



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

Restoring database with frm myi and myd

[To Glace]

Hello,

A database which has been deleted need to be restored.
There is no .sql dump file.
All we have is the .frm, .myi and .myd files from
/var/lib/mysql/DATABASENAME.

Here are the steps I have taken:
1. Created a new empty database - NEWDB
2. individually restored all tables using the restore command:
restore table TABLENAME from '/PATH/TO/FILES';
Most displayed.. "The storage engine for the table doesn't support restore"
and only created the structure for the table and did not import the actual
data.

I am not sure where to go from here.
The data itself need to be restored back and cannot get to this at all.

Is there a different way to restore this information?
How do I enable the storage engine to support restore?

Any input would be greatly appreciated.

Thank you: TG

Re: Restoring database with frm myi and myd

[To Glace]

The db format is in "innodb"

On Dec 17, 2007 5:38 PM, To Glace <[EMAIL PROTECTED]> wrote:

> Hello,
>
> A database which has been deleted need to be restored.
> There is no .sql dump file.
> All we have is the .frm, .myi and .myd files from
> /var/lib/mysql/DATABASENAME.
>
> Here are the steps I have taken:
> 1. Created a new empty database - NEWDB
> 2. individually restored all tables using the restore command:
> restore table TABLENAME from '/PATH/TO/FILES';
> Most displayed.. "The storage engine for the table doesn't support
> restore"
> and only created the structure for the table and did not import the actual
> data.
>
> I am not sure where to go from here.
> The data itself need to be restored back and cannot get to this at all.
>
> Is there a different way to restore this information?
> How do I enable the storage engine to support restore?
>
> Any input would be greatly appreciated.
>
> Thank you: TG
>

Missing mysql_install_db on Windows binary distribution

[Wilson To]

I'm totally new to mysql so I may be asking a real dumb question here.

Does anyone know whether the running of mysql_install_db necessary for the 
Windows distribution of mysql?  I downloaded the binary 
mysql-3.23.43-win.zip file yesterday and ran the setup.  However, there is 
no mysql_install_db installed (as described in the Technical Reference 
Manual.

Is this still necessary?  Also, is this what installs a test/sample 
database?

Thanks in advance,

Wilson

_
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

have a great problem to install the mysql database

[Renee To]

Dear Sir,


I have a great problem to install the mysql database.  I follow the
instruction at the below :

1.  tar  -xvzf  mysql-3.23.37.tar.gz
2.  ./configure ¡Vprefix=/usr/local/mysql
3.  make 
4.  make install
5.  echo "/usr/local/mysql/lib/mysql" >> /etc/ld.so.conf
6.  ldconfig -v | grep libmysqlclient 
7.  echo "/usr/local/mysql/bin/safe_mysqld > /dev/null &" >>
/etc/rc.d/rc.local 
8.  ./scripts/mysql_install_db
9.  /usr/local/mysql/bin/safe_mysqld > /dev/null &
10. PATH=¡¨$PATH:/usr/local/mysql/bin¡¨
11. Mysqladmin version

(when I input the no. 11 command, the screen show
out that bash : Lost connection to MYSQL Server during query)

Therefore, I input another command
(/usr/share/mysql/mysql.server start)
The screen show out that (Starting mysqld daemon
with database from /var/lib/mysql)

12. chmod +x /usr/local/mysql/mysql.server
13. mysql

(when I input the command mysql)
The screen show out that :
(Error 2013: Number of Processes running now : 1)
(mysqld process hanging : Pid 955-killed )
(mysql restarted on Sat Apr 21 09:33:27 CST 2001)


Please, can you ask me what ¡¥s happen and why, moreover; how can I do?


>From a worry programmer
RTO

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

error 2013 and 2002 when i install the mysql database

[Renee To]

Dear Sir, 

My Linux is RedHat Linux 6.0
I follow the below step to install the mysql version 3.23.37 database :

1.  tar ¡Vzxvf mysql-3.23.37.tar.gz
2.  ./configure ¡Vprefix/usr/local/mysql
3.  make
4.  make install
5.  echo ¡§/usr/local/mysql/lib/mysql¡¨ >> /etc/ld.so.conf
6.  ldconfig ¡Vv |grep libmysqlclient
7.  echo ¡§/usr/local/msyql/bin/safe_mysqld > /dev/null &¡¨ >>
/etc/rc.d/rc.local
8.  ./scripts/mysql_install_db
9.  /usr/local/mysql/bin/safe_mysqld > /dev/null &
10. PATH=¡¨$PATH:/usr/local/mysql/bin¡¨
11. mysqladmin version

(The screen show out that   :
     mysqladmin : connect to server at ¡¥localhost¡¦ failed
     Lost connection to Mysql Server during query)

I check the file /etc/resolv.conf  and I change the content
from 

Search
Servername

To 

Search localhost
Servername 127.0.0.1


I also check the file /etc/hosts  and the content is 

127.0.0.1   localhost   localhost.localdomain


After I prepare this two file, I input the command
mysqladmin version
It also not improve the situation.


Then, I check the file /tmp/mysql.sock and /etc/my.cnf but
this two files have not exist


Can you ask me what¡¦s happen and how can I do ?

This problem is very urgent for me.

I attach the mysqlbug file to you


>From the very worry programmer
RTO
 <> 



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Blob accepts LongBlob without error

[Ryan @ Common Ground . To]

I have successfully inserted and retrieved binary image files within 
MySQL.  No big deal.

However, for quite some time I was perplexed because only about half my 
image was going through.

It seems that MySQL accepts insertion of a string into a BLOB field that 
is longer than the maximum length of the BLOB data-type and it will 
still accept it.

Perhaps MySQL should output an error or at least a warning to know the 
string is too long.

Thanks much,

-Ryan Hatch


-
Before posting, please check:
  http://www.mysql.com/manual.php   (the manual)
  http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: MySQL 4.0.7 is released

[[EMAIL PROTECTED] via news-to-mail gateway]

In article <[EMAIL PROTECTED]>, Andreas  <[EMAIL PROTECTED]> wrote:
>Hsiao Ketung Contr 61 CS/SCBN wrote:
>
>> Who would have the need to use src version of installation.
>> I imagine that src version give user more options for customizing MySql.
>
>sure ... but do you feel anythig lacking ?

I know two reasons at least (because I have done both):

1. The site uses an unconventional directory structure by choice;
   have to run configure manually to get the directory structure
   right.

2. Previous installations from RPM gave upgrade/dependency troubles;
   decided to use source to make sure that MySQL works at the site.


-- 
Ambrose Li  <[EMAIL PROTECTED]>
http://ada.dhs.org/~acli/cmcc/  http://www.cccgt.org/

DRM is theft - We are the stakeholders

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

reset auto_increment

[INVALID - TESTING ONLY. IF YOU GET EMAIL WITH THIS ACCOUNT PLEASE REPLY TO [EMAIL PROTECTED] INSTEAD. OBVIOUSLY WE MADE A MISTAKE IF YOU ARE READING THIS]

I need to reset the auto_increment in a table full of data.  I had a problem
recently where some script was putting InvoiceID numbers into an
auto_increment CustID column...I since fixed the problem and corrected the
data but I now have a huge gap in my number sequence and I cant get
auto_increment reset to a more reasonable value.
 
Now I have read the archives and the online manual with user comments and
nothing has worked as of yet.
 
ALTER TABLE tbl_name AUTO_INCREMENT = 1
this command gives me a successful response but when I do a SHOW TABLE
STATUS the Auto_increment is still unchanged.
 
myisamchk -A=1 /path/to/db.MYI
this command also gives me a successful message but never changes the
auto_increment when displayed by SHOW TABLE STATUS
 
in section 3.5.9 of the MySQL manual with user comments I noted the
following comment on Oct 23 20002 by Ethan Pooley
http://www.mysql.com/doc/en/example-AUTO_INCREMENT.html
He stated that the ALTER TABLE tbl_name AUTO_INCREMENT = 1 will only work
when the table is empty.
 
So what I need is the ability to reset the auto_increment without having to
empty the table first.  Or I need someone to help me figure out why the
above mentioned commands fail to do what everyone tells me they are suppose
to do.
 
-Jason


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php