Debugging cron jobs

  • Open /etc/rsyslog.conf (RHEL 7.x) and check for following line:
# Log cron stuff
cron.*                                                  /var/log/cron
  • The path in previous step gives the filename to which cronjob runs will be logged. Open that file. Whenever a cronjob is executed, an entry is logged into this file. Example below:
Aug 30 01:00:06 azrxxxnval0001 CROND[30300]: (siddjain) CMD (/home/siddjain/cleanup.sh)

If you don’t see an entry, it means your job is not getting scheduled. If this is the problem, check if the cron service is even running.

  • The previous file tells if your job was executed or not, but does not tell if it ran into some error while executing. To find that out look in a file /var/mail/<username>. Example below:
/bin/sh: /home/siddjain/cleanup.sh: Permission denied
  • This tells us the job was executed but ran into an error. To check for file permissions use the namei utility:
$ namei -om /home/siddjain/cleanup.sh
f: /home/siddjain/cleanup.sh
 dr-xr-xr-x root    root   /
 drwxr-xr-x root    root   home
 drwx------ siddjain zusers siddjain
 -rw-r--r-- siddjain zusers cleanup.sh

  • By now its clear what the problem is:
$ chmod +x /home/siddjain/cleanup.sh

Cron jobs run under the environment of the user.

Checking if cron service is running

Sometimes the problem is that cron service is not running. You can check it as follows (on Red Hat 7.x):

#-> service crond status
Redirecting to /bin/systemctl status crond.service
● crond.service - Command Scheduler
   Loaded: loaded (/usr/lib/systemd/system/crond.service; disabled; vendor preset: enabled)
   Active: inactive (dead)

To start the service run:

#-> service crond start
Redirecting to /bin/systemctl start crond.service

Check the status again:

#-> service crond status
Redirecting to /bin/systemctl status crond.service
● crond.service - Command Scheduler
   Loaded: loaded (/usr/lib/systemd/system/crond.service; disabled; vendor preset: enabled)
   Active: active (running) since Wed 2021-09-08 13:29:43 EDT; 4s ago
 Main PID: 4781 (crond)
    Tasks: 1
   Memory: 732.0K
   CGroup: /system.slice/crond.service
           └─4781 /usr/sbin/crond -n

Sep 08 13:29:43 azrxxxnval0001 systemd[1]: Started Command Scheduler.
Sep 08 13:29:43 azrxxxnval0001 crond[4781]: (CRON) INFO (RANDOM_DELAY will be scaled with factor 53% if used.)
Sep 08 13:29:43 azrxxxnval0001 crond[4781]: (CRON) INFO (running with inotify support)

Crontab Commands

crontab -l

List cronjobs of current user

crontab -e

Edit the current user’s cronjobs

References

Posted in Software | Tagged , | Leave a comment

When the temperature record broke in Bellevue

Date June 28 2021

source: https://www.ncdc.noaa.gov/

https://www.washington.edu/uwem/2021/07/13/hottest-day-ever-in-seattle-june—2021/#:~:text=On%20June%2028%2C%202021%2C%20the,%2C%20an%20all%2Dtime%20record.

Posted in Personal Interests | Leave a comment

Remembering old days

Posted in Money | Leave a comment

Just for laughing

Just for laughing

Subject: 🤣🤣🤣

❌❌❌❌❌❌❌❌❌❌

Smart answer by a female passenger on a flight…

A guy asked a beautiful lady sitting next to him…’Nice perfume…..which one is it?…😍 I want to give it to my wife..!!’

Lady: ‘Don’t give her….some idiot will find an excuse to talk to her..!!’😜😜😜

❌❌❌❌❌❌❌❌❌❌

A letter from a teacher to a parent:

Dear Parent, Edward doesn’t smell nice in class. Please try to bath him.

Parent’s answer:

Dear Teacher, Edward is not a rose, Don’t smell him,Teach him ……😂😂😝😝

