ANN: PHP Generator for MySQL 15.12 released

[SQL Maestro Team]

Hi!

SQL Maestro Group announces the release of PHP Generator for MySQL
15.12, a powerful GUI frontend for Windows that allows you to generate
feature-rich CRUD web applications for your MySQL database.
http://www.sqlmaestro.com/products/mysql/phpgenerator/

Online demo:
http://demo.sqlmaestro.com/

Top 15 new features:


 1. New modern look and feel.
 2. 100% responsive design.
 3. PHP 7 support.
 4. Top side drop-down menus.
 5. Enhanced Filter Builder.
 6. Multi-column sorting.
 7. Keyboard shortcuts.
 8. A number of new and updated controls.
 9. 18 color themes.
10. Font-based icons.
11. Event management enhancements.
12. Less preprocessor syntax for user-defined styles.
13. HTML filter.
14. New and updated data access drivers.
15. PHP Generator UI improvements.

Full press-release is available at:
http://www.sqlmaestro.com/news/company/php_generator_15_12_released/

Background information:
---
SQL Maestro Group offers complete database admin, development and
management tools for MySQL, SQL Server, PostgreSQL, Oracle, DB2,
SQLite, SQL Anywhere, Firebird and MaxDB providing the highest
performance, scalability and reliability to meet the requirements of
today's database applications.

Sincerely yours,
The SQL Maestro Group Team
http://www.sqlmaestro.com

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

select contiguous addresses that start on a bit boundary

[John Stile]

I need help creating a select that returns 4 records that have
contiguous addresses that start on a bit boundary.

If 4 do not exist, I need a return of zero records.

I would like to do this in one statement and I do not have ownership of
this mysql server, so fancy views, temporary tables, indexing, etc are
outside my permission level.

I am also not the only consumer of this database, so altering it for my
needs could hurt the other consumers.

Below I specify the issue and where I am.

Thank you for your attention.

#
# Create problem set
# - This has non-contiguous addresses
# - This has one status not 0
# - This has contiguous addresses that start before the bit boundary
#
CREATE TABLE addresses ( address BIGINT(20), status INT );
INSERT INTO addresses
VALUES (1001,0),
   (1003,0),
   (1004,0),
   (1005,1),
   (1006,0),
   (1007,0),
   (1008,0),
   (1009,0),
   (1010,0),
   (1011,0),
   (1013,0),
   (1014,0),
   (1015,0),
   (1016,0),
   (1017,0);
#
# This shows the bit boundary, where the start is  (address & 3) = 0
#
select address, (address & 3) as boundary from addresses where address
>0 and status=0 order by address limit 10  ;
+--+--+
| address  | boundary |
+--+--+
| 1001 |1 |
| 1003 |3 |
| 1004 |0 |
| 1006 |2 |
| 1007 |3 |
| 1008 |0 |
| 1009 |1 |
| 1010 |2 |
| 1011 |3 |
| 1013 |1 |
+--+--+
10 rows in set (0.00 sec)
#
# This shows contiguous add, but they do not stat on the bit  boundary
#
select c1.address, (address & 3) as boundary  from addresses c1 where 4
= ( SELECT count(*) FROM addresses c2 WHERE c2.status = 0 and c2.address
BETWEEN c1.address AND (c1.address + 3)  ) limit 10;

+--+--+
| address  | boundary |
+--+--+
| 1006 |2 |
| 1007 |3 |
| 1008 |0 |
| 1013 |1 |
| 1014 |2 |
+--+--+
5 rows in set (0.00 sec)



I can't seem to add my ((address & 3) = 0) condition to the correct location to 
get the desired
result. I don't understand how I can use c1.address in the BETWEEN, and
yet I can't seem to make ((address & 3) = 0) work anywhere.



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Narrow A First Set Of Records

[Don Wieland]

Hey gang,

I need the ability to produce this end result:

condition a) All clients who have had at least 2 appointments in the last 24 
months
condition b) but have NO appointments in the last 6 months

I am able to get the clients with >= 2 appointments with this query

SELECT 
c.client_id, 
concat(c.first_name, ' ', c.last_name) AS client_name,
c.`address`, 
c.city, 
c.state, 
c.`zip`, 
c.email , 
max(from_unixtime(a.time_start)) AS last_appt,
count(a.client_id) AS appt_count

