Getting a “more than one row returned” error when running a SQL.

Asked By 230 points N/A Posted on -
qa-featured

 

I am using a EJB project to access my MySQL database.  I keep getting an error, "more than one row returned" when I run a select * from budget table.

 

SHARE
Best Answer by Stella
Answered By 0 points N/A #98021

Getting a “more than one row returned” error when running a SQL.

qa-featured

The error message states that the data provider returned more than one row when it should not.  This normally will happen if the relationship defined in the entity beans does not correspond to the data that is stored in the underlying database. You may need to change the relationship or check your data.

Answered By 230 points N/A #98022

Getting a “more than one row returned” error when running a SQL.

qa-featured

I do not understand. Can you explain this to me ?

Answered By 0 points N/A #98023

Getting a “more than one row returned” error when running a SQL.

qa-featured

Assuming you have a "OneToOne" relationship between your Budget table and Budget Schedule table. (Example only).

The Budget Schedule table may be having more than one reference of the Budget in its data or vice versa.

OneToOne implies a row created in one table uniquely matches one row on the second table.

—————————————————————–

@Entity

class Budget {

@OneToOne

Private budgetSchedule = null

}

When the above code is deployed, a foreign key is created in the Budget Table. The relationship states that the Budget Schedule reference is unique and should not contain in more than one Budget row line.

In event 2 Budget rows are found holding the same reference to the Budget Schedule, you will get an error!

Answered By 230 points N/A #98024

Getting a “more than one row returned” error when running a SQL.

qa-featured

Thank you Stella!

I manually checked the rows of the table and yes, I can see that there are duplicate references in the data!

How could this happen if the relationship is defined as OneToOne in the first place ? Does not EJB apply referential integrity when rows are added ?

Answered By 0 points N/A #98025

Getting a “more than one row returned” error when running a SQL.

qa-featured

What I think that could have happened is that the programmer must have changed the relationship in the source code and had forgotten to clean up the data.

When you modify the relationship in the source code AFTER the table has got created, EJB will only apply the rules for NEW data and not for existing data.

That must be the error you are experiencing now when you try to retrieve data that was added when the constraint was missing or was previously defined as OneToMany.

Answered By 230 points N/A #98026

Getting a “more than one row returned” error when running a SQL.

qa-featured

How can I make sure of what you say ? Is there a method to verify this behavior ?

Best Answer
Best Answer
Answered By 0 points N/A #98027

Getting a “more than one row returned” error when running a SQL.

qa-featured

What I could suggest is that you drop the tables and deploy your EJB project. Then verify by adding new data.

Or you can delete the old records from the last duplicated entry. 

Don't forget to backup your database before this!

You should not be getting this problem again!

Answered By 230 points N/A #98028

Getting a “more than one row returned” error when running a SQL.

qa-featured

Ouch! Dropping is a drastic action 🙂

I will try the delete option and let you know the outcome!

Answered By 230 points N/A #98029

Getting a “more than one row returned” error when running a SQL.

qa-featured

Hi Stella, just to give you an update. I deleted the old data and tried a few rounds. It now works perfectly without any errors.

I checked the data and all rows are unique as well.

Thank you for helping me out !

Answered By 0 points N/A #98030

Getting a “more than one row returned” error when running a SQL.

qa-featured

Glad to be of Help! Have a nice day!

Login/Register to Answer

Related Questions