❌❌❌❌❌❌❌❌❌❌

Mother to Son: Who is Sultan Aziz?

Son : Don’t know 🤔

Mother : Devote some time to pay attention to study also

Son to Mother : Do you know Aunty Jennifer?

Mother : Don’t know

Son: Sometimes pay attention to Daddy also 😝😜😜

❌❌❌❌❌❌❌❌❌❌

A cute excuse:

Teacher: Why are you late?

Student: Mom & dad were fighting.

Teacher: So what makes you late if they were fighting?

Student: One of my shoes was in mom’s hand, and the other in dad’s..😂😂😝😜


❌❌❌❌❌❌❌❌❌❌

Wife: I hate that beggar.

Husband: Why?

Wife: Rascal, yesterday I gave him food. Today he gave me a book on”How to Cook !!!😡👌😂😜😃😄

❌❌❌❌❌❌❌❌❌❌

Husband came home drunk.

To avoid wife’s scolding, he took a laptop & started working.

Wife: Did u drink?????

Husband : no!

Wife:  Idiot!!! then why are you typing on a suitcase?!!!😜😂😝🍻👍


❌❌❌❌❌❌❌❌❌❌

Don’t laugh alone. Pass it on 😂😂😂 to your buddies.

Posted in Jokes | Leave a comment

First, Break All The Rules

Recently I came across this AMAZING book on management – everyone should read it whether you are a manager or not. Making some notes so I can refer quickly later on:

After this the book says: employees don’t leave their company. They leave their manager.

The book distills down the job of a manger is to reach out within each employee and extract the maximum performance out of them. It sounds a bit evil but phrased differently its saying to help each individual reach their full potential. I think this used to be Microsoft’s mission statement at one point – one that I loved.

The book also makes it important to distinguish and differentiate a manager from a leader. Managers took inwards within a company. Leaders look outward.

The book identifies and differentiates 3 things we are made of:

  • Skills: the ability to do something well; expertise (dictionary definition)
  • Knowledge: facts, information, and skills acquired by a person through experience or education; the theoretical or practical understanding of a subject. (dictionary definition)
  • Talents: natural aptitude or skill. (dictionary definition)

Skills can be taught e.g., cooking. Knowledge can be accumulated over time and persistent practice. But talent cannot be taught or accumulated. It is your innate ability that you were born with. You either have it or not as part of how your brain is wired. The dictionary definition of talent makes a reference to skill. I think this is no coincidence – the two are related. The difference between talent and skill is that talent can be thought of as that skill which you just cannot acquire because your brain isn’t wired that way.

Anyway what’s the upshot? The upshot is that stop trying to fix your non-talents and instead capitalize on the talents you do have. If everyone could acquire every skill, knowledge and talent then everyone could be perfect. But reality is that every person is unique and not everyone can keep on rising and become company CEO.

Also this: https://karwin.com/blog/index.php/category/management/

Posted in Career | Leave a comment

invalid literal for int() with base 10


UPDATE: It turns out none of this works. I ran into the same error with latest VS Code and ms-python extension. The fix was to stick a breakpoint before the input command to prevent VS Code from taking conda activate base as input; essentially we need to pause and give it some time.


Got this error trying to debug Python script from VS Code (the script runs fine when executed outside of VS Code e.g., on a Bash terminal):

invalid literal for int() with base 10

After some Googling, the first thing to check is if you have console set to integratedTerminal in launch.json. That was the case in my case so that didn’t really help.

The Python input function is supposed to prompt the user for input. But I didn’t get any prompt from VS Code asking me for input. Just that error:

Output of integratedTerminal

I did more search and then noticed I was using older version of ms-python extension. I had

older version

which is an older version. So maybe upgrading to latest would fix. The problem is VS Code would not display newer version! Clicking on the gear icon above pops up this menu where you see an option to Install Another Version…

