Ytukyg

I'm still somewhat new to Python and am writing a Flask API for communicating with MySQL and executing the regular commands I get through my ticketing system on a day-to-day basis. The premise is that:

• A request is sent to the API with the query (among other things) in it


• The code regex'es the WHERE clause


• A mysqldump is started via subprocess to backup those rows of the table affected


• The query itself is then executed

The problem I'm running into is when a long where clause is provided (roughly 2,850 characters 2,865 characters or more, so not tremendously long) the mysqldump itself does not work. The query itself still executes and returns results, the dump though does not complete.

I've tried almost every combination I can think of on the subprocess library with call, Popen, communicate, wait and nothing seems to work with these larger queries.

While testing, the queries I'm executing are very fast (the SELECT executes in 0.00 seconds inside the mysql client) so they're not even long-running/timeouts. Right now, I'm testing with a simple SELECT statement passing a range of Id values in the WHERE clause. I have it at a point where if I add a single ID, the dump stops working, but the API still returns the query results. I can also take the mysqldump command being passed to the subprocess and execute within the shell on the intended server and it completes in .015s. So it seems that the code is written correctly, passing back and forth as it should, but something happens when the WHERE clause starts to get a little long.

The current working (on smaller queries) code is below:

from flask import request, jsonify, abort

from sshtunnel import SSHTunnelForwarder

import configparser, re, subprocess, paramiko



...



server = SSHTunnelForwarder(

    (proxy, 22),

    ssh_username=user,

    ssh_pkey=key,

    remote_bind_address=(db_host, 3306)

)



server.start()



conn = db.MySQL(

    '127.0.0.1',

    db_user,

    db_pass,

    database,

    server.local_bind_port

).client



cursor = conn.cursor()



regexp = re.compile("where", re.IGNORECASE)

query_split = regexp.split(query)

where_clause = query_split[-1].strip()



bak_file = open('/mnt/backup/' + ticket_no + '.bak.sql', 'w+')



mysqldump = '/usr/bin/mysqldump %s %s -u%s -p%s -h%s -P%d 

    --single-transaction --master-data=2 --replace -t -w"' % (

    database, table, db_user, db_pass, '127.0.0.1',

    server.local_bind_port) + where_clause + '"'



proc = subprocess.Popen(mysqldump, stdout=bak_file, stderr=subprocess.STDOUT, shell=True)

proc.wait()



bak_file.close()



try:

    cursor.execute(query)

    ret = cursor.fetchone()[0]



#                conn.commit()

    conn.rollback()

except (db.MySQLdb.Error, db.MySQLdb.Warning) as e:

    conn.rollback()

    conn.close()

    ret = e[1]



server.stop()



return jsonify({'result': ret})

Data is being sent over as JSON. As mentioned, adding a single id to the below list causes it to fail. Even adding 2 characters to an Id (2900->290011) fails (adding 1 character it still works).

{

"ticket": "0000000000",

"host": "servername",

"db": "dbname",

"query": "select count(*) from table WHERE Id in ('2025','2026','2027','2028','2029','2032','2033','2034','2036','2037','2038','2041','2042','2043','2044','2045','2046','2047','2048','2049','2050','2051','2057','2059','2060','2061','2062','2063','2064','2065','2067','2069','2084','2086','2087','2089','2091','2092','2093','2095','2096','2097','2098','2100','2101','2103','2104','2110','2111','2112','2113','2114','2115','2116','2117','2118','2120','2121','2122','2123','2124','2125','2126','2127','2128','2129','2130','2131','2144','2146','2160','2161','2163','2164','2165','2166','2167','2168','2169','2170','2172','2173','2174','2175','2176','2177','2178','2179','2180','2181','2182','2183','2184','2191','2193','2200','2201','2202','2204','2206','2208','2211','2212','2214','2215','2217','2226','2227','2228','2229','2230','2233','2234','2235','2236','2237','2241','2242','2244','2245','2246','2248','2250','2251','2252','2256','2257','2258','2260','2262','2264','2266','2267','2268','2269','2271','2272','2273','2274','2275','2276','2278','2280','2281','2282','2283','2284','2286','2287','2288','2289','2291','2292','2293','2294','2295','2296','2297','2298','2299','2300','2301','2302','2303','2304','2308','2309','2310','2312','2314','2322','2323','2326','2327','2328','2329','2330','2331','2332','2333','2334','2335','2336','2337','2338','2339','2341','2342','2343','2344','2345','2346','2347','2348','2349','2350','2351','2352','2353','2354','2355','2356','2358','2360','2363','2364','2365','2367','2368','2369','2372','2373','2375','2376','2378','2380','2381','2383','2384','2385','2386','2387','2388','2390','2391','2392','2393','2398','2399','2400','2401','2402','2405','2406','2407','2408','2409','2410','2411','2413','2414','2416','2417','2418','2421','2424','2427','2428','2429','2430','2432','2433','2434','2435','2436','2437','2438','2439','2440','2441','2447','2448','2450','2456','2457','2458','2459','2460','2461','2469','2470','2472','2473','2475','2477','2478','2479','2480','2481','2482','2487','2497','2498','2499','2500','2502','2505','2506','2507','2508','2509','2511','2512','2515','2518','2519','2520','2521','2523','2526','2527','2528','2529','2531','2532','2533','2534','2536','2539','2541','2542','2544','2549','2550','2551','2554','2555','2556','2559','2566','2594','2596','2598','2599','2600','2602','2603','2605','2607','2608','2609','2610','2611','2619','2620','2622','2623','2624','2631','2632','2633','2634','2635','2636','2638','2668','2670','2671','2672','2673','2675','2676','2677','2680','2685','2686','2688','2690','2709','2711','2737','2740','2744','2748','2764','2765','2768','2769','2770','2772','2773','2774','2775','2777','2778','2779','2787','2794','2795','2796','2807','2815','2839','2840','2841','2853','2854','2858','2859','2860','2861','2862','2865','2867','2871','2872','2874','2875','2880','2882','2885','2890','2891','2892','2895','2905','2906','2907')"

}

Note: Due to firewall restrictions, the query code connects through an SSHTunnel, which I thought might be part of/causing the issue. However, as I mentioned, with smaller queries, everything runs just fine.

UPDATE 1: I did some further testing and found that the maximum length WHERE clause I can pass is 2,864 characters/bytes. As soon as I hit 2,865, it breaks. However, the longer WHERE clauses run just fine when I run the mysqldump on the command-line.

UPDATE 2: I added the -vvv flag to the mysqldump command. On a failed backup, I get the following in my dump file:

-- Connecting to 127.0.0.1...

-- Starting transaction...

-- Setting savepoint...

-- Retrieving table structure for table tablename...

-- SELECT query...

The next line after this on a successful dump would read:

-- Retrieving rows...