FROM tl_clients c
LEFT JOIN tl_appt a ON c.client_id = a.client_id
GROUP BY a.client_id
HAVING max(a.time_start) >= UNIX_TIMESTAMP(DATE_SUB( NOW(), INTERVAL 24 MONTH))
AND last_appt IS NOT NULL AND count(a.client_id) >= 2;

BUT, how do I add the other “condition b” to the query to get an accurate end 
result.

I’d appreciate any help.

Don Wieland
D W   D a t a   C o n c e p t s
~
d...@dwdataconcepts.com
http://www.dwdataconcepts.com
Direct Line - (949) 336-4828
SKYPE - skypename = dwdata

Integrated data solutions to fit your business needs.

Need assistance in dialing in your FileMaker solution? Check out our Developer 
Support Plans:

Basic Developer Support Plan - 3 hours @ $360:
http://www.dwdataconcepts.com/DevSup.php 


Intermediate Developer Support Plan - 10 hours for $960 (2 FREE HOURS - $240 
savings off regular billable rate)
http://www.dwdataconcepts.com/IntDevSup.php 


Premium Developer Support Plan - 20 hours for $1800 ( 5 FREE HOURS - $600 
savings off regular billable rate)
http://www.dwdataconcepts.com/PremDevSup.php 


In all of these plans, we create a support account and credit the account with 
the hours purchased. You can then dictate how and when the hours are used. They 
will not expire until they are used up and at that point you can opt to 
replenish the account, if you desire. When we work on your system or with you, 
we simply deduct the billable time from your account. At a regular interval or 
per your request, a summary of your account status will be email to you.

---

Appointment 1.0v9 - Powerful Appointment Scheduling for FileMaker Pro 9 or 
higher
http://www.appointment10.com 

For a quick overview - 
http://www.appointment10.com/Appt10_Promo/Overview.html

Re: select contiguous addresses that start on a bit boundary

[John Stile]

I should have said consecutive addresses, rather than contiguous.
I care about a set of consecutive addresses, and there is no guarantee
of record order.

On 12/17/2015 07:35 AM, John Stile wrote:
> I need help creating a select that returns 4 records that have
> contiguous addresses that start on a bit boundary.
>
> If 4 do not exist, I need a return of zero records.
>
> I would like to do this in one statement and I do not have ownership of
> this mysql server, so fancy views, temporary tables, indexing, etc are
> outside my permission level.
>
> I am also not the only consumer of this database, so altering it for my
> needs could hurt the other consumers.
>
> Below I specify the issue and where I am.
>
> Thank you for your attention.
>
> #
> # Create problem set
> # - This has non-contiguous addresses
> # - This has one status not 0
> # - This has contiguous addresses that start before the bit boundary
> #
> CREATE TABLE addresses ( address BIGINT(20), status INT );
> INSERT INTO addresses
> VALUES (1001,0),
>(1003,0),
>(1004,0),
>(1005,1),
>(1006,0),
>(1007,0),
>(1008,0),
>(1009,0),
>(1010,0),
>(1011,0),
>(1013,0),
>(1014,0),
>(1015,0),
>(1016,0),
>(1017,0);
> #
> # This shows the bit boundary, where the start is  (address & 3) = 0
> #
> select address, (address & 3) as boundary from addresses where address
>> 0 and status=0 order by address limit 10  ;
> +--+--+
> | address  | boundary |
> +--+--+
> | 1001 |1 |
> | 1003 |3 |
> | 1004 |0 |
> | 1006 |2 |
> | 1007 |3 |
> | 1008 |0 |
> | 1009 |1 |
> | 1010 |2 |
> | 1011 |3 |
> | 1013 |1 |
> +--+--+
> 10 rows in set (0.00 sec)
> #
> # This shows contiguous add, but they do not stat on the bit  boundary
> #
> select c1.address, (address & 3) as boundary  from addresses c1 where 4
> = ( SELECT count(*) FROM addresses c2 WHERE c2.status = 0 and c2.address
> BETWEEN c1.address AND (c1.address + 3)  ) limit 10;
>
> +--+--+
> | address  | boundary |
> +--+--+
> | 1006 |2 |
> | 1007 |3 |
> | 1008 |0 |
> | 1013 |1 |
> | 1014 |2 |
> +--+--+
> 5 rows in set (0.00 sec)
>
>
>
> I can't seem to add my ((address & 3) = 0) condition to the correct location 
> to get the desired
> result. I don't understand how I can use c1.address in the BETWEEN, and
> yet I can't seem to make ((address & 3) = 0) work anywhere.
>
>
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Narrow A First Set Of Records