But clicking on it didn’t do anything. It gave me a 404 error notification.

So I then downloaded the .vsix file of latest version from https://github.com/microsoft/vscode-python/releases. VS Code has a Install from VSIX… option. See below for tip on where to find it:

Install from VSIX…

I tried installing using that option which gave me an error saying the version was incompatible with the version of VS Code I had.

VS Code 1.45.1

So the next step was to upgrade VS Code itself. To do this download the latest installer – comes as a zip file named VSCode-Darwin-Universal.zip. Unzip it and simply move the unzipped file to the Applications folder in Finder on Mac. You will get a prompt whether to keep both versions or replace. I choose to replace the older version with new one. You will also get some prompts asking for authentication since only administrator can make changes to Applications folder.

Now you can Install from VSIX… but before you do that, uninstall the old ms-python extension. Clicking on uninstall and reloading VS Code didn’t really uninstall it. I could still see the extension under ~/.vscode/extensions/. A useful directory to remember. I ran a rm -r command to forcefully remove it from the system:

$ rm -r ~/.vscode/extensions/ms-python.python-2020.6.91350

Then I installed from VSIX… and the May 2021 version of ms-python got installed. The original error also went away. Some notices I got. Documenting for reference later on:

References

Posted in Software | Tagged , , | Leave a comment

Databases 101

Posted in Software | Leave a comment

Understanding dates and time in MySQL

TL;DR: Set your server timezone and connection time zone to both ‘+00:00’ and use datetime instead of timestamp when you want to store dates outside the range supported by timestamp ('1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999').

Do’s when using datetime:

  • Add a suffix to the column name to reflect the time zone in which the dates are stored. Thus instead of naming your column registration_date name it registration_date_est to reinforce the fact that the column stores dates in EST for example.
  • Do any time zone conversion in your application code. When you execute the insert statement it should have the date in “absolute” format (YYYY-MM-DD HH:MM:SS) without any time zone suffix or offset. This will skip any conversions MySQL would do on the date otherwise and will protect you from any changes to the server’s time zone your DB administrator might do.
  • When we insert a date in “absolute” form i.e., YYYY-MM-DD HH:MM:SS in a datetime or timestamp column, MySQL assumes the date is in the session’s time zone. And by default the session time zone is equal to the server’s time zone unless you override it explicitly.
  • datetime column converts date into the session’s time zone at time of insertion. The date is stored in the form YYYY-MM-DD HH:MM:SS and has no time zone information associated with it. All subsequent select queries will return the stored string irrespective of whatever the session or server time zone is at time of select query. By contrast a timestamp column converts date to UTC offset in seconds. So the date is stored in UTC or Zulu time zone. But all select queries convert the stored date into the session’s time zone and return the date formatted as YYYY-MM-DD HH:MM:SS

where do we start? If you don’t have a MySQL server provisioned, you can provision one easily using Docker:

docker container create \
	--name mysql \
	--network bridge \
    -p 3306:3306 \
    -p 33060:33060 \
	--log-opt max-file=3 \
 	--log-opt max-size=3m \
	--workdir /home \
	--env MYSQL_ROOT_PASSWORD=ib7U8Tz9K20= \
	--env MYSQL_DATABASE=test \
	--env MYSQL_USER=test_user \
	--env MYSQL_PASSWORD=Hq5tPQnVM2k= \
	--env TZ=UTC \
  mysql:8.0.24 \
  mysqld --default-authentication-plugin=mysql_native_password

docker start mysql

All examples in this post are using above MySQL container that uses MySQL version 8.0.24. The reason for using mysql_native_password is explained in https://github.com/docker-library/wordpress/issues/313

system_time_zone vs. global time_zone vs. session time_zone

The TZ environment variable is used to set the system time zone. The default value is UTC which is same as +00:00.

