MySQL Forums

ndb loss data (no replies)

omar soto — Tue, 02 Dec 2008 23:00:38 +0000

i got a cluster with mysql-5.1.23 ndb-6.2.15, on SLES 10.2, and i got an error

Status: Temporary error, restart node
Message: Node lost connection to other nodes and can not form a unpartitioned cl
uster, please investigate if there are error(s) on other node(s) (Arbitration er
ror)
Error: 2305
Error data: Arbitrator decided to shutdown this node
Error object: QMGR (Line: 5024) 0x0000000a
Program: ndbd
Pid: 3251
Trace: /var/lib/mysql-cluster/ndb_4_trace.log.3

after that, i loss registered data since a week, this happened two times, the error looks like was caused by an NTP issue, but the real problem is the data lose.

SELECT on return of update (no replies)

Samantha Samantha — Tue, 02 Dec 2008 22:34:15 +0000

I need select a record choosing it on return of record count of update. I would like use a unique query. I developing in php and mysql_query not support multiple queries.
I tried many functions for example:

select * from messages where id_msg= (count(update table set record=1))

but MySQL tell me always there is a syntax error.

Thank you very much for the help
Samantha

Complex Select with Min/Max Syntax (no replies)

Dan Rinkes — Tue, 02 Dec 2008 22:19:56 +0000

Hi,

I've just recently begun using MySQL and PHP and I'm having some trouble with some syntax for a fairly complex query (to a newbie anyway). Does anyone have a suggestion? I'm using MySQL 5.0.67.

For anyone familiar with the MediaWiki database, that's where this question comes from. I have two tables, one called "revision", and one called "page". Page contains a list of all of the MediaWiki pages and revision contains a list to all edits of all of the pages. In this schema, rev_page is the foreign key in revision relating to page.page_id.

revision
--------
rev_page
rev_id
rev_timestamp
rev_user_text

page
----
page_id
page_title
page_counter

My application needs to list each page, along with the initial editor (which can be determined my using the min() function on the rev_id grouped by rev_page) along with the most recent editor (which can be determined by doing the same procedure with the max() function.

Ideally, the result would look something like this

page_id
page_title
rev_user_text (min)
rev_user_text (max)

I've been able to get either the min or the max with something like the following

select rv.rev_user_text, rv.rev_page, rv.rev_id
from revision rv,
(select max(rev_id) as maxrev, rev_page
from revision
group by rev_page) results
where (rv.rev_page = results.rev_page
and rv.rev_id = results.maxrev);

but I'm not sure how to join it with the other.

Any help on how to get the other column added would be greatly appreciated.

Thanks,
Dan

5.1 certifications? (no replies)

Per Andersson — Tue, 02 Dec 2008 21:50:49 +0000

I'm was thinking of taking the CMDEV 5.0 within a couple of weeks, but now when MySQL 5.1 is out I'm wondering if I should wait for the 5.1 certification? Or should I take the 5.0 and then upgrade to 5.1? Will there even be an upgrade option?

Regards,
Per

Issue sorted. Please ignore (no replies)

Philip Hooper — Tue, 02 Dec 2008 21:01:36 +0000

lock on select query (no replies)

john wang — Tue, 02 Dec 2008 20:25:46 +0000

I am doing some etl work, trying extract some data from source mysql database. When I am running select * from query, I want to know if there are locks on the table, what is the best way for me to look at that info,

innodb ibd file size (no replies)

Pavel Baranov — Tue, 02 Dec 2008 20:24:37 +0000

Hey guys,

I was wondering if .ibd file size ever goes down ?

I have innodb_file_per_table = 1, so there separate files for each table.

The size of one of the files is like 55G, I think i deleted like 20% of the table's content and the size did drop ...

best of...

nested-loop schema best for this? (no replies)

Hank v1rotate — Tue, 02 Dec 2008 20:23:06 +0000

Query below works great. (It selects comanyid "aaa001") and picks the best address for this company. I had to build this, since I had multiple address and some info were not on right places. But it works great. ;)

