I have gone through posts here on error 1242 (sql subquery -> more than one row -> unable to save) while saving a newly added product. Most of them seem to have been resolved after finding duplicate quey entries in url_alias table. But I am getting a peculiar version of this error:
I am adding & saving a new product (say product_id 800). I get this error. Then I click edit & save again. The error on this product goes away. But the error message appears for some random previously saved product (product_id=800-x). I resolve the errors one by one & after some number of errors, this stops & the website loads without error. I heave a sigh of relief. But, alas, the error starts all over again, when I add another new product.
I have checked my url_alias table again & again - no duplicate query entries for either category or product (as most of the posts here seem to have diagnosed). No duplicates in "product" or "category" tables either!
I am using OC 1.5.5.1.
I removed two extensions - Auto URL & Ultimate SEO Package, suspecting that these may be causing the problem (as some fora discussions suggest. But no use.
There seems to be some serious bug in product saving. I request the experts to address this or offer suggestions to fix the problem. There seem to be too many people having this problem.
The error seems prevalent since midway through last December.
Where it arises in relation to products and an extension at
http://forum.opencart.com/viewtopic.php ... &start=340
the advice was "fix your DIR_SYSTEM"
but if you read the entire page below that you will see how wonderful one leading or at least popular host's support was.
Phrase "error no 1242" leads to "About 828 results (0.52 seconds)" at
https://www.google.com/search?q=%22erro ... encart.com
where the ad at the bottom
http://www.wiki-errors.com/err.php?wiki=1242
probably should be prevented as it is likely hazardous to inexperienced readers hoping for solution to mysql on a public server (that's an administrative judgment call).
Where it arises in relation to products and an extension at
http://forum.opencart.com/viewtopic.php ... &start=340
the advice was "fix your DIR_SYSTEM"
but if you read the entire page below that you will see how wonderful one leading or at least popular host's support was.
Phrase "error no 1242" leads to "About 828 results (0.52 seconds)" at
https://www.google.com/search?q=%22erro ... encart.com
where the ad at the bottom
http://www.wiki-errors.com/err.php?wiki=1242
probably should be prevented as it is likely hazardous to inexperienced readers hoping for solution to mysql on a public server (that's an administrative judgment call).
Hi butte (& others - who could successfully fix this problem)
I have checked my DIR_SYSTEM in "config.phP" & "admin/config.php". It seems ok (not the kind of thing referred to in first post: t=3351). I have already read most of the search results (Google) on Error 1242 - almost all of them diagnose problem as one of "duplicate entries" in query column in url_alias table. But that is not the case with me - as I have already pointed out. The third post - url is producing "page not available" result.
So, any suggestions to diagnose this problem & fix it? Thanks for any help.
I have checked my DIR_SYSTEM in "config.phP" & "admin/config.php". It seems ok (not the kind of thing referred to in first post: t=3351). I have already read most of the search results (Google) on Error 1242 - almost all of them diagnose problem as one of "duplicate entries" in query column in url_alias table. But that is not the case with me - as I have already pointed out. The third post - url is producing "page not available" result.
So, any suggestions to diagnose this problem & fix it? Thanks for any help.
We started with reference to
error 1242 (sql subquery -> more than one row -> unable to save)
occuring as a peculiar version of that, but
url_alias table again & again - no duplicate query entries for either category or product.
You've added that DIR_SYSTEM seems okay, again dismissed duplicate entries, noted 404 unfound.
ACTUAL ERROR MESSAGES LOGGED were not posted, usually the whole is needed to see the pivotal part, specific duplications (in posts, often of rows, records) or fields or tables may be enlightening. Above you gave what you concluded about errors abbreviated above. If you instead quote the ACTUAL ERROR MESSAGES LOGGED, that would help. Often one short snippet or two within a repeating error or short set of errors is more significant than the other details.
error 1242 (sql subquery -> more than one row -> unable to save)
occuring as a peculiar version of that, but
url_alias table again & again - no duplicate query entries for either category or product.
You've added that DIR_SYSTEM seems okay, again dismissed duplicate entries, noted 404 unfound.
ACTUAL ERROR MESSAGES LOGGED were not posted, usually the whole is needed to see the pivotal part, specific duplications (in posts, often of rows, records) or fields or tables may be enlightening. Above you gave what you concluded about errors abbreviated above. If you instead quote the ACTUAL ERROR MESSAGES LOGGED, that would help. Often one short snippet or two within a repeating error or short set of errors is more significant than the other details.
@butte
Thanks.
I omitted the error message, because it was too long, which, I thought, would put off most forum members. But I get your point.
Got around the problem by saving the newly added product again (a second time), without any change, just by clicking the "edit" button after saving once. Strange, but it works! Does it make any sense?
Shall post detailed error message next time it occurs.
Thanks.
I omitted the error message, because it was too long, which, I thought, would put off most forum members. But I get your point.
Got around the problem by saving the newly added product again (a second time), without any change, just by clicking the "edit" button after saving once. Strange, but it works! Does it make any sense?
Shall post detailed error message next time it occurs.
The error appearance is consistently repetitive. Happens whenever I add or do a change to products & when any new extension affecting products is added. Each time the error message appears on a different product - but does not stop with just one. I open the offending product (edit) in admin & re-save. Then the error happens for another product. This goes on for some time. Then it stops.
Curiously, all products relate to one manufacturer, added in bulk using the "Smart Export Import" extension by Michael Liang: http://www.opencart.com/index.php?route ... n_id=12348. There was no problem while doing the bulk additions. Recently I have not added any & I have stopped using this extension (uninstalled), but imported the database into the clean install of 1.5.4.1 in "/test" subfolder & then properly upgraded to 1.5.5.1 (no upgrade problems). The problem was happening, even before this, in the original root installation, after the bulk additions.
Typical message looks like:
I shall be thankful for any help to resolve this problem. Regards.
Curiously, all products relate to one manufacturer, added in bulk using the "Smart Export Import" extension by Michael Liang: http://www.opencart.com/index.php?route ... n_id=12348. There was no problem while doing the bulk additions. Recently I have not added any & I have stopped using this extension (uninstalled), but imported the database into the clean install of 1.5.4.1 in "/test" subfolder & then properly upgraded to 1.5.5.1 (no upgrade problems). The problem was happening, even before this, in the original root installation, after the bulk additions.
Typical message looks like:
Code: Select all
Warning: mysql_query() [function.mysql-query]: Unable to save result set in /home1/user/public_html/test/system/database/mysql.php on line 22Notice: Error: Subquery returns more than 1 row
Error No: 1242
SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, (SELECT price FROM product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '1' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '1' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, (SELECT points FROM product_reward pr WHERE pr.product_id = p.product_id AND customer_group_id = '1') AS reward, (SELECT ss.name FROM stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '1') AS stock_status, (SELECT wcd.unit FROM weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '1') AS weight_class, (SELECT lcd.unit FROM length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '1') AS length_class, (SELECT AVG(rating) AS total FROM review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT COUNT(*) AS total FROM review r2 WHERE r2.product_id = p.product_id AND r2.status = '1' GROUP BY r2.product_id) AS reviews, p.sort_order FROM product p LEFT JOIN product_description pd ON (p.product_id = pd.product_id) LEFT JOIN product_to_store p2s ON (p.product_id = p2s.product_id) LEFT JOIN manufacturer m ON (p.manufacturer_id = m.manufacturer_id) WHERE p.product_id = '825' AND pd.language_id = '1' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '0' in /home1/user/public_html/test/system/database/mysql.php on line 50Thank you, that plainly brings up mysql.php itself.
Preliminarily, the extension apparently allows export by product_id or batches, as well as import by batches. How does that choice play upon what you've been getting? it may be stumbling over batching.
Preliminarily, the extension apparently allows export by product_id or batches, as well as import by batches. How does that choice play upon what you've been getting? it may be stumbling over batching.
Thanks, butte.
Frankly I didn't keep track of this aspect - I might have used both types of imports on different occasions. But right now I am scared to use this extension. Once I use it again I will track the difference & let you know. Thanks for your inputs again & regards.
Frankly I didn't keep track of this aspect - I might have used both types of imports on different occasions. But right now I am scared to use this extension. Once I use it again I will track the difference & let you know. Thanks for your inputs again & regards.
Once it is properly supported by way of php.ini values (execution/mem/post/upload to 300/256M or 512M/999M/998M), JNeuhoff's free export/import tool works well. If server honors OC php.ini (usually two), those values can be upped in them. It doesn't do batches, but it also doesn't do what you encountered.
Thanks. But JNeuhoff's extension requires uploading the entire lot of products, after editing the exported file. This is a bit risky. Whereas, this extension allows incremental additions. I might add that, in this extension, for both export/import by Product IDs and by batches, we have to specify the product id (after verifying last added product ID in phpMyadmin) for each product being added. Otherwise it won't work.
Some servers, with .exe versions and supportive configurations, provide for exporting and importing via phpMyAdmin itself not only .sql but also .csv and spreadsheet format(s). Some servers provide for .sql alone. You might look into that, if need be ask support. (Just as with IRS or the like, ask thrice, take whatever was twice.)
Risk is relative. "Right now I'm scared to use" it is not risk free, where I come from. Replacements start with a known good slate, increments are thrown [out of whack] if human jobs for the purpose are bungled, in db or elsewhere.
Risk is relative. "Right now I'm scared to use" it is not risk free, where I come from. Replacements start with a known good slate, increments are thrown [out of whack] if human jobs for the purpose are bungled, in db or elsewhere.
Last edited by butte on Tue Oct 22, 2013 9:08 pm, edited 1 time in total.
@Paisleybay : did you check with Michael Liang on this issue you are facing? I'm looking at purchasing his extension.. for the incremental importing........
No more using Apsona, as they are not updated.
- Every upgrade -- rem. 2 reinstall vqmod & mindful of modules w/ VQmod -- E.g Import / Export Tool by MHC
@thegeekz
I did & he said: "if the data is entered properly in the excel template, there shouldn't be any problem". I reckon I have entered data properly, to the best of my knowledge, but still the problem persists.
I did & he said: "if the data is entered properly in the excel template, there shouldn't be any problem". I reckon I have entered data properly, to the best of my knowledge, but still the problem persists.
Ok, that ought to make me think twice about buying his extension... haha...
No more using Apsona, as they are not updated.
- Every upgrade -- rem. 2 reinstall vqmod & mindful of modules w/ VQmod -- E.g Import / Export Tool by MHC
Who is online
Users browsing this forum: No registered users and 38 guests
