Re: mysql_real_query.... probably asked a lot

[Ludwig Pummer]

Eric Smith wrote:
OK, I'll bet you guys get a lot of this one, but I can't seem to find 
the answer in the archives.


I have binary data that I want to store in a longblob.  This is just 
byte data null bytes are possible.  So, I use mysql_real_query.  How 
do I format the char* query string?


Here's the way my format looks:
sprintf(queryString,"update images set imageData=%p where 
imageID=\'%s\'",imageData,[imageID cString]);


and then I do the query:
result = 
mysql_real_query(theConnection,queryString,strlen(theDBData)+nBytes);


where strlen(theDBData)+nBytes gives the total byte count for 
queryString.  Well, I get an error message saying that I have an error 
in my syntax.  How do I format this properly?


Thanks,
Eric


The documentation for mysql_real_query does explain that it can handle 
null bytes, but what if your binary data contains single-quote? You get 
a syntax error.


I see two options for you here:
1) use mysql_real_escape_string() on the binary data before you build it 
into the final query string

2) use the prepared statement API

If you go with option 1, you'll need to allocate another buffer twice 
the size of imageData to hold the escaped version.


If you go with option 2, you can use the imageData buffer directly, but 
you'll have to use the prepared statement functions instead of 
mysql_real_query(). I've never used prepared statements in the C API so 
I'm just going on what the documentation says.


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

Re: It's party time!

[Dotan Cohen]

On 2/25/06, Martijn Tonies <[EMAIL PROTECTED]> wrote:
>
> I'm sorry to say, there won't be any version for Linux. Not this
> year, at least.
>
> You can, however, connect to MySQL running on a Linux host.
>
> Martijn Tonies

Too bad. Do you provide the winbox so that I can connect? 'Cause I've
nothing but Kubuntu and Fedora at home.

Dotan Cohen
http://technology-sleuth.com/

mysql_real_query.... probably asked a lot

[Eric Smith]

OK, I'll bet you guys get a lot of this one, but I can't seem to find  
the answer in the archives.


I have binary data that I want to store in a longblob.  This is just  
byte data null bytes are possible.  So, I use mysql_real_query.   
How do I format the char* query string?


Here's the way my format looks:
sprintf(queryString,"update images set imageData=%p where imageID=\'%s 
\'",imageData,[imageID cString]);


and then I do the query:
result = mysql_real_query(theConnection,queryString,strlen(theDBData) 
+nBytes);


where strlen(theDBData)+nBytes gives the total byte count for  
queryString.  Well, I get an error message saying that I have an  
error in my syntax.  How do I format this properly?


Thanks,
Eric

Re: Debian and "localhost.localdomain"

[Yves Goergen]

On 26.02.2006 00:10 (+0100), Yves Goergen wrote:
I've been trying to get MySQL to work on Debian 3.1 but it won't let my 
newly created users connect. I added users like "[EMAIL PROTECTED]" 
and when one tries to connect to the server, it says 
"[EMAIL PROTECTED]" is not allowed to connect. I suppose 
it's a Debian 3.1 issue since it worked on Debian 3.0. Does anybody know 
how I can get that "localdomain" out of my computer? Using MySQL 4.0.


Aah, okay, forget it. :) I changed my hosts file and removed one of the 
several names, the one with that "localdomain". I have no idea what 
special meaning that first item had, because "hostname" always showed me 
the last one ("debian31"). Now I have rebooted that machine (just like 
with Windows, eh?) and it works now. The system seems to have cached 
that domain name somewhere in memory.


--
Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]>
"Does the movement of the trees make the wind blow?"
http://newsboard.unclassified.de - Unclassified NewsBoard Forum

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

Re: Debian and "localhost.localdomain"

[Yves Goergen]

On 26.02.2006 00:24 (+0100), Sam Tran wrote:

What do you have in your /etc/hosts file?


127.0.0.1   localhost   debian31

(and some IPv6 stuff that all begins with "ip6-")

There was a "localhost.localdomain" in that file, too, which I already 
removed, but that didn't help.


--
Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]>
"Does the movement of the trees make the wind blow?"
http://newsboard.unclassified.de - Unclassified NewsBoard Forum

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

Re: Debian and "localhost.localdomain"

[Sam Tran]

