Re: Money

[Duncan Hill]

On Tuesday 04 July 2006 21:33, Karl Larsen wrote:
>     I have not yet found how I can put a $ in front of all the money
> columns :-)

Either use concat() or do it in your program that retrieves the data from 
MySQL.  Currency symbols are not something the database should have to store 
for you.
--
Scanned by iCritical.

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

Re: Newbie - CREATE VIEW Question

[Barry]

Rob Desbois schrieb:


To those who responded - read the question.
He wants to combine the values from the data column of *2* rows into one, not 
just a straightforward string concatenation.



Sorry but you want me to write the whole SQL query?

He has to use his brain.

Grouping and joining the tables.

I'm not here for doing your or his work!

Barry

--
Smileys rule (cX.x)C --o(^_^o)
Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o)

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

re[2]: Newbie - CREATE VIEW Question

[Rob Desbois]

> Sorry but you want me to write the whole SQL query?
> He has to use his brain.
> Grouping and joining the tables.
> I'm not here for doing your or his work!

Barry,
I agree that it's often better to point someone in the right direction rather 
than just writing the query for them, but in this case it was a newbie 
question. From where I saw it, the difficulty was in concatenating values from 
2 rows, not the concatenation itself. That is why I thought your response was 
not sufficient.
Apologies if I caused any offense.
--Rob


__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
__

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

Question on mysqldump --single-transaction

[Dominik Klein]

Hi everyone

I'm wondering about the "--single-transaction" option on mysqldump. 
Documentation says

---
This option issues a BEGIN SQL statement before dumping data from the 
server. ...

---
So does this include the entire dump in one transaction? Or is it one 
transaction per database (or even table?)?


I could not find an answer to this in the documentation.

Regards
Dominik

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

Re: Newbie - CREATE VIEW Question

[Barry]

Rob Desbois schrieb:

Sorry but you want me to write the whole SQL query?
He has to use his brain.
Grouping and joining the tables.
I'm not here for doing your or his work!


Barry,
I agree that it's often better to point someone in the right direction rather 
than just writing the query for them, but in this case it was a newbie question.
And therefore it's most important that he tries to learn how to look at 
the doc.


Or your newbies will start asking every shit on List because the don't 
know what to do else.





From where I saw it, the difficulty was in concatenating values from 2 rows, 
not the concatenation itself. That is why I thought your response was not 
sufficient.
Apologies if I caused any offense.

Well it was to be exact concating 2 tables with their rows.
Well your post was also not sufficient, because you didn't helped him at 
all, too.


So what do we learn about this? Nothing. It's internet!

Barry

--
Smileys rule (cX.x)C --o(^_^o)
Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o)

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

Re: UPDATE Based on Relation

[Jesse]

I'm using version 5.?.

Excellent.  That seemed to do the trick.

Thanks,
Jesse

- Original Message - 
From: "Remo Tex" <[EMAIL PROTECTED]>

To: 
Sent: Wednesday, July 05, 2006 2:31 AM
Subject: Re: UPDATE Based on Relation



Jesse wrote:
I need to be able to do an UPDATE based on a relation. The following code 
works in Microsoft SQL.  What is the MySQL Equivalent?


UPDATE Chapters
SET MatSentDate='2006-07-04'
FROM Invoices I JOIN Chapters C ON C.ID=I.ChapterID
JOIN Schools S ON S.ID=C.SchoolID
WHERE I.InvoiceDate >= '2006-01-06' AND I.InvoiceDate <= '2006-01-31' AND 
MatSentDate IS NULL


Thanks,
Jesse

Hi, Jess
What version of mysql you're using? Since v 4.0 (I think) it is possible 
to use query like this:


update t1, t2 set t1.field = t2.value where t1.this = t2.that;

or in your case s.th. like:

UPDATE Invoices I JOIN Chapters C ON C.ID=I.ChapterID
 JOIN Schools S ON S.ID=C.SchoolID
 SET C.MatSentDate='2006-07-04'
 WHERE I.InvoiceDate >= '2006-01-06' AND I.InvoiceDate <= '2006-01-31' AND 
MatSentDate IS NULL


 - for more insight please read for multi-table updates here:
http://dev.mysql.com/doc/refman/5.1/en/update.html
...
 You can also perform UPDATE operations covering multiple tables. However, 
you cannot use ORDER BY or LIMIT with a multiple-table UPDATE. The 
table_references clause lists the tables involved in the join. Its syntax 
is described in Section 13.2.7.1, “JOIN Syntax”. Here is an example:


UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

HTH

--
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]

NDB API Guide

[Stefan Hinz]

Here's some information that's probably interesting for you if you're 
interested in MySQL Cluster ...


As part of the effort of making MySQL Cluster a more mainstream product,
it's been on our agenda for a very long time to rewrite the NDB API
documentation so that it becomes usable by mere mortals. It took nearly
three months to do this, because the new documentation is almost 300
pages long! The MySQL NDB API Guide covers two low-level APIs for
writing applications that work with MySQL's NDB Cluster storage engine:

- The NDB API is an object-oriented application programming interface
  for MySQL Cluster. The API provides ACID-compliant transactions,
  table and row scans that are similar in many ways to SQL cursors, and
  event handling. It also features object-oriented error-handling
  facilities.

- The other API is the MySQL Cluster Management API (MGM API), a
  programming interface intended to provide administrative services for
  the cluster, such as starting and stopping Cluster nodes, Cluster
  logging, backups, and restoration from backups.

All NDB API classes and subclasses are covered in the Guide, as is all
of the MGM API function library. Also provided are a Cluster/NDB API
glossary, class and relationshp diagrams, an index, and many examples.
This new edition of the Guide covers the NDB API as implemented in
MySQL 5.1. It's available for online viewing and download from here:

- http://dev.mysql.com/doc/#ndbapi

Regards,

Stefan
--
Stefan Hinz <[EMAIL PROTECTED]>
MySQL AB Documentation Team Lead. Berlin, Germany (UTC +2:00)
Skype:stefanhinz Cell:+491777841069 Desk:+493082702940 Fax:+493082702941

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

mysqldump: Got errno 27 on write. file too large

[Duhaime Johanne]

I have musql 4.1.7 on Solaris 9, 64 bits and I want to mysqldump a +-4
gigas db.
 
The dump I want to do is from a myISAM tables databank. I used to do
that dump wihtout any problem until recently while the size of the Db
double (+-2 to +-4)
 
The full directory that contains the *.frm, *.MYD,*.MYI files has the
following size: 
>du -ks  /seqdata/mysql/autres_bds/regen
3702719 /seqdata/mysql/autres_bds/regen
 