Now how do I do this to all of my compayid's I have several thousand companies and each of these have multiple addresses. So what should I create to loop this info around all of them. I like examples... ;) I read about the nested-loop schema, but not sure how to implement with this already complex query.
Thanks

SELECT
companyid,
Name,
addressid,
CONCAT(
Addr1,' ',
Addr2,' ',
Addr3) AS Address,
City,
State,
Postal_Code
FROM
((
SELECT
nameaddr.companyid,
nameaddr.Name,
nameaddr.addressid,
nameaddr.Addr1,
nameaddr.Addr2,
nameaddr.Addr3,
nameaddr.City,
nameaddr.State,
nameaddr.Postal_Code
FROM
nameaddr
WHERE
nameaddr.companyid = 'aaa01' AND
nameaddr.Addr1 RLIKE '^[0-9]'
)
UNION
(
SELECT
nameaddr.companyid,
nameaddr.Name,
nameaddr.addressid,
'',
nameaddr.Addr2,
nameaddr.Addr3,
nameaddr.City,
nameaddr.State,
nameaddr.Postal_Code
FROM
nameaddr
WHERE
nameaddr.companyid = 'aaa01' AND
nameaddr.Addr2 RLIKE '^[0-9]'
))
AS temp
ORDER BY Address DESC
LIMIT 1

SELECT from multiple tables (1 reply)

Mark Kalmus — Tue, 02 Dec 2008 20:49:53 +0000

I'm trying to formulate a query that will select multiple Product Names and their respective Product Descriptions from a Product table using a second table that lists only those Products available on that given day. Each day there will be four or five product available.

PRODUCT TABLE
______________________________________
ProdID | ProductName | ProductDesc |
______________________________________
1 | Espresso | Yum |
______________________________________
2 | Peruvian | Yum Yum |
______________________________________
3 | Cinnamon | Yum Yum Yum |
______________________________________

PROMO TABLE
_______________________________________________________________
PromoID | PromoDOW |PromoBlend | PromoVarietal | PromoFlavor |
_______________________________________________________________
1 | 0 | 1 | 2 | 3 |
_______________________________________________________________
2 | 1 | 6 | 7 | 9 |
_______________________________________________________________

Example:

SELECT product.ProductName, product.ProdDesc
FROM product, promo
WHERE promo.PromoBlend = product.ProdID
AND promo.PromoVarietal = product.ProdID
AND promo.PromoFlavor = product.ProdID
AND promoDOW = 0

I want the following result

________________________________________________________
blend | varietal | flavor |
________________________________________________________
espresso | peruvian | cinnamon |
________________________________________________________

Except I need the Product Name for the ProdID associated with PromoBlend, PromoVarietal, and PromoFlavor to be the query result for display. Is this a table design issue, or is it the SELECT statement is incomplete?

Thanks for any input.

HELP! Migrating db from 3.23 to 5.1 (3 replies)

Matt Ion — Tue, 02 Dec 2008 20:30:19 +0000

I have a php/mysql-based CMS running on an XP/Apache/MySQL 3.23 server... I'm migrating it to a W2k3S/IIS/MySQL 5.1 server. I'm getting an error right off the top trying to import the database into the new server.

I exported to an .sql file via the board's "Backup Database" function and am now trying to reimport it through HeidiSQL, but I get the message, "SQL Error: Invalid default value for 'authorid'". The query it's choking on is right at the start of the .sql file:

-----------------
CREATE TABLE ezauthors (
authorid int(11) NOT NULL DEFAULT '' AUTO_INCREMENT,
login varchar(20) NOT NULL DEFAULT '',
userpassword varchar(32) NOT NULL DEFAULT '',
authorname varchar(50) DEFAULT NULL,
authoremail varchar(255) DEFAULT NULL,
regdate datetime DEFAULT NULL,
usergroup varchar(32) NOT NULL DEFAULT '',
countrycode char(2) DEFAULT NULL,
language char(2) DEFAULT NULL,
phone varchar(20) DEFAULT NULL,
fax varchar(20) DEFAULT NULL,
address varchar(100) DEFAULT NULL,
city varchar(50) DEFAULT NULL,
state varchar(50) DEFAULT NULL,
zip varchar(20) DEFAULT NULL,
website varchar(255) DEFAULT NULL,
comments text DEFAULT NULL,
newsletter char(1) DEFAULT 'N',
privateemail char(1) DEFAULT 'N',
disuser char(1) DEFAULT 'N',
PRIMARY KEY (authorid),
UNIQUE KEY login (login)
) TYPE=MyISAM;
-----------------