[Don Wieland]

Hey gang,

I need the ability to produce this end result:

condition a) All clients who have had at least 2 appointments in the last 24 
months
condition b) of the set of “condition a” rows, which of those have NO 
appointments in the last 6 months

I am able to get the clients with >= 2 appointments with this query

SELECT 
c.client_id, 
concat(c.first_name, ' ', c.last_name) AS client_name,
c.`address`, 
c.city, 
c.state, 
c.`zip`, 
c.email , 
max(from_unixtime(a.time_start)) AS last_appt,
count(a.client_id) AS appt_count

FROM tl_clients c
LEFT JOIN tl_appt a ON c.client_id = a.client_id
GROUP BY a.client_id
HAVING max(a.time_start) >= UNIX_TIMESTAMP(DATE_SUB( NOW(), INTERVAL 24 MONTH))
AND last_appt IS NOT NULL AND count(a.client_id) >= 2;

BUT, how do I add the other “condition b” to the query to get an accurate end 
result.

I’d appreciate any help.

Don

Re: Narrow A First Set Of Records

[Roy Lyseng]

Hi Don,

On 17.12.15 16.14, Don Wieland wrote:

Hey gang,

I need the ability to produce this end result:

condition a) All clients who have had at least 2 appointments in the last 24 
months
condition b) of the set of “condition a” rows, which of those have NO 
appointments in the last 6 months

I am able to get the clients with >= 2 appointments with this query

SELECT
c.client_id,
concat(c.first_name, ' ', c.last_name) AS client_name,
c.`address`,
c.city,
c.state,
c.`zip`,
c.email ,
max(from_unixtime(a.time_start)) AS last_appt,
count(a.client_id) AS appt_count

FROM tl_clients c
LEFT JOIN tl_appt a ON c.client_id = a.client_id
GROUP BY a.client_id
HAVING max(a.time_start) >= UNIX_TIMESTAMP(DATE_SUB( NOW(), INTERVAL 24 MONTH))
AND last_appt IS NOT NULL AND count(a.client_id) >= 2;

BUT, how do I add the other “condition b” to the query to get an accurate end 
result.

I’d appreciate any help.

Don



Can you try this query?

select * FROM (
select client_id, client_name, ...,
   (select count(*) from tl_appt AS a
WHERE c.client_id = a.client_id AND
  a.time_start >=
  UNIX_TIMESTAMP(DATE_SUB( NOW(), INTERVAL 24 MONTH)) AND
  last_appt IS NOT NULL) AS twoyear,
(select count(*) from tl_appt AS a
 WHERE c.client_id = a.client_id AND
   a.time_start >=
   UNIX_TIMESTAMP(DATE_SUB( NOW(), INTERVAL 6 MONTH)) AND
   last_appt IS NOT NULL) AS halfyear
from tl_clients AS c) AS dt
WHERE twoyear >= 2 AND halfyear == 0;

I have not run it through MySQL, so you may have to fix the syntax a bit...

Thanks,
Roy

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: select contiguous addresses that start on a bit boundary

[John Stile]

I have a solution.

SELECT start_bit_boundary FROM (
  SELECT
min(address) as start_bit_boundary,
status, count(*) as CT
  FROM MAC_addresses
  WHERE status = 0
  GROUP BY address >> 2
) AS _INNER
WHERE
  _INNER.CT = 4
ORDER BY start_bit_boundary
LIMIT 0,1;

It returns the first of 4 consecutive addresses.
This works with the following data set.

CREATE TABLE addresses ( address BIGINT(20), status INT );
INSERT INTO addresses
VALUES (1001,0),
   (1003,0),
   (1004,0),
   (1005,1),
   (1006,0),
   (1007,0),
   (1009,0),
   (1010,0),
   (1011,0),
   (1013,0),
   (1008,0),
   (1014,0),
   (1015,0),
   (1016,0),
   (1017,0);

