Re: Examples of savepoints and transactions
Kind Regards / Med venlig hilsen Lars Nielsen > - Original Message - >> From: "Lars Nielsen" <l...@lfweb.dk> >> To: "MySql" <mysql@lists.mysql.com> >> Sent: Tuesday, 23 January, 2018 23:19:29 >> Subject: Re: Examples of savepoints and transactions > >>> Den 22-01-2018 kl. 22:01 skrev shawn l.green: >>> Hello Lars, >>> >>>> On 1/21/2018 3:37 PM, Lars Nielsen wrote: >>>> Hi, >>>> I have a system that uses begin and commit transactions. It works >>>> like a dream! ;) >>>> Now I want to test it by creating test data. This how ever cannot be >>>> rolled back. I think the solution for rolling back test data is to >>>> use savepoints and rollback. I think it is hard to find examples of >>>> this scenario. Are there some good guides or tutorials out there >>>> somewhere? Any suggestions are welcome. >>>> >>>> Best regards >>>> Lars Nielsen >>>> >>> >>> Can you mock up an example (a simple text walkthrough) of how you >>> think a savepoint should work with what you are calling "test data" ? >>> I think that the term "test data" is too general to make much sense to >>> most of us in the context you described. >>> >>> >>> Yours, >> Hello Shawn, >> Thanks for your interest. Here is an example of my idea. >> >> I have a php site working through PDO connections. I insert some data >> through php like this : >> >> |START TRANSACTION; SELECT @A:=SUM(salary) FROM table1 WHERE type=1; >> UPDATE table2 SET summary=@A WHERE type=1; INSERT INTO table3 values (x, >> y, z); COMMIT; ||| >> >> ||Now I want to do automated tests that create "dummy" data that i want >> to remove after the test has finished: >> like this : >> >> |SAVEPOINT autotest1; START TRANSACTION; SELECT @A:=SUM(salary) FROM >> table1 WHERE type=1; UPDATE table2 SET summary=@A WHERE type=1; INSERT >> INTO table3 values (x, y, z); COMMIT; -- DO OTHER TRANSACTIONAL >> OPERATIONS ROLLBACK TO autotest1; ||| >> >> ||All done. I have tested the application and have cleaned up the dummy >> test-data. >> >> The issue is that when I call the first commit then the savepoint is >> deleted. >> >> Is this possible at all? >> >> Regards Lars >> >> || >> > > -- > The bay-trees in our country are all wither'd > And meteors fright the fixed stars of heaven; > The pale-faced moon looks bloody on the earth > And lean-look'd prophets whisper fearful change. > These signs forerun the death or fall of kings. > -- Wm. Shakespeare, "Richard II" > Den 24. jan. 2018 kl. 14.50 skrev Johan De Meersman <vegiv...@tuxera.be>: > > What you're looking for is simple backup and restore :-) > > Savepoints are, simply put, markers within a transaction; allowing you to > rollback only part of a transaction instead of the whole thing. A commit will > inevitably commit the ENTIRE transactions, and thus remove the savepoints. > > A typical workflow for the kind of thing you're trying to do is to have your > (automated) testing framework restore last night's backup after the test run. > You could also make a backup before the test run and restore that afterwards; > have an automated nightly db copy from prod to dev; or in very specific cases > you could simply have your test system revert the data by issuing the > "reverse" queries - although that one is rarely an option in real life. > > Another alternative would be to take a filesystem (or virtual machine) > snapshot, and revert to that after the tests. Filesystem snapshots will > require your database to be stopped and started, though. > > /Johan > Thanks Johan, I understood the savepoints could be around transactions and not within! I know how to restore from a backup. I just wanted to avoid loading 500+GB after each test-run. :) Thanks for your help everyone. /Lars
Re: Examples of savepoints and transactions
Den 22-01-2018 kl. 22:01 skrev shawn l.green: Hello Lars, On 1/21/2018 3:37 PM, Lars Nielsen wrote: Hi, I have a system that uses begin and commit transactions. It works like a dream! ;) Now I want to test it by creating test data. This how ever cannot be rolled back. I think the solution for rolling back test data is to use savepoints and rollback. I think it is hard to find examples of this scenario. Are there some good guides or tutorials out there somewhere? Any suggestions are welcome. Best regards Lars Nielsen Can you mock up an example (a simple text walkthrough) of how you think a savepoint should work with what you are calling "test data" ? I think that the term "test data" is too general to make much sense to most of us in the context you described. Yours, Hello Shawn, Thanks for your interest. Here is an example of my idea. I have a php site working through PDO connections. I insert some data through php like this : |START TRANSACTION; SELECT @A:=SUM(salary) FROM table1 WHERE type=1; UPDATE table2 SET summary=@A WHERE type=1; INSERT INTO table3 values (x, y, z); COMMIT; ||| ||Now I want to do automated tests that create "dummy" data that i want to remove after the test has finished: like this : |SAVEPOINT autotest1; START TRANSACTION; SELECT @A:=SUM(salary) FROM table1 WHERE type=1; UPDATE table2 SET summary=@A WHERE type=1; INSERT INTO table3 values (x, y, z); COMMIT; -- DO OTHER TRANSACTIONAL OPERATIONS ROLLBACK TO autotest1; ||| ||All done. I have tested the application and have cleaned up the dummy test-data. The issue is that when I call the first commit then the savepoint is deleted. Is this possible at all? Regards Lars ||
Examples of savepoints and transactions
Hi, I have a system that uses begin and commit transactions. It works like a dream! ;) Now I want to test it by creating test data. This how ever cannot be rolled back. I think the solution for rolling back test data is to use savepoints and rollback. I think it is hard to find examples of this scenario. Are there some good guides or tutorials out there somewhere? Any suggestions are welcome. Best regards Lars Nielsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
SV: Mysql goes down when executing query
Hi Try setting these variables in you conf: set-variable = innodb_buffer_pool_size=128M set-variable = innodb_additional_mem_pool_size=10M you have to experiment with the size as it depends on how much ram you hardware has. Nickolai Nielsen -Oprindelig meddelelse- Fra: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sendt: 14. september 2004 22:39 Til: MySql List Emne: Mysql goes down when executing query Hi, Sorry to disturb but Mysql 4.1.4 gamma goes down when executing this query. I've tryed the same query without the coalesce function and the problem persists. select coalesce(viehc,0), coalesce(vieapellido,0), coalesce(vienombres,0), coalesce(viedoc,0), coalesce(numero,0), coalesce(apellido,0), coalesce(nombres,0), coalesce(f_nacimiento,0), coalesce(sexo,0), coalesce(doc_numero,0) from zzg_int.compara into outfile /tmp/compa.txt fields terminated by ',' lines terminated by '\r\n'; This is what the error log shows. Version: '4.1.4-gamma-standard-log' socket: '/tmp/mysql.sock' port: 3306 Official MySQL-standard binary mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=16777216 read_buffer_size=258048 max_used_connections=13 max_connections=100 threads_connected=10 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 92783 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x4b22efb8 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbfddeb68, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x808a183 0x82d3cb8 0x80ae46f 0x809d894 0x8097e4f 0x80977d8 0x8096f17 0x82d146c 0x82fa9fa New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x86da708 = EXPLAIN select coalesce(viehc,0),coalesce(vieapellido,0),coalesce(vienombres,0),coalesce(vi edoc,0),coalesce(numero,0), coalesce(apellido,0),coalesce(nombres,0),coalesce(f_nacimiento,0),coalesce(s exo,0),coalesce(doc_numero,0) from hrrg_int.compara into outfile /tmp/compa.txt fields terminated by ',' lines terminated by '\r\n' thd-thread_id=632 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. Number of processes running now: 0 040914 13:15:00 mysqld restarted 040914 13:15:00 [ERROR] Warning: Asked for 196608 thread stack, but got 126976 040914 13:15:00 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 040914 13:15:00 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 281648573. InnoDB: Doing recovery: scanned up to log sequence number 0 281648583 InnoDB: Last MySQL binlog file position 0 79779, file name ./hrrgp01-bin.05 040914 13:15:00 InnoDB: Flushing modified pages from the buffer pool... 040914 13:15:00 InnoDB: Started; log sequence number 0 281648583 /usr/local/mysql/bin/mysqld: ready for connections. Version: '4.1.4-gamma-standard-log' socket: '/tmp/mysql.sock' port: 3306 Official MySQL-standard binary -- 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]
SV: ASP Connection to Mysql fails
Hi
Try doing a response.write conn after you have made a connection with the
DSN
that should print out the connection string the DSN is using
i hope this help debug the problem
Nickolai
Paul Stearns wrote:
Actually I can connect from the web server to the DB server using DSN (the
odbc tool under windows). What I cannot do is connect from ASP in the same
way.
I have a work around, but I still would like to determine what the problem
is with a DSNless connection.
The work around is to use a DSN entry and call it, for example;
conn.open dsn=myodbcconn; user=myuser; password=mypassword
where myodbcconn is a System DSN
Why doesn't;
conn.open DRIVER={MySQL ODBC 3.51 Driver}; SERVER=myserver.com;
USER=myuser; PASSWORD=mypassword; DATABASE=myDB; PORT:3307; OPTION=35;
work?
Yes the port number really is 3307.
Paul
-Original Message-
From: Michael Stassen [EMAIL PROTECTED];]
Sent: 8/15/2004 8:05:23 PM
To: [EMAIL PROTECTED]
Subject: Re: ASP Connection to Mysql fails
If I understand you correctly, you can connect to both mysql servers from
localhost, but not to one from the other. To mysql, a user is a
combination
of user and connecting host, so I think your problem translates as you can
connect as [EMAIL PROTECTED], but you cannot connect as
[EMAIL PROTECTED]
You probably need to add a mysql user to allow this. See the manual
for
the details: http://dev.mysql.com/doc/mysql/en/Privilege_system.html and
http://dev.mysql.com/doc/mysql/en/GRANT.html.
Michael
Paul Stearns wrote:
Ignore the sentence that starts Remember...
When I telnet to port 3307 on the DB server from the web server I get the
version, a few characters of garbage and it disconnects.
Paul
-Original Message-
From: Paul Stearns [EMAIL PROTECTED];]
Sent: 8/15/2004 6:01:59 PM
To: [EMAIL PROTECTED]
Subject: ASP Connection to Mysql fails
I have an ASP application running on a server with a mysql DB, everything
works great. I connect using localhost, no problems.
I have a second mysql DB on another server. I connect to it via the odbc
Data Source Administrator from the web server with no problems.
When I try to connect from an ASP script from the web server using the
following;
=
Set connMailServer = Server.CreateObject(ADODB.Connection)
ConnectString = DRIVER={MySQL ODBC 3.51 Driver}; SERVER=myserver.com;
USER=root; PASSWORD=rootPW; DATABASE=myDB; PORT:3307; OPTION=35;
IntDebug=connMailServer.Open(ConnectString)
=
The server, user, password, database and port are what I used in the odbc
Data Source Administrator. Remember I can connect to my local DB from ASP.
I receive the following error;
==
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[MySQL][ODBC 3.51 Driver]Can't connect to MySQL server on 'myserver.com'
(10061)
=
The environment is w2k server on both computers. The MYSql version on
remote server is 4.0.17.
I'm so confused...
Paul
--
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]
SV: MySQL/InnoDB crashes system
Hi I still got this freeze problem, i have found out that this bug is related to InnoDB, i converted the table that gives problems back to MyISAM, and the dump operation runs fine just as it did before, but as soon as i convert it to InnoDB and dump this table my system freezes but not the first time, usually i can do a dump 2-10 times before it goes wrong. I am certain that it is not a heat problem, or any other hardware problem it could be a conflict between a driver and MySQL. I got 2 identical servers running in a replication setup, i do the testing on my backup server but i can create the freeze on both servers, we run on MySQL 4.0.18, but i have also tried 4.0.20a and 4.1.3-beta. the servers config: Intel Pentium 4 2.53 Ghz QDI Superb 4E-A 533 motherboard 1gb DDR333 ram 2x Seagate CHEETAH 73.5 gb U160 SCSI in Raid 1 Adaptec 2110S Raid controller Windows 2000 Server UK 3Com 996B Gigabit NIC The servers have SP4 and all updates if anyone have an idea on how to troubleshoot this? Thanks Nickolai Nielsen -Oprindelig meddelelse- Fra: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sendt: 9. juli 2004 05:07 Til: [EMAIL PROTECTED] Emne: Re: MySQL/InnoDB crashes system Nickolai, this very much sounds like a hardware fault. No MySQL or InnoDB bug should be able to freeze the WHOLE operating system. And SELECT ... INTO OUTFILE ... is a very basic operation in the database. Regards, Heikki - Original Message - From: Nickolai Nielsen [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, July 08, 2004 8:33 PM Subject: MySQL/InnoDB crashes system hi this SQL frezes the system: SELECT * FROM journal into OUTFILE 'c:/Backup/current/journal.asc' FIELDS terminated by '|' LINES terminated by '\r\n' this started after the table was converted to InnoDB, usualy it runs normaly the first time, but on 2-5 run it frezes the system so i have to reboot the server. System: Windows 2000 Server MySQL 4.0.18 commandline: mysqld --console --log --log-warnings --debug=d,info,error,query,general,whe re:O,/mysqld_3.trace this is a trace output: do_command: info: Command on TCP/IP (17560) = 3 (Query) dispatch_command: query: SELECT * FROM ptanamid into OUTFILE 'c:/30Min-Backup/current_stress/ptanamid.asc' FIELDS terminated by '|' LINES terminated by '\r\n' openfrm: info: i_count: 0 i_parts: 0 index: 3 n_length: 19 int_length: 0 open_table: info: inserting table 02A5B348 into the cache mi_lock_database: info: lock_type: 2 mi_get_status: info: key_file: 258048 data_file: 126648 init_io_cache: info: init_io_cache: cachesize = 131072 Info about JOIN ptanamid type: ALL q_keys:0 refs: 0 key: -1 len: 0 init_read_record: info: using rr_sequential init_io_cache: info: init_io_cache: cachesize = 131072 mi_lock_database: info: lock_type: 0 mi_lock_database: info: old lock: 2 mi_lock_database: info: changed: 0 w_locks: 0 do_select: info: 5277 records output dispatch_command: info: query ready close_thread_tables: info: thd-open_tables=02A5B348 do_command: info: Command on TCP/IP (17560) = 3 (Query) dispatch_command: query: SELECT * FROM ptanamnese into OUTFILE 'c:/30Min-Backup/current_stress/ptanamnese.asc' FIELDS terminated by '|' LINES terminated by '\r\n' openfrm: info: i_count: 0 i_parts: 0 index: 4 n_length: 53 int_length: 0 open_table: info: inserting table 02A5AC40 into the cache mi_lock_database: info: lock_type: 2 mi_get_status: info: key_file: 9187328 data_file: 9766188 init_io_cache: info: init_io_cache: cachesize = 131072 Info about JOIN ptanamnesetype: ALL q_keys:0 refs: 0 key: -1 len: 0 init_read_record: info: using rr_sequential init_io_cache: info: init_io_cache: cachesize = 131072 *** here it crashed *** Thanks Nickolai Nielsen -- 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]
SV: MySQL/InnoDB crashes system
Hi Again i forgot to mention that this table has 527101 rows, and takes 90mb when it is dumped to the disk. I dont have this freeze problem when i dump the smaller smaller tables that also uses InnoDB. i Also discovered that it is not on the same posistion ind the dump file the lockup happens. Nickolai -Oprindelig meddelelse- Fra: Nickolai Nielsen [mailto:[EMAIL PROTECTED] Sendt: 10. juli 2004 16:28 Til: [EMAIL PROTECTED] Emne: SV: MySQL/InnoDB crashes system Hi I still got this freeze problem, i have found out that this bug is related to InnoDB, i converted the table that gives problems back to MyISAM, and the dump operation runs fine just as it did before, but as soon as i convert it to InnoDB and dump this table my system freezes but not the first time, usually i can do a dump 2-10 times before it goes wrong. I am certain that it is not a heat problem, or any other hardware problem it could be a conflict between a driver and MySQL. I got 2 identical servers running in a replication setup, i do the testing on my backup server but i can create the freeze on both servers, we run on MySQL 4.0.18, but i have also tried 4.0.20a and 4.1.3-beta. the servers config: Intel Pentium 4 2.53 Ghz QDI Superb 4E-A 533 motherboard 1gb DDR333 ram 2x Seagate CHEETAH 73.5 gb U160 SCSI in Raid 1 Adaptec 2110S Raid controller Windows 2000 Server UK 3Com 996B Gigabit NIC The servers have SP4 and all updates if anyone have an idea on how to troubleshoot this? Thanks Nickolai Nielsen -Oprindelig meddelelse- Fra: Heikki Tuuri [mailto:[EMAIL PROTECTED] Sendt: 9. juli 2004 05:07 Til: [EMAIL PROTECTED] Emne: Re: MySQL/InnoDB crashes system Nickolai, this very much sounds like a hardware fault. No MySQL or InnoDB bug should be able to freeze the WHOLE operating system. And SELECT ... INTO OUTFILE ... is a very basic operation in the database. Regards, Heikki - Original Message - From: Nickolai Nielsen [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, July 08, 2004 8:33 PM Subject: MySQL/InnoDB crashes system hi this SQL frezes the system: SELECT * FROM journal into OUTFILE 'c:/Backup/current/journal.asc' FIELDS terminated by '|' LINES terminated by '\r\n' this started after the table was converted to InnoDB, usualy it runs normaly the first time, but on 2-5 run it frezes the system so i have to reboot the server. System: Windows 2000 Server MySQL 4.0.18 commandline: mysqld --console --log --log-warnings --debug=d,info,error,query,general,whe re:O,/mysqld_3.trace this is a trace output: do_command: info: Command on TCP/IP (17560) = 3 (Query) dispatch_command: query: SELECT * FROM ptanamid into OUTFILE 'c:/30Min-Backup/current_stress/ptanamid.asc' FIELDS terminated by '|' LINES terminated by '\r\n' openfrm: info: i_count: 0 i_parts: 0 index: 3 n_length: 19 int_length: 0 open_table: info: inserting table 02A5B348 into the cache mi_lock_database: info: lock_type: 2 mi_get_status: info: key_file: 258048 data_file: 126648 init_io_cache: info: init_io_cache: cachesize = 131072 Info about JOIN ptanamid type: ALL q_keys:0 refs: 0 key: -1 len: 0 init_read_record: info: using rr_sequential init_io_cache: info: init_io_cache: cachesize = 131072 mi_lock_database: info: lock_type: 0 mi_lock_database: info: old lock: 2 mi_lock_database: info: changed: 0 w_locks: 0 do_select: info: 5277 records output dispatch_command: info: query ready close_thread_tables: info: thd-open_tables=02A5B348 do_command: info: Command on TCP/IP (17560) = 3 (Query) dispatch_command: query: SELECT * FROM ptanamnese into OUTFILE 'c:/30Min-Backup/current_stress/ptanamnese.asc' FIELDS terminated by '|' LINES terminated by '\r\n' openfrm: info: i_count: 0 i_parts: 0 index: 4 n_length: 53 int_length: 0 open_table: info: inserting table 02A5AC40 into the cache mi_lock_database: info: lock_type: 2 mi_get_status: info: key_file: 9187328 data_file: 9766188 init_io_cache: info: init_io_cache: cachesize = 131072 Info about JOIN ptanamnesetype: ALL q_keys:0 refs: 0 key: -1 len: 0 init_read_record: info: using rr_sequential init_io_cache: info: init_io_cache: cachesize = 131072 *** here it crashed *** Thanks Nickolai Nielsen -- 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/InnoDB crashes system
hi this SQL frezes the system: SELECT * FROM journal into OUTFILE 'c:/Backup/current/journal.asc' FIELDS terminated by '|' LINES terminated by '\r\n' this started after the table was converted to InnoDB, usualy it runs normaly the first time, but on 2-5 run it frezes the system so i have to reboot the server. System: Windows 2000 Server MySQL 4.0.18 commandline: mysqld --console --log --log-warnings --debug=d,info,error,query,general,whe re:O,/mysqld_3.trace this is a trace output: do_command: info: Command on TCP/IP (17560) = 3 (Query) dispatch_command: query: SELECT * FROM ptanamid into OUTFILE 'c:/30Min-Backup/current_stress/ptanamid.asc' FIELDS terminated by '|' LINES terminated by '\r\n' openfrm: info: i_count: 0 i_parts: 0 index: 3 n_length: 19 int_length: 0 open_table: info: inserting table 02A5B348 into the cache mi_lock_database: info: lock_type: 2 mi_get_status: info: key_file: 258048 data_file: 126648 init_io_cache: info: init_io_cache: cachesize = 131072 Info about JOIN ptanamid type: ALL q_keys:0 refs: 0 key: -1 len: 0 init_read_record: info: using rr_sequential init_io_cache: info: init_io_cache: cachesize = 131072 mi_lock_database: info: lock_type: 0 mi_lock_database: info: old lock: 2 mi_lock_database: info: changed: 0 w_locks: 0 do_select: info: 5277 records output dispatch_command: info: query ready close_thread_tables: info: thd-open_tables=02A5B348 do_command: info: Command on TCP/IP (17560) = 3 (Query) dispatch_command: query: SELECT * FROM ptanamnese into OUTFILE 'c:/30Min-Backup/current_stress/ptanamnese.asc' FIELDS terminated by '|' LINES terminated by '\r\n' openfrm: info: i_count: 0 i_parts: 0 index: 4 n_length: 53 int_length: 0 open_table: info: inserting table 02A5AC40 into the cache mi_lock_database: info: lock_type: 2 mi_get_status: info: key_file: 9187328 data_file: 9766188 init_io_cache: info: init_io_cache: cachesize = 131072 Info about JOIN ptanamnesetype: ALL q_keys:0 refs: 0 key: -1 len: 0 init_read_record: info: using rr_sequential init_io_cache: info: init_io_cache: cachesize = 131072 *** here it crashed *** Thanks Nickolai Nielsen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Obscure FULLTEXT search problems
Hi there, I have some trouble understanding some results I'm getting when executing a SELECT statement in an FULLTEXT indexed table in my MySql db. In scenario 1 I'm using this SQL-statement: SELECT * FROM tArticles WHERE ( Title LIKE '%HYDRO%' OR Body LIKE '%HYDRO%' ) In scenario 2 I'm using this SQL-statement: SELECT * FROM tArticles WHERE ( MATCH( Title, Body ) AGAINST( 'HYDRO' ) ) My problem is that if I'm searching for a word (For instance HYDRO) that are found in a lot of my articles, then scenario 1 is much faster than scenario 2. But if the search word is in just a few of the articles then scenario 2 is _much_ faster than scenario 1. Can someone explain this to me? What do I do? I'm running MySql 3.23.46 on Solaris 8. My table creation statement looks like this: CREATE TABLE tArticles ( ArticleId int(11) NOT NULL auto_increment, Title varchar(255) NOT NULL default '', Author varchar(255) NOT NULL default '', Version int(3) NOT NULL default '0', TickerCodes varchar(255) NOT NULL default '', SubjectCodes varchar(255) NOT NULL default '', MessageNum varchar(20) NOT NULL default '', Timestamp datetime NOT NULL default '-00-00 00:00:00', Body text NOT NULL, Footer varchar(255) NOT NULL default '', PRIMARY KEY (ArticleId), KEY Timestamp (Timestamp), FULLTEXT KEY Title (Title,Body) ) TYPE=MyISAM; -- Jakob Vad Nielsen [EMAIL PROTECTED] NHST - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: HELP - Having serious trouble here...
On Mon, 2002-10-14 at 16:51, John Hinton wrote: I'm having the same problems. I'm on a RedHat machine... 7.2 running MySQL ver. 3.23.41. I've been told to upgrade my package. Me too. After reading your posting I realized that glibc probably was the root of the problems (my problems started after upgrading glibc). After I downgraded glibc back to glibc-2.2.4-29 on the server I could once again connect to mysql over network. -- Anders Nielsen [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: HELP - Having serious trouble here...
On Mon, 2002-10-14 at 17:29, Anders Nielsen wrote: On Mon, 2002-10-14 at 16:51, John Hinton wrote: I'm having the same problems. I'm on a RedHat machine... 7.2 running MySQL ver. 3.23.41. I've been told to upgrade my package. Me too. After reading your posting I realized that glibc probably was the root of the problems (my problems started after upgrading glibc). After I downgraded glibc back to glibc-2.2.4-29 on the server I could once again connect to mysql over network. The problem is already filed in bugzilla. See https://bugzilla.redhat.com/bugzilla/show_bug.cgi?id=75664 Let's hope redhat will fix this soon. -- Anders Nielsen [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mysql eating up diskspace.
Hello everyone. I have had this very strange problem with Mysql, it seems to start eating diskspace untill the disk is full, restarting the mysql process seems to free all the used space again. Any idea what could be wrong? Kind regards Michael Wulff Nielsen - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Non Ascii characters
hey If the text is supposed to be parsed from a mySQL to a HTML webpage, using #128; or euro; should display a € sign. A simple replace would then do the trick. Doc - Original Message - From: Haapanen, Tom [EMAIL PROTECTED] To: Mysql@Lists. Mysql. Com [EMAIL PROTECTED] Sent: Wednesday, October 03, 2001 1:15 PM Subject: RE: Non Ascii characters Jamie, This is likely a client software issue. We use Perl and DBI, and we have no trouble storing or retrieving non-USASCII characters. What are you using to access the MySQL database? Tom Haapanen [EMAIL PROTECTED] -Original Message- From: Jamie Smith [mailto:[EMAIL PROTECTED]] Sent: 03 October 2001 06:23 To: Mysql@Lists. Mysql. Com Subject: Non Ascii characters I'm having a problem with the sending of the new euro € symbol into the mysql database through a standard SQL stament. I have tried everything cut and paste or ALT 0128 on the command line but all I seem to get is a ? character stored. I know it can be done because I have managed to enter the symbol using a program 'ArtAdmin' by artronic to enter the data but this will not be usable because I am trying to write and web based from system to submit information. Can anyone tell me how to get this symbol in and are there any others that might casue trouble. I have not been able to find anything on this in the manual or previous posts. TIA Jamie - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Replication question
Hi there, My situation is like this: I have a local server behind a firewall. On the outside I have a webserver. I want the local server to act like a master for replications, and the external server to act like a slave. This way I can make the changes on my database directly on the local server. But the problem is that I can't connect to the local server from the external server because of the firewall. Is there a way where I can have the external server acting as a slave, but where the master a doing the connection stuff? in other words : The local server can do a connection to the external server, but the external server can't open a connection to the local server. Please help me out! -- Jakob Vad Nielsen System Developer NHST/TDN/EUROPOWER - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: NT installation
Thanks Jean - Claude, NT Workstation comes with a built-in client. but from what I can tell, NT Server does not come with a built-in Telnet Server. The NT book I have says : "The Telnet program requires TCP/IP on both the client and server, and requires an account set up on the server being contacted. Microsoft NT doesn't provide the server process, but it does provide the client interface. " Am i interpreting this correctly? If so, does mySQL provide the Telnet Server process? Thanks again, Chad -Original Message- From: jcmaes [mailto:[EMAIL PROTECTED]] Sent: Tuesday, March 27, 2001 11:47 AM To: Chad Nielsen Cc: [EMAIL PROTECTED] Subject: Re: NT installation Hi Chad You do not need a Telnet client. Just install Myodbc and the mysql client. To connect to your server, type "mysql -h yourhostname -u userID" If you defined a password for the userID, add -p at the end of the line. The system will prompt you for the password. See manual for more options. You can also install a web server (PWS or Infradig) and the PHP scripting langage on your local machine, and run PHPmyAdmin. Take care to protect your local machine for intrusions. Hope this helps you, Jean-Claude , or install - Original Message - From: "Chad Nielsen" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, March 27, 2001 6:57 PM Subject: NT installation Hi, I'm trying to install mySQL on NT Server 4.0 I guess I need to install a Telnet Server so I can manage mySQL from a workstation. What's a good Cheap or Free Telnet Server for NT? Also, any instructions on how to set this up would be great. Thanks, Chad Chad M. Nielsen, GIS Programmer/Analyst Tillamook County Performance Partnership (TCPP) Tillamook Coastal Watershed Resource Center (TCWRC) GIS: http://gisweb.co.tillamook.or.us TCPP: http://www.co.tillamook.or.us/countygovernment/Estuary/homepage.htm TCWRC: http://www.tcwrc.org - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
searching tables.
Hi Everyone, This question may have been asked before, but i could not find it in the archives, so i was wondering if someone could help me out. Say i had the following table: +++-+ | id | address| domainalias | +++-+ | 2 | [EMAIL PROTECTED] | fred| | 3 | [EMAIL PROTECTED] | bill| +++-+ Now say i had over 200 entrys in that table, but all i wanted to see was just "[EMAIL PROTECTED]" but i did not know what "id" it was, is there a way i can "search" the table, and only show the relevent information that i need? Thanks in Advanced. John Nielsen. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
auto_increments
Hey everyone, This may have already come up a couple of times in the mailling list, but I I was wondering with mysql-3.22.32 if you can have two auto_increment's in one table, e.g.: +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | username | char(32) | YES | | NULL|| | passwd | char(32) | YES | | NULL|| | uid | mediumint(9) | | PRI | 0 | auto_increment | | gid | mediumint(9) | YES | | NULL|| | gecos| char(32) | YES | | NULL|| | home_dir | char(32) | YES | | NULL|| | shell| char(32) | YES | | NULL|| | maildrop | char(128)| YES | | NULL|| | id | tinyint(4) | | PRI | 0 | auto_increment | +--+--+--+-+-++ Now i know this table will not work with the two PRI keys, but i was wondering *if* i could change one to just another key, as well as keep it's auto_increment's on both if possible. Thanks in Advanced. John. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: CREATE TABLE / Telnet (Linux / Unix server)
...and this will load the table into your database. Now, in the book it doesn't specify whether or not you have to be on the computer with the server, or if you can send the .sql file command from your computer to the remote server, sort of like a file transfer protocol as well. Basically, my question is, do I have to either FTP my files to the server, and then execute the command, or can I do it remotely without FTP? Or is it possible AT ALL to execute the command from a remote computer? In case it helps, I am on a Windows 98 system connected through SSH to an Linux system running Apache. Please help, I'm stuck and I won't give up until I'm un-stuck! Thanks in advance to all that reply. The file must be on the remote machine (as has been answered), but yes, if you are connected to the shell (via telnet/ssh/ssh2/etc), you can execute the command. And, no, the extension may be anything (all you're doing is redirecting the contents of the file (via '') into the 'mysql database' command. HTH, Jon A. Nielsen. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
General MySQL Questions
Greetings! I am one of the developers of a MUD, and we have recently decided to rewrite our entire driver to C++/OOP and use MySQL to store our data, so it has become my duty to begin to learn about all the facets of MySQL. I have been going through FAQs, manuals, etc, and we are also using MySQL++ to interface to the database. Here are a few general questions that I've got at this point: 1. We would like to make saving/retrieving information from the database non-blocking, if possible. Does anyone have any suggestion for this? Our thoughts were to write an external program to fetch information and return it to our main program; is there any easier way to handle this? Is it even necessary to make calls to the database non-blocking? 2. When our driver boots and begins accepting connections, would it be more feasible to read the various tables into memory structures, rather than querying the database everytime we need information on a specific item within the game? Thanks for your help! Also, if anyone might be willing to help us in a forum other than the list, a personal reply would be great. (it would not, of course, be a paying position, but just for asking advice) --- Jon A. Nielsen Lazarus of Spear of Insanity MUD http://spear.kilnar.com/ telnet://spear.kilnar.com:1066/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