mysql> show global variables like '%zone%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| system_time_zone | UTC    | --> this can be changed using TZ environment variable as per docs. this is the time zone of the OS.
| time_zone        | SYSTEM | --> change this in my.cnf file under mysqld or using SET GLOBAL time_zone = <timezone>; this is the time zone of mysql server.
+------------------+--------+
2 rows in set (0.01 sec)

This page is must read:https://dev.mysql.com/doc/mysql-g11n-excerpt/5.7/en/time-zone-support.html MySQL Server maintains several time zone settings:

* The system time zone. When the server starts, it attempts to determine the time zone of the host machine automatically and uses it to set the system_time_zone system variable. The value does not change thereafter.

* The server current time zone. The global time_zone system variable indicates the time zone the server currently is operating in. The initial time_zone value is ‘SYSTEM’, which indicates that the server time zone is the same as the system time zone

assuming you have a MySQL server provisioned, log into the server and run:

mysql> set global time_zone='+01:00';
Query OK, 0 rows affected (0.00 sec)

The server or global time_zone can also be changed in /etc/mysql/conf.d/mysql.cnf. Add following lines to the file as example:

[mysqld]
default-time-zone='+00:00'

Below command shows how to set the session time zone:

mysql> set @@time_zone='-07:00';
Query OK, 0 rows affected (0.01 sec)

The server or global time zone is intrinsic to the server and the connection or session time zone is scoped to that connection or session. The two can be different but the learning in this lesson is not to do that to avoid confusion and keep things simple.

mysql> SELECT @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| +01:00 | -07:00 |
+--------------------+---------------------+
1 row in set (0.00 sec)

The timezones can also be set using friendly names such as America/New York but this relies on the presence of a table what MySQL looks up internally to get the numerical offset. If this table does not exist named timezones will not work. From MySQL documentation:

Named time zones can be used only if the time zone information tables in the mysql database have been created and populated. Otherwise, use of a named time zone results in an error

https://dev.mysql.com/doc/mysql-g11n-excerpt/8.0/en/time-zone-support.html

Datetime vs. Timestamp

MySQL supports two data types to store datetime values – datetime and timestamp. There are subtle differences between the two. To illustrate the difference, create a table with a datetime and timestamp column in it:

mysql> create table test1 (row_id int unsigned auto_increment, timestamp timestamp, datetime datetime, primary key (row_id) ) engine=innodb;
Query OK, 0 rows affected (0.02 sec)

Next we insert same date (ISO formatted) in both the columns:

mysql> insert into test1 (timestamp, datetime) values ('2016-05-25T09:08:34.123+06:00', '2016-05-25T09:08:34.123+06:00');
Query OK, 1 row affected (0.01 sec)

Now what do you expect when you do a select?

mysql> select * from test1;
+--------+---------------------+---------------------+
| row_id | timestamp | datetime |
+--------+---------------------+---------------------+
| 1 | 2016-05-24 20:08:34 | 2016-05-24 20:08:34 |
+--------+---------------------+---------------------+
1 row in set (0.00 sec)

Explanation:

9-6-7+24
20

9 for the original 09:08:34 in our insert statement
-6 to convert to GMT from +06:00 (refer insert statement)
-7 to convert to session time zone which is -07:00 (refer @@session.time_zone)
24 for modulo 24 arithmetic

change the session time zone:

mysql> set @@time_zone='+03:00';

and do select again. what do you expect?

mysql> select * from test1;
+--------+---------------------+---------------------+
| row_id | timestamp | datetime |
+--------+---------------------+---------------------+
| 1 | 2016-05-25 06:08:34 | 2016-05-24 20:08:34 |
+--------+---------------------+---------------------+
1 row in set (0.00 sec)

Explanation:

9-6+3
6

the timestamp field changes but not datetime and that is the primary difference between timestamp and datetime.

change the server time zone:

mysql> set global time_zone='+05:00';

and do select again. what do you expect?