On 2/25/06, Yves Goergen <[EMAIL PROTECTED]> wrote:
> I've been trying to get MySQL to work on Debian 3.1 but it won't let my
> newly created users connect. I added users like "[EMAIL PROTECTED]"
> and when one tries to connect to the server, it says
> "[EMAIL PROTECTED]" is not allowed to connect. I suppose
> it's a Debian 3.1 issue since it worked on Debian 3.0. Does anybody know
> how I can get that "localdomain" out of my computer? Using MySQL 4.0.
>


What do you have in your /etc/hosts file?

Sam

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

Debian and "localhost.localdomain"

[Yves Goergen]

I've been trying to get MySQL to work on Debian 3.1 but it won't let my 
newly created users connect. I added users like "[EMAIL PROTECTED]" 
and when one tries to connect to the server, it says 
"[EMAIL PROTECTED]" is not allowed to connect. I suppose 
it's a Debian 3.1 issue since it worked on Debian 3.0. Does anybody know 
how I can get that "localdomain" out of my computer? Using MySQL 4.0.


--
Yves Goergen "LonelyPixel" <[EMAIL PROTECTED]>
"Does the movement of the trees make the wind blow?"
http://newsboard.unclassified.de - Unclassified NewsBoard Forum

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

Fw: Re[2]: using WHERE and OR in a query

[Rhino]

I'm putting this discussion back in the mailing list where it belongs; that 
way other people can contribute suggestions and learn from the discussion, 
either now or in the future via the archives.


--

Is there any possibility the Update is doing exactly what your SQL is 
telling it to do? You're asking for a row to be updated if _any_ of the 
following are true:

- the subscribe column is not blank
- the artist column contains 1
- the rank_change column does not contain 1

If at least one of those conditions is true for each row in the table, then 
it is entirely reasonable that every row gets updated because every row 
qualifies for the update. That's why I suggested you provide sample data, 
expected results and actual results: if you had done that, I probably 
wouldn't have to ask this followup question.


What language is your code? It looks a bit like Perl to me but I sometimes 
confuse Perl and PHP.


Is '!=' a legitimate way to say 'not equal' in this version of MySQL and the 
language you are using? I normally use either the NOT keyword or the symbols 
'<>' to negate conditions, for example:

- WHERE NOT subscribe = ''
- WHERE subscribe <> ''
Some dialects of SQL and some programming languages support '!=' but not all 
of them do. You should check that in the MySQL reference for 3.23 and the 
programming language, whatever it is.


Are you sure you are executing the statement against the table you _think_ 
you are updating? For example, Is it possible that you mean to execute the 
statement against a production table but are actually executing it against a 
test table that has very different data than the production table?


--
Rhino

- Original Message - 
From: "Bruce Therrien" <[EMAIL PROTECTED]>

To: "Rhino" <[EMAIL PROTECTED]>
Sent: Saturday, February 25, 2006 4:37 PM
Subject: Re[2]: using WHERE and OR in a query



the query is updating all records with no regards to the WHERE clauses.


On Sat, 25 Feb 2006 16:36:38 -0500
"Rhino" <[EMAIL PROTECTED]> wrote:

Define "not work": do you mean you get a compile error? A runtime error? 
Or

a result that differs from what you expected?

If you got an error message, please state the full message text. If the
result simply differed from what you wanted, please give us a small 
sample

of data, your expectations for what the result should have been, and
information about what you actually got.

The CREATE TABLE statement could also be helpful in determining what went
wrong.

--
Rhino

- Original Message - 
From: "Bruce Therrien" <[EMAIL PROTECTED]>

To: 
Sent: Saturday, February 25, 2006 4:28 PM
Subject: using WHERE and OR in a query


> Why does this query not work using mysql 3.23.58?
>
> $SQL =<> UPDATE $tablename SET rank = '$qdj_rank_en', rank_icon = 
> '$qdj_rank_icon',

> qdj = qdj+'$adata{qdj_credit}', gold = gold+'$adata{gold_credit}' WHERE
> subscribe != '' OR artist = '1' OR rank_change != '1'
> SQL
> $dbh->do($SQL) ||  &cgierr("Reason: $!");
>
>
>
> -- 
> Bruce Therrien <[EMAIL PROTECTED]>

>
> This is the end of the internet.
> Please turn around and go back.
>
>
> -- 
> MySQL General Mailing List

> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: 
> http://lists.mysql.com/[EMAIL PROTECTED]

>
>
> -- 
> No virus found in this incoming message.

> Checked by AVG Free Edition.
> Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 
> 24/02/2006

>
>



--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 24/02/2006


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