In my.conf, tmpdir is /seqdata/mysql/tmp.
The dmp file itself is created also on /seqdata/mysql.
Space available: 
mercure{root}142: df -k
Filesystemkbytesused   avail capacity  Mounted on
/dev/dsk/c0t0d0s07156569 6771681  31332396%/
/dev/dsk/c0t0d0s32056211  515444 147908126%/var
/dev/dsk/c2t5d0s02033839 1235855  73696963%/seqweb
/dev/dsk/c0t0d0s46120070 4997223 106164783%/opt
/dev/dsk/c1t5d0s2121640139 67202782 5322095656%/seqdata
/dev/dsk/c2t5d0s534077781 20618437 1345934461%/oracle
/dev/dsk/c2t5d0s6102749585 1677327 100044763 2%/dbs
 

Running the following, I eventually get an error message
mercure{mysql}53: /seqweb/mysql/bin/mysqldump --opt --flush-logs regen >
/seqdata/mysql/myregendump
/seqweb/mysql/bin/mysqldump: Got errno 27 on write
 
and I get the output du -k: 2098184 myregendump
 
this error supposed to be: 
bin/perror 27
Error code  27:  File too large
As you can see I have plenty of space.
 
In  the error file I have multiple times the line:
InnoDB: Error: unable to create temporary file; errno: 2
mercure{mysql}66: bin/perror 2
Error code   2:  No such file or directory
But  the directory exist.
 
Then I tried a verbose mysqldump.
 
mercure{mysql}73: /seqweb/mysql/bin/mysqldump --opt --verbose regen >
/seqdata/mysql/myregendump
-- Connecting to localhost...
-- Retrieving table structure for table cpgisland_Human_May2004...
-- Sending SELECT query...
-- Retrieving rows...
...
 21 tables
-- Retrieving table structure for table unit_occurence_Human_May2004...
-- Sending SELECT query...
-- Retrieving rows...
/seqweb/mysql/bin/mysqldump: Got errno 27 on write
This table is 1 giga data and 500mb index.
 
Then I tried a mysqldump of this table only: 
/seqweb/mysql/bin/mysqldump --opt --verbose regen
unit_occurence_Human_May2004
and it works fine.
 
How can I solve this problem? 
I have looked at the previous message in the forum but could not find
anything answering my problem.
Thank you in advance for any help.
 
 
 
 
Johanne Duhaime
[EMAIL PROTECTED]
tel 5556
local 1565.4, Centre de documentation
 

Re: mysqldump: Got errno 27 on write. file too large

[Dominik Klein]

How can I solve this problem? 


This might be a filesystem problem. Some filesystem (in certain 
configurations) cannot hold files larger than a particular size.


Do you have any files larger than that cut dumpfile on that partition?

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

PBXT version 0.9.5 has been released

[Paul McCullagh]

Hi All,

PBXT version 0.9.5 has been released and can be downloaded from 
http://www.primebase.com/xt.


This version includes a number of major structural changes to the 
implementation in order to improve performance. In addition, all bugs 
reported so far have been fixed. For further information on this 
release, please read my blog, http://pbxt.blogspot.com, and the 
associated comments. Details of all changes made are given in the 
release notes below.


PrimeBase XT (PBXT) is a new transactional database engine for MySQL. 
It has been designed for modern, web-based, high concurrency 
environments. The Beta release of PBXT is planned for September 2006. 
The current version is based on MySQL 4.1.16 and runs on Mac OS X and 
Linux. The next version will include support for the new 5.1 storage 
engine API and 64-bit processors.


Any questions, comments and bug reports can be sent directly to me. 
Thanks for your support!


Best regards,

Paul McCullagh
SNAP Innovation GmbH


PBXT Release Notes
==

--- 0.9.5 - 2006-07-03

RN15: This version concludes the re-structuring of the PBXT 
implementation. I have made a number of major changes, including:


- All files except the transaction logs are now associated with a 
particular table. All table related files begin with the name of the 
table. The extension indicates the function.


- I have merged the handle and the fixed length row data for 
performance reasons.


- Only the variable size component of a row is stored in the data log 
files. As a result the data logs can now be considered as a type of 
"overflow" area.


- Memory mapped files are no longer used because it is not possible to 
flush changes to the disk.


RN14: File names have the following forms:

[table-name]-[table-id].xtr - These files contains the table row 
pointers. Each row pointer occupies 8 bytes and refers to a list of 
records. The file name also contains the table ID. This is a unique 
number which is used internally by XT to identify the table.


[table-name].xtd - This file contains the fixed length data of a table. 
Each data item includes a handle and a record. The handle references a 
record in the data log file if the table contains variable length 
records.


[table-name].xti - This file contains the index data of the table.

[table-name]-[log-id].xtl - This is a data log file. It contains the 
variable length data of the table. A table may have any number of data 
log files, each with a unique ID.


xtlog-[log-id].xt - These files are the transaction logs. Log entries 
that specify updates reference a data file record. Each active thread 
has its own transaction log in order to avoid contension.


RN13: Fixed the bug "Hang on DROP DATABASE". [RESOLVED: 0016]

RN12: PBXT currently only supports the "Serializable" transaction 
isolation level. This is the highest isolation level possible and 
includes the "repeatable-read" functionality [RESOLVED: 0015]. This is 
implemented by giving every transaction a snapshot of the database at 
the point when the transaction is started.


If the transaction tries to update a record that was updated by some 
other transaction after the snapshot was taken, a locked error is 
returned. A deadlock can occur if 2 transactions update the same record 
in a different order. PBXT can detect all deadlocks.


RN11: I have implemented write buffering on the table data files. 
[RESOLVED: 0013]


RN10: The unique constraint (UNIQUE INDEX/PRIMARY KEY) is now checked 
correctly. [RESOLVED: 0008]


RN9: I have implemented a conventional B-tree algorithm for the indices 
(instead of the Lehman and Yoa B*-link tree). Although this reduces 
concurrency it improves the performance of queries significantly 
because of the simplicity of the algorithm. Deletion is also 
implemented in a very simple manner. [RESOLVED: 0007]


RN8: PBXT now has only 2 caches [RESOLVED: 0006]:

The Index Cache (pbxt_index_cache_size): This is the amount of memory 
the PBXT storage engine uses to cache index data and row pointers. This 
is all the data in the files with the extensions '.xti' and '.xtr'. 
This cache is managed in blocks of 2K.


The Record Cache (pbxt_record_cache_size): This is the amount of memory 
the PBXT storage engine uses to cache table row data (handles and 
records). This is all the data in the files with the extension '.xtd'.


