Re: TimeZone
Joseph Cochran wrote: Some countries have multiple timezones, so it is not sufficient to know the country code in order to get the timezone. If they have previously posted the timezone, however, then it should be possible to store that information in a cookie on the client machine that your web layer can retrieve. If you want to permanently tie a timezone to a user (assuming that this is an internal system or other system to which your users authenticate -- if it is a public website you're going to have to use cookies), simply include an extra column in the user's record that has a number that stores its differential from GMT (so the USA east coast would be -5) and save all of your data in GMT, applying the timezone column to the time via datetime functions either in the query or in your web layer. One more complication: daylight savings time are not the same world wide. So I would store the time zone and not the difference with GMT. I personally would do al the time zone calculations in the web layer. Most OSs have libraries with more or less knowledge about daylight savings in various countries/timezones. Using the functions in the language of the web layer you're more likely to get things right. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to find out the name of the server
Hi every probably silliest question ever posted: we are running mysql on serveral hosts and sometime it is important to know, which host is the mysql server you are connected to. this is not a session nor a global variable, there is no show statement... what is it else? how to ask a mysql client the name of the server you are connected to? suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how to find out the name of the server
Hello,
If the clients and servers are both Windows systems, then you might want
to try the following script. You need administrator rights for it on the
network and the server must be running as it tries to retrieve a running
process from the server. I am not sure if there is another way and it
only works on Windows. But if there is another way, then I would like to
know that too!
'--Begin Code--
Set FSO = CreateObject(Scripting.FileSystemObject)
Set objTextFile = FSO.CreateTextFile(MySQL.csv, True)
For i = 100 To 105
strIP = 192.168.128. CStr(i)
Set PingResults =
GetObject(winmgmts:{impersonationLevel=impersonate}//./root/cimv2) _
.ExecQuery(SELECT * FROM Win32_PingStatus WHERE Address
= ' + strIP + ')
For Each PingResult In PingResults
If PingResult.StatusCode = 0 Then
If LCase(strIP) =
PingResult.ProtocolAddress Then TestComputer(strIP)
End If
Next
Next
Set objTextFile = Nothing
Set FSO = Nothing
Sub TestComputer(strComputer)
Set MySQLProc = GetObject(winmgmts:\\ strComputer
\root\cimv2).ExecQuery(Select * from Win32_Process,,48)
For Each ProcItem in MySQLProc
sProc = ProcItem.Caption
iProc = InStr(1, sProc, mysqld, vbtextcompare)
arOS = Split(ProcItem.OSName, |)
If iProc 0 Then
objTextFile.WriteLine strIP ; sProc ;
arOS(0) ; ProcItem.WindowsVersion
End If
Next
End Sub
'--End Code--
HTH,
Arjan.
-Original Message-
From: suomi [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 09, 2005 09:07 AM
To: mysql@lists.mysql.com
Subject: how to find out the name of the server
Hi every
probably silliest question ever posted:
we are running mysql on serveral hosts and sometime it is important to
know, which host is the mysql server you are connected to.
this is not a session nor a global variable, there is no show
statement...
what is it else? how to ask a mysql client the name of the server you
are connected to?
suomi
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
The information contained in this communication and any attachments is
confidential and may be privileged, and is for the sole use of the intended
recipient(s). Any unauthorized review, use, disclosure or distribution is
prohibited. If you are not the intended recipient, please notify the sender
immediately by replying to this message and destroy all copies of this message
and any attachments. ASML is neither liable for the proper and complete
transmission of the information contained in this communication, nor for any
delay in its receipt.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update delay
Hello. If you send your configuration file there might be much more suggestions. In my opinion SET AUTOCOMMIT=0 before update should improve performance (don't forget to COMMIT after transaction). If you're sure in your data you can SET FOREING_KEY_CHECK=0 and SET UNIQUE_CHECKS=0. See: http://dev.mysql.com/doc/mysql/en/innodb-tuning.html javabuddy [EMAIL PROTECTED] wrote: On an InnoDB table for 70k records the update action is taking so much time.(More than 30minutes). We got the innodb_buffer_pool_size as 4gigs. IS there anything more to add up to get the processes kick its speed. - javabuddy. People are conversing... without posting their email or filling up their mail box. ~~112352645~~ roomity.com http://roomity.com/launch.jsp No sign up to read or search this Rich Internet App -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: Mysql 4.1.13 Problems
Hello. I have noticed that since the update i have multiple instances of mysql running which seem to be causing the problem ps uax output below I usually see a lot of mysqld processes (these are just different threads of the same process), and I don't see anything wrong with it: [EMAIL PROTECTED] gleb]$ ps uax |grep gleb |grep mysqld |wc -l 23 4.1.13 from the Mysql site however since the upgrade i have been having problems trying to write lines in tables . Could you provide more information about this problem? Peter Nikolic [EMAIL PROTECTED] wrote: Hi I am newish to Mysql i recently upgraded my install on suse 9.2 to version 4.1.13 from the Mysql site however since the upgrade i have been having problems trying to write lines in tables . I have noticed that since the update i have multiple instances of mysql running which seem to be causing the problem ps uax output below root 5368 0.0 0.1 2592 1252 pts/3S19:16 0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/ds9.pid mysql 5401 5.0 1.2 40152 12576 pts/3 S19:16 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/ds9.pid --skip-locking --port=3306 --socket=/ mysql 5402 0.0 1.2 40152 12576 pts/3 S19:16 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/ds9.pid --skip-locking --port=3306 --socket=/ mysql 5404 0.0 1.2 40152 12576 pts/3 S19:16 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/ds9.pid --skip-locking --port=3306 --socket=/ mysql 5405 0.0 1.2 40152 12576 pts/3 S19:16 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/ds9.pid --skip-locking --port=3306 --socket=/ mysql 5406 0.0 1.2 40152 12576 pts/3 S19:16 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/ds9.pid --skip-locking --port=3306 --socket=/ mysql 5407 0.0 1.2 40152 12576 pts/3 S19:16 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/ds9.pid --skip-locking --port=3306 --socket=/ mysql 5411 0.0 1.2 40152 12576 pts/3 S19:16 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/ds9.pid --skip-locking --port=3306 --socket=/ mysql 5412 0.0 1.2 40152 12576 pts/3 S19:16 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/ds9.pid --skip-locking --port=3306 --socket=/ mysql 5413 0.0 1.2 40152 12576 pts/3 S19:16 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/ds9.pid --skip-locking --port=3306 --socket=/ mysql 5414 0.0 1.2 40152 12576 pts/3 S19:16 0:00 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/ds9.pid --skip-locking --port=3306 --socket=/ My question is how do i get back to the state of just one instance of mysql running as everything worked ok then .. Thanks in advance Pete . -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: malloc troubles on 64-bit machine
Hi Mattijs, Am Montag, den 08.08.2005, 14:56 +0200 schrieb Matthijs van der Klip: ... Linux 2.4 and 2.6 kernels have a setting for their overcommitment behaviour under /proc/sys/vm/overcommit_memory. The different settings are: ... For now I've set this to '2' which means the kernel won't overcommit anymore, just like any other proper OS... ;-) I am running with this setting too since you pointed me to it some time ago. I do not notice a difference though. Also it does not fix my memory leak. A 'fillmem' like tool can however reclaim the memory. Unfortunately it does also reclaim the space hold by the file system buffers. On my development system this well-filled file system buffer space is the most valuable resource. :( My experiments with the 'fillmem' like tool showed that just allocating memory does not show up in the 'Active' memory value. Only initializing the allocated memory does the trick. This means that the memory leak results from pages which have been in real use. One final question though: my experience with InnoDB is that it really, really likes to be able to fit all of it's data and keys into the buffer pool. This would limit the maximum size of my database to roughly 4GB in this case, correct? This is in a website hosting environment where the database is hit with about 1000 queries/s (mixed read/write). I do not believe this. Perhaps you mean that the performance degrades if the database is bigger than the cache. I this case you are right. But I can't think of any way to get around it. If you mean something else, I can't help you much with InnoDB. Please start a new thread with good Subject: on the MySQL mailing list and/or on the InnoDB forum (forums.mysql.com). Regards, Ingo -- Ingo Strüwing, Senior Software Developer MySQL AB, www.mysql.com Office: +49 30 43672407 Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
JOIN QUERY - UPDATE ... help?!
Hi, We are running mysql 3.23.58 and I want to do a query with joins from two tables and then insert the results into the column of a third. This appears to be harder than I realised with this version of mysql and I am banging my head against a wall. Please Help! ok first query. - select link_ID,of_group FROM page_elements LEFT JOIN pages ON page_elements.link_to=pages.page_ID LEFT JOIN links_DB_bk ON page_elements.link_ID=links_DB_bk.link_ID WHERE content_type='text' - Now I want to update table 3 (links_DB) using page_elements.link_ID=links_DB.link_ID usual update query doesn't seem to work in this circumstance .. any ideas?
Re: Exporting a database from one PC to another using MySQL 5.0
Hello. See: http://dev.mysql.com/doc/mysql/en/mysqldump.html http://dev.mysql.com/doc/mysql/en/moving.html http://dev.mysql.com/doc/mysql/en/backup.html Eric Dahlenburg [EMAIL PROTECTED] wrote: Hi, I am currently a student learning SQL. I have MySQL 5.0 installed at = home and on my Laptop. How can I take a database that I have updated on my = laptop and transfer it to my home PC so that they are both synchronized ? I tried looking on the forums for this info, but forums locks-up my = Internet Explorer for some reason. Thanks, Eric Eric Dahlenburg Spacecoastsales.net [EMAIL PROTECTED] 321-453-7627 Voice/ Fax 321-917-9098 Cell=20 This communication is intended solely for the use of the person(s) to = whom it is addressed. This communication may contain confidential information = or information otherwise subject to laws and regulations regarding its use, = and any unauthorized use, dissemination, distribution or copying of this communication, or any portion thereof, may therefore be legally = prohibited. If you are not the intended recipient of this communication you are not authorized to use, disseminate, distribute or copy this communication or = any portion thereof, and are requested to notify the sender by return email = and delete this communication from your system. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: Regarding Security Problem - Murali (India)
Hello. MySQL doesn't support Windows 98, and it's (Windows 98 certainly) design isn't enough secure. There're no file system which has support for file permissions. Upgrade to the fresher Windows (2k, XP, 2003) will allow you to solve this issue by protecting 'mysql' database from replacement by other users. Read about how the privilege system works in MySQL: http://dev.mysql.com/doc/mysql/en/privileges.html Murali [EMAIL PROTECTED] wrote: First I have to Thank you for creating such a wonderful backend. Problem Statment : My Operating System is Windows 98. I have set password for my MySql database. Its working fine. Following are the databases that i am using in My Data Folder Employee Sales Marketing Mysql Reports But if i replaced that Mysql database with the old one(which is not set password) it's not asking for the password. I know that is how it has been designed but to overcome this problem what shall I do? Please help me out. Thank you V.Murali India -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: Limit on fulltext match?
Hello. Nothing mentioned at: http://dev.mysql.com/doc/mysql/en/fulltext-restrictions.html So, in my opinion, the length of your AGAINST clause is limited by the max_packet_length. But you should strongly think about the performance of FULLTEXT searches with long search condition. From my experience it degrade a lot even with several terms. Does anyone know if there Is there a limit on the number/length of terms using match against(terms)? [EMAIL PROTECTED] wrote: -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: explain not explaining long running query?
Hello. State: Sending data MySQL server shouldn't spend several days in state of sending one row (your query should return only one row :) to the client. Server doesn't work properly and steps like upgrade or switching to the official binaries might be helpful. The query has been running for ~5 days now: Id: 27977 User: root Host: localhost db: fractyl Command: Query Time: 421540 State: Sending data Info: select count(*) from msgs where message_id 112000 and message_id 112111 I also tried using the BETWEEN syntax but it has exactly the same problem. Any other ideas? ds -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: how to find out the name of the server
Hello. You can determine the address of the server using 'status' command: mysql status -- mysql Ver 14.7 Distrib 4.1.9, for pc-linux-gnu (i686) Connection id: 532284 Current database: Current user: [EMAIL PROTECTED] SSL:Not in use Current pager: stdout Using outfile: '' Using delimiter:; Server version: 4.1.12-log Protocol version: 10 Connection: 10.100.1.176 via TCP/IP ^^ Server characterset:cp1251 Db characterset:latin1 Client characterset:latin1 Conn. characterset:cp1251 TCP port: 3306 Uptime: 23 days 6 hours 27 min 59 sec suomi [EMAIL PROTECTED] wrote: Hi every probably silliest question ever posted: we are running mysql on serveral hosts and sometime it is important to know, which host is the mysql server you are connected to. this is not a session nor a global variable, there is no show statement... what is it else? how to ask a mysql client the name of the server you are connected to? suomi -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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]
Failed to open rowset
I am doing a LEFT OUTER JOIN between 2 MySQL tables in Crystal Report Designer for a subreport. However, whenever I try to use a field from othe joined table I get the 'Failed to open rowset' message. How can I solve this problem? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to link to crystal report
Enrique Sanchez Vela [EMAIL PROTECTED] wrote on 08/09/2005 12:46:44 AM: --- Elizabeth Bonifacio [EMAIL PROTECTED] wrote: Hi guys, I'm new in database development, and has been wondering if anyone can suggest a good reporting tool that will help me generate graphical report for my database queries using visual c++? Can I use excell to format my queries into graph? If all you need is to query the database and create an Excel SpreadSheet, I'd use perl plus the DBI and Spreadsheet::WriteExcel modules. regards, esv. Enrique Sanchez Vela email: [EMAIL PROTECTED] What may be even more simple for her than learning PERL+DBI in order to create Excel files would be to use Excel's native DB querying capability and go through the MyODBC driver to get his data. Then the data would already be in Excel and she can just point a chart object to the data. The problem is, there seems to be a lack of good, inexpensive graphing and charting libraries for the windows platform as compared to the linux platform. Things like Crystal Reports can run you several hundred dollars, depending on what options you want. The other response in this thread listed several good (but not all of then C-based) graphing, charting, and reporting tools. I am sure she can probably find all of the tools/libraries she may need if she searches in the open source community. It may take a little work to port some of them to Win32 but I think that may be a fair trade for the lower cost and more flexible licensing. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: how to find out the name of the server
suomi [EMAIL PROTECTED] wrote on 08/09/2005 03:07:11 AM: Hi every probably silliest question ever posted: we are running mysql on serveral hosts and sometime it is important to know, which host is the mysql server you are connected to. this is not a session nor a global variable, there is no show statement... what is it else? how to ask a mysql client the name of the server you are connected to? suomi Have you thought about changing your prompt? put a setting in the my.cfg/my.ini of your client tools something like this: [mysql] prompt=\h.\d Details here: http://dev.mysql.com/doc/mysql/en/mysql.html and here: http://dev.mysql.com/doc/mysql/en/mysql-commands.html That setting gives me a command prompt that is the name of the server, a dot, the name of the database with which I am currently working, all followed by a . It looks like this: testserver1.testdb1 Would that help you keep it straight? Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: JOIN QUERY - UPDATE ... help?!
Brendan Gogarty [EMAIL PROTECTED] wrote on 08/09/2005 05:30:51 AM: Hi, We are running mysql 3.23.58 and I want to do a query with joins from two tables and then insert the results into the column of a third. This appears to be harder than I realised with this version of mysql and I am banging my head against a wall. Please Help! ok first query. - select link_ID,of_group FROM page_elements LEFT JOIN pages ON page_elements.link_to=pages.page_ID LEFT JOIN links_DB_bk ON page_elements.link_ID=links_DB_bk.link_ID WHERE content_type='text' - Now I want to update table 3 (links_DB) using page_elements.link_ID=links_DB.link_ID usual update query doesn't seem to work in this circumstance .. any ideas? Start from here: http://dev.mysql.com/doc/mysql/en/update.html Updates *are* allowed to use JOINED tables as the thing to be updated. Which means that an UPDATE statement can look VERY MUCH like a SELECT statement turned on it's head. In your case, I think you are trying to figure out how to flip this: select link_ID,of_group FROM page_elements LEFT JOIN pages ON page_elements.link_to=pages.page_ID LEFT JOIN links_DB_bk ON page_elements.link_ID=links_DB_bk.link_ID WHERE content_type='text' into this (while adding the `links_db` table into the mix: UPDATE links_db INNER JOIN page_elements ON page_elements.link_ID=links_DB.link_ID LEFT JOIN pages ON page_elements.link_to=pages.page_ID LEFT JOIN links_DB_bk ON page_elements.link_ID=links_DB_bk.link_ID SET *** see note*** WHERE content_type='text'; *** note: your SET clause can reference ANY column from ANY table defined in your UPDATE clause. You are not limited to just changing one table at a time. Just make sure you properly identify the columns you want to get data from and which ones you want to set. Now, you didn't say exactly what you wanted to update with what or I would have filled in more of the SET clause. If you want to flip a SELECT ... GROUP BY statement into an UPDATE statement, you have to go through a temporary table first. That is because the GROUP BY eliminates any one-to-one row-to-value mappings 99.9% of the time. There is no UPDATE ... GROUP BY command for any RDBMS that I know of. However, if you save the results of the SELECT...GROUP BY statement, you can JOIN to that table in an UPDATE statement just like any other data. Let me know if you run into any more issues and I can help you work it out. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Replication, charset / collations Problem
Hi, I try to replicate an existing database to a new server and run into trouble with collations. Maybe somebody can help me. Existing DB-Server (Master): MySQL Version 4.1.12 ca. 100 GB Database size. New DB-Server (Slave): MySQL Version 4.1.12 Here is what I did to get the replication up and running: - Shutdown MySQL on the Master - Copy the MySQL datadir from the Master to the Slave (approx. 4 hours :-) - Startup the Master again and execute RESET MASTER to delete the existing binlogs. - Startup the Slave The Slave starts the replication, as I can see using SHOW PROCESSLIST on the Slave. Afer a while the following error occured listed in the .err-file. 050808 10:41:25 mysqld started 050808 10:41:27 InnoDB: Started; log sequence number 70 1628293808 /usr/sbin/mysqld: ready for connections. Version: '4.1.12-standard-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL) 050808 10:42:11 [Note] Slave SQL thread initialized, starting replication in log 'FIRST' at position 0, relay log './HAL2006-relay-bin.01' position: 4 050808 10:42:11 [Note] Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'FIRST' at position 4 050808 11:49:23 [ERROR] Slave: Error 'Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='' on query. Default database: 'report'. Query: 'UPDATE sum_day_key_requests SET sum = sum + 1 WHERE day = '2005-7-30' AND type = 'redirect' AND client = '7865' AND channel = '78' AND campaign = 'DE Conversion' AND grouping = 'Flirt- und Kontaktbörse' AND `key` = 'Flirt Kontakte' AND afftraf = 'NULL'', Error_code: 1267 050808 11:49:23 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log 'HAL2005-bin.03' position 27769229 The only idea, which came into my mind, is that the server use different default charsets or collations. I checked the configurations and can't find a difference. Is there a possibility to check the default charsets and collations on the running server ? thanks in advance Marco --- http://www.tuxoo.de http://www.kontaktlinsen-preisvergleich.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication, charset / collations Problem
The only idea, which came into my mind, is that the server use different default charsets or collations. I checked the configurations and can't find a difference. Is there a possibility to check the default charsets and collations on the running server ? If charsets/collations are not set explicitly in my.cnf, server could be using ones specified during compilation. To check charsets and collations on the running server do: show global variables like 'c%'; -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication, charset / collations Problem
Am Dienstag, den 09.08.2005, 17:57 +0400 schrieb Alexey Polyakov: If charsets/collations are not set explicitly in my.cnf, server could be using ones specified during compilation. To check charsets and collations on the running server do: show global variables like 'c%'; Thanks. I executed the statement on both servers and got identical results! So no diffenrence exists between the server configuration, right ? How can it be that I get the collation error ? Following the statement results: On the Master: -- HAL2005:~ # mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1803 to server version: 4.1.12-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show global variables like 'c%'; +--++ | Variable_name| Value | +--++ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_results| latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | | concurrent_insert| ON | | connect_timeout | 5 | +--++ 12 rows in set (0.00 sec) On the Slave: - HAL2006:/var/lib/mysql # mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 to server version: 4.1.12-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show global variables like 'c%'; +--++ | Variable_name| Value | +--++ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_results| latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | | concurrent_insert| ON | | connect_timeout | 5 | +--++ 12 rows in set (0.07 sec) Any ideas ?! Marco --- http://www.tuxoo.de http://www.kontaktlinsen-preisvergleich.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GRANTS for tables - why is create possible?
Hi there, What is the purpose of this GRANT statement? GRANT CREATE ON tablename TO [EMAIL PROTECTED]; eg: GRANT CREATE ON address TO [EMAIL PROTECTED]; It's possible, but what is it supposed to do? I can understand this grant on a global (server) and database level, but on a table level? With regards, Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, Oracle MS SQL Server Upscene Productions http://www.upscene.com Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stored Procedure, Dates, and Between
I have a simple SP that is selecting rows based on a date range using
parameters. I've tried several permutations that all return 0 rows.
The select statement looks like this:
select * from 'mit_log'.'mitlog' where StartDateTime between '2005-04-01' and
'2005-04-07'
and returns over 300,000 rows. The SP looks like this:
-
DELIMITER $$
DROP PROCEDURE IF EXISTS `mit_logs`.`spPatientsLikeUsersByDate`$$
Create procedure `mit_logs`.`spUsingDateRange`(begDate datetime, endDate
datetime)
BEGIN
SELECT *
FROM mitlog
WHERE (mitlog.StartDateTime between @begDate And @endDate);
END$$
-
and compiles ok. The calling statement looks like this:
call spPatientsLikeUsersByDate ('2005-04-01','2005-04-07')
Like I said, I've tried several guesses at syntax with no luck. When I take out
the parameters and hard code the dates, it works.
Any ideas?
Thanks in advance,
Kent in Montana
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication, charset / collations Problem
Hello. Please, send the output of the following statement executed both on master and slave: show variables like '%char%'; Send the definition of your table as well: SHOW CREATE TABLE sum_day_key_requests; Marco P$hler [EMAIL PROTECTED] wrote: Hi, I try to replicate an existing database to a new server and run into trouble with collations. Maybe somebody can help me. Existing DB-Server (Master): MySQL Version 4.1.12 ca. 100 GB Database size. New DB-Server (Slave): MySQL Version 4.1.12 Here is what I did to get the replication up and running: - Shutdown MySQL on the Master - Copy the MySQL datadir from the Master to the Slave (approx. 4 hours :-) - Startup the Master again and execute RESET MASTER to delete the existing binlogs. - Startup the Slave The Slave starts the replication, as I can see using SHOW PROCESSLIST on the Slave. Afer a while the following error occured listed in the .err-file. 050808 10:41:25 mysqld started 050808 10:41:27 InnoDB: Started; log sequence number 70 1628293808 /usr/sbin/mysqld: ready for connections. Version: '4.1.12-standard-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Edition - Standard (GPL) 050808 10:42:11 [Note] Slave SQL thread initialized, starting replication in log 'FIRST' at position 0, relay log './HAL2006-relay-bin.01' position: 4 050808 10:42:11 [Note] Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'FIRST' at position 4 050808 11:49:23 [ERROR] Slave: Error 'Illegal mix of collations (latin1_bin,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='' on query. Default database: 'report'. Query: 'UPDATE sum_day_key_requests SET sum = sum + 1 WHERE day = '2005-7-30' AND type = 'redirect' AND client = '7865' AND channel = '78' AND campaign = 'DE Conversion' AND grouping = 'Flirt- und Kontaktb$rse' AND `key` = 'Flirt Kontakte' AND afftraf = 'NULL'', Error_code: 1267 050808 11:49:23 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log 'HAL2005-bin.03' position 27769229 The only idea, which came into my mind, is that the server use different default charsets or collations. I checked the configurations and can't find a difference. Is there a possibility to check the default charsets and collations on the running server ? thanks in advance Marco --- http://www.tuxoo.de http://www.kontaktlinsen-preisvergleich.de -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [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: Stored Procedure, Dates, and Between
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Kent Roberts wrote: I have a simple SP that is selecting rows based on a date range using parameters. I've tried several permutations that all return 0 rows. The select statement looks like this: select * from 'mit_log'.'mitlog' where StartDateTime between '2005-04-01' and '2005-04-07' and returns over 300,000 rows. The SP looks like this: - DELIMITER $$ DROP PROCEDURE IF EXISTS `mit_logs`.`spPatientsLikeUsersByDate`$$ Create procedure `mit_logs`.`spUsingDateRange`(begDate datetime, endDate datetime) BEGIN SELECT * FROM mitlog WHERE (mitlog.StartDateTime between @begDate And @endDate); Kent, If you prefix things with @, they are session variables. You want something like the following, I believe: Create procedure `mit_logs`.`spUsingDateRange`(begDate datetime, endDate datetime) BEGIN SELECT * FROM mitlog WHERE (mitlog.StartDateTime between begDate And endDate); -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Connectivity www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFC+MHjtvXNTca6JD8RAvcSAKCayRHpQZBCbxkfKYmQb+f5RmFsXQCfec/l AupP4lU40BKSNF49w9DJto0= =SJIl -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored Procedure, Dates, and Between
Kent Roberts [EMAIL PROTECTED] wrote on 08/09/2005 10:42:24 AM:
I have a simple SP that is selecting rows based on a date range
using parameters. I've tried several permutations that all return 0
rows.
The select statement looks like this:
select * from 'mit_log'.'mitlog' where StartDateTime between
'2005-04-01' and '2005-04-07'
and returns over 300,000 rows. The SP looks like this:
-
DELIMITER $$
DROP PROCEDURE IF EXISTS `mit_logs`.`spPatientsLikeUsersByDate`$$
Create procedure `mit_logs`.`spUsingDateRange`(begDate datetime,
endDate datetime)
BEGIN
SELECT *
FROM mitlog
WHERE (mitlog.StartDateTime between @begDate And @endDate);
END$$
-
and compiles ok. The calling statement looks like this:
call spPatientsLikeUsersByDate ('2005-04-01','2005-04-07')
Like I said, I've tried several guesses at syntax with no luck. When
I take out the parameters and hard code the dates, it works.
Any ideas?
Thanks in advance,
Kent in Montana
Could it be that you are calling one procedure (spPatientsLikeUsersByDate)
but making all of your changes in another (spUsingDateRange)? What if you
tried calling `spUsingDateRange` instead?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Re: Stored Procedure, Dates, and Between
That's it. Thanks Mark. I think I was confusinged by MS SQL Server syntax which prefixes both session variables and parameters with @. And you're right Scott, I changed the name of the SP for posting simplicity and forgot to change the colling statement to match. Thanks a lot both of you for getting back to quickly! Mark Matthews 08/09/05 08:46AM -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Kent Roberts wrote: I have a simple SP that is selecting rows based on a date range using parameters. I've tried several permutations that all return 0 rows. The select statement looks like this: select * from 'mit_log'.'mitlog' where StartDateTime between '2005-04-01' and '2005-04-07' and returns over 300,000 rows. The SP looks like this: - DELIMITER $$ DROP PROCEDURE IF EXISTS `mit_logs`.`spPatientsLikeUsersByDate`$$ Create procedure `mit_logs`.`spUsingDateRange`(begDate datetime, endDate datetime) BEGIN SELECT * FROM mitlog WHERE (mitlog.StartDateTime between @begDate And @endDate); Kent, If you prefix things with @, they are session variables. You want something like the following, I believe: Create procedure `mit_logs`.`spUsingDateRange`(begDate datetime, endDate datetime) BEGIN SELECT * FROM mitlog WHERE (mitlog.StartDateTime between begDate And endDate); -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Connectivity www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFC+MHjtvXNTca6JD8RAvcSAKCayRHpQZBCbxkfKYmQb+f5RmFsXQCfec/l AupP4lU40BKSNF49w9DJto0= =SJIl -END PGP SIGNATURE-
Re: Stored Procedure, Dates, and Between
Sorry, I'll try and proof reed more karefullly in the futchure. :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored Procedure, Dates, and Between
Now that I think about it, if MySql forced declaration of session variables it would avoid some nasty bugs in SPs. Mark Matthews 08/09/05 08:46AM -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Kent Roberts wrote: I have a simple SP that is selecting rows based on a date range using parameters. I've tried several permutations that all return 0 rows. The select statement looks like this: select * from 'mit_log'.'mitlog' where StartDateTime between '2005-04-01' and '2005-04-07' and returns over 300,000 rows. The SP looks like this: - DELIMITER $$ DROP PROCEDURE IF EXISTS `mit_logs`.`spPatientsLikeUsersByDate`$$ Create procedure `mit_logs`.`spUsingDateRange`(begDate datetime, endDate datetime) BEGIN SELECT * FROM mitlog WHERE (mitlog.StartDateTime between @begDate And @endDate); Kent, If you prefix things with @, they are session variables. You want something like the following, I believe: Create procedure `mit_logs`.`spUsingDateRange`(begDate datetime, endDate datetime) BEGIN SELECT * FROM mitlog WHERE (mitlog.StartDateTime between begDate And endDate); -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Connectivity www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFC+MHjtvXNTca6JD8RAvcSAKCayRHpQZBCbxkfKYmQb+f5RmFsXQCfec/l AupP4lU40BKSNF49w9DJto0= =SJIl -END PGP SIGNATURE-
Calling Stored Procedures with MS Access Pass-Through Queries
Has anyone had experience with this yet? My attempts so far render ODBC--call failed. My connection is good and I can use select statements successfully, but no luck with calling an SP yet. I really want to call an SP with parameters, but I'm trying a simple one first. Also, anyone know of a forum dedicated to using Access as a front-end to MySql? Thanks in advance...again. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Calling Stored Procedures with MS Access Pass-Through Queries
It might help if I mention my syntax is: call spTestingStuff(); From: Kentnbsp;RobertsDate: August 9 2005 6:24pm Subject: Calling Stored Procedures with MS Access Pass-Through Queries Has anyone had experience with this yet? My attempts so far render = ODBC--call failed. My connection is good and I can use select statements = successfully, but no luck with calling an SP yet. I really want to call an = SP with parameters, but I'm trying a simple one first. Also, anyone know = of a forum dedicated to using Access as a front-end to MySql? Thanks in advance...again. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help table's locked/missing?
Hi Gleb, This is what I have using SHOW PROCESSLIST: Waiting for tableSHOW TABLE STATUS FROM `db_mambo` LIKE 'mos_TFS_visits' Help me pls, is there any way I can save this database? Looks like the troubled table is mos_TFS_visits. Thanks, Fakar On Monday 08 August 2005 03:59 pm, Gleb Paharenko wrote: Hello. Use SHOW PROCESSLIST to see what's going on in your database. Fajar Priyanto [EMAIL PROTECTED] wrote: Hi all, I'm hosting my web on a provider with MySQL 4.0.25-standard. I've got this situation when I open my database using phpMyadmin, one of my table has status is in use. And then when I try to repair the database, MySQL just hung there while consumed around 50% of CPU. And then when I tried to export the database, phpMyadmin got stuck at that tables, with a status of SHOW TABLE STATUS FROM 'thetroubledtable'. I tried to contact the hosting provider, but it seems they're having weekend break. Is there any way that I or my hosting provider can do about it? Maybe restarting the mysql server is enough? Or can the table just being deleted? Thanks, -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- Fajar Priyanto | Reg'd Linux User #327841 | http://linux2.arinet.org 23:51:20 up 25 min, Mandrakelinux release 10.2 (Limited Edition 2005) for i586 public key: https://www.arinet.org/fajar-pub.key -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calling Stored Procedures with MS Access Pass-Through Queries
Kent Roberts [EMAIL PROTECTED] wrote on 08/09/2005 12:24:11 PM: Has anyone had experience with this yet? My attempts so far render ODBC--call failed. My connection is good and I can use select statements successfully, but no luck with calling an SP yet. I really want to call an SP with parameters, but I'm trying a simple one first. Also, anyone know of a forum dedicated to using Access as a front-end to MySql? Thanks in advance...again. Your options depend on if you are using OLEDB, ADO, or DAO to communicate with the ODBC driver. Refer to the appropriate library documentation to figure out how to get a recordset from a command. If you are using ADO, I would look closely at these objects.methods: Connection.Execute Command.Execute (along with the Parameter object) Recordset.Open But you say you are in MS Access, so I guess you are using DAO to control the ODBC connection? I am too rusty with DAO to suggest anything there. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Calling Stored Procedures with MS Access Pass-Through Queries
Thanks Shawn. I'm actually trying using Access's Pass-Through Query technology which utilizes ODBC. I'm not sure if it's ADO or DAO. I'll guess ADO since that's the default for this version of Access (2002). I haven't tried connecting in code yet, but that's coming soon. I've had a lot of experience with Access and MS SQL Server, but I'm a MySql newbie. Do you have a favorite desktop front-end to MySql other than Access? I really appreciate your help and suggestions. [EMAIL PROTECTED] 08/09/05 10:48AM Kent Roberts [EMAIL PROTECTED] wrote on 08/09/2005 12:24:11 PM: Has anyone had experience with this yet? My attempts so far render ODBC--call failed. My connection is good and I can use select statements successfully, but no luck with calling an SP yet. I really want to call an SP with parameters, but I'm trying a simple one first. Also, anyone know of a forum dedicated to using Access as a front-end to MySql? Thanks in advance...again. Your options depend on if you are using OLEDB, ADO, or DAO to communicate with the ODBC driver. Refer to the appropriate library documentation to figure out how to get a recordset from a command. If you are using ADO, I would look closely at these objects.methods: Connection.Execute Command.Execute (along with the Parameter object) Recordset.Open But you say you are in MS Access, so I guess you are using DAO to control the ODBC connection? I am too rusty with DAO to suggest anything there. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: JOIN QUERY - UPDATE ... help?!
Brendan Gogarty [EMAIL PROTECTED] wrote on 08/09/2005 05:30:51 AM: Hi, We are running mysql 3.23.58 and I want to do a query with joins from two tables and then insert the results into the column of a third. This appears to be harder than I realised with this version of mysql and I am banging my head against a wall. Please Help! ok first query. [snip] any ideas? Start from here: http://dev.mysql.com/doc/mysql/en/update.html Updates *are* allowed to use JOINED tables as the thing to be updated. Which means that an UPDATE statement can look VERY MUCH like a SELECT statement turned on it's head. In your case, I think you are trying to figure out how to flip this: select link_ID,of_group FROM page_elements LEFT JOIN pages ON page_elements.link_to=pages.page_ID LEFT JOIN links_DB_bk ON page_elements.link_ID=links_DB_bk.link_ID WHERE content_type='text' into this (while adding the `links_db` table into the mix: UPDATE links_db INNER JOIN page_elements ON page_elements.link_ID=links_DB.link_ID LEFT JOIN pages ON page_elements.link_to=pages.page_ID LEFT JOIN links_DB_bk ON page_elements.link_ID=links_DB_bk.link_ID SET *** see note*** WHERE content_type='text'; *** note: your SET clause can reference ANY column from ANY table defined in your UPDATE clause. You are not limited to just changing one table at a time. Just make sure you properly identify the columns you want to get data from and which ones you want to set. Now, you didn't say exactly what you wanted to update with what or I would have filled in more of the SET clause. If you want to flip a SELECT ... GROUP BY statement into an UPDATE statement, you have to go through a temporary table first. That is because the GROUP BY eliminates any one-to-one row-to-value mappings 99.9% of the time. There is no UPDATE ... GROUP BY command for any RDBMS that I know of. However, if you save the results of the SELECT...GROUP BY statement, you can JOIN to that table in an UPDATE statement just like any other data. Let me know if you run into any more issues and I can help you work it out. Hi Shaun, I'm afraid after a few hours of testing various things it doesn't work. I am pretty sure its a version issue as even the simplest query such as UPDATE links_DB LEFT JOIN page_elements SET links_DB.in_group=0 brings up an error ' MySQL said: You have an error in your SQL syntax near 'LEFT JOIN page_elements SET links_DB.in_group = 0' at line 1 ' cheers, brendan.
Re: Transactions in Java - JDBC
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 C.F. Scheidecker Antunes wrote: Hello, Can anyone tell me what to do in order to use transactions on a java application? Is there any howto regarding this issu? Thanks, C.F. C.F. First, make sure you're using the InnoDB storage engine (which supports transactions): http://dev.mysql.com/doc/mysql/en/using-innodb-tables.html Then use Connection.setAutoCommit(false) before starting your transaction, and Connection.commit()/Connection.rollback() to commit or rollback transactions: http://java.sun.com/docs/books/tutorial/jdbc/basics/transactions.html -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Connectivity www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFC+OdZtvXNTca6JD8RApjfAJ4q5K0N/Tnn5hpQYzJapO8AoDZEFQCfXsE7 laCWxC37BdRNqC3E6qenBzw= =Rab0 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Disasterous database corruption
This is what I think hit me too, Daniel. Unfortunately, it's not on my own server (a hosting provider), so my access to the shell is very limited. My website is still running, but, I can't export the database using phpMyadmin anymore, because the troubled table is on the first list of tables. Do you have any idea on how to save my other tables? Thanks. Fajar On Tuesday 09 August 2005 08:16 am, Daniel Kasak wrote: Hi all. I've been testing out mysql-5.0.10 on my Powerbook ( Gentoo Linux PPC ), and I've hit an incredibly unfortunate bug. It is demonstrated adequately with the following: mysql use entropy; Database changed mysql show tables; ERROR 1052 (23000): Column 'TABLE_NAME' in order clause is ambiguous mysql quit Bye [EMAIL PROTECTED] ~ $ mysqldump -u root --opt --all-databases full_dump.sql -p Enter password: mysqldump: mysqldump: Couldn't execute 'SHOW DATABASES': Column 'SCHEMA_NAME' in field list is ambiguous (1052) [EMAIL PROTECTED] ~ $ I can't get anything out of the DB with any GUI tools. I assume they all want to inspect the tables and bail out when they hit something like the above. The data is still there, and I can select from tables that I already know the name of ( which, luckily, I do ... for the important stuff ). So I suppose I don't *really* need any help in backing stuff up - I can back up table by table to text files and then re-import. Is anyone interested in examining what went wrong? ie should I create a bug report? I suppose I'd have to upload a zipped copy of my /var/lib/mysql folder or something, since mysql isn't too keen on giving up any data voluntarily. 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 -- Fajar Priyanto | Reg'd Linux User #327841 | http://linux2.arinet.org 00:25:04 up 59 min, Mandrakelinux release 10.2 (Limited Edition 2005) for i586 public key: https://www.arinet.org/fajar-pub.key -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOIN QUERY - UPDATE ... help?!
Brendan Gogarty wrote: We are running mysql 3.23.58 and I want to do a query with joins from two tables and then insert the results into the column of a third. snip Shawn Green wrote: Start from here: http://dev.mysql.com/doc/mysql/en/update.html Updates *are* allowed to use JOINED tables as the thing to be updated. Which means that an UPDATE statement can look VERY MUCH like a SELECT statement turned on it's head. In your case, I think you are trying to figure out how to flip this: snip Brendan Gogarty wrote: Hi Shaun, I'm afraid after a few hours of testing various things it doesn't work. I am pretty sure its a version issue as even the simplest query such as UPDATE links_DB LEFT JOIN page_elements SET links_DB.in_group=0 brings up an error MySQL said: You have an error in your SQL syntax near 'LEFT JOIN page_elements SET links_DB.in_group = 0' at line 1 cheers, brendan. From the manual page Shawn cites: Starting with MySQL 4.0.4, you can also perform UPDATE operations that cover multiple tables. So, yes, it's a version issue. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: JOIN QUERY - UPDATE ... help?!
Multi Table UPDATES are first supported in 4.0.x -Original Message- From: Brendan Gogarty [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 09, 2005 12:16 PM To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Subject: RE: JOIN QUERY - UPDATE ... help?! Brendan Gogarty [EMAIL PROTECTED] wrote on 08/09/2005 05:30:51 AM: Hi, We are running mysql 3.23.58 and I want to do a query with joins from two tables and then insert the results into the column of a third. This appears to be harder than I realised with this version of mysql and I am banging my head against a wall. Please Help! ok first query. [snip] any ideas? Start from here: http://dev.mysql.com/doc/mysql/en/update.html Updates *are* allowed to use JOINED tables as the thing to be updated. Which means that an UPDATE statement can look VERY MUCH like a SELECT statement turned on it's head. In your case, I think you are trying to figure out how to flip this: select link_ID,of_group FROM page_elements LEFT JOIN pages ON page_elements.link_to=pages.page_ID LEFT JOIN links_DB_bk ON page_elements.link_ID=links_DB_bk.link_ID WHERE content_type='text' into this (while adding the `links_db` table into the mix: UPDATE links_db INNER JOIN page_elements ON page_elements.link_ID=links_DB.link_ID LEFT JOIN pages ON page_elements.link_to=pages.page_ID LEFT JOIN links_DB_bk ON page_elements.link_ID=links_DB_bk.link_ID SET *** see note*** WHERE content_type='text'; *** note: your SET clause can reference ANY column from ANY table defined in your UPDATE clause. You are not limited to just changing one table at a time. Just make sure you properly identify the columns you want to get data from and which ones you want to set. Now, you didn't say exactly what you wanted to update with what or I would have filled in more of the SET clause. If you want to flip a SELECT ... GROUP BY statement into an UPDATE statement, you have to go through a temporary table first. That is because the GROUP BY eliminates any one-to-one row-to-value mappings 99.9% of the time. There is no UPDATE ... GROUP BY command for any RDBMS that I know of. However, if you save the results of the SELECT...GROUP BY statement, you can JOIN to that table in an UPDATE statement just like any other data. Let me know if you run into any more issues and I can help you work it out. Hi Shaun, I'm afraid after a few hours of testing various things it doesn't work. I am pretty sure its a version issue as even the simplest query such as UPDATE links_DB LEFT JOIN page_elements SET links_DB.in_group=0 brings up an error ' MySQL said: You have an error in your SQL syntax near 'LEFT JOIN page_elements SET links_DB.in_group = 0' at line 1 ' cheers, brendan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Import data (BLOB) from txtt file to mysql
All, Could you tell me how to import data (file.txt)with BLOB column to mysql? Thank you Nguyen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Import data (BLOB) from txtt file to mysql
Nguyen, Phong [EMAIL PROTECTED] wrote on 08/09/2005 01:52:58 PM: All, Could you tell me how to import data (file.txt)with BLOB column to mysql? Thank you Nguyen The short answer is: you escape the content of the file and make one big INSERT statement out of it. The escaped content becomes the data value you are inserting to your BLOB field. The long answer depends completely on *HOW* you are reading the file and connecting with MySQL. Describe for us the technique you would like to use and we can help you to refine it to make it work. Don't forget to include your which operating system you are on and what version MySQL server you are using. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Replication, charset / collations Problem
Please, send the output of the following statement executed both on master and slave: show variables like '%char%'; mysql show global variables like 'c%'; +--++ | Variable_name| Value | +--++ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_results| latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | | concurrent_insert| ON | | connect_timeout | 5 | +--++ 12 rows in set (0.00 sec) The output is identical on both servers. Send the definition of your table as well: SHOW CREATE TABLE sum_day_key_requests; mysql SHOW CREATE TABLE sum_day_key_requests \G *** 1. row *** Table: sum_day_key_requests Create Table: CREATE TABLE `sum_day_key_requests` ( `day` date NOT NULL default '-00-00', `type` varchar(50) character set latin1 collate latin1_bin NOT NULL default '', `client` varchar(16) character set latin1 collate latin1_bin NOT NULL default '', `channel` varchar(16) character set latin1 collate latin1_bin NOT NULL default '', `campaign` varchar(100) character set latin1 collate latin1_bin NOT NULL default '', `grouping` varchar(64) character set latin1 collate latin1_bin NOT NULL default '', `key` varchar(255) character set utf8 collate utf8_bin NOT NULL default '', `afftraf` varchar(5) character set latin1 collate latin1_bin NOT NULL default '', `sum` int(10) unsigned NOT NULL default '1', PRIMARY KEY (`day`,`type`,`client`,`channel`,`campaign`,`grouping`,`key`,`afftraf`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (1.05 sec) The create statements are identical on both servers. I have no more ideas. Should I try to reproduce the error with a smaller example ? Marco --- http://www.tuxoo.org http://www.kontaktlinsen-preisvergleich.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
why does this query run slowly at times
Hi All, We have a query that sometimes takes 10-15 seconds to run, atleast thats what mytop or the slow log say, usually it runs in less than 0.04 seconds SELECT title, template FROM template WHERE templateid IN (608,2072,2073,1888,1898,1895,1897,1894,1896,1901,1903,1889,1900,1890,1891,1902,1904,2104,648,625,2152,617,1985,1996,646,1964,2103,2106,2113,1982,1983,1984,2074,623,2117,2124,2123,2122,2119,2121); templateid is the primary key, also there are only 500 rows in the table template, (mysql version 4.0.17 on Redhat 7.3 ) whats making the query to run slowly, the server only runs at less than 10% cpu utilization, has lots of ram and currently averages 120 qps ( 10M/day) , and is very fast 99% of the time except for this query... also the temp_table_size is set to 128MB, inspite of this the ratio for Created_tmp_disk_tables/Created_tmp_tables is close to 0.1, could this be a reason, why is Mysql creating soo many temp tables on the disk Thanks a lot and any help would be appreciated .. Kishore Jalleda -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why does this query run slowly at times
Kishore Jalleda wrote: Try two things. First: EXPLAIN SELECT title, template FROM template WHERE templateid IN (608,2072,2073,1888,1898,1895,1897,1894,1896,1901,1903,1889,1900,1890,1891,1902,1904,2104,648,625,2152,617,1985,1996,646,1964,2103,2106,2113,1982,1983,1984,2074,623,2117,2124,2123,2122,2119,2121); Second, when its running slow, do a SHOW FULL PROCESSLIST I suspect another, longer running query is running at the same time and has the table locked. Hi All, We have a query that sometimes takes 10-15 seconds to run, atleast thats what mytop or the slow log say, usually it runs in less than 0.04 seconds SELECT title, template FROM template WHERE templateid IN (608,2072,2073,1888,1898,1895,1897,1894,1896,1901,1903,1889,1900,1890,1891,1902,1904,2104,648,625,2152,617,1985,1996,646,1964,2103,2106,2113,1982,1983,1984,2074,623,2117,2124,2123,2122,2119,2121); templateid is the primary key, also there are only 500 rows in the table template, (mysql version 4.0.17 on Redhat 7.3 ) whats making the query to run slowly, the server only runs at less than 10% cpu utilization, has lots of ram and currently averages 120 qps ( 10M/day) , and is very fast 99% of the time except for this query... also the temp_table_size is set to 128MB, inspite of this the ratio for Created_tmp_disk_tables/Created_tmp_tables is close to 0.1, could this be a reason, why is Mysql creating soo many temp tables on the disk Thanks a lot and any help would be appreciated .. Kishore Jalleda -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why does this query run slowly at times
Explain select gives me this table type possible_keys key key_len refrows extra template range PRIMARY PRIMARY4 NULL 55 using where surpisingly it never runs slow when i run the query manually, also when this query runs for long, mytop or show full process list shows that other queries are running just fine, infact in the time that this query is running many similar queries are excecuted very fast Thanks Kishore Jalleda On 8/9/05, Mike Wexler [EMAIL PROTECTED] wrote: Kishore Jalleda wrote: Try two things. First: EXPLAIN SELECT title, template FROM template WHERE templateid IN (608,2072,2073,1888,1898,1895,1897,1894,1896,1901,1903,1889,1900,1890,1891,1902,1904,2104,648,625,2152,617,1985,1996,646,1964,2103,2106,2113,1982,1983,1984,2074,623,2117,2124,2123,2122,2119,2121); Second, when its running slow, do a SHOW FULL PROCESSLIST I suspect another, longer running query is running at the same time and has the table locked. Hi All, We have a query that sometimes takes 10-15 seconds to run, atleast thats what mytop or the slow log say, usually it runs in less than 0.04 seconds SELECT title, template FROM template WHERE templateid IN (608,2072,2073,1888,1898,1895,1897,1894,1896,1901,1903,1889,1900,1890,1891,1902,1904,2104,648,625,2152,617,1985,1996,646,1964,2103,2106,2113,1982,1983,1984,2074,623,2117,2124,2123,2122,2119,2121); templateid is the primary key, also there are only 500 rows in the table template, (mysql version 4.0.17 on Redhat 7.3 ) whats making the query to run slowly, the server only runs at less than 10% cpu utilization, has lots of ram and currently averages 120 qps ( 10M/day) , and is very fast 99% of the time except for this query... also the temp_table_size is set to 128MB, inspite of this the ratio for Created_tmp_disk_tables/Created_tmp_tables is close to 0.1, could this be a reason, why is Mysql creating soo many temp tables on the disk Thanks a lot and any help would be appreciated .. Kishore Jalleda -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Heeelp
Hi i have this error on IBM RS6000 with AIX 4.3.3 # scripts/mysql_install_db --user=mysql exec(): 0509-036 Cannot load program ./bin/my_print_defaults because of the following errors: 0509-023 Symbol stpcpy in my_print_defaults is not defined. 0509-023 Symbol isinf in my_print_defaults is not defined. 0509-026 System error: Cannot run a file that does not have a valid format. Neither host 'callcenter' nor 'localhost' could be looked up with ./bin/resolveip Please configure the 'hostname' command to return a correct hostname. If you want to solve this at a later stage, restart this script with the --force option Help me please Jorge Anibal Zapata Agreda Telf: +591-22123212 Entel S.A. La Paz - Bolivia -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: why does this query run slowly at times
Kishore Jalleda wrote: Explain select gives me this table type possible_keys key key_len refrows extra template range PRIMARY PRIMARY4 NULL 55 using where surpisingly it never runs slow when i run the query manually, also when this query runs for long, mytop or show full process list shows that other queries are running just fine, infact in the time that this query is running many similar queries are excecuted very fast What you want to do is when its running slow, see what the value is for the State field in show processlist for this query. I suspect it will show LOCKED which means its waiting for something else to complete. There will probably be another query that crosses multiple tables (including this one) and is running. The running query is probably the one causing the problem, but you need to catch it in the act. Thanks Kishore Jalleda On 8/9/05, Mike Wexler [EMAIL PROTECTED] wrote: Kishore Jalleda wrote: Try two things. First: EXPLAIN SELECT title, template FROM template WHERE templateid IN (608,2072,2073,1888,1898,1895,1897,1894,1896,1901,1903,1889,1900,1890,1891,1902,1904,2104,648,625,2152,617,1985,1996,646,1964,2103,2106,2113,1982,1983,1984,2074,623,2117,2124,2123,2122,2119,2121); Second, when its running slow, do a SHOW FULL PROCESSLIST I suspect another, longer running query is running at the same time and has the table locked. Hi All, We have a query that sometimes takes 10-15 seconds to run, atleast thats what mytop or the slow log say, usually it runs in less than 0.04 seconds SELECT title, template FROM template WHERE templateid IN (608,2072,2073,1888,1898,1895,1897,1894,1896,1901,1903,1889,1900,1890,1891,1902,1904,2104,648,625,2152,617,1985,1996,646,1964,2103,2106,2113,1982,1983,1984,2074,623,2117,2124,2123,2122,2119,2121); templateid is the primary key, also there are only 500 rows in the table template, (mysql version 4.0.17 on Redhat 7.3 ) whats making the query to run slowly, the server only runs at less than 10% cpu utilization, has lots of ram and currently averages 120 qps ( 10M/day) , and is very fast 99% of the time except for this query... also the temp_table_size is set to 128MB, inspite of this the ratio for Created_tmp_disk_tables/Created_tmp_tables is close to 0.1, could this be a reason, why is Mysql creating soo many temp tables on the disk Thanks a lot and any help would be appreciated .. Kishore Jalleda
World-wide Stop Order on PERL,Python,Java: Use Ruby instead
World-wide Stop Order on PERL,Python,Java: Use Ruby instead Language is the most critical interface to SQL server like MySQL and Postgresql. Carefully select the language for interfacing to MySQL!! Ruby will be storming the IT world and is targetting on Perl, Python and Java. Ruby will overtake Python, Java and Perl as it takes the best of best languages! Ruby learnt from the serious mistakes made in Python, Java, Eiffel, Smalltalk, Lisp and many others Perl is an old language like C but all new developments must be in Ruby and not perl. Perl programmers can be easily migrated to Ruby, since Ruby's code base was originally borrowed from Perl. Vast amount of Perl code world-wide will slowly move into Ruby. Ruby is about 10 to 20% faster than Java in execution speed. Ruby runs slightly faster than Java. Ruby programs are about 50% less than Java. Java has lot of code clutter. Development time in Ruby is about 50% of Java, if it takes 2 months for Java project then takes about a 1 month in Ruby. Ruby had won the hearts of Python programmers because it is more flexible and more object oriented than Python. It is strongly recommended you see this presentation - Ruby Slides - A Presentation About Ruby at http://www.pragmaticprogrammer.com/talks/perlmongers/perlmongers.htm Ruby's popularity surpassed the Python in Japan in year 2000, and now it is 2005. Imagine how many programs might have been written in Japan in 5 years!! Ruby interfaces with all the SQL server databases via the Ruby::DBI interface (similar to perl::DBI). It can connect to MySQL, PostgreSQL and others. Ruby is at http://www.ruby-lang.org/en/ See also language shootouts at http://www.geocities.com/alavoor (Ruby ranks higher than Java and is picking up points) Al Dev alavoor[at]-no-spam(dot)yahoo(dot)com __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calling Stored Procedures with MS Access Pass-Through Queries
Kent Roberts wrote: Has anyone had experience with this yet? My attempts so far render ODBC--call failed. My connection is good and I can use select statements successfully, but no luck with calling an SP yet. I really want to call an SP with parameters, but I'm trying a simple one first. Also, anyone know of a forum dedicated to using Access as a front-end to MySql? Can't be done. MyODBC-3.51.x doens't support multiple result sets ... or something like that. I have been waiting on this too, for years. Apparently the new MyODBC ( version 5 ) will support this, but I don't know when, and it doesn't work with Access currently anyway. If you absolutely must used stored procedures, the only way I can see it happening at the moment is by passing the stored procedure a unique number or string, and having the stored procedure create a table and dump the results of the query into the table. Then you'd select from that table, and delete it when you're done. It's a far from perfect solution. What can I say? I wouldn't be using MySQL-5.0.x for anything other than testing anyway - it's eaten my data on more than one occasion. Stick with 4.0.x, which is the latest stable version to work out-of-the-box with the latest stable MyODBC. http://bugs.mysql.com/bug.php?id=2273 -- 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]
Create Index with DESC not working on v5
It could be a known issue, but thought it's a well known well used feature so, asking you all. I am trying to create an index with the following command - create index an_idx on atable (acol desc, bcol, ccol) / Then when I do a - show index from atable It shows for all indexed columns the 'Collation' is 'A'. And also when we do a query without any ORDER BY, the result is not sorted in descending order on acol. For example a query like select pk, acol, bcol, ccol from atable doesn't show the result in descending order of acol, instead it's in ascending order of acol. Is there a simple trick I am missing ? I am using MySQL version 5.0.3. Thanks in advance. People are conversing... without posting their email or filling up their mail box. ~~1123626929418~~ roomity.com http://roomity.com/launch.jsp No sign up to read or search this Rich Internet App -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Create Index with DESC not working on v5
asdas wrote: I am trying to create an index with the following command - create index an_idx on atable (acol desc, bcol, ccol) / select pk, acol, bcol, ccol from atable doesn't show the result in descending order of acol, instead it's in ascending order of acol. Is there a simple trick I am missing ? Note the last line of this excerpt from The Fine Manual :-) http://dev.mysql.com/doc/mysql/en/create-index.html An index_col_name specification can end with ASC or DESC. These keywords are allowed for future extensions for specifying ascending or descending index value storage. Currently they are parsed but ignored; index values are always stored in ascending order. HTH! -- Hassan Schroeder - [EMAIL PROTECTED] Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com dream. code. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Disasterous database corruption
Timothy Smith wrote: Hi, Daniel, First, here's a trick that *should* work for dumping out the data. Change directory to the MySQL data directory, and then use this little shell command: for d in mysql test; do (cd $d; tables=`/bin/ls -1 *.frm | sed -e s,\\.frm,,`; mysqldump $d $tables); done tables_dump.sql No such luck. A slightly modified script: for d in dreams entropy incidents mysql; do cd $d; tables=`ls -l *.frm | sed -e s,\\.frm,,`; mysqldump $d $tables -psome_password; cd ..; done tables_dump.sql gives: mysqldump: mysqldump: Couldn't execute 'SHOW TABLES LIKE '1'': Column 'TABLE_NAME' in field list is ambiguous (1052) mysqldump: mysqldump: Couldn't execute 'SHOW TABLES LIKE '1'': Column 'TABLE_NAME' in field list is ambiguous (1052) mysqldump: mysqldump: Couldn't execute 'SHOW TABLES LIKE '1'': Column 'TABLE_NAME' in field list is ambiguous (1052) mysqldump: mysqldump: Couldn't execute 'SHOW TABLES LIKE '1'': Column 'TABLE_NAME' in field list is ambiguous (1052) It seems that mysqldump wants to inspect the schema, which is borked. I have backups of everything vitally important, but there is some poor soul who has the same problem as me - he's responded to my post on the list. Maybe you should give him a hand. I can get data out from the mysql client if I know the table names already. Currently I haven't actually done this ... mainly because I have backups of everything important. If there is a way of reformatting the output of the mysql client into say ... the output of mysqldump, then that would be great. But as I said, I'm covered by backups, and I do realise the danger of beta software. I would be very interested in tracing this. I'd suggest that you do make a copy of your /var/lib/mysql folder, just for completeness. If you can create a bug report and attach that file, it would be perfect. Or, feel free to just send it to me and I'll check it out and file a report. If it's too large to send by e-mail, you could either FTP it to ftp://ftp.mysql.com/pub/mysql/upload/ (let me know the file name) I've entered a bug report at: http://bugs.mysql.com/bug.php?id=12475 I've ftp'd my /var/lib/mysql folder to the 'secret' directory on your ftp site. The filename is: issue_12475.tar.bz2 Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Disasterous database corruption
Fajar Priyanto wrote: This is what I think hit me too, Daniel. Unfortunately, it's not on my own server (a hosting provider), so my access to the shell is very limited. My website is still running, but, I can't export the database using phpMyadmin anymore, because the troubled table is on the first list of tables. Do you have any idea on how to save my other tables? Thanks. Fajar A hosting provider running 5.0.x? That's whacky. When you say my access to the shell is very limited, what does that mean exactly? Can you ssh in and run the 'mysql' command-line client? If you can, then do it, and you should be able to 'select * from table_name' for all the tables in your database(s). Note that 'show databases' and 'show tables' will not work, so you have to know these already :) You can use the 'tee' option to output stuff to a file. I've entered a bug report at: http://bugs.mysql.com/bug.php?id=12475 Good luck :) -- 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]
What is MySql equivalent to SQL Server's sp_fkeys?
I need a MySql solution equivalent to sp_fkeys procedure of Sql Server 2000. As shown in the attached file I managed to get most of the part working, but steel searching for DELETE_RULE and UPDATE_RULE equivalents to programmatically check CASCADEupdate or delete. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Disasterous database corruption
On Wednesday 10 August 2005 06:50 am, Daniel Kasak wrote: A hosting provider running 5.0.x? That's whacky. When you say my access to the shell is very limited, what does that mean exactly? Can you ssh in and run the 'mysql' command-line client? If you can, then do it, and you should be able to 'select * from table_name' for all the tables in your database(s). Note that 'show databases' and 'show tables' will not work, so you have to know these already :) You can use the 'tee' option to output stuff to a file. I've entered a bug report at: http://bugs.mysql.com/bug.php?id=12475 Good luck :) Well, it's not 5.0.x, it's 4.0.25-standard. I'm not sure how the error appears in the first place. What I notice when I open the database using phpMyadmin is that that particular troubled table status is in use. I'm allowed to ssh into the server, and hopefully I can save the table one by one as you suggested. I'll inform the result here. Thanks. -- Fajar Priyanto | Reg'd Linux User #327841 | http://linux2.arinet.org 10:03:25 up 35 min, Mandrakelinux release 10.2 (Limited Edition 2005) for i586 public key: https://www.arinet.org/fajar-pub.key -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upgrade from 4.018 to 4.1.13 ?
Hi, I have a silly questionDUH Im running OS X Server, I installed 4.018 and I want to run 4.1.13, how do I upgrade the server, without losing anything ? Do I just install over it ? Of course backing up all the tables and what not first ? Any pointers would be helpful. Thanks. M i l e s. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Disasterous database corruption
Fajar Priyanto wrote: Well, it's not 5.0.x, it's 4.0.25-standard. I'm not sure how the error appears in the first place. What I notice when I open the database using phpMyadmin is that that particular troubled table status is in use. I'm allowed to ssh into the server, and hopefully I can save the table one by one as you suggested. I'll inform the result here. Thanks. That actually sounds nothing like the problem I'm having. I'm not getting any 'in use' errors. Perhaps you have a different issue? -- 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]
Re: UTF8 support in MySQL 4.0
Marco wrote: So is there any solution on how I can properly use foreign characters and store them in a MySQL 4.0 database? You can store UTF-8 in any database in the world. UTF-8 is compatible with any application capable of dealing with null-terminated strings of 8-bit characters. That's why it's possible in the Unix/C world, which was designed with null-terminated strings of 8-bit characters in mind. What UTF-8 support gets you is the ability for the database server to do things like Unicode-aware collations and such. But the lack of this feature doesn't prevent you from simply _storing_ UTF-8. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Table and Data access only through procedures
Since there was no reply, I will try once more. I wish to limit table access and data manipulation strictly through procedures. Can MySQL 5 offer this? If a user connects to the db and executes UPDATE or DELETE it should not allow it, except by calling a procedure e.g. CALL DELETE_USER($user_id) Since my database is being called through Oracle, VB, ASP and PHP applications, it's becoming increasingly difficult to ensure data integrity (and auditing). Any tips would be great. Original Message Subject: Tabls access only through procedures Date: Thu, 14 Jul 2005 17:10:51 +0800 From: Terence [EMAIL PROTECTED] To: mysql@lists.mysql.com mysql@lists.mysql.com Hi List, I belive version 5 does not allow me to grant access to execute procedures, but deny updates to tables directly? I am attempting to ensure that all data modification is done through procedures. Any tips or ideas when this will be available or work-arounds? Thanks, Terence -- 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: UTF8 support in MySQL 4.0
Warren Young wrote: That's why it's possible in the Unix/C world, Typo: should be That's why it's _popular_... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