--
Bruce Therrien <[EMAIL PROTECTED]>

This is the end of the internet.
Please turn around and go back.


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 24/02/2006






--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 24/02/2006


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

using WHERE and OR in a query

[Bruce Therrien]

Why does this query not work using mysql 3.23.58?
The query is updating all records with no regards to the WHERE clauses.
Do I need to do separate updates for each WHERE clause?

$SQL =

This is the end of the internet. 
Please turn around and go back.


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

Re: using WHERE and OR in a query

[Rhino]

Define "not work": do you mean you get a compile error? A runtime error? Or 
a result that differs from what you expected?


If you got an error message, please state the full message text. If the 
result simply differed from what you wanted, please give us a small sample 
of data, your expectations for what the result should have been, and 
information about what you actually got.


The CREATE TABLE statement could also be helpful in determining what went 
wrong.


--
Rhino

- Original Message - 
From: "Bruce Therrien" <[EMAIL PROTECTED]>

To: 
Sent: Saturday, February 25, 2006 4:28 PM
Subject: using WHERE and OR in a query



Why does this query not work using mysql 3.23.58?

$SQL =do($SQL) ||  &cgierr("Reason: $!");



--
Bruce Therrien <[EMAIL PROTECTED]>

This is the end of the internet.
Please turn around and go back.


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


--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 24/02/2006






--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 268.1.0/269 - Release Date: 24/02/2006


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

using WHERE and OR in a query

[Bruce Therrien]

Why does this query not work using mysql 3.23.58?

$SQL =

This is the end of the internet. 
Please turn around and go back.


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

Re: Problem installing mysql on Debian linux

[Tselemegkos Grigorios]

Thanks for the reply Sam,  :-)

I'm using Debian 3.1
I thought that termcap libraries where into ndevel package, which i
installed.
Where can i find the termcap libraries.
Do i have to do something special in the configure of termcap or mysql?
I need a version of mysql < 4.1.0 because i need to install CDSware (it has
problems with earlier versions)
Does mysql 5.0.18 compile without termcap libraries installed?

Gregory




- Original Message - 
From: "Sam Tran" <[EMAIL PROTECTED]>

To: "Tselemegkos Grigorios" <[EMAIL PROTECTED]>
Cc: 
Sent: Saturday, February 25, 2006 9:34 PM
Subject: Re: Problem installing mysql on Debian linux


On 2/25/06, Tselemegkos Grigorios <[EMAIL PROTECTED]> wrote:

Hi,

I'm trying to install mysql 4.0.25 from source on Debian linux without
root privileges.
The problem comes when i execute the configure command.
I typed:
configure --prefix /home/grtsel/mysql

and the output error was:

checking for FIONREAD in sys/ioctl.h... yes
checking for TIOCSTAT in sys/ioctl.h... no
checking if struct dirent has a d_ino member... yes
checking whether signal handlers are of type void... yes
checking for tgetent in -lncurses... no
checking for tgetent in -lcurses... no
checking for tgetent in -ltermcap... no
checking for termcap functions library... configure: error: No
curses/termcap library found

I installed ncurses package from source in order to bypass this problem
but nothing happend...

Does anyone know what's going wrong..?
Please help me...



Gregory,

You need to install the termcap libraries.
What version of Debian are you using? Why don't you install MySQL 5.0.18?

Sam

__
Χρησιμοποιείτε Yahoo!;
Βαρεθήκατε τα ενοχλητικά μηνύματα (spam);   Το Yahoo! Mail διαθέτει την καλύτερη δυνατή προστασία κατά των ενοχλητικών μηνυμάτων  
http://mail.yahoo.gr 



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

Re: Problem installing mysql on Debian linux

[Sam Tran]

On 2/25/06, Tselemegkos Grigorios <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I'm trying to install mysql 4.0.25 from source on Debian linux without root 
> privileges.
> The problem comes when i execute the configure command.
> I typed:
> configure --prefix /home/grtsel/mysql
>
> and the output error was:
>
> checking for FIONREAD in sys/ioctl.h... yes
> checking for TIOCSTAT in sys/ioctl.h... no
> checking if struct dirent has a d_ino member... yes
> checking whether signal handlers are of type void... yes
> checking for tgetent in -lncurses... no
> checking for tgetent in -lcurses... no
> checking for tgetent in -ltermcap... no
> checking for termcap functions library... configure: error: No curses/termcap 
> library found
>
> I installed ncurses package from source in order to bypass this problem but 
> nothing happend...
>
> Does anyone know what's going wrong..?
> Please help me...
>