The size of the caches are determined by the values of the system 
variables pbxt_index_cache_size and pbxt_row_cache_size. By default 
these values are set to 32MB.


RN7: Auto-increment is now implemented in memory. This is done by doing 
a MAX() select when a table is first opened to get the high value. 
After that, then high value is incremented in memory on INSERT. On 
UPDATE (or INSERT) the value in memory is adjusted if necessary. This 
method also makes it possible for rows to be inserted simultaneously on 
the same table. [RESOLVED: 0005, 0014]


RN6: ./run-all-tests --create-options=TYPE=PBXT succee

re[2]: Newbie - CREATE VIEW Question

[Rob Desbois]

>> I agree that it's often better to point someone in the right direction 
>> rather than just writing the query for them, but in this case it was a 
>> newbie question.
>And therefore it's most important that he tries to learn how to look at 
>the doc.
Remembering my own troubles learning MySQL, it can be difficult to know *what* 
to search for - if this person is completely new to SQL, the concept of joining 
a table to itself might not occur.

>Or your newbies will start asking every shit on List because the don't 
>know what to do else.
I know, it is annoying answering questions just because someone can't be 
bothered.

>> From where I saw it, the difficulty was in concatenating values from 2 rows, 
>> not the concatenation itself. That is why I thought your response was not 
>> sufficient.
>> Apologies if I caused any offense.
>Well it was to be exact concating 2 tables with their rows.
>Well your post was also not sufficient, because you didn't helped him at 
>all, too.

Yes, I know my post didn't answer the question either :) I mailed because I 
thought you might've misunderstood the original question and thought it was 
just about how to concatenate two strings, rather than the more difficult 
joining part.
--Rob


__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
__

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

RE: mysqldump: Got errno 27 on write. file too large

[Duhaime Johanne]

Thank you for your answer.

Yes I have file larger than what mysqldump could manager. Here is an example of 
this. Both files are on the same partition.

mercure{root}54: du -k  mercure.log.jui2006
11948544mercure.log.jui2006

mercure{root}68: du -k myregendump 
2098184 myregendump
Which stop at that size.

Which make me think that mysql is concerned. Or a tmp file but as I mention the 
tmp file has plenty of space.

Best regards

Johanne 

-Message d'origine-
De : Dominik Klein [mailto:[EMAIL PROTECTED] 
Envoyé : Wednesday, 05 July 2006 09:30
À : mysql@lists.mysql.com
Objet : Re: mysqldump: Got errno 27 on write. file too large

> How can I solve this problem? 

This might be a filesystem problem. Some filesystem (in certain
configurations) cannot hold files larger than a particular size.

Do you have any files larger than that cut dumpfile on that partition?

--
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: Newbie - CREATE VIEW Question

[Barry]

Rob Desbois schrieb:

I agree that it's often better to point someone in the right direction rather 
than just writing the query for them, but in this case it was a newbie question.
And therefore it's most important that he tries to learn how to look at 
the doc.

Remembering my own troubles learning MySQL, it can be difficult to know *what* 
to search for - if this person is completely new to SQL, the concept of joining 
a table to itself might not occur.

That's why i gave a hint of where to look.

Or your newbies will start asking every shit on List because the don't 
know what to do else.

I know, it is annoying answering questions just because someone can't be 
bothered.
I don't answer them. It's just annyoning to see the list overflowing 
with posts that had been easily done looking at the docs for a few minutes.



From where I saw it, the difficulty was in concatenating values from 2 rows, 
not the concatenation itself. That is why I thought your response was not 
sufficient.
Apologies if I caused any offense.

Well it was to be exact concating 2 tables with their rows.
Well your post was also not sufficient, because you didn't helped him at 
all, too.


Yes, I know my post didn't answer the question either :) I mailed because I 
thought you might've misunderstood the original question and thought it was 
just about how to concatenate two strings, rather than the more difficult 
joining part.

Well more difficult is relative.

Did not misunderstood.

Barry
--
Smileys rule (cX.x)C --o(^_^o)
Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o)

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

Re: NDB API Guide

[Barry]

Stefan Hinz schrieb:
Here's some information that's probably interesting for you if you're 
interested in MySQL Cluster ...


As part of the effort of making MySQL Cluster a more mainstream product,
it's been on our agenda for a very long time to rewrite the NDB API
documentation so that it becomes usable by mere mortals. It took nearly
three months to do this, because the new documentation is almost 300
pages long! The MySQL NDB API Guide covers two low-level APIs for
writing applications that work with MySQL's NDB Cluster storage engine:

- The NDB API is an object-oriented application programming interface
  for MySQL Cluster. The API provides ACID-compliant transactions,
  table and row scans that are similar in many ways to SQL cursors, and
  event handling. It also features object-oriented error-handling
  facilities.

- The other API is the MySQL Cluster Management API (MGM API), a
  programming interface intended to provide administrative services for
  the cluster, such as starting and stopping Cluster nodes, Cluster
  logging, backups, and restoration from backups.

All NDB API classes and subclasses are covered in the Guide, as is all
of the MGM API function library. Also provided are a Cluster/NDB API
glossary, class and relationshp diagrams, an index, and many examples.
This new edition of the Guide covers the NDB API as implemented in
MySQL 5.1. It's available for online viewing and download from here:

- http://dev.mysql.com/doc/#ndbapi

Regards,

Stefan

Good work :)

Keep it up !

You guys are really doing great ;D

With high regards
Barry

--
Smileys rule (cX.x)C --o(^_^o)
Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o)

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

MySQL service startup time

[Rob Desbois]

Hi,
I have an application self-installer program which also installs MySQL and sets 
it up. This is all on Windows.

I have a problem in that when the installer runs 'net start MySQL', it returns 
immediately but the MySQL daemon is not ready for connections immediately.
As the next step in the installation is to create the application database, I 
need to wait until I can connect.

What's the best way to achieve this? At the moment I have a rather crude 5 
second 'sleep', but that isn't always long enough.
Any ideas?

--Rob


__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
__

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

Re: mysqldump: Got errno 27 on write. file too large

[Michael Stassen]

Dominik Klein wrote:
> This might be a filesystem problem. Some filesystems (in certain
> configurations) cannot hold files larger than a particular size.
>
> Do you have any files larger than that cut dumpfile on that partition?
>

Duhaime Johanne wrote:
> Thank you for your answer.
>
> Yes I have file larger than what mysqldump could manage. Here is an
> example of this. Both files are on the same partition.
>
> mercure{root}54: du -k  mercure.log.jui2006
> 11948544mercure.log.jui2006