Looking ahead, it appears I'm going to run into this on a number of tables...

AUTOINCREMENT with LOAD DATA INFILE (4 replies)

Rich Pallotta — Tue, 02 Dec 2008 22:19:34 +0000

I have a 2-column table with a primary key that I want to auto-increment when I load a data file, but it's generating an error for me.

CREATE TABLE MySample(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
data VARCHAR(100)
);

My data file is 1-column and I thought the point of the auto-increment was to automatically assign and number an id upon data load.

mysql> LOAD DATA INFILE 'C:\\_temp\\Fruit.txt' INTO TABLE MySample;

It BEEPS and gives me this error:

' for column 'id' at row 1ect integer value: 'Apples
mysql>

Apples is the first row of my data file.

If I insert an ID column into the data file and manually increment the values starting at 1 (using Excel), it loads fine. If I do INSERT VALUES from the command line, it also loads and auto-increments as I would have expected. Any suggestions?

MySQL/PHP Image display (1 reply)

Ashish Vohra — Tue, 02 Dec 2008 18:31:04 +0000

Hello MySQL/PHP Experts

I this piece of code and I can not seem to figure out why my images are not being displayed. I have the IMAGEURL link stored in my MySQL database. I have the FULL link stored in the database and it still will not display the image. Here is my short code:

// Make a MySQL Connection
mysql_connect("IPADDRESS","databaseID","password") or die(mysql_error());
mysql_select_db("dbname") or die(mysql_error());

// Get all the data from the "example" table
$result = mysql_query("SELECT * FROM producttable")
or die(mysql_error());

echo "";
echo "";
// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result )) {
// Print out the contents of each row into a table
echo "";
}

echo "

Product ID	IMAGE PICTURE	Product Name
"; echo $row['ProductID']; echo "	"; echo "' />"; echo "	"; echo $row['ProductName']; echo "

";
?>

Can someone help explain to me what I am doing wrong?

Thank you in advance!

Regards,

Ashish

IF EXISTS problem (4 replies)

G C — Tue, 02 Dec 2008 20:26:05 +0000

Ok I created a table by doing the following:

CREATE TABLE Location (
IPAddress VARCHAR (15) primary key,
Latitude VARCHAR (25),
Longitude VARCHAR (25));

Now Im trying to use then following to either UPDATE if there is a matching IPAddress value, or INSERT one if there isn't:

IF EXISTS (SELECT * FROM Location WHERE IPAddress = '192.0.0.1') THEN
UPDATE Location
SET Latitude = '53', Longitude = '122'
WHERE IPAddress = '192.0.0.1';
ELSE
INSERT INTO Location
VALUES ('192.0.0.1', '53', '122');
END IF;

This doesn't work however and i get the following error message:

"ERROR 1064 (42000): You have an error in your SQL syntax...... at line 1.

Any help on why this doesn't work?

Cheers.

replication may break (no replies)

Ann Kok — Tue, 02 Dec 2008 17:47:08 +0000

Hi

I got the warning but not sure how I can fix

Thank you

[Warning] No argument was provided to --log-bin, and --log-bin-index was not used; so replication may break when this MySQL server acts as a master and has his hostname changed!! Please use '--log-bin=/var/run/mysqld/mysqld-bin' to avoid this problem.

Implicit JOIN precedence switch ? (1 reply)

Russell King — Tue, 02 Dec 2008 19:45:59 +0000

Hi,

