About the `next_short_code` function

[Hu1-Li]

Here is the simple test result, about function next_short_code

• Order by Link.ID desc

  mysql> select id, long_url, short_code, created_at from link where id between 15581 and 15585 order by id desc;
  +-------+----------------------------+------------+---------------------+
  | id    | long_url                   | short_code | created_at          |
  +-------+----------------------------+------------+---------------------+
  | 15585 | http://test.com/a/b/c/5582 | l95        | 2018-05-16 02:11:59 |
  | 15584 | http://test.com/a/b/c/5581 | l94        | 2018-05-16 02:11:59 |
  | 15583 | http://test.com/a/b/c/5580 | l93        | 2018-05-16 02:11:59 |
  | 15582 | http://test.com/a/b/c/5579 | l92        | 2018-05-16 02:11:58 |
  | 15581 | http://test.com/a/b/c/5578 | l91        | 2018-05-16 02:11:58 |
  +-------+----------------------------+------------+---------------------+

• Order by Link.created_at desc

mysql> select id, long_url, short_code, created_at from link where id between 15581 and 15585 order by created_at desc;
+-------+----------------------------+------------+---------------------+
| id    | long_url                   | short_code | created_at          |
+-------+----------------------------+------------+---------------------+
| 15583 | http://test.com/a/b/c/5580 | l93        | 2018-05-16 02:11:59 |
| 15584 | http://test.com/a/b/c/5581 | l94        | 2018-05-16 02:11:59 |
| 15585 | http://test.com/a/b/c/5582 | l95        | 2018-05-16 02:11:59 |
| 15581 | http://test.com/a/b/c/5578 | l91        | 2018-05-16 02:11:58 |
| 15582 | http://test.com/a/b/c/5579 | l92        | 2018-05-16 02:11:58 |
+-------+----------------------------+------------+---------------------+

As you can see, order by link.created_at desc is not always corect.

And If using link.created_at desc will get the incorrect answer of base_str, which will trapped into the while cycle.

here is the log of simple call next_short_code, which iter from h2k to h2x.

2018-05-16 02:05:12,407 INFO sqlalchemy.engine.base.Engine SELECT link.id AS link_id, link.long_url AS link_long_url, link.protocol AS link_protocol, link.domain AS link_domain, link.long_url_hash AS link_long_url_hash, link.short_code AS link_short_code, link.description AS link_description, link.owner AS link_owner, link.secret_key AS link_secret_key, link.expire_after AS link_expire_after, link.is_default AS link_is_default, link.is_protected AS link_is_protected, link.is_disabled AS link_is_disabled, link.is_custom AS link_is_custom, link.created_at AS link_created_at, link.updated_at AS link_updated_at
FROM link
WHERE link.is_custom IS false AND link.short_code IS NOT NULL AND link.short_code != %(short_code_1)s ORDER BY link.created_at DESC
 LIMIT %(param_1)s