mysql> select * from test1;
+--------+---------------------+---------------------+
| row_id | timestamp | datetime |
+--------+---------------------+---------------------+
| 1 | 2016-05-25 06:08:34 | 2016-05-24 20:08:34 |
+--------+---------------------+---------------------+
1 row in set (0.00 sec)

we get back same result. so its the connection time zone that matters or overrides the server time zone in case the two are different.

we can see this with the CURRENT_TIMESTAMP function as well.

mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2021-06-09 01:59:11 |
+---------------------+
1 row in set (0.00 sec)

change session time_zone

mysql> set @@time_zone = '-09:00';
Query OK, 0 rows affected (0.00 sec)
mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2021-06-08 13:59:51 |
+---------------------+
1 row in set (0.00 sec)

change server time_zone:

mysql> set global time_zone='+04:00';
Query OK, 0 rows affected (0.00 sec)
mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2021-06-08 14:00:33 |
+---------------------+
1 row in set (0.00 sec)

note the timestamp did not change in accordance with server time_zone. This is documented in MySQL manual as well:

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_now:

The value is expressed in the session time zone.

Another way to understand the essential difference between datetime and timestamp is that datetime converts time into session’s time zone at time of insertion whereas timestamp stores time in UTC and converts this time into the session’s time zone at time of retrieval.

Let’s explain this further.

When you insert a time (e.g., 2016-05-25T09:08:34.123+06:00) into a datetime column, datetime converts that time into the session’s time zone. And remember by default session time zone will equal the server’s time zone unless you change it explicitly using select @@time_zone query. So if the session time zone is UTC-07:00 for example, the time becomes

2016-05-24 20:08:34

as we saw earlier and this is the value that is stored and that you will get back from MySQL when you do a select query. There is no time zone stored. Time zone is essentially like units (km vs. mi if we were measuring distance e.g.). When you do a select query on a datetime column, to make sense of the returned time, you should know what the time zone was when the value was inserted into the datetime column. Because a datetime column does not store the time zone information in it, a good practice to adopt is to add a suffix to the column name which tells the user the time zone the date is in. Thus rather than naming your column registration_date name it registration_date_est to reflect the fact that the dates stored in the column are in US Eastern time zone.

Now contrast with timestamp. When you insert 2016-05-25T09:08:34.123+06:00 into a timestamp column, the time is converted to UTC – more accurately the offset from the UTC epoch (January 1, 1970, 00:00:00) in seconds which is 1,464,145,714 in this case and this is the value that gets stored internally. But when you do a select query on timestamp column, the stored time is converted into the current session’s time zone before being returned to the client. So if the current time zone is UTC+03:00 the returned value will be 2016-05-25T06:08:34.123 (explanation: 9-6+3 is 6). And the return value will change if we change the session’s time zone.

This is really the difference between datetime and timestamp and the reason timestamp can store time only up to 2038-01-19 03:14:07.999999 is because the max value a 4 byte signed integer can hold is 2,147,483,647 and that is the UTC offset corresponding to 2038-01-19 03:14:07.999999

WARNING: When using datetime you should never change the session time zone during the course of your application’s lifetime because if you do so, the two sets of dates will now be in different time zones. E.g., say the session time zone was EST and you stored 2021-06-10 19:20:19 in a row. Then a week later you – or the DB administrator – decided to change the server time zone to PST. Unless you are explicitly setting session time zone to EST in your application code (Node.js, Java etc.), the new dates will now be stored in PST while the earlier dates are in EST. This is likely to break your application!

Next, we will demo the fact that there is no conversion to the session’s time zone when inserting values into a datetime column if the input date does not have an explicit time zone offset. To demo this, we will run following command:

mysql> insert into test1 (datetime) values('2021-06-10 09:22:16');
Query OK, 1 row affected (0.00 sec)

Now let’s query the datetime:

| row_id | timestamp           | datetime            |
|      2 | NULL                | 2021-06-10 09:22:16 |

