Re: Can I dissable transactions?
Jeff, Thanks for your idea. Deleting data x rows at a time would certainly help, if AUTOCOMMIT=TRUE. But I have no idea how :) I have tried: DELETE FROM very_large_table WHERE delete_flag=1 LIMIT 10; But the LIMIT is not understood (4.1.8). Unfortunately my 'delete_flag' is not key. Therefore repeated attempts at deleting small amounts of data is very slow. The fastest way seem to be to dump the data, edit the file, and re-insert the data. But you have given my my solution: If I cannot disable transactions, I'll have to work with one of the keys and iterate through that key bit bit. So thanks, I'll go off and give it a go... Ben. Jeff Smelser wrote: On Monday 14 February 2005 03:52 am, Ben Clewett wrote: I am having a lot of problems deleting a large amount of data. Say 20GB from a 40GB table. I seem to get failure quite a lot (due NOT to mysql, but bad hardware), then MySQL roles back the transaction, which takes as many hours and starting the transaction. I also get this a lot: There is a feature of DB2 that can do this.. Its really not always all its cracked up to be.. In this case, it would happily delete, if something goes wrong, your table is now marked bad.. The other 20million rows are now gone.. Is that what you want? What you need to do, is set up a simple script to delete 20,000 rows a time, and commit, just keep doing it till its done.. This way you could do 20,000 rows, wait a bit, do it again. or whatever. If it fails, you only rollback what it was doing during the transaction and you wont have to start all over. Jeff -- Ben Clewett Road Tech Computer System Ltd [EMAIL PROTECTED] http://www.roadrunner.uk.com +44(0)1923 46 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL on AIX 5.2
Dear user, Slightly off topic, but knowing that there seems to be somebody here who knows anything, I need advise from an AIX expert :) Trying to run MySQL 4.1.8 on AIX 5.2.0.0. Finding it cannot malloc more than about 90 MB for whole MySQL. Sample error: 050215 8:44:41 InnoDB: Fatal error: cannot allocate 536887296 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 27043112 bytes. Operating system errno: 12 InnoDB: Cannot continue operation! The machine has 4GB available. My limits in /etc/security/limits are unlimited: mysql: rss = -1 rss_hard = -1 stack = -1 stack_hard = -1 data = -1 data_hard = -1 I would be extremely interested if some AIX expert could advise me what is going on? Kind regards, Ben Clewett. -- Ben Clewett Road Tech Computer System Ltd [EMAIL PROTECTED] http://www.roadrunner.uk.com +44(0)1923 46 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Time in VBA for Excel
Hey Dan,
Thanks for the post and the code ideas. Unfortuantely I still can't get the
thing to work even when trying the CONCAT and CAST functions.
As per your request, here is the details of what I have got so far:
I'm running MySQL 4.1.9 with MyODBC 3.51. The table in question is the Users
table which has been setup as follows
USERS
=
UserID INT(11) NOT NULL AUTO_INCREMENT,
CompanyCode TEXT NOT NULL,
AssessmentCode TEXT NOT NULL,
UserCode TEXT NOT NULL,
Profession INT(5) NOT NULL DEFAULT '0',
Category INT(5) NOT NULL DEFAULT '0',
Username VARCHAR(10),
Password VARCHAR(10),
AssessmentDate DATE,
StartTime TIME,
EndTime TIME,
Completion ENUM('Y','N') NOT NULL DEFAULT 'N',
The sample data I'm working with is:
UserID :: 1
CompanyCode :: BEC-24ECMQP-TFXCFDOY-290105
AssessmentCode :: BEA-24ECMQP-O701-290105
UserCode :: BEU-24ECMQP-XR01-290105
Profession :: 12
Category :: 2
Username :: BEU1
Password :: password
AssessmentDate :: 2005-03-05
StartTime :: 12:33:59
EndTime :: 14:33:24
Completion :: Y
Here is my VBA Code that aims to get the data from the database and insert
it into a worksheet. I'm pretty new to VBA coding so please excuse any bad
form
==
Sub UserData()
Dim calcMode, updateMode
Dim ws As Worksheet
Dim conn As ADODB.Connection
Dim rec As New ADODB.Recordset
Dim qtTarget$, sqlQuery$, strAssCode$, strCoCode$
Dim i, intUsrCount%
calcMode = Application.Calculation
updateMode = Application.ScreenUpdating
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
strCoCode = ThisWorkbook.Worksheets(AssmntInfo).Range(C8).Value
strAssCode = ThisWorkbook.Worksheets(AssmntInfo).Range(M8).Value
Set ws = ThisWorkbook.Worksheets(UserInfo)
qtTarget = ws.Names(UsrList).RefersToLocal
Set conn = OpenDatabase
If conn Is Nothing Then Exit Sub
sqlQuery = SELECT ud.UserCode, ct.CategoryDesc, ud.StartTime,
ud.EndTime, TIMEDIFF(ud.EndTime,ud.StartTime) AS TotalTime _
FROM Users AS ud _
RIGHT JOIN Categories AS ct ON ud.Category = ct.CategoryID _
WHERE ud.AssessmentCode = ' strAssCode ' AND ud.CompanyCode = '
strCoCode ' _
ORDER BY ud.UserID ASC
rec.Open sqlQuery, conn
With ws
.Range(qtTarget).ClearContents
i = 7
intUsrCount = 1
While Not rec.EOF
.Cells(i, 2) = intUsrCount
.Cells(i, 3) = rec!UserCode
.Cells(i, 4) = rec!CategoryDesc
.Cells(i, 5) = rec!StartTime
.Cells(i, 6) = rec!EndTime
.Cells(i, 7) = rec!TotalTime
i = i + 1
intUsrCount = intUsrCount + 1
rec.MoveNext
Wend
intUsrCount = intUsrCount - 1
.Range(J6) = intUsrCount
.Activate
.Cells(7, 2).CurrentRegion.Select
.Names.Add Name:=UsrList, RefersTo:== + Selection.Address
End With
ThisWorkbook.Worksheets(AssmntInfo).Cells(18, 13) = intUsrCount
rec.Close
conn.Close
Application.Calculation = calcMode
Application.ScreenUpdating = updateMode
Application.Calculate
End Sub
Function OpenDatabase() As Connection
Const DBS$ = DSN=Assessment; _
Uid=username; _
Pwd=password;
Dim conn As ADODB.Connection
On Error Resume Next
Set conn = New ADODB.Connection
With conn
.CursorLocation = adUseClient
.Open DBS
End With
If Err 0 Then
MsgBox REPORT GRAPH GENERATOR ERROR: _
vbCrLf Could not connect to database. The report graph processing
will be stopped.
Exit Function
End If
Set OpenDatabase = conn
End Function
==
Any further ideas or help will be greatly appreciated.
Kind Regards
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
last_insert_id
Hi! I have a database where several users can connect and input data. I managed to have my insert queries as atomic, but I was wondering about one special case: I make one insert, and retrieve the last id inserted by mysql because I need to update an other table with that id. - if one user inserts and retrieves the id, but between both one other has inserted something the id returned will be the right one? - or do I have to lock my table, execute the query, retrieve the id, unlock the table? - is there a way to make an atomic query with this that would avoid me to use locks? Thanks a lot for any help, Melissa _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL on AIX 5.2
Forget the question, I have found my answer, thanks! The answer is in MySQL manual, section 2.12.5.3. Seems AIX default to max 256MB memory without compilation options. Then a max of 2GB is possible. Ben Ben Clewett wrote: Dear user, Slightly off topic, but knowing that there seems to be somebody here who knows anything, I need advise from an AIX expert :) Trying to run MySQL 4.1.8 on AIX 5.2.0.0. Finding it cannot malloc more than about 90 MB for whole MySQL. Sample error: 050215 8:44:41 InnoDB: Fatal error: cannot allocate 536887296 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 27043112 bytes. Operating system errno: 12 InnoDB: Cannot continue operation! The machine has 4GB available. My limits in /etc/security/limits are unlimited: mysql: rss = -1 rss_hard = -1 stack = -1 stack_hard = -1 data = -1 data_hard = -1 I would be extremely interested if some AIX expert could advise me what is going on? Kind regards, Ben Clewett. -- Ben Clewett Road Tech Computer System Ltd [EMAIL PROTECTED] http://www.roadrunner.uk.com +44(0)1923 46 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: last_insert_id
Hi, You can probably use SELECT LAST_INSERT_ID() which keeps auto-increment values on a per connection basis. See: http://dev.mysql.com/doc/mysql/en/getting-unique-id.html /Johan mel list_php wrote: Hi! I have a database where several users can connect and input data. I managed to have my insert queries as atomic, but I was wondering about one special case: I make one insert, and retrieve the last id inserted by mysql because I need to update an other table with that id. - if one user inserts and retrieves the id, but between both one other has inserted something the id returned will be the right one? - or do I have to lock my table, execute the query, retrieve the id, unlock the table? - is there a way to make an atomic query with this that would avoid me to use locks? Thanks a lot for any help, Melissa _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: last_insert_id
mel list_php [EMAIL PROTECTED] wrote on 15/02/2005 10:18:55: Hi! I have a database where several users can connect and input data. I managed to have my insert queries as atomic, but I was wondering about one special case: I make one insert, and retrieve the last id inserted by mysql because I need to update an other table with that id. - if one user inserts and retrieves the id, but between both one other has inserted something the id returned will be the right one? - or do I have to lock my table, execute the query, retrieve the id, unlock the table? - is there a way to make an atomic query with this that would avoid me to use locks? last_insert_id is on a per-connection basis i.e. it gives the last id inserted using that particular connection. Therefore you will always get the most recent ID that you inserted, not the most recent that anyone inserted. I think, therefore, that the natural behaviour is what you want. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
instable behaviour of mysql
Hi turns me crazy: no reply button found in your ... mailing-list. Hello. Please send us an ouput of the following statement, which you should perform in the middle of your test case: show grants for current_user(); THIS IS THE OUTPUT: DROP TABLE fai_accounts DROP TABLE fai_contacts DROP TABLE fai_knowledge DROP TABLE fai_tasks DROP TABLE fai_timer DELETE FROM participants WHERE ident = 'fai' DELETE FROM part_val WHERE ident = 'fai' UPDATE SET activ = 'inactiv' WHERE ident = 'fai' Query 'UPDATE SET activ = 'inactiv' WHERE ident = 'fai'' failed. MySQL Error Number: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET activ = 'inactiv' WHERE ident = 'fai'' at line 1 REVOKE all ON fai_accounts FROM 'fai'@'mydom.tld' MySQL Error Number: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET activ = 'inactiv' WHERE ident = 'fai'' at line 1 show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE all ON fai_contacts FROM 'fai'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE all ON fai_knowledge FROM 'fai'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE all ON fai_tasks FROM 'fai'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE all ON fai_timer FROM 'fai'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE all ON fai_files FROM 'fai'@'mydom.tld' Query 'REVOKE all ON fai_files FROM 'fai'@'mydom.tld'' failed. MySQL Error Number: 1147: There is no such grant defined for user 'fai' on host 'mydom.tld' on table 'fai_files' show grants for current_user() MySQL Error Number: 1147: There is no such grant defined for user 'fai' on host 'mydom.tld' on table 'fai_files' Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE select ON participants FROM 'fai'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE select,update ON part_val FROM 'fai'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE GRANT OPTION ON *.* FROM 'fai'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION DROP USER 'fai'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION and after that i get [EMAIL PROTECTED]:~ mysql -h mydom.tld -u root -p Enter password: ERROR 1130 (0): Host 'mydom.tld' is not allowed to connect to this MySQL server [EMAIL PROTECTED]:~ and when i do a (in a valid root window of course) mysql flush privileges; Query OK, 0 rows affected (0.00 sec) mysql i can login again: [EMAIL PROTECTED]:~ mysql -h mydom.tld -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 113 to server version: 4.1.7-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: last_insert_id
mel list_php wrote: Hi! I have a database where several users can connect and input data. I managed to have my insert queries as atomic, but I was wondering about one special case: I make one insert, and retrieve the last id inserted by mysql because I need to update an other table with that id. - if one user inserts and retrieves the id, but between both one other has inserted something the id returned will be the right one? Last_insert_id() is consistent on a per-connection basis, meaning you don't need to use lock (hopefullly !) http://dev.mysql.com/doc/mysql/en/getting-unique-id.html btw, what do you mean by : I managed to have my insert queries as atomic. Because if you do : INSERT it's already suppose to be atomic. HIMH. -- Philippe Poelvoorde COS Trading Ltd. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL on AIX 5.2
Hi Ben, all! Am Di, den 15.02.2005 schrieb Ben Clewett um 11:28: Forget the question, I have found my answer, thanks! The answer is in MySQL manual, section 2.12.5.3. Seems AIX default to max 256MB memory without compilation options. Then a max of 2GB is possible. Yes, this is an architectural issue with AIX (32 bit): Its address space is divided into 16 segments (4 bit) of 256 MB (28 bit) each. You will find more details about this in the newsgroup comp.unix.aix, together with ways to overcome that restriction in a recompilation - but I do not know whether anybody used these on MySQL. Regards, Joerg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: instable behaviour of mysql
First of all you have an eroneus update statement: UPDATE SET activ = 'inactiv' WHERE ident = 'fai' After UPDATE the tablename must be pesent UPDATE `tble_name` SET activ = 'inactiv' WHERE ident = 'fai' Now next in line... REVOKE all ON fai_accounts FROM 'fai'@'mydom.tld' You are revoking user 'fai' from host 'mydom.tld' at database 'fai_accounts'... but this has nothing to do with show grants for current_user() Because that will show the grant 'source' for the current user that I think is not 'fai' but 'root' as you output says... To see grants for 'fai'@'mydom.tld' you should use: SHOW GRANTS FOR 'fai'@'mydom.tld' Hope it helps ! Gabriel PREDA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
instable behaviour of mysql
Hi in the meantime i installed mysql select version(); ++ | version() | ++ | 4.1.9-standard-log | ++ 1 row in set (0.07 sec) mysql [EMAIL PROTECTED]:~ uname -a Linux rosetta 2.6.4-54.5-default #1 Fri May 7 21:43:10 UTC 2004 i686 i686 i386 GNU/Linux [EMAIL PROTECTED]:~ shows exactly the same behaviour as the older version: DROP TABLE fili_accounts DROP TABLE fili_contacts DROP TABLE fili_knowledge DROP TABLE fili_tasks DROP TABLE fili_timer DELETE FROM participants WHERE ident = 'fili' DELETE FROM part_val WHERE ident = 'fili' UPDATE SET activ = 'inactiv' WHERE ident = 'fili' Query 'UPDATE SET activ = 'inactiv' WHERE ident = 'fili'' failed. MySQL Error Number: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET activ = 'inactiv' WHERE ident = 'fili'' at line 1 REVOKE all ON fili_accounts FROM 'fili'@'mydom.tld' MySQL Error Number: 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET activ = 'inactiv' WHERE ident = 'fili'' at line 1 show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE all ON fili_contacts FROM 'fili'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE all ON fili_knowledge FROM 'fili'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE all ON fili_tasks FROM 'fili'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE all ON fili_timer FROM 'fili'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE all ON fili_files FROM 'fili'@'mydom.tld' Query 'REVOKE all ON fili_files FROM 'fili'@'mydom.tld'' failed. MySQL Error Number: 1147: There is no such grant defined for user 'fili' on host 'mydom.tld' on table 'fili_files' show grants for current_user() MySQL Error Number: 1147: There is no such grant defined for user 'fili' on host 'mydom.tld' on table 'fili_files' Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE select ON participants FROM 'fili'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE select,update ON part_val FROM 'fili'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION REVOKE GRANT OPTION ON *.* FROM 'fili'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION DROP USER 'fili'@'mydom.tld' show grants for current_user() Grants for [EMAIL PROTECTED]GRANT ALL PRIVILEGES ON *.* TO 'root'@'mydom.tld' IDENTIFIED BY PASSWORD '17e741d62b857bab' WITH GRANT OPTION and after that [EMAIL PROTECTED]:~ mysql -h mydom -u root -p Enter password: ERROR 1130 (0): Host 'rosetta.ayni.com' is not allowed to connect to this MySQL server [EMAIL PROTECTED]:~ uname -a Linux rosetta 2.6.4-54.5-default #1 Fri May 7 21:43:10 UTC 2004 i686 i686 i386 GNU/Linux [EMAIL PROTECTED]:~ and then doing a mysql flush privileges; Query OK, 0 rows affected (0.00 sec) mysql which makes me again go into mysql: [EMAIL PROTECTED]:~ mysql -h mydom -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 to server version: 4.1.9-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: last_insert_id
Yes that's what I mean I arranged to have single queries for the inserts. For example I avoid doing a select on criteria to retrieve that id an then an update of this id. thanks for help!!! From: Philippe Poelvoorde [EMAIL PROTECTED] Reply-To: 'mysql@lists.mysql.com ' mysql@lists.mysql.com To: mel list_php [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: last_insert_id Date: Tue, 15 Feb 2005 10:50:40 + mel list_php wrote: Hi! I have a database where several users can connect and input data. I managed to have my insert queries as atomic, but I was wondering about one special case: I make one insert, and retrieve the last id inserted by mysql because I need to update an other table with that id. - if one user inserts and retrieves the id, but between both one other has inserted something the id returned will be the right one? Last_insert_id() is consistent on a per-connection basis, meaning you don't need to use lock (hopefullly !) http://dev.mysql.com/doc/mysql/en/getting-unique-id.html btw, what do you mean by : I managed to have my insert queries as atomic. Because if you do : INSERT it's already suppose to be atomic. HIMH. -- Philippe Poelvoorde COS Trading Ltd. _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to plan the Tablespace in a huge mysql?
Hello: The mysql server is estimated to be as follows, 1. two servers, one is master and the other is slaves (replication) 2. two databases in mysql 3. 513 tables in each database 4. about 300 rows in each table 5. about 2T disk space for each server using SAN Storage 6. backup database periodically The running environment is follows, Server: Dual Intel Xeon 3.2G with 4G DDR2 Memory. OS: FreeBSD 5.3-RELEASE MySQL: 4.1 branch Operation: 70 ~ 80% operation is query (select statement) According to the above terms, how to plan the Tablespace in the mysql server? Using raw devices for the tablespace or innodb_data_file? ( How many Tablespace do I create? ) or using innodb_data_file with innodb_file_per_table? Regards, proace. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: last_insert_id
I just would ask for a precision: In my system, I include a connection file with my parameters (host,user,pass).This is the details of the account allowed to establish the connection with the mysql server. When 2 users are connecting to the database (through the web), they will use the same details (host,user,pass) for the connection. Does that mean that they are sharing the same mysql connection (and in that case will I need a lock?) or are they each of them opening their own connection? Can I check that somewhere? Thanks a lot. From: Philippe Poelvoorde [EMAIL PROTECTED] Reply-To: 'mysql@lists.mysql.com ' mysql@lists.mysql.com To: mel list_php [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: last_insert_id Date: Tue, 15 Feb 2005 10:50:40 + mel list_php wrote: Hi! I have a database where several users can connect and input data. I managed to have my insert queries as atomic, but I was wondering about one special case: I make one insert, and retrieve the last id inserted by mysql because I need to update an other table with that id. - if one user inserts and retrieves the id, but between both one other has inserted something the id returned will be the right one? Last_insert_id() is consistent on a per-connection basis, meaning you don't need to use lock (hopefullly !) http://dev.mysql.com/doc/mysql/en/getting-unique-id.html btw, what do you mean by : I managed to have my insert queries as atomic. Because if you do : INSERT it's already suppose to be atomic. HIMH. -- Philippe Poelvoorde COS Trading Ltd. _ Stay in touch with absent friends - get MSN Messenger http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: last_insert_id
really sorry to bother you with my connections problems. I've made a test using select connection_id(), to see what was the current identifier for my connection. Each time I change of page, the connection_id is different, I suppose that is because I require my connection file at the beginning of each script. This seems fine to me as I don't want to lock the tables, and the last_insert_id is performed in the same file than the insert, so on the per connection basis it's perfect (thank you very much for your help!!). But it seems a bit strange to me to open so many connections. I know there is a limit somewhere, at the moment it is not a problem I don't have a lot of users and they are not coming often, but can it become one in the future? I tried to find in the documentation some information on when is a connection open or if it is possible to keep one connection per user, but found nothing. In addition I don't think it is possible because for the mysql server only one user gets connected, it doesn't care about the users I have in my authentication table. If I misunderstood something please point me to some doc or give me some advice... From: Philippe Poelvoorde [EMAIL PROTECTED] Reply-To: 'mysql@lists.mysql.com ' mysql@lists.mysql.com To: mel list_php [EMAIL PROTECTED] CC: mysql@lists.mysql.com Subject: Re: last_insert_id Date: Tue, 15 Feb 2005 10:50:40 + mel list_php wrote: Hi! I have a database where several users can connect and input data. I managed to have my insert queries as atomic, but I was wondering about one special case: I make one insert, and retrieve the last id inserted by mysql because I need to update an other table with that id. - if one user inserts and retrieves the id, but between both one other has inserted something the id returned will be the right one? Last_insert_id() is consistent on a per-connection basis, meaning you don't need to use lock (hopefullly !) http://dev.mysql.com/doc/mysql/en/getting-unique-id.html btw, what do you mean by : I managed to have my insert queries as atomic. Because if you do : INSERT it's already suppose to be atomic. HIMH. -- Philippe Poelvoorde COS Trading Ltd. _ Express yourself with cool new emoticons http://www.msn.co.uk/specials/myemo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.1.10 has been released
Hi, MySQL 4.1.10, a new version of the popular Open Source/Free Software Database Management System, has been released. It is now available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/ and mirror sites. Note that not all mirror sites may be up to date at this point in time - if you can't find this version on some mirror, please try again later or choose another download site. This is a bugfix release for the current production version. Starting from 4.1.10, we also offer the binaries for Solaris in PKG format as well as special NDB packages in RPM format. Feedback about these new packages is welcome. Please refer to our bug database at http://bugs.mysql.com/ for more details about the individual bugs fixed in this version. News from the ChangeLog: Functionality added or changed: * Added `mysql_library_init()' and `mysql_library_end()' as synonyms for the `mysql_server_init()' and `mysql_server_end()' C API functions. `mysql_library_init()' and `mysql_library_end()' are `#define' symbols, but the names more clearly indicate that they should be called when beginning and ending use of a MySQL C API library no matter whether the application uses `libmysqlclient' or `libmysqld'. (Bug #6149) * The server now issues a warning when `lower_case_table_names=2' and the data directory is on a case-sensitive filesystem, just as when `lower_case_table_names=0' on a case-insensitive filesystem. (Bug #7887) * The server now issues a warning to the error log when it encounters older tables that contain character columns that might be interpreted by newer servers to have a different column length. (Bug #6913) See *Note Upgrading-from-4.0:: for a discussion of this problem and what to do about it. * InnoDB: When MySQL/InnoDB is compiled on Mac OS X 10.2 or earlier, detect the operating system version at run time and use the `fcntl()' file flush method on Mac OS X versions 10.3 and later. Apple had disabled `fsync()' in Mac OS X for internal disk drives, which caused corruption at power outages. * InnoDB: A shared record lock (`LOCK_REC_NOT_GAP') is now taken for a matching record in the foreign key check because inserts can be allowed into gaps. * InnoDB: Relaxed locking in INSERT...SELECT, single table UPDATE...SELECT and single table DELETE...SELECT clauses when innobase_locks_unsafe_for_binlog is used and isolation level of the transaction is not serializable. `InnoDB' uses consistent read in these cases for a selected table. Bugs fixed: * A test case was failing on Linux/IA-64 due to insufficient thread stack size. The size was increased from 192KB to 256KB on this platform. (Bug #8391) * Fixed `LOAD INDEX' statement to actually load index in memory. (Bug #8452) * If multiple prepared statements were executed without retrieving their results, executing one of them again would cause the client program to crash. (Bug #8330) * Non-numeric values inserted into a `YEAR' column were being stored as `2000' rather than as `'. (Bug #6067) * Fixed a failure of multiple-table updates to replicate properly on slave servers when `--replicate-*-table' options had been specified. (Bug #7011) * `mysql_stmt_close()' C API function was not clearing an error indicator when a previous prepare call failed, causing subsequent invocations of error-retrieving calls to indicate spurious error values. (Bug #7990) * Fixed failure of `CREATE TABLE ... LIKE' Windows when the source or destination table was located in a symlinked database directory. (Bug #6607) * With `lower_case_table_names' set to 1, `mysqldump' on Windows could write the same table name in different lettercase for different SQL statements. Fixed so that consistent lettercase is used. (Bug #5185) `HAVING' that referred to `RAND()' or a user-defined function in the `SELECT' part through an alias could cause a crash or wrong value. (Bug #8216) * If one used `CONVERT_TZ()' function in `SELECT', which in its turn was used in `CREATE TABLE' statements, then system time zone tables were added to list of tables joined in SELECT and thus erroneous result was produced. (Bug #7899) * Fixed a bug in `CONV()' function returning unsigned `BIGINT' number (third argument is positive, and return value does not fit in 32 bits). (Bug #7751) * Fixed a failure of the `IN()' operator to return correct result if all values in the list were constants and some of them were using substring functions, for example, `LEFT()', `RIGHT()', or `MID()'. * Fixed problem with `SHOW INDEX' reporting `Sub_part' values in bytes rather than characters for columns with a multi-byte character set. (Bug #7943) * Fixed
how to make question that check the last hour
Hello, I relly new with databases and writing sql-questions. But in my db want I to check what have new rows have come the last hour. the db have I as follow: ID email created updated 001 [EMAIL PROTECTED] 20050215131034 20050215133401 063 [EMAIL PROTECTED] 20050215141034 20050215141201 76 [EMAIL PROTECTED] 20050215134500 20050215134556 Now I would like to make a sql-question that show which new users have come the last hour, without that I need to edit the question each time I want to ask. Please help! -- ___ Sign-up for Ads Free at Mail.com http://promo.mail.com/adsfreejump.htm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to make question that check the last hour
Jesper Andersson [EMAIL PROTECTED] wrote on 15/02/2005 13:15:43: Hello, I relly new with databases and writing sql-questions. But in my db want I to check what have new rows have come the last hour. the db have I as follow: ID email created updated 001 [EMAIL PROTECTED] 20050215131034 20050215133401 063 [EMAIL PROTECTED] 20050215141034 20050215141201 76 [EMAIL PROTECTED] 20050215134500 20050215134556 Now I would like to make a sql-question that show which new users have come the last hour, without that I need to edit the question each time I want to ask. select colums from table where date_sub(now(), interval 1 hour) = created ; Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to make question that check the last hour
Jesper Andersson wrote: Hello, I relly new with databases and writing sql-questions. But in my db want I to check what have new rows have come the last hour. the db have I as follow: ID email created updated 001 [EMAIL PROTECTED] 20050215131034 20050215133401 063 [EMAIL PROTECTED] 20050215141034 20050215141201 76 [EMAIL PROTECTED] 20050215134500 20050215134556 Now I would like to make a sql-question that show which new users have come the last hour, without that I need to edit the question each time I want to ask. Please help! - try using date_format and now(). See http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html - ian -- +---+ | Ian Sales Database Administrator | | | | If your DBA is busy all the time... | | ...he's not doing his job properly | | ebuyer http://www.ebuyer.com | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Capitalize first letter
Hello, 1-I am trying to capialize the first letter of each word in a ceratin filed: SELECT CONCAT( UPPER( SUBSTRING( cat_name, 1, 1 ) ) , LOWER( SUBSTRING( cat_name, 2 ) ) ) FROM `cateogries` It is only doing it for the first word. if the category has more than one word separated by (space) the others are left in lower case 2- Assume I want to update the result directly in the database, is there anyway to automatically do it in one statement like: (select replace) Georges -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LOAD DATA INFILE Opposite
Hi, The following function loads data from a file: http://dev.mysql.com/doc/mysql/en/load-data.html Is there a function like this that I can use to save the results of a query to a CSV file for the user of my PHP application to donwload? Thanks for your help -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LOAD DATA INFILE Opposite
From: shaun thornburgh [mailto:[EMAIL PROTECTED] Hi, The following function loads data from a file: http://dev.mysql.com/doc/mysql/en/load-data.html Is there a function like this that I can use to save the results of a query to a CSV file for the user of my PHP application to donwload? Thanks for your help SELECT ... INTO OUTFILE is what you want. It's sort of covered in the SELECT syntax documentation: http://dev.mysql.com/doc/mysql/en/select.html Pretty handy at times. HTH! -- Mike Johnson Smarter Living, Inc. Web Developerwww.smartertravel.com [EMAIL PROTECTED] (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: join speed vs. 2 queries
Many Thanks Peter, I appreciate your response. Played around with the indexes, and modified the query a bit more to match the campaignId of the value first and got a 2000x performance increase from the original query...now it takes .03 seconds on average where it used to take 60. One question though: is there a benefit to doing the INNER JOIN in the FROM clause rather than creating a join condition in the WHERE clause? I rewrote the query a bit using the WHERE join condition and noticed a slight performance hit on this particular query... Also, I am doing some pretty hefty multi-dimensional analysis on the data such as getting all the hits for each of m-variables with n-values each within a date range that also contain certain other varname/value combinations. Now if I am doing multiple different queries on this data, using different fields of the table in each one, is it preferable to create an index for each query, or make one uber-index that can be used as a swiss-army knife for at least a couple of them? I don't want to slow down INSERT calls if I can help it, but I don't want my queries to take 5 minutes each either... Perhaps the solution is two different databases, one without indexes for inserts, one with indexes for pulling the data back out? Then I can use indexes when I need to and don't have the performance hit when I need to insert data. The last question I have regards EXPLAIN results... Am I correct in believing that each row corresponds to a different comparison in the WHERE clause, and that each row analysis is done on each of the rows before it (associative)? So if rows is 10,10,10 for 3 joins, then in actuality 1000 rows will be examined? If this is the case, then I imagine it is better to winnow to the smallest possible set first and then go from there trying to get as few rows examined as theoretically possible given the constraints of the query. ~Mathew Peter Brawley wrote: I have a gut feeling that this kind of join should be able to be done with similar speed without having to use a temp table Yep but remember the query engine uses one index per table so without seeing your EXPLAIN output I'd try indexing ... the data table on name_id,value_id,campaign_id, the names table on id,name, the values table on id,value, then writing the query as ... SELECT COUNT(*) FROM data INNER JOIN names ON data.name_id=names.id INNER JOIN values ON data.value_id=values.id WHERE data.campaign_id = 22 AND names.name = 'content' AND values.value = 'index' PB - Mathew Ray wrote: Newbie on the list here having a bit of confusion at the moment why an INNER JOIN is taking so long... I have replaced a few column names to make it a bit more succinct: SELECT COUNT(*) FROM data, values, names WHERE data.campaign_id = 22 AND names.name = 'content' AND values.value = 'index' AND data.name_id = names.id AND data.value_id = value.id; This query should pull out all of the index content from the data table for campaign 22. name_id and value_id are indexed, as are the name and value fields of the names and values tables. campaign_id is also indexed in the data table and each name and value is unique per campaign. The vardata dataset for this campaign that has around 163000 entries and the above query takes nearly a minute to run. Total size of data table is around 3 million records. On the same machine, the following query takes roughly 2 seconds to run: CREATE TEMPORARY TABLE IF NOT EXISTS names_temp SELECT names.id as var_id, values.id as val_id FROM values, names WHERE names.campaign_id = 22 AND values.campaign_id = names.campaign_id AND names.name = 'content' AND values.value = 'index'; SELECT COUNT(*) FROM vardata, names_temp WHERE vardata.varNameId = names_temp.var_id AND vardata.varValueId = names_temp.val_id; After looking at EXPLAIN for both, I understand that the latter is faster because it is doing lookups based on constant values, but I have a gut feeling that this kind of join should be able to be done with similar speed without having to use a temp table... Is there any way to optimize the performance of the join query without having to go with the two-query option? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unable to start MySQL
Hello, I have been trying to install and run MySQL on a Redhat Enterprise machine. The installation appears to go just fine, but when I go to run it, MySQL doesn't startup. Here is what I have done so far: INSTALLATION: ./configure --with-openssl=/usr/local/ssl --with-openssl-includes=/usr/local/ssl/include --with-openssl-libs=/usr/local/ssl/lib --with-isam --with-archive-storage-engine --with-csv-storage-engine make make install /usr/bin/mysql_install_db --user=mysql After doing the above, I tried running MySQL with the following commands: /usr/bin/safe_mysqld /usr/bin/safe_mysqld --user=mysql Each of these resulted in this sort of response: --- [EMAIL PROTECTED] usr]# /usr/bin/safe_mysqld --user=mysql [1] 14204 [EMAIL PROTECTED] usr]# Starting mysqld daemon with databases from /var/lib/mysql 050215 11:31:25 mysqld ended [1]+ Done/usr/bin/safe_mysqld --user=mysql [EMAIL PROTECTED] usr]# --- Any assistance with this would be greatly appreciated. --- Thank You, Jason Williard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
one hour is/is not 60 minutes, that's the question...
Hi again mysql-listers mysql select version(); ++ | version() | ++ | 4.1.9-standard-log | ++ 1 row in set (0.00 sec) mysql [EMAIL PROTECTED]:~ uname -a Linux mydom 2.6.4-54.5-default #1 Fri May 7 21:43:10 UTC 2004 i686 i686 i386 GNU/Linux [EMAIL PROTECTED]:~ mysql select addtime(now(), '00:00:00'); ++ | addtime(now(), '00:00:00') | ++ | 2005-02-15 16:49:17| ++ 1 row in set (0.00 sec) mysql select addtime(now(), '00:60:00'); ++ | addtime(now(), '00:60:00') | ++ | NULL | ++ 1 row in set, 1 warning (0.00 sec) mysql select addtime(now(), '01:00:00'); ++ | addtime(now(), '01:00:00') | ++ | 2005-02-15 17:50:27| ++ 1 row in set (0.00 sec) in my opinion the result of the second and third example above must be the same. suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: one hour is/is not 60 minutes, that's the question...
On Tue, Feb 15, 2005 at 06:48:08PM +0100, schlubediwup [EMAIL PROTECTED] wrote: Hi again mysql-listers mysql select addtime(now(), '00:60:00'); ++ | addtime(now(), '00:60:00') | ++ | NULL | ++ 1 row in set, 1 warning (0.00 sec) ^^ Did you check the warning (with SHOW WARNINGS)? -Rich -- Rich Lafferty --+--- Ottawa, Ontario, Canada| Save the Pacific Northwest Tree Octopus! http://www.lafferty.ca/|http://zapatopi.net/treeoctopus.html [EMAIL PROTECTED] ---+--- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Unable to start MySQL
2 things -- 1) Permissions on your /mysql/data/ directory. User mysql needs to have ownership, group mysql needs to have ownership as well. shell groupadd mysql shell useradd -g mysql mysql shell chown -R root /path/to/mysql/. shell chown -R mysql /path/to/mysql/data/. shell chgrp -r mysql /path/to/mysql/. 2) Check the hostname.err file. It will tell you why you are getting those errors. More than likely the permissions. J.R. -Original Message- From: Jason Williard [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 15, 2005 12:33 PM To: mysql@lists.mysql.com Subject: Unable to start MySQL Hello, I have been trying to install and run MySQL on a Redhat Enterprise machine. The installation appears to go just fine, but when I go to run it, MySQL doesn't startup. Here is what I have done so far: INSTALLATION: ./configure --with-openssl=/usr/local/ssl --with-openssl-includes=/usr/local/ssl/include --with-openssl-libs=/usr/local/ssl/lib --with-isam --with-archive-storage-engine --with-csv-storage-engine make make install /usr/bin/mysql_install_db --user=mysql After doing the above, I tried running MySQL with the following commands: /usr/bin/safe_mysqld /usr/bin/safe_mysqld --user=mysql Each of these resulted in this sort of response: --- [EMAIL PROTECTED] usr]# /usr/bin/safe_mysqld --user=mysql [1] 14204 [EMAIL PROTECTED] usr]# Starting mysqld daemon with databases from /var/lib/mysql 050215 11:31:25 mysqld ended [1]+ Done/usr/bin/safe_mysqld --user=mysql [EMAIL PROTECTED] usr]# --- Any assistance with this would be greatly appreciated. --- Thank You, Jason Williard -- 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: one hour is/is not 60 minutes, that's the question...
Hello, mysql select version(); ++ | version() | ++ | 4.1.9-standard-log | ++ 1 row in set (0.00 sec) mysql Although I'm not fully aware of MySQL time/date symantecs, I would like to make a comment... mysql select addtime(now(), '00:00:00'); ++ | addtime(now(), '00:00:00') | ++ | 2005-02-15 16:49:17| ++ 1 row in set (0.00 sec) mysql select addtime(now(), '00:60:00'); There's no such time. 00:60:00 is a duration, not a time. So I too wonder what the warning was... ++ | addtime(now(), '00:60:00') | ++ | NULL | ++ 1 row in set, 1 warning (0.00 sec) mysql select addtime(now(), '01:00:00'); This is a valid time. Namely, 01:00 AM. ++ | addtime(now(), '01:00:00') | ++ | 2005-02-15 17:50:27| ++ 1 row in set (0.00 sec) in my opinion the result of the second and third example above must be the same. Perhaps there's another function to add durations? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: one hour is/is not 60 minutes, that's the question...
Is the DATE_ADD(time, INTERVAL) an option? SELECT Now(), DATE_ADD(Now(), interval 60 minute) schlubediwup wrote: Hi again mysql-listers mysql select version(); ++ | version() | ++ | 4.1.9-standard-log | ++ 1 row in set (0.00 sec) mysql [EMAIL PROTECTED]:~ uname -a Linux mydom 2.6.4-54.5-default #1 Fri May 7 21:43:10 UTC 2004 i686 i686 i386 GNU/Linux [EMAIL PROTECTED]:~ mysql select addtime(now(), '00:00:00'); ++ | addtime(now(), '00:00:00') | ++ | 2005-02-15 16:49:17| ++ 1 row in set (0.00 sec) mysql select addtime(now(), '00:60:00'); ++ | addtime(now(), '00:60:00') | ++ | NULL | ++ 1 row in set, 1 warning (0.00 sec) mysql select addtime(now(), '01:00:00'); ++ | addtime(now(), '01:00:00') | ++ | 2005-02-15 17:50:27| ++ 1 row in set (0.00 sec) in my opinion the result of the second and third example above must be the same. suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: one hour is/is not 60 minutes, that's the question...
The minute part of a time expression only has a valid range of 0 to 59. http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html http://dev.mysql.com/doc/mysql/en/time.html --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: schlubediwup [mailto:[EMAIL PROTECTED] Sent: Tuesday, February 15, 2005 11:48 To: mysql@lists.mysql.com Subject: one hour is/is not 60 minutes, that's the question... Hi again mysql-listers mysql select version(); ++ | version() | ++ | 4.1.9-standard-log | ++ 1 row in set (0.00 sec) mysql [EMAIL PROTECTED]:~ uname -a Linux mydom 2.6.4-54.5-default #1 Fri May 7 21:43:10 UTC 2004 i686 i686 i386 GNU/Linux [EMAIL PROTECTED]:~ mysql select addtime(now(), '00:00:00'); ++ | addtime(now(), '00:00:00') | ++ | 2005-02-15 16:49:17| ++ 1 row in set (0.00 sec) mysql select addtime(now(), '00:60:00'); ++ | addtime(now(), '00:60:00') | ++ | NULL | ++ 1 row in set, 1 warning (0.00 sec) mysql select addtime(now(), '01:00:00'); ++ | addtime(now(), '01:00:00') | ++ | 2005-02-15 17:50:27| ++ 1 row in set (0.00 sec) in my opinion the result of the second and third example above must be the same. suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: one hour is/is not 60 minutes, that's the question...
In article [EMAIL PROTECTED], schlubediwup [EMAIL PROTECTED] writes: mysql select addtime(now(), '00:00:00'); ++ | addtime(now(), '00:00:00') | ++ | 2005-02-15 16:49:17| ++ 1 row in set (0.00 sec) mysql select addtime(now(), '00:60:00'); ++ | addtime(now(), '00:60:00') | ++ | NULL | ++ 1 row in set, 1 warning (0.00 sec) mysql select addtime(now(), '01:00:00'); ++ | addtime(now(), '01:00:00') | ++ | 2005-02-15 17:50:27| ++ 1 row in set (0.00 sec) in my opinion the result of the second and third example above must be the same. Nope. Leap seconds do not accumulate to leap minutes; thus there's no hour with 60 minutes. I'm not sure about the silent conversion to NULL; PostgreSQL throws an error stating 'interval field value out of range: 00:60:00'. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Network and You
Hi all, So what does the buzz about MySQL Network mean, for you? And what about licensing? I wrote an article about all that: MySQL Network and You. http://dev.mysql.com/tech-resources/articles/mysql-network-and-you.html In a nutshell: - Some people spend time to save money, and MySQL Community Edition is for them. - Other people spend money to save time, and MySQL Network is for them. And the most important thing: Nothing has been taken away, nor was licensing changed, nor code forked. There is a single code base for the MySQL server, dual-licensed with the free version available under the GPL license. If you have any feedback or questions, please feel to ask on the dedicated forum: http://forums.mysql.com/list.php?95 Regards, Arjen. -- Arjen Lentz, Community Relations Manager MySQL AB, www.mysql.com MySQL Users Conference (Santa Clara CA, 18-21 April 2005) Early registration until February 28: http://www.mysqluc.com/ 13 days left for early registration, 61 days until the event! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
timezone questions
Dear list, I never tried any timezone features(options) of mysql and like to learn from you. (1) In what situation one need to set mysql timezone? (I know there are application level timezone solutions) (2) What the effect if a custome timezone is settled with mysql? I use mysql for web applications and need to deal with timezone at web page generation level. Also I hope my datetime related data in the database be server timezone independent so that the data can be replicated among database servers located in possibly different zones. Thanks -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.300 / Virus Database: 265.8.8 - Release Date: 2/14/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Dual Xeon EM64T fedora3 x86_64
Hello, I just installed MySQL-server-4.0.23-0.x86_64.rpm on Dual Xeon 3.2 64 bit 8G of RAM with Fedora3 x86_64. I noticed (doing a ps ax|grep mysql) that there is only one mysqld process running. Is this normal? BR Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL/InnoDB-4.1.10 is released
Hi!
InnoDB is the MySQL table type that supports foreign keys, transactions,
non-escalating row-level locking, all SQL-92 transaction isolation levels,
multiversion concurrency control, savepoints, multiple tablespaces, and a
non-free online binary hot backup tool.
MySQL-4.1.10 is mainly a bugfix release.
Windows users of the my.cnf option innodb_file_per_table should upgrade to
this version, because this fixes the bug introduced to the Windows version
of 4.1.9, and earlier versions contained the critical bug in
innodb_file_per_table.
Functionality added or changed:
* When MySQL/InnoDB is compiled on Mac OS X 10.2 or earlier, detect the
operating system version at run time and use the fcntl() file flush method
on Mac OS X versions 10.3 and later. Apple had disabled fsync() in Mac OS X
for internal disk drives, which caused corruption at power outages.
* A shared record lock LOCK_REC_NOT_GAP is now taken for a matching record
in the foreign key check because inserts can be allowed into gaps.
* Relaxed locking in INSERT...SELECT, single table UPDATE...SELECT and
single table DELETE...SELECT clauses when innobase_locks_unsafe_for_binlog
is used and isolation level of the transaction is not serializable. InnoDB
uses consistent read in these cases for a selected table.
Bugs fixed:
* Fixed a bug introduced in 4.1.9 to the Windows version if you used
innodb_file_per_table. mysqld would stop and complain about Windows error
number 87 in a file operation. (Bug #8021)
* Corrected the handling of trailing spaces in the ucs2 character set. (Bug
#7350)
* Use native tmpfile() function on Netware. All InnoDB temporary files are
created under sys:\tmp. Previously, InnoDB temporary files were never
deleted on Netware.
* Fix a race condition that could cause the assertion
space-n_pending_flushes == 0 to fail in fil0fil.c, in
@code{fil_space_free()}, in DROP TABLE or in ALTER TABLE.
* ALTER TABLE ... ADD CONSTRAINT PRIMARY KEY ... complained about bad
foreign key definition. (Bug #7831)
* Fix a theoretical hang over the adaptive hash latch in InnoDB if one runs
INSERT ... SELECT ... (binlog not enabled), or a multi-table UPDATE or
DELETE, and only the read tables are InnoDB type, the rest are MyISAM. (Bug
#7879)
Upgrading to 4.1.9:
* If you have created or used InnoDB tables with TIMESTAMP columns in MySQL
versions 4.1.0-4.1.3, you have to rebuild those tables when you upgrade to
MySQL-4.1.4 or later. The storage format in those MySQL versions for a
TIMESTAMP column was wrong. If you upgrade from 4.0 to 4.1.4 or later, then
no rebuild of TIMESTAMP tables is needed.
* If you have stored characters ASCII(32) to non-latin1 non-BINARY indexed
columns in MySQL versions = 4.1.2, then you have to rebuild those tables
after you upgrade to = 4.1.3. The reason is that the sorting order of those
characters and the space character changes for some character sets in 4.1.3.
See the MySQL/InnoDB-4.1.3 changelog for a precise description of the cases
where you need to rebuild the table. Also MyISAM tables have to be rebuilt
or repaired in these cases.
* If you have used column prefix indexes on UTF-8 columns or other multibyte
character set columns in 4.1.0 - 4.1.5, you have to rebuild the tables when
you upgrade to 4.1.6 or later.
* If you have used accent characters (ASCII codes = 128) in database names,
table names, constraint names, or column names in versions 4.1, you cannot
upgrade to 4.1 directly, because 4.1 uses UTF-8 to store metadata names. Use
RENAME TABLE to overcome this if the accent character is in the table name
or the database name, or rebuild the table.
Best regards,
Heikki Tuuri
Innobase Oy
http://www.innodb.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: join speed vs. 2 queries
Matthew, ...is there a benefit to doing the INNER JOIN in the FROM clause rather than creating a join condition in the WHERE clause? I rewrote the query a bit using the WHERE join condition and noticed a slight performance hit on this particular query... There are (at least) three benefits to putting JOINs in the FROM clause: clarity for you and anyone who reads it, maintainability by you or anyone who follows you, and you've removed one kind of guessing from the query engine's joblist. Also, I am doing some pretty hefty multi-dimensional analysis on the data such as getting all the hits for each of m-variables with n-values each within a date range that also contain certain other varname/value combinations. Now if I am doing multiple different queries on this data, using different fields of the table in each one, is it preferable to create an index for each query, or make one uber-index that can be used as a swiss-army knife for at least a couple of them? I don't want to slow down INSERT calls if I can help it, but I don't want my queries to take 5 minutes each either... If you can get one multi-col index to work for most queries, you're golden. Failing that ... Perhaps the solution is two different databases, one without indexes for inserts, one with indexes for pulling the data back out? Then I can use indexes when I need to and don't have the performance hit when I need to insert data. ... an OLTP db for updates, an OLAP db for reports will often SYA. Yes, winnow down starting at the top. PB Mathew Ray wrote: Many Thanks Peter, I appreciate your response. Played around with the indexes, and modified the query a bit more to match the campaignId of the value first and got a 2000x performance increase from the original query...now it takes .03 seconds on average where it used to take 60. One question though: is there a benefit to doing the INNER JOIN in the FROM clause rather than creating a join condition in the WHERE clause? I rewrote the query a bit using the WHERE join condition and noticed a slight performance hit on this particular query... Also, I am doing some pretty hefty multi-dimensional analysis on the data such as getting all the hits for each of m-variables with n-values each within a date range that also contain certain other varname/value combinations. Now if I am doing multiple different queries on this data, using different fields of the table in each one, is it preferable to create an index for each query, or make one uber-index that can be used as a swiss-army knife for at least a couple of them? I don't want to slow down INSERT calls if I can help it, but I don't want my queries to take 5 minutes each either... Perhaps the solution is two different databases, one without indexes for inserts, one with indexes for pulling the data back out? Then I can use indexes when I need to and don't have the performance hit when I need to insert data. The last question I have regards EXPLAIN results... Am I correct in believing that each row corresponds to a different comparison in the WHERE clause, and that each row analysis is done on each of the rows before it (associative)? So if rows is 10,10,10 for 3 joins, then in actuality 1000 rows will be examined? If this is the case, then I imagine it is better to winnow to the smallest possible set first and then go from there trying to get as few rows examined as theoretically possible given the constraints of the query. ~Mathew Peter Brawley wrote: I have a gut feeling that this kind of join should be able to be done with similar speed without having to use a temp table Yep but remember the query engine uses one index per table so without seeing your EXPLAIN output I'd try indexing ... the data table on name_id,value_id,campaign_id, the names table on id,name, the values table on id,value, then writing the query as ... SELECT COUNT(*) FROM data INNER JOIN names ON data.name_id=names.id INNER JOIN values ON data.value_id=values.id WHERE data.campaign_id = 22 AND names.name = 'content' AND values.value = 'index' PB - Mathew Ray wrote: Newbie on the list here having a bit of confusion at the moment why an INNER JOIN is taking so long... I have replaced a few column names to make it a bit more succinct: SELECT COUNT(*) FROM data, values, names WHERE data.campaign_id = 22 AND names.name = 'content' AND values.value = 'index' AND data.name_id = names.id AND data.value_id = value.id; This query should pull out all of the index content from the data table for campaign 22. name_id and value_id are indexed, as are the name and value fields of the names and values tables. campaign_id is also indexed in the data table and each name and value is unique per campaign. The vardata dataset for this campaign that has around 163000 entries and the above query takes nearly a minute to run. Total size of data table is around 3 million records. On the same machine, the following query
Re: MySQL/InnoDB-4.1.10 is released
Are foreign key and other constraints enforced by the db server in this
version or is this something that the programmer has to ensure via
application logic?
- Asad
On Tue, 15 Feb 2005, Heikki Tuuri wrote:
Hi!
InnoDB is the MySQL table type that supports foreign keys, transactions,
non-escalating row-level locking, all SQL-92 transaction isolation levels,
multiversion concurrency control, savepoints, multiple tablespaces, and a
non-free online binary hot backup tool.
MySQL-4.1.10 is mainly a bugfix release.
Windows users of the my.cnf option innodb_file_per_table should upgrade to
this version, because this fixes the bug introduced to the Windows version
of 4.1.9, and earlier versions contained the critical bug in
innodb_file_per_table.
Functionality added or changed:
* When MySQL/InnoDB is compiled on Mac OS X 10.2 or earlier, detect the
operating system version at run time and use the fcntl() file flush method
on Mac OS X versions 10.3 and later. Apple had disabled fsync() in Mac OS X
for internal disk drives, which caused corruption at power outages.
* A shared record lock LOCK_REC_NOT_GAP is now taken for a matching record
in the foreign key check because inserts can be allowed into gaps.
* Relaxed locking in INSERT...SELECT, single table UPDATE...SELECT and
single table DELETE...SELECT clauses when innobase_locks_unsafe_for_binlog
is used and isolation level of the transaction is not serializable. InnoDB
uses consistent read in these cases for a selected table.
Bugs fixed:
* Fixed a bug introduced in 4.1.9 to the Windows version if you used
innodb_file_per_table. mysqld would stop and complain about Windows error
number 87 in a file operation. (Bug #8021)
* Corrected the handling of trailing spaces in the ucs2 character set. (Bug
#7350)
* Use native tmpfile() function on Netware. All InnoDB temporary files are
created under sys:\tmp. Previously, InnoDB temporary files were never
deleted on Netware.
* Fix a race condition that could cause the assertion
space-n_pending_flushes == 0 to fail in fil0fil.c, in
@code{fil_space_free()}, in DROP TABLE or in ALTER TABLE.
* ALTER TABLE ... ADD CONSTRAINT PRIMARY KEY ... complained about bad
foreign key definition. (Bug #7831)
* Fix a theoretical hang over the adaptive hash latch in InnoDB if one runs
INSERT ... SELECT ... (binlog not enabled), or a multi-table UPDATE or
DELETE, and only the read tables are InnoDB type, the rest are MyISAM. (Bug
#7879)
Upgrading to 4.1.9:
* If you have created or used InnoDB tables with TIMESTAMP columns in MySQL
versions 4.1.0-4.1.3, you have to rebuild those tables when you upgrade to
MySQL-4.1.4 or later. The storage format in those MySQL versions for a
TIMESTAMP column was wrong. If you upgrade from 4.0 to 4.1.4 or later, then
no rebuild of TIMESTAMP tables is needed.
* If you have stored characters ASCII(32) to non-latin1 non-BINARY indexed
columns in MySQL versions = 4.1.2, then you have to rebuild those tables
after you upgrade to = 4.1.3. The reason is that the sorting order of those
characters and the space character changes for some character sets in 4.1.3.
See the MySQL/InnoDB-4.1.3 changelog for a precise description of the cases
where you need to rebuild the table. Also MyISAM tables have to be rebuilt
or repaired in these cases.
* If you have used column prefix indexes on UTF-8 columns or other multibyte
character set columns in 4.1.0 - 4.1.5, you have to rebuild the tables when
you upgrade to 4.1.6 or later.
* If you have used accent characters (ASCII codes = 128) in database names,
table names, constraint names, or column names in versions 4.1, you cannot
upgrade to 4.1 directly, because 4.1 uses UTF-8 to store metadata names. Use
RENAME TABLE to overcome this if the accent character is in the table name
or the database name, or rebuild the table.
Best regards,
Heikki Tuuri
Innobase Oy
http://www.innodb.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL/InnoDB-4.1.10 is released
Asad,
InnoDB type tables have enforced FOREIGN KEY constraints since 2001.
Unfortunately, none of the table types of MySQL yet supports CHECK
constraints.
Best regards,
Heikki
.
List: mysql
Subject:Re: MySQL/InnoDB-4.1.10 is released
From: Asad Habib ahabib () engin ! umich ! edu
Date: 2005-02-15 20:00:40
Message-ID: Pine.GSO.4.58.0502151459050.28688 () lapis ! engin ! umich !
edu
[Download message RAW]
Are foreign key and other constraints enforced by the db server in this
version or is this something that the programmer has to ensure via
application logic?
- Asad
On Tue, 15 Feb 2005, Heikki Tuuri wrote:
Hi!
InnoDB is the MySQL table type that supports foreign keys, transactions,
non-escalating row-level locking, all SQL-92 transaction isolation levels,
multiversion concurrency control, savepoints, multiple tablespaces, and a
non-free online binary hot backup tool.
MySQL-4.1.10 is mainly a bugfix release.
Windows users of the my.cnf option innodb_file_per_table should upgrade to
this version, because this fixes the bug introduced to the Windows version
of 4.1.9, and earlier versions contained the critical bug in
innodb_file_per_table.
Functionality added or changed:
* When MySQL/InnoDB is compiled on Mac OS X 10.2 or earlier, detect the
operating system version at run time and use the fcntl() file flush method
on Mac OS X versions 10.3 and later. Apple had disabled fsync() in Mac OS
X
for internal disk drives, which caused corruption at power outages.
* A shared record lock LOCK_REC_NOT_GAP is now taken for a matching record
in the foreign key check because inserts can be allowed into gaps.
* Relaxed locking in INSERT...SELECT, single table UPDATE...SELECT and
single table DELETE...SELECT clauses when innobase_locks_unsafe_for_binlog
is used and isolation level of the transaction is not serializable. InnoDB
uses consistent read in these cases for a selected table.
Bugs fixed:
* Fixed a bug introduced in 4.1.9 to the Windows version if you used
innodb_file_per_table. mysqld would stop and complain about Windows error
number 87 in a file operation. (Bug #8021)
* Corrected the handling of trailing spaces in the ucs2 character set.
(Bug
#7350)
* Use native tmpfile() function on Netware. All InnoDB temporary files are
created under sys:\tmp. Previously, InnoDB temporary files were never
deleted on Netware.
* Fix a race condition that could cause the assertion
space-n_pending_flushes == 0 to fail in fil0fil.c, in
@code{fil_space_free()}, in DROP TABLE or in ALTER TABLE.
* ALTER TABLE ... ADD CONSTRAINT PRIMARY KEY ... complained about bad
foreign key definition. (Bug #7831)
* Fix a theoretical hang over the adaptive hash latch in InnoDB if one
runs
INSERT ... SELECT ... (binlog not enabled), or a multi-table UPDATE or
DELETE, and only the read tables are InnoDB type, the rest are MyISAM.
(Bug
#7879)
Upgrading to 4.1.10:
* If you have created or used InnoDB tables with TIMESTAMP columns in
MySQL
versions 4.1.0-4.1.3, you have to rebuild those tables when you upgrade to
MySQL-4.1.4 or later. The storage format in those MySQL versions for a
TIMESTAMP column was wrong. If you upgrade from 4.0 to 4.1.4 or later,
then
no rebuild of TIMESTAMP tables is needed.
* If you have stored characters ASCII(32) to non-latin1 non-BINARY
indexed
columns in MySQL versions = 4.1.2, then you have to rebuild those tables
after you upgrade to = 4.1.3. The reason is that the sorting order of
those
characters and the space character changes for some character sets in
4.1.3.
See the MySQL/InnoDB-4.1.3 changelog for a precise description of the
cases
where you need to rebuild the table. Also MyISAM tables have to be rebuilt
or repaired in these cases.
* If you have used column prefix indexes on UTF-8 columns or other
multibyte
character set columns in 4.1.0 - 4.1.5, you have to rebuild the tables
when
you upgrade to 4.1.6 or later.
* If you have used accent characters (ASCII codes = 128) in database
names,
table names, constraint names, or column names in versions 4.1, you
cannot
upgrade to 4.1 directly, because 4.1 uses UTF-8 to store metadata names.
Use
RENAME TABLE to overcome this if the accent character is in the table name
or the database name, or rebuild the table.
Best regards,
Heikki Tuuri
Innobase Oy
http://www.innodb.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
auto_increm, forced step back
Hi, My understanding is that with mysql = 3.23 versions the last value of an auto_increm column is stored, thus even if records are deleted, when a new one is inserted (as NULL), values will not be re-used. This is a fine attribute, but is there any way to override it? That is, to bump back the counter by force? E.g. I have test: 1 2 3 4 and then delete 3 and 4: 1 2 and when inserting a new record by saying insert into test NULL , I would like it to become 3. Maybe I need a new table creation, and copy of current table in that, etc.? Cheers Gaspar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Repair with keycache
Hi, just had a problem with a myisam table who reached 4GB of data, I increased the number of rows by doing : ALTER TABLE foo MAX_ROWS=10 mysql server created some temporary files on disk ( 3 hours ) #sql-7ad2_d6cb95.MYD #sql-7ad2_d6cb95.MYI #sql-7ad2_d6cb95.frm and now the process is in state : Repair with keycache It seems to be really slow, how can I improve speed ? or how can I get more infos about what mysql is doing ? I try to set global variables like myisam_sort_buffer_size but it didn't work. I use mysql v 3.23.57 any help wil be appreciated. thanks. --- http://webmail.netbadri.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Repair with keycache
Mohamed Badri wrote: Hi, just had a problem with a myisam table who reached 4GB of data, I increased the number of rows by doing : ALTER TABLE foo MAX_ROWS=10 This is EXACTLY what you want: http://www.peerfear.org/rss/permalink/2004/10/16/MySQLAndALTERTABLEGuiltyAsCharged http://www.peerfear.org/rss/permalink/2004/10/15/MaxTableSizeInMySQL REPAIR TABLE considered harmful would be a better blog post title for these guys! Kevin -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LOAD DATA INFILE using 4.0.17
Hi, I have a table with 26 fields, each row in this table must be unique. I can't define all of the fields to be primary keys as the limit is 16. Therefore before I insert data I have to check that each row is unique. Data is to be inserted into this table from files uploaded to the server - CSV files. Is it possible to use the LOAD DATA INFILE statement to check for unique rows, or does anyone have a better solution to my problem! Thanks for any advice offered. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data in different tables or is one big table just as fast?
We have a table that grow by 200MB each day. Should we put data in different tables or is one big table just as fast? The table contains data from RSS and Atom feeds. Most users only need to see the newest items. A select could look like this: SELECT title, desc FROM items WHERE feedid = 25 ORDER BY id DESC LIMIT 10 I would, however, be seriously concerned about diskspace if a table is adding 200 MB a day with no archiving/compression/purges. What if we use COMPRESS() for the text in old rows? Jacob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increm, forced step back
Gaspar Bakos [EMAIL PROTECTED] wrote on 02/15/2005 04:28:26 PM: Hi, My understanding is that with mysql = 3.23 versions the last value of an auto_increm column is stored, thus even if records are deleted, when a new one is inserted (as NULL), values will not be re-used. This is a fine attribute, but is there any way to override it? That is, to bump back the counter by force? E.g. I have test: 1 2 3 4 and then delete 3 and 4: 1 2 and when inserting a new record by saying insert into test NULL , I would like it to become 3. Maybe I need a new table creation, and copy of current table in that, etc.? Cheers Gaspar -- What you are trying to accomplish is generally considered a bad practice. Autoincrement numbers should always increase as you add rows to the table (you can reset the next value using an ALTER TABLE) and generally they should not be messed with. If you add 4 rows (1,2,3,4) to a new table then delete rows 2 and 3, rows 1 and 4 remain. The next value to be added should be 5 (not 2 as it has been used already) It is generally a BAD THING to change a row's primary key after it has been created. But, in order to maintain your plan of compact numbering, that would be exactly what you want to happen. You will have to change the 4 to a 2 then issue an ALTER TABLE to reset the autoincrement counter. That's not only messing with any data relationships you once had (what if you had another record in a separate table that once pointed to row 4. What record would it point to now? Row 4 no longer exists. You broke the relationship just to maintain an artificial sequence) but you are working the database WAY too hard to keep those numbers in sequence. I highly suggest that you give up the idea of maintaining sequential primary keys and look into other ways of generating row numbers for your queries. I am not saying it's impossible to do what you ask but I am strongly discouraging it. It just makes so many other things harder if you do what you think you want to do. Most data manipulation libraries have sequence numbers or row numbers or recordset positions as part of the metadata returned with any query's result. You could use one of those values as a sequence number instead of actual table data, for example. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Data in different tables or is one big table just as fast?
how about purging rows older than a month? Do you need to keep them? Archive them them to another database? Actually, I got a better idea. Have your master db which is huge and holds everything. Then on a seperate DB run a table for each feedid with the last 100 feeds for that id. Have a cron job that runs continually updating those tables with current data. get it? On Tue, 15 Feb 2005 23:02:38 +0100, Jacob Friis Larsen [EMAIL PROTECTED] wrote: We have a table that grow by 200MB each day. Should we put data in different tables or is one big table just as fast? The table contains data from RSS and Atom feeds. Most users only need to see the newest items. A select could look like this: SELECT title, desc FROM items WHERE feedid = 25 ORDER BY id DESC LIMIT 10 I would, however, be seriously concerned about diskspace if a table is adding 200 MB a day with no archiving/compression/purges. What if we use COMPRESS() for the text in old rows? Jacob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Ryan McCullough mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE using 4.0.17
shaun thornburgh [EMAIL PROTECTED] wrote on 02/15/2005 04:53:54 PM: Hi, I have a table with 26 fields, each row in this table must be unique. I can't define all of the fields to be primary keys as the limit is 16. Therefore before I insert data I have to check that each row is unique. Data is to be inserted into this table from files uploaded to the server - CSV files. Is it possible to use the LOAD DATA INFILE statement to check for unique rows, or does anyone have a better solution to my problem! Thanks for any advice offered. What you want to do is called data scrubbing. Exactly how always depends on resources and the data itself however the general procedure works something like this: *create an import table that matches your source data *Import your data (without changes or omissions if at all possible) to this intermediate table. *Validate your imported data to make sure you have everything you wanted from the LOAD DATA INFILE command. *Eliminate any duplicate rows from your imported data table (many ways to do this. comparing the hash values for each row is one idea) *use your import table as the source for your final update/insert *drop your intermediate table. I would normally get into more details but it's time to head home. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Repair with keycache
thank you for the links, I can't change system variables at runtime, so the only choice I have is, probably, to stop mysql server set variables and then run another ALTER TABLE. ;-( I'm going to siwtch to mysql4 as soon as possible. Selon Kevin A. Burton [EMAIL PROTECTED]: Mohamed Badri wrote: Hi, just had a problem with a myisam table who reached 4GB of data, I increased the number of rows by doing : ALTER TABLE foo MAX_ROWS=10 This is EXACTLY what you want: http://www.peerfear.org/rss/permalink/2004/10/16/MySQLAndALTERTABLEGuiltyAsCharged http://www.peerfear.org/rss/permalink/2004/10/15/MaxTableSizeInMySQL REPAIR TABLE considered harmful would be a better blog post title for these guys! Kevin -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] --- http://webmail.netbadri.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE using 4.0.17
Hi, Thanks for your reply, but the problem I am facing is that there may be duplicate values in the uploaded file and I dont want these to appear in my table... From: Bastian Balthazar Bux [EMAIL PROTECTED] To: shaun thornburgh [EMAIL PROTECTED] Subject: Re: LOAD DATA INFILE using 4.0.17 Date: Tue, 15 Feb 2005 23:32:56 +0100 shaun thornburgh ha scritto: Hi, I have a table with 26 fields, each row in this table must be unique. I can't define all of the fields to be primary keys as the limit is 16. Therefore before I insert data I have to check that each row is unique. Data is to be inserted into this table from files uploaded to the server - CSV files. Is it possible to use the LOAD DATA INFILE statement to check for unique rows, or does anyone have a better solution to my problem! Thanks for any advice offered. make a table of 27 fields ;) , use the MD5() function http://dev.mysql.com/doc/mysql/en/encryption-functions.html , simply add a unique index on the column that holds the md5 sum. Regards, Francesco -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE using 4.0.17
So what you meant was every field in each row must be unique from all other instances in all other rows? Not just each row must be unique? Bob - Original Message - From: shaun thornburgh [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, February 15, 2005 2:40 PM Subject: Re: LOAD DATA INFILE using 4.0.17 Hi, Thanks for your reply, but the problem I am facing is that there may be duplicate values in the uploaded file and I dont want these to appear in my table... From: Bastian Balthazar Bux [EMAIL PROTECTED] To: shaun thornburgh [EMAIL PROTECTED] Subject: Re: LOAD DATA INFILE using 4.0.17 Date: Tue, 15 Feb 2005 23:32:56 +0100 shaun thornburgh ha scritto: Hi, I have a table with 26 fields, each row in this table must be unique. I can't define all of the fields to be primary keys as the limit is 16. Therefore before I insert data I have to check that each row is unique. Data is to be inserted into this table from files uploaded to the server - CSV files. Is it possible to use the LOAD DATA INFILE statement to check for unique rows, or does anyone have a better solution to my problem! Thanks for any advice offered. make a table of 27 fields ;) , use the MD5() function http://dev.mysql.com/doc/mysql/en/encryption-functions.html , simply add a unique index on the column that holds the md5 sum. Regards, Francesco -- 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]
insert...select with auto increment
How do you you get the next auto increment value? I think I'm running into the insert...select problem. I want to duplicate records from one table to the _same_ table, with an incremented auto increment value. Can I do something like this: insert into table (autoincrement_key, field1, field2) select (get_autoincrement(),field1,field2) from table where autoincrement_key = 31337 Not so far as I can tell. Unless I'm just not seeing it in the documentation. Is there a function to return the next autoincrement value? -steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE using 4.0.17
Sorry for the private answer hitted the wrong replay button.
It's possible for you unload data with an SQL like this ?
SELECT list, of, fields,
MD5 ( CONCAT ( list, of, fields ) )
INTO OUTFILE 'file_name'
FROM tab
if not (probably, you have csv files), you must use a shell script like
this (maybe slow)
#! /bin/sh
export SEP=;
while read myline ; do
echo ${myline}${SEP}$(echo ${myline} | md5sum | cut
--characters=-32)
done /etc/fstab
in M$ windows you must find an alternative.
BIG WARNINGs!
- This solution implies that forever you will be sticked to the same
method / program
- the md5 produced from the shell script will be different from the one
produced from the database
shaun thornburgh ha scritto:
Hi,
Thanks for your reply, but the problem I am facing is that there may
be duplicate values in the uploaded file and I dont want these to appear
in my table...
From: Bastian Balthazar Bux [EMAIL PROTECTED]
To: shaun thornburgh [EMAIL PROTECTED]
Subject: Re: LOAD DATA INFILE using 4.0.17
Date: Tue, 15 Feb 2005 23:32:56 +0100
shaun thornburgh ha scritto:
Hi,
I have a table with 26 fields, each row in this table must be
unique. I can't define all of the fields to be primary keys as the limit
is 16. Therefore before I insert data I have to check that each row is
unique. Data is to be inserted into this table from files uploaded to
the server - CSV files. Is it possible to use the LOAD DATA INFILE
statement to check for unique rows, or does anyone have a better
solution to my problem!
Thanks for any advice offered.
make a table of 27 fields ;) , use the MD5() function
http://dev.mysql.com/doc/mysql/en/encryption-functions.html , simply add
a unique index on the column that holds the md5 sum.
Regards, Francesco
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LOAD DATA INFILE using 4.0.17
No just every row needs to be unique. Sorry for the confusion... From: Robert Dunlop [EMAIL PROTECTED] To: shaun thornburgh [EMAIL PROTECTED],mysql@lists.mysql.com Subject: Re: LOAD DATA INFILE using 4.0.17 Date: Tue, 15 Feb 2005 15:06:19 -0800 So what you meant was every field in each row must be unique from all other instances in all other rows? Not just each row must be unique? Bob - Original Message - From: shaun thornburgh [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, February 15, 2005 2:40 PM Subject: Re: LOAD DATA INFILE using 4.0.17 Hi, Thanks for your reply, but the problem I am facing is that there may be duplicate values in the uploaded file and I dont want these to appear in my table... From: Bastian Balthazar Bux [EMAIL PROTECTED] To: shaun thornburgh [EMAIL PROTECTED] Subject: Re: LOAD DATA INFILE using 4.0.17 Date: Tue, 15 Feb 2005 23:32:56 +0100 shaun thornburgh ha scritto: Hi, I have a table with 26 fields, each row in this table must be unique. I can't define all of the fields to be primary keys as the limit is 16. Therefore before I insert data I have to check that each row is unique. Data is to be inserted into this table from files uploaded to the server - CSV files. Is it possible to use the LOAD DATA INFILE statement to check for unique rows, or does anyone have a better solution to my problem! Thanks for any advice offered. make a table of 27 fields ;) , use the MD5() function http://dev.mysql.com/doc/mysql/en/encryption-functions.html , simply add a unique index on the column that holds the md5 sum. Regards, Francesco -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Time in VBA for Excel
Dan Wareham wrote: Hey Dan, Thanks for the post and the code ideas. Unfortuantely I still can't get the thing to work even when trying the CONCAT and CAST functions. As per your request, here is the details of what I have got so far: I'm running MySQL 4.1.9 with MyODBC 3.51. The table in question is the Users table which has been setup as follows Before I even get to a solution, here are some tips for next time. When you post details of your setup, try to make it easy to reproduce your setup. It took me 15 minutes to get your test case running on my setup. - use mysql's show create table to create a valid 'create table' statement, or ( even better ): - use mysql's mysqldump to dump the contents of the table you want - don't use DSNs - convert it to a DNS-less connection - I don't have a DSN set up for your test case - if your test case involves data in a spreadsheet, either include the spreadsheet, or alter the code to not rely on this data - include ALL table definitions in the query - you didn't mention the Categories table in your post ( apart from in the SQL ) - don't switch between upper and lower case. You mention a 'USERS' table, and then your code refers to 'Users' --- Now as for the solution, I'm getting time-looking values by using the following: select ud.UserCode, cast(ud.StartTime as char) as StartTime, cast(ud.EndTime as char) as EndTime etc In my previous post I think I said to use 'cast(ud.StartTime as varchar(50))', which doesn't work - I wasn't near a MySQL install, but the basic idea is right, you just have to search for 'cast' on the mysql web site to get the correct usage ( varchar isn't one of the options you can cast to ). -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database creation privileges
Hi all, ok, I thought I had it figured out. I am using 4.1.9 now, and it looks like it behaves a little bit differently (or maybe not) than the previous 4.0.20 did when it comes to privileges. I want to create a user that does not have the ability to create databases. But, I do want them to be able to create tables in a specific database. Currently, I create the user in the global user table, and give then no privileges. Then, when I create a database, I assign them the privileges to that database by using a command like this : GRANT select,insert,update,delete,create,drop ON dbname.* to username@'%' identified by 'userpass'; This seems to work, but when that user logs in, they are able to create a database ! If I don't have the create privilege specified, then they aren't able to create tables, which I want them to be able to do... Is there a way to assign a user to a database, and give them the ability to do anything within that database, but not create another database Thanks, Tim. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database creation privileges
Hi all, ok, I thought I had it figured out. I am using 4.1.9 now, and it looks like it behaves a little bit differently (or maybe not) than the previous 4.0.20 did when it comes to privileges. I want to create a user that does not have the ability to create databases. But, I do want them to be able to create tables in a specific database. Currently, I create the user in the global user table, and give then no privileges. Then, when I create a database, I assign them the privileges to that database by using a command like this : GRANT select,insert,update,delete,create,drop ON dbname.* to username@'%' identified by 'userpass'; This seems to work, but when that user logs in, they are able to create a database ! If I don't have the create privilege specified, then they aren't able to create tables, which I want them to be able to do... Is there a way to assign a user to a database, and give them the ability to do anything within that database, but not create another database Thanks, Tim. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Repair with keycache
Mohamed Badri wrote: thank you for the links, I can't change system variables at runtime, so the only choice I have is, probably, to stop mysql server set variables and then run another ALTER TABLE. ;-( I'm going to siwtch to mysql4 as soon as possible. OH!... yeah... if you're on a 4 ver of mysql then I don't know what to tell you ;) Kevin -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Database creation privileges
-Original Message- From: Tim Traver Sent: Tuesday, February 15, 2005 19:30 To: mysql@lists.mysql.com Subject: Database creation privileges Hi all, ok, I thought I had it figured out. I am using 4.1.9 now, and it looks like it behaves a little bit differently (or maybe not) than the previous 4.0.20 did when it comes to privileges. I want to create a user that does not have the ability to create databases. But, I do want them to be able to create tables in a specific database. Currently, I create the user in the global user table, and give then no privileges. Then, when I create a database, I assign them the privileges to that database by using a command like this : GRANT select,insert,update,delete,create,drop ON dbname.* to username@'%' identified by 'userpass'; This seems to work, but when that user logs in, they are able to create a database ! Your grant statement should work fine on 4.1.9. Check the permissions with: SHOW GRANTS FOR 'username'@'%'; It should return the following: GRANT USAGE ON *.* TO 'username'@'%' IDENTIFIED BY PASSWORD 'password_hash' GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP ON `dbname`.* TO 'username'@'%' They will be able to drop the database dbname, but I doubt this is an issue since you want them to be able to have the ability to drop tables from this db anyway. Also when you connect as this user, try: SELECT CURRENT_USER(); Make sure that it returns [EMAIL PROTECTED], to verify that this connection is not falling under a different grant that does not have a wildcard. If you are connected as that user, then SHOW GRANTS FOR CURRENT_USER() will accomplish both of the above in one step. If I don't have the create privilege specified, then they aren't able to create tables, which I want them to be able to do... Is there a way to assign a user to a database, and give them the ability to do anything within that database, but not create another database Thanks, Tim. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert...select with auto increment
On Tue, 2005-02-15 at 17:56, steve cooley wrote: How do you you get the next auto increment value? I think I'm running into the insert...select problem. I want to duplicate records from one table to the _same_ table, with an incremented auto increment value. Can I do something like this: insert into table (autoincrement_key, field1, field2) select (get_autoincrement(),field1,field2) from table where autoincrement_key = 31337 Not so far as I can tell. Unless I'm just not seeing it in the documentation. Is there a function to return the next autoincrement value? -steve I don't think you can anticipate the value but, if I under stand you correctly, this aught to work (I assume you got 31337 from last_insert_id(): insert into table (field1, field2) select (field1,field2) from table where autoincrement_key = 31337 -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Data in different tables or is one big table just as fast?
how about purging rows older than a month? Do you need to keep them? Yes. Archive them them to another database? We are currently archiving them to another table, where we compress the text. Actually, I got a better idea. Have your master db which is huge and holds everything. Then on a seperate DB run a table for each feedid with the last 100 feeds for that id. That would be some 40 tables. What about the Drawbacks to Creating Many Tables in the Same Database http://dev.mysql.com/doc/mysql/en/creating-many-tables.html Have a cron job that runs continually updating those tables with current data. get it? I think so :) Thanks, Jacob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