2018-05-16 02:05:12,407 INFO sqlalchemy.engine.base.Engine {'short_code_1': '', 'param_1': 1}
2018-05-16 02:05:12,417 INFO sqlalchemy.engine.base.Engine SELECT count(*) AS count_1
FROM (SELECT link.id AS link_id, link.long_url AS link_long_url, link.protocol AS link_protocol, link.domain AS link_domain, link.long_url_hash AS link_long_url_hash, link.short_code AS link_short_code, link.description AS link_description, link.owner AS link_owner, link.secret_key AS link_secret_key, link.expire_after AS link_expire_after, link.is_default AS link_is_default, link.is_protected AS link_is_protected, link.is_disabled AS link_is_disabled, link.is_custom AS link_is_custom, link.created_at AS link_created_at, link.updated_at AS link_updated_at
FROM link
WHERE link.short_code = %(short_code_1)s) AS anon_1
2018-05-16 02:05:12,418 INFO sqlalchemy.engine.base.Engine {'short_code_1': 'h2k'}
2018-05-16 02:05:12,419 INFO sqlalchemy.engine.base.Engine SELECT link.id AS link_id, link.long_url AS link_long_url, link.protocol AS link_protocol, link.domain AS link_domain, link.long_url_hash AS link_long_url_hash, link.short_code AS link_short_code, link.description AS link_description, link.owner AS link_owner, link.secret_key AS link_secret_key, link.expire_after AS link_expire_after, link.is_default AS link_is_default, link.is_protected AS link_is_protected, link.is_disabled AS link_is_disabled, link.is_custom AS link_is_custom, link.created_at AS link_created_at, link.updated_at AS link_updated_at
FROM link
WHERE link.short_code = %(short_code_1)s
2018-05-16 02:05:12,419 INFO sqlalchemy.engine.base.Engine {'short_code_1': 'h2k'}
2018-05-16 02:05:12,423 INFO sqlalchemy.engine.base.Engine SELECT count(*) AS count_1
FROM (SELECT link.id AS link_id, link.long_url AS link_long_url, link.protocol AS link_protocol, link.domain AS link_domain, link.long_url_hash AS link_long_url_hash, link.short_code AS link_short_code, link.description AS link_description, link.owner AS link_owner, link.secret_key AS link_secret_key, link.expire_after AS link_expire_after, link.is_default AS link_is_default, link.is_protected AS link_is_protected, link.is_disabled AS link_is_disabled, link.is_custom AS link_is_custom, link.created_at AS link_created_at, link.updated_at AS link_updated_at
FROM link
WHERE link.short_code = %(short_code_1)s) AS anon_1
2018-05-16 02:05:12,423 INFO sqlalchemy.engine.base.Engine {'short_code_1': 'h2l'}
2018-05-16 02:05:12,424 INFO sqlalchemy.engine.base.Engine SELECT link.id AS link_id, link.long_url AS link_long_url, link.protocol AS link_protocol, link.domain AS link_domain, link.long_url_hash AS link_long_url_hash, link.short_code AS link_short_code, link.description AS link_description, link.owner AS link_owner, link.secret_key AS link_secret_key, link.expire_after AS link_expire_after, link.is_default AS link_is_default, link.is_protected AS link_is_protected, link.is_disabled AS link_is_disabled, link.is_custom AS link_is_custom, link.created_at AS link_created_at, link.updated_at AS link_updated_at
FROM link
WHERE link.short_code = %(short_code_1)s
2018-05-16 02:05:12,424 INFO sqlalchemy.engine.base.Engine {'short_code_1': 'h2l'}
2018-05-16 02:05:12,429 INFO sqlalchemy.engine.base.Engine SELECT count(*) AS count_1
FROM (SELECT link.id AS link_id, link.long_url AS link_long_url, link.protocol AS link_protocol, link.domain AS link_domain, link.long_url_hash AS link_long_url_hash, link.short_code AS link_short_code, link.description AS link_description, link.owner AS link_owner, link.secret_key AS link_secret_key, link.expire_after AS link_expire_after, link.is_default AS link_is_default, link.is_protected AS link_is_protected, link.is_disabled AS link_is_disabled, link.is_custom AS link_is_custom, link.created_at AS link_created_at, link.updated_at AS link_updated_at
FROM link
WHERE link.short_code = %(short_code_1)s) AS anon_1
2018-05-16 02:05:12,429 INFO sqlalchemy.engine.base.Engine {'short_code_1': 'h2m'}
2018-05-16 02:05:12,430 INFO sqlalchemy.engine.base.Engine SELECT link.id AS link_id, link.long_url AS link_long_url, link.protocol AS link_protocol, link.domain AS link_domain, link.long_url_hash AS link_long_url_hash, link.short_code AS link_short_code, link.description AS link_description, link.owner AS link_owner, link.secret_key AS link_secret_key, link.expire_after AS link_expire_after, link.is_default AS link_is_default, link.is_protected AS link_is_protected, link.is_disabled AS link_is_disabled, link.is_custom AS link_is_custom, link.created_at AS link_created_at, link.updated_at AS link_updated_at
FROM link
WHERE link.short_code = %(short_code_1)s
2018-05-16 02:05:12,430 INFO sqlalchemy.engine.base.Engine {'short_code_1': 'h2m'}
2018-05-16 02:05:12,434 INFO sqlalchemy.engine.base.Engine SELECT count(*) AS count_1
FROM (SELECT link.id AS link_id, link.long_url AS link_long_url, link.protocol AS link_protocol, link.domain AS link_domain, link.long_url_hash AS link_long_url_hash, link.short_code AS link_short_code, link.description AS link_description, link.owner AS link_owner, link.secret_key AS link_secret_key, link.expire_after AS link_expire_after, link.is_default AS link_is_default, link.is_protected AS link_is_protected, link.is_disabled AS link_is_disabled, link.is_custom AS link_is_custom, link.created_at AS link_created_at, link.updated_at AS link_updated_at
FROM link
WHERE link.short_code = %(short_code_1)s) AS anon_1
2018-05-16 02:05:12,434 INFO sqlalchemy.engine.base.Engine {'short_code_1': 'h2n'}
2018-05-16 02:05:12,435 INFO sqlalchemy.engine.base.Engine SELECT link.id AS link_id, link.long_url AS link_long_url, link.protocol AS link_protocol, link.domain AS link_domain, link.long_url_hash AS link_long_url_hash, link.short_code AS link_short_code, link.description AS link_description, link.owner AS link_owner, link.secret_key AS link_secret_key, link.expire_after AS link_expire_after, link.is_default AS link_is_default, link.is_protected AS link_is_protected, link.is_disabled AS link_is_disabled, link.is_custom AS link_is_custom, link.created_at AS link_created_at, link.updated_at AS link_updated_at
FROM link
WHERE link.short_code = %(short_code_1)s
2018-05-16 02:05:12,435 INFO sqlalchemy.engine.base.Engine {'short_code_1': 'h2n'}
2018-05-16 02:05:12,439 INFO sqlalchemy.engine.base.Engine SELECT count(*) AS count_1
FROM (SELECT link.id AS link_id, link.long_url AS link_long_url, link.protocol AS link_protocol, link.domain AS link_domain, link.long_url_hash AS link_long_url_hash, link.short_code AS link_short_code, link.description AS link_description, link.owner AS link_owner, link.secret_key AS link_secret_key, link.expire_after AS link_expire_after, link.is_default AS link_is_default, link.is_protected AS link_is_protected, link.is_disabled AS link_is_disabled, link.is_custom AS link_is_custom, link.created_at AS link_created_at, link.updated_at AS link_updated_at
FROM link
WHERE link.short_code = %(short_code_1)s) AS anon_1
2018-05-16 02:05:12,439 INFO sqlalchemy.engine.base.Engine {'short_code_1': 'h2o'}
2018-05-16 02:05:12,440 INFO sqlalchemy.engine.base.Engine SELECT link.id AS link_id, link.long_url AS link_long_url, link.protocol AS link_protocol, link.domain AS link_domain, link.long_url_hash AS link_long_url_hash, link.short_code AS link_short_code, link.description AS link_description, link.owner AS link_owner, link.secret_key AS link_secret_key, link.expire_after AS link_expire_after, link.is_default AS link_is_default, link.is_protected AS link_is_protected, link.is_disabled AS link_is_disabled, link.is_custom AS link_is_custom, link.created_at AS link_created_at, link.updated_at AS link_updated_at
FROM link
WHERE link.short_code = %(short_code_1)s
2018-05-16 02:05:12,440 INFO sqlalchemy.engine.base.Engine {'short_code_1': 'h2o'}
2018-05-16 02:05:12,444 INFO sqlalchemy.engine.base.Engine SELECT count(*) AS count_1
FROM (SELECT link.id AS link_id, link.long_url AS link_long_url, link.protocol AS link_protocol, link.domain AS link_domain, link.long_url_hash AS link_long_url_hash, link.short_code AS link_short_code, link.description AS link_description, link.owner AS link_owner, link.secret_key AS link_secret_key, link.expire_after AS link_expire_after, link.is_default AS link_is_default, link.is_protected AS link_is_protected, link.is_disabled AS link_is_disabled, link.is_custom AS link_is_custom, link.created_at AS link_created_at, link.updated_at AS link_updated_at
FROM link
WHERE link.short_code = %(short_code_1)s) AS anon_1
2018-05-16 02:05:12,445 INFO sqlalchemy.engine.base.Engine {'short_code_1': 'h2p'}
2018-05-16 02:05:12,446 INFO sqlalchemy.engine.base.Engine SELECT link.id AS link_id, link.long_url AS link_long_url, link.protocol AS link_protocol, link.domain AS link_domain, link.long_url_hash AS link_long_url_hash, link.short_code AS link_short_code, link.description AS link_description, link.owner AS link_owner, link.secret_key AS link_secret_key, link.expire_after AS link_expire_after, link.is_default AS link_is_default, link.is_protected AS link_is_protected, link.is_disabled AS link_is_disabled, link.is_custom AS link_is_custom, link.created_at AS link_created_at, link.updated_at AS link_updated_at
FROM link
WHERE link.short_code = %(short_code_1)s
2018-05-16 02:05:12,446 INFO sqlalchemy.engine.base.Engine {'short_code_1': 'h2p'}
2018-05-16 02:05:12,450 INFO sqlalchemy.engine.base.Engine SELECT count(*) AS count_1
FROM (SELECT link.id AS link_id, link.long_url AS link_long_url, link.protocol AS link_protocol, link.domain AS link_domain, link.long_url_hash AS link_long_url_hash, link.short_code AS link_short_code, link.description AS link_description, link.owner AS link_owner, link.secret_key AS link_secret_key, link.expire_after AS link_expire_after, link.is_default AS link_is_default, link.is_protected AS link_is_protected, link.is_disabled AS link_is_disabled, link.is_custom AS link_is_custom, link.created_at AS link_created_at, link.updated_at AS link_updated_at
FROM link
WHERE link.short_code = %(short_code_1)s) AS anon_1
2018-05-16 02:05:12,450 INFO sqlalchemy.engine.base.Engine {'short_code_1': 'h2q'}
2018-05-16 02:05:12,451 INFO sqlalchemy.engine.base.Engine SELECT link.id AS link_id, link.long_url AS link_long_url, link.protocol AS link_protocol, link.domain AS link_domain, link.long_url_hash AS link_long_url_hash, link.short_code AS link_short_code, link.description AS link_description, link.owner AS link_owner, link.secret_key AS link_secret_key, link.expire_after AS link_expire_after, link.is_default AS link_is_default, link.is_protected AS link_is_protected, link.is_disabled AS link_is_disabled, link.is_custom AS link_is_custom, link.created_at AS link_created_at, link.updated_at AS link_updated_at
FROM link
WHERE link.short_code = %(short_code_1)s
2018-05-16 02:05:12,451 INFO sqlalchemy.engine.base.Engine {'short_code_1': 'h2q'}
2018-05-16 02:05:12,455 INFO sqlalchemy.engine.base.Engine SELECT count(*) AS count_1
FROM (SELECT link.id AS link_id, link.long_url AS link_long_url, link.protocol AS link_protocol, link.domain AS link_domain, link.long_url_hash AS link_long_url_hash, link.short_code AS link_short_code, link.description AS link_description, link.owner AS link_owner, link.secret_key AS link_secret_key, link.expire_after AS link_expire_after, link.is_default AS link_is_default, link.is_protected AS link_is_protected, link.is_disabled AS link_is_disabled, link.is_custom AS link_is_custom, link.created_at AS link_created_at, link.updated_at AS link_updated_at
FROM link
WHERE link.short_code = %(short_code_1)s) AS anon_1
2018-05-16 02:05:12,455 INFO sqlalchemy.engine.base.Engine {'short_code_1': 'h2r'}
2018-05-16 02:05:12,457 INFO sqlalchemy.engine.base.Engine SELECT link.id AS link_id, link.long_url AS link_long_url, link.protocol AS link_protocol, link.domain AS link_domain, link.long_url_hash AS link_long_url_hash, link.short_code AS link_short_code, link.description AS link_description, link.owner AS link_owner, link.secret_key AS link_secret_key, link.expire_after AS link_expire_after, link.is_default AS link_is_default, link.is_protected AS link_is_protected, link.is_disabled AS link_is_disabled, link.is_custom AS link_is_custom, link.created_at AS link_created_at, link.updated_at AS link_updated_at
FROM link
WHERE link.short_code = %(short_code_1)s
2018-05-16 02:05:12,457 INFO sqlalchemy.engine.base.Engine {'short_code_1': 'h2r'}
2018-05-16 02:05:12,461 INFO sqlalchemy.engine.base.Engine SELECT count(*) AS count_1
FROM (SELECT link.id AS link_id, link.long_url AS link_long_url, link.protocol AS link_protocol, link.domain AS link_domain, link.long_url_hash AS link_long_url_hash, link.short_code AS link_short_code, link.description AS link_description, link.owner AS link_owner, link.secret_key AS link_secret_key, link.expire_after AS link_expire_after, link.is_default AS link_is_default, link.is_protected AS link_is_protected, link.is_disabled AS link_is_disabled, link.is_custom AS link_is_custom, link.created_at AS link_created_at, link.updated_at AS link_updated_at
FROM link
WHERE link.short_code = %(short_code_1)s) AS anon_1
2018-05-16 02:05:12,461 INFO sqlalchemy.engine.base.Engine {'short_code_1': 'h2s'}
2018-05-16 02:05:12,462 INFO sqlalchemy.engine.base.Engine SELECT link.id AS link_id, link.long_url AS link_long_url, link.protocol AS link_protocol, link.domain AS link_domain, link.long_url_hash AS link_long_url_hash, link.short_code AS link_short_code, link.description AS link_description, link.owner AS link_owner, link.secret_key AS link_secret_key, link.expire_after AS link_expire_after, link.is_default AS link_is_default, link.is_protected AS link_is_protected, link.is_disabled AS link_is_disabled, link.is_custom AS link_is_custom, link.created_at AS link_created_at, link.updated_at AS link_updated_at
FROM link
WHERE link.short_code = %(short_code_1)s
2018-05-16 02:05:12,462 INFO sqlalchemy.engine.base.Engine {'short_code_1': 'h2s'}
2018-05-16 02:05:12,466 INFO sqlalchemy.engine.base.Engine SELECT count(*) AS count_1
FROM (SELECT link.id AS link_id, link.long_url AS link_long_url, link.protocol AS link_protocol, link.domain AS link_domain, link.long_url_hash AS link_long_url_hash, link.short_code AS link_short_code, link.description AS link_description, link.owner AS link_owner, link.secret_key AS link_secret_key, link.expire_after AS link_expire_after, link.is_default AS link_is_default, link.is_protected AS link_is_protected, link.is_disabled AS link_is_disabled, link.is_custom AS link_is_custom, link.created_at AS link_created_at, link.updated_at AS link_updated_at
FROM link
WHERE link.short_code = %(short_code_1)s) AS anon_1
2018-05-16 02:05:12,466 INFO sqlalchemy.engine.base.Engine {'short_code_1': 'h2t'}
2018-05-16 02:05:12,467 INFO sqlalchemy.engine.base.Engine SELECT link.id AS link_id, link.long_url AS link_long_url, link.protocol AS link_protocol, link.domain AS link_domain, link.long_url_hash AS link_long_url_hash, link.short_code AS link_short_code, link.description AS link_description, link.owner AS link_owner, link.secret_key AS link_secret_key, link.expire_after AS link_expire_after, link.is_default AS link_is_default, link.is_protected AS link_is_protected, link.is_disabled AS link_is_disabled, link.is_custom AS link_is_custom, link.created_at AS link_created_at, link.updated_at AS link_updated_at
FROM link
WHERE link.short_code = %(short_code_1)s
2018-05-16 02:05:12,467 INFO sqlalchemy.engine.base.Engine {'short_code_1': 'h2t'}
2018-05-16 02:05:12,471 INFO sqlalchemy.engine.base.Engine SELECT count(*) AS count_1
FROM (SELECT link.id AS link_id, link.long_url AS link_long_url, link.protocol AS link_protocol, link.domain AS link_domain, link.long_url_hash AS link_long_url_hash, link.short_code AS link_short_code, link.description AS link_description, link.owner AS link_owner, link.secret_key AS link_secret_key, link.expire_after AS link_expire_after, link.is_default AS link_is_default, link.is_protected AS link_is_protected, link.is_disabled AS link_is_disabled, link.is_custom AS link_is_custom, link.created_at AS link_created_at, link.updated_at AS link_updated_at
FROM link
WHERE link.short_code = %(short_code_1)s) AS anon_1
2018-05-16 02:05:12,471 INFO sqlalchemy.engine.base.Engine {'short_code_1': 'h2u'}
2018-05-16 02:05:12,473 INFO sqlalchemy.engine.base.Engine SELECT link.id AS link_id, link.long_url AS link_long_url, link.protocol AS link_protocol, link.domain AS link_domain, link.long_url_hash AS link_long_url_hash, link.short_code AS link_short_code, link.description AS link_description, link.owner AS link_owner, link.secret_key AS link_secret_key, link.expire_after AS link_expire_after, link.is_default AS link_is_default, link.is_protected AS link_is_protected, link.is_disabled AS link_is_disabled, link.is_custom AS link_is_custom, link.created_at AS link_created_at, link.updated_at AS link_updated_at
FROM link
WHERE link.short_code = %(short_code_1)s
2018-05-16 02:05:12,473 INFO sqlalchemy.engine.base.Engine {'short_code_1': 'h2u'}
2018-05-16 02:05:12,477 INFO sqlalchemy.engine.base.Engine SELECT count(*) AS count_1
FROM (SELECT link.id AS link_id, link.long_url AS link_long_url, link.protocol AS link_protocol, link.domain AS link_domain, link.long_url_hash AS link_long_url_hash, link.short_code AS link_short_code, link.description AS link_description, link.owner AS link_owner, link.secret_key AS link_secret_key, link.expire_after AS link_expire_after, link.is_default AS link_is_default, link.is_protected AS link_is_protected, link.is_disabled AS link_is_disabled, link.is_custom AS link_is_custom, link.created_at AS link_created_at, link.updated_at AS link_updated_at
FROM link
WHERE link.short_code = %(short_code_1)s) AS anon_1
2018-05-16 02:05:12,477 INFO sqlalchemy.engine.base.Engine {'short_code_1': 'h2v'}
2018-05-16 02:05:12,478 INFO sqlalchemy.engine.base.Engine SELECT link.id AS link_id, link.long_url AS link_long_url, link.protocol AS link_protocol, link.domain AS link_domain, link.long_url_hash AS link_long_url_hash, link.short_code AS link_short_code, link.description AS link_description, link.owner AS link_owner, link.secret_key AS link_secret_key, link.expire_after AS link_expire_after, link.is_default AS link_is_default, link.is_protected AS link_is_protected, link.is_disabled AS link_is_disabled, link.is_custom AS link_is_custom, link.created_at AS link_created_at, link.updated_at AS link_updated_at
FROM link
WHERE link.short_code = %(short_code_1)s
2018-05-16 02:05:12,478 INFO sqlalchemy.engine.base.Engine {'short_code_1': 'h2v'}
2018-05-16 02:05:12,482 INFO sqlalchemy.engine.base.Engine SELECT count(*) AS count_1
FROM (SELECT link.id AS link_id, link.long_url AS link_long_url, link.protocol AS link_protocol, link.domain AS link_domain, link.long_url_hash AS link_long_url_hash, link.short_code AS link_short_code, link.description AS link_description, link.owner AS link_owner, link.secret_key AS link_secret_key, link.expire_after AS link_expire_after, link.is_default AS link_is_default, link.is_protected AS link_is_protected, link.is_disabled AS link_is_disabled, link.is_custom AS link_is_custom, link.created_at AS link_created_at, link.updated_at AS link_updated_at
FROM link
WHERE link.short_code = %(short_code_1)s) AS anon_1
2018-05-16 02:05:12,482 INFO sqlalchemy.engine.base.Engine {'short_code_1': 'h2w'}
2018-05-16 02:05:12,483 INFO sqlalchemy.engine.base.Engine SELECT link.id AS link_id, link.long_url AS link_long_url, link.protocol AS link_protocol, link.domain AS link_domain, link.long_url_hash AS link_long_url_hash, link.short_code AS link_short_code, link.description AS link_description, link.owner AS link_owner, link.secret_key AS link_secret_key, link.expire_after AS link_expire_after, link.is_default AS link_is_default, link.is_protected AS link_is_protected, link.is_disabled AS link_is_disabled, link.is_custom AS link_is_custom, link.created_at AS link_created_at, link.updated_at AS link_updated_at
FROM link
WHERE link.short_code = %(short_code_1)s
2018-05-16 02:05:12,483 INFO sqlalchemy.engine.base.Engine {'short_code_1': 'h2w'}
2018-05-16 02:05:12,488 INFO sqlalchemy.engine.base.Engine SELECT count(*) AS count_1
FROM (SELECT link.id AS link_id, link.long_url AS link_long_url, link.protocol AS link_protocol, link.domain AS link_domain, link.long_url_hash AS link_long_url_hash, link.short_code AS link_short_code, link.description AS link_description, link.owner AS link_owner, link.secret_key AS link_secret_key, link.expire_after AS link_expire_after, link.is_default AS link_is_default, link.is_protected AS link_is_protected, link.is_disabled AS link_is_disabled, link.is_custom AS link_is_custom, link.created_at AS link_created_at, link.updated_at AS link_updated_at
FROM link
WHERE link.short_code = %(short_code_1)s) AS anon_1
2018-05-16 02:05:12,488 INFO sqlalchemy.engine.base.Engine {'short_code_1': 'h2x'}
h2x