Gregory,

You need to install the termcap libraries.
What version of Debian are you using? Why don't you install MySQL 5.0.18?

Sam

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

Problem installing mysql on Debian linux

[Tselemegkos Grigorios]

Hi,

I'm trying to install mysql 4.0.25 from source on Debian linux without root 
privileges.
The problem comes when i execute the configure command.
I typed:
configure --prefix /home/grtsel/mysql

and the output error was:

checking for FIONREAD in sys/ioctl.h... yes
checking for TIOCSTAT in sys/ioctl.h... no
checking if struct dirent has a d_ino member... yes
checking whether signal handlers are of type void... yes
checking for tgetent in -lncurses... no
checking for tgetent in -lcurses... no
checking for tgetent in -ltermcap... no
checking for termcap functions library... configure: error: No curses/termcap 
library found

I installed ncurses package from source in order to bypass this problem but 
nothing happend...

Does anyone know what's going wrong..?
Please help me...

Gregory

RE: error 1016 : cant open ibd file even though it exists

[mysql]

I do not use Innodb tables at the moment, so all this is 
pure speculation.

Is/was the server connected to a UPS when the power failure 
happened? 

If so, did the UPS function properly and do you have any UPS 
logs to match against the mysql error log?

Things to check for would be the time the UPS took over from 
the mains supply?

If there was no UPS in operation, were your InnoDB tables 
corrupted when the power failure occured.

Is it possible for the InnoDB tables to have become so 
corrupted that mysql had no alternative but to re-construct 
the tables?

What are your config settings for mysql at boot up time?
Do you have any directives to force checking of InnoDB 
tables at boot-time, and if so what are they please?

As soon as you mentioned 'power failure' the words 'table 
corruption' sprang to my mind.

Also, are there any mysql transaction logs (*.log) you can 
check, to see what was happening when the power outage 
occured?

Keith

In theory, theory and practice are the same;
In practice they are not. 

On Sat, 25 Feb 2006, Rithish Saralaya wrote:

> To: [EMAIL PROTECTED]
> From: Rithish Saralaya <[EMAIL PROTECTED]>
> Subject: RE: error 1016 : cant open ibd file even though it exists
> 
> Hello David.
> 
> There was supposed to be a power outage in our office that day. So the
> server was shut down. Finally when the power was back, the machine was
> plugged on. That's all. No file system change. Nothing.
> 
> Regards,
> Rithish.

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

Re: It's party time!

[Martijn Tonies]

> On Friday 24 February 2006 09:14, Martijn Tonies wrote:
> > And we're handing out the presents...
> >
> > A 5-year celebration of our developer tool Database Workbench.
> >
> > The MySQL module was added recently, so are the MS SQL
> > Server and Oracle modules.
> >
> > >From this Monday until next Friday, it's a 75% discount!
> >
> > Check here:
> > http://www.upscene.com/index.htm?dbw_party_promo.htm
>
> Looks pretty nice.  I'll look at buying a copy as soon as there is a
version
> for Linux.

I'm sorry to say, there won't be any version for Linux. Not this
year, at least.

You can, however, connect to MySQL running on a Linux host.

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, Oracle & MS SQL
Server
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: error 1016 : cant open ibd file even though it exists

[Heikki Tuuri]

Rithish,

ok, then the most probable explanation is that someone had edited my.cnf 
earlier, when mysqld was running. When mysqld was restarted, it read the new 
my.cnf, and got confused.


Please ask your sysadmins to scan the file system of the computer if they 
can find another ibdata1 file or ib_logfiles.


Best regards,

Heikki

Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL

InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM 
tables

http://www.innodb.com/order.php


- Original Message - 
From: ""Rithish Saralaya"" <[EMAIL PROTECTED]>

Newsgroups: mailing.database.myodbc
Sent: Saturday, February 25, 2006 12:14 PM
Subject: RE: error 1016 : cant open ibd file even though it exists



--=_NextPart_000_001D_01C63A22.BB0C91A0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: 7bit

Hello David.

There was supposed to be a power outage in our office that day. So the
server was shut down. Finally when the power was back, the machine was
plugged on. That's all. No file system change. Nothing.