Umm, that's only about 1Gb, which makes it smaller than the problem file.  Did 
you mean to show us a different file?


> mercure{root}68: du -k myregendump
> 2098184 myregendump
> Which stop at that size.
>
> Which make me think that mysql is concerned. Or a tmp file but as I
> mention the tmp file has plenty of space.
>
> Best regards
>
> Johanne

My first thought is that Dominik is on the right track.  I get

  ~: perror 27
  OS error code  27:  File too large

which suggests there is some OS limitation.  Perhaps the user running mysqldump 
is limited?  Do you have any larger files owned by the same user?  Can that user 
currently create a file larger than that using another means?


The other possibility would be a bug.  You are using version 4.1.7, which is 
nearly 2 years old now (released October 2004).  The current version is 4.1.20. 
   If you have indeed hit a bug, your best bet would be to upgrade and try 
again.  You should probably at least read the *long* list of bug fixes from 
4.1.7 to 4.1.20 in the MySQL change history in the manual 
.


Michael

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

add users in mysql 5.0 ubuntu dapper drake

[Dimitri Mallis]

hi list

firstly am in the right place to ask this sort of question

i installed mysql 5.0 from the repos on ubuntu dapper drake

i could only go in to mysql when i was root, & then i guess i am
suposed to add users there.

i have tried "man mysql" i might have missed the part on how you add
normal users to write databases

i have downloaded the documentation on the mysql.com site for version 5.0
i followed these steps

5.7.5. How to Run MySQL as a Normal User

mysqld worked as root but not as the normal user, i got a error

[EMAIL PROTECTED]:~$ mysqld
060705 18:55:12 [Warning] Can't create test file
/var/lib/mysql/ubuntu.lower-test
060705 18:55:12 [Warning] One can only use the --user switch if running as root

060705 18:55:12  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'open'.
InnoDB: Cannot continue operation.

i got a bit confused with
shell> chown -R user_name /path/to/mysql/datadir
i think i might have messed up the "/path/to/mysql/datadir" part, i
think i put "/usr/sbin/mysqld"

no luck with that iether. i know i have missed some thing very simple
i just cant see it

any help

thanks

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

Temporary table ERROR 1109 (42S02) where are temporary tables kept?

[Jacob, Raymond A Jr]

I ran the following commands:

USE snort;

CREATE TEMPORARY TABLE sidtemp SELECT cid FROM event WHERE timestamp <
'2006-05-01';
...
SELECT count(*) from sidtemp;
 count(*)
 7501376

DELETE FROM data WHERE data.cid = sidtemp.cid;
ERROR 1109 (42S02): Unkown table 'sidtemp' in where clause


SHOW tables; 

Does not include sidtemp in the list of tables in the snort database nor
would I expect it to.

Question: What database is the table sidtemp in?

r/Raymond

Re: Scheduled Backups Issue...

[cnelson]

> I am attempting to set and and schedule automated MySQL backups 
> using the MySQL Administrator Tool downloaded from mysql.com.  All 
> of my back ups work fien when I perform them manually.  However 
> none of the scheduled backups ever get done.  I am not sure where 
> the glicth is but here is what the details are...
>   
>   
>  I have a remote server (*nx box colocated at an offsite 
> location).  MySQL 5.0.21 is on the box.
>  Logged in as root to the MySQL.
>   
>  Created a Backup Project.
>  Added Schema to it.
>  Under Advanced left all options at default EXCEPT changed to Lock 
> All Tables.
>  Set a local path to save the back ups.
>  Set backup to run daily.
>  Saved and said to use the root as the user to perform the back up.
>   
>  Nada...
>   
>   
>  Please advise if I am being a toal DFU or what.

There's an issue with MySQL Administrator creating bad scheduled items
in 'Windows.  I found a note about it but not a bug report or a fix. 
Try going to the Windows Scheduler, opening the backup item and removing
"-c" (including the quotes) from the middle of the command to run.  (I
found it helpful to copy the command to Notepad, edit it there, and copy
back as it is quite long.)

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

Re: Temporary table ERROR 1109 (42S02) where are temporary tables kept?

[Michael Stassen]

Jacob, Raymond A Jr wrote:


I ran the following commands:

USE snort;

CREATE TEMPORARY TABLE sidtemp SELECT cid FROM event WHERE timestamp <
'2006-05-01';
...
SELECT count(*) from sidtemp;
 count(*)
 7501376

DELETE FROM data WHERE data.cid = sidtemp.cid;
ERROR 1109 (42S02): Unkown table 'sidtemp' in where clause

SHOW tables; 


Does not include sidtemp in the list of tables in the snort database nor
would I expect it to.

Question: What database is the table sidtemp in?

r/Raymond


You have the wrong syntax.  You can't mention a table in the WHERE clause that 
wasn't in the FROM clause.  Try


  DELETE data FROM data JOIN sidtemp ON data.cid = sidtemp.cid;

or

  DELETE data FROM data, sidtemp WHERE data.cid = sidtemp.cid;

See the manual for details .

Michael

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

RE: Temporary table ERROR 1109 (42S02) where are temporary tables kept?

[Jacob, Raymond A Jr]

Thank you,
I was definitely on the wrong track on this one.
I annotated your commands to make sure that I understood what they
were doing. Are my comments correct?
---
You have the wrong syntax.  You can't mention a table in the WHERE
clause that wasn't in the FROM clause.  Try

   DELETE data FROM data JOIN sidtemp ON data.cid = sidtemp.cid;
/* deletes all records in data with cid equal cid in sidtemp but leaves
sidtemp unchanged */

or

   DELETE data FROM data, sidtemp WHERE data.cid = sidtemp.cid;
/* deletes all records in data and sidtemp where cids are equal */

--
/* Will the USING clause work also? */
 or

/* looking that link below: */
DELETE  data FROM data USING data, sidtemp WHERE data.cid =
sidtemp.cid
 or
DELETE  data FROM data USING data, sidtemp JOIN sidtemp ON data.cid
= sidtemp.cid

Is the above correct also?

Thank you,
raymond 

--

See the manual for details
.

Michael
 

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

Dynamic link

[z247]

Say I have two regular tables (table1, table2) and what a column (status) in
the second table to update when it changes in table1. For example, if I set
the status for a user in table1 to "0", the status for all that user's
records in table2 dynamically changes to "0". 

Can this be done? What method is used?

Thank you
-- 
View this message in context: 
http://www.nabble.com/Dynamic-link-tf1896489.html#a5187212
Sent from the MySQL - General forum at Nabble.com.


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