If you want to re-appear this situation, write two script

1. try import data using API

   
   import requests
   import json

def short(i): r = requests.post( "http://localhost:9119/api/shorten", headers={'Content-Type': 'Application/json'}, data=json.dumps( {"long_url": "https://test.com/a/b/c/{i}".format(i=i)} ) )

for i in range(10000): short(i)

2. try to get `next_short_code`

!/usr/bin/env python3

"""Script when you just want to run Pygmy API."""

from pygmy.core.initialize import initialize initialize()

from pygmy.helpers.link_helper import next_short_code import time while True: print(next_short_code())

I'm writing this because that we use this and it cause the server near down(can't login using ssh). Is this related?

[amitt001]

@Hu1-Li I see where this issue is coming from. Ordering the links by created_at desc and then picking the top link as latest will cause this. As for high load websites with many concurrent requests at a time will have many rows with the same created_at time. Wrong ordering is causing while loop and eventually select queries in MySQL to execute many more times than expected.

This can be fixed by ordering by id instead of created_at in latest_default_link method.

I am bit busy right now, I will fix and test this when I get some time. Would you like to make necessary changes and the above tests and open a PR?

[Hu1-Li]

OK, I will try.

[Hu1-Li]

Update: [USING MYSQL] In my test on the fixed code, which change ordering from created_at to id, the test comes with A99, then it got stucks. As expect, the next short code should be Baa, as the below line

link_manager.find(short_code=base_str)

it got baa

In Mysql, String compare is case-insensitive, thus, causing the server down ,since it needs to loop a really big cycle, from baa to A99 then starts over and over again.

So when creating table, the field short_code should be

short_code varchar(10) COLLATE utf8_bin DEFAULT NULL,`

In SQLAchemy, This can be achieved by

short_code = Column(Unicode(6, collation="utf8_bin"), unique=True, index=True, default=None)

[Hu1-Li]

Pull request #17 fixes this problem, As for test, i get no idea how to achieve the test for the above problem @amitt001

// Update For sqlite, it should be Column(Unicode(6, collation="BINARY"), unique=True, index=True, default=None) For mysql, it should be Column(Unicode(6, collation="utf8_bin"), unique=True, index=True, default=None)

emmmm,

[amitt001]

Fixed in commit: https://github.com/amitt001/pygmy/commit/67260baca6b3ecf11848169f20a857e8fd384762

@Hu1-Li Thanks for fixing this bug and contributing 🍰