I know the precedence of implicit JOINs (comma joins) has been downgraded
since version 5.x, however I need to know if there is a switch to revert
this behavior to that used in previous versions.

We maintain a legacy application of around 500k lines of code. Many queries are
built programmatically, so changing the affected queries is extremely complex
and will require many months of QA to ensure they're working correctly.

Ideally, I'm hoping for a switch like HIGH_NOT_PRECEDENCE.

Failing that, my understanding is that the new precedence will only affect queries containing LEFT, RIGHT, OUTER JOINs, and not pure INNER JOIN queries. Have I understood this correctly ? If that's the case it will save us a lot of time.

Many thanks,

Russ.

InnoDB error (no replies)

Kray Mitchell — Tue, 02 Dec 2008 17:22:05 +0000

Hello All,
I am pretty new to Windows Servers (Mac guy, have run 2 Xserves for Web and Email though) and I seem to be having an issue with getting 5.1 running on our Windows Server 2003.

Ran the package installer and everything went OK, but we are unable to get the service to start or login via MySQL Administrator.

When I attempt to launch the MySQL service, I get this in the Event Viewer:
ERROR: Plugin 'InnoDB' init function returned error.
ERROR: Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
ERROR: Unknown/unsupported table type: INNODB
ERROR: Aborting
Information: C:\mysqlPATH\bin\mysqld: Shutdown complete

When I attempt to login via MySQL Administrator, I get this error:
Could not connect to the specified instance.

MySQL Error Number 2003
Can't connect to MySQL server on 'localhost' (10061)

If you want to check the network connection, please click the Ping button.

I have also received an error about incorrect user/pass in MySQL Admin.

