2018-12-10

HW5 Troubleshooting .

'''mysqlimport --secure-file-priv'''

One problem that someone else in the class and I ran into was an error saying something along the lines that "--secure-file-priv" was preventing the mysqlimport line from executing. This is a value that belongs to the server that is hosting your SQL database. The internet states in several places that a solution to this is using the "--local" option when running mysqlimport, but I only got other errors from this and ultimately something different worked for me:

If you installed MySQL through the MySQL website and did the whole installer process, you hopefully have a program on your computer called MySQL Workbench. If you open this and access your database, on the left there's a button in the navigation called Options File. Click this and then the Security tab, scroll down and there should be the --secure-file-priv setting. Do not uncheck the box, just delete any text in the box to the right and then hit Apply.

If you didn't install MySQL through the website, you should have a my.ini file that has a --secure-file-priv line somewhere in it. Set it's value to "", which is just an empty string and should be equivalent to what I did through the Workbench.

'''mysqlimport options'''

If you created a comma-delimited .csv file for MovieExec and Studio like I did (I did mine through Excel), you may also get an error after --secure-file-priv that says the csv couldn't be read properly due to an error on line 1. Here are a few handy options you can use that fixed a series of weird problems I had:

--fields-terminated-by=,

Putting this in your mysqlimport line will properly split each line by commas. I believe the default is a tab, or \t, so you will probably need this option.

--lines-terminated-by=\r\n

You can probably guess that this splits each row by a carriage return \r and a new line \n. Some people may not have this same problem that I did, but my csv had a \r\n after every line instead of a \n. I think just a \n is default, so I also needed this option.

--ignore-lines=1

This line makes mysqlimport ignore the first entire line of the csv file. At first I didn't see any reason to use this because I didn't add headers to my csv file. But then I saw an unusual problem where the very first row that mysqlimport brought in would have a question mark at the front, and I think this problem was caused by my \r\n in the lines-terminated-by option. So to fix that I just added column headers to my csv files through Excel and then used this ignore-lines option.

'''path and classpath'''

There are about 100 ways of doing this and I think anyone who has used Java for a class in the past knows that this problem is oddly frustrating. Or maybe I'm the only one. Every time I want to execute the javac command I have to first specify my Java \bin folder, which for some reason has never worked properly through the Windows Environment Variables settings. If you encounter a problem like "javac is not a recognized program", try this:

set path=%path%;C:\Program Files\Java\jdk1.8.0_161\bin

Of course, I probably don't have the same version of Java as you, so please check your Program Files folder and change the jdk1.8.0_161 to whatever your actual version of Java is.

Beyond that if you also get a "could not find the drivers specified" problem for JDBC, I fixed that in a similar way:

set classpath=%classpath%;your-path-here\mysql-connector-java-8.0.13.jar;

But again please set the proper path to the connector, and make sure you have the same version connector as me.

Those are all the main issues I faced. Feel free to use this thread to post your own troubleshooting problems and solutions.

(Edited: 2018-12-10)

