Re: copy blob between tables may get in consistent data?
See Thread at: http://www.techienuggets.com/Detail?tx=78262 Posted on behalf of a User I have no problems in copying MEDIUMBLOB type data from one table to another. Under either 5.0 or 5.1 community editions. In Response To: hi, I found this: http://bugs.mysql.com/bug.php?id=34300 in the bug system. As it descibed, there is chance which I get corrupted data when read BLOB. Now I wonder why I also get corrupted data when write BLOB into table... I tried this: step 1: read BLOB data from TABLE1 step 2: log the BLOB data step 3: write the data into TABLE2 result: select hex(data) from TABLE1 is the same as what I've logged in step2 select hex(data) from TABLE2 is different with select hex(data) from TABLE2 Any suggestions? 2009/3/19 Cui Shijun : > hi, > Â I'm using mysql-5.1.22, innodb. For some reason, I need to add some > columns to a table without locking it, so I do this: > > Â Â insert into TABLE2(col1, col2, ... ,colN) select col1, col2, ... > colN from TABLE1 where ...(primary key traversal condition). > > Â which TABLE1 is under use, and TABLE2 has new structure. > Â Before I really do the copy task, I created some triggers to > synchronize the insert & update. > > Â When I finally complete the task, I find ALL blob column appear to > result in inconsistent status with the orignal table. > Â There are users which do query and update on TABLE1. > Â So what's going wrong? > > Â Thank you > Â Â Â Cui Shijun > -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Upgrading MySQL from 5.0 to 5.1
See Thread at: http://www.techienuggets.com/Detail?tx=78654 Posted on behalf of a User This has proved to be quite challenging. I wasn't able to do the upgrade and basically created another instance of 5.1 and took a backup of my database from 5.0 and restored it to 5.1. Then added all my users, etc. There's got to be a better way? I couldn't figure out how to get the new version 5.1 to see the file system (containing the database) that was in use by 5.0. I looked at all the documents and nowhere (or at least I couldn't see it) does it talk about upgrading the database. When you install the new version how do you get it ot upgrade and use the 5.0 database? Any pointers/help on this would be great for the next time I have to do this. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Error -1
I'm running 5.1.31-community on WinXP.
Im getting
ERROR 1030 (HY000): Got error -1 from storage engine
from the following query:
SELECT SQL_CALC_FOUND_ROWS customers.*, account.account_name,
account.real_name AS `Sales Rep`, stage.stage_name, memos.note_text AS
m_note_text
FROM customers INNER JOIN account ON account.account_id =
customers.account_id
INNER JOIN stage ON customers.stage_id = stage.stage_id
LEFT JOIN memos ON customers.customer_id = memos.customer_id AND
(memos.note_id = (SELECT note_id FROM memos WHERE
memos.customer_id=customers.customer_id
AND (MATCH(memos.note_text) AGAINST ("frog"))
ORDER BY note_timestamp DESC LIMIT 1) OR memos.note_id IS NULL) LIMIT 0,25;
If I take out the MATCH term, the query works just fine.
SELECT SQL_CALC_FOUND_ROWS customers.*, account.account_name,
account.real_name AS `Sales Rep`, stage.stage_name, memos.note_text AS
m_note_text
FROM customers INNER JOIN account ON account.account_id =
customers.account_id
INNER JOIN stage ON customers.stage_id = stage.stage_id
LEFT JOIN memos ON customers.customer_id = memos.customer_id AND
(memos.note_id = (SELECT note_id FROM memos WHERE
memos.customer_id=customers.customer_id
ORDER BY note_timestamp DESC LIMIT 1) OR memos.note_id IS NULL) LIMIT 0,25;
The error -1 isn't very helpful, does anyone have a clue? Perhaps I can't
use MATCH in a JOIN condition?
Regards,
Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
www.the-infoshop.com
www.giiexpress.com
www.etudes-marche.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
is this a bug?
In mysql embedded server 6.09 built from source for Linux 2.6.27.11 kernel. First create a connection (mysql_real_connect) then close the connection in a different thread. This causes a SIGSEGV crash in THD:store_globals ( ). My mysql embedded library is thread-safe. Thanks in advance, -Alex
Re: New to MySQL
Manish, Manish Gupta wrote: > Hi all, > I am Manish from India and i am new to mysql. > > These are the steps that i had taken: > > 1. Installed MySQL 4.0.20d-Winit came with a cd in a book i bought. > 2. Executed the winmysqladmin file from mysql/bin. gave it a username and > password. > 3. went to command prompt and the troubles started. > > Problem 1: when i type show databases, it only gives me one data base: test. > i read that it shud give me 2, test and sql. > > Problem 2: when i want to create database is shows: > mysql>create database music; > "ERROR 1044: access denied for user: '@localhost' to database 'music' > > can someone please help me. do yourself a favor: Do not touch version 4.0.20, just drop it and get something current. MySQL 4.0 is past its end-of-life, you will not get any upgrades (within the 4.0 series) for it. Investing any effort in it is waste, you should immediately get a current version and use it. The candidates are: - Of the 5.0 series: version 5.0.75 - Of the 5.1 series: version 5.1.31 or 5.1.32 All these are available as free downloads via http://dev.mysql.com/downloads/mysql/ As I don't know your book, I can't tell which 4.0-specific instructions it contains that need to be adapted to 5.0 or 5.1, but there will be some. Nonetheless, it should be much easier to use general instructions for 5.0 or 5.1 together with your book, than now do anything based on 4.0 just to realize you then want to upgrade. Also, bear in mind that 5.0 and 5.1 have not only lots of functional improvements over 4.0 but also fixes for several security-related bugs, you do not want to run MySQL without these. Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
New to MySQL
Hi all, I am Manish from India and i am new to mysql. These are the steps that i had taken: 1. Installed MySQL 4.0.20d-Winit came with a cd in a book i bought. 2. Executed the winmysqladmin file from mysql/bin. gave it a username and password. 3. went to command prompt and the troubles started. Problem 1: when i type show databases, it only gives me one data base: test. i read that it shud give me 2, test and sql. Problem 2: when i want to create database is shows: mysql>create database music; "ERROR 1044: access denied for user: '@localhost' to database 'music' can someone please help me. thanx
Re: MySQL General Discussion question
Hi Richard, all! Richard Gagnon wrote: > Sorting a varchar field alphabetically with correct numerical order help > needed > > > > I have a varchar 50 field that contains product names, which are typically > numerical, alphabetical and punctuation thrown in. I would like to have them > returned in some sort of order that is roughly alphabetical, but with the > numbers in numerical order. The basic Order By clause does not do it > correctly. > > [[...]] > > > In the above, the #2 JCAL should be second, otherwise, list is correct. I > could also live with the values beginning with # or any alpha character > coming after the numerical ones, but the 1, 11, 2, order is the issue. This is a perfect example why you have to differ between string and numeric data types when declaring your column. > > Does anyone have any idea how to do this? I have been playing around with > various suggested ways, including casting and converting, but so far have > not been able to solve this. Any ideas would be greatly appreciated. There is no standard way (in SQL) to do the sorting you want based just on those strings, so I can think of three choices: 1) Don't let the database do the sorting but do it in the application. It will still be some effort to do this (code the routine), also it will require you to read all (qualifying) data from the DB into your app before it starts sorting and can return the first row, so you get latency (time for data transfer!). 2) Modify your product names so that string sorting is possible. This could be done by inserting leading zeros at the front of the numbers to bring them all to the same fixed length, then ORDER BY will ensure the sequence 001, 002, 011. The obvious drawback is that it would modify the product names which get returned and displayed. 3) Split your product names into a numeric part and a string part. You could add two columns (one numeric, one string) to your table which take these parts and then do ORDER BY on these columns, not on the original name. You need never return or use these columns externally, they would just be set on INSERT and maintained on UPDATE. The tricky part will be to extract these parts from the product name, but it should be possible to do that with a sensible result. (It would even allow you to order by Roman numerals if your extract routine is good enough ...) IMO, introducing the additional columns is the safest approach: 1) Sorting in the application prevents you from using any standard application (say, a report generator) and must be duplicated in all applications using the data. 2) Modifying the data is not the proper thing to do. 3) Maintaining the separate columns might even be automated (triggers on INSERT and UPDATE), and the ORDER BY can be used in all applications. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: why is this happening?
No, there's only one mysqld process running. Thanks, tho. On Thu, Mar 19, 2009 at 3:56 PM, Claudio Nanni wrote: > I am afraid you have two mysql instances up on the same host. > > ps -ef | grep mysql ? > > > Claudio > > > Jim Lyons wrote: > >> I have a rather odd problem with a replication slave. >> >> The slave has been down for a number of hours. Of course, both io and sql >> threads are stopped - from SHOW SLAVE STATUS: >> >> Slave_IO_Running: No >> Slave_SQL_Running: No >> >> >> However, the relay logs are still being written to. I was under the >> impression that the slave's io thread was what brought data from the >> master's bin log to the slave's relay log. With the io thread stopped, >> the >> relay logs should stop filling up - right? Mine are definitely filling >> up. >> >> >> Does anyonee know why the relay logs keep filling up after replication has >> broken? >> >> Thanks >> >> >> > > -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Upgrading and screwed up
Trying to upgrade from 5.0.37 to 5.1.32, These are the steps I have taken: 1. Took a dump of the our production database using mysqldump. 2. Downloaded the binary version for i86 Linux and placed it on a Slackware 12 server. 3. Foolishly ran mysql_upgrade against the data (/storage/data/mysql... these are all Inodb) thinking I had started mysqld for the new version. However, it was probably the prior version (5.0.37.) 4. Moved the data directory (/storage/data) to .bak (/storage/data.bak). 5. Unpacked the .gz and changed the linked directory to the new installed directory. 6. Ran the install script (scripts/mysql_install_db --user=root). Yes, I know this is out of sequence, no excuses. 7. Realized I could not make my time deadline for the install, so I started reverting to the previous setup... moved the new data directory to data.new, moved the data.bak to data, moved the new link to mysql.new and the old link (now named mysql.old) back to mysql. 8. Started mysqld (really mysqld_safe) under 5.0.37 and it looked good (I could see the data tables, etc.) When I attempted to start our app, the system complained that it a stored procedure did not exist. Oh-oh. At this point, I can edit the stored procedure (there are a couple of hundred that behave the same way) but not save it. I can see that the stored procedure is in the mysql proc table and it is complete (when I bring the stored procedure up in Navicat, it is missing the paramters which indicates to me that Navicat is using something it has stored because I can see the praramters when I look in the table.) To summarize the problem, mysql does not seem to be able to find/see the stored procedures. Anyone have any ideas? TIA, Carl