We only ever use 2 passwords on this server, and neither of them work. We have attempted to reset the password using the mysql-init.txt and it appears to work (We don't get any errors or anything) but we still can't login.

I can't seem to find any info on completely removing MySQL from the server so we can literally start from scratch (Without reformatting the server).

Can anybody assist with this?? Thanks.

When I try and login to MySQL Administrator, I get this error:

Partitioning problem (no replies)

martin mouterde — Tue, 02 Dec 2008 16:21:04 +0000

Hello.

I get a mysql backup that I try to restore.
Some errors happen and I do not know why, and consequenlty how to solve my problem !

Here is an instruction from the infoschema file which produce an error :

CREATE TABLE `maTable` (
`NumberId` int(11) NOT NULL AUTO_INCREMENT,
`StateOfResource` BINARY(1) DEFAULT NULL,
`destinationNumber` BINARY(10) NOT NULL,
PRIMARY KEY USING HASH (`NumberId`),
UNIQUE KEY `MyDestinationNumber` USING HASH (`destinationNumber`)
) ENGINE=ndbcluster DEFAULT CHARSET=latin1 COLLATE=latin1_bin
PARTITION BY KEY (NumberId) ;

Here is what I get :
ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function

thanks

PS : I use Mysql cluster 5.1.30 under fedora. My backup come from mysql cluster 5.2.2.

Delete ibdata1 (no replies)

Steven Mills — Tue, 02 Dec 2008 15:59:52 +0000

Our database originally was created and used without the individual file per table option for innodb. Since switching to the one file per table option ibdata has grown from 17Gb to 22Gb in several months. So something is still using that file, but has remained at 22Gb for several weeks.

How safe is it to remove the file? Will it be re-created?

Can I rename the file to see if the db still functions and if so then remove?

How can I be certain its no longer in use to remove?

Thank you,

-Steve

Help a newbie write a trigger -> delete all but 10 latest records (1 reply)

Jan Jansen — Tue, 02 Dec 2008 16:54:24 +0000

Hi,

Im trying to write a trigger that will delete all but the 10 latest records for a given (non unique) key.

I have a 'report_history' table with:
id (PK), room, cupboard, shelve, report_date

The trigger is .. triggered by an insert on the table (or alternatively, before an update on the original table, but that part I figured out).,

If more than 10 records exist with the same location index (=room + cupboard + shelve) then the oldest one should be deleted (or updated with the new record). If less than 10 records for that location exist, it should be inserted as normal.

I can't figure this out :/

Now I would usually do this in PHP easily but in this case, i can't. It has be done by a trigger in MySQL, so any help would be greatly appreciated!

MySQLMembershipProvider (v 5.2.5) does not handle encrypted passwords (no replies)

Simon Moscrop — Tue, 02 Dec 2008 15:54:14 +0000

Hi,

I am trying to use MySql Connector .NET with mono to build an ASP .NET application and have discovered that the code will not function correctly with encrypted passwords.

The problem is that the EncodePassword routine uses a 'passwordKey'(*) which, in this implementation, adds on some extra 'salt' to the password *AFTER* it has been encoded and then stores that in the database.

The effect of this is that calling EncodePassword on the string 'Fred' will lead UnEncodePassword to return something like '"藍坹顺﫴郖䘛fred".

HOW TO FIX::
============
To fix this the 'salt' should be added to the array of bytes BEFORE calling encode password. Also the salt should not need to be stored as a 'PasswordKey'.. this actually weakens your security. Some magic happens inside EncryptPassword that knows to treat the leading bytes as random nonsense.

If i get around to it i'll post some code here to fix the problem.... just waiting rebuilding my mono development PC at the moment so it might be some time

Cheers

Mozzy

*( This is not really a password key at all, but, instead some 'salt' which is added to the password - the real key is set in the machine.config)

mysql replication (no replies)

Ann Kok — Tue, 02 Dec 2008 15:18:44 +0000

Hi

I setup the mysql replication.

I did test, it won't work
A table exists in the master, but I haven't copied to the slave.

I deleted this table in the master, I see "show slave status" that the table couldn't find

Then I create another table, that table is also not showing in the slave also.

I have to fix after I delete all binary log and restart the mysql and copy this table to slave

Then I create another table in master, the slave can see and works fine

My question: ls an error happening in replication? this feature won't work anymore.

Thank you

group by and sum conditionals (1 reply)

Marcelo Barbudas — Tue, 02 Dec 2008 20:01:57 +0000

Hi,

I have two tables: users and payments. Commissions can have multiple statuses(rejected, accepted etc)

How do I create a query that outputs something like:
all_the_user_table_fields, payments_accepted, payments_suspended etc.

I thought something like select * from users, JOIN payments, group by users.id.

However I don't know how to say *, sum(amount) as payments_accepted where payments.status = 'accepted', sum(amount) as payments_suspended where payments.status = 'suspended', etc.

.NET Connector and Replication (no replies)

Armin Lorenz — Tue, 02 Dec 2008 14:21:41 +0000

Hello,

DDL and DML comands are not stored in the binlog files and not replicated if i am using the .NET connector. With ODBC or the C-API everything is fine.
I am using .NET connector 5.5.5.0 (have tried others to) on an XP Client against MYSQL 4.1.22 on Windows 2003 Server.
Are there any options disable writing to the binlog in the .NET connector?

Thanks alot and best regards

Armin

myisamchk fails with myisam_sort_buffer_size is too small (no replies)

Mark Carrington — Tue, 02 Dec 2008 14:10:18 +0000

I am trying to optimize my databases by using myisamchk to sort the index and data for selected tables. On most of the tables I have done this on so far, it has worked well and I've seen large performance improvements. However, on one particular (quite large) table, I'm getting this error:

myisamchk.exe --sort-index --sort-records=3 .MYI
- Sorting records for MyISAM-table '

.MYI'
Data records: 39739756 Deleted: 0
- recovering (with sort) MyISAM-table '

.MYI'
Data records: 39739756
- Fixing index 1
myisamchk: error: myisam_sort_buffer_size is too small
MyISAM-table '

.MYI' is not fixed because of errors
Try fixing it by using the --safe-recover (-o), the --force (-f) option or by not using the --quick (-q) flag

The myisam_sort_buffer_size was set to 205M. I've tried increasing it to the maximum 4G and re-running the command after restoring the table from a backup, but still get the same error.

My understanding was that this buffer was used in preference to the key cache for sorting, but if it was not large enough then the key cache would be used instead rather than the command failing entirely.

Is this a case of a misleading error message, or is it not possible to use this command on a table of this size (6 GB data, 860 MB index)?

Creating function gives error (1 reply)

mukesh hirve — Tue, 02 Dec 2008 19:46:36 +0000

create function genregno(p2 INT)
returns INTEGER
Begin
select convert(substr(max(regid),2,7),decimal) into p2 from university
set p2 = p2 + 1
return p2
end;

it gives error

Error Code : 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 'set p2 = p2 + 1
return p2
end' at line 2
(0 ms taken)

any helps appreciates

Missing mysql.sock file (no replies)

Natalie Zhuravleva — Tue, 02 Dec 2008 13:55:13 +0000

I tried to install mysql community server on my MacBookPro (OS X 10.5) but it is saying that its missing mysql.sock file.

Different forums say it should be in "/etc" folder or "/var" or "usr/local" folders but I dont have them on my computer and when I search for mysql.sock file, nothing comes up. What can I do?

Notes: I can connect to mysql through the terminal but this is happening when I try to install drupal on my machine.

how to implement paging system in mysql (no replies)

Dheeraj Gupta — Tue, 02 Dec 2008 13:14:36 +0000

hi Everyone,
I want to create paging system through database, if some one have any idea tell me.
i mean i fetch 10 records per page but when i fire next event it will next 10 record
so pls help me.

pls give me reply.
thanks.

"Host '::ffff:<IP address>' is not allowed to connect to this MySQL server" (1 reply)

Amol Gaikwad — Tue, 02 Dec 2008 14:15:19 +0000

Hello,

I have 2 MySQL server installations (MySQL 5.1 & MySQL 6.0.7) on UNIX SUN Solaris machine. MySQL 5.1 is configured on port 3306 and MySQL 6.0.7 is configured on port 3307.

I am able to connect to MySQL 5.1 server from a remote Windows machine without any problem. But when I try to connect to MySQl 6.0.7 server, I get the error as
"Host '::ffff:' is not allowed to connect to this MySQL server"

Locally I can connect without any glitch. But it is failing to connect using hostname, which is required as to connect from windows machine using MySQL GUI (TOAD).

Also, all the grants for remote access have been granted using the below statement
GRANT ALL PRIVILEGES ON *.* TO root@'sun-mysql-test' IDENTIFIED BY 'test' WITH GRANT OPTION;

Is there any other configuration changes which are needed for granting remote access to MySQL 6.0.7 server? Please let me know

UDT on new versions of MySQL? (no replies)

Jonathan Dias — Tue, 02 Dec 2008 12:48:44 +0000

Hi,

I looked into MySQL documentation and searched over google, but couldn't find someone saying that there are UDT(user defined type) on MySQL... but as there are new versions coming around, I would like to know if this have changed.

I kinda need them and I will have to use mssql untill mysql creates it(if they ever will).
I like mssql but it lacks tools... simple tools like mysql does and well... everything from ms is limited to what they think and I like to use my imagination as I want, thats why I am into MySQL, but can only migrate it when it have UDTs.

are there UDT in these new MySql versions? is it planned in the near future?
Thanks!

Joe

MySql Connector/Net 5.1.6 backward compatiblity (no replies)

Robin Massart — Tue, 02 Dec 2008 12:20:24 +0000

I have developed a number of ASP.Net web apps using the MySql Connector/Net 5.0.5 and our server has that version installed on it.

Our newer products use MySql Connector/Net 5.1.6 and is running on the same server. When I try to run the app on the server I get the following message:

"Could not load file or assembly 'MySql.Data, Version=5.1.6.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d' or one of its dependencies. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)"

I understand that this means I need to install version 5.1.6. However, when I try to install it, it tells me to uninstall the previous version. If I do that, will the older apps be able to run with the new version of the connector without needing changes. ie is is 5.1.6 backward compatible with 5.0.5. Or is there another way of installing both on the same machine.

Thanks for your help,
Robin