Re: Прошу помощи
Здравствуйте! Загляние сюда: http://dev.mysql.com/doc/refman/4.1/en/charset.html Обратите внимание на 'SET NAMES', оно должно помочь, если проблема при выходе из таблицы. Have a look here: http://dev.mysql.com/doc/refman/4.1/en/charset.html Pay attention to 'SET NAMES' statement. Гордеев Константин пишет: Здравствуйте! Интересует такой момент. Мой сайт хостится у местного провайдера. Они создали базу mySQL где у меня и лежат таблицы для моего портала. Возникла необходимость перенести модули (гостевая книга и фотогалерея) на новый хост. Файлы модулей я скопировал, проблем с этим не возникло. Однако, после импорта таблиц этих модулей, все русские буквы превратились в знаки вопросов. Как я понял дело в кодировке. Кодировка базы - Юникод UTF-8. Импортируемые таблицы находятся к Windows-1251. Но что интересно... После импорта, базы сохраняют свои истинные буквы (нормальные русские буквы). Видимо дело тут на выходе с таблицы. Как можно исправит такой глюк? Заранее Вас благодарю! _ С уважением, Гордеев Константин E-Mail: [EMAIL PROTECTED] [EMAIL PROTECTED] Web: www.gymnasium.ru -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INNODB Questions and Optimization help
Key_blocks_used 40 Key_read_requests 2019841 Key_reads 6 Key_write_requests 106 Key_writes 0 Max_used_connections 7 Not_flushed_delayed_rows 0 Not_flushed_key_blocks 0 Open_files 22 Open_streams 0 Open_tables 50 Opened_tables 56 Qcache_free_blocks 2 Qcache_free_memory 63613664 Qcache_hits 12764 Qcache_inserts 8891 Qcache_lowmem_prunes 0 Qcache_not_cached 60 Qcache_queries_in_cache 3037 Qcache_total_blocks 6103 Questions 23349 Rpl_status NULL Select_full_join 78 Select_full_range_join 0 Select_range 44 Select_range_check 0 Select_scan 328 Slave_open_temp_tables 0 Slave_running OFF Slow_launch_threads 0 Slow_queries 3 Sort_merge_passes 0 Sort_range 1682 Sort_rows 6378 Sort_scan 267 Table_locks_immediate 10386 Table_locks_waited 0 Threads_cached 5 Threads_connected 3 Threads_created 8 Threads_running 3 Uptime 282 -- 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: dump a xml result
Hello. Not enough information to make a conclusion. Please, could you provide versions of MySQL Server and mysqldump utility. Include the command line options for mysqldump. Check if --skip-quote-names helps you. See: http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html wangxu wrote: When i dump a table to a xml result mysqldump throw a warning: - Warning: Can't set SQL_QUOTE_SHOW_CREATE option () - If i dump the table to a txt result mysqldump don't throw the warning. What't the warning mean?How to avoid this warning? -- 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: Optimizing Tables - Trimming Data
| ++--++--++++--+--++---+--+--++---++--+---++-+-++--+++---+-++---++-+-+---++++---+---+-+---+--+-+---+---+---+---+--+-- + | x | x | 9 | x| x| x | x |x| x | x | x1998 | x| x | 0 | Not Known | x | x| x | 6 | 2 | 5 | NULL |2 | 3 | 2 | 4 | x| x | x | x | No | No | No| No | No | No | No| No| No | No| No | Yes | Not Known | No| NULL | No| NULL | x | ++--++--++++--+--++---+--+--++---++--+---++-+-++--+++---+-++---++-+-+---++++---+---+-+---+--+-+---+---+---+---+--+-- + 1 row in set (20.84 sec) mysql Gleb Paharenko [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hello. You will get much more help from the list if you provide the current table structure, short description of the information which is stored in that fields, and the query you want to optimize (most probably it takes much more time than others). If point 1 is true then is there a way to trim all whitespace data? There are LTRIM and RTRIM functions. See: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html Shaun wrote: Hi, I am trying to optimize my data and am currently looking at indexes etc. Someone has suggested the following as well: 1. Ensure that there is no whitespace at the beginning or end of entries 2. Use 1 or 0 instead of yes or no. If point 1 is true then is there a way to trim all whitespace data? The problem I see with point 2 is it makes querying the data more awkward to replace every 1 with a yes and 0 with a no. I would be most grateful for your comments on this. -- 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 -- 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: Error reading binlog - recoverable?
Hello. If the data in binary logs is important for you and you're ready to research and recover the data manually. Have a look here: http://dev.mysql.com/doc/internals/en/replication-binary-log.html Check if the problem still exists with mysqlbinlog from the fresh MySQL shipment. The --hexdump option for mysqlbinlog can be helpful for debugging purposes. See: http://dev.mysql.com/doc/refman/5.0/en/mysqlbinlog.html Martijn van den Burg wrote: Hi, MySQL 4.1.10-log, Solaris Replication stopped with an error that closely resembles bug #8215. When examining the binlog with mysqlbinlog, it stops before reaching the end of the binlog, with the error ERROR: Error in Log_event::read_log_event(): 'Event too big', data_len: 1635131433, event_type: 109 Could not read entry at offset 1384:Error in log format or read error Is there a way to recover the remainder of the binlog, and thus restart replication without the need to start from scratch? Regards, Martijn -- 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: Error reading binlog - recoverable?
Hello. Perhaps, there is nothing to do with a corrupted binarylog. Check one more time if you have a copy of the files without an errors. The last resource in my opinion is manually find the entry of the next event and correct the event size in the binary log. BTW the event_type: 109 looks suspicious for me as it isn't met in sql/log_event.h file. Martijn van den Burg wrote: Hi, I tried mysqlbinlog for 5.0.18, but whether I use the --hexdump option or not, it still aborts with the same error: ERROR: Error in Log_event::read_log_event(): 'Event too big', data_len: 1635131433, event_type: 109 Could not read entry at offset 1384:Error in log format or read error Is there anything else I can try? Martijn Datum: 08/02/06 01:52 PM Van: Martijn van den Burg [EMAIL PROTECTED] Aan: mysql@lists.mysql.com CC: Gleb Paharenko [EMAIL PROTECTED] Onderwerp : Re: Error reading binlog - recoverable? Hi Gleb, Thank you for you reply. I am very much willing to recover the data manually, since I am reluctant to bring down our production environment. I checked out the binlog-link you mentioned, but since mysqlbinlog quits with an error, it is of little use. I am now compiling 5.0.18 to see if the --hexdump option will work on the corrupt binlog file. If it does, I presume I can just do a 'mysqlbinlog binlog.xxx | egrep -v ^#' and feed the results back into MySQL, right? Best regards, Martijn Datum: 08/02/06 01:12 PM Van: Gleb Paharenko [EMAIL PROTECTED] Aan: mysql@lists.mysql.com CC: Onderwerp : Re: Error reading binlog - recoverable? Hello. If the data in binary logs is important for you and you're ready to research and recover the data manually. Have a look here: http://dev.mysql.com/doc/internals/en/replication-binary-log.html Check if the problem still exists with mysqlbinlog from the fresh MySQL shipment. The --hexdump option for mysqlbinlog can be helpful for debugging purposes. See: http://dev.mysql.com/doc/refman/5.0/en/mysqlbinlog.html Martijn van den Burg wrote: Hi, MySQL 4.1.10-log, Solaris Replication stopped with an error that closely resembles bug #8215. When examining the binlog with mysqlbinlog, it stops before reaching the end of the binlog, with the error ERROR: Error in Log_event::read_log_event(): 'Event too big', data_len: 1635131433, event_type: 109 Could not read entry at offset 1384:Error in log format or read error Is there a way to recover the remainder of the binlog, and thus restart replication without the need to start from scratch? Regards, Martijn -- 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: question about locking
Hello.
The logic of your application is clear and should work (though I haven't
been digging deeply inside the code). Check that the table type is
InnoDB. 4.0.1 version is rather old and could have lots of bugs, I
recommend you to upgrade to the latest release. Another reason, is that
your algorithm generates overlapped intervals of transerid values, but
this is not a MySQL issue. Enable the general log and check the
sequences of queries produced by your container.
Patrick Duda wrote:
Hi,
I am running MySQL 4.0.1 with j/connector 3.1 and I am having problems
trying to figure out why I am not getting the results I am expecting.
I have a table that is used for generating primary keys. It only has
one item, an int that is incremented each time a key is needed. This is
not my code or my design so using something like auto_incrament is not
an option.
The code runs under a container and our desire is to have several
different containers running at the same time, all accessing the same
database. Each container is independent so the controls need to be on
the database side.
The solution also needs to be portable to other databases so I am trying
to stay with standard JDBC or SQL options.
The code for generating a new key is this:
try {
c = DatabaseSetup.getDBConnection();
c.setAutoCommit(false);
statement =
c.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_UPDATABLE);
rs = statement.executeQuery(select transfer_id from transferid for
update);
if (!rs.next()) {
nextTransferId = nextTransferId + 1;
StringBuffer query = new StringBuffer();
query.append(insert into transferid(transfer_id) values
();
query.append(nextTransferId);
query.append());
tempStatement = c.createStatement();
// Now Update the old value with new value
tempStatement.executeUpdate(query.toString());
} else {
rs.previous();
while( rs != null rs.next() ) {
nextTransferId = rs.getInt(1);
// Get the transfer Id and increment it instead of
using
// Db Specific sequence
nextTransferId = nextTransferId + 1;
// Now Update the old value with new value
tempStatement = c.createStatement();
tempStatement.executeUpdate(update transferid set +
transfer_id= + nextTransferId);
}
}
} catch (SQLException e) {
if( c != null )
{
try
{
c.rollback();
c.setAutoCommit(true);
}
catch( SQLException ex )
{
}
}
throw new DBException(i18n.getMessage(dbInsertErr),
e);
} finally {
try {
c.commit();
c.setAutoCommit(true);
if (statement != null) {
statement.close();
}
if (tempStatement != null) {
tempStatement.close();
}
if (rs != null) {
rs.close();
}
if (c != null) {
DatabaseSetup.returnDBConnection(c);
}
} catch (SQLException sql) {
logger.warn(i18n.getMessage(dbStatementErr), sql);
}
}
return nextTransferId;
}
I thought, that if I turned off autocommit I would enter a transaction.
Then, by using the select...for update, that I would take and hole a
lock on the table. That no other transaction would be able to read the
table until I released the lock. However, this is not what I am seeing
when I run some tests. I start up a number of containers and then fire
off a bunch of jobs to each. Each of these jobs will hit the above
code. The problem is that every so often I see the following error
message.
Error inserting records into database [Caused by: Duplicate entry '131'
for key 1]
What am I doing wrong?
How am I suppose to be doing this via JDBC? I know it should work...
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Exporting Date
Hello. This seems like a FAQ. Search in archives at: http://lists.mysql.com See: http://lists.mysql.com/mysql/101771 http://dev.mysql.com/doc/refman/5.0/en/select.html http://dev.mysql.com/doc/refman/5.0/en/csv-storage-engine.html Kleiton L R Soares wrote: Good Morning Hi, i need make a query on my database and the result save in a text file with coma (CSV-file) Somepeople can help-me 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Moving from PowWeb to Rackspace
Hello. Usually my preferred option is mysqldump, however, a lot of depends on your production environment, and how long your server can be off-line. You should know about different ways to move your databases to another machine: http://dev.mysql.com/doc/refman/5.0/en/moving.html http://dev.mysql.com/doc/refman/5.0/en/innodb-backup.html http://dev.mysql.com/doc/refman/5.0/en/backup.html Brian Dunning wrote: I have a bunch of databases - some are really big, 2GB - on a number of different accounts at PowWeb. I am buying a Rackspace server and want to move everything over -- hopefully all in one night. Can anyone suggest the best way to do this? Would it be to use the Export command in phpMyAdmin? -- 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: replacement for Oracle initcap function
Hello.
It seems that there is no such functions:
http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
However, it should be possible to do with:
http://dev.mysql.com/doc/refman/5.0/en/stored-procedures.html
http://dev.mysql.com/doc/refman/5.0/en/adding-functions.html
Sid Lane wrote:
I am finishing up on performing an Oraclectomy on a bunch of legacy java
code (don't ask why the DBA got stuck w/this - sore subject) and have one
outstanding problem to solve:
Oracle has a function, initcap(), which capitalizes the 1st character of
each word and lowercases the rest. for example, initcap('ABC DEF GHI') =
'Abc Def Ghi'.
I have not found a (my)sql way to do this. did I overlook something or do I
need to do this client side after I fetch the results?
--
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: Can't Create Foreign Key Constraints
Hello. The query which is works is: CREATE TABLE `Films` ( `FilmID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `MovieTitle` TEXT NULL ,`PitchText` TEXT NULL, `AmountBudgeted` DECIMAL(11, 0) NULL, `RatingID` INT(11) unsigned , `Summary` LONGTEXT NULL, `ImageName` VARCHAR(50) NULL, `DateInTheaters` DATETIME NULL, PRIMARY KEY (`FilmID`), CONSTRAINT `FK_RatingID` FOREIGN KEY `RatingID` (RatingID) REFERENCES `FilmsRatings` (`RatingID`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; See: http://dev.mysql.com/doc/refman/5.0/en/create-table.html Lola J. Lee Beno wrote: Michael Stassen wrote: 1) I'm not sure what you are intending with (`(not null)`) in the middle of your foreign key definition, but that isn't valid mysql syntax. See the manual for the correct syntax http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html. This is from the script that was generated using Mysql Workbench, 1.0.3-alpha. I tried it with (null) and (not null); neither worked. 2) In FilmsRatings, RatingID is defined as INT UNSIGNED, but in Films it is an INT. The manual says I then modified the query as such: CREATE TABLE `ows`.`Films` ( `FilmID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `MovieTitle` TEXT NULL, `PitchText` TEXT NULL, `AmountBudgeted` DECIMAL(11, 0) NULL, `RatingID` INT(11) UNSIGNED NULL, `Summary` LONGTEXT NULL, `ImageName` VARCHAR(50) NULL, `DateInTheaters` DATETIME NULL, PRIMARY KEY (`FilmID`), CONSTRAINT `FK_RatingID` FOREIGN KEY `RatingID` (`(not null)`) REFERENCES `ows`.`FilmsRatings` (`RatingID`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; No dice. 3) Again quoting the manual, You can use SHOW ENGINE INNODB STATUS to display a detailed explanation of the most recent InnoDB foreign key error in the server. Which gives me: LATEST FOREIGN KEY ERROR 060207 8:33:49 Error in foreign key constraint of table ows/#sql-a8_11: foreign key (RatingID) references FilmsRatings (RatingID): Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with = InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables. See http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html for correct foreign key definition. Which leads me back to the same URL that you gave me. so, it looks like I should create an index for FilmsRatings first, and then create the table Films - is that correct? -- 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: Can't Create Foreign Key Constraints
Hello. You can post a bug: http://dev.mysql.com/doc/refman/5.0/en/bug-reports.html Lola J. Lee Beno wrote: Gleb Paharenko wrote: Hello. The query which is works is: Thanks - just what I needed. Looks like I'll need to be extra careful with sql scripts generated from Mysql Workbench, which is still alpha right now. -- 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: Spatial Extensions in MySQL: Multidimensional Points?
Hello. For a pity, I do not have an exact answer on how to do your task in a best way with MySQL. Searching in the archives gave me these links: http://lists.mysql.com/mysql/185851 http://lists.mysql.com/mysql/185846 Perhaps, you will need the help of stored routines or UDFs: http://dev.mysql.com/doc/refman/5.0/en/stored-procedures.html http://dev.mysql.com/doc/refman/5.0/en/adding-functions.html Jose Antonio wrote: MySQL offers Spacial Extensions to operate with points in a 2 dimensional space. For a project I'm working on, I need to work with points in an N dimensional space and measure the distance among them. Is there a clever do to solve the following problem?: Let's say I have 4 3D point (X,Y,Z): (0,0,0), (1,1,1), (4,4,2), (-2,30,18) And for a new point (3, -1, 1.5), I need to know which of my other points are closer and what the distance is. Any clues? Thank you in advance, Jose. -- 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: Report Generator
Hello. I'm not sure, but have a look here: http://dev.mysql.com/tech-resources/articles/dba-dashboard.html Chuck Craig wrote: Hi, I'm new to the list and not sure whether my question belongs here or not. I'm looking for an open source program, that runs on JSP, to generate reports on data in MySQL databases. I've found a few myself but they run on PHP. Any thoughts or advice would be very appreciated. -Chuck Craig -- 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: Out of Range value adjusted?
Hello.
If you still want to use the decimal(3,2) data type for your table
(even after excellent explanations by Shawn), check that the pure
instance of MySQL doesn't use STRICT sql mode. See:
http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html
Kerry Frater wrote:
I am trying to test some code on the new 5.0 version and am getting
problems.
I currently have two MYSQL's running on two differing machines. Both have
the same schema set up by the my own program. The difference is that Machine
1 was set up on version 4.1 and then upgraded to 5. Machine 2 was a fresh
installation of version 5.
My Data insertion code works perfectly well with the Version 5 instance that
was upgraded from version 4, but fails on the pure version 5. The
Administrator interface tells me that the tables are OK and I cannot see any
difference between the V4 upgraded to 5 table against the pure V5 install.
The code being used is
INSERT INTO Invs
(InvRef,InvDate,Outgoingref,Outgoingref2,BillDescription,BillAmount,VatCode,
VatRate)
VALUES
('1234#6','19991016','C','19990731','\Orig Bill
£728.50\',364.68,'A',17.50);
The table columns are set to
char(12),date,char(1),date,char(40),decimal(12,2),char(1),decimal(3,2). The
table is MyISAM.
There error produced on the pure version 5 installation is:
Out of Range value adjusted for column VATRate at row 1
and then quits. I have let the installation of MySQL use its defaults and is
of charset latin1 if it is an issue. The above statement does not error on
the upgraded version of MySQL but adds the row with no problem.
The set up program is using a function
with SQLBatch.SQL do
begin
Add('CREATE TABLE Invs (');
Add('InvRef char(12) default NULL,');
Add('InvDate date,');
Add('OutgoingRef char(1),');
Add('OutgoingRef2 date,');
Add('BillDescription char(40),');
Add('BillAmount decimal(12,2),');
Add('VATCode char(1),');
Add('VATRate decimal(3,2),');
Add(' index (OutgoingRef2)');
Add(') TYPE=MyISAM;');
end;
SQLBatch.ExecSql;
This seems to work fine. Any ideas on why it works with one and not the
other?
Kerry
--
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: update a Blob field using UPDATE
Hello. Have you applied mysql_real_escape_string to your BLOB variable first? See: http://dev.mysql.com/doc/refman/5.0/en/mysql-real-escape-string.html I do not see the quotes around mystring$ as well. Kerry Frater wrote: I am importing data from a non MySQL table into MySQL. In the table there is a text field of up to length 4000 chars. I have defined the column as blob in the MySQL table. I can read the text field of the source table into a variable e.g. mystring$. The MySQL table has been set, except for this data. I thought to use SQLString = UPDATE TheTable SET Notes = + mystring$ + WHERE TheTableRef = ' + Myref$ + '; I get error: You have an error in the SQL syntax I have tried to search the manual for an example of updating a blob column from a variable and cannot find one. I don't want to save the content of the var to disk and then load from file because of the time it takes. Do I have another option? Kerry -- 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: Optimizing Tables - Trimming Data
Hello. You will get much more help from the list if you provide the current table structure, short description of the information which is stored in that fields, and the query you want to optimize (most probably it takes much more time than others). If point 1 is true then is there a way to trim all whitespace data? There are LTRIM and RTRIM functions. See: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html Shaun wrote: Hi, I am trying to optimize my data and am currently looking at indexes etc. Someone has suggested the following as well: 1. Ensure that there is no whitespace at the beginning or end of entries 2. Use 1 or 0 instead of yes or no. If point 1 is true then is there a way to trim all whitespace data? The problem I see with point 2 is it makes querying the data more awkward to replace every 1 with a yes and 0 with a no. I would be most grateful for your comments on this. -- 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: Character set problem
Hello. Perhaps it is an issue of your collation: mysql select a from ts where a like '%ó%' collate utf8_bin ; ++ | a | ++ | Mester József | ++ 1 row in set (0.00 sec) mysql select a from ts where a like binary '%ó%'; ++ | a | ++ | Mester József | ++ mysql select a from ts where a like '%ó%'; ++ | a | ++ | Mester József | | Job György | | Czibere Lajos | ++ See: http://dev.mysql.com/doc/refman/5.0/en/charset-collations.html Mester József wrote: Hy I have a table datas like that : name Mester József Job György Czibere Lajos If I create : select name from dolgozok where name like '%jó%' ; then all data will be shown. But I wolud like see datas which really contain ó character (only Mester József). Joe - Win a BlackBerry device from O2 with Yahoo!. Enter now. -- 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: mysqld got signal 11
Privet! key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 441967 K Check that you have enough virtual memory for MySQL processes. Have a look at: http://dev.mysql.com/doc/refman/5.0/en/freebsd.html http://dev.mysql.com/doc/refman/5.0/en/crashing.html What version of MySQL are you using and in what way have you installed it. Please report an exact version of FreeBSD. Гаврилов Вячеслав wrote: Hello developers of MySql. It is my problem: --- mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=33554432 read_buffer_size=2093056 max_used_connections=22 max_connections=100 threads_connected=8 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 441967 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. -- This message from mysql_err.log. It up very often. At 1-2 days.How you comment this? In my system FreeBSD work RADIUS and write account in database on MySQL server . Calls friquency is 1-8 in one second. -- 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: Creating REGEXP patterns with use of subquery
Hello. This looks like a task for prepared statements. You can dynamically form the string from the query using GROUP_CONCAT, assign the created string to the variable. Then just prepare a right statement and invoke it using that variable. See: http://dev.mysql.com/doc/refman/5.0/en/sqlps.html http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html Kim Christensen wrote: Is there any way to build a REGEXP pattern set by using a subquery? I have a set of rows in table table, with the column value being target for my query. That column's content is a bracket separated list of values, like this: [118][Word][Integer][Stuff]... [67][Another word][Another integer][More stuff]... Now, to get all rows which value string starts with [118], this does the trick: SELECT * FROM table WHERE value REGEXP '^\\[118' And further on, to get all rows which value string starts with either [21], [42] or [999], this works fine: SELECT * FROM table WHERE value REGEXP '^\\[(21|42|999)' But I need to be able to do this last query without having to specify the values by hand, but with a subquery. Imagine the following query: SELECT id FROM items WHERE parent=5 This gives me a result set of rows which parent columns matches 5. I would like to use all these results in the last REGEXP query example above, something like this: SELECT * FROM table WHERE value REGEXP '^\\[(SELECT id FROM items WHERE parent=5)' Anyone got any clues? -- Kim Christensen [EMAIL PROTECTED] -- 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: auto_increment
Hello. Please, could add more details of your actions. Are you inserting in the table which already has data? Please, provide exact error message. I'm not a telepathist, but in case of duplicate key errors a brute solution is to perform a dump with --insert-ignore option. Scott Johnson wrote: Hi All, I'm trying to restore a database where the first column is set to auto_increment. When I apply the inserts created from the mysqldump I get errors about the auto_increment column. Is there a may to turn it off while I run the inserts or do I have to modify the table? Thanks, Scott Johnson [EMAIL PROTECTED] Tel.: (514) 917-4922 Fax: (514) 673-0011 -- 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: Help please
Hello. Have a look here: http://dev.mysql.com/doc/refman/5.0/en/crashing.html If you feel that there are too much sockets in a TIME_WAIT have a look here: http://dev.mysql.com/doc/refman/5.0/en/communication-errors.html Logg, Connie A. wrote: Two days ago, a system that has been running fine started crashing...It could be for a variety of reasons which I am researchinig. However (running mysql 5.0.18) I notice the following from netstat: tcp0 0 iepm-bw.slac.stanford.:1000 iepm-bw.slac.stanford:38672 ESTABLISHED tcp0 0 iepm-bw.slac.stanford.:1000 iepm-bw.slac.stanford:38775 TIME_WAIT tcp0 0 iepm-bw.slac.stanford.:1000 iepm-bw.slac.stanford:38781 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38780 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38781 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38782 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38783 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38776 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38777 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38778 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38779 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38772 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38773 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38774 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38768 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38769 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38770 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38771 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38764 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38765 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38766 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38760 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38761 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38762 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38763 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38756 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38757 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38758 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38759 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38752 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38753 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38754 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38755 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38748 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38749 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38750 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38751 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38744 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38745 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38746 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38747 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38742 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38743 iepm-bw.slac.stanford.:1000 TIME_WAIT tcp0 0 iepm-bw.slac.stanford:38672 iepm-bw.slac.stanford.:1000 ESTABLISHED One of the messages in /var/log/messages is too many orphaned sockets. Do the above indicate orphaned sockets? I logged into mysql root and did a 'show full processlist' and there were only one or two mysql processes. Can someone explain why there might be so many tcp sockets taken up to connect to mysql (which is running on port 1000). Thanks, Connie Logg SLAC -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko
Re: Problem storing lonf files
Hello. Have a look here: http://dev.mysql.com/doc/refman/5.0/en/gone-away.html I suggest you to check the max_allowed_packet. See: http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html http://dev.mysql.com/doc/refman/5.0/en/packet-too-large.html Celestino Gomez Cid wrote: Dear All, I am trying to store (using the C API) a long field in a longblob table field. The size of the sentence is 2361408 Bytes and when using the function mysql_real_query it returns an error saying that the MySQL server has gone. However, if I reduce the data to be stored in a factor of 4. Then the data is stored without problems. Does anybody know what is happenning ? Thanks very much, Celestino. __ Este mensaje, y en su caso, cualquier fichero anexo al mismo, puede contener informacion clasificada por su emisor como confidencial en el marco de su Sistema de Gestion de Seguridad de la Informacion siendo para uso exclusivo del destinatario, quedando prohibida su divulgacion copia o distribucion a terceros sin la autorizacion expresa del remitente. Si Vd. ha recibido este mensaje erroneamente, se ruega lo notifique al remitente y proceda a su borrado. Gracias por su colaboracion. __ This message including any attachments may contain confidential information, according to our Information Security Management System, and intended solely for a specific individual to whom they are addressed. Any unauthorised copy, disclosure or distribution of this message is strictly forbidden. If you have received this transmission in error, please notify the sender immediately and delete it. __ -- 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: Type and Size of JOIN fields
Hello. In my opinion it shouldn't because, according to the manual the value in brackets affects only the display characteristics of the fields. See: http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html C.R.Vegelin wrote: Hello All, More than once I read on this list that problems may occur, because of unequal types and/or sizes of join fields. Suppose a Countries table with primary key ID SmallInt(5), and a Accounts table with CountryID SmallInt(4). Does this have any negative affect, eg. on performance ? By the way, I am using MyISAM tables for MySQL 5.0. Cor -- 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: error 1146 X.1 does not exist
Hello. MySQL CC is not supported now, and could have some problems with a fresh versions of MySQL. If error doesn't appear in latest MySQL Administrator then everything is ok. [local] ERROR 1146: Table 'llcopy.1' doesn't exist Have a look here: http://dev.mysql.com/doc/refman/5.0/en/cannot-find-table.html The error was ERROR 1054 (42S22): Unknown column 'C1' in 'field list' Check if the sql_mode has ANSI_QUOTES enabled. See: http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html Kerry Frater wrote: Can someone point me in the right direction. I had 4.1 running and all was well with my little program. I decided to test the code with the newer 5.0.18. I updated my DB and then tried to access it using the MYSQl Control Centre - 0.9.4-Beta and query browser 1.0.4 alpha. The Administrator allows me to view table structure but not to add any numerical columns. Using the Control centre, If I double click on the table I wish to view data I get two lines in the message area Empty set (0.00) sec [local] ERROR 1146: Table 'llcopy.1' doesn't exist llcopy being my test database name. These messages are got no matter what the table content is. I do note that the error message on this app is different numerically to the previous Also my insert program now fails to insert data. So I extracted one of the SQL insert commands that worked with 4.1 and manually run it using the mysql.exe command interface. This is the manual command. INSERT INTO BanksC (Bank,BankName,AccountNo,Balance,PMClosingBalance,EOMTempBalance,STTS) VALUES (C1,Client Bank,NONE,0.00,0.00,0.00,C); The error was ERROR 1054 (42S22): Unknown column 'C1' in 'field list' The settings of the table are char(2), char(20), char(12), decimal(12,2),decimal(12,2),decimal(12,2),char(1) respectively. This is driving me crazy. I am obviously doing something stupid but I have got so close to the problem I can't see the wood for the trees. Kerry -- 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: latin1 -utf8 conversion
Hello. Start from reading this part of the manual: http://dev.mysql.com/doc/refman/5.0/en/charset.html From my understanding, the database itself never do any conversion, meaning if you insert utf8 data into tables declared as latin1 it doesn't really matter if you retrieve the data as utf8 on the client side(true?) Database does do conversion. If fields in a table have latin1 character set, all characters that are not present in latin1, will be converted most probably to '?'. utf8 can held all symbols which are in latin1, and in my opinion there shouldn't be any problems. mel list_php wrote: Hi list, I guess this is a classic problem...! I found that on the web: http://www.oreillynet.com/pub/wlg/9022?wlg=yes, where basically the guy did dump data, change the charset in the table definition and reinsert the records into an utf8 database and ended up with some problems... I saw somewhere in the mysql doc (unable to find the link back though) that converting between charsets can be tricky, especially if you're not sure of what you actually have. Which is my problem: the tables are latin1, but some people may have executed queries from the command line (utf8) and inserted data (which are then utf8 right?), some may have used an utf8 phpmyadmin (producing utf8 data?) and some a old isolatin one.(which would then give latin1 data?) I think the majority of the data are latin1, but there may be some utf8 at some places. I have mostly basic characters, and a few names with accents. I saw somewhere that you can convert to binary before to be sure of keeping things right. From my understanding, the database itself never do any conversion, meaning if you insert utf8 data into tables declared as latin1 it doesn't really matter if you retrieve the data as utf8 on the client side(true?) I strongly suspect that I'm kind of intolerant to encodings and how to manage them, I just don't get it. Does anybody knows what is the best way to do? Would a dump be enough? Does the dump itself need to be utf8 encoded to be loaded properly? Do I need to load it through an utf8 interface? I have an old iso latin shell, an utf8 one, and 2 versions of phpmyadmin, one latin1 and one utf8: does it matter where I will load the dump from? Any help more than welcome! Thanks, melanie _ Be the first to hear what's new at MSN - sign up to our free newsletters! http://www.msn.co.uk/newsletters -- 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: tmpdir option
Hello. It'll say that the disk is full, and will not make any attemt to ignore it and use the second partition. Eamon Daly wrote: This is wishful thinking, but I figured I'd ask anyway: the manual states: Starting from MySQL 4.1, the --tmpdir option can be set to a list of several paths that are used in round-robin fashion. Are these directories promotable at all? In other words, if I specify an 8G partition and a 20G partition, and a temporary table runs out of space in the former, will MySQL attempt to move it to the latter? We've been considering the purchase of a flash drive as a temporary disk for some of our larger reports, but on occassion we'll run a Very Large Report that would easily overrun the smaller disk. Eamon Daly -- 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: Testing Concurrent Insert on InnoDB
Hello. The table should not be locked, right :D Most probably it will be locked. If you are not running in strict SQL mode, any column not explicitly given a value is set to its default (explicit or implicit) value. For example, if you specify a column list that does not name all the columns in the table, unnamed columns are set to their default values. For an AUTO_INCREMENT column, the default value is the next value in the sequence. So your insert will put an AUTO-INC lock on the table. Ady Wicaksono wrote: Hi Gleb Thanx a lot for explanation, make sense. I haven't try with INSERT INTO Y (t_y_time,and so on) SELECT (t_x_time, ... and so on) FROM X excluding t_y_id The table should not be locked, right :D Thx Gleb Paharenko wrote: Hello. It seems that the problem is in the t_y_id auto_increment field. InnoDB puts special AUTO-INC table lock, and prevent other threads from inserting into Y. See: http://dev.mysql.com/doc/refman/5.0/en/innodb-auto-increment-column.html Ady Wicaksono wrote: Below is the SQL to create table Y CREATE TABLE `Y` ( `t_y_id` int(11) NOT NULL auto_increment, `t_y_time` timestamp NOT NULL default CURRENT_TIMESTAMP, `t_y_dest` varchar(16) NOT NULL default '', `t_y_msg` varchar(160) NOT NULL default '', `t_y_status` tinyint(2) NOT NULL default '0', `t_y_type` varchar(16) NOT NULL default '', `t_y_trx_id` varchar(40) NOT NULL default '', `t_y_trx_date` varchar(33) NOT NULL default '', `t_y_serviceid` varchar(20) NOT NULL default '', `t_y_pin` varchar(15) NOT NULL default '', `t_y_key` varchar(20) NOT NULL default '', `t_y_ans` varchar(160) NOT NULL default '', `in_sms_message_id` varchar(22) NOT NULL default '', `in_sms_time` datetime NOT NULL default '-00-00 00:00:00', `t_y_city` varchar(50) NOT NULL default '', PRIMARY KEY (`t_y_id`), KEY `t_idx01` (`t_y_type`(3)), KEY `t_idx02` (`t_y_key`(3)), KEY `t_idx03` (`t_y_ans`(8)), KEY `t_idx04` (`t_y_dest`(7)), KEY `t_idx05` (`t_y_dest`(13),`t_y_key`(15),`t_y_ans`(10)), KEY `t_idx06` (`t_y_time`), KEY `t_idx07` (`t_y_time`,`t_y_key`(6)), KEY `t_idx08` (`t_y_trx_id`(8)), KEY `t_idx09` (`t_y_trx_id`(10),`t_y_dest`(6)), KEY `t_idx10` (`t_y_status`,`t_y_type`(3)), KEY `t_idx11` (`in_sms_time`), KEY `t_idx12` (`t_y_time`,`t_y_type`(3)), KEY `t_idx13` (`t_y_city`(7)) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -- 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: charset
Hello. mysqld has a --default-character-set=name command line option. Put latin5 instead of 'name'. It should be possible to specify this variable through the configuration file or environment variable. You can check if your server supports latin5 with: show variables like 'character_sets'; statement. OKAN ARI wrote: yes it is mysql 4.0 how can I set i to latin 5 or how can I set the server to latin 5? os: centos 4.2 Thank you - Original Message - From: Gleb Paharenko [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, January 31, 2006 1:40 PM Subject: Re: charset Hello. In case mysql4 is mysql4.0 there should be a server system variable default_character_set. If it is 4.1 see: http://dev.mysql.com/doc/refman/4.1/en/charset-syntax.html http://dev.mysql.com/doc/refman/4.1/en/charset-connection.html OKAN ARI wrote: How can I set the characterset to latin5 for mysq4 in linux? OKAN -- 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] -- 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: OPTIMIZE fails on very large table
Hello. If you're not using per-file tablespace, the OPTIMIZing of the table most probably won't decrease the size of ibdata files. See: http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html about how to decrease the size of the tablespace. OPTIMIZE table is mapped to ALTER TABLE for InnoDB, and it recreates the whole table, and most probably it will need a lot of space in the directory where MySQL stores temporary files. In case this table is in a separated file, you can dump it to another machine with mysqldump through the network, drop it, and restore from the dump. Rithish Saralaya wrote: Hello. We had an INNoDB table of very large size of 47 GB. Recently we deleted a lot of rows from the table in order to reduce the size. The number of rows now stands at 164,000+, however, the disk usage remains the same. I tried OPTIMIZEing the table; but the optimize fails after running for about an hour and a half. We have 15 GB of free space left in /var/lib/mysql/ and about 14GB in /tmp/ The MySQL server is running on a 2*3.00 GHz linux box of 4 GB ram and 140 GB HDD. How will we able to regain the free space? Comments/suggestions/flamings solicited. Regards, Rithish. -- 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: Rows counted but not returned after join
Hello. trying to write a query to find out whether there are any email addresses in the first table that do not have a counterpart in the second. It seems like a job for a LEFT JOIN. To see the records which are present in table A and not present in table B use this query: SELECT A.* FROM A LEFT JOIN B USING(common_field) WHERE B.common_field is NULL. See: http://dev.mysql.com/doc/refman/5.0/en/join.html There is a good article as well: http://dev.mysql.com/tech-resources/articles/mysql-db-design-ch5.html Dougal Watson wrote: When I perform a particular query using a join the number of rows counted as hits exceeds the number of rows returned. I think I know why but don't know how to interrogate the database to confirm (and remedy) my suspicion. The basic database structure is two tables. Each table contains an email address field. The join allows me to link a publication reference in the first table, via the email address, to the author's name in the second. I suspect my problem results from there being a couple of email addresses in the first table that do not have a counterpart in the second. I've been trying to write a query to find out whether there are any email addresses in the first table that do not have a counterpart in the second. My follow-on question is how do I write my original query so that if there's no corresponding email address in the second table, a result is still returned? My basic query is of the form: $query = SELECT datetime, author_email, title, body, author.person AS author_name FROM $table01, $table02 WHERE ( ( ((body REGEXP '$searchstring01') OR (title REGEXP '$searchstring01')) $choice3 ((body REGEXP '$searchstring02') OR (title REGEXP '$searchstring02')) $choice4 ((body REGEXP '$searchstring03') OR (title REGEXP '$searchstring03')) ) AND ((YEAR(datetime) = '$startyear') AND (YEAR(datetime) = `$endyear')) AND (postings.author_email = author.email) ) ORDER BY datetime DESC I also perform a COUNT query but that query doesn't include the join, just the hits against the searchstrings. I've tried the COUNT with the join but it returns and error. I'm sorry if these are very basic, MySQL 101, questions ... I am a very basic recreational coder :-) Cheers Dougal -- 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: charset
Hello. In case mysql4 is mysql4.0 there should be a server system variable default_character_set. If it is 4.1 see: http://dev.mysql.com/doc/refman/4.1/en/charset-syntax.html http://dev.mysql.com/doc/refman/4.1/en/charset-connection.html OKAN ARI wrote: How can I set the characterset to latin5 for mysq4 in linux? OKAN -- 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: upgrade to mysql 5.x?
Hello. Most probably you have to upgrade when you need one of the new features of MySQL 5.0. See: http://dev.mysql.com/doc/refman/5.0/en/mysql-5-0-nutshell.html suomi wrote: Hi listers i have been looking around quite a bit for info on - when do i need to upgrade to mysql 5.x (because such and such functionality is not available in 4.1.x) - when is it recommended to upgrade to mysql 5.x (because such and such functionality is better implemented) - when will i fail to upgrade to mysql 5.x (because such and such database/type/funtion is not supported in mysql 5.x) - which are the pre-requisites to upgrade? - do i need to send my database through any batch when upgrading (such as when upgrading to 4.1: the grant tables) could anybody shed some light on these issues? 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]
Re: Type conversion from Mysql to Oracle
Hello. In case you want to automate the process of migration have a look here: http://dev.mysql.com/doc/migration-toolkit/en/index.html There is a specific forum as well: http://forums.mysql.com/list.php?61 Xiaobo Chen wrote: Hi, folks I am trying to convert some Mysql database into Oracle. But their types are not exactly match. Could any one give a hint? Thanks in advance! Xiaobo -- 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: Testing Concurrent Insert on InnoDB
Hello. It seems that the problem is in the t_y_id auto_increment field. InnoDB puts special AUTO-INC table lock, and prevent other threads from inserting into Y. See: http://dev.mysql.com/doc/refman/5.0/en/innodb-auto-increment-column.html Ady Wicaksono wrote: Below is the SQL to create table Y CREATE TABLE `Y` ( `t_y_id` int(11) NOT NULL auto_increment, `t_y_time` timestamp NOT NULL default CURRENT_TIMESTAMP, `t_y_dest` varchar(16) NOT NULL default '', `t_y_msg` varchar(160) NOT NULL default '', `t_y_status` tinyint(2) NOT NULL default '0', `t_y_type` varchar(16) NOT NULL default '', `t_y_trx_id` varchar(40) NOT NULL default '', `t_y_trx_date` varchar(33) NOT NULL default '', `t_y_serviceid` varchar(20) NOT NULL default '', `t_y_pin` varchar(15) NOT NULL default '', `t_y_key` varchar(20) NOT NULL default '', `t_y_ans` varchar(160) NOT NULL default '', `in_sms_message_id` varchar(22) NOT NULL default '', `in_sms_time` datetime NOT NULL default '-00-00 00:00:00', `t_y_city` varchar(50) NOT NULL default '', PRIMARY KEY (`t_y_id`), KEY `t_idx01` (`t_y_type`(3)), KEY `t_idx02` (`t_y_key`(3)), KEY `t_idx03` (`t_y_ans`(8)), KEY `t_idx04` (`t_y_dest`(7)), KEY `t_idx05` (`t_y_dest`(13),`t_y_key`(15),`t_y_ans`(10)), KEY `t_idx06` (`t_y_time`), KEY `t_idx07` (`t_y_time`,`t_y_key`(6)), KEY `t_idx08` (`t_y_trx_id`(8)), KEY `t_idx09` (`t_y_trx_id`(10),`t_y_dest`(6)), KEY `t_idx10` (`t_y_status`,`t_y_type`(3)), KEY `t_idx11` (`in_sms_time`), KEY `t_idx12` (`t_y_time`,`t_y_type`(3)), KEY `t_idx13` (`t_y_city`(7)) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 -- 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: my.cnf files
Hello. Example files are usually shipped with MySQL distribution. Check the support-files directory for my-*.cnf examples. Search in archives at http://list.mysql.com/mysql as well. Philip R. Thompson wrote: Hi all. I am having some troubles with what should be contained within my my.cnf file. Would a few of you be willing to show me what yours consists of? Thanks, ~Philip -- 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: default characterset of function string parameter
Hello.
This seems like a bug:
http://bugs.mysql.com/bug.php?id=16676
Hirofumi Fujiwara wrote:
Dear MySQL fans,
I am checking MySQL 5.0 to use multibyte strings, Japanese.
When I use char type parameter without CHARACTER SET clause,
parameter's character set is not the database character set but
latin1.
17.2.1. CREATE PROCEDURE says:
For character data types, if there is a CHARACTER SET clause
in the declaration, the specified character set and its default
collation are used. If there is no such clause, the database
character set and collation are used. (These are given by the
values of the character_set_database and collation_database
system variables.)
Following is my test:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
mysql SET @@character_set_database=eucjpms;
Query OK, 0 rows affected (0.00 sec)
mysql SHOW VARIABLES LIKE 'character\_set\_%';
+--+-+
| Variable_name| Value |
+--+-+
| character_set_client | eucjpms |
| character_set_connection | eucjpms |
| character_set_database | eucjpms |
| character_set_results| eucjpms |
| character_set_server | latin1 |
| character_set_system | utf8|
+--+-+
6 rows in set (0.00 sec)
mysql DELIMITER //
mysql CREATE FUNCTION parametercharset( s CHAR(20) )
- RETURNS CHAR(50) CHARACTER SET binary
- DETERMINISTIC RETURN CONCAT( s, ':', CHARSET(s) );
- //
Query OK, 0 rows affected (0.00 sec)
mysql DELIMITER ;
mysql SELECT parametercharset('hello');
++
| parametercharset('hello') |
++
| hello:latin1 |
++
1 row in set (0.00 sec)
mysql
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Hirofumi Fujiwara (Tokyo JAPAN) enjoy JAVA and Puzzle World
[EMAIL PROTECTED] http://www.pro.or.jp/~fuji/index-eng.html
[EMAIL PROTECTED] Puzzle Japanhttp://www.puzzle.jp/
My SUDOKU Probs http://www.pro.or.jp/~fuji/sudoku/problems/
--
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: Revert using Binlog
Hello. If you have all inserts and updates on the tables from which you've lost your data, you can simply recover the whole database from the binary logs, however if the logs were create later than tables, most probably you won't get the consistent data in them. Use --stop-datetime option for mysqlbinlog utility. See: http://dev.mysql.com/doc/refman/5.0/en/mysqlbinlog.html Rob Gormley wrote: Am hoping someone can give me the safest advice here. Using 4.1 and InnoDB, someone in an attempt to be helpful set up a lot of foreign key constraints. However... ON DELETE CASCADE When we needed RESTRICT. Net effect? Some editing today lead to cries of Where has half the data gone? We have the binlog, and know the point at which the erroneous stuff happened - however, no backup exists, so I can't roll forward from that (don't ask)... Is it at all possible to rollback using the binlog to that point? Or is it time to get back to manual data entry? Any help greatly appreciated. Rob -- 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: Testing Concurrent Insert on InnoDB
Hello. According to: http://dev.mysql.com/doc/refman/5.0/en/innodb-locks-set.html INSERT ... SELECT set a non-next-key lock on each row. So in most cases you should be able to insert the record into Y. Please, provide the CREATE statement for table Y and the output of 'show variables' statement. In my opinion, the problem could be in keys, if they're present in the table. Include the output of SHOW PROCESSLIST and SHOW INNODB STATUS as well. Ady Wicaksono wrote: I use MySQL 5.0.15 I have about 5 billion rows in table X, i create another table Y with the same stucture with X CREATE TABLE Y LIKE X; Both X and Y is the InnoDB table, since i believe that both work on the row locking models I try to initiate 2 session First session try to INSERT all of data from X to Y like this INSERT INTO Y SELECT * FROM X; After a while (i need a long time to finish this Query), i start my second session and try to insert a data into Y What happened later make me confuse, because INSERT INTO Y VALUES (something) is just hang on, seems waiting another operation to be finished Why concurrent insert is not working? FYI, i use standar configuration from MySQL Any explanation? Thx -- 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: Can't reload from dump file
Hello. Perhaps, you haven't recreated the InnoDB tablespace after moving the database. Recreate the InnoDB table space, or follow recommendations from the error message. About InnoDB table space you can read here: http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html Chris Mason (Lists) wrote: We have had some problems with corrupt data due to running out of space recently. I wanted to repair the tables so I backed up our database by dumping to a file with mysqldump. I then tried to repair the database and had some problems with that. I stopped MySQL and moved the database to database.old and restarted the database. I wanted to import the dump file. As it is 700 MB it takes a while to load. I got this error [EMAIL PROTECTED] backups]# mysql -u root -prt3.sql Enter password: ERROR 1005 (HY000) at line 694: Can't create table './rt3/Attributes.frm' (errno: 121) and in the error file: 060129 19:27:32 InnoDB: Error: table `rt3/Attributes` already exists in InnoDB internal InnoDB: data dictionary. Have you deleted the .frm file InnoDB: and not used DROP TABLE? Have you used DROP DATABASE InnoDB: for InnoDB tables in MySQL version = 3.23.43? InnoDB: See the Restrictions section of the InnoDB manual. InnoDB: You can drop the orphaned table inside InnoDB by InnoDB: creating an InnoDB table with the same name in another InnoDB: database and copying the .frm file to the current database. InnoDB: Then MySQL thinks the table exists, and DROP TABLE will InnoDB: succeed. InnoDB: You can look for further help from InnoDB: http://dev.mysql.com/doc/mysql/en/InnoDB_troubleshooting_datadict.html What can I do? -- 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: display a hierarchic tree
Hello. This is not an exact answer on your question, however it could be interesting for you: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html Jochen Kaechelin wrote: I have the following table: mysql select * from link_categories; ++---+-+---+---+-+ | id | level | category_id | category | parent_id | deleted | ++---+-+---+---+-+ | 1 | 1 |1000 | Software | 0 | 0 | | 2 | 1 |2000 | Harware | 0 | 0 | | 3 | 2 |1001 | Virenscanner | 1000 | 0 | | 4 | 2 |1003 | Packprogramme | 1000 | 0 | | 5 | 3 |1004 | Linux | 1001 | 0 | | 6 | 3 |1005 | Windows | 1001 | 0 | | 7 | 4 |1006 | Windows XP| 1005 | 0 | | 8 | 2 |1007 | Sniffer | 1000 | 0 | | 9 | 4 |1008 | Debian Woody | 1004 | 0 | | 10 | 1 | 10 | Vermischtes | 0 | 0 | ++---+-+---+---+-+ 10 rows in set (0.24 sec) and I want to display a tree like: Software Virenscanner Linux Debian Woody Windows Windowsd XP Packprogramm Sniffer Hardware Vermischtes Can someone give me hint how to build a query? I run MySQL 4.1.x and 5.0.x and I use PHP. Thanx. -- 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: help with character sets and collation
Hello. Chris, the collation is subordinated to the character set. You should work with the character sets, and only after with collations. The data which you store in your table is silently converted to ascii character set. Are you sure that the characters which you want to store are present in ascii character set? You should change the character set of the fields of your table to that one which can hold non English characters. Another question - are you sure that the data which you're passing to MySQL is in latin1 encoding? See: http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html http://dev.mysql.com/doc/refman/5.0/en/charset-general.html How to change the character set of the fields is described at: http://dev.mysql.com/doc/refman/5.0/en/alter-table.html Chris wrote: I'm sorry but I do not know what you mean by NO_TABLE_OPTIONS in @@sql_mode). The database has a Collation = ascii_general_ci. The only other option is ascii_bin. With respect to the table, it also has Collation of the same, ascii_general_ci. There are many Collation types which the table may be change to, including several of the utf8 verity (utf8.bin, utf8.danish.ci,) but no utf8 without an extension. I tried setting the table to utf8.unicode.ci, but still encounter the INSERT error as before. -- 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: i have one doubt
Hello. set @s:=0; select Division, Units, Year from (select v1.Division, [EMAIL PROTECTED] as Units, v1.Year, @s:=v1.Units from veer v1) as v2; Veerabhadrarao Narra wrote: i have to write one query DivisionUnitsYear ameerpet 200 2004 ameerpet 300 2005 ameerpet 500 2006 From these values i want to retreive as like this DivisionUnitsYear ameerpet 200 2004 ameerpet 100 2005 ameerpet 200 2006 Means difference of the Units values by year can u give me this query. -- 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 start mysql with --old-password
Hello. See: http://dev.mysql.com/doc/refman/5.0/en/program-options.html I agree, some times it takes some time find the configuration file. show variables like '%dir%' should help you. [EMAIL PROTECTED] wrote: Dear Friends, I need to start mysql with --old-passwords but i did not know how to do so. Actually i had mysql installed with someone else. I did not know how have he installed that. He have placed an entry in /etc/rc.d/rc.local as /usr/local/mysql/bin/mysqld_safe to start mysql when server starts. Also i have no file as my.cnf . I have 3 ques: 1)Is it correct ot add that entry into this file 2)Also how to start mysqld with --old-passwords etc. 3)How to add a configuration file. Pl. do help me with the asnwers. I shall be very grateful. -- Regards Abhishek jain mail2web - Check your email from the web at http://mail2web.com/ . -- 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: UPDATE behavior
Hello. If you set a column to the value it currently has, MySQL notices this and does not update it. Perhaps it is the answer on your question. See: http://dev.mysql.com/doc/refman/5.0/en/update.html Nicolas Verhaeghe wrote: Is it normal for MySQL to not update fields that are already identical? I am talking about an INNER JOIN UPDATE, when copying from table A over table B. My count did not match at the first run and when I did the second run, I go a zero rows updated... -- 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 start mysql with --old-password
Hello. I wanted to know that is it OK to add an entry into /etc/rc.d/rc.local as /usr/local/mysql/bin/mysqld_safe . I've seen the systems where mysqld is started in a similar way. However, I usually prefer to work with properly configured mysql.server script (due its ability to do restart of the server in an elegant way :) See: http://dev.mysql.com/doc/refman/5.0/en/automatic-start.html Also now i cannot write mysqldump without the path . Does it mean that you're unable to invoke mysqldump without specifying the path to it? Check your $PATH environment variable. [EMAIL PROTECTED] wrote: Hi Gleb Paharenko , Thanks for replying to the email. Actually i have done locate my.cnf and cannot find he file. I am sure this file do not exist on mine VPS. I wanted to know that is it OK to add an entry into /etc/rc.d/rc.local as /usr/local/mysql/bin/mysqld_safe . Also now i cannot write mysqldump without the path . I mean has the installation of mysql been awry. Pl. do help me. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Access denied after upgrade to 5.0
Hello. Are you able to connect to MySQL Server if you blank the root password? Use mysql command line client from 5.0 distribution. Have you run mysql_fix_privilege_tables script? Please, provide the CREATE statement for mysql.user table.See: http://dev.mysql.com/doc/refman/5.0/en/access-denied.html M wrote: Hello, I upgraded MySQL from 4.1 to 5.0 on my machine (MDK 2006). Since I can't connect, I always get: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) (or using password:YES) I followed the reset password procedure. If the server is started with --skip-grant-tables, I can see correct entries for user root: mysql select Host, User from user where User = 'root'; +--+--+ | Host | User | +--+--+ | localhost| root | | localhost.localdomain| root | +--+--+ encoded passwords are there too. Is there anything else I could miss. What should I check, where to look? Thank you Marek -- 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: help with character sets and collation
Hello.
I do not see the CHARACTER SET of your table (usually SHOW CREATE
includes it, may be you have NO_TABLE_OPTIONS in @@sql_mode), so I
assume it is the same as database character set - ascii. Check if the
problem disappears after changing the character set of your fields to utf8.
Chris wrote:
Sorry, I am unable to work the command line. I have used this script
instead.
$sql = show variables like '%char%';
$result = mysql_query($sql) or die(Couldn't Select .mysql_error());
$count = mysql_num_rows($result);
//echo $count;
while ($row = mysql_fetch_row($result)) foreach($row as $key=$value) echo
$key=$valueBR;
echo BR;
$sql = show variables like '%collation%';
$result = mysql_query($sql) or die(Couldn't Select .mysql_error());
while ($row = mysql_fetch_row($result)) foreach($row as $key=$value) echo
$key=$valueBR;
hope this provides the appropriate info.
Thanks
Gleb Paharenko [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
Hello.
Please, execute the following statements in mysql command line and php,
and provide its output to the list:
show variables like '%char%';
0=character_set_client
1=latin1
0=character_set_connection
1=latin1
0=character_set_database
1=ascii
0=character_set_results
1=latin1
0=character_set_server
1=latin1
0=character_set_system
1=utf8
0=character_sets_dir
1=C:\Program Files\MySQL\MySQL Server 5.0\share\charsets\
show variables like '%collation%';
0=collation_connection
1=latin1_swedish_ci
0=collation_database
1=ascii_general_ci
0=collation_server
1=latin1_swedish_ci
Include the CREATE statement for your table as well.
CREATE TABLE my_table (location_id varchar(20) NOT NULL default '',name
varchar(50) NOT NULL default '',PRIMARY KEY (location_id)) TYPE=MyISAM
Chris wrote:
I think I have a problem with mysql related character sets and collation.
With language English (en-utf-8), MySQL charset UTF-8 Unicode and a MySQL
connection collation: ascii_general_ci. I can execute a sql statement in
phpmyadmin, like INSERT INTO mytable (id, name) VALUES ('5','Unterwinkel
Stra?e')
But if I try to execute the insert using a php script I get the error
1406
record too long. Using the same insert but without the ? character, the
sql
statement works. INSERT INTO mytable (id, name) VALUES ('5','Unterwinkel
StraXe')
How would I configure mysql so characters like ? can be inserted without
problems.
Thanks
chris
--
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
--
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: question about recovery with binlog
Hello. At least it replaces the contents of my test file. If you're able to provide the test case where replace utility doesn't work please provide it to the list with the contents of the file. wangxu wrote: I execute follow statement. - shellreplace @@session.sql_mode=0 @@session.sql_mode=1 -- 1.01 - But string @@session.sql_mode=0 doesn't replace to @@session.sql_mode=1. - Original Message - From: Gleb Paharenko [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, January 23, 2006 7:56 PM Subject:Re: question about recovery with binlog Hello. There a lot of different ways to perform this operation. See: http://dev.mysql.com/doc/refman/5.0/en/replace-utility.html man sed man awk wangxu wrote: How to replace it? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- 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 5.0.18: Bind on unix socket: Permission denied
Hello. Please, next time always CC your messages to the list as well. I'm not a lampp expert and can only point you to: http://dev.mysql.com/doc/refman/5.0/en/program-options.html This advice proved very helpful. This is where its at now: I noticed that the .err file belonged to user 'nobody'. *Somehwere* in the lampp scripts (or is compiled?) it forces this user. If I change into /opt/lampp/sbin and do ./mysqld -umysql it comes up! BUT... I don't know what cnf it reads and/or what else lampp needs to feed it upon startup. I text searched the entire lampp subdir, but I cant figure out where I can tell lamp to use user mysql. (Two obvious places, don't work.) If anyone knows, please advise. Thanks; -nat -- 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: Unable to connect tomcat with mysql pl help
Hello. See: http://dev.mysql.com/doc/refman/5.0/en/error-access-denied.html sankar subramanian wrote: Hi All, Iam using tomcat 5x and mysql5.x the proble is when i try to connect mysql and tomcat using j/connector 3.x tomcat throws error as access denied to [EMAIL PROTECTED] host using password 'YES'. Please help me to overcome this problem. Thanks in advance sankar - Do you Yahoo!? With a free 1 GB, there's more in store with Yahoo! Mail. -- 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: SSL Overhead
Hello. When running our benchmark tests using secure connections (all data encrypted with internal SSL support) performance was 55% slower than with unencrypted connections. See: http://dev.mysql.com/doc/refman/5.0/en/compile-and-link-options.html Khalid Hanif wrote: Hi Guys, What sort of overheads am I expecting to get when running MySQL 5.0.18 in SSL mode? I need to decide whether to run MySQL in SSL mode, or use CIPE (on RHEL 3). Thanks, Khalid -- 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: Error inserting text containing a ? character
Hello. Check that you're using the fresh enough versions of Connector/NET and MySQL. See: http://bugs.mysql.com/bug.php?id=5392 David P. Donahue wrote: This morning I began noticing some errors coming from my MySQL database that appear to be the result of a user inserting text which contains a question mark anywhere in it. The error is: Parameter '?' must be defined ... Is there a way to tell MySQL to just treat the ? as another character in the string, rather than as a parameter? Maybe have my application replace all occurrances of ? with something else that will represent a ? to the database before issuing the query? For reference, I'm using the MySQLConnector .NET for connecting my application to a MySQL 4.x database. Regards, David P. Donahue [EMAIL PROTECTED] http://www.cyber0ne.com -- 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: Difficult Problem: SQLDescribeCol call on MySQL Error
Hello. I'm not a MyODBC expert and not completely sure if it is your case, but it seems that there're several bugs similar to your. See: http://bugs.mysql.com/bug.php?id=10148 You can find more by searching on the 'SQLDescribeCol' keyword in the bugs database. Check that you're using the latest version of MyODBC. Daxin Zuo wrote: This call works fine on Oracle, Access, MS SQL. But Not works on MySQL. MySQL 5.0.15, MySQL ODBC drive 3.51, The OP is Windows 2000/2003, Program in VC++ In my SQLDescribeCol call on MySQL I get correct value on: ColumnName, BufferLength, NameLengthPtr, NullablePtr I get corect value on DataTypePtr if the type is not Text. For Text, it return -1. I get correct value on ColumnSizePtr only if the column type is varchar, and datetime , I get all 0 on DecimalDigitsPtr. The SQLRETURN always 0. Any Instruction is welcome. 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MYISAM only: Can I remove the ibdata file?
Hello. Before removing the ibd files, add skip-innodb to your configuration file, restart the server and check if your applications still work fine. Nathan Gross wrote: I recently changed all my ibd files to MYISAM. Can I safely remove the ibdata file and restart mysql? Thanks; -nat -- 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: help with character sets and collation
Hello.
Please, execute the following statements in mysql command line and php,
and provide its output to the list:
show variables like '%char%';
show variables like '%collation%';
Include the CREATE statement for your table as well.
Chris wrote:
I think I have a problem with mysql related character sets and collation.
With language English (en-utf-8), MySQL charset UTF-8 Unicode and a MySQL
connection collation: ascii_general_ci. I can execute a sql statement in
phpmyadmin, like INSERT INTO mytable (id, name) VALUES ('5','Unterwinkel
Stra�e')
But if I try to execute the insert using a php script I get the error 1406
record too long. Using the same insert but without the � character, the sql
statement works. INSERT INTO mytable (id, name) VALUES ('5','Unterwinkel
StraXe')
How would I configure mysql so characters like � can be inserted without
problems.
Thanks
chris
--
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: insert...on duplicate key update...help
Hello.
Perhaps you have forgotten to add col_name=expr to the end of your
query. See:
http://dev.mysql.com/doc/refman/5.0/en/insert.html
Jonathan Mangin wrote:
I'm trying to change a couple of replace statements to
insert...on duplicate key update (using Perl/DBI).
foreach my $key (keys %e_items) {
my $sql = insert table1
(id, date, time, uid, type, seq, value)
values
(?, ?, ?, ?, ?, ?, ?)
on duplicate key update;
my $sth = $dbh-prepare($sql);
$sth-execute($e_items{$key}-[0], $date, $e_items{$key}-[3],
$uid, $e_items{$key}-[1], $e_items{$key}-[2],
$e_items{$key}-[4]) || die $sth-errstr;
}
The manual says more is needed at the end of my sql, but
I'm not sure of the syntax. (Looks to me like all required
info is present ;) id is primary key and the only unique
index.
Thanks,
Jon
--
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: 回复： Re: MySQL 4.1 and PHP 4.4
Hello. collation. PHP 4.4 doesn't provide API to work with Connection Character Sets and Collations. PHP 5 adds You can change the character_set_xxx variables using SQL queries. I usually put 'SET NAMES' at the beginning of my scripts. 立 周 wrote: --- Gleb Paharenko [EMAIL PROTECTED]写道: Hello. From my experience PHP 4.4 works fine with MySQL 4.1. What troubles are you getting while restoring utf8 database to the server? Please, could you explain more in detail all steps of the restoring process. Include information about database character set as well. I think the problem is: the server is running mysqld with latin chracter set and latin_swidish_ci collation ( the default ). But my database is utf8 encoded ( mainly Simplified Chinese ) with utf8_general_ci collation. PHP 4.4 doesn't provide API to work with Connection Character Sets and Collations. PHP 5 adds this and you can also compile PHP 4.4 with mysqli ( MySQL Improved ) extension to bring this API to PHP 4.4. But on a shared hosting plan, i obviously have no access to any of these solutions. Lionel ___ 雅虎1G免费邮箱百分百防垃圾信 http://cn.mail.yahoo.com/ -- 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: Error 2016 and 2013
connection loss? BTW: these are the first procedures I've writting so I'm a novice with stored procedures. Accomplishing the impossible means only that the boss will add it to your regular duties. David Godsey -- 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 5.0.18: Bind on unix socket: Permission denied
Hello. Make several checks that should help. Be sure that no instances of MySQL are running on your computer and check if 5.0.18 starts successfully. Specify another location of the socket file. Temporary disable SELinux. See: http://dev.mysql.com/doc/refman/5.0/en/starting-server.html Nathan Gross wrote: Yesterday I installed the XAMPP (used to be called LAMP) stack, which includes mysql 5.0.18, on a Fedora Core 4 system. This seems like the quickest way of getting version 5 running WITHOUT clashing at all with my current install. Everything went 100% smooth, and I was able to copy my 4.x myisam based schemas over to the new directory (/opt/lampp/var/mysql), browse the data with mysql's gui clients as well as with the phpMysql console. (I do NOT bring both up simultaneously, and have disabled the autostart for the old one.) I changed the own and grp on the /opt/lampp directroy structure, with the owner now being mysql with full rights. I do not know at which point this happened but I can't bring up the thing any longer. This is what I get: 060124 14:15:44 mysqld started 060124 14:15:44 [ERROR] Can't start server : Bind on unix socket: Permission denied 060124 14:15:44 [ERROR] Do you already have another mysqld server running on socket: /opt/lampp/var/mysql/mysql.sock ? 060124 14:15:44 [ERROR] Aborting 060124 14:15:44 [Note] /opt/lampp/sbin/mysqld: Shutdown complete 060124 14:15:44 mysqld ended Your help is appreciated. -nat -- 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: synopsis of the problem (one line)
/time_zone_leap_second.MYI' (Errcode: 13) Fill help tables ERROR: 1146 Table 'mysql.help_topic' doesn't exist ERROR: 1146 Table 'mysql.help_category' doesn't exist ERROR: 1146 Table 'mysql.help_keyword' doesn't exist ERROR: 1146 Table 'mysql.help_relation' doesn't exist ERROR: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 060125 13:23:58 [ERROR] Aborting 060125 13:23:58 [Note] ./bin/mysqld: Shutdown complete WARNING: HELP FILES ARE NOT COMPLETELY INSTALLED! The HELP command might not work properly To start mysqld at boot time you have to copy support-files/mysql.server to the right place for your system PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ! To do so, start the server, then issue the following commands: ./bin/mysqladmin -u root password 'new-password' ./bin/mysqladmin -u root -h ds201.cesca.es password 'new-password' See the manual for more instructions. You can start the MySQL daemon with: cd . ; ./bin/mysqld_safe You can test the MySQL daemon with the benchmarks in the 'sql-bench' directory: cd sql-bench ; perl run-all-tests Please report any problems with the ./bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com Support MySQL by buying support/licenses at https://order.mysql.com -- 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: Database backups
Hello. Have a look here: http://dev.mysql.com/doc/refman/5.0/en/backing-up.html George Law wrote: Just a quick question regarding mysql backups. I know myisam tables are portal from machine to machine, but I know there are some differences Between innodb tables. I am running mysql 5.0.18 on suse linux 10. I have innodb set up so it stores each table in its own .idb file. I've read that innodb tables are not portable from server to server, my question is if I grab the whole mysql/data directory, can it be restored back on the same computer in the event of a crash. Do I need to enable binlog to do this? I plan on giving myself about a 1 hour maintenance window where all my import scripts skip importing and then just copying the entire mysql/data directory to a back up server where I will tar/gzip the data and push it out to a back up directory so it will get dumped to tape. -- Geo -- 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: Troubles installing MySQL5 via Darwin Ports
Hello. /opt/local/share/mysql5/mysql/mysql.server: line 234: cd: /opt/ local/libexec/mysqld: No such file or directory . • First of all, I check to see if 'mysqld' existed in /opt/local/ libexec/ and it does, so I don't know why it can't find it. Any ideas? Is /opt/local/libexec/mysqld a directory or a binary file? basedir system variable should point to the directory where MySQL Server is installed. Philip R. Thompson wrote: Hi all. Let me clarify my subject line. I *think* I actually got all the 'MySQL5 +server' files installed correctly using Darwin Ports. The problem is that I am not able to start my server. I think that 'my.cnf' may also be incorrect. Here's what's happening... - When I try to start the server --- [Claire:share/mysql5/mysql] chimi% sudo / /share/ mysql5/mysql/mysql.server start Password: /opt/local/share/mysql5/mysql/mysql.server: line 234: cd: /opt/ local/libexec/mysqld: No such file or directory Starting MySQL ERROR! Couldn't find MySQL manager or server [Claire:share/mysql5/mysql] chimi% my.cnf --- [mysqld] datadir=/opt/local/var/db/mysql5 socket=/tmp/mysql.sock port=3306 [mysql.server] user=mysql basedir=/opt/local/libexec/mysqld [client] socket=/opt/local/lib/mysql5/bin/mysql • First of all, I check to see if 'mysqld' existed in /opt/local/ libexec/ and it does, so I don't know why it can't find it. Any ideas? • Second what is wrong with my.cnf? I have searched the MySQL installation stuff and have not found anything that's useful. I have also searched archives on Darwin Ports and found some similar problems, but nothing that completely helps! Sorry for my *newbness*! Please help me! =D ~Philip -- 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 can I isolate the integer part of a varchar field and use it in an ORDER BY?
Hello. The brute force way is to create and UDF or store function which can extract the numeric part from the string and ORDER BY the results of this function. See: http://dev.mysql.com/doc/refman/5.0/en/adding-functions.html http://dev.mysql.com/doc/refman/5.0/en/stored-procedures.html http://dev.mysql.com/doc/refman/5.0/en/string-functions.html Nicolas Verhaeghe wrote: A client of mine sells motorcycle parts and the motorcycle models are for instance: YZ85 YZ125 WRF450 YZF450 Etc... If you know motorcycles, you know that the number is always the displacement in cc. What I am looking to do here is make it so that the models are sorted properly according to their displacement and not their alphanumerical order. Currently they are sorted like this: WRF450 YZ125 YZF450 YZ85 I would like them sorted like this: YZ85 YZ125 WRF450 YZF450 The displacement is not always at the end, sometimes it's at the beginning, for instance: 125SX 250EXC (Yes, those are Yamahas and KTMs, for those who are into that type of vehicles). How can I achieve this goal without creating a specific field in the database? I tried converting the field to integer, which is something that I can do with MS SQL (converting a varchar field to integer extracts the integer part, if any) but the CAST and CONVERT are not the same functions and I have looked for 30 minutes for something that could work with no success. Thanks a lot for your help! -- 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 and PHP 4.4
Hello. From my experience PHP 4.4 works fine with MySQL 4.1. What troubles are you getting while restoring utf8 database to the server? Please, could you explain more in detail all steps of the restoring process. Include information about database character set as well. 立 周 wrote: Dear list subscribers, My web hosting server runs PHP 4.4 and MySQL 4.1. But PHP 4.4 doesn't fully support MySQL 4.1. and i have problems restoring a utf8 encoded database dumped from a MySQL 4.0 server to the new 4.1 server. Do i have any chance to get it working in this setup or should i persuade the hosting provider to either upgrade to PHP 5 so i can use Connection Character Sets and Collations in PHP or downgrade to MySQL 4.0? I am really stucked in this combination of PHP and MySQL and my site has been not working correctly for more than one month now. my site is at http://www.cnads.org/, i have manually changed all collation attribute to utf8_general_ci ( at database level, table level and column level) and it still doesn't work. Lionel . ___ 雅虎1G免费邮箱百分百防垃圾信 http://cn.mail.yahoo.com/ -- 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: Storing a string value with slash(\)
Hello. If I retrive the same value with the slash (\), not able to display properly in the GUI. Do we need to use any MySQL specific functions to select such values?. If you're retrieving the same values which have stored, that this is rather a GUI problem, not MySQL. Is there any way in MySQL so that I can enter any number of slashes between the name with out escaping with another slash?. It is all depends on the way you're using to store data in MySQL. You can pass everything to mysql_real_escape_string(). See: http://dev.mysql.com/doc/refman/5.0/en/mysql-real-escape-string.html [EMAIL PROTECTED] wrote: Hi, From the front end I need to use double slashes i.e (\\) to enter one slash (\) into the MySQL database. I.e, if I enter Gelb\Paha, it stores in the mySQL as GlebPaha, If I enter S\\Greeen, it stores as S\Green in the database. Is there any way in MySQL so that I can enter any number of slashes between the name with out escaping with another slash?. If I retrive the same value with the slash (\), not able to display properly in the GUI. Do we need to use any MySQL specific functions to select such values?. Please guide me for a solution here. Thanking you in advance. Thanks, Narasimha The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- 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: Update query
Hello.
If dbA.id has the format you have specified MySQL should be able to
silently convert the type from char to int, and you can work with dbA.id
as it is integer column.
mysql create table ch(id char(6));
Query OK, 0 rows affected (0.04 sec)
mysql insert into ch set id='001234';
Query OK, 1 row affected (0.00 sec)
mysql select id+0 from ch;
+--+
| id+0 |
+--+
| 1234 |
+--+
Use something similar to:
update dbB, dbA set dbB.foo=dbA.foo, dbB.bar=dbA.bar where
dbB.id=dbA.id ;
See:
http://dev.mysql.com/doc/refman/5.0/en/update.html
Jørn Dahl-Stamnes wrote:
Assume that you have two tables (in two different databases):
table A in database dbA:
idCHAR(6)
foo int
bar int
table B in database dbB:
idINT(6)
foo int
bar int
Both tables has a several records with identical ID values, but the format is
different ('001234' vs 1234).
Is it possible to create a update query that copies the 'foo' and 'bar' from
table dbA.A to dbB.B for each record in dbB.B?
--
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: question about recovery with binlog
Hello. There a lot of different ways to perform this operation. See: http://dev.mysql.com/doc/refman/5.0/en/replace-utility.html man sed man awk wangxu wrote: How to replace it? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Installed CompleteMySQL 4.0.21 - now cant login to terminal
Hello. It is rather not MySQL problem, but the problem of your initial shell scripts. AFAIK bash usually reads .bashrc and .bash_profile. Remove the call to mysql_init from them if you have write access to you home directory. See 'man bash' for more details. Mike Stathopoulos wrote: I installed MySQL, which runs an initialize script everytime I launch the terminal, and it automaticlly logs me out, so I cant use the terminal locally. I uninstalled MySQL, now I need to get rid of the script and what calls it. I get the following when I launch the terminal: Last login: Fri Jan 20 08:50:37 from 207.200.44.110 /Library/MySQL/bin/mysql_init.command; exit Welcome to Darwin! $ /Library/MySQL/bin/mysql_init.command; exit -bash: /Library/MySQL/bin/mysql_init.command: No such file or directory logout [Process completed] Michael Stathopoulos Product Support Specialist Fieldbus Foundation 9005 Mountain Ridge Dr. Bowie Bldg. Ste. 190 Austin, TX 78759 Phone: 512-794-8890 x12 Fax: 512-794-8893 E-Mail: [EMAIL PROTECTED] -- 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: Import File / Insert Optimization Help
Hello.
I'm not sure if it suitable for you case, but sometimes it is better
to import data to the temporary table on the server and extract values
from the fields of that table.
Scott Klarenbach wrote:
I am importing records from a text file into my DB. Each record in the text
file corresponds to six tables in the DB due to normalization.
So for example, an inventory record containing a part, vendor,
contactPerson, mpn etc...in ONE line of a text file, requires 6 inserts into
the DB.
Further more, before each insert, I need to check for redundancy. So, if an
inventory line in the text file has vendor 'Scott', BEFORE I add 'Scott' to
the vendor table, I check the vendor table for whether 'Scott' exists. If
'Scott' does exist, then I just pull the ID and use that in the inventory
insert - if 'Scott' DOESN'T exist yet, I insert 'Scott' into the Vendor
Table, get the last_insert_id() and use that in the inventory table.
Each LINE in the text file can result in more than 20 Select/Insert
statements of the underlying DB before I can insert the record with all the
properly allocated foreign keys.
Considering many of these text files have thousands of lines of inventory,
as you can imagine, I have a massive performance problem. Each complete
line of the text file requires about 1 full second to validate and insert
into the underlying schema.
I'm using InnoDB tables so alot of the Insert Optimization techniques I
found from MySQL don't seem to apply too well. I'm hoping for some
experienced feedback in alternative techniques for performing this sort of
import.
Some further info:
In PHP 5, I get my connection object, and then in a loop I'm calling
$connecion-query(); for each line of the text file. I'm assuming this is
always using the same connection and that each query doesn't require a
re-connect to the DB...if it does, that's a major bottleneck that could be
avoided (with persistent connections?)
ie
$conn = mysqli_init(); //null connection object
$conn-real_connect('host', 'user', 'pass', 'db') or die('connection');
foreach($file as $line)
{
buildQuery();
$result = $conn-query($sql);
}
I hope each time I call $conn-query($sql) it's using the SAME connection
resource and not having to reconnect to the DB.
Secondly, $sql involves a call to a Stored Procedure, which in turn ends up
calling other stored procedures to faciliate all the transactions,
validations and inserts...I assume that's not too much more innefficient
than using sql insert statements directly.
Any help is appreciated.
Scott.
--
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: Can I change the password of a user on the server without interupting the user's existing connections?
Hello. Changes to global privileges and passwords take effect the next time the client connects. So, if your application doesn't reconnect you can safely change password. Another possibility is to directly modify grant tables, and perform FLUSH PRIVILEGES just before your application restarts. Jacob, Raymond A Jr wrote: I have three(3) instances of an application inserting data into a MYSQL (4.X) database. I have to change the password. I would like to know, if I can change the password of the application on the server without disrupting existing connections then modify the password in the startup file for the application. So that when the application is restarted automatically at night, the application will login to the database with the new password with a minimal disruption of service. Thank you, Raymond -- 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: assigning variable without printing result?
Hello. It is not clear for what does it mean without printing the result. Could you provide an example. If you don't want the variable to be in the column listing you can use such a technique: select * from ab where @variable:=1 and (other conditions). Do not forget about SET syntax (it doesn't produce any result set at all). Jacek Becla wrote: Hi, Can someone tell me how to assign result to a variable inside SELECT without printing the result? Thanks, Jacek -- 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 and utf8
Hello.
Have a look here:
http://dev.mysql.com/doc/refman/5.0/en/mysql-real-escape-string.html
devy wrote:
Hi list,
I'm having some problems while trying to save into a TEXT field some data
containing text and special characters from a C program!
I thought to convert the special characters with UTF8 but I don't know how
to write the SQL-query to insert data in the following table:
CREATE TABLE `books`
(
`id` int(11) NOT NULL auto_increment,
`txt` text character set latin1 NOT NULL,
PRIMARY KEY (`id`),
FULLTEXT KEY `txt` (`txt`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC
this because I should convert all characters to utf8 and I would convert
only the special one! For example I would like to do:
INSERT INTO books (txt) VALUES('exa \xC3\xAE mple');
but this will result in: exa xC3xAE mple!
I know that I can do:
INSERT INTO books (txt) VALUES(concat('exa', convert(0xC3AE using utf8)
,'mple'));
but I need a way to write this query without the use of concat (like the
first example) by converting special characters in a format understood by
mysql as UTF8 and preserving the others characters!
Is there a way?
Thanks,
Devy
--
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: convert help
Hello. ERROR 1314 (0A000): PREPARE is not allowed in stored procedures PREPARE in the stored procedures should work in the latest release (5.0.18). David Godsey wrote: Thank you. I tried this outside of the procedure and it works. However In MYSQL 5 I get: ERROR 1314 (0A000): PREPARE is not allowed in stored procedures Is there a way without needing to use prepare? Any idea why CAST(fdata AS UNSIGNED) doesn't work? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: union/collation problem, error 1267: feature or bug?
| auto_increment | +--+--+--+-+---++ 16 rows in set (0.00 sec) localhost.addresses2 3. following character-sets and collations: localhost.addresses2 show session variables like character%; +--++ | Variable_name| Value | +--++ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_results| utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--++ 7 rows in set (0.00 sec) localhost.addresses2 show session variables like collat%; +--+---+ | Variable_name| Value | +--+---+ | collation_connection | utf8_general_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +--+---+ 3 rows in set (0.00 sec) localhost.addresses2 4. following problem: when i do a union statement between the two tables, where obviousely at least in one select of the union some fields are left open by the placeholder or '' , these left-off fields are acted on with the collation_database (latin1_swedish_ci), wherease all non-left-off fields (i.e. those with a real column value in it are acted on with the collation_connection (utf8_general_ci), which then turns out in the error 1267. if i give those left off fields a _latin1 converter, everything works fine. to find out, however, if the _latin1 converter is necessary, is not easy. is there a better solution to solve this problem? straight away: is this a bug? 5. following examples: is accepted: (SELECT name,task_name,actiony,date_last_action,counter,task_counter FROM tasks2 LEFT OUTER JOIN contacts2 ON tasks2.contact_link = contacts2.counter WHERE name LIKE '%meier%' ORDER BY counter LIMIT 100) UNION (SELECT name,firm,status,'', counter,'' FROM contacts2 WHERE name LIKE '%meier%' ORDER BY counter LIMIT 100) ORDER BY counter LIMIT 100; is accepted: (SELECT name,firm,status,businesscategory,addon,date_last_action,counter,task_counter FROM tasks2 LEFT OUTER JOIN contacts2 ON tasks2.contact_link = contacts2.counter WHERE name LIKE '%meier%' ORDER BY counter LIMIT 100) UNION (SELECT name,firm,status,businesscategory,addon, '', counter,'' FROM contacts2 WHERE name LIKE '%meier%' ORDER BY counter LIMIT 100) ORDER BY counter LIMIT 100; is not accepted: (SELECT name,firm,status,businesscategory,addon,actiony,date_last_action,counter,task_counter FROM tasks2 LEFT OUTER JOIN contacts2 ON tasks2.contact_link = contacts2.counter WHERE name LIKE '%meier%' ORDER BY counter LIMIT 100) UNION (SELECT name,firm,status,businesscategory,addon, '','', counter,'' FROM contacts2 WHERE name LIKE '%meier%' ORDER BY counter LIMIT 100) ORDER BY counter LIMIT 100; ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'UNION' is accepted: (SELECT name,firm,status,businesscategory,addon,actiony,date_last_action,counter,task_counter FROM tasks2 LEFT OUTER JOIN contacts2 ON tasks2.contact_link = contacts2.counter WHERE name LIKE '%meier%' ORDER BY counter LIMIT 100) UNION (SELECT name,firm,status,businesscategory,addon, _latin1'','', counter,'' FROM contacts2 WHERE name LIKE '%meier%' ORDER BY counter LIMIT 100) ORDER BY counter LIMIT 100; 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]
Re: question about recovery with binlog
17:10:47 server id 1 end_log_pos 188 Query thread_id=25
exec_time=0 error_code=0
SET TIMESTAMP=1137661847;
INSERT INTO `ht_detail` VALUES
('79NK0001/0003','79NK0001',1),('79NK0001/0003','79NK0002',1),('79NK0001/0003','79NK0003',1);
# at 2260
#060119 17:10:47 server id 1 end_log_pos 2287 Xid = 432
COMMIT;
# at 2287
#060119 17:10:47 server id 1 end_log_pos 2399 Query thread_id=25
exec_time=0 error_code=0
SET TIMESTAMP=1137661847;
/*!4 ALTER TABLE `ht_detail` ENABLE KEYS */;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET [EMAIL PROTECTED]/;
--
- Original Message -
From: Gleb Paharenko [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Thursday, January 19, 2006 6:55 PM
Re: question about recovery with binlog
Hello.
Are you sure that the bug is thrown by mysqlbinlog? May be you're
getting this while importing the output produced by mysqlbinlog?
Have a look here:
http://bugs.mysql.com/bug.php?id=13897
Check that you're using the same versions of mysql client and mysqlbinlog.
wangxu wrote:
Mysqlbinlog throw out a error ERROR 1231 (42000) at line 10: Variable
'sql_mode' can't be set to the value of '501481487' when i recovery a
binlog.
What can i do?
--
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]
--
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: INSERT encrypted data
Hello. What data type are you using to store the result of AES_DECRYPT? Please, send the CREATE statement for your table. sharif islam wrote: On 1/19/06, Gleb Paharenko [EMAIL PROTECTED] wrote: Hello. What doesn't work? In case you want more help please, provide the results you want obtain from your query and CREATE statement for you table. Sorry for not being clear. The data is getting saved as NULL instead of being encrypted. sharif islam wrote: mysql insert into ccard values(AES_ENCRYPT(123453535,'uiwuerw'),'10/2003'); Query OK, 1 row affected (0.00 sec) mysql select * from ccard - ; +--+-+ | crypt| expire | +--+-+ | )\u\u\u\u\u\u\u | 10/2003 | | )\u\u\u\u\u\u\u | 10/2003 | | )\u\u\u\u\u\u\u | 10/2003 | | )\u\u\u\u\u\u\u | 10/2003 | +--+-+ 4 rows in set (0.00 sec) why doesn't this work? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ -- 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: Import from another db....
Hello. In case it is an 'Access denied error' see: http://dev.mysql.com/doc/refman/5.0/en/error-access-denied.html ESV Media GmbH wrote: Hi everyone, how can i import a database export from another database. I´ve got alway a permission error... I used mysqlimport mysqldump... Thanks in advance Marco Schierhorn -- 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: Best Configuratuion ( my.cnf ) for a DB with many users and largecolumns ( Images in BLOB Columns ) on a Website
Hello. A lot of depends on the type of your queries. Find out which of them make the maximum load on the server and then tune your configuration. ESV Media GmbH wrote: Hey, what do you think is the best configuration ( my.cnf ) for running a database, which many users access at the same time ( Website - Portal ). We´ve also saved our Pictures ( nearly 3.500 rows ) in our database. Every coloumn is round about 60-70 Kbyte, so i had to use mediumblob columns. There are 116 Tables and we have a primary key and an index on every table and use them in ( i hope so ;-) ) every statement. Here i´ve a my.cnf from another website. Would that be an good configuration ? Thanks in advance Marco My.cnf : # The MySQL server [mysqld] port= 3307 socket = /tmp/mysql.sock skip-locking key_buffer_size = 256M max_allowed_packet = 1M table_cache = 500 sort_buffer_size = 24M net_buffer_length = 8K read_buffer_size = 1M read_rnd_buffer_size = 256K myisam_sort_buffer_size = 24M record_buffer=1M log-slow-queries long_query_time = 3 query_cache_size = 512M max_connections = 500 join_buffer_size = 16M [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] key_buffer = 25M sort_buffer_size = 25M read_buffer = 6M write_buffer = 6M [myisamchk] key_buffer = 25M sort_buffer_size = 25M read_buffer = 6M write_buffer = 6M [mysqlhotcopy] interactive-timeout -- 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: Error from mysqldump
Hello. If you switch to the debug binary of the mysqldump, you will be able to find the query which causes 1064 error. See: http://dev.mysql.com/doc/refman/5.0/en/debugging-client.html Rhino wrote: Just as a followup to my own remarks, I've tried running my backup script with the new syntax that Gerald suggested. I was going to wait for the normal daily backup but I was eager to see if the new version would work better so I just ran it from the command line. Unfortunately, it came back with the same error. The new syntax is still cleaner and I'm going to keep it but I'm back to square one in determining why the mysqldump of this one database is giving me trouble. Does anyone have any ideas? Rhino -- 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: convert help
Hello.
You can use this technique:
drop procedure if exists test20;
DELIMITER $$
create procedure test20()
BEGIN
DECLARE fdata BLOB;
DECLARE foffset INT UNSIGNED;
DECLARE flength INT UNSIGNED;
DECLARE tmp_int BIGINT UNSIGNED;
SELECT 0xABCDEF0123456789 INTO fdata;
SELECT 14 INTO foffset;
SELECT 7 INTO flength;
SELECT SUBSTR(BINARY(fdata),
FLOOR(foffset/8)+1,
CEIL((flength + (foffset %8 ))%8))
INTO fdata;
SELECT HEX(fdata);
set @a=concat('0x',hex(fdata));
drop temporary table if exists ta;
create temporary table ta(a bigint);
set @s=concat('insert into ta set a=(',@a,'+0)');
select @s;
prepare st1 from @s;
execute st1 ;
deallocate prepare st1;
select @b:=a from ta;
select @b into tmp_int;
drop temporary table ta;
--SELECT CONVERT(fdata,BIGINT) INTO tmp_int;
SELECT HEX(tmp_int);
END
$$
DELIMITER ;
call test20();
[EMAIL PROTECTED] mysql-debug-5.0.18-linux-i686-glibc23]$ lmysql res
HEX(fdata)
CDEF012345
@s
insert into ta set a=(0xCDEF012345+0)
@b:=a
884478124869
HEX(tmp_int)
CDEF012345
David Godsey wrote:
I am trying to convert binary data to a bigint so I can do bitwise
operations on the data, and I'm having trouble doing it.
I noticed that if I have binary data and I:
select data1; I get 0 (not what I'm expecting).
Here is a test procedure I wrote:
create procedure test20 ()
BEGIN
DECLARE fdata BLOB;
DECLARE foffset INT UNSIGNED;
DECLARE flength INT UNSIGNED;
DECLARE tmp_int BIGINT UNSIGNED;
SELECT 0xABCDEF0123456789 INTO fdata;
SELECT 14 INTO foffset;
SELECT 7 INTO flength;
SELECT SUBSTR(BINARY(fdata),
FLOOR(foffset/8)+1,
CEIL((flength + (foffset %8 ))%8))
INTO fdata;
SELECT HEX(fdata);
SELECT CONVERT(fdata,BIGINT) INTO tmp_int;
SELECT HEX(tmp_int);
END
The last two selects are added to show what I would like to do, but have
not been able to get it to work.
Any help would be great. Thanks in advance.
Accomplishing the impossible means only that the boss will add it to your
regular duties.
David Godsey
Accomplishing the impossible means only that the boss will add it to your
regular duties.
David Godsey
--
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: problem with using CONSTRAINT declaration
Hello. MySQL doesn't support this yet. The CHECK clause is parsed but ignored by all storage engines. Ferindo Middleton Jr wrote: I have the following table where I have a CHECK CONSTRAINT to check for logical data values but for some reason it's not working on INSERTs to the table. MySQL doesn't give any error message when I CREATE TABLE. Any ideas what I'm doing wrong?... or Is this type of declaration not supported... What command can you issue from the command line to check the existence of CONTRAINT declarations such as this? CREATE TABLE schedules ( id SERIAL NOT NULL UNIQUE, start_date DATE NOT NULL, end_date DATE NOT NULL, start_time TIME, end_time TIME, CONSTRAINT end_date_cannot_be_before_start_date CHECK (end_date = start_date), CONSTRAINT end_time_cannot_be_before_start_time CHECK (end_time = start_time), PRIMARY KEY (class_id, start_date, end_date, start_time, end_time) ); -- 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: question about recovery with binlog
Hello. Are you sure that the bug is thrown by mysqlbinlog? May be you're getting this while importing the output produced by mysqlbinlog? Have a look here: http://bugs.mysql.com/bug.php?id=13897 Check that you're using the same versions of mysql client and mysqlbinlog. wangxu wrote: Mysqlbinlog throw out a error ERROR 1231 (42000) at line 10: Variable 'sql_mode' can't be set to the value of '501481487' when i recovery a binlog. What can i do? -- 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: problem on mysql.sock and mysql.host
Hello. Have you run mysql_install_db? See: http://dev.mysql.com/doc/refman/5.0/en/unix-post-installation.html Chen Abella wrote: i cant start mysql. error in the log says: Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist service configuration says: mysqld dead but subsys locked. i can't find any mysql.sock in /var/lib/mysql i reinstalled MySQL through yum but error is still the same. i was able to run MySQL before. i was even able to replicate. i dont know what's wrong. i tried many things in different forums but all is still the same.. please can anyone help? =( im using FC4. thanks... chEn Send instant messages to your online friends http://uk.messenger.yahoo.com -- 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: Timezone settings
Hello. See: http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html [EMAIL PROTECTED] wrote: Dear Friends, I need to do the timezone settings so that now() gives the system time.Actually first i have installed mysql on a different timezone han changed the system time zone but perhaps mysql shows the previous time zone or the default time zone only. Pl. tell me how to change that . I shall be very grateful. -- Regards Abhishek jain mail2web - Check your email from the web at http://mail2web.com/ . -- 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: 16 vs 41 byte password hashes
Hello. Most probably the reason is in old_passwords in your configuration file. You can check this with the following statement: show variables like 'old_passwords'; Gary Huntress wrote: I have a new installation of MySQL 5.0 (I did not port an old ver). I am running a Ruby on Rails application that uses this db. I have grants for [EMAIL PROTECTED], root@localhost and root@'192.168.0.63'. The passwords for these 3 grants are old style 16 byte hashes. There was one single grant for root that had a 41 byte new style hash. I thought it was redundant and deleted it. I can log in using the mysql client but my rails application can no longer log in. I'm not 100% sure that the problem is because I removed that grant but I'm fairly sure (no other configuration info has changed) My question is, why when I GRANT all on *.* to root@'localhost' identified by 'xxx' do I get a 16 byte hash and not a 41 byte hash? Since my theory is the lack of a grant with a 41 byte hash I'd like to test that. How do I create 41 byte password hashes ? Thanks, Gary -- 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: Install help on Linux: I cant obtain access
Hello. See: http://dev.mysql.com/doc/refman/5.0/en/access-denied.html Wade Smart wrote: 01182006 1627 GMT-6 Im on Ubuntu. I have mysql 4.0.24. I have phpmyadmin installed. Im a little frustrated at this point so bear with me. Mysql is running. My book says type in: mysql -h localhost testto see if the install is working. That provides me with Access denied. I then tried: mysqladmin -u root password newpassword and that failed with Access denied. I tried doing that as (computer) root and again I received that error. I just do not understand what it is that I am supposed to do. Can someone provide some light? Wade -- 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: Interesting Query Problem
Hello. Perhaps this will work (depends on the version of MySQL you're using): select question_id , count(*) from Records group by question_id having question_id not in ( select distinct question_id from Records r where member_id = @current_member_id); @current_member_id equals to current_user G G wrote: Hello, I have a simple Records table with two columns, member_id and question_id. The object of the query is to retrieve the question_id, as well as how many times it's been answered - as long as the current user hasn't answered it (member_id). So, the query shouldn't return any question_id's (and counts) if it has been answered by the current user. Right now I have this: SELECT question_id, COUNT(*) as times_answered FROM records GROUP BY question_id; I've tried throwing in different variants of 'WHERE member_id != X', but all that seems to return is the count of questions answered, minus the amount of times the particular user has answered them. For example, if user X has answered a question that had been answered another 50 times, my query will still return that question_id, but with a count of 49. Your help is appreciated in advance. Thanks! Kind Regards, Gerald Glickman G2 Innovations.com, Inc. http://www.g2innovations.com http://www.g2innovations.com/ -- 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: INSERT encrypted data
Hello. What doesn't work? In case you want more help please, provide the results you want obtain from your query and CREATE statement for you table. sharif islam wrote: mysql insert into ccard values(AES_ENCRYPT(123453535,'uiwuerw'),'10/2003'); Query OK, 1 row affected (0.00 sec) mysql select * from ccard - ; +--+-+ | crypt| expire | +--+-+ | )\u\u\u\u\u\u\u | 10/2003 | | )\u\u\u\u\u\u\u | 10/2003 | | )\u\u\u\u\u\u\u | 10/2003 | | )\u\u\u\u\u\u\u | 10/2003 | +--+-+ 4 rows in set (0.00 sec) why doesn't this work? -- 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: Migration of DB from MySQL 4.0.20 to 4.0.24
Hello. My fault - it is a typo, instead of dealer_type_name, I should have written PRIMARY KEY (`dealer_type_id`,`dealer_type_id`). I don't know how it was possible to import this table to the same version, but it is not a right syntax. Change it to PRIMARY KEY (`dealer_type_id`). cybermalandro cybermalandro wrote: My fault about not replying to the list. What is the cause of this extra line? is this a mysqldump bug in that older version of MySQL? how come I was able to export and import this DB in the same version but different platform? Any ideas? Thanks, cybm On 1/17/06, Gleb Paharenko [EMAIL PROTECTED] wrote: Hello. Sorry for such a late reply. The problem is in the duplicate line `dealer_type_name`. Remove one of these lines. Please, always send a copy of the message to the list. Most probably somebody whould have suggest you the same, if you had posted you mail to the list as well. Kuai cybermalandro cybermalandro wrote: Sorry about that -- -- Table structure for table `dealer_type` -- CREATE TABLE `dealer_type` ( `dealer_type_id` int(11) NOT NULL auto_increment, `dealer_type_type` varchar(40) NOT NULL default '', `dealer_type_name` varchar(40) NOT NULL default '', PRIMARY KEY (`dealer_type_id`,`dealer_type_id`) ) TYPE=MyISAM PACK_KEYS=1; Here it is, thanks a lot for your help. Kuai -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- 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: a question about innodb log file
Hello. Some information you can find here: http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html http://dev.mysql.com/doc/refman/5.0/en/innodb-checkpoints.html Have a look to innobase/log/log0log.c file in MySQL source distribution. wangxu wrote: There are many information about different logs in manual but innodb log file. Please tell me where can i find it? What's innodb log file different to binlog? Where can i find data about redo and undo? 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using SET time_zone for localization
Hello.
Perhaps this can resolve some queries:
http://lists.mysql.com/mysql/177314
John Lauck wrote:
I'm using SET time_zone = 'US/Hawaii' to convert timezones and it's not
working.
What am I doing wrong?
I have verified that the session.time_zone var is set correctly.
if(defined('ZMM_USER_LOCALE')) {
$query = SET time_zone = ' .
mysql_real_escape_string(ZMM_USER_LOCALE) .'; ;
$sel_result = @mysql_query($query);
}
$query = SELECT `int_resp_id`, ;
$sel_result = @mysql_query($query);
If I make the second $query = SELECT NOW() I get the correct time
adjusted values.
Does this method only work for INSERTS or when you want to adjust the
NOW() value? Not for pulling select statements out of the db?
--
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: Droping multiple tables by a pattern in the table name
Hello. My advice doesn't solve your issue, but if you had a 5.0, you would have been able to use INFORMATION_SCHEMA to retrieve the table names and assign them to variables. Then using prepared statements and variables you can dynamically drop a table. And at the end, you can put all this stuff in the stored procedure. See: http://dev.mysql.com/doc/refman/5.0/en/information-schema.html http://dev.mysql.com/doc/refman/5.0/en/sqlps.html http://dev.mysql.com/doc/refman/5.0/en/stored-procedures.html Yair Zohar wrote: Hello, I'm using mysql 4.1.14-standard. I would like to drop multiple tables in one or few queries. I don't have an easy way to predict their names (I have a way to find their names, but it's by using software and I prefer doing it with mysql). All of the table names have a fixed part which I know, and I thought of using the fixed, known part of the names to delete all of these tables at once. Can someone lead my to the way doing it with a mysql queries? I thought of using the combination of 'SHOT TABLES LIKE '%regexp%'; and DROP, but I don't know how to combine them. Thanks ahead, Yair. -- 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: --replicate-rewrite-db fails when attempting to drop a table
Hello. You should not use a cross database syntax for you queries. Instead of 'DROP TABLE `livedb`.`test_replication`' use use livedb; DROP TABLE test_replication Ian wrote: Hi List, I have been asked to setup replication between two customers servers for one particular database, which I will call in this post 'livedb'. The customer also has a copy of this database on the slave which they use for testing. This copy does not contains the same data as the master and the customer wants to leave it this way, they therefore asked if it was possible to replicate to a different database name, e.g. livedb_backup. I suggested the use of the --replicate-rewrite-db option. When I set this up and tested it I decided to create a new table and then drop it to make sure it was working ok ( if it wasn't I didn't want to taint the existing data ). I created a table called test_replication and it was promptly created on the slave, but, when I issued a DROP TABLE command the replication stopped and the follwing error was present in the .err file: 060117 15:25:22 Slave: Error 'Unknown table 'test_replication'' on query. Default database: 'livedb_backup'. Query: 'DROP TABLE `livedb`.`test_replication`', Error_code: 1051 Both servers are Windows running version 4.0.26 ( one is win2k the other win2k3 ). Am I doing something wrong or is this a bug? Here is the relevant section of my.ini on the slave: ---8- [mysqld] basedir=D:/mysql datadir=D:/mysql/data set-variable=max_connections=300 log-bin server-id=20 log-error log-slow-queries log-slave-updates log-warnings replicate-do-db=livedb_backup replicate-rewrite-db=livedb-livedb_backup ---8- Thanks Ian -- 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: relaying mysql datas
Hello. Start solving the locking issues with reading this part of the manual: http://dev.mysql.com/doc/refman/5.0/en/locking-issues.html Xor wrote: Hi, Recently i think sqlrelay is a solution for me, but now i don't know. I try to explain what I would like to do. I have written a software on win32 platform in c++. It's using mysql database and connecting to it with libmysql.dll. The task is connect two local network throught the internet using one database. (or master / slave database ?) The modifications have to come into force immediately on local networks but the syncronization between networks can be late. I have no problem on local network of course. The problem is come forward when i would like to connect to the mysql server throught internet. When i'm running querys which hit a lot of records it takes a long time to get result and while mysql serves this query the local clients is waiting too because of select read lock. (nolock is not a solution) I planed to use one database server and sqlrelay, but unfortunately i have to realize that sqlrelay is not transparent - so i can't connect to it like to a mysql server. I can't rewrite the win32 software to use sqlrelay c++ api so i can't use it? Please help if you have any idea / software to handle this situation! MySQL master/slave replication maybe a solution but i don't know what's happening when i trying to modifiy the slave database? The modification not get on the master, am i right? Thank you in advance! Best Regards Zsolt -- 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: Unknown problem with backup restore
Hello. MyISAM table of about 1.8 GB it stops restoring with no error output. It looks strange. Get the debug binary of mysql command line tool and create a trace file to find out the place where if fails. See: http://dev.mysql.com/doc/refman/5.0/en/debugging-client.html Jose Maria de Dios wrote: I am trying to restore a backup on a two processor machine with a Debian installation with 2.4 kernel and 2GB or RAM, but when it reaches a MyISAM table of about 1.8 GB it stops restoring with no error output. The MySQL version is Distrib 5.0.16. I have tried to restore it in many other systems (unstable Debian, Ubuntu, Windows 2000) and all of them worked fine. Trying to figure out if the table had problems, I used myisamchk and it returned OK. I tried to backup and restore just this table and it restored fine. I have tried almost everything, but the only way it worked is by restoring the table independently. It is not an acceptable way for backing up the database because it will grow up to a size in which it could be very hard to backup. The backup was created with mysqldump --opt ... and I try to restore it with mysql -u root -pPwd DB file.sql. Any ideas??? -- 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 5.0 error after upgrade
Hello. ALTER TABLE `filos`.`settings` MODIFY COLUMN `IntVal` INTEGER CHARACTER SET latin1 COLLATE latin1_general_ci; This seems like a bug. MySQL Administrator should not assign character set to integer columns. See: http://dev.mysql.com/doc/refman/5.0/en/bug-reports.html Kerry Frater wrote: Can someone help me. I was running v4 and just upgraded the version to v5.0. Most of my tables are MyISAM with some InnoDb. Most of the MyISAM tables are char fields but a few have integer columns. When trying to create a new table using the Administrator 1.1 program it is fine creating columns which are of type char but it fails when creating a column of type integer. The error message is ALTER TABLE `filos`.`settings` MODIFY COLUMN `IntVal` INTEGER CHARACTER SET latin1 COLLATE latin1_general_ci; Can someone advise me what has changed in the upgrade and how I can get back to using integers? kERRY -- 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: doc for administration mysql
Hello. Use the manual. See: http://dev.mysql.com/doc/refman/5.0/en/ I like MySQL (3rd Edition) by Paul Dubois as well. There a lot of other good books: http://dev.mysql.com/books/ Bayrouni wrote: Hello all, Wich are the best doc for (mysql administration) beginners Thank you -- 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: UPDATE statement causes signal 11 on 5.0.16
Hello. Please, could you explain what does it mean 'Signal 11 without fail'. Usually after receiving such a signal MySQL crashes. Of course, it should do this in normal circumstances. What is in the error log? Check if the problem still exists on official binaries of 5.0.18. Ian Sales (DBA) wrote: Hi, I'm running a 5.0.16 instance on a Debian box (2.6.13 kernel). The following statement causes a signal 11 without fail, and each time when mysqld_safe restarts the daemon, no socket file is created: UPDATE X_Products.product_details AS pd , X_Products.tblMaxProductStockDisplay AS sd SET pd.allocated = IF( pd.stock_levelsd.intMaxStockDisplay , (pd.stock_level-sd.intMaxStockDisplay) , 0 ) WHERE pd.product_uid = sd.intProductID; I can find no reference to unsupported syntax or a bug. Has anyone else had the same happen to them? - ian -- 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 driving make.
Hello. Please, could you explain more in detail what is the problem to check table dependencies? mysql command line client supports the batch mode, and you can run queries with -e option in the command line. See: http://dev.mysql.com/doc/refman/5.0/en/mysql-command-options.html Hugh Sasse wrote: I have a database backed-website in development. (It's Rails based, but for this question it probably doesn't matter.) To make sure things are clean during development I re-generate things from a script. Some things I generate depend on tables existing. Other things depend on tables being populated with data. And my testing depends on the web application having been created as well as the tables populated. Clearly the above dependency graph is asking for a Makefile. So how do I check the table dependencies from make? Searching for mysql and make mostly turns up building instructions, of course, so it's tricky to find the answer to this. This is with Mysql 4.1.x, cygwin and Solaris. Thank you Hugh -- 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]