We can see no conversion happened. This is because if no time zone offset has been explicitly specified, then MySQL assumes the input date is already in the session’s time zone – a reasonable assumption to make. We can use this fact to protect ourselves from our DB administrator who may change the server time zone. If our application is setup so that it inserts dates in “absolute” format, our dates will be immune to undesired MySQL conversions.

When we have a datetime defined like this for example:

`datetime` datetime default current_timestamp

it auto-inserts the current_timestamp in the datetime column when a row is added. Functionally, its equivalent to calling:

mysql> insert into test1 (datetime) values(CURRENT_TIMESTAMP());

And since current_timestamp will return the date in the session’s time zone with no explicit offset, MySQL will not do any conversion to the value while inserting it into the datetime column. This gives us the expected behavior and “does the right thing”.

How does the timestamp column behave if the date being inserted is in “absolute” format i.e., has no time zone offset included with it? Let’s try it (before running the command make sure your session time zone is set to something other than UTC):

mysql> insert into test1 (timestamp) values ('2021-06-10 09:40:25');
Query OK, 1 row affected (0.00 sec)

What do you expect when you do a select?

mysql> select * from test1;
+--------+---------------------+---------------------+
| row_id | timestamp           | datetime            |
+--------+---------------------+---------------------+
...
|      4 | 2021-06-10 09:40:25 | NULL                |
+--------+---------------------+---------------------+
4 rows in set (0.00 sec)

we get back same result. Again, MySQL assumes that since we did not specify the time zone offset the given time is in the session’s time zone. And is the right thing to do and gives sensible results.

Using Node.js mysql2 library

In part 2 of this post we discover gotchas related to Node.js mysql2 library. I prefer to use this library over mysql for two reasons:

  • first, it has support for Promises
  • second, it supports prepared statements

Let’s read the values using Node.js mysql2 library:

const mysql2 = require('mysql2');

const conn = mysql2.createConnection({
  host     : 'localhost',
  user     : 'root',
  password : 'xxx',
  port     : 3306,
  database : 'test',
  timezone : '+10:00'
});


conn.query('select * from test1', function(error, results, fields) {
     if (error) throw error;
     for (var row of results) {
         console.log(row);
     }
     conn.end();
  });

What do you expect? Before we run let’s check values of the two time zones:

mysql> select @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| +04:00 | -09:00 |
+--------------------+---------------------+
1 row in set (0.00 sec)
bash-5.0# node read-values.js
TextRow {
row_id: 1,
timestamp: 2016-05-24T21:08:34.000Z,
datetime: 2016-05-24T10:08:34.000Z
}

Let’s understand the timestamp we got 2016-05-24T21:08:34.000Z:

9-6-10+4+24
21

9 for original 09:08:34.123 in our insert statement
-6 to convert it to GMT (refer offset in insert staement)
-10 comes from timezone in createConnection. This is opposite of correct behavior. The offset was positive but mysql2 applies it in the wrong direction (bug in mysql2)
+4 comes from @@global.time_zone. This is mysql2 doing its own thing. I don’t think it should be doing this.
+24 for modulo 24 arithmetic

But is this what we would expect? Based on our experiments with the CLI, we would have expected to get:

9-6+10
13

9-6 converts to GMT and then we apply the +10 offset in our timezone

Explanation of the difference: Reading the documentation and specifically as called out in this PR, the timezone property doesn’t set the session time_zone variable. Read and digest it again. The session time zone is set equal to the server’s time zone when the mysql2 connection is made. To change it, you have to execute a set @@time_zone query from mysql2 just like we did for the CLI. The node-mysql docs say that the timezone should be set equal to the  time_zone configured on the MySQL server. Thus in our case it should be set to +04:00. And what happens if we do that? We get:

9-6-4+4
3

It cancels out the time_zone on the server and we get correct time in UTC. This also explains the Z suffix in the returned value if you observe carefully.