Regards,
Rithish.
 -Original Message-
 From: David Logan [mailto:[EMAIL PROTECTED]
 Sent: Saturday, February 25, 2006 3:32 PM
 To: Rithish Saralaya
 Cc: mysql@lists.mysql.com
 Subject: Re: error 1016 : cant open ibd file even though it exists


 Hi Rithish,

 After reading Heikkis points, I am inclined to agree. Did your sysadmins
change a filesystem during the maintenance? Did they forget to restore a
directory if they changed disks? What was the maintenance that was
performed? Your InnoDB files disappeared at some point because the server
would not have recreated them otherwise. I am sure it was a graceful
shutdown, but something has changed. These things just don't happen by
themselves.

 Regards

 Rithish Saralaya wrote:
The mysql server was shut down for maintenance. However it was a graceful
shutdown and restart. That's all. No files were touched or removed. How
could this have happened, I fail to see.

Regards,
Rithish.


-Original Message-
From: Heikki Tuuri [mailto:[EMAIL PROTECTED]
Sent: Friday, February 24, 2006 3:55 PM
To: mysql@lists.mysql.com
Subject: Re: error 1016 : cant open ibd file even though it exists


Rithish,

from the .err log we see that mysqld was shut down for 12 hours on Feb 
19th.

What did the sysadmins do during that time?

There are two plausible explanations:

1) they edited datadir in my.cnf to point to a different location (
/var/lib/mysql),

or

2) they removed ibdata1 and ib_logfiles from the the datadir.

That caused InnoDB to recreate these files.

Best regards,

Heikki

Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL

InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up 
MyISAM

tables
http://www.innodb.com/order.php

- Original Message -
From: ""Rithish Saralaya"" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Friday, February 24, 2006 6:55 AM
Subject: RE: error 1016 : cant open ibd file even though it exists


 Hello.

The tables were working perfectly fine a week back. The database was
created
from a sql file generated through the mysqldump utility. So there was
nothing wrong with the database. This irregularity happened this week
onwards.

Our system admins tell us that the server was restarted last weekend. When
I
dug up the mysql error logs, this was what I found saw.

==
060219  5:20:25  InnoDB: Starting shutdown...
060219  5:20:30  InnoDB: Shutdown completed; log sequence number 19
1867461149
060219  5:20:30 [Note] /usr/sbin/mysqld: Shutdown complete


Memory status:
Non-mmapped space allocated from system: 16589028
Number of free chunks: 10
Number of fastbin blocks: 0
Number of mmapped regions: 19
Space in mmapped regions: 1472028672
Maximum total allocated space: 0
Space available in freed fastbin blocks: 0
Total allocated space: 16479548
Total free space: 109480
Top-most, releasable space: 102224
Estimated memory (with thread stack):1488744676

060219 05:20:30  mysqld ended

060219 16:57:48  mysqld started
060219 16:57:49 [Warning] Asked for 196608 thread stack, but got 126976
InnoDB: The first specified data file /var/lib/mysql/ibdata1 did not
exist:
InnoDB: a new database to be created!
060219 16:57:49  InnoDB: Setting file /var/lib/mysql/ibdata1 size to 10
MB
InnoDB: Database physically writes the file full: wait...
060219 16:57:50  InnoDB: Log file /var/lib/mysql/ib_logfile0 did not
exist:
new to be created
InnoDB: Setting log file /var/lib/mysql/ib_logfile0 size to 256 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200
060219 16:58:02  InnoDB: Log file /var/lib/mysql/ib_logfile1 did not
exist:
new to be created
InnoDB: Setting log file /var/lib/mysql/ib_logfile1 size to 256 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200
060219 16:58:1

RE: error 1016 : cant open ibd file even though it exists

[Rithish Saralaya]

Hello David.

There was supposed to be a power outage in our office that day. So the
server was shut down. Finally when the power was back, the machine was
plugged on. That's all. No file system change. Nothing.