Re: MySQL service startup time

[Jeremy Cole]

Hi Rob,


I have an application self-installer program which also installs
MySQL and sets it up. This is all on Windows.

I have a problem in that when the installer runs 'net start MySQL',
it returns immediately but the MySQL daemon is not ready for
connections immediately. As the next step in the installation is to
create the application database, I need to wait until I can connect.

What's the best way to achieve this? At the moment I have a rather
crude 5 second 'sleep', but that isn't always long enough. Any ideas?


The only way really to handle this is to loop and delay until MySQL
responds to a simple ping like "SELECT 1".

Regards,

Jeremy

--
high performance mysql consulting
www.provenscaling.com

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

RE: Dynamic link

[Jay Blanchard]

[snip]
Say I have two regular tables (table1, table2) and what a column
(status) in
the second table to update when it changes in table1. For example, if I
set
the status for a user in table1 to "0", the status for all that user's
records in table2 dynamically changes to "0". 

Can this be done? What method is used?
[/snip]

http://www.mysql.com/trigger

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

Re: Dynamic link

[Chris White]

On Wednesday 05 July 2006 11:40 am, Jay Blanchard wrote:
> [snip]
> Say I have two regular tables (table1, table2) and what a column
> (status) in
> the second table to update when it changes in table1. For example, if I
> set
> the status for a user in table1 to "0", the status for all that user's
> records in table2 dynamically changes to "0".
>
> Can this be done? What method is used?
> [/snip]
>
> http://www.mysql.com/trigger

Why not a foreign key constraint in this case?  Granted it would be something 
like ON DELETE SET NULL, and  you'd have to verify that the columns were not 
NOT NULL, but the application logic should remain the same with regards to 
that.

-- 
Chris White
PHP Programmer
Interfuel
805.642.2200 x110

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

RE: MySQL service startup time

[Bartis, Robert M (Bob)]

Considering there are a number of pieces that are involved in the connection 
being ready why not create a small program that tries to connect and checks the 
return value. If it fails sleep for 500ms to 1 sec and try again for up to 
X-times before aborting altogether?

Bob

