We launched new forums in March 2019—join us there. In a hurry for help with your website? Get Help Now!
MODX Community Forums Archive

Different behavior between mySQL 5.1.44 and 5.1.50 ?

    • 3440
    • 17 Posts
    bitbox Reply #1, 15 years, 10 months ago
    Hello,

    I’m building a web application using xPDO as ORM.

    At a point I’m using a bind graph to query the db and obtain records related to a main one, this graph is composed of simple (one to one) relations but also nested (many to many) relations.

    The graph is expressed as follow:
    $graph = '{"Civility":{},"Sector":{},"Country":{},"ContactSpoken":{"Lang":{}},"ContactFields":{"Field":{}}}';


    Considering I already have a configured $query instance, I then invoque getCollectionGraph to recover the corresponding db records:
    $x_objs = $xpdo->getCollectionGraph( $db_class, $graph, $query );


    Fine, it all runs smoothly on my dev configuration, returning an array of objects I can work with...

    But then, when I run the exact same code on my production server >:( nothing comes out in $x_objs... it’s just an empty array ! No php error generated, just a silent failure to execute the exact same operation that runs on the dev machine !?

    After a little debugging, I discovered that removing the nested graph parts miraculously brought back some data in $x_objs ! That is, using the following graph value works on the production server:
    $graph = '{"Civility":{},"Sector":{},"Country":{},"ContactSpoken":{},"ContactFields":{}}';


    But, of course, it doesn’t satisfy me since I’m not recovering the needed information at the second level of the relationship.
    After (double) checking what differences exist between my dev and prod environments, it appears that the dev machine runs php 5.2.13 and mySQL 5.1.44, when the prod server runs php 5.2.14 and mySQL 5.1.50... That’s all I could detect, and since I run the test on a database containing only 2 records each with 2 nested related records it seems that memory shouldn’t be an issue.

    I’m stuck there !.. Could anyone tell me if anything is known about a different behavior when running 5.1.44 and 5.1.50 (couldn’t find something relevant on the mySQL web site) ?.. Why does it work under 5.1.44 and break under the, supposedly newer, 5.1.50, or is it something else ?

    Any help would be greatly appreciated !

    Thanks.
      • 22303 MODX Staff
      • 10,725 Posts
      opengeek Reply #2, 15 years, 10 months ago
      Sounds very odd, but kind of hard to diagnose something like this without knowledge of the environment or the model code TBH. There should be no difference between working with 5.1.44 and 5.1.50.
        Jason Coward
        Chief Architect @ MODX
        http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek
        • 3440
        • 17 Posts
        bitbox Reply #3, 15 years, 10 months ago
        Thank you for answering so fast !..

        Well... if of any interest here is the schema I’m working with:
        <?xml version="1.0" encoding="UTF-8"?>
<model package="whoswho" baseClass="xPDOObject" platform="mysql" defaultEngine="MyISAM" tablePrefix="" phpdoc-package="whoswho" phpdoc-subpackage="model">

	<!-- MAIN CONTACT TABLE -->
	<!-- main contacts -->
	<object class="wwContact" table="ww_contacts" extends="xPDOSimpleObject">
		<field key="uuid" dbtype="varchar" precision="36" phptype="string" null="false" default="" index="unique" />
		<field key="temp_id" dbtype="varchar" precision="10" phptype="string" null="false" default="" index="unique" />
		<field key="confirmed" dbtype="int" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />
		<field key="active" dbtype="int" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />
		<field key="blocked" dbtype="int" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />
		<field key="username" dbtype="varchar" precision="50" phptype="string" null="false" default="" index="unique" />
		<field key="pseudohash" dbtype="varchar" precision="32" phptype="string" null="false" default="" />
		<field key="civility_id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" index="fk" />
		<field key="firstname" dbtype="varchar" precision="50" phptype="string" null="false" default="" />
		<field key="lastname" dbtype="varchar" precision="50" phptype="string" null="false" default="" />
		<field key="dob" dbtype="int" precision="8" attributes="unsigned" phptype="integer" null="false" default="0" />
		<field key="image" dbtype="varchar" precision="255" phptype="string" null="true" />
		<field key="sector_id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" index="fk" />
		<field key="company" dbtype="varchar" precision="50" phptype="string" null="false" default="" />
		<field key="department" dbtype="varchar" precision="50" phptype="string" null="true" />
		<field key="address" dbtype="varchar" precision="200" phptype="string" null="true" />
		<field key="zip" dbtype="varchar" precision="20" phptype="string" null="true" />
		<field key="city" dbtype="varchar" precision="50" phptype="string" null="true" />
		<field key="country_i3166_1" dbtype="varchar" precision="2" phptype="string" null="false" default="" index="fk" />
		<field key="email" dbtype="varchar" precision="150" phptype="string" null="false" default="" />
		<field key="website" dbtype="varchar" precision="255" phptype="string" null="true" />
		<field key="mobile" dbtype="varchar" precision="20" phptype="string" null="true" />
		<field key="phone" dbtype="varchar" precision="20" phptype="string" null="false" default="" />
		<field key="fax" dbtype="varchar" precision="20" phptype="string" null="true" />
		<field key="reg_datetime" dbtype="datetime" phptype="string" null="false" default="0000-00-00 00:00:00" />
		<field key="log_datetime" dbtype="datetime" phptype="string" null="false" default="0000-00-00 00:00:00" />
		<!-- table relationships -->
		<!-- one to one -->
		<aggregate alias="Civility" class="civility" local="civility_id" foreign="id" cardinality="one" owner="foreign" />
		<aggregate alias="Sector" class="wwSector" local="sector_id" foreign="id" cardinality="one" owner="foreign" />
		<aggregate alias="Country" class="isoCountry" local="country_i3166_1" foreign="i3166_1" cardinality="one" owner="foreign" />
		<!-- one to many composites -->
		<composite alias="ContactSpoken" class="wwSpoken" local="id" foreign="contact_id" cardinality="many" owner="local" />
		<composite alias="ContactFields" class="wwContactHaswwFields" local="id" foreign="contact_id" cardinality="many" owner="local" />
		<composite alias="ContactGroups" class="wwContactGroup" local="id" foreign="owner_id" cardinality="many" owner="local" />
		<composite alias="ContactMessages" class="wwMessage" local="id" foreign="sender_id" cardinality="many" owner="local" />
	</object>


	<!-- CONTACT GROUPS AND MEMBERS -->
	<!-- contact groups -->
	<object class="wwContactGroup" table="ww_contact_groups" extends="xPDOSimpleObject">
		<field key="name" dbtype="varchar" precision="50" phptype="string" null="false" default="" />
		<field key="owner_id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" index="fk" />
		<field key="is_global" dbtype="int" precision="1" attributes="unsigned" phptype="integer" null="false" default="0" />
		<!-- table relationships -->
		<!-- one to many aggregates -->
		<aggregate alias="Owner" class="wwContact" local="owner_id" foreign="id" cardinality="one" owner="foreign" />
		<!-- one to many composites -->
		<composite alias="GroupMembers" class="wwContactGroupMember" local="id" foreign="group_id" cardinality="many" owner="local" />
	</object>

	<!-- contact group members -->
	<object class="wwContactGroupMember" table="ww_group_members" extends="xPDOObject">
		<field key="contact_id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" index="fk" />
		<field key="group_id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" index="fk" />
		<!-- table relationships -->
		<!-- one to one -->
		<aggregate alias="Contact" class="wwContact" local="contact_id" foreign="id" cardinality="one" owner="foreign" />
		<!-- one to many aggregates -->
		<aggregate alias="OwnerGroup" class="wwContactGroup" local="group_id" foreign="id" cardinality="one" owner="foreign" />
	</object>


	<!-- MESSAGES AND ATTACHMENTS -->
	<!-- messages -->
	<object class="wwMessage" table="ww_messages" extends="xPDOSimpleObject">
		<field key="sender_id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" index="fk" />
		<field key="timestamp" dbtype="timestamp" phptype="integer" null="false" default="0" />
		<field key="subject" dbtype="varchar" precision="255" phptype="string" null="false" default="" />
		<field key="content" dbtype="mediumtext" phptype="string" null="false" default="" />
		<!-- table relationships -->
		<!-- one to many composites -->
		<composite alias="Recipients" class="wwRecipient" local="id" foreign="message_id" cardinality="many" owner="local" />
		<composite alias="Attachments" class="wwAttachment" local="id" foreign="message_id" cardinality="many" owner="local" />
		<!-- one to many aggregates -->
		<aggregate alias="Sender" class="wwContact" local="sender_id" foreign="id" cardinality="one" owner="foreign" />

	</object>

	<!-- message recipients -->
	<object class="wwRecipient" table="ww_message_recipients" extends="xPDOObject">
		<field key="message_id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" index="fk" />
		<field key="recipient_id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" index="fk" />
		<!-- table relationships -->
		<!-- one to one -->
		<aggregate alias="Recipient" class="wwContact" local="recipient_id" foreign="id" cardinality="one" owner="foreign" />
		<!-- one to many aggregates -->
		<aggregate alias="Message" class="wwMessage" local="message_id" foreign="id" cardinality="one" owner="foreign" />
	</object>

	<!-- message attachments -->
	<object class="wwAttachment" table="ww_message_attachments" extends="xPDOSimpleObject">
		<field key="file_ref" dbtype="varchar" precision="255" phptype="string" null="false" default="" />
		<field key="message_id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" index="fk" />
		<!-- table relationships -->
		<!-- one to many aggregates -->
		<aggregate alias="Message" class="wwMessage" local="message_id" foreign="id" cardinality="one" owner="foreign" />
	</object>


	<!-- STANDALONE REFERENCES TABLES -->
	<!-- activity sectors -->
	<object class="wwSector" table="ww_sectors" extends="xPDOSimpleObject">
		<field key="active" dbtype="int" precision="1" attributes="unsigned" phptype="integer" null="false" default="1" />
		<field key="en" dbtype="varchar" precision="200" phptype="string" null="false" default="" />
		<field key="fr" dbtype="varchar" precision="200" phptype="string" null="false" default="" />
	</object>

	<!-- activity fields -->
	<object class="wwField" table="ww_fields" extends="xPDOSimpleObject">
		<field key="active" dbtype="int" precision="1" attributes="unsigned" phptype="integer" null="false" default="1" />
		<field key="group_id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="0" index="fk" />
		<field key="en" dbtype="varchar" precision="200" phptype="string" null="false" default="" />
		<field key="fr" dbtype="varchar" precision="200" phptype="string" null="false" default="" />
		<!-- table relationships -->
		<!-- one to many aggregates -->
		<aggregate alias="FieldGroup" class="wwFieldGroup" local="group_id" foreign="id" cardinality="one" owner="foreign" />
		<!-- many to many -->
		<composite alias="ContactFields" class="wwField" local="id" foreign="field_id" cardinality="many" owner="local" />
	</object>

	<!-- field groups -->
	<object class="wwFieldGroup" table="ww_field_groups" extends="xPDOSimpleObject">
		<field key="en" dbtype="varchar" precision="200" phptype="string" null="false" default="" />
		<field key="fr" dbtype="varchar" precision="200" phptype="string" null="false" default="" />
		<!-- table relationships -->
		<!-- one to many composites -->
		<composite alias="GroupFields" class="wwField" local="id" foreign="group_id" cardinality="many" owner="local" />
	</object>

	<!-- many to many ww_contacts to ww_fields relationship -->
	<object class="wwContactHaswwFields" table="ww_contacts_has_ww_fields" extends="xPDOObject">
		<field key="contact_id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="" index="pk" />
		<field key="field_id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="" index="pk" />
		<!-- table relationships -->
		<!-- many to many -->
		<aggregate alias="Contact" class="wwContact" local="contact_id" foreign="id" cardinality="one" owner="foreign" />
		<aggregate alias="Field" class="wwField" local="field_id" foreign="id" cardinality="one" owner="foreign" />
	</object>

	<!-- civilities translations -->
	<object class="civility" table="civilities" extends="xPDOSimpleObject">
		<field key="en_short" dbtype="varchar" precision="50" phptype="string" null="false" default=""/>
		<field key="en_long" dbtype="varchar" precision="50" phptype="string" null="false" default="" />
		<field key="fr_short" dbtype="varchar" precision="50" phptype="string" null="false" default="" />
		<field key="fr_long" dbtype="varchar" precision="50" phptype="string" null="false" default="" />
	</object>

	<!-- iso-3166-1 country codes -->
	<object class="isoCountry" table="iso_countries" extends="xPDOObject">
		<field key="i3166_1" dbtype="varchar" precision="2" phptype="string" null="false" default="" index="pk" />
		<field key="active" dbtype="int" precision="1" attributes="unsigned" phptype="integer" null="false" default="1" />
		<field key="en" dbtype="varchar" precision="200" phptype="string" null="false" default="" />
		<field key="fr" dbtype="varchar" precision="200" phptype="string" null="false" default="" />
	</object>

	<!-- iso-639-1/2 language codes -->
	<object class="isoLang" table="iso_langs" extends="xPDOObject">
		<field key="i639_1" dbtype="varchar" precision="2" phptype="string" null="false" default="" index="pk" />
		<field key="i639_2" dbtype="varchar" precision="7" phptype="string" null="false" default="" index="index" />
		<field key="active" dbtype="int" precision="1" attributes="unsigned" phptype="integer" null="false" default="1" />
		<field key="utf_8" dbtype="varchar" precision="255" phptype="string" null="false" default="" />
		<field key="en" dbtype="varchar" precision="200" phptype="string" null="false" default="" />
		<field key="fr" dbtype="varchar" precision="200" phptype="string" null="false" default="" />
		<!-- one to many composites -->
		<composite alias="SpokenLangs" class="isoLang" local="i639_1" foreign="lang_i639_1" cardinality="many" owner="local" />
	</object>

	<!-- many to many ww_contacts to iso_langs -->
	<object class="wwSpoken" table="ww_spoken" extends="xPDOObject">
		<field key="contact_id" dbtype="int" precision="10" attributes="unsigned" phptype="integer" null="false" default="" index="pk" />
		<field key="lang_i639_1" dbtype="varchar" precision="2" phptype="string" null="false" default="" index="pk" />
		<!-- table relationships -->
		<!-- many to many -->
		<aggregate alias="Contact" class="wwContact" local="contact_id" foreign="id" cardinality="one" owner="foreign" />
		<aggregate alias="Lang" class="isoLang" local="lang_i639_1" foreign="i639_1" cardinality="one" owner="foreign" />
	</object>

</model>


        And the methods that are involved in data recovery process:
        /**
	 * read - record(s) data recovery
	 *
	 * @param array $filter - ?			list of "where" conditions formatted as: '(and,or)|field_name:(=,!=,>,>=,<,<=,LIKE)|value'
	 * @param array $fields - optional	list of field name(s) to select in query
	 * @param string $graph - optional	a graph object listing the db relation aliases used for joining related tables
	 * 									format is in JSON notation {"rel_alias_0":{},"rel_alias_1":{"rel_alias_2":{}}}
	 *
	 * @return array - containing the data set
	 */
	public function read( $filter, $fields = false, $graph = false ) {
		/*** prepare new xPDO query ***/
		$this->query = $this->xpdo_instance->newQuery( $this->db_class );

		# retain only fields specified in $fields parameter (if any) otherwise retain all
		$base_fields = $fields;
		$fields = ( $fields ) ? $fields : array_keys( $this->xpdo_instance->getFields( $this->db_class ));
		/*
		 * the primary key(s) is (are) required for the query to execute properly, so we add it (them) if necessary
		 */
		$pk = $this->xpdo_instance->getPK( $this->db_class );
		if ( !is_array( $pk ) && $pk != null ) {
			$pk = array( $pk );
		}
		if ( $pk != null ) {
			$fields = array_unique( array_merge( $fields, $pk ));
		}
		# the $fields are safe now
		$select_columns = $this->xpdo_instance->getSelectColumns( $this->db_class, $this->db_class, '', $fields );
		$this->query->select( $select_columns );

		# build query based on specified conditions
		# TODO -> check if filter is required for the query to function
		if ( $filter ) {
			$this->filterToConditions( $filter );
		}

		/*** collect objects ***/
		if ( $graph ) {
			# when $graph parameter is specified, use collectionGraph
			$x_objs = $this->xpdo_instance->getCollectionGraph( $this->db_class, $graph, $this->query );
		}
		else {
			# standard collection - no table relation specified
			$x_objs = $this->xpdo_instance->getCollection( $this->db_class, $this->query );
		}
$this->debug->log($x_objs,'x_objs');
		/*** extract fields data ***/
		$data = array();
		foreach( $x_objs as $x_obj ) {
			# collect graph data if any
			$j_data = array();
			if ( $graph ) {
				$j_data = $this->extractGraphData( $graph, $x_obj );
			}
			array_push( $data, array_merge( $x_obj->toArray(), $j_data ) );
		}
		/*
		 * when using graph (getCollectionGraph) all fields are returned -> filter them out if necessary (only first level of data array)
		 */
		if ( $base_fields ) {
			$filtered = array();
			foreach ( $data as $key => $row ) {
				$f_row = array();
				foreach ( $base_fields as $field ) {
					if ( array_key_exists( $field, $row )) {
						$f_row[$field] = $row[$field];
					}
				}
				$filtered[$key] = $f_row;
			}
			$data = $filtered;
		}

		# return result set
		return $data;
	}


        /**
	 * filterToConditions - parses a filer array creating and / or conditions in the query object
	 *
	 * @param array $filer - required	list of "where" conditions formatted as: '(and,or)|field_name:(=,!=,>,>=,<,<=,LIKE)|value'
	 */
	private function filterToConditions( $filter ) {
		foreach ( $filter as $condition_txt ) {
			$condition_list = explode( '|', $condition_txt );
			# check numerical value
			$condition_list[2] = ( $condition_list[2] === (int)$condition_list[2] ) ? (int)$condition_list[2] : $condition_list[2];
			# check php code
			if ( stripos( $condition_list[2], '<?' ) !== false ) {
				$condition_list[2] = str_replace( '<?', '', $condition_list[2] );
				$condition_list[2] = stripslashes( $condition_list[2] );
				if (( stripos( $condition_list[2], '$_SESSION' ) !== false ) && !isset( $_SESSION )) {
					session_start();
				}
				eval ( '$condition_list[2] = ' . $condition_list[2] . ';' );
			}
			$condition = array( $condition_list[1] => $condition_list[2] );
			if ( strripos( $condition_list[0], 'and' ) !== false ) {
				$this->query->andCondition( $condition );
			}
			else {
				$this->query->orCondition( $condition );
			}
		}
	}


        /**
	 * extractGraphData - recursively parses a collectionGraph object to extract data based on graph's defined relationships
	 *
	 * @param string $graph - optional	a graph object listing the db relation aliases used for joining related tables
	 * 									format is in JSON notation {"rel_alias_0":{},"rel_alias_1":{"rel_alias_2":{}}}
	 * @param  abject $db_obj			an xPDO object collection returned by the getCollectionGraph method with same graph applied
	 *
	 * @return array - a possibly multi-dimensional array containing the objects data with graph names as keys for relational data
	 */
	private function extractGraphData( $graph, $db_obj ) {
		$graph = json_decode( $graph );
		foreach( $graph as $join => $v ) {
			if ( is_array( $db_obj->$join )) {
				$count = 0;
				foreach( $db_obj->$join as $x_join ) {
					$xj_data[$count] = ( $x_join != null && !empty( $x_join )) ? $x_join->toArray() : null;
					if ( count( get_object_vars( $v )) > 0) {
						$xj_data[$count] = array_merge( $xj_data[$count], $this->extractGraphData( json_encode ( $v ), $x_join ));
						$count++;
					}
				}
				$j_data[$join] = $xj_data;
			}
			else {
				$j_data[$join] = ( $db_obj->$join != null && !empty( $db_obj->$join )) ? $db_obj->$join->toArray() : null;
				if ( count( get_object_vars( $v )) > 0) {
					$j_data[$join] = array_merge( $j_data[$join], $this->extractGraphData( json_encode ( $v ), $x_join ));
				}
			}
		}
		return $j_data;
	}


        The problem arise in the "read" method, where the debug->log is located...
        But once again the most incomprehensible is that it all runs well under one configuration and not the other !? So frustrating, and I see no way out of this problem undecided
          • 22303 MODX Staff
          • 10,725 Posts
          opengeek Reply #4, 15 years, 10 months ago
          I’m a little lost in your code, and am really not sure how to respond. In particular, I have no idea what extractGraphData is doing or why. Regardless, I still see no reason why the version of MySQL would affect it. Perhaps it’s a problem with the PHP environment...
            Jason Coward
            Chief Architect @ MODX
            http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek
            • 3440
            • 17 Posts
            bitbox Reply #5, 15 years, 10 months ago
            Thank you for paying attention to the problem... I’m still investigating.

            The code extracts are in fact part of a wider class that is used in an ajax environment, aimed at providing a kind of "standard" API to xPDO’s CRUD functionalities straight from javascript (whatever)...
            From my experience, and if I’m correct, using the ->toArray() method on an object returned by getCollectionGraph only returns the values of columns in the main database class (base level), not the related ones (deeper levels). The purpose of extractGraphData is to merge "sub-arrays" containing the related fields’ data, returning one multi-dimensional array containing all the selected information, including related fields values. This array is then transmitted to the javascript frontend...

            If I’m missing something here, don’t hesitate to point me to a cleaner solution, but I must say that as documentation on xPDO is (understandably) still scarce I’m still exploring ways of doing things...

            Regarding the 5.1.50 problem I’m still on the case, I’ll keep you posted if a solution emerges.

            Thank you.