Showing entries 11 to 17
« 10 Newer Entries
Displaying posts with tag: functions (reset)
True Random Database and Table Name Generator - Part 2 of 2

Permalink: http://bit.ly/QuBLVB



Read part 1 for the rationale behind the code.

As discussed in the first part of this blog entry, we'll be utilizing a statement that uses base 36 to generate the random name. We will be adding the $ and _ characters using the ELT function. Here is a true random database and table name generator:

DELIMITER $$
DROP FUNCTION IF EXISTS `randomNameGenerator` $$
CREATE DEFINER=`root`@`localhost` FUNCTION `randomNameGenerator`(
) RETURNS varchar(64) CHARSET utf8
BEGIN
DECLARE numberOfChars, charDiceRoll TINYINT(2);
DECLARE charCount TINYINT DEFAULT 0;
DECLARE randomChar CHAR(1);
DECLARE randomName VARCHAR(64) DEFAULT '';
[Read more]
True Random Database and Table Name Generator - Part 1 of 2

Permalink: http://bit.ly/UZY7xT



Skip to part 2 to go straight to the code snippet.

See also a similar generator: Truly Random and Complex Password Generator

Database names and table names have certain restrictions in MySQL:

  • The maximum name length for both are 64 characters
  • Allowed characters are a-z, A-Z, 0-9, $, and _


It is possible to create a table or database with a dot (.) in its name, however this is not recommended as it will cause some of MySQL's built-in functions to not work as …

[Read more]
Random Number Range Picker

Permalink: http://bit.ly/R5rizQ



A function that can randomly pick an integer from a given range can be used by applications that need a number dice roll and random generation of strings, numbers, and even random generation of complex passwords. Let's say you would like to randomly pick a number from 1 to 10. This gives us 10 choices to randomly pick, not 9 as can be mistakenly thought of since 10 minus 1 equals 9. The same is with the range of choices from 0 to 10. This gives 11 possibilities, not 10. To illustrate:

  • The range of choices should include the value of the lower end of the range
  • It should …
[Read more]
A function to get all the columns of any table from any database

Permalink: http://bit.ly/VP174V



Certain complex MySQL SELECT and subquery statements will not allow the use of the * wildcard and you will need to fill in the entire column list of a given table. Consider the following simplified example, a SELECT statement that contains 3 columns. The asterisk here refers to all columns, which is actually the 3 columns listed in the GROUP BY clause:

SELECT
IF(
EXISTS(
SELECT *
FROM (
SELECT *
FROM `dbName_A`.`tableName_A`
UNION ALL
SELECT *
FROM `dbName_B`.`tableName_B`
AS `compareTables`
GROUP BY `column_1`, `column_2`, `column_3`
HAVING COUNT(*) = 1),
1, 0);


Imagine if it were dozens of columns instead of just 3. You can't simply put in the * wildcard like 'GROUP BY * '. The above example will not …

[Read more]
implementing table quotas in MySQL

I have just seen Limiting table disk quota in MySQL by Shlomi Noach, and I could not resist.
You can actually implement a disk quota using an updatable view with the CHECK OPTION.
Instead of giving the user access to the table, you give access to the view (at least for inserting, see the caveat at the end), and you will get a genuine MySQL error when the limit is reached.

drop table if exists logs;
create table logs (t mediumtext) engine=innodb;

drop function if exists exceeded_logs_quota ;
create function exceeded_logs_quota()
returns boolean
deterministic
return (
select CASE
WHEN (DATA_LENGTH + INDEX_LENGTH) > (25*1024)
THEN TRUE ELSE FALSE
[Read more]
Pitfalls of monitoring MySQL table activity with stored routines
A friend of mine needed a quick method to monitor a table that was frequently updated. There were several procedures writing to the table several times per second. He needed a quick way of determining how many updates per second the table was getting.
The table has only one row, which includes a counter that is increased at each operation. Therefore, the simple plan was:
  1. Let's get the counter from the table;
  2. Allow N seconds to pass;
  3. Get the counter again;
  4. The difference between the second counter and the first counter, divided by the number of seconds gives the updates per second.
The plan makes sense, and if you run the above …
[Read more]
memcached Functions for Drizzle now in main tree!

What a great day! I see that the drizzle team has merged in my (and Padraig O'Sullivan's) memcached Functions for Drizzle (UDFs). I'm really glad to have this in drizzle now as it adds a means of interacting with memcached from within Drizzle. I have most functions from the MySQL branch implemented now as well. I'm extremely grateful to Padraig O'Sullivan for getting this project off the ground. I was a bit stuck with the new API and C++ when I first attempted these and he designed the class setup and had the major functions working which I then picked up and added more functions as well as tests. I was also glad to have the drizzle team add in my sleep() UDF which allowed me to test expirations in these memcached functions.

These functions have some similar, but very little code from the memcached Functions for MySQL. The new UDF API is completely different than MySQL's UDF API. You have to create a class for each function that is a …

[Read more]
Showing entries 11 to 17
« 10 Newer Entries