mySQL in Hebrew
Does anyone have any experience of working with mySQL in Hebrew? The front end to my database is written in Delphi/Windows; it transmits Hebrew characters and displays Hebrew characters. The backend is in mySQL/Linux, and what was Hebrew appears in English characters. The only problem which I have with this is that almost every table has a 'name' index, and the names aren't sorted in the correct order. I had a quick glance at the documentation regarding internationalisation and didn't completely understand it, so I am asking for help. Would it be a good idea to build a translation table, ie 'aleph' = 'a' and comes first in the sorting order? What about numbers and English names? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How Do I Insert ...... ?
"where and how do I indicate that there is a problem insert a row into the table 'message'?" I don't understand you there. INSERT INTO message(thread_topic, thread_body) VALUES ('This is a topic','This is the body.'); That should work just fine. -Original Message- From: Caroline Jen [mailto:[EMAIL PROTECTED] Sent: Thursday, December 25, 2003 2:37 PM To: [EMAIL PROTECTED] Subject: How Do I Insert .. ? I have a table 'message' in the MySQL database. To make it simple; say, I have three fields in that table: thread_ID, thread_topic, and thread_body. The thread_ID field is AUTO_INCREMENT; therefore, I do not insert any value into that column. The value of that field starts with 1 when the first row is inserted. Now, I have the values for thread_topic and thread_body and want to insert those values into the 'message' table to create the first record in that table. 1. where and how do I indicate that there is a problem insert a row into the table 'message'? 2. Do I code this way: protected static void create(String threadTopic, String threadBody) throws CreateException, DatabaseException, ForeignKeyNotFoundException { StringBuffer sb = new StringBuffer(512); sb.append("INSERT INTO " + message + "(thread_topic, thread_body)"); sb.append(" VALUES (?, ?)"); try { Connection conn = DBConnection.getDBConnection(); Statement stmt = conn.createStatement(sb.toString()); stmt.setString(1, threadTopic); stmt.setString(2, threadBody); } catch(SQLException sqle) { sqle.printStackTrace(); throw new DatabaseException("Error executing SQL in ThreadHelper.create."); } finally { stmt.close(); conn.close(); } } __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How Do I Insert ...... ?
I have a table 'message' in the MySQL database. To make it simple; say, I have three fields in that table: thread_ID, thread_topic, and thread_body. The thread_ID field is AUTO_INCREMENT; therefore, I do not insert any value into that column. The value of that field starts with 1 when the first row is inserted. Now, I have the values for thread_topic and thread_body and want to insert those values into the 'message' table to create the first record in that table. 1. where and how do I indicate that there is a problem insert a row into the table 'message'? 2. Do I code this way: protected static void create(String threadTopic, String threadBody) throws CreateException, DatabaseException, ForeignKeyNotFoundException { StringBuffer sb = new StringBuffer(512); sb.append("INSERT INTO " + message + "(thread_topic, thread_body)"); sb.append(" VALUES (?, ?)"); try { Connection conn = DBConnection.getDBConnection(); Statement stmt = conn.createStatement(sb.toString()); stmt.setString(1, threadTopic); stmt.setString(2, threadBody); } catch(SQLException sqle) { sqle.printStackTrace(); throw new DatabaseException("Error executing SQL in ThreadHelper.create."); } finally { stmt.close(); conn.close(); } } __ Do you Yahoo!? New Yahoo! Photos - easier uploading and sharing. http://photos.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Selecting the latest entries
Hi there, This subject might be discussed before, but I couldn't find any mail from the archives. I have a table containing weather reports of different types and cities. The structure is following: - type - city - time - report Which kind of query should I use to select the latest reports of specified cities. I mean, if I had three different types of reports for Helsinki and Turku, how can I get them all in one query? One way is to make two queries: first to get the latest report time for each type/city... SELECT MAX(time) FROM reports WHERE city IN ('Helsinki', 'Turku') AND type IN ('Observation', 'Forecast 1', 'Forecast 2') GROUP BY type, city; ... and then another query getting the report text for each time and city and type. Anyway I find this not very good one and now wondering if there is any other way to make it in one query... Thanks for any help. :) Ville -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
creating a minimal mysqldump
I'm currently working on a little PHP-project, where I would like the possibillety of automatically upgrading the MySQL-tables, so that users don't have the need to drop the database, and then run the installscript again, from a mysqldump of the original database I'm running. What I was thinking, is a PHP-script that parses the running database, and a mysqldump --no-data of my database, and checks if it's something that doesn't match, and the run an ALTER TABLE of that. Instead of reinventing the wheel; I was wondering if anyone has a better solution to it Is mysqldump capable of if not exists field... alter table add column ? Any ideas? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql 5.0.0 error when creating a stored procedure
I've installed 5.0.0 (from 24dec04) on WinXP. I connect with the following string mysql --user=root when I try to create a stored procedure with the following command: create procedure xxx() select * from pet; I get the following error: ERROR 1146 (42S02): Table 'mysql.proc' doesn't exist What should I do? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Hot standby database question
> -Message d'origine- > De : Jim Richardson [mailto:[EMAIL PROTECTED] > Envoye : Thursday, December 25, 2003 10:18 AM > A : [EMAIL PROTECTED] > Objet : Hot standby database question > > > > I would like to set up two systems, a primary, and a backup, in > physically seperate locations. I want the backup to be synced with the > primary, and if the primary goes down, dns will resolve to the backup, > so it needs to be able to start processing transactions immediately. > > I am new to MySQL, and to SQL in general, I *think* I can do the first > part (stay in sync with the primary) by making the backup a slave, and > replicating the DB on a constant basis. But will the slave DB happily > start accepting transactions when stuff starts coming in from the > webserver? or do I have to do something to allow that in MySQL? > > As an aside, a recommendation on good beginner and intermediate level > MySQL books would be appreciated. I want to RTFM, but I want to make > sure it *is* the Fine manual :) The first reference is the mysql reference manual available online at www.mysql.com. I am reading "Mysql enterprise solutions" from Alexander Sasha Pachev (Wiley) and MySQL, from Paul Dubois (developper's library) and they're both good. > > Oh, and seasons greetings to all, yes I'm working on Christmas, it's a > startup, what can I say... > > -- > Jim Richardson http://www.eskimo.com/~warlock > Quantum mechanics: The dreams stuff are made of. > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Bug Report
Dear Sir/Madame, Sehr geehrte Damen und Herren, I got the following message using the mysql-Database with phpMyAdmin: Ich habe folgende Meldung beim benutzen der mysql-Datenbank mit phpmyadmin erhalten: Möglicherweise haben Sie einen Bug im SQL-Parser entgeckt. Bitte überprüfen Sie Ihre Abfrage genaustens insbesondere auf falsch gesetzte oder nicht geschlossene Auführungszeichen. Eine weite Ursache könnte darin liegen, dass Sie versuchen eine Datei mit binären Daten welche nicht von Auführungszeichen eingeschlossen sind hochzuladen. Sie können alternativ versuchen Ihre Abfrage über das MySQL-Kommandozeileninterface zu senden. Die MySQL-Fehlerausgabe, falls vorhanden, kann Ihnen auch bei der Fehleranalyse helfen. Falls Sie weiterhin Probleme haben sollten oder der Parser dort versagt wo die Kommandozeile erfolgreich ist, so reduzieren Sie bitte Ihre Abfrage auf den Befehl, welcher die Probleme verursacht, und senden Sie uns einen Bugreport mit den Datenausschnitt, den Sie weiter unten auf dieser Seite finden.: AUSSCHNITTSANFANG eNo9kM9Og0AYxO/7FHPocdkui62wiQfEtSXhT12wjacGAloSAkirrT4XD+jWGE/fJDOT+eVTWqda IrARCEQqkRAeHA7XI1lujImQYJtJzMJK4vjeDsV4rEfWNiUbDgP9hM2EgOBczLk757cQQjpLabsY +7IsTgLqMmBG4q/sKZJwmHDYQlht/0aeM400o/BXKskptsrM7ZoOocKScbKJfQPDnKtcb642TQ3I DRPMkDbdx4VEfrKSqGqrOfaW6y48yya/M5mKVJDjvD/344maW3TfxaHFo05jTOe+Hk/1OCHVD0rj /uU/QP8q2K2VVnjdN9XdAlEYhzk4NX/5AUJXU3c= AUSSCHNITTSENDE BEGINN DER AUSGABE ERROR: C1 C2 LEN: 29 30 89 STR: ´ CVS: $Id: sqlparser.lib.php,v 1.22 2002/08/07 22:36:18 robbat2 Exp $ MySQL: 3.23.52-log USR OS, AGENT, VER: Win IE 6.0 PMA: 2.3.0 PHP VER,OS: 4.2.2 Linux LANG: de-iso-8859-1 SQL: SELECT w_wort, w_anzahl FROM ´woerter´ ORDER BY w_anzahl, w_wort WHERE f_id=5 LIMIT 0, 30 ENDE DER AUSGABE Fehler SQL-Befehl : SELECT w_wort, w_anzahl FROM ´woerter´ ORDER BY w_anzahl, w_wort WHERE f_id=5 LIMIT 0, 30 MySQL meldet: You have an error in your SQL syntax near '´woerter´ ORDER BY w_anzahl, w_wort WHERE f_id=5 LIMIT 0, 30' at line 1 Zurück I hope to help you, debugging and developing the system. Ich hoffe Ihnen bei der Weiterentwicklung hilfreich gewesen zu sein. Sincerely yours Mit freundlichen Grüßen Dennis Lehnen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: relative performance between innodb and myisam
Bruce, InnoDB in most cases uses some more CPU time than MyISAM. But the main performance disadvantage of InnoDB is that tables in the InnoDB format typically require 50 % - 300 % more disk space than MyISAM format tables. Marko Mäkelä of Innobase Oy is writing a new InnoDB table format which will reduce space usage substantially, maybe 30 % - 80 %. The new InnoDB table format will probably be available in some MySQL-5.0.x release. We will probably offer some PACK_KEYS=1 option in CREATE TABLE, so that the user can control the tradeoff between using more CPU time to compress/uncompress data, or using more disk bandwidth. You could find this from the TODO at www.innodb.com, but unfortunately our web host has technical problems, and the website is not accessible at the moment. Merry Christmas! Heikki - Original Message - From: "Bruce Ferrell" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Thursday, December 25, 2003 12:05 PM Subject: relative performance between innodb and myisam > we have a bit of a debate going on at work. The gist of it is that > there is a a performance hit when using innodb tables vs myisam tables. > > I understand the for a given dataload innodb will be larger, but is > there a performance hit as well? If so, as a rough comparison, how much > of a hit is it? > > > > -- > 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: How to do case sensitive replace with wild card matching?
Chris W wrote: Michael Stassen wrote: mos wrote: Ok, put your thinking caps on because this one bit of a toughie. I an Update statement that will insert a '/' in front of the *first* lowercase letter of a field value. Example: "ABCDef" becomes "ABCD/ef". Of course it doesn't always end in "ef" and could be any 2 or 3 group of letters like "ABcde" becomes "AB/cde". Any idea on how to do this? Or should I do it manually? TIA Mike I assume you've already made sure that the column with the letters is wide enough to hold an additional character. Not sure what you mean by manually. I'd probably do it in perl, as it's almost a one-liner ($field =~ s/([a..z])/\$1/). In mysql, though, the problem seems to be that while we can match with a regular expression, we can't replace with one. Hmm... Being an official perl hater I don't know the exact syntax with out looking it up, but I am sure perl can do the search and replace in one line. Here is how you would do it if this column were lines of text in vi :%s/[a-z]*$/\/&/ this is basically saying find a group of any number of lower case letters at the end of the line and then replace it with a / and then the string that matched the search and do this on every line. I'm not sure why the $ needs to be there but it didn't work right with out it. Now if I were to guess at the perl equivalent of this it would look something like this. $fielsd =~ s/[a-z]*$/\/$1/; You are right that perl can do the search and replace in one line, but you'll need some more code to read and write the data, either from mysql or from a source file before importing into mysql. That's why I said "almost" a one-liner. You are also right that I got the perl line wrong. Considering how much perl I use, that's embarassing. Sigh. I have to admit I tested the sql I suggested but not the perl. I should have been more careful. Your regular expression isn't quite right either, though. The * means to match 0 or more, so [a-z]* will match the start of ANY string. That's why you needed the $ at the end, as it anchors the search to match at the end. Thus [a-z]*$ matches 0 or more lower case letters at the end. This is fine if there are any lower case letters in the string, but will put a / at the end of any string that doesn't contain a lower case letter. For example, it will turn "ABCD" into "ABCD/". It will also turn "ABcdEFg" into "ABcdEF/g". In other words, your version puts a / after the last non-lower case letter, instead of in front of the first lower case letter. To find the first lower case letter, we need to look for [a-z]. So the perl line should be $field =~ s/([a-z])/\/$1/ or, better yet $field =~ s|([a-z])|/$1| In vi, then, this would be :%s/[a-z]/\/&/ Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Php > mysql question please help
I just started working with mysql and I'm very novice Created a simple database and programmed php to connect as follows... $link= mysql_connect($hostname,$user,$password)or die ("_something is whong"); mysql_select_db($db,$link); $query ="SELECT * FROM projects"; $result = mysql_query($query); $array = mysql_fetch_array($result); $usercount = mysql_num_rows($result); mysql_close($link); ?> Question 1 I put the files created from mysql(MYI ,MYD,frm) in the correct directory at my site Is this correct or do they need some kind of compilling? Question 2 How do I Grant my host name and user name I'm comfused..It gives me an error message Can't connect because of host and user mistake.. Thanks Aaron
Hot standby database question
I would like to set up two systems, a primary, and a backup, in physically seperate locations. I want the backup to be synced with the primary, and if the primary goes down, dns will resolve to the backup, so it needs to be able to start processing transactions immediately. I am new to MySQL, and to SQL in general, I *think* I can do the first part (stay in sync with the primary) by making the backup a slave, and replicating the DB on a constant basis. But will the slave DB happily start accepting transactions when stuff starts coming in from the webserver? or do I have to do something to allow that in MySQL? As an aside, a recommendation on good beginner and intermediate level MySQL books would be appreciated. I want to RTFM, but I want to make sure it *is* the Fine manual :) Oh, and seasons greetings to all, yes I'm working on Christmas, it's a startup, what can I say... -- Jim Richardson http://www.eskimo.com/~warlock Quantum mechanics: The dreams stuff are made of. signature.asc Description: Digital signature
Re: How to do case sensitive replace with wild card matching?
Michael Stassen wrote: mos wrote: Ok, put your thinking caps on because this one bit of a toughie. I an Update statement that will insert a '/' in front of the *first* lowercase letter of a field value. Example: "ABCDef" becomes "ABCD/ef". Of course it doesn't always end in "ef" and could be any 2 or 3 group of letters like "ABcde" becomes "AB/cde". Any idea on how to do this? Or should I do it manually? TIA Mike I assume you've already made sure that the column with the letters is wide enough to hold an additional character. Not sure what you mean by manually. I'd probably do it in perl, as it's almost a one-liner ($field =~ s/([a..z])/\$1/). In mysql, though, the problem seems to be that while we can match with a regular expression, we can't replace with one. Hmm... Being an official perl hater I don't know the exact syntax with out looking it up, but I am sure perl can do the search and replace in one line. Here is how you would do it if this column were lines of text in vi :%s/[a-z]*$/\/&/ this is basically saying find a group of any number of lower case letters at the end of the line and then replace it with a / and then the string that matched the search and do this on every line. I'm not sure why the $ needs to be there but it didn't work right with out it. Now if I were to guess at the perl equivalent of this it would look something like this. $fielsd =~ s/[a-z]*$/\/$1/; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ANNOUNCE: moodss-17.14 and moomps-2.16
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 ### CHANGES ### - --- moodss 17.14 and moomps 2.16 --- - - upgraded myvars module for MySQL 4.0.17 support - - in moodss GUI: ~ - allow saving configuration (in a .moo file) in database history ~mode, so that building database based dashboards is possible ~ - in history (database browsing) mode, automatically disconnect from ~database when instances viewers is destroyed ~ - when a module is updated or periodically updating, no longer set ~the mouse cursor to a watch as that seems to eat X Window server ~memory (on Linux at least) ~ - in database history mode, in module instance table, displayed ~values were not limited in time by leftmost instant cursor ~ - made configuration and preferences dialog boxes tree look closer ~to standard ~ - uses newest BWidget 1.7.0 release - - in moomps daemon: ~ - disallow and report error when configuration file(s) contain(s) ~database instance modules - - in partitions module, number of kilobytes read or written was based ~ on incorrect values in /proc/partitions file (should be fine now ~ provided disk sector size is 512 bytes, as it seems to be always the ~ case with 2.4 and above kernels) - - when installed as a rpm, the System Tools/Monitoring menu now ~ launches moodss with a predefined dashboard for linux, including ~ CPU, memory and mounted file systems statistics - - included sample dashboard for Linux in moodss tarball (linux.moo) - - made tclodbc, tclperl and tclpython rpms for Red Hat Fedora Core 1 ~ available on homepage ### README ### This is moodss (Modular Object Oriented Dynamic SpreadSheet) version 17.14 and moomps (Modular Object Oriented Multi-Purpose Service) version 2.16. For Unix Review, moodss is "a must-have application for today's network and systems administrators". Moodss won in the Best System Admin Technology category (Tcl Tips and Tricks, Valuable Real World Programming Examples) at the O'Reilly Tcl/Tk 1999 Conference. Linux Magazine calls it a "lifesaver". Tucows gives it 5 stars (cows or penguins :-). Moodss is a modular application. It displays data described and updated in one or more modules, which can be specified in the command line or dynamically loaded or unloaded while the application is running. Data is originally displayed in tables. Graphical viewers (graph, bar, 3D pie charts, ...), summary tables (with current, average, minimum and maximum values) and free text viewers can be created from any number of table cells, originating from any of the displayed viewers. The display area can be extended by adding pages with notebook tabs. Thresholds can be set on any number of cells. Moomps (shipped with moodss) is a monitoring daemon which works using configuration files created by moodss. Thresholds, when crossed, create messages in the system log, and eventually trigger the sending of email alert messages and the execution of user defined scripts. For both moodss and moomps, it is also possible to use a database as a storage mean, so that data history is for example available for presentations and graphs, via commonly available spreadsheet software. Specific modules can easily be developed in the Tcl, Perl and Python scripting languages or in C. A thorough and intuitive drag'n'drop scheme is used for most viewer editing tasks: creation, modification, type mutation, destruction, ... and thresholds creation. Table rows can be sorted in increasing or decreasing order by clicking on column titles. The current configuration (modules, tables and viewers geometry, ...) can be saved in a file at any time, and later loaded at the user's convenience, thus achieving a dashboard functionality. The module code is the link between the moodss core and the data to be displayed. All the specific code is kept in the module package. Since module data access is entirely customizable (through C code, Tcl, Perl, Python, HTTP, ...) and since several modules can be loaded at once, applications for moodss become limitless. Many modules are provided, such as a comprehensive set for Linux system monitoring, MySQL, network, SNMP, Python and Perl modules examples. For example, thoroughly monitor a dynamic web server on a single dashboard with graphs, using the Apache, MySQL, ODBC, cpustats, memstats, ... modules. If you have replicated servers, dynamically add them to your view, even load the snmp module on the fly and let your imagination take over... Thorough help is provided through menus, widget tips, a message area, a module help window and a global help window with a complete HTML documentation. Moodss is multi-lingual thanks to Tcl internationalization capabilities. So far only English and partially French are supported. Help with other languages will be very warmly welcomed. Development of moodss is continuing and as more features are added in future versions, backward module code compatibility will be maintained. ### you may find it now at my homepage: http://jfon
Re: Column name: CHECK
Hi Hans, > Yes, I have used ` > > I don't think check is reserverd because I can CREATE the column name! From > the mysql prompt its possible to insert a record, but not from my Delphi > Code. After that i try to alter / delete the column but thats impossible. > Other columns in the same table where altered without any problems. How exactly with Delphi? Do you enter your SQL manually like UPDATE ... SET `CHECK` = ... ? > Maybe its a bug I can create the column name check?? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
relative performance between innodb and myisam
we have a bit of a debate going on at work. The gist of it is that there is a a performance hit when using innodb tables vs myisam tables. I understand the for a given dataload innodb will be larger, but is there a performance hit as well? If so, as a rough comparison, how much of a hit is it? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]