Now let’s understand how we get 2016-05-24T10:08:34.000Z for the datetime. In this case the value that got stored in the datetime column was 2016-05-24 20:08:34 and

20-10
10

where -10 is due to the timezone in the createConnection command. But is this the expected value? You might think the expected value should be 2016-05-24 20:08:34 because as in the CLI case the server returns 2016-05-24 20:08:34 irrespective of the session timezone. And it seems mysql2 incorrectly applies the timezone “correction” in this case. But hold on. What if you had set mysql2 timezone equal to the value of the time zone when the time was inserted into the datetime column? At time of insertion the session time zone was ‘-07:00’. Try making this change in the code snippet and running it again. What do you get?

bash-5.0# node read-values.js
TextRow {
  row_id: 1,
  timestamp: 2016-05-25T10:08:34.000Z,
  datetime: 2016-05-25T03:08:34.000Z
}

The server would return 2016-05-24 20:08:34 and mysql2 applies timezone “correction” in the opposite direction to give 2016-05-25T03:08:34Z and this is the time in GMT that was inserted into the database. Note the Z suffix.

So the purpose of the timezone variable in mysql2 is to set it to what the session timezone was at time of insertion in case of datetime columns and set it to the current session’s timezone in case of timestamp columns. When you do this, mysql2 returns time from datetime or timestamp columns in GMT or Zulu timezone. Of course, if the current vs. past session timezones are different you can only do one and will only get either datetime correct or the timestamp correct. So the lesson is not to change your server’s time zone.

The timezone property in mysql2 has no effect on write operations when datetime or timestamps are inserted into the database. Its effect appears only on read (select) operations.

mysql> set global time_zone='-13:00';
Query OK, 0 rows affected (0.00 sec)
mysql> set time_zone='+05:00';
Query OK, 0 rows affected (0.00 sec)
mysql> select @@global.time_zone, @@session.time_zone;
+--------------------+---------------------+
| @@global.time_zone | @@session.time_zone |
+--------------------+---------------------+
| -13:00 | +05:00 |
+--------------------+---------------------+
1 row in set (0.00 sec)

bash-5.0# node read-values.js
TextRow {
row_id: 1,
timestamp: 2016-05-24T04:08:34.000Z,
datetime: 2016-05-24T10:08:34.000Z
}

Verify:

for the timestamp:

9-6-10-13+24
4

for the datetime changing the server time zone has no effect and we get back the same

2016-05-24T10:08:34.000Z

the @@session.time_zone we set in CLI has no effect on values returned from mysql2 as mysql2 is using a different session altogether from the CLI.

  • This – https://github.com/sidorares/node-mysql2/issues/1336 – is a bug I filed where timezone in connection makes no difference when updating a timestamp field using CURRENT_TIMESTAMP. But once we realize that mysql2 timezone does not set the connection time_zone, we can see why. The connection time_zone is always equal to the server timezone no matter what you set in mysql2 timezone property. Thus this property will have no effect on write operations.
  • following bug can also be understood in light of what we learned above: https://github.com/sidorares/node-mysql2/issues/1337

from mysql documentation, the range for DATETIME values is '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999', and the range for TIMESTAMP values is '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'

The MySQL client/server protocol is explained here: https://dev.mysql.com/doc/internals/en/client-server-protocol.html

Its fun to read the 6,000 page MySQL manual: https://downloads.mysql.com/docs/refman-8.0-en.pdf

Posted in Software | Tagged | Leave a comment

MySQL Foreign Keys

I used to think that the foreign key column has to be unique [ref] but that is not the case.

Consider the tables below where we have a parent -> child -> grandchild relationship. A competition has teams and a team has users in it:

mysql> CREATE TABLE competitions(
    ->     cID INT UNSIGNED AUTO_INCREMENT,
    ->     title text not null,
    ->     primary key (cid)
    -> ) engine=innodb CHARACTER SET utf8mb4;
