cancel
Showing results for 
Search instead for 
Did you mean: 

Admin Reports - Unable to generate reports By Customers

Admin Reports - Unable to generate reports By Customers

Hello Guys, Cheers!

 

Magento CE Ver: 2.1.5 Apache 2.4, Win 7, MySQL 5.7.17 with sample data is installed.
magento deploy:mode:set developer

Magento installation - DB tables uses prefix_name.

DB: magento1

Tables:
bd_customer_entity
bd_customer_entity_datetime
bd_customer_entity_decimal
bd_customer_entity_int
bd_customer_entity_text
bd_customer_entity_varchar
bd_customer_form_attribute
bd_customer_grid_flat
bd_customer_group
bd_customer_log
bd_customer_visitor

I tried to call a simple report by Customers:
Adminpanel > Reports > by Customers
and i receive this Error Message:

 

2 exception(s):
Exception #0 (Zend_Db_Statement_Exception): SQLSTATE[42S02]: Base table or view not found: 1146 Table
'magento1.customer_entity' doesn't exist, query was: SELECT COUNT(DISTINCT detail.customer_id) FROM `bd_review` AS `main_table`
 INNER JOIN `bd_review_detail` AS `detail` ON main_table.review_id = detail.review_id
 INNER JOIN `customer_entity` AS `customer` ON customer.entity_id = detail.customer_id

Exception #1 (PDOException): SQLSTATE[42S02]: Base table or view not found: 1146 Table 'magento1.customer_entity' doesn't exist

Exception #0 (Zend_Db_Statement_Exception): SQLSTATE[42S02]: Base table or view not found: 1146 Table
'magento1.customer_entity' doesn't exist, query was: SELECT COUNT(DISTINCT detail.customer_id) FROM `bd_review` AS `main_table`
 INNER JOIN `bd_review_detail` AS `detail` ON main_table.review_id = detail.review_id
 INNER JOIN `customer_entity` AS `customer` ON customer.entity_id = detail.customer_id

[2017-03-08 15:30:11] main.INFO: Cache file with merged layout:

LAYOUT_adminhtml_STORE1_39501b345b8c7abc018f5f6da66657657 and handles default, reports_report_statistics_index: Please correct the XML data and try again.  [] []

It's clear that the SQL Statement has a mistake. When I run the Statement in MySQL Environment, everythings works fine with:

SELECT COUNT(DISTINCT detail.customer_id) FROM `bd_review` AS `main_table`
 INNER JOIN `bd_review_detail` AS `detail` ON main_table.review_id = detail.review_id
 INNER JOIN `bd_customer_entity` AS `customer` ON customer.entity_id = detail.customer_id

Please how does someone alter the SQL Statement in the code? Whats is the file please.
i could´t find the real execution page! (MVC)

Thank you all!

4 REPLIES 4

Re: Admin Reports - Unable to generate reports By Customers

@da Silva

 

The error is clearly mentioned - the table wasn't found. 


Best part of the action is to redeploy Magento using composer and run system upgrade command. I hope that will fix the issue. 

 

composer update

php bin/magento setup:upgrade

php bin/magento setup:di:compile

php bin/magento setup:static-content:deploy

Let me know if that helps. 

 

Cheers 

Magento Certified Solution Specialist | Lead Magento developer
If this response was helpful to you, consider giving kudos to this post

Re: Admin Reports - Unable to generate reports By Customers

@da Silva

 

You must check the table_prefix in config.php file in app/etc/. It should have your DB prefix there. 

 array (
    'table_prefix' => '', 
  ),
Magento Certified Solution Specialist | Lead Magento developer
If this response was helpful to you, consider giving kudos to this post

Re: Admin Reports - Unable to generate reports By Customers

Hello @ShoaibRehman89

Thanks for your help!

I didn´t still find the files that support SQL Statemant.

To avoid new SQL Statement erros what i did was:

1° I dropped the whole server directory "Magento-CE-2.1.5_sample_data-2017-02-20-05-07-23" and installed it again.  

2° I let the DB tables without "Table_Prefix".

3° Because of 1000 error messages on CLI regarding Php insufficient memory i did:

; Maximum amount of memory a script may consume (128MB)
; http://php.net/memory-limit
memory_limit = 2G

4° Another requirement from the server:

[Date]
; Defines the default timezone used by the date functions
; http://php.net/date.timezone
date.timezone = Europe/Berlin

5° # Creating Cron Job

6° Step

php bin/magento setup:upgrade

php bin/magento deploy:mode:set developer


7° After that, it comes the whole [opcache] Settings on php.ini.

