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.

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

Sarita – school teacher

😀😀
Sarita was a very good teacher in the school …
*Sarita*: – Tell me where is the Taj Mahal …. 🕌
*Students*:  Agra …  
*Sarita* : Wrong … It’s in Bengaluru…
The students all got into thinking .. and were confused😮😲The students told their parents.  
The very next day, all the parents reached the school and started complaining to Sarita that why are you teaching wrong facts to the children .
*Sarita to all the parents:* 👇🏽👇🏽

.

.

.

.

.

.

.

.

.

.

.

.

First of all, you should deposit the fees of the last six months 💰Till the fees is not deposited, Taj Mahal will remain in *Bengaluru*.🤣😂

Posted in Jokes | Leave a comment

Swoole Notes

First of all you have to understand:

  • the difference between a coroutine and a thread, and
  • the difference between concurrency and parallelism

The TL;DR is that threads are managed by the OS whereas coroutines are managed by the language. OS does not know what a coroutine is. OS preemptively interrupts threads and interleaves them (this is known as concurrent execution) to give the illusion of parallelism. It is an illusion because concurrency is not the same as parallelism . Coroutines run within a thread and are managed entirely by the language.

Just as the OS interleaves threads, a single thread interleaves coroutines running within it. The difference is that whereas threads are preemptively interrupted, coroutines have well-defined points where execution is yielded to another coroutine.

The OS might be running say 30 threads to take an example. Within each thread you may further have 30 coroutines running.

Understand pre-emptive (this is what threads do; a thread has no control over when it will be interrupted; the OS can interrupt it suddenly at any unpredictable point) vs. co-operative (this is what coroutines do) multitasking:

The term preemptive multitasking is used to distinguish a multitasking operating system, which permits preemption of tasks, from a cooperative multitasking system wherein processes or tasks must be explicitly programmed to yield when they do not need system resources.

In simple terms: Preemptive multitasking involves the use of an interrupt mechanism which suspends the currently executing process and invokes a scheduler to determine which process should execute next. Therefore, all processes will get some amount of CPU time at any given time

Read this as this is the essence of Swoole: https://www.swoole.co.uk/docs/modules/swoole-coroutine 

A coroutine can be simply understood as a thread, but this thread is in user mode and does not require the participation of the operating system. The cost of creating, destroying and switching is very low. Unlike threads, the coroutine cannot use multiple CPU core because it operates in user space

Swoole creates one coroutine for each request to the server and switches coroutines based on I/O status automatically, this happens within the coroutine scheduler.

So the beauty is that you write code as if you were programming synchronously but Swoole will automatically switch coroutines based on I/O status thus giving you benefits that come with async execution.

A coroutine context is created using Co\run. The context is created automatically for you for each HTTP request.

A coroutine context is created with the callback function: request, receive, connect in a Swoole\Server or Swoole\HTTP\Server for you, so you can start using coroutines straight away within those callbacks.

A coroutine can then be created within the context using the go function.

inside each go call is a coroutine

Coroutines have access to the same memory space, they can conflict with modifying memory which could be dependent by another coroutine as they are running within user space, on the same thread.

To solve the problem of conflicting memory access we have channels they are used for the communication between coroutines.

$chan->pop(); // Read data from the channel, it will block and wait if the channel is empty
$chan->push(); // Write data into the channel, it will block and wait if the channel is full

Keywords:

  • go, Create a new coroutine. go() is alias of Swoole\Coroutine::create()
  • chan, Create a new channel for message-passing
  • defer, Delay the task until the exit of the coroutine, FIFO

Swoole coroutines are analogous to goroutines in Go with some differences .

More notes from: http://vesko.blogs.azonmedia.com/2019/09/19/coroutines-in-swoole/  (an excellent article)

Each coroutine has its own stack but shares the global state of the PHP process (the Worker) with the rest of the coroutines.

The parent/child relationship of the coroutines does not mean that that they are nested like the function calls/stack. Unlike the functions the child coroutine can end after the parent one and both coroutines are running in parallel (in terms that they can get and yield execution multiple times in between giving the impression of parallel execution, not that they are actually running in parallel as it is explained further down).

The coroutines yield the control to the scheduler only at certain points. In Swoole these are requests that can be served asynchronously[2] like database queries, file operations, etc. By saying asynchronous I do not mean that these are using callbacks, but instead that at the point of a database query being sent the coroutine gives the control back to the scheduler to run another coroutine. The first coroutine will be resumed once the data is received from the DB and the currently running coroutine gives up the control.

It is very important to note that the core PHP libraries and DB extensions are blocking and do not allow for coroutine switching. This means that if you execute mysqli_connect() the Swoole Worker will wait (block) for the result of the operations instead of yielding the control to another coroutine. Thus running an existing PHP code in Swoole will make no use at all of the coroutines.

On the contrary – the global shared state allows for a pool of database connections to be created and connections from this pool to be obtained by the coroutines as needed and then released. Because of the persistent nature of Swoole, these connections do not have to be opened and closed for each request but instead once they are opened when the Swoole Server is started these only need to be pinged to keep them alive. The connection pool provides a speedup compared to the traditional setup as it saves time for opening the DB connection.

Another good read: http://swoft.io/docs/2.x/en/ready/swoole.html 

How does Swoole Work?

The essence of Swoole is its coroutines which are very similar to Go. But how do the Swoole coroutines work internally? They work by tapping into the epoll  I/O event notification facility of Linux. This is how all the magic happens ref . And this is also the reason why Swoole does not work on Windows since epoll is specific to Linux.

Gotchas

  • Xdebug must be disabled when using Swoole and Yasd does not work. So debugging is going to be harder.
  • Also can’t use xhprof, Blackfire ref . Also can’t use zendtraceuopz ref 
  • Can’t just edit and save php files for changes to take effect like you can do with PHP-FPM. Have to restart the server (just like you have to do with Node.js) or send SIGTERM or USR signal to worker process.
Posted in Software | Tagged | Leave a comment