Regards,
Rithish.
  -Original Message-
  From: David Logan [mailto:[EMAIL PROTECTED]
  Sent: Saturday, February 25, 2006 3:32 PM
  To: Rithish Saralaya
  Cc: mysql@lists.mysql.com
  Subject: Re: error 1016 : cant open ibd file even though it exists


  Hi Rithish,

  After reading Heikkis points, I am inclined to agree. Did your sysadmins
change a filesystem during the maintenance? Did they forget to restore a
directory if they changed disks? What was the maintenance that was
performed? Your InnoDB files disappeared at some point because the server
would not have recreated them otherwise. I am sure it was a graceful
shutdown, but something has changed. These things just don't happen by
themselves.

  Regards

  Rithish Saralaya wrote:
The mysql server was shut down for maintenance. However it was a graceful
shutdown and restart. That's all. No files were touched or removed. How
could this have happened, I fail to see.

Regards,
Rithish.


-Original Message-
From: Heikki Tuuri [mailto:[EMAIL PROTECTED]
Sent: Friday, February 24, 2006 3:55 PM
To: mysql@lists.mysql.com
Subject: Re: error 1016 : cant open ibd file even though it exists


Rithish,

from the .err log we see that mysqld was shut down for 12 hours on Feb 19th.
What did the sysadmins do during that time?

There are two plausible explanations:

1) they edited datadir in my.cnf to point to a different location (
/var/lib/mysql),

or

2) they removed ibdata1 and ib_logfiles from the the datadir.

That caused InnoDB to recreate these files.

Best regards,

Heikki

Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL

InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php

- Original Message -
From: ""Rithish Saralaya"" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Friday, February 24, 2006 6:55 AM
Subject: RE: error 1016 : cant open ibd file even though it exists


  Hello.

The tables were working perfectly fine a week back. The database was
created
from a sql file generated through the mysqldump utility. So there was
nothing wrong with the database. This irregularity happened this week
onwards.

Our system admins tell us that the server was restarted last weekend. When
I
dug up the mysql error logs, this was what I found saw.

==
060219  5:20:25  InnoDB: Starting shutdown...
060219  5:20:30  InnoDB: Shutdown completed; log sequence number 19
1867461149
060219  5:20:30 [Note] /usr/sbin/mysqld: Shutdown complete


Memory status:
Non-mmapped space allocated from system: 16589028
Number of free chunks: 10
Number of fastbin blocks: 0
Number of mmapped regions: 19
Space in mmapped regions: 1472028672
Maximum total allocated space: 0
Space available in freed fastbin blocks: 0
Total allocated space: 16479548
Total free space: 109480
Top-most, releasable space: 102224
Estimated memory (with thread stack):1488744676

060219 05:20:30  mysqld ended

060219 16:57:48  mysqld started
060219 16:57:49 [Warning] Asked for 196608 thread stack, but got 126976
InnoDB: The first specified data file /var/lib/mysql/ibdata1 did not
exist:
InnoDB: a new database to be created!
060219 16:57:49  InnoDB: Setting file /var/lib/mysql/ibdata1 size to 10
MB
InnoDB: Database physically writes the file full: wait...
060219 16:57:50  InnoDB: Log file /var/lib/mysql/ib_logfile0 did not
exist:
new to be created
InnoDB: Setting log file /var/lib/mysql/ib_logfile0 size to 256 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200
060219 16:58:02  InnoDB: Log file /var/lib/mysql/ib_logfile1 did not
exist:
new to be created
InnoDB: Setting log file /var/lib/mysql/ib_logfile1 size to 256 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200
060219 16:58:14  InnoDB: Log file /var/lib/mysql/ib_logfile2 did not
exist:
new to be created
InnoDB: Setting log file /var/lib/mysql/ib_logfile2 size to 256 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
060219 16:58:28  InnoDB: Started; log sequence number 0 0
/usr/sbin/mysqld: ready for connections.
Version: '4.1.11-standard-log'  socket: '/var/lib/mysql/mysql.sock'
port:
3306  MySQL Community Edition - Standard (GPL)
=

So... It shows that the ibdata1 file was recreated... But how can that be
possible? when it was a regular server shutdown and startup?

Regards,
Rithish.


-Original Message-
From: Heikki Tuuri [mailt

Re: error 1016 : cant open ibd file even though it exists

[David Logan]

Hi Rithish,

After reading Heikkis points, I am inclined to agree. Did your sysadmins 
change a filesystem during the maintenance? Did they forget to restore a 
directory if they changed disks? What was the maintenance that was 
performed? Your InnoDB files disappeared at some point because the 
server would not have recreated them otherwise. I am sure it was a 
graceful shutdown, but something has changed. These things just don't 
happen by themselves.


Regards

Rithish Saralaya wrote:


The mysql server was shut down for maintenance. However it was a graceful
shutdown and restart. That's all. No files were touched or removed. How
could this have happened, I fail to see.

Regards,
Rithish.


-Original Message-
From: Heikki Tuuri [mailto:[EMAIL PROTECTED]
Sent: Friday, February 24, 2006 3:55 PM
To: mysql@lists.mysql.com
Subject: Re: error 1016 : cant open ibd file even though it exists


Rithish,

from the .err log we see that mysqld was shut down for 12 hours on Feb 19th.
What did the sysadmins do during that time?

There are two plausible explanations:

1) they edited datadir in my.cnf to point to a different location (
/var/lib/mysql),

or

2) they removed ibdata1 and ib_logfiles from the the datadir.