; Determines if Zend OPCache is enabled
opcache.enable=1

; Determines if Zend OPCache is enabled for the CLI version of PHP
opcache.enable_cli=1

; The OPcache shared memory storage size.
opcache.memory_consumption=64

; The amount of memory for interned strings in Mbytes.
opcache.interned_strings_buffer=4

; The maximum number of keys (scripts) in the OPcache hash table.
; Only numbers between 200 and 100000 are allowed.
opcache.max_accelerated_files=20000

; The maximum percentage of "wasted" memory until a restart is scheduled.
opcache.max_wasted_percentage=5

; When this directive is enabled, the OPcache appends the current working
; directory to the script key, thus eliminating possible collisions between
; files with the same name (basename). Disabling the directive improves
; performance, but may break existing applications.
opcache.use_cwd=1

; When disabled, you must reset the OPcache manually or restart the
; webserver for changes to the filesystem to take effect.
opcache.validate_timestamps=1

; How often (in seconds) to check file timestamps for changes to the shared
; memory storage allocation. ("1" means validate once per second, but only
; once per request. "0" means always validate)
opcache.revalidate_freq=2

; Enables or disables file search in include_path optimization
;opcache.revalidate_path=0

; If disabled, all PHPDoc comments are dropped from the code to reduce the
; size of the optimized code.
opcache.save_comments=1

; If enabled, a fast shutdown sequence is used for the accelerated code
; Depending on the used Memory Manager this may cause some incompatibilities.
;opcache.fast_shutdown=0

; Allow file existence override (file_exists, etc.) performance feature.
opcache.enable_file_override=1

; A bitmask, where each bit enables or disables the appropriate OPcache
; passes
opcache.optimization_level=0xffffffff

;opcache.inherited_hack=1
;opcache.dups_fix=0

; The location of the OPcache blacklist file (wildcards allowed).
; Each OPcache blacklist file is a text file that holds the names of files
; that should not be accelerated. The file format is to add each filename
; to a new line. The filename may be a full path or just a file prefix
; (i.e., /var/www/x  blacklists all the files and directories in /var/www
; that start with 'x'). Line starting with a ; are ignored (comments).
;opcache.blacklist_filename=

; Allows exclusion of large files from being cached. By default all files
; are cached.
opcache.max_file_size=0

; Check the cache checksum each N requests.
; The default value of "0" means that the checks are disabled.
;opcache.consistency_checks=0

; How long to wait (in seconds) for a scheduled restart to begin if the cache
; is not being accessed.
opcache.force_restart_timeout=180

; OPcache error_log file name. Empty string assumes "stderr".
;opcache.error_log=

; All OPcache errors go to the Web server log.
; By default, only fatal errors (level 0) or errors (level 1) are logged.
; You can also enable warnings (level 2), info messages (level 3) or
; debug messages (level 4).
opcache.log_verbosity_level=1

; Preferred Shared Memory back-end. Leave empty and let the system decide.
opcache.preferred_memory_model=

; Protect the shared memory from unexpected writing during script execution.
; Useful for internal debugging only.
opcache.protect_memory=0

; Allows calling OPcache API functions only from PHP scripts which path is
; started from specified string. The default "" means no restriction
opcache.restrict_api=

; Mapping base of shared memory segments (for Windows only). All the PHP
; processes have to map shared memory into the same address space. This
; directive allows to manually fix the "Unable to reattach to base address"
; errors.
opcache.mmap_base=0x20000000

; Enables and sets the second level cache directory.
; It should improve performance when SHM memory is full, at server restart or
; SHM reset. The default "" disables file based caching.
;opcache.file_cache=

; Enables or disables opcode caching in shared memory.
opcache.file_cache_only=0

; Enables or disables checksum validation when script loaded from file cache.
opcache.file_cache_consistency_checks=1

; Implies opcache.file_cache_only=1 for a certain process that failed to
; reattach to the shared memory (for Windows only). Explicitly enabled file
; cache is required.
opcache.file_cache_fallback=1

8° I don't intend to use composer at the moment.

9° That´s what i did, now at first it works!

Have you found where or which files we can alter the simple generated SQL Statements?

Please let me or let us know if you found all right!

Thank you, Cheers!

Re: Admin Reports - Unable to generate reports By Customers

In mein case "Magento-CE-2.1.5_sample_data-2017-02-20-05-07-23", this is the folder and file root:
htdocs > magento > app > etc> env.php

But there was no conflict with array ( 'table_prefix' => '', when i used table_prefix.

The real problem was that file generated SQL Statement errors i want to discover where we can alter this situation, so we can personalize the DB as wished.


Cheers!