Re: Multiple Primary key
Hi, Good evening people. I'm trying to create a simple table via MySQl Navigator. The table il structured in this way dt_amt Date Not Null Primary Key oper INT Not Null Primary Key amount Decimal(3,3) Null --- but when I choose fire it shows me a multiple primary key defined message and doesn't make me create the table. Isn't MySql able to handle multiple primary key or it's a navigator bug? In the same day I can receive data from different operators. It depends on what you want - Do you want a compound primary key? If so, use the PRIMARY KEY (col1, col2, coln) clause. If you want multiple primary keys: no can do, only 1 primary key per table can be defined. If you want to enforce uniqueness in more than one column, use additional UNIQUE constraints/indices. 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: Security Question
Thomas, It would be more secure if you has the DB on another server that was locked down and only allowed access to the web server on the MySql port, (plus probably ssh access for admin). If you're going to the expense of audits, this must be fairly important, so the cost of the other server would not be too significant? Best regards, Andy -Original Message- From: Curley, Thomas [mailto:[EMAIL PROTECTED] Sent: 26 November 2003 13:22 To: [EMAIL PROTECTED] Subject: RE: Security Question Importance: High thanks for reply - the requirement comes from a security audit - so try to think in terms of a hacker Obviously and (I had assumed) 1.- the files would have tight unix security file permissions applied 2.- indeed the key would be stored on an internal tightly managed box (or device) Another Assumption -- Encrypting / decrypting all data on the fly would be too expensive and grind the app to a halt So the question again :- Any ideas on how to avoid having data files stored with absolutely no protection against copying If there is no solution to this then MySql should not be used on internet accessible boxes for dynamic web sites Thomas -Original Message- From: Fagyal, Csongor [mailto:[EMAIL PROTECTED] Sent: 26 November 2003 12:51 To: Curley, Thomas Cc: [EMAIL PROTECTED] Subject: Re: Security Question Thomas, I am trying to find a solution to the following security issue with MySql DB on linux - Someone copies the DB files to another box, starts a mysql instance, loads the DB and presto - views the 'private' data !!! Well, someone should not have access rights to the DB files on the first hand. Ideally I would like to know if there is any option in MySql to store the DB files in a secure format and one that needs a key or similiar to open the DB If someone was able to access your DB files, he would probably also be able to access that key (that you must store _somewhere_), wouldn't he? - Csongor ** *** This email and any attachments are confidential and intended for the sole use of the intended recipient(s).If you receive this email in error please notify [EMAIL PROTECTED] and delete it from your system. Any unauthorized dissemination, retransmission, or copying of this email and any attachments is prohibited. Euroconex does not accept any responsibility for any breach of confidence, which may arise from the use of email. Please note that any views or opinions presented in this email are solely those of the author and do not necessarily represent those of the Company. This message has been scanned for known computer viruses. ** *** -- 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]
row_format=compressed
anybody knows when row_format=compressed will work? and what means row_format=default? . that it´s not compressed? thanks __ WEB.DE FreeMail wird 5 Jahre jung! Feiern Sie mit uns und nutzen Sie die neuen Funktionen http://f.web.de/features/?mc=021130 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: Security Question
- Someone copies the DB files to another box, starts a mysql instance, loads the DB and presto - views the 'private' data !!! PD Sure. That's why you establish filesystem level access privileges so that PD only the mysql user can copy them in the first place. Some DBMSs allow to setup databases on a separate partition with its own filesystem that will have nothing in common with OS filesystem. OS is unable to read DBMS filesystem data. So getting root on OS does not give the hacker access to the DBMS file system and only DBMS users can access it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Security Question
Hi! On Nov 27, DeBug wrote: - Someone copies the DB files to another box, starts a mysql instance, loads the DB and presto - views the 'private' data !!! PD Sure. That's why you establish filesystem level access privileges so that PD only the mysql user can copy them in the first place. Some DBMSs allow to setup databases on a separate partition with its own filesystem that will have nothing in common with OS filesystem. OS is unable to read DBMS filesystem data. So getting root on OS does not give the hacker access to the DBMS file system and only DBMS users can access it. No, getting root gives access to each and every byte on the hard drive. He can read the partition where the data are. And if he is prepared, he can interpret them, of course (we are not talikng about script kiddies here, do we ?). Or, he can patch the in-memory image of the running db process and access the data through it. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Porting MySQL Embedded to VXWorks
Hi I'm working on a project developed in C which is being developed under windows but which is to be ported to an embedded platform (VXWorks OS) at some point in the next few months. Part of the project comprises a database and the initial plan has been to develop the database update code using the MySQL embedded API and then either port the MySQL embedded code to the VXWORKS OS or convert to another third-party embedded database Can anybody offer any advice for how to port the embedded code to VXWorks, or suggest alternative embedded databases we could use without high amounts of reworking of the database layer code Thanks in advance Andy Winter Software Engineer General Dynamics United Kingdom Limited Registered in England and Wales No. 1911653 Registered Office: 100 New Bridge Street, London, EC4V 6JA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using Mysql functions in INSERTS and UPDATES in replication setup
We are using Mysql with one master and several slaves. In some INSERT and UPDATE queries we use Mysql functions like CURDATE() and RAND() . The problem that now occurs is that if a slave is reading the queries from the binary log, the resulting data is different from that on the master, because of delays. Apparently the literal queries including the function calls in the queries are recorded in the binary log. I expected the master writing the results from the functions into the binary log instead of the function calls themselves. In the manual of MySQL I did not find any hints about this issue. One solution is to first get the results from the mysql-function with a SELECT query, and then INSERT the results from the functions. But I would vote for a change in the writing method into the binary log. Can somebody comment on this? Arnoud Witt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unique Index efficiency query
On Wed, Nov 26, 2003 at 06:44:57PM -0600, Matt W wrote: Hi Chris, It doesn't take MySQL any more or less time to update a unique index than a non-unique one. :-) Ah, a nice simple answer to a long drawn out question :) Thanks Matt, just the reassurance I was after, I didn't want inserts to suddenly slow down by a factor of 10. I realise they'll slow down slightly with another index to update, but never mind. Now I can go give MySQL half an hours work to do creating this index :) -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
restore says: Couldn't fix table with quick recovery - what is the reason ?
Hello, i'm writing a program which automatically makes a backup of some tables (with backup table foo1, foo2, ... to '/tmp/backupdir') and after that if some errors occur, the program tries to execute restore table foo1, foo2, ... from '/tmp/backupdir'. The table which is returned contains SOMETIMES for SOME tables errors like: Couldn't fix table with quick recovery: Found wrong number of deleted records and Run recovery again without -q What is the reason for that and how do i avoid it? I searched the Manual, web and this list but couldn't find an answer. My System: Redhat 9.0 with mysql Ver 12.20 Distrib 4.0.13, for pc-linux (i686) Thanks for your attention and time, Christian Matuszewski -- NEU FÜR ALLE - GMX MediaCenter - für Fotos, Musik, Dateien... Fotoalbum, File Sharing, MMS, Multimedia-Gruß, GMX FotoService Jetzt kostenlos anmelden unter http://www.gmx.net +++ GMX - die erste Adresse für Mail, Message, More! +++ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
table corruption with certain (valid) insert statements
Description:
The included sql test script (output of mysqldump 10.2 (create table statement and
insertion of 250 records into this table) with added check for table integrity)
results in corrupted table and error message 'Found key at page 36864 that points to
record outside datafile' when checked with 'check table' or myisamcheck. Bizarrely, if
you delete any one of the records being inserted the resulting table will be OK. Also,
as you'll note, the value inserted into the 3rd column is always string
'DatabaseIdentifier'. If you change it in any record by deleting or adding a character
(i.e. change it to 'DatabaseIdentifie' or 'DatabaseIdentifierr' the resulting table
will also be fine.
If you don't issue the 'check table' command you'll be able to do selects on the
table.However, once the check has been issued any attempt to do select result in
ERROR 1016: Can't open file: 'DatabaseObject.MYI'. (errno: 145).
The problem also occurs on precompiled MySQL 4.1 for alphas (I haven't tested any
other platforms).
However, the problem does not occur with precompiled MySQL 4.0.16 on intel-linux (the
same platform used to run mysqlbug producing this report for 4.1). Also, if you dump
the test database with mysqldump 9.09 and re-upload it, the resulting table is OK.
How-To-Repeat:
-- MySQL dump 10.2
--
-- Host: localhostDatabase: test
-
-- Server version 4.0.16-standard
--
-- Table structure for table 'DatabaseObject'
--
DROP TABLE IF EXISTS DatabaseObject;
CREATE TABLE DatabaseObject (
DB_ID int(10) NOT NULL auto_increment,
_Protege_id varchar(255) default NULL,
_class varchar(64) default NULL,
_partial tinyint(4) NOT NULL default '0',
_displayName text,
created int(10) unsigned default NULL,
created_class varchar(64) default NULL,
_internal1 varchar(255) default NULL,
_internal2 varchar(255) default NULL,
_html longblob,
_timestamp timestamp(14) NOT NULL,
__is_ghost enum('TRUE') default NULL,
_internal3 varchar(255) default NULL,
PRIMARY KEY (DB_ID),
KEY _Protege_id (_Protege_id),
KEY _class (_class),
KEY created (created),
KEY _internal1 (_internal1),
KEY _internal2 (_internal2),
KEY _timestamp (_timestamp),
KEY __is_ghost (__is_ghost),
KEY _internal3 (_internal3),
KEY _partial (_partial),
FULLTEXT KEY _displayName (_displayName),
FULLTEXT KEY _Protege_id_fulltext (_Protege_id),
FULLTEXT KEY _class_fulltext (_class),
FULLTEXT KEY _internal1_fulltext (_internal1),
FULLTEXT KEY _internal2_fulltext (_internal2),
FULLTEXT KEY _internal3_fulltext (_internal3)
) TYPE=MyISAM;
--
-- Dumping data for table 'DatabaseObject'
--
/*!4 ALTER TABLE DatabaseObject DISABLE KEYS */;
LOCK TABLES DatabaseObject WRITE;
INSERT INTO DatabaseObject VALUES
(9400,NULL,'DatabaseIdentifier',0,'GO:0006258',NULL,NULL,NULL,NULL,NULL,00,NULL,NULL),(9402,NULL,'DatabaseIdentifier',0,'GO:0042160',NULL,NULL,NULL,NULL,NULL,00,NULL,NULL),(9404,NULL,'DatabaseIdentifier',0,'GO:0042161',NULL,NULL,NULL,NULL,NULL,00,NULL,NULL),(9406,NULL,'DatabaseIdentifier',0,'GO:0042162',NULL,NULL,NULL,NULL,NULL,00,NULL,NULL),(9408,NULL,'DatabaseIdentifier',0,'GO:0042163',NULL,NULL,NULL,NULL,NULL,00,NULL,NULL),(9411,NULL,'DatabaseIdentifier',0,'GO:0042164',NULL,NULL,NULL,NULL,NULL,00,NULL,NULL),(9415,NULL,'DatabaseIdentifier',0,'GO:0046139',NULL,NULL,NULL,NULL,NULL,00,NULL,NULL),(9416,NULL,'DatabaseIdentifier',0,'GO:0006787',NULL,NULL,NULL,NULL,NULL,00,NULL,NULL),(9419,NULL,'DatabaseIdentifier',0,'GO:0042168',NULL,NULL,NULL,NULL,NULL,00,NULL,NULL),(9421,NULL,'DatabaseIdentifier',0,'GO:0046149',NULL,NULL,NULL,NULL,NULL,0!
0,NULL,NULL),(9422,NULL,'DatabaseIdentifier',0,'GO:0042167',NULL,NULL,NULL,NULL,NULL,00,NULL,NULL),(9424,NULL,'DatabaseIdentifier',0,'GO:0042169',NULL,NULL,NULL,NULL,NULL,00,NULL,NULL),(9427,NULL,'DatabaseIdentifier',0,'GO:0004200',NULL,NULL,NULL,NULL,NULL,00,NULL,NULL),(9429,NULL,'DatabaseIdentifier',0,'GO:0004201',NULL,NULL,NULL,NULL,NULL,00,NULL,NULL),(9431,NULL,'DatabaseIdentifier',0,'GO:0004202',NULL,NULL,NULL,NULL,NULL,00,NULL,NULL),(9432,NULL,'DatabaseIdentifier',0,'GO:0005600',NULL,NULL,NULL,NULL,NULL,00,NULL,NULL),(9434,NULL,'DatabaseIdentifier',0,'GO:0004203',NULL,NULL,NULL,NULL,NULL,00,NULL,NULL),(9435,NULL,'DatabaseIdentifier',0,'GO:0005601',NULL,NULL,NULL,NULL,NULL,00,NULL,NULL),(9437,NULL,'DatabaseIdentifier',0,'GO:0004204',NULL,NULL,NULL,NULL,NULL,00,NULL,NULL),(9438,NULL,'DatabaseIdentifier',0,'GO:0005602',NULL,NULL,NULL,NULL,NULL,00!
Re: Inefficient use of index (was: Big difference in MyISAM and InnoDB SELECT speed)
Janusz Krzysztofik wrote: ... I am trying to optimize MySQL (3.23.49 from Debian stable) setup for ASPseek application. I decided to try InnoDB in order to be able to update tables while performing time consuming selects. After converting all tables to InnoDB I noticed a big difference in processing speed of one of the SELECT queries performed by the application. Query: select url_id from urlword where deleted=0 and status=200 and origin=1 MyISAM: Query OK, 14274315 rows affected (4 min 54.88 sec) InnoDB: Query OK, 14274315 rows affected (1 day 8 hours 46 min 46.70 sec) Mechain Marc wrote: ... Have you done an EXPLAIN on your query ? May be an index on (origin,status,deleted) could help. Janusz Krzysztofik wrote: ... mysql explain select url_id from urlword where deleted=0 and status=200 and origin=1; MyISAM (fast): +-+--+---+--+-+--+--++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+--+---+--+-+--+--++ | urlword | ALL | crc | NULL |NULL | NULL | 46648925 | where used | +-+--+---+--+-+--+--++ InnoDB (very slow): +-+--+---+--+-+---+--++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+--+---+--+-+---+--++ | urlword | ref | crc | crc | 5 | const | 16951116 | where used | +-+--+---+--+-+---+--++ So I retried InnoDB with ignore index (crc) and got: Query OK, 14274315 rows affected (5 min 43.23 sec) Next I found that this issue is not related to InnoDB. On my second server with almost the same data in MyISAM tables the query also uses the index and lasts forever. Now the question is: how should I set up (and maintain?) my MySQL server to prevent it from using indexes inefficiently? Mechain Marc wrote: ... Quite a good question, no idea. But if the query select url_id from urlword where deleted=?? and status=??? and origin=?; is a query that you will use very often, I still think that creating an index on (origin,status,deleted) is the good answer. gerald_clark wrote: ... Try adding an index on (status,deleted) Martijn Tonies wrote: ... I wonder: how many possible different values would such an index return? mysql select distinct status, deleted from urlword; ... 13 rows in set (6 min 55.94 sec) mysql select distinct status, deleted, origin from urlword; ... 23 rows in set (7 min 9.90 sec) If this is a (very) low value, won't the index make things slower (if it's being used) compared to a full table scan? I guess these values are very low for a table with 46 milion records, so I understand it is better not to use such indicies, right? Well, that's what I have understood from many db engines :-) ... The idea is, that fetching rows in index order takes make time then fetching rows in storage order. Let's try Marc's idea first: mysql create index x1 on urlword (deleted,status,origin); Query OK, 46648925 rows affected (3 hours 56 min 36.17 sec) Oops, Marc said (origin,status,deleted), not (deleted,status,origin), but we'll see. mysql explain select url_id from urlword where deleted=0 and status=200 and origin=1; +-+--+---+--+-+---+--++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+--+---+--+-+---+--++ | urlword | ref | crc,x1| crc | 5 | const | 16934432 | where used | +-+--+---+--+-+---+--++ 1 row in set (0.27 sec) ^ | Why crc again, not x1? + mysql show index from urlword; +-++-+--+-+---+-+--++-+ | Table | Non_unique | Key_name| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment | +-++-+--+-+---+-+--++-+ | urlword | 0 | PRIMARY |1 | url_id | A |46411809 | NULL | NULL | | | urlword | 0 | url |1 | url | A |46411809 | NULL | NULL | | | urlword | 1 | next_index_time |1 | next_index_time | A |15470603 | NULL | NULL | | | urlword | 1 | hops
Speed difference between boolean full-text searches and full-text searches
Executing this SQL, takes ~5 sec.
select artists.name , cds.title , tracks.title from artists, tracks,
cds
where artists.artistid = tracks.artistid and cds.cdid = tracks.cdid
and MATCH (artists.name) AGAINST ('madonna')
and MATCH (cds.title) AGAINST ('music')
and MATCH (cds.title) AGAINST ('mix')
and MATCH (cds.title) AGAINST ('2001')
limit 1001
and this, ~40 sec.
select artists.name , cds.title, tracks.title from artists, tracks, cds
where artists.artistid = tracks.artistid and cds.cdid = tracks.cdid
and MATCH (artists.name) AGAINST ('madonna' IN BOOLEAN MODE)
and MATCH (cds.title) AGAINST ('music mix 2001' IN BOOLEAN MODE) limit
1001
Same result but the speed difference is quite a different, why is that ?
Regards
Index before or after inserts?
I need to insert hundreds of milions of records to a table and make several indicies on it. Now, is it faster to make tables with indicies and then fill tables or fill tables first, then make indicies? Any experiancies? regards, mirza -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySqlManager.rc file missing in mysql source for windows
hi all., i tried to compile mysql-3.23.58 source distribution for windows using VisualStudio 6 and found that MySqlManager.rc file missing and hence the compilation failed , The MySqlManeger.rc file is not available in the MySqlManager folder downloaded from mysql home site. plz help me out insolving the problem. thanks and regards! Download Yahoo! Messenger now for a chance to win Live At Knebworth DVDs http://www.yahoo.co.uk/robbiewilliams -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to convert a mysql-3.23.5x database to UTF-8
Hi, we have a mysql-3.23.5x database (Linux) that contains a lot of German umlauts. We have to support Asian charters (UTF-8) and how can I convert the database to UTF-8? Zsolt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UFT-8 support under mysql-3.23.5x?
Hi, we use mysql-3.23.5x? under Linux and cannot upgrade to 4.x. Our java applications can store and fetch UTF-8 characters we have problem only with sorting. Any ideas how I can fix the problem? Zsolt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index before or after inserts?
On Nov 27, 2003, at 10:32 AM, Mirza wrote: I need to insert hundreds of milions of records to a table and make several indicies on it. Now, is it faster to make tables with indicies and then fill tables or fill tables first, then make indicies? Any experiancies? Insert the records first, then index. Otherwise, the indexes have to be updated with each and every insert. ___/ / __/ / / Ed Leafe Linux Love: unzip;strip;touch;finger;mount;fsck;more;yes;umount;sleep -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
table backup
how can I backup the table but not with BACKUP TABLE because it Backups the .frm and another files. I want that it backups in SQL with data. How can i do it? Thanks __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Repeated 100% CPU problem in FreeBSD
Hello, Most of the time, MySQL 4.0.16 runs absolutely fine on my FreeBSD 4.9 system. However, today, unexplicably, it's run into the same problem 3 times now; a couple of the threads suddenly start eating 100% CPU for no good reason while doing a SELECT. They'll sit there until I kill them, which results in a lot of hair tearing as it comes back up checking all the tables. I haven't been able to produce a core despite sending it a SIGABRT, but I'll try recompiling with -g (do the supplied binaries have this?) soon. Pretty much all I can find around that might contribute relates to the poor threading implementation on FreeBSD and the name resolving issue. I should have worked around both of them; I've built with linuxthreads, and I have skip-name-resolve in my.cnf. Does *anyone* have any clues why this randomly happens? It had been fine for 30 or so days prior to this, and today it's crashed 3 times in this way. I've recently run a myisamchk -r *.MYI over all the tables in the database which comes up clean. Any help would be appreciated. -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: UFT-8 support under mysql-3.23.5x?
Zsolt, we use mysql-3.23.5x? under Linux and cannot upgrade to 4.x. Our java applications can store and fetch UTF-8 characters we have problem only with sorting. Any ideas how I can fix the problem? I'd advise you to upgrade to MySQL 4. MySQL 4.1.1 would be best, as it has extensive support for UTF-8 and other character sets. 4.1.1 will be out as an Alpha version pretty soon. On National Character Sets and Unicode, see the appropriate manual pages: http://www.mysql.com/doc/en/Charset.html Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 [filter fodder: sql, mysql, query] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
list, order and limit data
MySQL version: 3.23.49 OS: Debian3 Scripting Language: PHP I'm working on a trouble ticket system. There are several tables involved, but for this query only two tables are involved: tickets and comments. They are related by the ticketnumber field. This client cannot afford a high end database, and their host does not support MySQL 4 yet. I want to display the last 50 trouble tickets and the last response from support for each ticket. I can pull the last fifty with this query: SELECT * FROM tickets LIMIT 50; and I can select the latest date of response from the comments table for a given trouble ticket with this query: SELECT dtg FROM comments WHERE ticketnumber =540856 ORDER BY dtg DESC LIMIT 1; My question is, how can I pull 50 rows from the tickets table and then grab the last resonse date of each ticket from the comments table?. The queries can be run individually easily, but I need them to run together or I need some way of relating the comment table results and the ticket table results. I plan to stick each row in an html table so the user is presented with 50 ticket items, and a link to each item (that part is easy, I just need to know how to pull that query). The reason for the last resonse date is for informational purposes. Just to help you visualize (this is in an HTML table): | username | submit date | problem class | ticket status | last response | the first four fields come from the tickets table, the last comes from the comments table. There might be 20 commenst for each ticket, or there may be none, but I only want to show the date of the last comment. Hopefully I've been clear in what I'm trying to acomplish. Thanks in advance! - Do you Yahoo!? Free Pop-Up Blocker - Get it now
Re: table backup
Zenzo [EMAIL PROTECTED] wrote: how can I backup the table but not with BACKUP TABLE because it Backups the .frm and another files. I want that it backups in SQL with data. How can i do it? Take a look at mysqldump program: http://www.mysql.com/doc/en/mysqldump.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table backup
The problem is that I wnt to use this remotely on another server. Is there a SQL command for doing this (exporting the table with data into a SQL script)? --- Victoria Reznichenko [EMAIL PROTECTED] wrote: Zenzo [EMAIL PROTECTED] wrote: how can I backup the table but not with BACKUP TABLE because it Backups the .frm and another files. I want that it backups in SQL with data. How can i do it? Take a look at mysqldump program: http://www.mysql.com/doc/en/mysqldump.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using Mysql functions in INSERTS and UPDATES in replication setup
Arnoud Witt (Marktplaats) [EMAIL PROTECTED] wrote: We are using Mysql with one master and several slaves. In some INSERT and UPDATE queries we use Mysql functions like CURDATE() = and RAND() . The problem that now occurs is that if a slave is reading the queries = from the binary log, the resulting data is different from that on the master, because of delays. Apparently the literal queries including the function calls in the queries are recorded in the binary log. I expected the master writing the results from the functions into the = binary log instead of the function calls themselves. In the manual of MySQL I = did not find any hints about this issue. One solution is to first get the results from the mysql-function with a = SELECT query, and then INSERT the results from the functions. But I = would vote for a change in the writing method into the binary log. Can somebody comment on this? What versions of MySQL do you use? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: table backup
Zenzo [EMAIL PROTECTED] wrote: The problem is that I wnt to use this remotely on another server. You can use --host option of mysqldump. Is there a SQL command for doing this (exporting the table with data into a SQL script)? No. --- Victoria Reznichenko [EMAIL PROTECTED] wrote: Zenzo [EMAIL PROTECTED] wrote: how can I backup the table but not with BACKUP TABLE because it Backups the .frm and another files. I want that it backups in SQL with data. How can i do it? Take a look at mysqldump program: http://www.mysql.com/doc/en/mysqldump.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Index before or after inserts?
Mirza, Definitely, index after insert. Andy -Original Message- From: Mirza [mailto:[EMAIL PROTECTED] Sent: 27 November 2003 15:33 To: [EMAIL PROTECTED] Subject: Index before or after inserts? I need to insert hundreds of milions of records to a table and make several indicies on it. Now, is it faster to make tables with indicies and then fill tables or fill tables first, then make indicies? Any experiancies? regards, mirza -- 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: table backup
I think mysqldump does that. It creates a script that contains SQL statements to create the table and then the data to populate the table. Mysqldump has command line parameters, but I don't know which ones you would need to use. Bob -Original Message- From: Zenzo [mailto:[EMAIL PROTECTED] Sent: Thursday, November 27, 2003 12:26 PM To: Victoria Reznichenko Cc: MySQL List Subject: Re: table backup The problem is that I wnt to use this remotely on another server. Is there a SQL command for doing this (exporting the table with data into a SQL script)? --- Victoria Reznichenko [EMAIL PROTECTED] wrote: Zenzo [EMAIL PROTECTED] wrote: how can I backup the table but not with BACKUP TABLE because it Backups the .frm and another files. I want that it backups in SQL with data. How can i do it? Take a look at mysqldump program: http://www.mysql.com/doc/en/mysqldump.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Free Pop-Up Blocker - Get it now http://companion.yahoo.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: Index before or after inserts?
At 03:19 PM 11/27/2003, you wrote: Mirza, Definitely, index after insert. Andy Maybe not. g I know this is the common sense approach that works with many databases, but I'm not sure it is faster with MySQL. MySQL Manual on Alter Table Note that if you use any other option to ALTER TABLE than RENAME, MySQL will always create a temporary table, even if the data wouldn't strictly need to be copied (like when you change the name of a column). We plan to fix this in the future, but as one doesn't normally do ALTER TABLE that often this isn't that high on our TODO. For MyISAM tables, you can speed up the index recreation part (which is the slowest part of the recreation process) by setting the myisam_sort_buffer_size variable to a high value. So if the index is added later (after the data is inserted), a new temporary table is created and the data is reloaded. MySQL probably does it this way to ensure the table isn't destroyed if something happens in the middle of the table restructure. Now if you really, really want to add the indexes later, make sure you add all the indexes in *one* alter table command so the data is reloaded only once. Otherwise it will get loaded for each alter table Mike -Original Message- From: Mirza [mailto:[EMAIL PROTECTED] Sent: 27 November 2003 15:33 To: [EMAIL PROTECTED] Subject: Index before or after inserts? I need to insert hundreds of milions of records to a table and make several indicies on it. Now, is it faster to make tables with indicies and then fill tables or fill tables first, then make indicies? Any experiancies? regards, mirza -- 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: Index before or after inserts?
Hi, Create the indexes right away and then use ALTER TABLE table DISABLE KEYS; Load your data and then ALTER TABLE table ENABLE KEYS; This will not make a tmp copy of the data file, but will simply start rebuilding the index. However, DISABLE KEYS doesn't disable unique indexes, so these still have to be updated as opposed to adding them afterwards. This is probably good though for integrity. Using LOCK TABLES around multi-row INSERTs will make index updating much faster than single-row non-locked INSERTs. And a large enough key_buffer will make the indexes be flushed less often. For ENABLE KEYS, I think myisam_sort_buffer_size is the important variable. Also note: DISABLE/ENABLE KEYS doesn't work in 3.23, but you can do the same thing using myisamchk. Hope that helps. Matt - Original Message - From: mos Sent: Thursday, November 27, 2003 3:44 PM Subject: RE: Index before or after inserts? At 03:19 PM 11/27/2003, you wrote: Mirza, Definitely, index after insert. Andy Maybe not. g I know this is the common sense approach that works with many databases, but I'm not sure it is faster with MySQL. MySQL Manual on Alter Table Note that if you use any other option to ALTER TABLE than RENAME, MySQL will always create a temporary table, even if the data wouldn't strictly need to be copied (like when you change the name of a column). We plan to fix this in the future, but as one doesn't normally do ALTER TABLE that often this isn't that high on our TODO. For MyISAM tables, you can speed up the index recreation part (which is the slowest part of the recreation process) by setting the myisam_sort_buffer_size variable to a high value. So if the index is added later (after the data is inserted), a new temporary table is created and the data is reloaded. MySQL probably does it this way to ensure the table isn't destroyed if something happens in the middle of the table restructure. Now if you really, really want to add the indexes later, make sure you add all the indexes in *one* alter table command so the data is reloaded only once. Otherwise it will get loaded for each alter table Mike -Original Message- From: Mirza [mailto:[EMAIL PROTECTED] Sent: 27 November 2003 15:33 Subject: Index before or after inserts? I need to insert hundreds of milions of records to a table and make several indicies on it. Now, is it faster to make tables with indicies and then fill tables or fill tables first, then make indicies? Any experiancies? regards, mirza -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: list, order and limit data
Hi, For the query that you would need, see this page in the manual: http://www.mysql.com/doc/en/example-Maximum-column-group-row.html Also see the comment from March 16, 2003 about the LEFT JOIN trick. However, in your case, why don't you just add another column in the tickets table? last_response or whatever. Then you have everything you need right in 1 table and just have to UPDATE the last_response when a response is made. Hope that helps. Matt - Original Message - From: brfg3 at yahoo Sent: Thursday, November 27, 2003 1:00 PM Subject: list, order and limit data MySQL version: 3.23.49 OS: Debian3 Scripting Language: PHP I'm working on a trouble ticket system. There are several tables involved, but for this query only two tables are involved: tickets and comments. They are related by the ticketnumber field. This client cannot afford a high end database, and their host does not support MySQL 4 yet. I want to display the last 50 trouble tickets and the last response from support for each ticket. I can pull the last fifty with this query: SELECT * FROM tickets LIMIT 50; and I can select the latest date of response from the comments table for a given trouble ticket with this query: SELECT dtg FROM comments WHERE ticketnumber =540856 ORDER BY dtg DESC LIMIT 1; My question is, how can I pull 50 rows from the tickets table and then grab the last resonse date of each ticket from the comments table?. The queries can be run individually easily, but I need them to run together or I need some way of relating the comment table results and the ticket table results. I plan to stick each row in an html table so the user is presented with 50 ticket items, and a link to each item (that part is easy, I just need to know how to pull that query). The reason for the last resonse date is for informational purposes. Just to help you visualize (this is in an HTML table): | username | submit date | problem class | ticket status | last response | the first four fields come from the tickets table, the last comes from the comments table. There might be 20 commenst for each ticket, or there may be none, but I only want to show the date of the last comment. Hopefully I've been clear in what I'm trying to acomplish. Thanks in advance! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Disorder result with ORDER BY with ENUM, INT
Hi, - Original Message - From: Chuck Gadd Sent: Wednesday, November 26, 2003 2:29 PM Subject: Re: Disorder result with ORDER BY with ENUM, INT Kriengkrai J. wrote: -- System: MySQL 4.0.13, 4.0.16 on Linux x86 -- Table type: MyISAM, InnoDB -- Description / How-To-Repeat: -- 1. When I use -- SELECT id, type FROM test.report ORDER BY type, id; -- the result is in wrong order -- -- -- ++-+ -- | id | type| -- ++-+ -- | 4 | general | -- | 3 | general | -- | 1 | general | -- | 2 | general | -- | 5 | inhouse | -- | 6 | inhouse | -- .. It is sorted EXACTLY as you specified. First by type, and then by ID. It doesn't look sorted to me (generals aren't sorted by id). :-) Shouldn't this be the order? ++-+ | id | type| ++-+ | 1 | general | | 2 | general | | 3 | general | | 4 | general | | 5 | inhouse | | 6 | inhouse | Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Repeated 100% CPU problem in FreeBSD
In the last episode (Nov 27), Chris Elsworth said: Most of the time, MySQL 4.0.16 runs absolutely fine on my FreeBSD 4.9 system. However, today, unexplicably, it's run into the same problem 3 times now; a couple of the threads suddenly start eating 100% CPU for no good reason while doing a SELECT. They'll sit there until I kill them, which results in a lot of hair tearing as it comes back up checking all the tables. I haven't been able to produce a core despite sending it a SIGABRT, but I'll try recompiling with -g (do the supplied binaries have this?) soon. You can use the gcore command to create a coredump of a running program without killing it, and the pstack command (in ports) is handy for printing the stacks of all threads in a running process without even coring it. Once you get a stack trace, send it to [EMAIL PROTECTED] and/or here. Pretty much all I can find around that might contribute relates to the poor threading implementation on FreeBSD and the name resolving issue. I should have worked around both of them; I've built with linuxthreads, and I have skip-name-resolve in my.cnf. You should only need one or the other, actually. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Repeated 100% CPU problem in FreeBSD
On Thu, Nov 27, 2003 at 06:25:38PM -0600, Dan Nelson wrote: In the last episode (Nov 27), Chris Elsworth said: Most of the time, MySQL 4.0.16 runs absolutely fine on my FreeBSD 4.9 system. However, today, unexplicably, it's run into the same problem 3 times now; a couple of the threads suddenly start eating 100% CPU for no good reason while doing a SELECT. They'll sit there until I kill them, which results in a lot of hair tearing as it comes back up checking all the tables. I haven't been able to produce a core despite sending it a SIGABRT, but I'll try recompiling with -g (do the supplied binaries have this?) soon. You can use the gcore command to create a coredump of a running program without killing it, and the pstack command (in ports) is handy for printing the stacks of all threads in a running process without even coring it. Once you get a stack trace, send it to [EMAIL PROTECTED] and/or here. Please do post a strack trace here. I'd very curious to see what it looks like. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 75 days, processed 2,959,075,536 queries (456/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Window parts
Greetings! Im new to this MySQL..please help me identifying the different parts of the screen and its purpose. God Bless and thank you Alvi - Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard
How To Create An ArrayList From the MySQL database
I have a table members in the MySQL database. I am
going to supply a String editor and search the
members table. All those who are editor will be
picked up and their name will enter the array I want
to create.
How do I code it and return this array?
Here is a sketch of my code. I do not have any
confidence in my code. I tend to creat a bean for
this ArrayList.
EditorBean editorList = null;
try
{
conn = DBConnection.getDBConnection();
stmt = conn.createStatement();
String query = SELECT user_name FROM members
WHERE user_role = ' + userrole + ';
rs = stmt.executeQuery( query );
if ( start = 0 rs.absolute( start + 1 ) )
{
boolean hasNext = false;
List editors = new ArrayList();
do
{
editors.add(new EditorBean(
rs.getString( user_name ) );
} while ( ( hasNext = rs.next() ) (
--count 0 ) );
editorList = new EditorBean( editors,
start, hasNext );
}
else
{
editorList = null:
}
return editorList;
}
__
Do you Yahoo!?
Free Pop-Up Blocker - Get it now
http://companion.yahoo.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
