Announcement

Collapse
No announcement yet.

PCI Tool Belt and incorrect MariaDB association

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    PCI Tool Belt and incorrect MariaDB association

    We recently purchased PCI Tool Belt and I am trying to run a simple query. Having installed the module and attempted to run the example code, I am getting an odd error that seems to be assuming we are using a MariaDB server – which, of course, we are not. We are using MySQL (which is a requirement of this module). The exact error is:

    Runtime error in mm5/5.00/modules/util/ry_toolbelt_miva_9.mvc @ [00000005:000103dc]: .toolslookup.mv: Line 30: MvQUERY: mysql_stmt_prepare: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server versi PCI Net Tool Belt Error: Invalid query: :
    mysql_stmt_prepare: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server versi

    I can dump out the query on the screen and when I copy and paste it into phpMyAdmin, it works fine. So, there is something up with the module itself. This is the primary reason we purchased this module so this functionality is very important. Can you assist? Is there a configuration file somewhere that erroneously points to a MariaDB as opposed to MySQL?

    #2
    Got also your email, Ray will get back to you momentarily.
    Andreas Toman
    PCINET, LLC

    Miva Merchant Design, Development, Integration & Support
    We built over 200 Miva Merchant stores!
    Miva shopping cart design & integration service and see our Portfolio!


    e-mail: [email protected]
    web: www.pcinet.com
    LinkedIn: Andreas Toman
    phone: (786) 250-2056 (Miami, FL)

    Comment


      #3
      MariaDB is what our later servers run and is compatible with MySQL. Basically what happened is Oracle bought the company that operated MySQL in 2010, the original developers knew what typically happens when Oracle buys something (kills it or commercializes it), so they started MariaDB based on the last freely usable MySQL 5.5 code and have continued to release new versions since then. RedHat switched from MySQL to MariaDB in their later linux releases, so that's how we ended up with the switch.

      So, long story short, the same query you're running via phpmyadmin is talking to the same server as your store, and that server is MariaDB. This means something about the query is being altered before it hits the database server. Our support folks could try to take a look, but Andreas at PCI would probably be able to quickly help too.
      David Hubbard
      CIO
      Miva
      [email protected]
      http://www.miva.com

      Comment


        #4
        That's Ray's domain and not mine so whoever is faster ;-)
        Andreas Toman
        PCINET, LLC

        Miva Merchant Design, Development, Integration & Support
        We built over 200 Miva Merchant stores!
        Miva shopping cart design & integration service and see our Portfolio!


        e-mail: [email protected]
        web: www.pcinet.com
        LinkedIn: Andreas Toman
        phone: (786) 250-2056 (Miami, FL)

        Comment


          #5
          I typically just blame everything on you though...
          David Hubbard
          CIO
          Miva
          [email protected]
          http://www.miva.com

          Comment


            #6
            Posting the SQL statement would help unless it contains confidential information.
            Andreas Toman
            PCINET, LLC

            Miva Merchant Design, Development, Integration & Support
            We built over 200 Miva Merchant stores!
            Miva shopping cart design & integration service and see our Portfolio!


            e-mail: [email protected]
            web: www.pcinet.com
            LinkedIn: Andreas Toman
            phone: (786) 250-2056 (Miami, FL)

            Comment


              #7
              Hrm. Ok, so something is being modified before it hits the actual database. I tried running the example code provided, but had to modify the double quotes with an escape symbol to get the SQL formatted correctly as a MySql query. So the basic code I am using is:

              <mvt:item name="ry_toolbelt" param="query|query_string|array_name" />

              Example 1: Select query
              <mvt:item name="ry_toolbelt" param="assign|g.select_sql|'
              SELECT * FROM s01_Orders WHERE (ship_lname = "Test") ORDER BY id ASC;'" />
              (Note: The forums are removing my backslashes in front of "Test")

              The SQL is: &mvt:global:select_sql;<br>
              <mvt:item name="ry_toolbelt" param="query|g.select_sql|MyOrders" />

              Which displays:

              Select query The SQL is: SELECT * FROM s01_Orders WHERE (ship_lname = "Test") ORDER BY id ASC;

              Running that on the SQL tab in phpMyAdmin works correctly –so I am not sure how this is an invalid query. As you said, it must be something in the module that is modifying the sql.

              Comment


                #8
                Code:
                <mvt:item name="ry_toolbelt" param="query|query_string|array_name" />
                
                Example 1: Select query
                <mvt:item name="ry_toolbelt" param="assign|g.select_sql|'
                SELECT * FROM s01_Orders WHERE (ship_lname = \"Test\") ORDER BY id ASC;'" />
                
                The SQL is: &mvt:global:select_sql;<br>
                <mvt:item name="ry_toolbelt" param="query|g.select_sql|MyOrders" />
                I added the display so I could see what the SQL statement being passed was to see if there was an actual error; however, it correctly displays:

                The SQL is: SELECT * FROM s01_Orders WHERE (ship_lname = "Test") ORDER BY id ASC;
                Last edited by macrobber; 03-17-17, 07:14 AM.

                Comment


                  #9
                  In the past, I've had trouble with the semicolon at the end of a query. I don't recall the details, but there's some reason why I stopped using them a few years back.

                  Kent Multer
                  Magic Metal Productions
                  http://TheMagicM.com
                  * Web developer/designer
                  * E-commerce and Miva
                  * Author, The Official Miva Web Scripting Book -- available on-line:
                  http://www.amazon.com/exec/obidos/IS...icmetalproducA

                  Comment


                    #10
                    I've tried both ways - both with and without the semicolon. I still get the same error.

                    Comment


                      #11
                      I've heard back from Ray and he says that the error is being generated by the MivaScript/Empresa engine and not by tool belt. Toolbelt is simply passing the error back to me. There seems to be some mis-configuration of our Empresa installation and to contact my hosting provider.

                      So, I'm back to square one. Is anyone else having this issue? We are on 9.00066.

                      Comment


                        #12
                        I was able to get your query working by using the right quote in the prepare statement. Ray's toolbelt requires the right quotes to represent the inner string within the string expression. On my keyboard the right quote (`) is found with the tilde (~) character. I also removed the parenthesis in your statement.

                        Code:
                        
                        <mvt:item name="ry_toolbelt" param="assign|g.select_sql|'SELECT * FROM s01_Orders WHERE ship_lname =`Test` ORDER BY id ASC'" />
                        <mvt:item name="ry_toolbelt" param="query|g.select_sql|MyOrders" />
                        <mvt:item name="ry_toolbelt" param="assign|g.MyOrderslist|varlist('l', 'MyOrders', l.all_settings)" />
                        
                        &mvt:global:MyOrderslist;
                        It may still be something to do with your Empresa engine but the toolbelt works as described for me. I hope this helps.

                        http://www.alphabetsigns.com/

                        Comment


                          #13
                          That seems to fix it; although using the example code given by Tool Belt, the line

                          Code:
                          <mvt:item name="ry_toolbelt" param="query|query_string|array_name" />
                          Still gives the error. If I remove this line, the error goes away. I am not sure if this is specific to our install or an outdated example, but it appears that line was the one causing the issue.

                          As reference, the code block included as an example with PCI Tool Belt is as follows:

                          Code:
                           <mvt:item name="ry_toolbelt" param="query|query_string|array_name" />    
                          Example 1: Select query <mvt:item name="ry_toolbelt" param="assign|g.select_sql|' SELECT * FROM s01_Orders WHERE (ship_lname = `' $ g.customer_lname $ '`) ORDER BY id ASC'" />
                          <mvt:item name="ry_toolbelt" param="query|g.select_sql|MyOrders" />

                          Comment


                            #14
                            I'm glad you were able to get it to work out.

                            The query item you removed is throwing an error because it is looking for a query_string variable which you had not yet created. It would had stored the result in an array named l.all_settings:array_name. Or in any other array that you name such as l.all_settings:MyOrders.

                            You may also want to remove the text 'Example 1: Select query' as that will render to the html.
                            http://www.alphabetsigns.com/

                            Comment


                              #15
                              Yeah, I was just using that in testing to display. This is on a dev site - so it's ok that it's displaying at the moment.

                              Comment

                              Working...
                              X