-Original Message-
From: Rob Desbois [mailto:[EMAIL PROTECTED]
Sent: Wednesday, July 05, 2006 11:51 AM
To: mysql@lists.mysql.com
Subject: MySQL service startup time


Hi,
I have an application self-installer program which also installs MySQL and sets 
it up. This is all on Windows.

I have a problem in that when the installer runs 'net start MySQL', it returns 
immediately but the MySQL daemon is not ready for connections immediately.
As the next step in the installation is to create the application database, I 
need to wait until I can connect.

What's the best way to achieve this? At the moment I have a rather crude 5 
second 'sleep', but that isn't always long enough.
Any ideas?

--Rob


__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email 
__

-- 
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: Temporary table ERROR 1109 (42S02) where are temporary tables kept?

[Michael Stassen]

Jacob, Raymond A Jr wrote:

Thank you,
I was definitely on the wrong track on this one.
I annotated your commands to make sure that I understood what they
were doing. Are my comments correct?
---
You have the wrong syntax.  You can't mention a table in the WHERE
clause that wasn't in the FROM clause.  Try

   DELETE data FROM data JOIN sidtemp ON data.cid = sidtemp.cid;
/* deletes all records in data with cid equal cid in sidtemp but leaves
sidtemp unchanged */


Correct.


or

   DELETE data FROM data, sidtemp WHERE data.cid = sidtemp.cid;
/* deletes all records in data and sidtemp where cids are equal */


No, this only deletes from data.  These 2 are supposed to be equivalent.  Using 
this version of the syntax deletes rows from the tables named *before* the 
"FROM".  Tables used to determine the matching rows come after the "FROM".  The 
first version I gave uses an explicit JOIN, the second uses the implicit, comma 
join.  I prefer explicit joins, but I included the implicit join because it 
seemed to be what you were trying.



--
/* Will the USING clause work also? */


It should.


 or

/* looking that link below: */
DELETE  data FROM data USING data, sidtemp WHERE data.cid =
sidtemp.cid
 or
DELETE  data FROM data USING data, sidtemp JOIN sidtemp ON data.cid
= sidtemp.cid

>
> Is the above correct also?

Almost.  In the USING form, the tables which should lose rows go after "FROM", 
while the tables used to make the selection go after "USING".  Hence, the query 
would be


  DELETE FROM data USING data JOIN sidtemp ON data.cid = sidtemp.cid;

or

  DELETE FROM data USING data, sidtemp WHERE data.cid = sidtemp.cid;

In general, the implicit join syntax (comma between tables with join 
condition(s) in the WHERE clause) seems easy to use, but frequently leads to 
trouble.  The explicit join syntax (table JOIN table ON condition) is much 
clearer, which should help avoid mistakes.  You should probably read the manual 
page describing JOIN syntax .



Thank you,
raymond 


Michael

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

RE: Temporary table ERROR 1109 (42S02) where are temporary tables kept?

[Jacob, Raymond A Jr]

 Thanks again,
 raymond


-Original Message-
From: Michael Stassen [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, July 05, 2006 14:54
To: Jacob, Raymond A Jr
Cc: mysql@lists.mysql.com
Subject: Re: Temporary table ERROR 1109 (42S02) where are temporary
tables kept?

Jacob, Raymond A Jr wrote:
> Thank you,
> I was definitely on the wrong track on this one.
> I annotated your commands to make sure that I understood what they 
> were doing. Are my comments correct?
> ---
> You have the wrong syntax.  You can't mention a table in the WHERE 
> clause that wasn't in the FROM clause.  Try
> 
>DELETE data FROM data JOIN sidtemp ON data.cid = sidtemp.cid;
> /* deletes all records in data with cid equal cid in sidtemp but 
> leaves sidtemp unchanged */

Correct.

> or
> 
>DELETE data FROM data, sidtemp WHERE data.cid = sidtemp.cid;
> /* deletes all records in data and sidtemp where cids are equal */

No, this only deletes from data.  These 2 are supposed to be equivalent.
Using this version of the syntax deletes rows from the tables named
*before* the "FROM".  Tables used to determine the matching rows come
after the "FROM".  The first version I gave uses an explicit JOIN, the
second uses the implicit, comma join.  I prefer explicit joins, but I
included the implicit join because it seemed to be what you were trying.

> --
> /* Will the USING clause work also? */

It should.

>  or
> 
> /* looking that link below: */
> DELETE  data FROM data USING data, sidtemp WHERE data.cid = 
> sidtemp.cid
>  or
> DELETE  data FROM data USING data, sidtemp JOIN sidtemp ON 
> data.cid = sidtemp.cid
 >
 > Is the above correct also?

Almost.  In the USING form, the tables which should lose rows go after
"FROM", while the tables used to make the selection go after "USING".
Hence, the query would be

   DELETE FROM data USING data JOIN sidtemp ON data.cid = sidtemp.cid;

or

   DELETE FROM data USING data, sidtemp WHERE data.cid = sidtemp.cid;

In general, the implicit join syntax (comma between tables with join
condition(s) in the WHERE clause) seems easy to use, but frequently
leads to trouble.  The explicit join syntax (table JOIN table ON
condition) is much clearer, which should help avoid mistakes.  You
should probably read the manual page describing JOIN syntax
.

> Thank you,
> raymond

Michael

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

RE: Dynamic link

[z247]

Is this the only option? I get Access denied.

CREATE TRIGGER ins_sum BEFORE INSERT ON account
FOR EACH
ROW SET @sum = @sum + NEW.amount;

MySQL said: Documentation
#1227 - Access denied; you need the SUPER privilege for this operation

This is sample code from mysql site,
http://dev.mysql.com/doc/refman/5.1/en/using-triggers.html

Thank you
-- 
View this message in context: 
http://www.nabble.com/Dynamic-link-tf1896489.html#a5188173
Sent from the MySQL - General forum at Nabble.com.


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

Re: Dynamic link

[z247]

How would a foreign key constraint work in this case?

Thank you
-- 
View this message in context: 
http://www.nabble.com/Dynamic-link-tf1896489.html#a5188192
Sent from the MySQL - General forum at Nabble.com.


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

RE: MS Access gives error no. -7776.

[Duzenbury, Rich]

> -Original Message-
> From: Daniel Kasak [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, July 04, 2006 5:45 PM
> To: C K; mysql@lists.mysql.com
> Subject: Re: MS Access gives error no. -7776.
> 
> C K wrote:
> 
> > Thanks for your sugesstions. I tried to search this issue on
> > microsoft's website. and found that this problem is related with
> > timestamp fields. I have tried to connect from access 2k and 2003 to
> > mysql database. It works well for all the things. but gives above
> > error only when control jumps to subform with diff. table as it's
> > recordsource. Strange thing is that on few PCs it gives error and on
> > some it not gives any error. I have installed WinXP with SP2, Access
> > 2003 with JET 4 sp 8. Also this error does not occurs when I used
SQL
> > server 2005 express  edition
> > I am tring to use seperate forms for dataentry. Thanks again
> > CPK
> > On 7/3/06, Duzenbury, Rich <[EMAIL PROTECTED]> wrote:
> 
> Contrary to the advice given on working with MS Access, I've found
that
> I can *only* get things to work with Access 2003 if I remove the
> timestamp field.
> Also, make sure your primary key column isn't larger than an int ( ie
> don't use int unsigned, and don't use bigint ).

I recently converted an application with 1M+ records to use mysql as the
backend.  

I had similar errors until I:

Make sure every table haa a unique key field
Convert all timestamp fields to datetime (via alter table)
Add one (and only one) last_changed timestamp, and fill it with a valid
stamp
Null out datetime fields that have '-00-00 00:00:00' as their value.
Refresh the ODBC links.

Access likes to use the primary key field + the last_changed timestamp
in it's update queries so that it can easily manage optimistic locking.
It issues update queries like

Update some_table ... where primary_key = 27 and last_changed =
'2006-12-01 23:52:55'.

If the query doesn't affect any rows, then Access knows that someone
else changed the record since it was initially retrieved, and can then
prompt the user accordingly.

Access also throws errors when it finds that timestamp or datetime
fields have '-00-00 00:00:00'.  Immediately after I loaded the mysql
tables with the access data, I ran an 'update $full_table set
$full_column = NULL where $full_column = '-00-00 00:00:00' on every
datetime column in the database.

In the case of the special last_changed column, I set all records to
'1980-01-01 00:00:00' immediately following the conversion.  Since this
column was added by the conversion, it is never referenced in any forms,
which seems to help things.  

The bad news is that all of the automatic date fields that were in the
original .mdb file now have to be filled in with 'now()' in all sorts of
places like forms and insert queries.

I also had some trouble where a data entry subform would not allow
additions.  The 'add record' part of the record navigator would be
grayed out.  Usually, this was because I had neglected to have a primary
key + last_changed timestamp on the table in question.  In some cases,
it was because a data source for a subform was a query.  What I did to
get those to work was to change the datasource to the table, and supply
a proper filter/link.

That aside, I feel much better having the data backed by mysql.  

The application has now been running successfully for about three weeks
and users do not notice any difference in performance.  Our regular
backup capability now encompasses backup and restore for the data, which
is great for us.

Regards,

Rich


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

Re: Dynamic link

[Chris White]

On Wednesday 05 July 2006 12:26 pm, z247 wrote:
> How would a foreign key constraint work in this case?

An example of a foreign key constraint would be something like:

CREATE TABLE users (
  id BIGINT UNSIGNED NOT NULL UNIQUE,
  username VARCHAR NOT NULL,
  password VARCHAR NOT NULL,
  status INT UNSIGNED,
  PRIMARY KEY (id),
   CONSTRAINT foreign_user_status FOREIGN KEY foreign_user_status (status)
REFERENCES user_status (id)
ON DELETE SET NULL
);

another option is to have it as an ALTER TABLE statement:

ALTER TABLE users ADD FOREIGN KEY(foreign_user_status) 
REFERENCES user_status ( id );
-- 
Chris White
PHP Programmer/DBoo
Interfuel

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

Re: Show tables replacement

[Ed Reed]

Anyone have an idea on this?
 
Thanks

>>> "Ed Reed" <[EMAIL PROTECTED]> 6/30/06 2:51:44 PM >>>
Opps!

Thanks for the quick reply, Dan. But I forgot to mention I'm running MySQL 
4.1.11

Any other suggestions?

Thanks again.

>>> Dan Nelson < [EMAIL PROTECTED] > 6/30/06 2:48:57 PM >>>
In the last episode (Jun 30), Ed Reed said:
> Is there a Select statement I can use to get table names so that I
> could use other Select statement syntax on the results?
> 
> What I'd like to do is this,
> 
> SHOW Replace(TABLES, 'tbl','') Like 'tbl%';
> 
> But this doesn't work so I need a Select statement that can do the
> same thing.

SELECT table_name FROM information_schema.tables;

-- 
Dan Nelson
[EMAIL PROTECTED] 

automatic timestamp

[Nestor]

People,

I have the following table:
DROP TABLE IF EXISTS bid;
CREATE TABLE bid (
 bid_id int(11) NOT NULL auto_increment,
 bid_proj_name varchar(100) NOT NULL default '',
 bid_prop_name varchar(100) NOT NULL default '',
 bid_amount varchar(20) NULL default '',
 bid_sub_name varchar(100) NOT NULL default '',
 bid_sub_desc varchar(100) default '',
 bid_sub_amount varchar(20) NULL default '',
 bid_winner tinyint(1) NOT NULL default '0',
 bid_date TIMESTAMP,
 PRIMARY KEY  (bid_id),
 UNIQUE KEY proj_prop (bid_proj_name,bid_prop_name)
) TYPE=MyISAM;


and I am trying toload a file with the following data:
,Construction Management,RCG Consulting,,Orly-Belle
,Construction Management,RCG Consulting,,American Engineers

I am using phpmyadmin and the date always gets enter as 00

What do I need to do to force the current time stamp?
I am using mysql version 4.0.21-standard

Thanks,

Néstor :-)

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

Re: automatic timestamp

[Scott Haneda]

> People,
> 
> I have the following table:
> DROP TABLE IF EXISTS bid;
> CREATE TABLE bid (
>   bid_id int(11) NOT NULL auto_increment,
>   bid_proj_name varchar(100) NOT NULL default '',
>   bid_prop_name varchar(100) NOT NULL default '',
>   bid_amount varchar(20) NULL default '',
>   bid_sub_name varchar(100) NOT NULL default '',
>   bid_sub_desc varchar(100) default '',
>   bid_sub_amount varchar(20) NULL default '',
>   bid_winner tinyint(1) NOT NULL default '0',
>   bid_date TIMESTAMP,
>   PRIMARY KEY  (bid_id),
>   UNIQUE KEY proj_prop (bid_proj_name,bid_prop_name)
> ) TYPE=MyISAM;
> 
> 
> and I am trying toload a file with the following data:
> ,Construction Management,RCG Consulting,,Orly-Belle
> ,Construction Management,RCG Consulting,,American Engineers
> 
> I am using phpmyadmin and the date always gets enter as 00
> 
> What do I need to do to force the current time stamp?
> I am using mysql version 4.0.21-standard

IIRC, you need two timestamp fields for auto to work, default timestamps for
the first timestamp column will be ignored, but the first column will get
auto timestamp in a table that has more than one timestamp column.

I usually set up two, updated and added, and I always set the added field to
NOW()

It explains it pretty well in the docs here
http://dev.mysql.com/doc/refman/4.1/en/timestamp-4-1.html
-- 
-
Scott HanedaTel: 415.898.2602
 Novato, CA U.S.A.



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

Re: automatic timestamp

[Chris]

Nestor wrote:

People,

I have the following table:
DROP TABLE IF EXISTS bid;
CREATE TABLE bid (
 bid_id int(11) NOT NULL auto_increment,
 bid_proj_name varchar(100) NOT NULL default '',
 bid_prop_name varchar(100) NOT NULL default '',
 bid_amount varchar(20) NULL default '',
 bid_sub_name varchar(100) NOT NULL default '',
 bid_sub_desc varchar(100) default '',
 bid_sub_amount varchar(20) NULL default '',
 bid_winner tinyint(1) NOT NULL default '0',
 bid_date TIMESTAMP,
 PRIMARY KEY  (bid_id),
 UNIQUE KEY proj_prop (bid_proj_name,bid_prop_name)
) TYPE=MyISAM;


and I am trying toload a file with the following data:
,Construction Management,RCG Consulting,,Orly-Belle
,Construction Management,RCG Consulting,,American Engineers

I am using phpmyadmin and the date always gets enter as 00

What do I need to do to force the current time stamp?
I am using mysql version 4.0.21-standard


Don't specify the bid_date as an empty string - ie take the last "," 
from the end. The empty string is being converted to '00' because 
it's an invalid date.


If you don't specify that column, it will default to 'now()'.

mysql> insert into bid(bid_proj_name) values ('test');
Query OK, 1 row affected (0.00 sec)

mysql> insert into bid(bid_proj_name, bid_date) values ('test 2', '');
Query OK, 1 row affected (0.01 sec)

mysql> select bid_id, bid_proj_name, bid_date from bid;
++---++
| bid_id | bid_proj_name | bid_date   |
++---++
|  1 | test  | 20060706110203 |
|  2 | test 2| 00 |
++---++
2 rows 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: Question on mysqldump --single-transaction

[Chris]

Dominik Klein wrote:

Hi everyone

I'm wondering about the "--single-transaction" option on mysqldump. 
Documentation says

---
This option issues a BEGIN SQL statement before dumping data from the 
server. ...

---
So does this include the entire dump in one transaction? Or is it one 
transaction per database (or even table?)?


I could not find an answer to this in the documentation.


The easiest way to find out?

create database blah;
create table blah1(id int);
create table blah2(id int);

see what mysqldump --single-transaction does and post a comment on the 
mysqldump page :)


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