If I want to print all the addresses I could do this:

select * from addresses
where status = 0
AND  address BETWEEN (
  SELECT @b := start_bit_boundary FROM (
SELECT min(address) as start_bit_boundary,status,count(*) as CT
FROM MAC_addresses
WHERE status = 0
GROUP BY address >> 2
  ) AS _INNER
  WHERE _INNER.CT = 4
  ORDER BY start_bit_boundary
  LIMIT 0,1
) AND (@b+3) limit 0,4;

On 12/17/2015 08:14 AM, John Stile wrote:
> I should have said consecutive addresses, rather than contiguous.
> I care about a set of consecutive addresses, and there is no guarantee
> of record order.
>
> On 12/17/2015 07:35 AM, John Stile wrote:
>> I need help creating a select that returns 4 records that have
>> contiguous addresses that start on a bit boundary.
>>
>> If 4 do not exist, I need a return of zero records.
>>
>> I would like to do this in one statement and I do not have ownership of
>> this mysql server, so fancy views, temporary tables, indexing, etc are
>> outside my permission level.
>>
>> I am also not the only consumer of this database, so altering it for my
>> needs could hurt the other consumers.
>>
>> Below I specify the issue and where I am.
>>
>> Thank you for your attention.
>>
>> #
>> # Create problem set
>> # - This has non-contiguous addresses
>> # - This has one status not 0
>> # - This has contiguous addresses that start before the bit boundary
>> #
>> CREATE TABLE addresses ( address BIGINT(20), status INT );
>> INSERT INTO addresses
>> VALUES (1001,0),
>>(1003,0),
>>(1004,0),
>>(1005,1),
>>(1006,0),
>>(1007,0),
>>(1008,0),
>>(1009,0),
>>(1010,0),
>>(1011,0),
>>(1013,0),
>>(1014,0),
>>(1015,0),
>>(1016,0),
>>(1017,0);
>> #
>> # This shows the bit boundary, where the start is  (address & 3) = 0
>> #
>> select address, (address & 3) as boundary from addresses where address
>>> 0 and status=0 order by address limit 10  ;
>> +--+--+
>> | address  | boundary |
>> +--+--+
>> | 1001 |1 |
>> | 1003 |3 |
>> | 1004 |0 |
>> | 1006 |2 |
>> | 1007 |3 |
>> | 1008 |0 |
>> | 1009 |1 |
>> | 1010 |2 |
>> | 1011 |3 |
>> | 1013 |1 |
>> +--+--+
>> 10 rows in set (0.00 sec)
>> #
>> # This shows contiguous add, but they do not stat on the bit  boundary
>> #
>> select c1.address, (address & 3) as boundary  from addresses c1 where 4
>> = ( SELECT count(*) FROM addresses c2 WHERE c2.status = 0 and c2.address
>> BETWEEN c1.address AND (c1.address + 3)  ) limit 10;
>>
>> +--+--+
>> | address  | boundary |
>> +--+--+
>> | 1006 |2 |
>> | 1007 |3 |
>> | 1008 |0 |
>> | 1013 |1 |
>> | 1014 |2 |
>> +--+--+
>> 5 rows in set (0.00 sec)
>>
>>
>>
>> I can't seem to add my ((address & 3) = 0) condition to the correct location 
>> to get the desired
>> result. I don't understand how I can use c1.address in the BETWEEN, and
>> yet I can't seem to make ((address & 3) = 0) work anywhere.
>>
>>
>>
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

mysql dump global read lock

[Artem Kuchin]

Hello!

Hereis my mysqldump command line

mysqldump -u root --events --complete-insert --skip-opt 
--single-transaction --add-drop-table --add-locks --create-options 
--disable-keys --
extended-insert --quick --set-charset --routines --triggers --hex-blob  
DB_NAME


But i see tons of Waiting for global read lock
in show processlist for many tables in many different databases for all 
modification queries and locks


Why?  As i understood --skip-opt --single-transaction must disable 
global read lock



mysql version
Server version: 5.6.27-log Source distribution

Artem





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql