ANN: Advanced Data Generator 1.6.2 released
Dear ladies and gentlemen, Upscene Productions is happy to announce a new version of the database developer tool: Advanced Data Generator (version 1.6.2) A fast test-data generator tool that comes with a library of real-life data, can generate data to your database, SQL script or CSV files, many filling options, presets and much more. This new release consists of four versions: - Pro: ADO and ODBC connectivity - InterBase Edition - Firebird Edition - MySQL Edition More info and a 30-day trial version on www.upscene.com Pricing information available on www.upscene.com/purchase.htm#adg Recent changes include MySQL 5 Stored Procedure support (MySQL Edition), Microsoft SQL 2005 support (Pro), large font systems enhancements and bugfixes. With regards, Martijn Tonies Upscene Productions - Database Tools for Developers 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: UPDATE from one server to another
I am not that clever, but I would just create a Web Service (WS) on the Server that the Laptop call whenever the laptop want to push the date into the server database. I heard something about something called rsync, but I think that is restricted to Linx, Unix. /Peter -Original Message- From: Jason Dimberg [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 06, 2006 10:35 AM To: mysql@lists.mysql.com Subject: UPDATE from one server to another I am working on an application where data will be collected on laptops and then uploaded to a central database once the laptop is able to connect to the network after being in the field. I was initially thinking of using MS Access as a front end with linked tables through MySQL ODBC. I am now considering running WAMP on each machine with a web interface because there will be no interoperability issues if MySQL is the db server on both ends, but I am 1.) looking for any recommendations for the laptop interface (MS Access/WAMP or whatever other options might be available) and 2.) want to know what is the actual command for updating a table across two servers (this is NOT replication, but merely updating new data to an existing table). Data transfered will include binary objects and possibly GIS data. For example, Laptop 1 might have 10 rows of data from todays activities that need to be added to the main Server. Laptop 2 might have 30 rows of data that need to be added to the main Server. Neither laptop needs to have the data from the other, but the Server will contain data from both Laptops at the end of the day. The Server will then offer the data through a web interface. Server: Linux/MySQL 5.0 Laptops MS Access 2003 OR MySQL 5.0/ PHP 5/ Apache 2 Thank you. -- Jason -- 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: SELECT ALL and flag
hi, Peter
Try this:
select a.name, sum(if (b.table_a.id is NULL,0,1)) as indid from table_a a
left join table_b b on(a.id=b.table_id.id) group by a.id;
best regards,
Leo Huang
2006/6/1, Peter Lauri [EMAIL PROTECTED]:
Hi,
I have a table table_a and table_b:
table_a {
id
name
}
table_b {
table_a_id
b_value
}
Table A is a table with names, and table B is a table with values for a
specific name (optional, therefore a specific table).
I would like to select all records in A, done by:
SELECT name FROM table_a;
Returns:
Peter
Johan
Fredrik
But then I also would like to have a flag that flags if table_b, I want it
to return this if Peters id is the only one matching in table_b:
Peter 1
Johan 0
Fredrik 0
I tried:
SELECT name, if(table_a.id=table_b.table_a_id, 1, 0) AS indid FROM table_a
JOIN table_b;
But that generates multiple rows of the records in table_a. I tried GROUP
BY
in combination with ORDER BY, but I did not manage to get it to work.
How can I do that if() without having to action do a join, I just want to
check if it exist, and then give value 1 or 0.
Anyone with ideas?
/Peter
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
Delphi and MySQL (Encoding Problems)
Hello, I have been using MySQL version 4.0 because I have experienced problems since version 4.1 and later versions... I tried to update to version 5.0 but problems remain, in short Due to my stupidity I had been using latin1 encoding for my database even though everything seemed quite smooth. Data stored was retrieved fine and my Delphi applications (Delphi 5) run perfectly... When I dumped my database I took some steps on the resulted .sql backup file and managed to turn the database into greek encoding by specifying the encoding in on the table definitions of the backup file. I set /*!40101 SET NAMES greek */; on the header and ENGINE=MyISAM DEFAULT CHARSET=greek; at the end of every table definition. That way managed to get the data running smoothly for the specified greek encoding through some MySQL management utilities that connect to MySQL on version 5.0, so I assume I have correctly turned into GREEK encoding. The problem occures in DELPHI. Whenever I perform a select statement the result is that whenever there is a Greek character appears as the char(63) or ?. There is one ? per greek char (not two ?? per greek char, so we can assume utf8). I seem to be unable to find any sources about delphi on how to specify an encoding, and I would like to know if there is a way arround that problem. I had to roll back to version 4.0 of MySQL so that my utility runs correctly... Please any advice?
Copy large amount of data from non-normalized to normalized table
I need to upgrade a MyISAM DB, one of the tables has the structure: CREATE TABLE old_event_data ( event_id MEDIUMINT UNSIGNED NOT NULL, p1 UNSIGNED INT NOT NULL, ... p30 UNSIGNED INT NOT NULL, part UNSIGNED TINYINT(1) NOT NULL ); This is 'event data', each event having 6 entries with part taking values 0...5 I want a normalized structure where the 6 entries for each event are expanded into 180 rows in the structure: CREATE TABLE new_event_data ( event_id MEDIUMINT UNSIGNED NOT NULL, `index` TINYINT(3) UNSIGNED NOT NULL, p UNSIGNED NOT NULL ); So each of the old rows splits into 30 new rows. The existing table has about 85000 rows, equating to over 15 million in the new structure. Ways I have tried end up creating massive queries or just hogging the server for absolutely ages - what is the best way to perform this update? --Rob __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Delphi and MySQL (Encoding Problems)
Ioannis Anifantakis wrote: Hello, I have been using MySQL version 4.0 because I have experienced problems since version 4.1 and later versions... I tried to update to version 5.0 but problems remain, in short Due to my stupidity I had been using latin1 encoding for my database even though everything seemed quite smooth. Data stored was retrieved fine and my Delphi applications (Delphi 5) run perfectly... When I dumped my database I took some steps on the resulted .sql backup file and managed to turn the database into greek encoding by specifying the encoding in on the table definitions of the backup file. I set /*!40101 SET NAMES greek */; on the header and ENGINE=MyISAM DEFAULT CHARSET=greek; at the end of every table definition. That way managed to get the data running smoothly for the specified greek encoding through some MySQL management utilities that connect to MySQL on version 5.0, so I assume I have correctly turned into GREEK encoding. The problem occures in DELPHI. Whenever I perform a select statement the result is that whenever there is a Greek character appears as the char(63) or ?. There is one ? per greek char (not two ?? per greek char, so we can assume utf8). I seem to be unable to find any sources about delphi on how to specify an encoding, and I would like to know if there is a way arround that problem. I had to roll back to version 4.0 of MySQL so that my utility runs correctly... Please any advice? Why don't you change the default encoding of your MySQL server? This is appropriate if you have no other databases that use other encodings for they data. I personally use UTF-8. Put this in your configuration file: [mysqld] init_connect='SET NAMES greek' Or you can issue this statement just after connecting to the server: SET NAMES greek Good luck! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DateTime limits
To whom it may concern, I'm involved in lots of C# coding with several coders. I have a gripe with MySQL which may be easy to solve in future development. C# has two DateTime constants: DateTime.MinValue = '0001-01-01 00:00:00.000' DateTime.MaxValue = '-12-31 23:59:59.999' These are very useful to denote a date always beyond some data, or a date always before some data. Further, C# does not let you set DateTime to NULL. (very annoying!) These values therefore tend to creep into code where NULL would be better used. MySQL really doesn't like these values, it shows warnings: +-+--+-+ | Level | Code | Message | +-+--+-+ | Warning | 1292 | Incorrect datetime value: '0001-01-01 00:00:00' | +-+--+-+ The real problem with these warning is: 1. This date is legal, if a little unlikely. 2. Any warning crash MySql.Data.dll!!! If there is a kindly developer watching these mailing lists, can I suggest MySQL accepts these values, at least for comparisons, a little cleaner? For what it's worth, and to help us C# coders... Ben -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: UPDATE from one server to another
rsync is a *nix utility that synchronizes two file systems, one local and one remote (typically). It is used to produce mirrors / backups / etc. You would not want to use it to synchronize database (raw) files via the file system. If you include a timestamp field, you can use that to copy across all records that are newer then the last time you uploaded, or, greater then the most-recent date in the main database. Tim rsync(1) - faster, flexible replacement for rcp DESCRIPTION rsync is a program that behaves in much the same way that rcp does, but has many more options and uses the rsync remote-update protocol to greatly speed up file transfers when the destination file is being updated. The rsync remote-update protocol allows rsync to transfer just the differences between two sets of files across the network connection, using an efficient checksum-search algorithm described in the techni- cal report that accompanies this package. -Original Message- From: Peter Lauri [mailto:[EMAIL PROTECTED] Sent: Monday, June 05, 2006 10:17 PM To: 'Jason Dimberg'; mysql@lists.mysql.com Subject: RE: UPDATE from one server to another I am not that clever, but I would just create a Web Service (WS) on the Server that the Laptop call whenever the laptop want to push the date into the server database. I heard something about something called rsync, but I think that is restricted to Linx, Unix. /Peter -Original Message- From: Jason Dimberg [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 06, 2006 10:35 AM To: mysql@lists.mysql.com Subject: UPDATE from one server to another I am working on an application where data will be collected on laptops and then uploaded to a central database once the laptop is able to connect to the network after being in the field. I was initially thinking of using MS Access as a front end with linked tables through MySQL ODBC. I am now considering running WAMP on each machine with a web interface because there will be no interoperability issues if MySQL is the db server on both ends, but I am 1.) looking for any recommendations for the laptop interface (MS Access/WAMP or whatever other options might be available) and 2.) want to know what is the actual command for updating a table across two servers (this is NOT replication, but merely updating new data to an existing table). Data transfered will include binary objects and possibly GIS data. For example, Laptop 1 might have 10 rows of data from todays activities that need to be added to the main Server. Laptop 2 might have 30 rows of data that need to be added to the main Server. Neither laptop needs to have the data from the other, but the Server will contain data from both Laptops at the end of the day. The Server will then offer the data through a web interface. Server: Linux/MySQL 5.0 Laptops MS Access 2003 OR MySQL 5.0/ PHP 5/ Apache 2 Thank you. -- Jason -- 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: DateTime limits
Ben Clewett schrieb: To whom it may concern, I'm involved in lots of C# coding with several coders. I have a gripe with MySQL which may be easy to solve in future development. C# has two DateTime constants: DateTime.MinValue = '0001-01-01 00:00:00.000' DateTime.MaxValue = '-12-31 23:59:59.999' These are very useful to denote a date always beyond some data, or a date always before some data. Further, C# does not let you set DateTime to NULL. (very annoying!) These values therefore tend to creep into code where NULL would be better used. MySQL really doesn't like these values, it shows warnings: +-+--+-+ | Level | Code | Message | +-+--+-+ | Warning | 1292 | Incorrect datetime value: '0001-01-01 00:00:00' | +-+--+-+ The real problem with these warning is: 1. This date is legal, if a little unlikely. 2. Any warning crash MySql.Data.dll!!! If there is a kindly developer watching these mailing lists, can I suggest MySQL accepts these values, at least for comparisons, a little cleaner? For what it's worth, and to help us C# coders... Ben When does this happen? What query had you run? A more insight might help fix the problem ;) Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DateTime limits
Hi Barry, This will happen when comparing against a TIMESTAMP field. CREATE TABLE a ( t TIMESTAMP ); SELECT * FROM a WHERE t '0001-01-01 00:00:00'; I understand that TIMESTAMP cannot handle this date. But I would hope to be able to compare against this date without MySQL giving the warnings which crash MySql.Data.dll. MySQL version 5.1.6 Just a suggestion for later versions of MySQL :) Ben Barry wrote: Ben Clewett schrieb: To whom it may concern, I'm involved in lots of C# coding with several coders. I have a gripe with MySQL which may be easy to solve in future development. C# has two DateTime constants: DateTime.MinValue = '0001-01-01 00:00:00.000' DateTime.MaxValue = '-12-31 23:59:59.999' These are very useful to denote a date always beyond some data, or a date always before some data. Further, C# does not let you set DateTime to NULL. (very annoying!) These values therefore tend to creep into code where NULL would be better used. MySQL really doesn't like these values, it shows warnings: +-+--+-+ | Level | Code | Message | +-+--+-+ | Warning | 1292 | Incorrect datetime value: '0001-01-01 00:00:00' | +-+--+-+ The real problem with these warning is: 1. This date is legal, if a little unlikely. 2. Any warning crash MySql.Data.dll!!! If there is a kindly developer watching these mailing lists, can I suggest MySQL accepts these values, at least for comparisons, a little cleaner? For what it's worth, and to help us C# coders... Ben When does this happen? What query had you run? A more insight might help fix the problem ;) Barry -- Ben Clewett +44(0)1923 46 Project Manager Road Tech Computer Systems Ltd http://www.roadrunner.uk.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Copy large amount of data from non-normalized to normalized table
Rob, to clarify, your new 'index' column will be based on the value of the 'part' column and individual column names from the old table? Perhaps something like this, where [colnum] is derived from column name like p1? (part+1)*[colnum] Dan Rob Desbois wrote: I need to upgrade a MyISAM DB, one of the tables has the structure: CREATE TABLE old_event_data ( event_id MEDIUMINT UNSIGNED NOT NULL, p1 UNSIGNED INT NOT NULL, ... p30 UNSIGNED INT NOT NULL, part UNSIGNED TINYINT(1) NOT NULL ); This is 'event data', each event having 6 entries with part taking values 0...5 I want a normalized structure where the 6 entries for each event are expanded into 180 rows in the structure: CREATE TABLE new_event_data ( event_id MEDIUMINT UNSIGNED NOT NULL, `index` TINYINT(3) UNSIGNED NOT NULL, p UNSIGNED NOT NULL ); So each of the old rows splits into 30 new rows. The existing table has about 85000 rows, equating to over 15 million in the new structure. Ways I have tried end up creating massive queries or just hogging the server for absolutely ages - what is the best way to perform this update? --Rob __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
re[2]: Copy large amount of data from non-normalized to normalized table
Rob, to clarify, your new 'index' column will be based on the value of the 'part' column and individual column names from the old table? That is correct. Perhaps something like this, where [colnum] is derived from column name like p1? (part+1)*[colnum] The actual formula I want to use is: `index` = (30 * part) + [colnum] The problem is I don't know how to implement this in an SQL statement - what I want is something like an INSERT...SELECT which can split the SELECTed columns of a single row (id, c1, c2, c3) into multiple rows in the INSERT: (id, 1, c1), (id, 2, c2), (id, 3, c3). Afaik there is no such thing so I need an equivalent method - one that isn't going to kill my server (like the several attempts I've made so far!) --Rob __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Copy large amount of data from non-normalized to normalized table
There may be a technique for that in SQL, but I sure don't know it.
I'd likely approach this problem with an external language like perl.
You'll still end up processing about 15 million inserts, so it'll take a
while, but it shouldn't actually end up being all that rough on your
database server (a simple select and a bunch of simple inserts).
Here's a rough outline of what I'd do (in pseudo-code) ...
while $event = select * from old_event_data #(select as hashref)
foreach my $colnum (1..30)
my $event_id = $event{event_id}
my $indexval = ($event{part} * 30) + $colnum
my $value = $event{p$colnum}
insert into new_event_data ($event_id, $indexval, $value)
end
end
To speed up the inserts a bit, you could borrow a technique used in
bulk-loading data and disable keys on the new table prior to running
this - then re-enable them when it's all done. Might save some time
overall.
Hope this helps.
Dan
Rob Desbois wrote:
Rob, to clarify, your new 'index' column will be based on the value of
the 'part' column and individual column names from the old table?
That is correct.
Perhaps something like this, where [colnum] is derived from column name
like p1? (part+1)*[colnum]
The actual formula I want to use is:
`index` = (30 * part) + [colnum]
The problem is I don't know how to implement this in an SQL statement - what I
want is something like an INSERT...SELECT which can split the SELECTed columns
of a single row (id, c1, c2, c3) into multiple rows in the INSERT: (id, 1, c1),
(id, 2, c2), (id, 3, c3).
Afaik there is no such thing so I need an equivalent method - one that isn't
going to kill my server (like the several attempts I've made so far!)
--Rob
__
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
__
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DateTime limits
On Tuesday 06 June 2006 15:38, [EMAIL PROTECTED] wrote: Quoting Barry [EMAIL PROTECTED]: Well my msql doesn't give me any errors using that query. neither a warning. Ditto. usemysql use test; Database changed mysql CREATE TABLE a ( t TIMESTAMP ); Query OK, 0 rows affected (0.27 sec) mysql SELECT * FROM a WHERE t '0001-01-01 00:00:00'; Empty set, 1 warning (0.21 sec) mysql show warnings; +-+--+---+ | Level | Code | Message | +-+--+---+ | Warning | 1292 | Truncated incorrect datetime value: '0001-01-01 00:00:00' | +-+--+---+ Scanned by mailCritical. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DateTime limits
Quoting Barry [EMAIL PROTECTED]: Ben Clewett schrieb: Hi Barry, This will happen when comparing against a TIMESTAMP field. CREATE TABLE a ( t TIMESTAMP ); SELECT * FROM a WHERE t '0001-01-01 00:00:00'; Well my msql doesn't give me any errors using that query. neither a warning. Ditto. usemysql use test; Database changed mysql CREATE TABLE a ( t TIMESTAMP ); Query OK, 0 rows affected (0.27 sec) mysql SELECT * FROM a WHERE t '0001-01-01 00:00:00'; Empty set, 1 warning (0.21 sec) mysql SELECT VERSION(); ++ | VERSION() | ++ | 5.0.21-log | ++ 1 row in set (0.05 sec) mysql DROP TABLE a; Query OK, 0 rows affected (0.04 sec) mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DateTime limits
Duncan Hill wrote: On Tuesday 06 June 2006 15:38, [EMAIL PROTECTED] wrote: Quoting Barry [EMAIL PROTECTED]: Well my msql doesn't give me any errors using that query. neither a warning. Ditto. usemysql use test; Database changed mysql CREATE TABLE a ( t TIMESTAMP ); Query OK, 0 rows affected (0.27 sec) mysql SELECT * FROM a WHERE t '0001-01-01 00:00:00'; Empty set, 1 warning (0.21 sec) mysql show warnings; +-+--+---+ | Level | Code | Message | +-+--+---+ | Warning | 1292 | Truncated incorrect datetime value: '0001-01-01 00:00:00' | +-+--+---+ Scanned by mailCritical. Fails here, with no warnings however -- but we have an older 4.0.x version... Wouldn't a DATETIME field be better used in this case? When I use the same queries on a DATETIME field, this works ok. Maybe there is some reason in a larger context that hasn't been explained yet -- Thanks, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DateTime limits
Hi Barry, This is what I get: mysql CREATE TABLE a ( t TIMESTAMP ); Query OK, 0 rows affected (0.25 sec) mysql SELECT * FROM a WHERE t '0001-01-01 00:00:00'; Empty set, 1 warning (0.00 sec) mysql SHOW WARNINGS; +-+--+-+ | Level | Code | Message | +-+--+-+ | Warning | 1292 | Incorrect datetime value: '0001-01-01 00:00:00' for column 't' at row 1 | +-+--+-+ 1 row in set (0.00 sec) You can see MySQL complaining about the time format. Although I am using .NET, I am not using any windows code. This is MySQL on UNIX and .NET under Mono/Linux. As I said, this warning is annoying. It falls on a bug in the .NET MySQL library and causes it to crash nastily. Personally it annoys me because this warning is a bit of a misnomer. I am not assigning this value, only comparing to it... (I know that TIMESTAMP has a far smaller date range than DATETIME. But all our data has to be time-zone independent. Therefore TIMESTAMP is the only field appropriate for our use.) But this is not a 'religious' thing. We can easily code around it. Until we hit a place we have forgotten this and crash Mono... So, I am only asking nicely to any kind MySQL developer on this list, please remove this warning :) Ben Barry wrote: Ben Clewett schrieb: Hi Barry, This will happen when comparing against a TIMESTAMP field. CREATE TABLE a ( t TIMESTAMP ); SELECT * FROM a WHERE t '0001-01-01 00:00:00'; Well my msql doesn't give me any errors using that query. neither a warning. This might be a problem with windows. Windows has his problem using dates before 1st april 1970. Probably this is here going wrong. you can always compare ISO datetime formats to timestamps, Datetime fields or whatever. Barry -- Ben Clewett +44(0)1923 46 Project Manager Road Tech Computer Systems Ltd http://www.roadrunner.uk.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DateTime limits
Ben Clewett schrieb: Hi Barry, This will happen when comparing against a TIMESTAMP field. CREATE TABLE a ( t TIMESTAMP ); SELECT * FROM a WHERE t '0001-01-01 00:00:00'; Well my msql doesn't give me any errors using that query. neither a warning. This might be a problem with windows. Windows has his problem using dates before 1st april 1970. Probably this is here going wrong. you can always compare ISO datetime formats to timestamps, Datetime fields or whatever. Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DateTime limits
Ben Clewett schrieb: Hi Barry, This is what I get: mysql CREATE TABLE a ( t TIMESTAMP ); Query OK, 0 rows affected (0.25 sec) mysql SELECT * FROM a WHERE t '0001-01-01 00:00:00'; Empty set, 1 warning (0.00 sec) mysql SHOW WARNINGS; +-+--+-+ | Level | Code | Message| +-+--+-+ | Warning | 1292 | Incorrect datetime value: '0001-01-01 00:00:00' for column 't' at row 1 | +-+--+-+ 1 row in set (0.00 sec) You can see MySQL complaining about the time format. Although I am using .NET, I am not using any windows code. This is MySQL on UNIX and .NET under Mono/Linux. As I said, this warning is annoying. It falls on a bug in the .NET MySQL library and causes it to crash nastily. Personally it annoys me because this warning is a bit of a misnomer. I am not assigning this value, only comparing to it... (I know that TIMESTAMP has a far smaller date range than DATETIME. But all our data has to be time-zone independent. Therefore TIMESTAMP is the only field appropriate for our use.) But this is not a 'religious' thing. We can easily code around it. Until we hit a place we have forgotten this and crash Mono... So, I am only asking nicely to any kind MySQL developer on this list, please remove this warning :) Well removing 'explicit' warinings for every user having problems with 3rd party modules would have mysql without any warnings nowadays ;) i think that your mono should get more stable. i'm also using an older version of mysql this might be the readon why i don't get the arning. probably downgrade or well a path around might work also. I don't think any developer will remove that warning for that. ;) -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
recontruct database from NORMAL logs : how ?
Hi all, We have a copy of an old version of a database and a huge LOG file ( 9 Gig ) ( not the binary log unfortunately but the GENERAL QUERY LOG ) started when we put this database live. The only solution I see would be to run the queries from this log file to the database. What do you think? Is there any script that will enable me to get the queries concerning only one database from the General query log? Any comment is welcome. thanks in advance, -- Dilipan Sebastiampillai Systems - DBA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select query problem
Nenad Bosanac schrieb: Hi I have one problem that i can`t resolve. still need advice or is it solved? -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DateTime limits
Hi Barry, Well removing 'explicit' warnings for every user having problems with 3rd party modules would have mysql without any warnings nowadays ;) i think that your mono should get more stable. I completely take this on board. This is a bug outside MySQL. Warnings are very useful. When we test code and get a warning, this almost always results in us re-coding. Eg: ... WHERE '2006-06-06' '2006-06-06 12:00:00' This will give a valid warning, this is bad SQL, time for a re-code! BUT my warning is a special case: - It's not bad SQL. - Because of the C# DateTime object, this happens often. Therefore a candidate for dropping. Which will also avoid bugs in badly written MySQL clients :) But, I've probably said enough on the point :) Ben PS, another solution to our specific problem would be if the DATETIME could be made time-zone aware. Maybe something like: CREATE TABLE a ( t DATETIME WITH TIME_ZONE ) But this is a much bigger question! Barry wrote: Ben Clewett schrieb: Hi Barry, This is what I get: mysql CREATE TABLE a ( t TIMESTAMP ); Query OK, 0 rows affected (0.25 sec) mysql SELECT * FROM a WHERE t '0001-01-01 00:00:00'; Empty set, 1 warning (0.00 sec) mysql SHOW WARNINGS; +-+--+-+ | Level | Code | Message| +-+--+-+ | Warning | 1292 | Incorrect datetime value: '0001-01-01 00:00:00' for column 't' at row 1 | +-+--+-+ 1 row in set (0.00 sec) You can see MySQL complaining about the time format. Although I am using .NET, I am not using any windows code. This is MySQL on UNIX and .NET under Mono/Linux. As I said, this warning is annoying. It falls on a bug in the .NET MySQL library and causes it to crash nastily. Personally it annoys me because this warning is a bit of a misnomer. I am not assigning this value, only comparing to it... (I know that TIMESTAMP has a far smaller date range than DATETIME. But all our data has to be time-zone independent. Therefore TIMESTAMP is the only field appropriate for our use.) But this is not a 'religious' thing. We can easily code around it. Until we hit a place we have forgotten this and crash Mono... So, I am only asking nicely to any kind MySQL developer on this list, please remove this warning :) Well removing 'explicit' warinings for every user having problems with 3rd party modules would have mysql without any warnings nowadays ;) i think that your mono should get more stable. i'm also using an older version of mysql this might be the readon why i don't get the arning. probably downgrade or well a path around might work also. I don't think any developer will remove that warning for that. ;) -- Ben Clewett +44(0)1923 46 Project Manager Road Tech Computer Systems Ltd http://www.roadrunner.uk.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: procedure analyse() returns bad type?
Hi, Jeremy, RE: Looks like a bug. The code in question is: if (num_info.dval -FLT_MAX num_info.dval FLT_MAX) sprintf(buff, FLOAT(%d,%d), num_info.integers, num_info.decimals); else sprintf(buff, DOUBLE(%d,%d), num_info.integers, num_info.decimals); OK, thanks for the info. Seems like procedure analyse() has some other bugs, for example despite controlling the number of possible enum fields to e.g. 16, it returns an enum with 256 elements. Cheers, Gaspar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Security fix for 4.0.27?
On Mon, Jun 05, 2006 at 10:16:05PM -0700, Ken Williams wrote: Anyone know if 4.0.27 will be fixed for the mysql_real_escape issue? (http://lists.mysql.com/announce/364) 4.1 and 5 have been already, kinda wondering why 4.0 hasn't. It will not, because 4.0 does not have this bug. Jim Winstead MySQL Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
myisamchk location for the intermediate files TMD
Hello, Is it possible to tell myisamchk where the intermediate files (.TMD) must be created. Because my database are big and these not enough space in his directory. I try with --tmpdir but this doesn't work. I use myisamchk ver 2.7 and mysql 4.1.11 thank's Nuno -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DateTime limits
Ben Clewett wrote: (I know that TIMESTAMP has a far smaller date range than DATETIME. But all our data has to be time-zone independent. Therefore TIMESTAMP is the only field appropriate for our use.) try and see if this works SELECT * FROM a WHERE cast(t as datetime) '0001-01-01 00:00:00' I only have 4.1 so I don't know what would happen in 5 -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Passwords in Mysql5.x
Hi, what type of password algorithum does mysql 5.x uses for encrypting passwords? and how does these algorithum keeps the password in secure. Regards, Shivaji.
RE: Passwords in Mysql5.x
Hello, Have you taken a look at the following sections in the manual? http://dev.mysql.com/doc/refman/5.0/en/user-names.html http://dev.mysql.com/doc/refman/5.0/en/encryption-functions.html http://dev.mysql.com/doc/refman/5.0/en/password-hashing.html Thanks, Jimmy Guerrero Sr Product Manager MySQL, Inc -Original Message- From: Shivaji S [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 06, 2006 1:30 PM To: mysql@lists.mysql.com Subject: Passwords in Mysql5.x Hi, what type of password algorithum does mysql 5.x uses for encrypting passwords? and how does these algorithum keeps the password in secure. Regards, Shivaji. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DateTime limits
Ben Clewett wrote: snip C# has two DateTime constants: DateTime.MinValue = '0001-01-01 00:00:00.000' DateTime.MaxValue = '-12-31 23:59:59.999' snip MySQL really doesn't like these values, it shows warnings: +-+--+-+ | Level | Code | Message | +-+--+-+ | Warning | 1292 | Incorrect datetime value: '0001-01-01 00:00:00' | +-+--+-+ The real problem with these warning is: 1. This date is legal, if a little unlikely. 2. Any warning crash MySql.Data.dll!!! snip Ben Clewett wrote: Hi Barry, This will happen when comparing against a TIMESTAMP field. CREATE TABLE a ( t TIMESTAMP ); SELECT * FROM a WHERE t '0001-01-01 00:00:00'; snip Ben Clewett wrote: Hi Barry, Well removing 'explicit' warnings for every user having problems with 3rd party modules would have mysql without any warnings nowadays ;) i think that your mono should get more stable. I completely take this on board. This is a bug outside MySQL. Warnings are very useful. When we test code and get a warning, this almost always results in us re-coding. Eg: ... WHERE '2006-06-06' '2006-06-06 12:00:00' This will give a valid warning, this is bad SQL, time for a re-code! This will not give a warning, as it is perfectly valid sql. (It will simply return no rows, as the WHERE clause condition cannot be satisfied.) Why do you think otherwise? BUT my warning is a special case: - It's not bad SQL. Yes, it is bad sql. Your WHERE clause is WHERE t '0001-01-01 00:00:00' As t is a TIMESTAMP column, mysql must convert the DATETIME constant to a TIMESTAMP, but '0001-01-01 00:00:00' is not a valid TIMESTAMP. Hence the warning. - Because of the C# DateTime object, this happens often. Therefore a candidate for dropping. Which will also avoid bugs in badly written MySQL clients :) But, I've probably said enough on the point :) Ben According to the manual http://dev.mysql.com/doc/refman/5.0/en/date-and-time-type-overview.html, the officially supported range for DATETIME columns is '1000-01-01 00:00:00' to '-12-31 23:59:59', though '0001-01-01 00:00:00' seems to work in practice. From the same manual page, the officially supported range for TIMESTAMP columns is '1970-01-01 00:00:00' to partway through the year 2037. In practice, TIMESTAMP columns take timezone into account. Hence, as my offset from GMT is -5, the earliest TIMESTAMP I can get away with is '1969-12-31 19:00:01'. That is, SELECT * FROM a WHERE t '1969-12-31 19:00:00'; produes a warning, but SELECT * FROM a WHERE t '1969-12-31 19:00:01'; does not. I believe your problem begins with your assumption that DATETIME and TIMESTAMP are interchangeable. They are not. You would probably be fine using C#'s DateTime.MinValue = '0001-01-01 00:00:00.000' for actual DATETIME columns, but it just isn't valid for TIMESTAMP columns. That said, mysql manages to do the right thing when faced with this query. As '0001-01-01 00:00:00.000' is earlier than the first valid TIMESTAMP, it is converted to a TIMESTAMP of 0, one second before the earliest valid TIMESTAMP, and the query proceeds, giving the results you expected, I believe. It also issues a warning to let you know what it did. This is where the second problem comes into play. Your system crashes on warnings! As you've already admitted, this is a bug in the .NET MySQL library. The solution seems plain. The library must be fixed to handle warnings. Mysql should not be changed back to the old days of silently changing your query without issuing warnings. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Passwords in Mysql5.x
[snip] what type of password algorithum does mysql 5.x uses for encrypting passwords? and how does these algorithum keeps the password in secure. [/snip] Here is some helpful info; http://www.mysql.com/search/?q=password+securitycharset= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: UPDATE from one server to another
Can you run rsync on Windows environment? -Original Message- From: Tim Lucia [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 06, 2006 6:32 PM To: 'Peter Lauri'; 'Jason Dimberg'; mysql@lists.mysql.com Subject: RE: UPDATE from one server to another rsync is a *nix utility that synchronizes two file systems, one local and one remote (typically). It is used to produce mirrors / backups / etc. You would not want to use it to synchronize database (raw) files via the file system. If you include a timestamp field, you can use that to copy across all records that are newer then the last time you uploaded, or, greater then the most-recent date in the main database. Tim rsync(1) - faster, flexible replacement for rcp DESCRIPTION rsync is a program that behaves in much the same way that rcp does, but has many more options and uses the rsync remote-update protocol to greatly speed up file transfers when the destination file is being updated. The rsync remote-update protocol allows rsync to transfer just the differences between two sets of files across the network connection, using an efficient checksum-search algorithm described in the techni- cal report that accompanies this package. -Original Message- From: Peter Lauri [mailto:[EMAIL PROTECTED] Sent: Monday, June 05, 2006 10:17 PM To: 'Jason Dimberg'; mysql@lists.mysql.com Subject: RE: UPDATE from one server to another I am not that clever, but I would just create a Web Service (WS) on the Server that the Laptop call whenever the laptop want to push the date into the server database. I heard something about something called rsync, but I think that is restricted to Linx, Unix. /Peter -Original Message- From: Jason Dimberg [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 06, 2006 10:35 AM To: mysql@lists.mysql.com Subject: UPDATE from one server to another I am working on an application where data will be collected on laptops and then uploaded to a central database once the laptop is able to connect to the network after being in the field. I was initially thinking of using MS Access as a front end with linked tables through MySQL ODBC. I am now considering running WAMP on each machine with a web interface because there will be no interoperability issues if MySQL is the db server on both ends, but I am 1.) looking for any recommendations for the laptop interface (MS Access/WAMP or whatever other options might be available) and 2.) want to know what is the actual command for updating a table across two servers (this is NOT replication, but merely updating new data to an existing table). Data transfered will include binary objects and possibly GIS data. For example, Laptop 1 might have 10 rows of data from todays activities that need to be added to the main Server. Laptop 2 might have 30 rows of data that need to be added to the main Server. Neither laptop needs to have the data from the other, but the Server will contain data from both Laptops at the end of the day. The Server will then offer the data through a web interface. Server: Linux/MySQL 5.0 Laptops MS Access 2003 OR MySQL 5.0/ PHP 5/ Apache 2 Thank you. -- Jason -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: UPDATE from one server to another
Ask our mutual friend, Google. It has lots of answers for you. -Original Message- From: Peter Lauri [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 06, 2006 10:09 AM To: 'Tim Lucia'; 'Jason Dimberg'; mysql@lists.mysql.com Subject: RE: UPDATE from one server to another Can you run rsync on Windows environment? -Original Message- From: Tim Lucia [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 06, 2006 6:32 PM To: 'Peter Lauri'; 'Jason Dimberg'; mysql@lists.mysql.com Subject: RE: UPDATE from one server to another rsync is a *nix utility that synchronizes two file systems, one local and one remote (typically). It is used to produce mirrors / backups / etc. You would not want to use it to synchronize database (raw) files via the file system. If you include a timestamp field, you can use that to copy across all records that are newer then the last time you uploaded, or, greater then the most-recent date in the main database. Tim rsync(1) - faster, flexible replacement for rcp DESCRIPTION rsync is a program that behaves in much the same way that rcp does, but has many more options and uses the rsync remote-update protocol to greatly speed up file transfers when the destination file is being updated. The rsync remote-update protocol allows rsync to transfer just the differences between two sets of files across the network connection, using an efficient checksum-search algorithm described in the techni- cal report that accompanies this package. -Original Message- From: Peter Lauri [mailto:[EMAIL PROTECTED] Sent: Monday, June 05, 2006 10:17 PM To: 'Jason Dimberg'; mysql@lists.mysql.com Subject: RE: UPDATE from one server to another I am not that clever, but I would just create a Web Service (WS) on the Server that the Laptop call whenever the laptop want to push the date into the server database. I heard something about something called rsync, but I think that is restricted to Linx, Unix. /Peter -Original Message- From: Jason Dimberg [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 06, 2006 10:35 AM To: mysql@lists.mysql.com Subject: UPDATE from one server to another I am working on an application where data will be collected on laptops and then uploaded to a central database once the laptop is able to connect to the network after being in the field. I was initially thinking of using MS Access as a front end with linked tables through MySQL ODBC. I am now considering running WAMP on each machine with a web interface because there will be no interoperability issues if MySQL is the db server on both ends, but I am 1.) looking for any recommendations for the laptop interface (MS Access/WAMP or whatever other options might be available) and 2.) want to know what is the actual command for updating a table across two servers (this is NOT replication, but merely updating new data to an existing table). Data transfered will include binary objects and possibly GIS data. For example, Laptop 1 might have 10 rows of data from todays activities that need to be added to the main Server. Laptop 2 might have 30 rows of data that need to be added to the main Server. Neither laptop needs to have the data from the other, but the Server will contain data from both Laptops at the end of the day. The Server will then offer the data through a web interface. Server: Linux/MySQL 5.0 Laptops MS Access 2003 OR MySQL 5.0/ PHP 5/ Apache 2 Thank you. -- Jason -- 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] -- 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: UPDATE from one server to another
On 6/6/06, Jason Dimberg [EMAIL PROTECTED] wrote: I am working on an application where data will be collected on laptops and then uploaded to a central database once the laptop is able to connect to the network after being in the field. I was initially thinking of using MS Access as a front end with linked tables through MySQL ODBC. I am now considering running WAMP on each machine with a web interface because there will be no interoperability issues if MySQL is the db server on both ends, but I am 1.) looking for any recommendations for the laptop interface (MS Access/WAMP or whatever other options might be available) and 2.) want to know what is the actual command for updating a table across two servers (this is NOT replication, but merely updating new data to an existing table). Data transfered will include binary objects and possibly GIS data. For example, Laptop 1 might have 10 rows of data from todays activities that need to be added to the main Server. Laptop 2 might have 30 rows of data that need to be added to the main Server. Neither laptop needs to have the data from the other, but the Server will contain data from both Laptops at the end of the day. The Server will then offer the data through a web interface. Server: Linux/MySQL 5.0 Laptops MS Access 2003 OR MySQL 5.0/ PHP 5/ Apache 2 Don't use ACCESS to deal with MySQL Data, you'll probably meet inconsistencies, along with bugs and problems with field types, besides, MS sucks... To sync the laptop with the actual database, you can use a Web Interface, or simply upload data via ftp or any other protocol (check for security) and write a script to add this data to MySQL. Are you sure you're not better served using the filesystem to store the data and simply indexing names, sizes, maybe a hash for security reasons (md5 or whatever) and timestamps so you can use the database to quickly search or organize files but offer them via HTTP or FTP? A simple app can get info about the file, upload it to the server and add a row to MySQL with the info and the location of the file in the filesystem. You would get something like: filesystem: /home/ftp/file1.bin mysql: name: file1.bin location: /home/ftp size: 1024 bytes hash: 78687hhg89686578h786 uploaded: 06/06/2006 14:30 from: laptop1 A simple web interface written in PHP or whatever can search this database, filter data, sort stuff and simply offer links to the ftp site... Just a suggestion... -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Copy large amount of data from non-normalized to normalized table
Rob Desbois wrote (06-06-2006 15:22): Rob, to clarify, your new 'index' column will be based on the value of the 'part' column and individual column names from the old table? That is correct. Perhaps something like this, where [colnum] is derived from column name like p1? (part+1)*[colnum] The actual formula I want to use is: `index` = (30 * part) + [colnum] The problem is I don't know how to implement this in an SQL statement - what I want is something like an INSERT...SELECT which can split the SELECTed columns of a single row (id, c1, c2, c3) into multiple rows in the INSERT: (id, 1, c1), (id, 2, c2), (id, 3, c3). Afaik there is no such thing so I need an equivalent method - one that isn't going to kill my server (like the several attempts I've made so far!) Run 30 INSERT ... SELECT ... One for each column. -- René Seindal ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query performance.
Hi, List! I'm a little bit confused with (IMHO) poor query performance. I have a table with 1'000'000 records. Table consists of 2 service fields and a number of data fields. Service fields are status and processor_id (added for concurrent queue processing). The question is why are updates so slow? A query like: UPDATE queue SET status=1 WHERE status=0 LIMIT 1; takes about 5 seconds while this SELECT * FROM queue WHERE status=0 LIMIT 1; takes 0.01-0.02 second. As I can see in process list most of the time query is Searching rows for update what's very strange. I thought UPDATE searches rows the same way SELECT does. Doesn't it? Actually, seems like it does, because if I remove all fields except for id and status, same both queries (SELECT UPDATE) work quite fast. So, why is my update query so slow? What can I do to make it work faster? Can I somehow find out what is the bottleneck here? May be I should increase some buffers or something else? I copied my-huge my.cnf sample from mysql distribution. I'm looking forward for any help because I'm stuck with this and don't know what to do. Thanks in advance to all! P.S.: Some table info: mysql show table status like 'queue'; +--++-++++-+-+--+---++-+-++--+--++---+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation| Checksum | Create_options | Comment | +--++-++++-+-+--+---++-+-++--+--++---+ | queue | InnoDB | 9 | Dynamic| 726423 |159 | 116031488 |NULL | 32555008 | 0 |101 | 2006-06-06 22:01:21 | NULL| NULL | koi8r_general_ci | NULL || InnoDB free: 68608 kB | +--++-++++-+-+--+---++-+-++--+--++---+ mysql show indexes from queue; +--++--+--+--+---+-+--++--++-+ | Table| Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--++--+--+--+---+-+--++--++-+ | queue | 0 | PRIMARY |1 | id | A | 1170633 | NULL | NULL | | BTREE | | | queue | 1 | status |1 | status | A | 18 | NULL | NULL | | BTREE | | | queue | 1 | processor_id |1 | processor_id | A | 18 | NULL | NULL | YES | BTREE | | +--++--+--+--+---+-+--++--++-+ 3 rows in set (0.01 sec) /etc/my.cnf: ... [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size = 32M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 4 ... -- BR, Eugene Kosov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query performance.
On 6/6/06, Eugene Kosov [EMAIL PROTECTED] wrote: Hi, List! I'm a little bit confused with (IMHO) poor query performance. I have a table with 1'000'000 records. Table consists of 2 service fields and a number of data fields. Service fields are status and processor_id (added for concurrent queue processing). The question is why are updates so slow? A query like: UPDATE queue SET status=1 WHERE status=0 LIMIT 1; takes about 5 seconds while this SELECT * FROM queue WHERE status=0 LIMIT 1; takes 0.01-0.02 second. As I can see in process list most of the time query is Searching rows for update what's very strange. I thought UPDATE searches rows the same way SELECT does. Doesn't it? Actually, seems like it does, because if I remove all fields except for id and status, same both queries (SELECT UPDATE) work quite fast. So, why is my update query so slow? What can I do to make it work faster? Can I somehow find out what is the bottleneck here? May be I should increase some buffers or something else? I copied my-huge my.cnf sample from mysql distribution. I'm looking forward for any help because I'm stuck with this and don't know what to do. Thanks in advance to all! P.S.: Some table info: mysql show table status like 'queue'; +--++-++++-+-+--+---++-+-++--+--++---+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation| Checksum | Create_options | Comment | +--++-++++-+-+--+---++-+-++--+--++---+ | queue | InnoDB | 9 | Dynamic| 726423 |159 | 116031488 |NULL | 32555008 | 0 |101 | 2006-06-06 22:01:21 | NULL| NULL | koi8r_general_ci | NULL || InnoDB free: 68608 kB | +--++-++++-+-+--+---++-+-++--+--++---+ mysql show indexes from queue; +--++--+--+--+---+-+--++--++-+ | Table| Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--++--+--+--+---+-+--++--++-+ | queue | 0 | PRIMARY |1 | id | A | 1170633 | NULL | NULL | | BTREE | | | queue | 1 | status |1 | status | A | 18 | NULL | NULL | | BTREE | | | queue | 1 | processor_id |1 | processor_id | A | 18 | NULL | NULL | YES | BTREE | | +--++--+--+--+---+-+--++--++-+ 3 rows in set (0.01 sec) /etc/my.cnf: ... [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size = 32M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 4 ... Check http://bugs.mysql.com/bug.php?id=12915 -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
XML - DB Conversion
Hi all, I'm currently looking for a tool that will take XML and produce a database from it. Not really looking for anything in particular at this point, just sort of scoping around. Anyone know of such a tool? -- Chris White PHP Programmer/DB Sloth Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INT wants a float/double?
Hi, I am using Java to maintain a MySQL database. I have a table in the database created by: CREATE TABLE track_char ( + id INT UNSIGNED NOT NULL AUTO_INCREMENT, + PRIMARY KEY (id), + name CHAR(40), + posX DOUBLE(10,1), + posY DOUBLE(10,1), + posZ DOUBLE(10,1), + rotX INT(10), + rotY INT(10), + rotZ INT(10)) Now, when I try to update the rotX, rotY, and rotZ fields, I have to put a double into it, ints don't work. But then, when I print out the database, it looks like this: 1 Tom 0.0 0.0 0.0 0 0 0 Which clearly indicates that it is storing the rot fields as ints. I am just wondering why I can't give it an int when I am updating the entry, even though what I am updating is an int?
RE: XML - DB Conversion
[snip] I'm currently looking for a tool that will take XML and produce a database from it. Not really looking for anything in particular at this point, just sort of scoping around. Anyone know of such a tool? [/snip] MySQL kinda' has a built-in tool http://dev.mysql.com/tech-resources/articles/mysql-5.1-xml.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Copy large amount of data from non-normalized to normalized table
Hi Rob,
So each of the old rows splits into 30 new rows.
Really 180 rows, right?
The existing table has about 85000 rows, equating to over 15 million in the new
structure.
Ways I have tried end up creating massive queries or just hogging the server
for absolutely ages - what is the best way to perform this update?
Doing it in a single query is possible, but not really feasible.
How about 180 queries, generated by:
for part in `seq 0 5`; do
for col in `seq 1 30`; do
echo INSERT INTO new_event_data (event_id, index, p) \
SELECT event_id, (30*${part})+${col} as index, p${col} \
FROM old_event_data;
done
done
Regards,
Jeremy
--
Jeremy Cole
MySQL Geek, Yahoo! Inc.
Desk: 408 349 5104
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INT wants a float/double?
[snip] Now, when I try to update the rotX, rotY, and rotZ fields, I have to put a double into it, ints don't work. But then, when I print out the database, it looks like this: Which clearly indicates that it is storing the rot fields as ints. I am just wondering why I can't give it an int when I am updating the entry, even though what I am updating is an int? [/snip] It does not clearly indicate that the rot fields are stored as INTs, far from it. You cannot 'give' an INT to a DOUBLE as that it is incorrect. Anytime you have strongly typed variables you must use them as they are typed. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
libmysqlclient_r.so not being created
Any idea why libmysqlclient_r.so is not being created when I compile after a configure --without-server? I did two other boxes this morning and everything went just fine. Now this one (RH 9) is not getting it's libraries created. The compile/install looks like it works just fine. --ja -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INT wants a float/double?
Right...the thing is that they are supposed to be INTs, but I can only successfully perform an update query if I use doubles for the fields...? -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 06, 2006 1:59 PM To: Thomas Amundsen; mysql@lists.mysql.com Subject: RE: INT wants a float/double? [snip] Now, when I try to update the rotX, rotY, and rotZ fields, I have to put a double into it, ints don't work. But then, when I print out the database, it looks like this: Which clearly indicates that it is storing the rot fields as ints. I am just wondering why I can't give it an int when I am updating the entry, even though what I am updating is an int? [/snip] It does not clearly indicate that the rot fields are stored as INTs, far from it. You cannot 'give' an INT to a DOUBLE as that it is incorrect. Anytime you have strongly typed variables you must use them as they are typed. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INT wants a float/double?
[snip] Right...the thing is that they are supposed to be INTs, but I can only successfully perform an update query if I use doubles for the fields...? [/snip] Do a describe on the table and make sure that they are INTs, then show us the update statement. because it is hard to read why? top posting is bad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INT wants a float/double?
Here is what it gave me when I did describe: +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | id| int(10) unsigned | NO | PRI | NULL| auto_increment | | name | char(40) | YES | | NULL|| | posX | double(10,1) | YES | | NULL|| | posY | double(10,1) | YES | | NULL|| | posZ | double(10,1) | YES | | NULL|| | rotX | int(10) | YES | | NULL|| | rotY | int(10) | YES | | NULL|| | rotZ | int(10) | YES | | NULL|| +---+--+--+-+-++ 8 rows in set (0.00 sec) Here is the update statement that works correctly: UPDATE track_char SET rotX = + Double.parseDouble(fields[i + 1]) + , rotY = + Double.parseDouble(fields[i + 2]) + , rotZ = + Double.parseDouble(fields[i + 3]) + WHERE name = 'Tom' where 'fields' is a string array of tokens that I have parsed from a message. I tried doing this: UPDATE track_char SET rotX = + Integer.parseInt(fields[i + 1]) + , rotY = + Int.parseInt(fields[i + 2]) + , rotZ = + Int.parseInt(fields[i + 3]) + WHERE name = 'Tom' but that wouldn't work. And yes, I have made sure that it isn't a problem with the parseInt() method by using hard-coded values...only hard-coded doubles work, hard-coded ints wouldn't. -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 06, 2006 2:09 PM To: Thomas Amundsen; mysql@lists.mysql.com Subject: RE: INT wants a float/double? [snip] Right...the thing is that they are supposed to be INTs, but I can only successfully perform an update query if I use doubles for the fields...? [/snip] Do a describe on the table and make sure that they are INTs, then show us the update statement. because it is hard to read why? top posting is bad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INT wants a float/double?
[snip] Here is what it gave me when I did describe: +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | id| int(10) unsigned | NO | PRI | NULL| auto_increment | | name | char(40) | YES | | NULL|| | posX | double(10,1) | YES | | NULL|| | posY | double(10,1) | YES | | NULL|| | posZ | double(10,1) | YES | | NULL|| | rotX | int(10) | YES | | NULL|| | rotY | int(10) | YES | | NULL|| | rotZ | int(10) | YES | | NULL|| +---+--+--+-+-++ 8 rows in set (0.00 sec) Here is the update statement that works correctly: UPDATE track_char SET rotX = + Double.parseDouble(fields[i + 1]) + , rotY = + Double.parseDouble(fields[i + 2]) + , rotZ = + Double.parseDouble(fields[i + 3]) + WHERE name = 'Tom' where 'fields' is a string array of tokens that I have parsed from a message. I tried doing this: UPDATE track_char SET rotX = + Integer.parseInt(fields[i + 1]) + , rotY = + Int.parseInt(fields[i + 2]) + , rotZ = + Int.parseInt(fields[i + 3]) + WHERE name = 'Tom' but that wouldn't work. And yes, I have made sure that it isn't a problem with the parseInt() method by using hard-coded values...only hard-coded doubles work, hard-coded ints wouldn't. [/snip] Do an update straight to the database and show us that without Java code. because it is hard to read why? top posting is bad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: procedure analyse() returns bad type?
Hi Gaspar, OK, thanks for the info. Seems like procedure analyse() has some other bugs, for example despite controlling the number of possible enum fields to e.g. 16, it returns an enum with 256 elements. By the way, I opened a bug for this issue: http://bugs.mysql.com/20305 Regards, Jeremy -- Jeremy Cole MySQL Geek, Yahoo! Inc. Desk: 408 349 5104 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INT wants a float/double?
It seems to work with INTs when I do it from the MySQL commandline. I guess something is weird with the JDBC connector or something... -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 06, 2006 2:26 PM To: Thomas Amundsen; mysql@lists.mysql.com Subject: RE: INT wants a float/double? [snip] Here is what it gave me when I did describe: +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | id| int(10) unsigned | NO | PRI | NULL| auto_increment | | name | char(40) | YES | | NULL|| | posX | double(10,1) | YES | | NULL|| | posY | double(10,1) | YES | | NULL|| | posZ | double(10,1) | YES | | NULL|| | rotX | int(10) | YES | | NULL|| | rotY | int(10) | YES | | NULL|| | rotZ | int(10) | YES | | NULL|| +---+--+--+-+-++ 8 rows in set (0.00 sec) Here is the update statement that works correctly: UPDATE track_char SET rotX = + Double.parseDouble(fields[i + 1]) + , rotY = + Double.parseDouble(fields[i + 2]) + , rotZ = + Double.parseDouble(fields[i + 3]) + WHERE name = 'Tom' where 'fields' is a string array of tokens that I have parsed from a message. I tried doing this: UPDATE track_char SET rotX = + Integer.parseInt(fields[i + 1]) + , rotY = + Int.parseInt(fields[i + 2]) + , rotZ = + Int.parseInt(fields[i + 3]) + WHERE name = 'Tom' but that wouldn't work. And yes, I have made sure that it isn't a problem with the parseInt() method by using hard-coded values...only hard-coded doubles work, hard-coded ints wouldn't. [/snip] Do an update straight to the database and show us that without Java code. because it is hard to read why? top posting is bad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INT wants a float/double?
[snip] It seems to work with INTs when I do it from the MySQL commandline. I guess something is weird with the JDBC connector or something... [/snip] May I suggest echoing your query out so that you can see what it is trying to insert? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INT wants a float/double?
Ahhh! I should have thought of this beforehand. The query I was sending using doubles: UPDATE track_char SET rotX = 0.0, rotY = 0.0, rotZ = 0.0WHERE name = 'Tom' The query I was sending using ints: UPDATE track_char SET rotX = 0, rotY = 0, rotZ = 0WHERE name = 'Tom' There was a missing space between the value for rotZ and the 'WHERE'. I guess when I used a double, it just ignored that and accepted the syntax. Well, thanks for advising me to do the obvious, I now have this working exactly the way it should be! -Original Message- From: Jay Blanchard [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 06, 2006 2:38 PM To: Thomas Amundsen; mysql@lists.mysql.com Subject: RE: INT wants a float/double? [snip] It seems to work with INTs when I do it from the MySQL commandline. I guess something is weird with the JDBC connector or something... [/snip] May I suggest echoing your query out so that you can see what it is trying to insert? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: libmysqlclient_r.so not being created
[EMAIL PROTECTED] wrote: Any idea why libmysqlclient_r.so is not being created when I compile after a configure --without-server? Compile MySQL with --enable-thread-safe-client configure option. -- Jay Pipes Community Relations Manager, North America, MySQL Inc. Roaming North America, based in Columbus, Ohio email: [EMAIL PROTECTED]mob: +1 614 406 1267 Are You MySQL Certified? http://www.mysql.com/certification Got Cluster? http://www.mysql.com/cluster -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sad, I know...
hey what is happening. Thank you for this list.. THank you for having me.. I would appreciate your assistance if you are familiar with this... first off, I KNOW F¡¢K ALL about mySQL. Now.. I'm doing this tutorial and it tells me to install phpmyadmin, mySQL, etc. I get to a screen in phpmyadmin where it warns me to setup a password OR ELSE. So I set up a password. Right now, all I want to know is how do I log into mySQL, so that I can type in my password and get on with things.. I have no idea how to log in to mySQL... can you help? I used to be able to log straight into phpmyadmin, but now I get this: Welcome to phpMyAdmin 2.8.1 Probably reason of this is that you did not create configuration file. You might want to use setup scripthttp://localhost/phpmyadmin/scripts/setup.phpto create one. Error *MySQL said: *[image: Documentation] http://dev.mysql.com/doc/refman/5.0/en/error-returns.html #1045 - Access denied for user 'root'@'localhost' (using password: NO) Open new phpMyAdmin windowhttp://localhost/phpmyadmin/index.php?target=index.phplang=en-iso-8859-1convcharset=iso-8859-1token=19846bf63e5f9bcd5a2eaf9e658d377c
Re: UPDATE from one server to another
Daniel da Veiga wrote: On 6/6/06, Jason Dimberg [EMAIL PROTECTED] wrote: I am working on an application where data will be collected on laptops and then uploaded to a central database once the laptop is able to connect to the network after being in the field. I was initially thinking of using MS Access as a front end with linked tables through MySQL ODBC. I am now considering running WAMP on each machine with a web interface because there will be no interoperability issues if MySQL is the db server on both ends, but I am 1.) looking for any recommendations for the laptop interface (MS Access/WAMP or whatever other options might be available) and 2.) want to know what is the actual command for updating a table across two servers (this is NOT replication, but merely updating new data to an existing table). Data transfered will include binary objects and possibly GIS data. For example, Laptop 1 might have 10 rows of data from todays activities that need to be added to the main Server. Laptop 2 might have 30 rows of data that need to be added to the main Server. Neither laptop needs to have the data from the other, but the Server will contain data from both Laptops at the end of the day. The Server will then offer the data through a web interface. Server: Linux/MySQL 5.0 Laptops MS Access 2003 OR MySQL 5.0/ PHP 5/ Apache 2 Don't use ACCESS to deal with MySQL Data, you'll probably meet inconsistencies, along with bugs and problems with field types, besides, MS sucks... To sync the laptop with the actual database, you can use a Web Interface, or simply upload data via ftp or any other protocol (check for security) and write a script to add this data to MySQL. Are you sure you're not better served using the filesystem to store the data and simply indexing names, sizes, maybe a hash for security reasons (md5 or whatever) and timestamps so you can use the database to quickly search or organize files but offer them via HTTP or FTP? A simple app can get info about the file, upload it to the server and add a row to MySQL with the info and the location of the file in the filesystem. You would get something like: filesystem: /home/ftp/file1.bin mysql: name: file1.bin location: /home/ftp size: 1024 bytes hash: 78687hhg89686578h786 uploaded: 06/06/2006 14:30 from: laptop1 A simple web interface written in PHP or whatever can search this database, filter data, sort stuff and simply offer links to the ftp site... Just a suggestion... Daniel, Thanks for the on-topic response! Binary data will be a small portion of the data collected in the field (it will be checklists and data entry, mostly), but I am glad to hear what you said about Access. I have some fears about integrating it with MySQL and I think I am going to set up MySQL on each laptop to avoid those issues altogether and work in an environment I am familiar with (PHP/MySQL). Thanks for your suggestion. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sad, I know...
On 6/6/06, tomáz rezistänz [EMAIL PROTECTED] wrote: extreme snip I have no idea how to log in to mySQL... can you help? MySQL_dir/bin/mysql.exe for Windows /usr/bin/mysql (AFAIK) for *ix like YMMV Run it with -u root -p and you can provide your password. That's the problem with wizards and howtos, you gotta KNOW what you're installing and how to deal with errors. I used to be able to log straight into phpmyadmin, but now I get this: Welcome to phpMyAdmin 2.8.1 # Probably reason of this is that you did not create configuration file. You might want to use setup script http://localhost/phpmyadmin/scripts/setup.php to create one. #1045 - Access denied for user 'root'@'localhost' (using password: NO) # It can't be more verbose than that... So, you do have MySQL installed, you've setup a password for it, and for some reason (probably a lack of a config file, as PHPMyAdmin told you) PHPMyAdmin tries to connect to mysql without a password, and its kicked in the butt by trying. You could try running the script it told you (setup.php) to let PHPMyAdmin know that you must provide a password to MySQL. You can also run the console application (mysql or mysql.exe) using mysql -u root -p, provide your password and login, but that will not solve PHPMyAdmin, you MUST configure it. You can also login and reset your password, so PHPMyAdmin will log you in without a password and you can change your passwd from the Web Interface. You can also take a look at the MySQL Manual for a way to reset your password in case you can't login using console. -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sad, I know...
!! I apologize.. I should have mentioned that I am using a Mac in OSX 10.4.5 My main concern is should I begin logging into mSQL using the terminal or is this done from a browser??... Yes, I am know nothing.. no commands.. nada.. On 6/6/06, Daniel da Veiga [EMAIL PROTECTED] wrote: On 6/6/06, tomáz rezistänz [EMAIL PROTECTED] wrote: extreme snip I have no idea how to log in to mySQL... can you help? MySQL_dir/bin/mysql.exe for Windows /usr/bin/mysql (AFAIK) for *ix like YMMV Run it with -u root -p and you can provide your password. That's the problem with wizards and howtos, you gotta KNOW what you're installing and how to deal with errors. I used to be able to log straight into phpmyadmin, but now I get this: Welcome to phpMyAdmin 2.8.1 # Probably reason of this is that you did not create configuration file. You might want to use setup script http://localhost/phpmyadmin/scripts/setup.php to create one. #1045 - Access denied for user 'root'@'localhost' (using password: NO) # It can't be more verbose than that... So, you do have MySQL installed, you've setup a password for it, and for some reason (probably a lack of a config file, as PHPMyAdmin told you) PHPMyAdmin tries to connect to mysql without a password, and its kicked in the butt by trying. You could try running the script it told you (setup.php) to let PHPMyAdmin know that you must provide a password to MySQL. You can also run the console application (mysql or mysql.exe) using mysql -u root -p, provide your password and login, but that will not solve PHPMyAdmin, you MUST configure it. You can also login and reset your password, so PHPMyAdmin will log you in without a password and you can change your passwd from the Web Interface. You can also take a look at the MySQL Manual for a way to reset your password in case you can't login using console. -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sad, I know...
I wish I could uninstall mySQL and start over but I don't know how.. On 6/7/06, tomáz rezistänz [EMAIL PROTECTED] wrote: !! I apologize.. I should have mentioned that I am using a Mac in OSX 10.4.5 My main concern is should I begin logging into mSQL using the terminal or is this done from a browser??... Yes, I am know nothing.. no commands.. nada.. On 6/6/06, Daniel da Veiga [EMAIL PROTECTED] wrote: On 6/6/06, tomáz rezistänz [EMAIL PROTECTED] wrote: extreme snip I have no idea how to log in to mySQL... can you help? MySQL_dir/bin/mysql.exe for Windows /usr/bin/mysql (AFAIK) for *ix like YMMV Run it with -u root -p and you can provide your password. That's the problem with wizards and howtos, you gotta KNOW what you're installing and how to deal with errors. I used to be able to log straight into phpmyadmin, but now I get this: Welcome to phpMyAdmin 2.8.1 # Probably reason of this is that you did not create configuration file. You might want to use setup script http://localhost/phpmyadmin/scripts/setup.php to create one. #1045 - Access denied for user 'root'@'localhost' (using password: NO) # It can't be more verbose than that... So, you do have MySQL installed, you've setup a password for it, and for some reason (probably a lack of a config file, as PHPMyAdmin told you) PHPMyAdmin tries to connect to mysql without a password, and its kicked in the butt by trying. You could try running the script it told you (setup.php) to let PHPMyAdmin know that you must provide a password to MySQL. You can also run the console application (mysql or mysql.exe ) using mysql -u root -p, provide your password and login, but that will not solve PHPMyAdmin, you MUST configure it. You can also login and reset your password, so PHPMyAdmin will log you in without a password and you can change your passwd from the Web Interface. You can also take a look at the MySQL Manual for a way to reset your password in case you can't login using console. -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UPDATE from one server to another
Jason Dimberg wrote: I am working on an application where data will be collected on laptops and then uploaded to a central database once the laptop is able to connect to the network after being in the field. I was initially thinking of using MS Access as a front end with linked tables through MySQL ODBC. I am now considering running WAMP on each machine with a web interface because there will be no interoperability issues if MySQL is the db server on both ends, but I am 1.) looking for any recommendations for the laptop interface (MS Access/WAMP or whatever other options might be available) and 2.) want to know what is the actual command for updating a table across two servers (this is NOT replication, but merely updating new data to an existing table). Data transfered will include binary objects and possibly GIS data. For example, Laptop 1 might have 10 rows of data from todays activities that need to be added to the main Server. Laptop 2 might have 30 rows of data that need to be added to the main Server. Neither laptop needs to have the data from the other, but the Server will contain data from both Laptops at the end of the day. The Server will then offer the data through a web interface. I'll start with the syncing records. You can script this in PHP or Perl very relatively. Your main issue is going to be dealing with primary keys. Here's how I'd do it. Each table has a number of fields for tracking changes, eg: - inserted - edited - deleted These would be boolean fields that your application sets when inserting / editing / deleting data. When the laptops return home to sync with the server, your script would select all the flagged records and take appropriate action. For example: - inserts: If you have related records ( ie primary key / foreign key relationships ), you'll have to do some shuffling of data around, eg insert record into server, fetch created primary key ( auto_increment ), then select 'child' relationship stuff from the laptop, and insert this into the server, using the newly created primary key, and NOT the primary key from the laptop. If you don't have relationships set up, you won't have this trouble. - edits: Just update the entire table with fresh data from the client - deleted: Delete :) After you've updated all data, you should probably dump everything ( drop tables, maybe even drop database ), and then import fresh data from a mysldump ( after you've imported data from the other laptop as well ). For the interface, let me push my own wheelbarrow for a second ... I've made a nifty set of libraries to help you create rich database front-ends ( using Gtk2 ) that you might be able to make use of instead of doing your stuff in PHP. They're written in Perl, but if you're doing simple data entry, you will hardly have to do any code at all. You create your GUI in Glade, create DBI database handle, and then use my libraries to connect your database to your GUI. Inserting, deleting, and applying records are all one-liners, and everything else is taken care of :) Everything is open-source and cross-platform, and there is even a module for creating PDF reports. Trust me - it's a LOT less work ( and trouble ) than doing it in PHP. If you're interested, have a look at: http://entropy.homelinux.org/axis_not_evil And also please send feature requests, bug reports, and contributions :) Dan -- 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]