space usage

[Martin Jespersen]

Does anyone havea  clue of how mysql optimizes empty fields and how 
query speed is affected?


i have a  db with around 3 million rows where i need to add 2 new fields 
- one smallint and one varchar(10)


for alot of the rows they will be empty, but because of query speed i 
opt to put them ion the maintable instead of a seperate table so i can 
avoid slow joins.


what will be better for queryspeed/size: adding them with NULL using 
NULL as default or with NOT NULL using 0 and '' as defaults?


Regards

Martin Jespersen


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

Re: mysqldump: Got errno 27 on write. file too large

[Greg 'groggy' Lehey]

On Wednesday,  5 July 2006 at  9:12:52 -0400, Duhaime Johanne wrote:
> I have musql 4.1.7 on Solaris 9, 64 bits and I want to mysqldump a +-4
> gigas db.
>
> ...
>
> The full directory that contains the *.frm, *.MYD,*.MYI files has the
> following size:
>> du -ks  /seqdata/mysql/autres_bds/regen
> 3702719 /seqdata/mysql/autres_bds/regen
> ... I get the output du -k: 2098184 myregendump
>
> this error supposed to be:
> bin/perror 27
> Error code  27:  File too large
> As you can see I have plenty of space.

Error codes below 128 come from the kernel.  It's possible for
applications to return error numbers in this range too, but it's not a
good idea, and mysqldump doesn't do it.  So whatever's happening here,
it's being reported by the kernel.

There are two numbers:

#define EFBIG   27  /* File too large */
#define ENOSPC  28  /* No space left on device */

EFBIG refers to limitations in the size of one file; you can get it
even if there's plenty of space in the file system.  ENOSPC is the
other way round: you can get it even if the file isn't at its maximum
allowed size.

> In  the error file I have multiple times the line:
> InnoDB: Error: unable to create temporary file; errno: 2
> mercure{mysql}66: bin/perror 2
> Error code   2:  No such file or directory
> But  the directory exist.

I'd guess that it doesn't.  Unfortunately the message doesn't tell you
which file it's trying to create.  This might be worth a bug report,
since it seriously hinders you in finding out what that particular
problem is.

Why does this not show up in your verbose mysqldump?

>
> Then I tried a verbose mysqldump.
>
> mercure{mysql}73: /seqweb/mysql/bin/mysqldump --opt --verbose regen >
> /seqdata/mysql/myregendump
> -- Connecting to localhost...
> -- Retrieving table structure for table cpgisland_Human_May2004...
> -- Sending SELECT query...
> -- Retrieving rows...
> ...
>  21 tables
> -- Retrieving table structure for table unit_occurence_Human_May2004...
> -- Sending SELECT query...
> -- Retrieving rows...
> /seqweb/mysql/bin/mysqldump: Got errno 27 on write
> This table is 1 giga data and 500mb index.

Note that mysqldump is not very efficient in its format.  How big was
the output file when it failed?  I'd hazard a guess at 2 GB
(specifically, 2147483647 bytes).  If this is the case, it's
definitely a file system limitation.

> Then I tried a mysqldump of this table only:
> /seqweb/mysql/bin/mysqldump --opt --verbose regen
> unit_occurence_Human_May2004
> and it works fine.
>
> How can I solve this problem?

Well, you've found one workaround :-) 

What file system are you using?  Could this be (Sun's old) UFS?

I'm sure that Sun has file systems that aren't limited to 2 GB; you
could use one of them.  They'll probably give you other advantages
too.

> I have looked at the previous message in the forum but could not
> find anything answering my problem.

I'm relatively confident that this isn't a mysqldump problem.

On Wednesday,  5 July 2006 at 12:28:53 -0400, Michael Stassen wrote:
>
> My first thought is that Dominik is on the right track.  I get
>
>> : perror 27
>   OS error code  27:  File too large
>
> which suggests there is some OS limitation.  Perhaps the user
> running mysqldump is limited?  Do you have any larger files owned by
> the same user?  Can that user currently create a file larger than
> that using another means?

Yes, this could be a disk quota issue.

> The other possibility would be a bug.  You are using version 4.1.7, which
> is nearly 2 years old now (released October 2004).  The current version is
> 4.1.20. If you have indeed hit a bug, your best bet would be to upgrade
>and try again.  You should probably at least read the *long* list of bug
> fixes from 4.1.7 to 4.1.20 in the MySQL change history in the manual
> .

While it's my duty not to stand up and say "it's not a mysqldump bug",
I'd be very surprised in this case--see above for reasoning.

Greg
--
Greg Lehey, Senior Software Engineer, Online Backup
MySQL AB, http://www.mysql.com/
Echunga, South Australia
Phone: +61-8-8388-8286   Mobile: +61-418-838-708
VoIP:  sip:[EMAIL PROTECTED], sip:[EMAIL PROTECTED]
Diary http://www.lemis.com/grog/diary.html

Are you MySQL certified?  http://www.mysql.com/certification/


pgpu1arScIahf.pgp
Description: PGP signature

Re: add users in mysql 5.0 ubuntu dapper drake

[Chris]

Dimitri Mallis wrote:

hi list

firstly am in the right place to ask this sort of question

i installed mysql 5.0 from the repos on ubuntu dapper drake

i could only go in to mysql when i was root, & then i guess i am
suposed to add users there.


You've got things confused.

'mysql' the service, you can only run as the root user. So

/etc/init.d/mysqld

you can only run as the system root user (ie you do "su -" or log in as 
"root").



To create users inside mysql, read this page:
http://dev.mysql.com/doc/refman/5.1/en/create-user.html

--
Postgresql & php tutorials
http://www.designmagick.com/

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

Re: Dynamic link

[Chris]

z247 wrote:

Is this the only option? I get Access denied.

CREATE TRIGGER ins_sum BEFORE INSERT ON account
FOR EACH
ROW SET @sum = @sum + NEW.amount;

MySQL said: Documentation
#1227 - Access denied; you need the SUPER privilege for this operation


Read the error message.

You don't have "super" privilege for your mysql user.

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

binary select - case sensitive

[kalin mintchev]

hi all...

i found this on the mysql dev manual site:
"MySQL 4 and later string comparisons, including DISTINCT, aren't case
sensitive unless the field is declared as BINARY or you use BINARY in your
comparison."

so here i tried it but no good. any ideas?! the field is not declared
binary but i do have 'binery' in my select. is it in the wrong place?!

thanks

mysql> select binary code from promo_codes where code = "USAW2umHv7De";
+--+
| binary code  |
+--+
| USAW2umHv7De |
+--+
1 row in set (0.02 sec)

mysql> select binary code from promo_codes where code = "USAW2umHv7DE";
+--+
| binary code  |
+--+
| USAW2umHv7De |
+--+
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: binary select - case sensitive

[Chris]

kalin mintchev wrote:

hi all...

i found this on the mysql dev manual site:
"MySQL 4 and later string comparisons, including DISTINCT, aren't case
sensitive unless the field is declared as BINARY or you use BINARY in your
comparison."

so here i tried it but no good. any ideas?! the field is not declared
binary but i do have 'binery' in my select. is it in the wrong place?!


Check the comments on this page:

http://dev.mysql.com/doc/refman/5.1/en/charset-binary-op.html

It has some good examples.


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

Re: binary select - case sensitive

[kalin mintchev]

>
> http://dev.mysql.com/doc/refman/5.1/en/charset-binary-op.html
>
> It has some good examples.

got it thanks...

>
>



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

special char in full-text search

[Harini Raghavan]

Hi All,

I am using the MySQL full text search capability in the search workflow 
in my appplication. I found that MySQL treats special character like 
*./,* etc. as tokenizers if they are not specified within a 
phrase(inside double quotes). For ex. If the search string entered is 
M.B.A or 24/7 then it searches for M, B, and A sepearately. I can't 
include the quotes for all search strings as wild char(*) search would 
not work when specified within a phrase. Is there a way to avoid MySQL 
from tokenizing the search criteria?

Any ideas?

Thanks,
Harini

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