I'd like to quickly post a few fixes to problems I encountered throughout HW5, while still making an effort to avoid doing anyone's homework for them. These are issues that aren't well documented and ended up giving me a headache for an hour trying to debug, largely due to changes in version of MySQL or simply because people assume certain things are common knowledge. '''mysqlimport --secure-file-priv''' One problem that someone else in the class and I ran into was an error saying something along the lines that "--secure-file-priv" was preventing the mysqlimport line from executing. This is a value that belongs to the server that is hosting your SQL database. The internet states in several places that a solution to this is using the "--local" option when running mysqlimport, but I only got other errors from this and ultimately something different worked for me: If you installed MySQL through the MySQL website and did the whole installer process, you hopefully have a program on your computer called MySQL Workbench. If you open this and access your database, on the left there's a button in the navigation called Options File. Click this and then the Security tab, scroll down and there should be the --secure-file-priv setting. Do not uncheck the box, just delete any text in the box to the right and then hit Apply. ((resource:01_secure-file-priv.png|Resource Description for 01_secure-file-priv.png)) If you didn't install MySQL through the website, you should have a my.ini file that has a --secure-file-priv line somewhere in it. Set it's value to "", which is just an empty string and should be equivalent to what I did through the Workbench. '''mysqlimport options''' If you created a comma-delimited .csv file for MovieExec and Studio like I did (I did mine through Excel), you may also get an error after --secure-file-priv that says the csv couldn't be read properly due to an error on line 1. Here are a few handy options you can use that fixed a series of weird problems I had: --fields-terminated-by=, Putting this in your mysqlimport line will properly split each line by commas. I believe the default is a tab, or \t, so you will probably need this option. --lines-terminated-by=\r\n You can probably guess that this splits each row by a carriage return \r and a new line \n. Some people may not have this same problem that I did, but my csv had a \r\n after every line instead of a \n. I think just a \n is default, so I also needed this option. --ignore-lines=1 This line makes mysqlimport ignore the first entire line of the csv file. At first I didn't see any reason to use this because I didn't add headers to my csv file. But then I saw an unusual problem where the very first row that mysqlimport brought in would have a question mark at the front, and I think this problem was caused by my \r\n in the lines-terminated-by option. So to fix that I just added column headers to my csv files through Excel and then used this ignore-lines option. '''path and classpath''' There are about 100 ways of doing this and I think anyone who has used Java for a class in the past knows that this problem is oddly frustrating. Or maybe I'm the only one. Every time I want to execute the javac command I have to first specify my Java \bin folder, which for some reason has never worked properly through the Windows Environment Variables settings. If you encounter a problem like "javac is not a recognized program", try this: set path=%path%;C:\Program Files\Java\jdk1.8.0_161\bin Of course, I probably don't have the same version of Java as you, so please check your Program Files folder and change the jdk1.8.0_161 to whatever your actual version of Java is. Beyond that if you also get a "could not find the drivers specified" problem for JDBC, I fixed that in a similar way: set classpath=%classpath%;your-path-here\mysql-connector-java-8.0.13.jar; But again please set the proper path to the connector, and make sure you have the same version connector as me. ---- Those are all the main issues I faced. Feel free to use this thread to post your own troubleshooting problems and solutions.

-- HW5 Troubleshooting

Thanks David! The error I've been getting was from not putting in lines-terminated-by.          
This helped!

Thanks David! The error I've been getting was from not putting in lines-terminated-by. This helped!

Query Statistics

https://www.yioop.com/thread/9606

Total Elapsed Time for Queries: 0.05127239227294922 seconds.

SELECT LOCALE_NAME, WRITING_MODE FROM LOCALE WHERE LOCALE_TAG ='en-US'

Time: 0.0001139640808105469 seconds.

SELECT COALESCE(MAX(UPDATE_TIMESTAMP), 0) AS MOST_RECENT FROM ITEM_IMPRESSION_SUMMARY WHERE USER_ID = 2 AND ITEM_TYPE = 3 AND ITEM_ID IN (SELECT GROUP_ID FROM USER_GROUP WHERE USER_ID = 2 AND STATUS = 1) AND UPDATE_PERIOD = -4

Time: 0.0003290176391601562 seconds.

DELETE FROM ITEM_IMPRESSION_SUMMARY WHERE USER_ID=? AND ITEM_ID=? AND ITEM_TYPE=? AND UPDATE_PERIOD = -4
Array ( [0] => 2 [1] => 9606 [2] => 1 )

Time: 0.0035400390625 seconds.

INSERT INTO ITEM_IMPRESSION_SUMMARY VALUES (?, ?, ?, -4, ?, 0, -1, -1) ON CONFLICT DO NOTHING
Array ( [0] => 2 [1] => 9606 [2] => 1 [3] => 1789974786 )

Time: 0.00251007080078125 seconds.

UPDATE ITEM_IMPRESSION_SUMMARY SET NUM_VIEWS = NUM_VIEWS + 1 WHERE USER_ID=? AND ITEM_ID=? AND ITEM_TYPE=? AND UPDATE_PERIOD = -2 AND UPDATE_TIMESTAMP = 0
Array ( [0] => 2 [1] => 9606 [2] => 1 )

Time: 0.0001018047332763672 seconds.

DELETE FROM ITEM_IMPRESSION_SUMMARY WHERE USER_ID=? AND ITEM_ID=? AND ITEM_TYPE=? AND UPDATE_PERIOD = -4
Array ( [0] => 2 [1] => 303 [2] => 3 )

Time: 0.002427816390991211 seconds.

INSERT INTO ITEM_IMPRESSION_SUMMARY VALUES (?, ?, ?, -4, ?, 0, -1, -1) ON CONFLICT DO NOTHING
Array ( [0] => 2 [1] => 303 [2] => 3 [3] => 1789974786 )

Time: 0.002074956893920898 seconds.

UPDATE ITEM_IMPRESSION_SUMMARY SET NUM_VIEWS = NUM_VIEWS + 1 WHERE USER_ID=? AND ITEM_ID=? AND ITEM_TYPE=? AND UPDATE_PERIOD = -2 AND UPDATE_TIMESTAMP = 0
Array ( [0] => 2 [1] => 303 [2] => 3 )

Time: 8.702278137207031E-5 seconds.

SELECT COUNT(GI.ID) AS NUM FROM GROUP_ITEM GI WHERE GI.GROUP_ID IN (SELECT GROUP_ID FROM USER_GROUP WHERE USER_ID = ? AND STATUS = 1) AND GI.TITLE NOT LIKE ? AND GI.PUBDATE > ?
Array ( [0] => 2 [1] => %2% [2] => 1789974782 )

Time: 0.0004069805145263672 seconds.

SELECT PARENT_ID FROM GROUP_ITEM WHERE GROUP_ID=? AND USER_ID=? AND TITLE=? LIMIT 1
Array ( [0] => -1 [1] => 2 [2] => 2-4 )

Time: 0.0001900196075439453 seconds.

SELECT PARENT_ID FROM GROUP_ITEM WHERE GROUP_ID=? AND USER_ID=? AND TITLE=? LIMIT 1
Array ( [0] => -1 [1] => 2 [2] => 2-37 )

Time: 6.103515625E-5 seconds.

SELECT PARENT_ID FROM GROUP_ITEM WHERE GROUP_ID=? AND USER_ID=? AND TITLE=? LIMIT 1
Array ( [0] => -1 [1] => 2 [2] => 2-202 )

Time: 5.102157592773438E-5 seconds.

SELECT PARENT_ID FROM GROUP_ITEM WHERE GROUP_ID=? AND USER_ID=? AND TITLE=? LIMIT 1
Array ( [0] => -1 [1] => 2 [2] => 2-687 )

Time: 5.006790161132812E-5 seconds.

SELECT PARENT_ID FROM GROUP_ITEM WHERE GROUP_ID=? AND USER_ID=? AND TITLE=? LIMIT 1
Array ( [0] => -1 [1] => 2 [2] => 2-1115 )

Time: 3.314018249511719E-5 seconds.

SELECT PARENT_ID FROM GROUP_ITEM WHERE GROUP_ID=? AND USER_ID=? AND TITLE=? LIMIT 1
Array ( [0] => -1 [1] => 2 [2] => 2-1138 )

Time: 5.006790161132812E-5 seconds.

SELECT PARENT_ID FROM GROUP_ITEM WHERE GROUP_ID=? AND USER_ID=? AND TITLE=? LIMIT 1
Array ( [0] => -1 [1] => 2 [2] => 2-1140 )

Time: 3.218650817871094E-5 seconds.

SELECT PARENT_ID FROM GROUP_ITEM WHERE GROUP_ID=? AND USER_ID=? AND TITLE=? LIMIT 1
Array ( [0] => -1 [1] => 2 [2] => 2-1149 )

Time: 3.099441528320312E-5 seconds.

SELECT PARENT_ID FROM GROUP_ITEM WHERE GROUP_ID=? AND USER_ID=? AND TITLE=? LIMIT 1
Array ( [0] => -1 [1] => 2 [2] => 2-1152 )

Time: 3.600120544433594E-5 seconds.

SELECT * FROM GROUP_ITEM WHERE ID=? LIMIT 1
Array ( [0] => 9606 )

Time: 0.0001900196075439453 seconds.

SELECT OPTIONS FROM SOCIAL_GROUPS WHERE GROUP_ID = ?
Array ( [0] => 303 )

Time: 6.008148193359375E-5 seconds.

SELECT COUNT(DISTINCT GI.ID) AS NUM FROM GROUP_ITEM GI, SOCIAL_GROUPS G, USER_GROUP UG, USERS O WHERE GI.PARENT_ID='9606' AND NOT LOWER(group_name) LIKE LOWER('Personal$%') AND (UG.USER_ID='2' OR G.REGISTER_TYPE IN ('4','3') ) AND GI.USER_ID=O.USER_ID AND GI.GROUP_ID=G.GROUP_ID AND GI.GROUP_ID=UG.GROUP_ID AND (( G.MEMBER_ACCESS IN ('2','3','4', '5')) OR (G.OWNER_ID = UG.USER_ID OR UG.USER_ID = '1'))

Time: 0.0007610321044921875 seconds.

SELECT DISTINCT GI.ID AS ID, GI.PARENT_ID AS PARENT_ID, GI.GROUP_ID AS GROUP_ID, GI.TITLE AS TITLE, GI.DESCRIPTION AS DESCRIPTION, GI.FLAG AS FLAG, GI.PUBDATE AS PUBDATE, GI.EDIT_DATE AS EDIT_DATE, G.OWNER_ID AS OWNER_ID, G.MEMBER_ACCESS AS MEMBER_ACCESS, G.GROUP_NAME AS GROUP_NAME, P.USER_NAME AS USER_NAME, P.USER_ID AS USER_ID, GI.TYPE AS TYPE, GI.UPS AS UPS, GI.DOWNS AS DOWNS, G.VOTE_ACCESS AS VOTE_ACCESS FROM GROUP_ITEM GI, SOCIAL_GROUPS G, USER_GROUP UG, USERS P WHERE GI.PARENT_ID='9606' AND NOT LOWER(group_name) LIKE LOWER('Personal$%') AND (UG.USER_ID='2' OR G.REGISTER_TYPE IN ('4','3') ) AND GI.GROUP_ID=G.GROUP_ID AND GI.GROUP_ID=UG.GROUP_ID AND (( G.MEMBER_ACCESS IN ('2','3','4', '5')) OR (G.OWNER_ID = UG.USER_ID OR UG.USER_ID = '1')) AND GI.PARENT_ID NOT IN (SELECT DISCUSS_THREAD FROM GROUP_PAGE WHERE TITLE LIKE '%$$%' AND DISCUSS_THREAD IS NOT NULL) AND P.USER_ID = GI.USER_ID ORDER BY GI.PUBDATE ASC LIMIT 10 OFFSET 0

Time: 0.03328490257263184 seconds.

SELECT OPTIONS FROM SOCIAL_GROUPS WHERE GROUP_ID = ?
Array ( [0] => 303 )

Time: 0.0004849433898925781 seconds.

SELECT OPTIONS FROM SOCIAL_GROUPS WHERE GROUP_ID = ?
Array ( [0] => 303 )

Time: 0.0002400875091552734 seconds.

SELECT * FROM GROUP_ITEM WHERE ID=? LIMIT 1
Array ( [0] => 9606 )

Time: 0.0004119873046875 seconds.

SELECT OPTIONS FROM SOCIAL_GROUPS WHERE GROUP_ID = ?
Array ( [0] => 303 )

Time: 0.0001590251922607422 seconds.

SELECT STATUS FROM USERS WHERE USER_ID = ?
Array ( [0] => 2 )

Time: 0.0003771781921386719 seconds.

SELECT USER_NAME FROM USERS WHERE USER_ID = ?
Array ( [0] => 2 )

Time: 0.0001859664916992188 seconds.

SELECT * FROM VISITOR WHERE ADDRESS = :address AND PAGE_NAME = :page_name LIMIT 1
Array ( [:address] => 216.73.216.124 [:page_name] => forbidden_time_out )

Time: 0.0004358291625976562 seconds.

SELECT COUNT(DISTINCT G.GROUP_ID) AS NUM FROM USER_GROUP UG, SOCIAL_GROUPS G WHERE UG.USER_ID = ? AND UG.GROUP_ID = G.GROUP_ID AND ( UG.STATUS = 1 OR UG.STATUS = 5)
Array ( [0] => 2 )

Time: 0.0003340244293212891 seconds.

SELECT G.GROUP_ID AS GROUP_ID FROM SOCIAL_GROUPS G WHERE G.GROUP_NAME = ?
Array ( [0] => Personal$2 )

Time: 0.0001389980316162109 seconds.

SELECT USER_ID FROM USER_GROUP WHERE GROUP_ID = ?
Array ( [0] => -1 )

Time: 6.508827209472656E-5 seconds.

SELECT G.GROUP_ID AS GROUP_ID, G.GROUP_NAME AS GROUP_NAME, G.OWNER_ID AS OWNER_ID, O.USER_NAME AS OWNER, REGISTER_TYPE, UG.STATUS AS STATUS, G.MEMBER_ACCESS AS MEMBER_ACCESS, G.VOTE_ACCESS AS VOTE_ACCESS, G.POST_LIFETIME AS POST_LIFETIME, UG.JOIN_DATE AS JOIN_DATE, G.OPTIONS AS OPTIONS, G.RENDER_ENGINE AS RENDER_ENGINE, G.GROUP_THEME AS GROUP_THEME, G.PAGE_HEADER AS PAGE_HEADER, G.PAGE_FOOTER AS PAGE_FOOTER FROM SOCIAL_GROUPS G, USERS O, USER_GROUP UG WHERE (UG.USER_ID = :user_id) AND UG.GROUP_ID= :group_id AND UG.GROUP_ID=G.GROUP_ID AND OWNER_ID = O.USER_ID LIMIT 1
Array ( [:group_id] => 303 [:user_id] => 2 )

Time: 0.0001969337463378906 seconds.

SELECT RENDER_ENGINE FROM SOCIAL_GROUPS WHERE GROUP_ID = ?
Array ( [0] => -2 )

Time: 0.000514984130859375 seconds.

SELECT RENDER_ENGINE FROM SOCIAL_GROUPS WHERE GROUP_ID = ?
Array ( [0] => 303 )

Time: 0.0001080036163330078 seconds.

SELECT RENDER_ENGINE FROM SOCIAL_GROUPS WHERE GROUP_ID = ?
Array ( [0] => 303 )

Time: 0.0002040863037109375 seconds.

SELECT G.GROUP_ID AS GROUP_ID, G.GROUP_NAME AS GROUP_NAME, G.OWNER_ID AS OWNER_ID, O.USER_NAME AS OWNER, REGISTER_TYPE, UG.STATUS AS STATUS, G.MEMBER_ACCESS AS MEMBER_ACCESS, G.VOTE_ACCESS AS VOTE_ACCESS, G.POST_LIFETIME AS POST_LIFETIME, UG.JOIN_DATE AS JOIN_DATE, G.OPTIONS AS OPTIONS, G.RENDER_ENGINE AS RENDER_ENGINE, G.GROUP_THEME AS GROUP_THEME, G.PAGE_HEADER AS PAGE_HEADER, G.PAGE_FOOTER AS PAGE_FOOTER FROM SOCIAL_GROUPS G, USERS O, USER_GROUP UG WHERE (UG.USER_ID = :user_id OR G.REGISTER_TYPE IN (3,4)) AND UG.GROUP_ID= :group_id AND UG.GROUP_ID=G.GROUP_ID AND OWNER_ID = O.USER_ID LIMIT 1
Array ( [:group_id] => 303 [:user_id] => 2 )

Time: 0.0007739067077636719 seconds.

SELECT STATUS FROM USER_GROUP WHERE USER_ID=? AND GROUP_ID=? LIMIT 1
Array ( [0] => 2 [1] => 303 )

Time: 0.0002191066741943359 seconds.