That caused InnoDB to recreate these files.

Best regards,

Heikki

Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL

InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php

- Original Message -
From: ""Rithish Saralaya"" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Friday, February 24, 2006 6:55 AM
Subject: RE: error 1016 : cant open ibd file even though it exists


 


Hello.

The tables were working perfectly fine a week back. The database was
created
from a sql file generated through the mysqldump utility. So there was
nothing wrong with the database. This irregularity happened this week
onwards.

Our system admins tell us that the server was restarted last weekend. When
I
dug up the mysql error logs, this was what I found saw.

==
060219  5:20:25  InnoDB: Starting shutdown...
060219  5:20:30  InnoDB: Shutdown completed; log sequence number 19
1867461149
060219  5:20:30 [Note] /usr/sbin/mysqld: Shutdown complete


Memory status:
Non-mmapped space allocated from system: 16589028
Number of free chunks: 10
Number of fastbin blocks: 0
Number of mmapped regions: 19
Space in mmapped regions: 1472028672
Maximum total allocated space: 0
Space available in freed fastbin blocks: 0
Total allocated space: 16479548
Total free space: 109480
Top-most, releasable space: 102224
Estimated memory (with thread stack):1488744676

060219 05:20:30  mysqld ended

060219 16:57:48  mysqld started
060219 16:57:49 [Warning] Asked for 196608 thread stack, but got 126976
InnoDB: The first specified data file /var/lib/mysql/ibdata1 did not
exist:
InnoDB: a new database to be created!
060219 16:57:49  InnoDB: Setting file /var/lib/mysql/ibdata1 size to 10
MB
InnoDB: Database physically writes the file full: wait...
060219 16:57:50  InnoDB: Log file /var/lib/mysql/ib_logfile0 did not
exist:
new to be created
InnoDB: Setting log file /var/lib/mysql/ib_logfile0 size to 256 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200
060219 16:58:02  InnoDB: Log file /var/lib/mysql/ib_logfile1 did not
exist:
new to be created
InnoDB: Setting log file /var/lib/mysql/ib_logfile1 size to 256 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200
060219 16:58:14  InnoDB: Log file /var/lib/mysql/ib_logfile2 did not
exist:
new to be created
InnoDB: Setting log file /var/lib/mysql/ib_logfile2 size to 256 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Progress in MB: 100 200
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
060219 16:58:28  InnoDB: Started; log sequence number 0 0
/usr/sbin/mysqld: ready for connections.
Version: '4.1.11-standard-log'  socket: '/var/lib/mysql/mysql.sock'
port:
3306  MySQL Community Edition - Standard (GPL)
=

So... It shows that the ibdata1 file was recreated... But how can that be
possible? when it was a regular server shutdown and startup?

Regards,
Rithish.


-Original Message-
From: Heikki Tuuri [mailto:[EMAIL PROTECTED]
Sent: Thursday, February 23, 2006 7:52 PM
To: mysql@lists.mysql.com
Subject: Re: error 1016 : cant open ibd file even though it exists


Rithish,

the table definition does not exist in the ibdata file. You have the
.frm file and the .ibd file, but that does not help if the table
definition is not stored in the ibdata file.

How did you end up in this situation? Did you move .frm and .ibd files
around? Did you recreate the ibdata1 file?

Best r

RE: error 1016 : cant open ibd file even though it exists

[Rithish Saralaya]

The mysql server was shut down for maintenance. However it was a graceful
shutdown and restart. That's all. No files were touched or removed. How
could this have happened, I fail to see.

Regards,
Rithish.


-Original Message-
From: Heikki Tuuri [mailto:[EMAIL PROTECTED]
Sent: Friday, February 24, 2006 3:55 PM
To: mysql@lists.mysql.com
Subject: Re: error 1016 : cant open ibd file even though it exists


Rithish,

from the .err log we see that mysqld was shut down for 12 hours on Feb 19th.
What did the sysadmins do during that time?

There are two plausible explanations:

1) they edited datadir in my.cnf to point to a different location (
/var/lib/mysql),

or

2) they removed ibdata1 and ib_logfiles from the the datadir.