Query OK, 0 rows affected (0.01 sec)

mysql> create table teams (
    ->     tID INT UNSIGNED AUTO_INCREMENT,
    ->     cid int unsigned not null,
    ->     name varchar(24) not null,
    ->     primary key (tid),
    ->     foreign key (cid) references competitions(cid)
    -> ) engine=innodb CHARACTER SET utf8mb4;
Query OK, 0 rows affected (0.01 sec)

mysql> create table users (
    ->     row_id int unsigned auto_increment,
    ->     uID INT UNSIGNED not null,
    ->     tid int unsigned not null,
    ->     cid int unsigned not null,
    ->     primary key (row_id),
    ->     unique key (cid, uid),
    ->     foreign key (tid) references teams(tid),
    ->     foreign key (cid) references teams(cid)  /* refers to non-unique column */
    -> ) engine=innodb CHARACTER SET utf8mb4;
Query OK, 0 rows affected (0.01 sec)

Create a few competitions:

mysql> insert into competitions (title) values ('olympics 2020'), ('wimbledon 2021'), ('
ICC world cup 2022');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

Now create a few teams:

mysql> insert into teams(cid, name) values (1, 'usa'), (1, 'china'), (2, 'germany'), (2, 'france'), (3, 'india'), (3, 'england');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

Now insert a user into competition 1 and team 1:

mysql> insert into users (cid, tid, uid) values (1, 1, 1);
Query OK, 1 row affected (0.00 sec)

Now insert same user in another team:

mysql> insert into users (cid, tid, uid) values (1, 2, 1);
ERROR 1062 (23000): Duplicate entry '1-1' for key 'users.cid'

This is good and unique key (cid, uid) is ensuring a user can be part of one team only within a competition.

What happens if we try to do this:

mysql> insert into users (cid, tid, uid) values (2, 2, 1);
Query OK, 1 row affected (0.00 sec)

This is undesired and we should have gotten an error because team 2 is associated with competition 1. The cid is not an independent variable. Its value depends on tid. How can we accomplish this in MySQL?

mysql> select * from teams;
+-----+-----+---------+
| tID | cid | name    |
+-----+-----+---------+
|   1 |   1 | usa     |
|   2 |   1 | china   |
|   3 |   2 | germany |
|   4 |   2 | france  |
|   5 |   3 | india   |
|   6 |   3 | england |
+-----+-----+---------+
6 rows in set (0.00 sec)

mysql> select * from users;
+--------+-----+-----+-----+
| row_id | uID | tid | cid |
+--------+-----+-----+-----+
|      1 |   1 |   1 |   1 |
|      3 |   1 |   2 |   2 |
+--------+-----+-----+-----+
2 rows in set (0.00 sec)
Posted in Software | Tagged | Leave a comment

Dealing with annoying Azure Devops Permissions

Like many other of its products Azure Devops is over-engineered and a pain to use. Unfortunately you might have to use it in your organization. One of the biggest problems I have run into with it is when it will not allow me to do something (such as bypass the configured policies when completing a pull request) even though I have admin privileges. It literally drives me nuts. I keep on changing the permissions in the UI and nothing happens. I have tried turning off inheritance but nothing happens. The solution is to delete the Contributors (or it could be another group in your case) group under Azure DevOps Groups.

My educated guess as to what is happening here is that Contributors do not have permission to bypass policies. I am a member of the Contributors group. I end up inheriting the privileges associated with that group and there are 2 bugs thanks to the talented engineers at Microsoft:

  • turning off inheritance does not turn it off
  • my own settings do not override the inherited settings

so the only solution is to remove the offending group from the Azure DevOps groups. This does not delete the group from the project. It simply removes it from consideration and having any effect as far as security for the master branch (the screenshot) is concerned. Its better to add the users individually to the Users tab and manually assign permissions to them. For a large project it can be tedious.

Posted in Software | Leave a comment