[Resolved] 'name' is not allowed as key

[zombiesbyte]

Hi

I've got the dreaded 'name' is not allowed as key. Seems it's quite a common issue with the web plastered with people asking about this issue.

I'm getting it too. This is my first upload to a live staging server so I was never under the impression it was going to be smooth (programmers joke). None-the-less I'm faced with the error and have tried a dozen resets on the DB setup, global.inc.php file and even gone down randomly deleting cache files in hopes I'd solve the problem.

I've created a PDO:MySQL test connection using the same details I have in my global.in.php and I can retrieve data with no problems so I assume it's another issue which I've not read about.

For the record, I can't seem to get logged into the Admin area either. (username or password incorrect msg). When I log back in locally to check my login details that I have saved I manage to login no problem. There's a message about cache being disabled which I must have turned off a while ago while setting the site up so that may be worth noting.

I've re-uploaded all of my files (FTP) to make sure that that wasn't any issues with filezilla communication. I've also taken a fresh dump of the SQL in local PHPMyAdmin and dropped and redumped that into my hosts PHPMyAdmin.

preview.zombiesbyte.com

preview.zombiesbyte.com/conchecker.php (I just print_r the results of the cubecart_geo_country table as there's nothing sensitive here... I hope lol)

 

My thoughts:

# Does CC need to know what domain name it runs from. Locally this is just zombiesbyte.dev but my staging server is preview.zombiesbyte.com while my live will eventually be on SSL under the zombiesbyte.com

# I've got cache disabled so I'm not sure if this is a cache issue at all but when it comes to my experience with cache it has always caught me out!

# I find it strange that I can't login to admin, that would imply that the database can't be accessed at all using CC's methods (pun intended) but a classic straight forward connection works so I can't figure out the missing piece.

# This is where you tell me I've just forgot to change a value somewhere (fingers crossed it's this simple)

 

Many thanks

James

 

 

[Dirty Butter]

Please be sure your /includes/global.inc.php has values for your website and not something left over from your local installation. That was Bsmither's advice to someone else with the same error message, but the thread was not resolved.

[zombiesbyte]

Thanks Dirty Butter

I think that had to be the quickest reply I've even seen on a forum. I'm sure if you compared the time stamps your reply went out before my post did

As I'd mentioned , my test script used the same details and I'm connecting to my online DB only.

Cheers

James

Edited October 16, 2016 by zombiesbyte
Gramma

[bsmither]

The baseline reason for "database table column name is not known" is because the database (or something between PHP and the database) is answering, but is returning non-sensical info.

CubeCart uses PHP's mysql or mysqli connectoids, whichever has been enabled in the PHP.INI configuration file. CC6 will try to use mysqli first, then mysql.

If you still have the setup folder present, make a request for /setup/info.php. Scroll to the mysql or mysqli tables. Verify these tables exist.

[zombiesbyte]

Thanks bsmither

I don't have the setup folder as this would have been removed after install (in case I forgot about deleting it)

The conchecker.php file is simply a file that uses PDO to contact the same database as set in my global.in.php. It is impossible for me to contact any database other than the one at my hosts address as they don't support foreign connections to the outside network.

As you can see the data gets extracted so there's no problem with the communication between script and DB server as far as native PHP -> MySQL (just can't work out why CC -> MySql doesn't work)

Thanks

James

[zombiesbyte]

I've been running a few checks with regards to my conchecker.php file. (http://preview.zombiesbyte.com/conchecker.php)

Both PDO and mySQLi work

<?php

$dbdatabase = '{removed-for-security}';
$dbhost = '{removed-for-security}';
$dbpassword = '{removed-for-security}';
$dbprefix = '{removed-for-security}';
$dbusername = '{removed-for-security}';


$db = new PDO("mysql:host={$dbhost};dbname={$dbdatabase};charset=utf8mb4", $dbusername, $dbpassword);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

try	{
	echo "Total tables found using PDO: " . count($db->query('show tables')->fetchAll(PDO::FETCH_NUM)) . "<br>\n";
} catch(PDOException $ex) {
	echo "problem connecting to db using PDO";
}


$mysqli = new mysqli($dbhost, $dbusername, $dbpassword, $dbdatabase);
try {
	echo "Total tables found using mySQLi: " . count(mysqli_fetch_all($mysqli->query('SHOW TABLES'))) . "<br>\n";
} catch(Exception $e) {
	echo "problem connecting to db using mysqli";
}

 

[zombiesbyte]

As additional investigation

the error seems quite strange to say that name is not allowed as a key since this is a table automatically setup by CC with the DB fields "name" and "array"

Screen shot of LOCAL DEV table ###_cubecart_config above

(note: ### is a mask for what I have as my prefix and not actually my table name)

{see local-db.jpg}

My Live DB shows the same info:

{see live-db.jpg}

PDO and mySQLi confirm that connections are established to the database from my script and I can also confirm that CC uses mySQLi class to successfully connect. Whatever is going on in database.class.php seems to be the problem but I'm not 100% sure what checks are happening to change the script. Obviously this is something probably small that is miss-aligning information/communication but I can't put my finger on it

 

I just thought I'd post this in an effort to pin point the problem

Thanks

James

 

 

 

[bsmither]

Please temporarily put a file named phpinfo.php in CubeCart's main folder, and for it's contents, have:

<?php
phpinfo();
?>

Then request that file with your browser. It will generate the series of tables I mentioned earlier.

"The error seems quite strange to say that 'name' is not allowed as a key."

It is strange only in that there is no better error message. This is what CubeCart says for bad data being received -- expecting column names -- but what is sending that bad data is not easily determined.

CubeCart does not use PDO, so experiments with that can be disregarded. That leaves MySQli, or the mysqlnd that is part of PHP. See if that table is in the PHPINFO report.

[zombiesbyte]

Hi bsmither

phpini added

I realise PDO wasn't used but left it in on my tests anyway.

I've updated the script to die() with error if it tries to use the old mysql connection type. It never loads it so the mysqli has passed and is always used.

I can't find anything in PHP info that stands out but I've left it on for you to look at if you need it.

Many thanks

James

 

[bsmither]

In your test code above, I see that you are instantiating $mysqli with four parameters. CubeCart's mysqli class is being constructed with six parameters:

Yours:
$mysqli = new mysqli($dbhost, $dbusername, $dbpassword, $dbdatabase);

CubeCart:
$dbport = (isset($config['dbport']) && !empty($config['dbport'])) ? $config['dbport'] : ini_get('mysqli.default_port');
$dbsocket = (isset($config['dbsocket']) && !empty($config['dbsocket'])) ? $config['dbsocket'] : ini_get('mysqli.default_socket');
$this->_db_connect_id = new mysqli(
    $config['dbhost'],
    $config['dbusername'],
    $config['dbpassword'],
    $config['dbdatabase'],
    $dbport,
    $dbsocket
);

According to the PHP.INI:

Yours:
--MYSQLI--
mysqli.default_port: 3306
mysqli.default_socket: /tmp/mysqld.sock

Mine:
--MYSQLI--
mysqli.default_port: 3306
mysqli.default_socket: [no value]

So, mine uses TCP and yours supposedly uses a socket. But your test code does not include these parameters. Try changing your test code to use the ini_get() commands that CubeCart does.

 

[zombiesbyte]

Hi bsmither

I've updated the script to use the port and socket:

 

<?php

$dbdatabase = '{removed-for-security}';
$dbhost = '{removed-for-security}';
$dbpassword = '{removed-for-security}';
$dbprefix = '{removed-for-security}';
$dbusername = '{removed-for-security}';

$dbport = ini_get('mysqli.default_port');
$dbsocket = ini_get('mysqli.default_socket');

echo "<br>\ndbport from ini_get('mysqli.default_port'): " . $dbport .  "<br>\n";
echo "dbsocket from ini_get('mysqli.default_socket'):" . $dbsocket .  "<br>\n";

$mysqli = new mysqli($dbhost, $dbusername, $dbpassword, $dbdatabase, $dbport, $dbsocket);

try {
	echo "Total tables found using mySQLi: " . count(mysqli_fetch_all($mysqli->query('SHOW TABLES'))) . "<br>\n";
} catch(Exception $e) {
	echo "problem connecting to db using mysqli";
}

Which results in the table connecting as before and both values actually being supplied when parsing

//Output

dbport from ini_get('mysqli.default_port'): 3306
dbsocket from ini_get('mysqli.default_socket'):/tmp/mysqld.sock
Total tables found using mySQLi: 64

As far as I can see my script is connecting at every turn, even the CC mysqli class is connecting.

 

Many thanks

James

[bsmither]

The next test is to try to fetch the columns from a table.

SHOW COLUMNS FROM CubeCart_config;

Dump the resultset to see the actual return.

[zombiesbyte]

I've jumped ahead a step and got data too:

<?php

//... variables as before
  
$dbport = ini_get('mysqli.default_port');
$dbsocket = ini_get('mysqli.default_socket');

echo "<br>\ndbport from ini_get('mysqli.default_port'): " . $dbport .  "<br>\n";
echo "dbsocket from ini_get('mysqli.default_socket'):" . $dbsocket .  "<br>\n";

$mysqli = new mysqli($dbhost, $dbusername, $dbpassword, $dbdatabase, $dbport, $dbsocket);

try {
	echo "Total tables found using mySQLi: " . count(mysqli_fetch_all($mysqli->query('SHOW TABLES'))) . "<br>\n";
} catch(Exception $e) {
	echo "problem connecting to db using mysqli";
}

echo "Lets get the column names from ###_cubecart_config table<br>\n";
print_r(mysqli_fetch_all($mysqli->query('SHOW COLUMNS FROM `###_cubecart_config`')));


echo "Lets get the first 3 rows of data from ###_cubecart_config table<br>\n";
print_r(mysqli_fetch_all($mysqli->query('SELECT * FROM `###_cubecart_config` LIMIT 3')));

 

 

//output

dbport from ini_get('mysqli.default_port'): 3306
dbsocket from ini_get('mysqli.default_socket'):MySQL
Total tables found using mySQLi: 64
Lets get the column names from ###_cubecart_config table
Array
(
    [0] => Array
        (
            [0] => name
            [1] => varchar(100)
            [2] => NO
            [3] => PRI
            [4] => 
            [5] => 
        )

    [1] => Array
        (
            [0] => array
            [1] => text
            [2] => NO
            [3] => 
            [4] => 
            [5] => 
        )

)
Lets get the first 3 rows of data from ###_cubecart_config table
Array
(
    [0] => Array
        (
            [0] => All_In_One_Shipping
            [1] => eyJzdGF0dX...{shortened this for display purpsoses}
        )

    [1] => Array
        (
            [0] => config
            [1] => eyJzdG9yZV...{shortened this for display purpsoses}
        )

    [2] => Array
        (
            [0] => Contact_Form
            [1] => eyJzdGF0dXM...{shortened this for display purpsoses}
        )

)

 

 

I can confirm that this is exactly the same live as it is locally.

http://preview.zombiesbyte.com/conchecker.php

 

 

[bsmither]

I've exhausted my advice and troubleshooting steps at this level.

I would next use a diagnostic tool I built and place calls to the tool at key locations in the actual CubeCart script files.

If you want to pursue this offline, send me a PM with your email address.

 

[zombiesbyte]

Thanks bsmither

I understand.

Just as a final angle, I've been working with the where function on line 768 of database.class.php in an effort to understand what is going on.

I include this small section of code:

<?php

/**
 * Builds a WHERE string
 *
 * @param string $table
 * @param array $whereArray
 * @return string
 */
public function where($table, $whereArray = null) {
    
    echo "call to function where:\n";
    echo "\$table: {$table}\n";
    echo "\$whereArray::\n";
    print_r($whereArray);
    echo "\n\n";
  
	...

Locally this produces approximately 218 calls

While online it stops after only 62 calls.

Interestingly the error occurs 2nd call in (or so it seems but this maybe an output buffer delay or something)

The last group of results are different, they seem to get stuck in a loop of:

call to function where:
$table: CubeCart_inventory
$whereArray::
Array
(
    [status] => 1
    [featured] => 1
)

I've left this on for you to see. Does this help identify the problem?

(view page source, it's easier to read if you're not already)

http://preview.zombiesbyte.com/

 

[zombiesbyte]

I've managed to setup an error log to see what else is caught since this seems strange for it to just die like this

I can that my error log is looking a lot like this post by matchattaxoutlet

matchattaxoutlet talks with the hosting provider which reports that it is down to server load. I'm with 1&1 and they are quite good at handling bulky tasks so I'm surprised if it is down to resources but everything is starting to look that way.

I'm not sure what I should be doing next

...update: I'm re-uploading everything again.

[zombiesbyte]

In the mean time, http://test.zombiesbyte.com/setup/ has a fresh install which is working fine.

I'll be removing this probably tonight so the link won't work for anyone reading this in the future.

 

I'm just uploading the fresh copy now. I've enabled caching rules (CC admin settings) and turned on debugging mode too

Fingers crossed

[bsmither]

" Locally this produces approximately 218 calls "

For a request for the Homepage? My Homepage is 365 calls to Database->where(). But probably I have more products to show on the Homepage.

 

[bsmither]

In your conchecker.php file, try using the method CC uses:

		$query = "SHOW COLUMNS FROM CubeCart_config;";
		$return = array();
		if (($result = $mysqli->query($query)) !== false) { echo "We have a non-false result from making the query.";
			while (($row = $result->fetch_assoc()) !== null) {
				$return[$row['Field']] = $row['Field'];
			}
		} else { echo ("We have a false result from making the query."; }
		print_r($return);

You also said:

dbsocket from ini_get('mysqli.default_socket'):MySQL

But my test just a few minutes ago gave:

(HTML source) dbsocket from ini_get('mysqli.default_socket'):<br>

 

[zombiesbyte]

Hi bsmither.

Not sure what I'm to expect with this one.

I'm still uploading so this is the result of that script locally (the version of my website that works)

We have a non-false result from making the query.Array
(
    [name] => name
    [array] => array
)

I'll run it on the staging once I've completed my upload

[zombiesbyte]

Files are all uploaded.

Same error as before

I've noted straight away that the debug is not on which tells me that communication with the database for this setting is not being read or used.

Ran the script on the live staging: (same as off-line dev)

We have a non-false result from making the query.Array
(
    [name] => name
    [array] => array
)

[bsmither]

In /includes/global.inc.php, what do you have for, if anything:

$glob['cache'];

[Al Brookbanks]

This issue has been resolved internally. The database had incorrect case on table names (probably having come off a Windows box). Once the case on the table names were fixed it worked fine. 

For example:

`cc_cubecart_config`

was changed to:

`cc_CubeCart_config`

This issue does come up a lot and it is always due to a config error or database corruption. 

[zombiesbyte]

Thanks Al

I've been trying to get the XAMPP environment which I use to develop on in line with the Linux environment. The thing is these two need to be interchangeable as any future changes I roll out need to be synchronised directly without having to then force manual changes. The DB calls use a prefix but I can't see the option for changing "_CubeCart_" prefix anywhere as it looks hardcoded.

I've identified 996 matches across 64 files which I intend to change the code directly for calling the "CubeCart_" to "cubecart_" thus solving the issue with casing although I imagine this causes an issue with potentially any automatic updates which I'll need to perhaps avoid doing on the live version of my site. Updates on my local dev copy would then just need to have the "CubeCart_" to "cubecart_" casing changed again.

Can you see any potential issues with this workaround?

[Al Brookbanks]

I don't think you have done anything wrong. I believe that when you export from XAMPP the table names get malformed. There may be a MySQL setting or database collation that can be used to stop this happening in the future. 

Have a look at this: http://stackoverflow.com/questions/6248735/how-to-force-case-sensitive-table-names