That caused InnoDB to recreate these files.

Best regards,

Heikki

Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL

InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php

- Original Message -
From: ""Rithish Saralaya"" <[EMAIL PROTECTED]>
Newsgroups: mailing.database.myodbc
Sent: Friday, February 24, 2006 6:55 AM
Subject: RE: error 1016 : cant open ibd file even though it exists


> Hello.
>
> The tables were working perfectly fine a week back. The database was
> created
> from a sql file generated through the mysqldump utility. So there was
> nothing wrong with the database. This irregularity happened this week
> onwards.
>
> Our system admins tell us that the server was restarted last weekend. When
> I
> dug up the mysql error logs, this was what I found saw.
>
> ==
> 060219  5:20:25  InnoDB: Starting shutdown...
> 060219  5:20:30  InnoDB: Shutdown completed; log sequence number 19
> 1867461149
> 060219  5:20:30 [Note] /usr/sbin/mysqld: Shutdown complete
>
>
> Memory status:
> Non-mmapped space allocated from system: 16589028
> Number of free chunks: 10
> Number of fastbin blocks: 0
> Number of mmapped regions: 19
> Space in mmapped regions: 1472028672
> Maximum total allocated space: 0
> Space available in freed fastbin blocks: 0
> Total allocated space: 16479548
> Total free space: 109480
> Top-most, releasable space: 102224
> Estimated memory (with thread stack):1488744676
>
> 060219 05:20:30  mysqld ended
>
> 060219 16:57:48  mysqld started
> 060219 16:57:49 [Warning] Asked for 196608 thread stack, but got 126976
> InnoDB: The first specified data file /var/lib/mysql/ibdata1 did not
> exist:
> InnoDB: a new database to be created!
> 060219 16:57:49  InnoDB: Setting file /var/lib/mysql/ibdata1 size to 10
> MB
> InnoDB: Database physically writes the file full: wait...
> 060219 16:57:50  InnoDB: Log file /var/lib/mysql/ib_logfile0 did not
> exist:
> new to be created
> InnoDB: Setting log file /var/lib/mysql/ib_logfile0 size to 256 MB
> InnoDB: Database physically writes the file full: wait...
> InnoDB: Progress in MB: 100 200
> 060219 16:58:02  InnoDB: Log file /var/lib/mysql/ib_logfile1 did not
> exist:
> new to be created
> InnoDB: Setting log file /var/lib/mysql/ib_logfile1 size to 256 MB
> InnoDB: Database physically writes the file full: wait...
> InnoDB: Progress in MB: 100 200
> 060219 16:58:14  InnoDB: Log file /var/lib/mysql/ib_logfile2 did not
> exist:
> new to be created
> InnoDB: Setting log file /var/lib/mysql/ib_logfile2 size to 256 MB
> InnoDB: Database physically writes the file full: wait...
> InnoDB: Progress in MB: 100 200
> InnoDB: Doublewrite buffer not found: creating new
> InnoDB: Doublewrite buffer created
> InnoDB: Creating foreign key constraint system tables
> InnoDB: Foreign key constraint system tables created
> 060219 16:58:28  InnoDB: Started; log sequence number 0 0
> /usr/sbin/mysqld: ready for connections.
> Version: '4.1.11-standard-log'  socket: '/var/lib/mysql/mysql.sock'
> port:
> 3306  MySQL Community Edition - Standard (GPL)
> =
>
> So... It shows that the ibdata1 file was recreated... But how can that be
> possible? when it was a regular server shutdown and startup?
>
> Regards,
> Rithish.
>
>
> -Original Message-
> From: Heikki Tuuri [mailto:[EMAIL PROTECTED]
> Sent: Thursday, February 23, 2006 7:52 PM
> To: mysql@lists.mysql.com
> Subject: Re: error 1016 : cant open ibd file even though it exists
>
>
> Rithish,
>
> the table definition does not exist in the ibdata file. You have the
> .frm file and the .ibd file, but that does not help if the table
> definition is not stored in the ibdata file.
>
> How did you end up in this situation? Did you move .frm and .ibd files
> around? Did you recreate the ibdata1 file?
>
> Best regards,
>
> Heikki
> Oracle Corp./Innobase Oy
> InnoDB - transactions, row level locking, and foreign keys for MySQL
> InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up
> MyISAM tables
> http://www.innodb.com/order.php
>
> .
> List:   mysql
> Subject:error 1016 : cant open ibd file even