Determining CPU Usage of last week We got a production issue last week (high CPU spike) on 25-Feb due to which server reboot was performed but ticket landed our DBA queue just today for analysis on CPU spike. Could you pls help me in modifying the below script to determine the resource which caused on 25th or pls suggest any alternatives

DECLARE @ts_now bigint
SELECT @ts_now = cpu_ticks / (cpu_ticks/ms_ticks)  FROM sys.dm_os_sys_info
SELECT top 20 record_id, EventTime,
  CASE WHEN system_cpu_utilization_post_sp2 IS NOT NULL THEN system_cpu_utilization_post_sp2 ELSE system_cpu_utilization_pre_sp2 END AS system_cpu_utilization,
  CASE WHEN sql_cpu_utilization_post_sp2 IS NOT NULL THEN sql_cpu_utilization_post_sp2 ELSE sql_cpu_utilization_pre_sp2 END AS sql_cpu_utilization
FROM
(
  SELECT
    record.value('(Record/@id)[1]', 'int') AS record_id,
    DATEADD (ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS EventTime,
    100-record.value('(Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_post_sp2,
    record.value('(Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_post_sp2 ,
    100-record.value('(Record/SchedluerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_pre_sp2,
    record.value('(Record/SchedluerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_pre_sp2
  FROM (
    SELECT timestamp, CONVERT (xml, record) AS record
    FROM sys.dm_os_ring_buffers
    WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR'
      AND record LIKE '%<SystemHealth>%') AS t
) AS t
ORDER BY record_id desc

Your code requires little tweaking. Where you are doing getdate you just need to do getdate() -6. The changed code is below.

Please note that ring buffer capacity is limited so it would not store information for complete day it would have information about specific time.

DECLARE @ts_now bigint
SELECT @ts_now = cpu_ticks / (cpu_ticks/ms_ticks)  FROM sys.dm_os_sys_info
SELECT top 20 record_id, EventTime,
  CASE WHEN system_cpu_utilization_post_sp2 IS NOT NULL THEN system_cpu_utilization_post_sp2 ELSE system_cpu_utilization_pre_sp2 END AS system_cpu_utilization,
  CASE WHEN sql_cpu_utilization_post_sp2 IS NOT NULL THEN sql_cpu_utilization_post_sp2 ELSE sql_cpu_utilization_pre_sp2 END AS sql_cpu_utilization
FROM
(
  SELECT
    record.value('(Record/@id)[1]', 'int') AS record_id,
    DATEADD (ms, -1 * (@ts_now - [timestamp]), GETDATE()-6) AS EventTime,--changed here
    100-record.value('(Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_post_sp2,
    record.value('(Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_post_sp2 ,
    100-record.value('(Record/SchedluerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS system_cpu_utilization_pre_sp2,
    record.value('(Record/SchedluerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS sql_cpu_utilization_pre_sp2
  FROM (
    SELECT timestamp, CONVERT (xml, record) AS record
    FROM sys.dm_os_ring_buffers
    WHERE ring_buffer_type = 'RING_BUFFER_SCHEDULER_MONITOR'
      AND record LIKE '%<SystemHealth>%') AS t
) AS t
ORDER BY record_id desc


SQL query not displaying a row when one of the variable gets 0 values I have this query that is set to return a % but when one of the sender_marks in the list values gets 0%, the mark is not displayed with 0 but disappears.

SELECT  p.SENDER_MARK,ROUND (Removed/TotalTrains*100,2) TrainRemovals
FROM
(SELECT COUNT(DISTINCT TRAIN_ID) AS Removed,SENDER_MARK
FROM BULLETIN.DISPATCH_TRAIN
WHERE SENDER_MARK in ( 'BNSF','NS','UP','METR','BRC','CPRS','CSXT')
AND REMOVED_FROM_DISPATCH_IND = 'Y'
AND MODIFIED_TS >= SYSDATE-7 group by SENDER_MARK ) p,
(SELECT COUNT(DISTINCT TRAIN_ID) AS TotalTrains,SENDER_MARK
FROM BULLETIN.DISPATCH_TRAIN
WHERE SENDER_MARK in ( 'BNSF','NS','UP','METR','BRC','CPRS','CSXT')
AND MODIFIED_TS >= SYSDATE-7 group by SENDER_MARK )b where  p.SENDER_MARK=b.SENDER_MARK

Your problem is that you are inner joining both subqueries together, meaning that if there are no rows for the join condition (in your case, p.sender_mark = b.sender_mark) in one of the subqueries, you won't get a row in the results for the row in the other subquery that does exist.

You could get around this by turning the join into an outer join, but (IMO) the better option is to use conditional aggregation, like so:

SELECT sender_mark,
       ROUND(removed * 100 / total_trains, 2) trainremovals
FROM   (SELECT sender_mark,
               COUNT(DISTINCT CASE WHEN removed_from_dispatch_ind = 'Y' THEN train_id END) AS removed,
               COUNT(DISTINCT train_id) AS totaltrains
        FROM   bulletin.dispatch_train
        WHERE  sender_mark IN ('BNSF', 'NS', 'UP', 'METR', 'BRC', 'CPRS', 'CSXT')
        AND    modified_ts >= SYSDATE - 7
        GROUP  BY sender_mark);

Aggregate functions (including COUNT, except for when you do COUNT(*)) nearly all exclude null values from their calculations. Therefore by outputting NULL values if a condition is not met, you can get COUNT to ignore those rows.

Doing it like that means you are now only querying the table once instead of twice, which is nearly always going to be more performant.


TSQL Query - Efficient? I want to output data that will display various results depending on Type and/or Quantity values.

Each query calls the same table and returns the same columns. I was thinking of creating a UNION on each one. Or should I just make a single call to database, populate a table variable from this, and then query table variable to get the desired output?

Below a sample of queries.
Thanks.

-- 1) display where type is 'detail'
SELECT Type, Description, Country, Quantity
FROM UserHoldings
WHERE (Type = 'Detail')
ORDER BY Quantity DESC;

-- 2) display where type is 'current' and positive
SELECT Type, Description, Country, Quantity
FROM UserHoldings
WHERE (Type = 'Current')
AND Quantity >= 0
ORDER BY Quantity DESC;

-- sum positive values
SELECT SUM(Quantity)
FROM UserHoldings
WHERE (Type = 'Current')
AND Quantity >= 0
GROUP BY Type;

-- 3) displaywhere type is 'current' and negative
SELECT Type, Description, Country, Quantity
FROM UserHoldings
WHERE (Type = 'Current')
AND Quantity < 0
ORDER BY Quantity DESC;

-- sum negative values
SELECT SUM(Quantity)
FROM UserHoldings
WHERE (Type = 'Current')
AND Quantity < 0
GROUP BY Type;

In my personal opinion, it seems as all of those queries have very separate requirements and output.  I would personally keep them separate to the end.  In other words, what are you going to gain out of filling a temporary object with a key/value pair of the output of 5 unrelated queries?

I'd wrap them each in a stored procedure, parameterize what I can to make code reusable, and then just make individual calls to each whenever I needed to.  I like to follow the principle that a logical piece of code (stored proc, method, etc.) should do just one thing.

So the approach I would take is something like this:

create proc dbo.GetDetail
as
    SELECT Type, Description, Country, Quantity
    FROM UserHoldings
    WHERE (Type = 'Detail')
    ORDER BY Quantity DESC;
go

create proc dbo.GetCurrent
    @Quantity int = 0
as
    SELECT Type, Description, Country, Quantity
    FROM UserHoldings
    WHERE (Type = 'Current')
    AND Quantity >= @Quantity
    ORDER BY Quantity DESC;
go

So on and so forth...

That way when you needed the data, you'd make your calls:

exec dbo.GetDetail;
exec dbo.GetCurrent;
exec dbo.GetCurrent @Quantity = 10;

Why this particular approach?  Because I think this scales well.  It's the way I typically approach database development.  I'm completely fine with having a bunch of small pieces of executable code.  It beats having a few big ones, and having to modify accordingly.  That doesn't grow.

This is my opinion, and I'm looking forward to seeing what others would do in this scenario.


Help combining two MySQL queries to improve performance I have two MySQL queries. The first one returns a list of codes, which comprise either one or two letters (e.g. "AB", "WO", "G"). This is the query:

SELECT d.field_id_105 AS code,
       title
FROM exp_channel_titles AS t
INNER JOIN exp_channel_data AS d ON (d.entry_id = t.entry_id)
WHERE t.channel_id = 18
AND t.status = 'open'
AND t.author_id = 123
ORDER BY t.entry_id ASC

Then, using a PHP foreach loop, I'm looping through each of those codes and performing a second query on each one, which is obviously incredibly inefficient.

The second query varies slightly depending on whether the code comprises one or two letters. If it comprises 2 letters, I simply want to find all records where a specific field starts with those two letters. If it comprises one letter, I want to find all records where a specific field starts with that letter followed by any number from 1 to 9 (e.g. G1 or W8).

Here are the two different versions of the second query:

If the code comprises two letters

SELECT t.title AS title,
       t.entry_date AS entry_date,
       t.entry_id AS entry_id,
       i.title AS installer,
       i.entry_id AS installer_id,
       d.entry_id
FROM exp_channel_data AS d
INNER JOIN exp_relationships AS r ON (r.parent_id = d.entry_id AND r.field_id = 48)
INNER JOIN exp_channel_titles AS t ON (d.entry_id = t.entry_id AND t.status = 'open')
INNER JOIN exp_channel_titles AS i ON (r.child_id = i.entry_id AND i.status = 'open')
WHERE LEFT(field_id_56, 2)  = 'AB'
AND d.channel_id = 12

If the code comprises one letter:

SELECT t.title AS title,
       t.entry_date AS entry_date,
       t.entry_id AS entry_id,
       i.title AS installer,
       i.entry_id AS installer_id,
       d.entry_id
FROM exp_channel_data AS d
INNER JOIN exp_relationships AS r ON (r.parent_id = d.entry_id AND r.field_id = 48)
INNER JOIN exp_channel_titles AS t ON (d.entry_id = t.entry_id AND t.status = 'open')
INNER JOIN exp_channel_titles AS i ON (r.child_id = i.entry_id AND i.status = 'open')
WHERE LEFT(field_id_56, 2) IN ('G1','G2','G3','G4','G5','G6','G7','G8','G9')
AND d.channel_id = 12

I've 'hard-coded' the values in the WHERE clauses for demo purposes, but they would actually be populated dynamically by PHP.

So essentially what I'd like to do is combine all of these queries into one query. I'm not sure if that's even possible. I got as far as using the first query as a subquery inside the LEFT() function, but I'm getting a little out of my depth at that point and can't work out how to handle appending the numbers to the single-letter codes. Maybe some REGEXP, but I'm not sure.

Any help appreciated, thanks.

I'm assuming that G followed by anything is valid in the second case. If so you sould be able to replace your 3 queries with something like:

SELECT d.field_id_105 AS code
     , t.title AS title,
     , t.entry_date AS entry_date
     , t.entry_id AS entry_id
     , i.title AS installer
     , i.entry_id AS installer_id
     , d.entry_id
FROM exp_channel_data AS d
JOIN exp_relationships AS r
    ON r.parent_id = d.entry_id
   AND r.field_id = 48
JOIN exp_channel_titles AS t
    ON d.entry_id = t.entry_id
   AND t.status = 'open'
JOIN exp_channel_titles AS i
    ON r.child_id = i.entry_id
   AND i.status = 'open'
WHERE SUBSTR(d.field_id_56,1
            ,CASE WHEN SUBSTR(d.field_id_56,1,2) REGEXP '[A-Z][0-9]'
                                    THEN 1
                                    ELSE 2
                               END)
    = SUBSTR(d.field_id_105,1
            ,CASE WHEN SUBSTR(d.field_id_56,1,2) REGEXP '[A-Z][0-9]'
                                    THEN 1
                                    ELSE 2
                               END)
AND d.channel_id = 12;

This is untested and may contain syntax errors since there where no ddl in the question.


Oracle number precision datatype performance impact Does number size affect to speed of inserting, updating, ...(DML operations) rows?
If i use number(20) instead of number(6), does it impact performance?

The precision and scale of a number column is a matter for business rules and data modelling. If a number can have values in the trillions don't declare it as number(6).The general cases seem to be

leave number columns without further definition - number
enforce integer columns e.g. number(20,0) or less commonly integer
for cases where it really matters (e.g. monetary values) specify scale and precision e.g. number(15,2)

Then there are the specialized edge cases for binary_float and binary_double. If you are doing heavy-duty calculations there are definite performance benefits from choosing these data types (at the price of losing some exactitude). But if you're using regular number columns just choose the precision and scale you need for the business rules, and don't sweat on performance.

  if i define as a number without precision, how it works? what would be default precision?

If we don't define number further there is no default precision. We can store any number from 0.0000000000000000000000000000000000001 to 99999999999999999999999999999999999999.


mySQL Workbench new Connection : There is no disk in drive. Please insert disk in \Device\Harddisk2\DR2 I'm trying to migrate the data at my company from Excel to mySQL so I'm very new at this. I just installed the latest mySQL package via the standard installer and when I try a new connection (I'm trying to get through the mySQLTutorial) the error pops up:

MySQLWorkbench.exe - No disk

There is no disk in drive.
Please insert disk in drive \Device\Harddisk\DR2

My setup for the new connection is as follows:

Connection name: local
Connection method: Standard (TCP/IP)

Hostname: 127.0.0.1
Port: 3306
Default schema: ""

Anyone have any ideas? I'm completely at a loss here...

Did you install that on network share? Please  try reinstall only workbench using this package https://dev.mysql.com/downloads/workbench/ and use default location c:\program files ....
Maybe you didn't install MySQL Workbench Windows Prerequisites. You can find links on the same download page.

Regards,
Marcin


Access not reading extended parameters We have an ADP Access 2010 project, linked to a MS SQL 2012 server.

I am trying to set up a table that has a combo box to select appropriate values, which are supplied by another table.

A table under the default dbo schema reads the extended properties, and shows the appropriate combo box

When I try to add the same extended properties to a table that is under another schema, the combo box does not appear.

What do I need to do to solve for this, or am I resigned to giving up with the task, and deal with it later when we drop the ADP?

(I tried asking this on SuperUser, but got no response)

Access code that opens table that works:

Private Sub cmdAvayaQ_Click()
DoCmd.OpenTable "dbo.tbl_o_etc_Avaya_Skills", acViewNormal, acEdit
End Sub

Access code that opens table that doesn't work

Private Sub cmdNonPhone_Click()
DoCmd.OpenTable "forecasting.tbl_nonphone_queues", acViewNormal, acEdit
End Sub

I don't use Access, but it is likely that it is unable to query extended properties except those under the dbo schema - it must make the assumption that everything is under dbo. I don't really have a solution for you other than not using schemas with your ADP if extended properties are critical.


Recover an Innodb mysql database from an EBS on Ec2 My instance crashed on AWs, and i had to create a new one and mount my Vol that contain my web application and the Mysql Database.

After i stoped the Mysql Server i mounted the vol and also mounted mysql folders from the colume to /var/lib/mysq, /var/log/mysal and /etc/mysql  using this command lines:

echo "/mnt/vol/etc/mysql /etc/mysql     none bind" | sudo tee -a /etc/fstab
sudo mount /etc/mysql

echo "/mnt/vol/var/lib/mysql /var/lib/mysql none bind" | sudo tee -a /etc/fstab
sudo mount /var/lib/mysql

echo "/mnt/vol/var/log/mysql /var/log/mysql none bind" | sudo tee -a /etc/fstab
sudo mount /var/log/mysql

[Tutorial ref : aws.amazon.com/articles/1663 ]

Then i tried to run the Mysql again, with :

# service mysql start

but with no joy.

And when i run mysqld i got this errors messages :

# mysqld
140123 19:34:56 [Warning] Can't create test file /mnt/vol/var/lib/mysql/ip-15-120-63-11.lower-test
140123 19:34:56 [Warning] Can't create test file /mnt/vol/var/lib/mysql/ip-15-120-63-11.lower-test
140123 19:34:56 [Note] Plugin 'FEDERATED' is disabled.
mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
140123 19:34:56 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
140123 19:34:56  InnoDB: Initializing buffer pool, size = 8.0M
140123 19:34:56  InnoDB: Completed initialization of buffer pool
140123 19:34:56  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'open'.
InnoDB: Cannot continue operation.

I can see Permission issue for InnoDB! but what i am curious about is that i get this erro when i try to run my mysql command

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'

Solution :

Step 1

Now Mysql have an apparmor profile enabled by default that will prevent mysql to access non standard area of the file system.

To fix this, i had to add /mnt/vol/var/lib/mysql (or whatever non standard path you are using) to the list of authorized paths in the apparmor mysql profile you will find in /etc/apparmor.d/.

In my case i found it under :

# sudo vi /etc/apparmor.d/usr.sbin.mysqld

NB: You can also put mysql profile in complain mode using "sudo aa-complain" as described in the server guide should show you this in the logs.

Step 2

Run Mysqld to be sure that everything is okey:

# mysqld

Step 3

Run mysql and check the it status :

# sudo service mysql start
# sudo service mysql status

You have to find your Mysql up and running now.
You canalso check mysql log file for more details under /var/log/mysql/error.log

InnoDB: Cannot continue operation.

The MySQL server is refusing to start, so it's not running, and you're not going to be able to connect with the mysql command line utility until it is... but you don't need it running to fix the permissions.

The permissions you need to fix are filesystem permissions, with some variant of this:

$ sudo chmod -R mysql:mysql /var/lib/mysql
$ sudo chmod -R mysql:mysql /var/log/mysql

(Don't copy/paste this unless you are clear on what it does.)

After that, you should be able to start the server if the files are intact and the paths were correct.

Also, I'm not sure where you found the steps you originally used, but I assume the purpose of this exercise is to get the server running again so you can make a backup, after which you'll stop it and unmount those "bind" mounts.


how to decrypt one encrypted column with lost password one column in sql server 2008 database is encrypted with symmetric key, master key password not found, how  to decrypt this column? I create a new database with different name in the same server and transfre that table by generating script. When I tried o run query, it said symmetric key and certificate not found. Error 3102 and 3013

In order to decrypt the column that is encrypted by symmetric key you would have to create the exact same symmetric key on the new database.

Since symmetric keys cannot be backed up, in order to use them on another database you would have to provide them 2 attributes that have to be specified when creating a new symmetric key on a different database to decrypt a column.

Those two attributes are KEY_SOURCE and IDENTITY_VALUE

After you have specified these attributes you need to encrypt that key, with one of the following: password, certificate, another symmetric key, asymmetric key, or some third party provider. In your case is certificate, therefore you need to create a certificate from certificate(original DB) that you supposedly backed up already. Such as:

CREATE  CERTIFICATE NewDBCertificate
FROM FILE = 'E:\OldDBCertificate.crt'
WITH PRIVATE KEY(
FILE = 'C:\OldDBCertificateKey.pvk',
DECRYPTION BY PASSWORD = N'P@$$W0RD')

This DECRYPTION BY PASSWORD is password that you specified when you were making a certificate backup.

And last step is creating an actual symmetric key, with the same KEY_SOURCE and IDENTITY_VALUE as the one on original DB, with certificate(that you just created) specified encryption.

CREATE SYMMETRIC KEY NewSymmetricKey
WITH ALGORITHM = AES_256, --Note that algorithm has to be the same
KEY_SOURCE = 'PassPhrase used in Original Symmetric Key',
IDENTITY_VALUE = 'Phrase used in Original symmetric key'
ENCRYPTION BY CERTIFICATE NewDBCertificate

So in summary,

Find the name of symmetric key and see which certificate is used for encryption
Make sure you have certificate backup somewhere.
Create certificate on a new DB from the backup
Create new symmetric key using the same KEY_SOURCE & IDENTITY_VALUE and encrypt it using the newly created certificate


SQL Server Stored procedures run very slowly after a while We have an ASP.Net application with a report page that runs several stored procedures. We have a problem with a production system where the report runs so slowly that it times out when the user selects a longer date range.

When the slow down occurs, we are able to restore functionality by running EXEC sp_recompile 'CrucialTable' on a table that is touched by all of the stored procedures, and then immediately running the report for a long date range. This fixes the problem for a while, but it slowly creeps back.

My guess was a bad execution plan so I added WITH RECOMPILE to the stored procedures, but sadly it didn't fix the problem.

We are using SQL Azure if that makes any difference.

UPDATE

I discovered a couple of SPs that were still missing the recompile options so that has now been added and now we have a new problem. The long date range reports run great (under 4 seconds which is fine given the amount of crunching that is taking place), but oddly now the short range reports are running horribly. For example a report that covers 2 years worth of transactions takes about 3.5 seconds, but 2 weeks takes 20 seconds!

UPDATE 2

I've managed to replicate the problem on a dev machine and have a look at the execution plan. I've tried a raft of option combinations like OPTION(RECOMPILE) and OPTIMIZE but as it turns out the query itself runs quite fast.

The problem is that

  The query had to wait xx seconds for MemoryGrant during execution

xx ranged from 20 to 90 seconds depending on the run.

That struck me as a but unusual because there were no other queries running at the time and the dev machine has plenty of ram. The execution plan also suggested a missing index so I'm going to try and add that and see if it helps.

UPDATE 3

I've experimented with a number of recompile/optimize options but the solution still eludes me.

Basically the stored procedures all have recompile options on them now, and in test everything works great even with big data (queries take less than a second). My initial problem of MemoryGrant has stopped, which I think was just because at the time the machine it was running on had a lot of applications open and was a little low on free ram.

In production we now have all SPs with the same recompile options on them, but unfortunately the short range queries continue to be incredibly slow. I've run the same SPs using SSMS to the production DB (with ARITHABORT set to OFF to simulate a similar connection) and they run virtually instantaneously regardless of a long or short range.

When running the short range report from the application in production there is an entry in the sys.dm_exec_query_memory_grants table for about 12 seconds. The full report then takes another 14+ seconds to complete after that entry has disappeared.

What you're describing sounds like parameter sniffing to me. Parameter sniffing occurs because SQL Server caches the execution plan of the procedure if the plan is not found in the cache. Subsequent executions will use that version of the plan. In most cases, that's desirable since it eliminates compilation time. However, if the characteristics of the data accessed by the procedure's queries varies significantly when different parameters are passed, the result can be very poor performance.

Your description of the runtimes of short date ranges versus long ranges fits that pattern perfectly. I would guess that the query optimizer is choosing different indexes in the two scenarios.

You're on the right track for your solutions. WITH RECOMPILE can resolve the parameter sniffing in some cases, but recompilation is a high cost to pay on every execution. OPTION(RECOMPILE) on individual queries can definitely help and is sometimes the best answer, but again, it comes at a high cost. OPTION(OPTIMIZE FOR UNKNOWN) is another possibility, but it resolves the issue by providing a plan that's consistent (but may still be horribly inefficient).

Start by reviewing these articles, which explain parameter sniffing far better than I can in a short answer:

Kimberly Tripp, Building High Performance Stored Procedures
Kendra Little, Optimize for… Mediocre?

Then, check the execution plans for your procedure, looking for the specific queries that vary the most between executions. Try OPTION(RECOMPILE), OPTION(OPTIMIZE FOR UNKNOWN), or Kimberly's sp_executesql method on those, rather than the entire procedure.


Postgresql query is very slow for my big table My database version is postgresql 9.5.

create table if not exists request_log
(
    id               bigserial not null constraint app_requests_pkey  primary key,
    request_date     timestamp not null,
    ip               varchar(50),
    start_time       timestamp,
    application_name varchar(200),
    request_path     text,
    display_url      text,
    username         varchar(50)
);

I have a table that icludes incoming http request informations. The id column is primary key and index. The table has no relation.

So I have 72320081 rows in this table. And when I run the count query to get count of table, select count(id) from request_log; query takes 3-5 minutes.

The explain(analyze, buffers, format text) result for this request is:

Aggregate  (cost=3447214.71..3447214.72 rows=1 width=0) (actual time=135575.947..135575.947 rows=1 loops=1)
  Buffers: shared hit=96 read=2551303
  ->  Seq Scan on request_log  (cost=0.00..3268051.57 rows=71665257 width=0) (actual time=2.517..129032.408 rows=72320081 loops=1)
        Buffers: shared hit=96 read=2551303
Planning time: 0.067 ms
Execution time: 135575.988 ms

This is very bad performance for me. I could not get reports from table from web applications because of performance.

My server hardware sources are:

OS: Linux ubuntu server 16, On Vmware
4 core cpu
Mem 6Gb
HDD 120 Gb

I run the queries at nights, that there are no users on database, but slow. How can solve this problem?

Counting rows is slow, because all rows of the table have to be visited.
Counting id is even slower, because PostgreSQL first has to check if id is NULL or not (NULL values are not counted).

There are a few options to speed things up:

Use a more recent version of PostgreSQL.

Then you can get parallel query, which will make the execution even more expensive, but faster.
Use the index on id and keep the table well vacuumed.

Then you can get an index only scan.
Use an extra table with a counter that gets updated on every data modifying statement on the large table using a trigger.

Have a look at my blog post for an in-depth discussion.


Fixing an error 9004 without taking the database offline My database (SQL Server 2008 r2) is running fine but I’m getting the following error in my event log.

Error: 9004, Severity: 23, State: 6.
An error occurred while processing the log for database 'MyDB'.  If possible, restore from
backup. If a backup is not available, it might be necessary to rebuild the log.

Is there any way to fix this without taking the database offline?

If not, what would be the fastest way? Transactional data loss would be accepted.

Not knowing much else about your problem, if your database is online and you're not using anything that may be actively using part of the log - such as Mirroring, AGs, Replication, etc, you could attempt removing that part of the log from the sweep by changing to the simple recovery mode (if in bulk or full) and issuing a checkpoint. Then go back to your normal recovery model and either take a full or differential to restart a valid LSN chain.

This may or may not work for you depending on what the actual underlying cause is, but should work for most cases caused by a state of 6.

Please note: I am not advocating that you do anything to compromise your database. If the database is online and functioning, this won't cause any data loss nor would it be destructive other than to your LSN chain which can be bridged.


Improve performance with the WHERE NOT IN sub-select clause In the following query, I have to count transactions for each customer.  [EDIT]However, I have to exclude from the result set entirely, customers that have a transaction older than one year.

Shouldn't the query optimizer be smart enough to only evaluate existence once for each customer?

--Count transactions on customers that are less than 1 year old

  SELECT t1.CUSTID,COUNT(*)
  FROM CUST_TRX t1
  WHERE NOT EXISTS (
    SELECT FIRST 1 1
    FROM CUST_TRX t2
    WHERE
      t2.CUSTID=t1.CUSTID AND
      t2.DATED<CURRENT_DATE-365
    GROUP BY t2.CUSTID
  )
  GROUP BY t1.CUSTID

There are no naturals in my query plan.  This query is performing as if the database is running the existence clause for every transaction instead of running it for every customer.  Performance is the same if I remove the GROUP BY in the sub-query.

Is there a better way to do this so that I may get better performance out of the database?  Hopefully a simple select query will work avoiding a CTE if possible (that would introduce other challenges).  Due to other group by criteria (not shown here) I'm not able to simply check MIN(DATED), I really need to perform another query.

With queries like this is it often more efficient to perform a LEFT OUTER JOIN instead of the NOT EXISTS style check, it often implies a full index scan (or table scan without the right indexes in place) but with many rows in the main table(s) this is less expensive than the large number of index seeks (one on the reference table for each row returned from the main table) that would otherwise result. Some query planners are quite bright about spotting this equivalence and using the alternate plan where it is the better choice, but it doesn't sound like this has happened in your case.

Try something like:

SELECT t1.CUSTID, COUNT(*)
FROM   CUST_TRX t1
LEFT OUTER JOIN
       CUST_TRX t2
ON     t2.CUSTID=t1.CUSTID
AND    t2.DATED<CURRENT_DATE-365
WHERE  t2.CUSTID IS NULL
GROUP BY t1.CUSTID

(note: I'm not familiar with firebird, so the above syntax may need tweaks but should illustrate the point)

Without the WHERE  t2.CUSTID IS NULL every row from t1 with matches in t2 will be output once for every match found in t2 and those with no matches in t2 will be output once but with any columns selected from that object set to NULL. The WHERE clause then screens out the matches.

Depending on the DB engine's abilities, especially if the amount of data in the reference object (CUST_TRX with a filter applied here) is huge, this may be significantly less efficient than the WHERE <something> NOT IN or WHERE NOT EXISTS options, so benchmark over realistic data sets first before using the method. It often works out much more efficient with MS SQL Server in cases where the query planner doesn't notice that the WHERE NOT IN arrangement can be performed this way more efficiently.

Also if you do it this way around leave a comment in the code (and/or supporting documentation) to say that you are doing this as an equivalent to WHERE <something> NOT IN or WHERE NOT EXISTS which you expect to be more efficient. You'll remember it and an experienced SQL person will recognise the pattern, but other people looking at the code might not immediately understand the intent/reason and flip it back to using WHERE NOT EXISTS for clarity as that reads better as on English sentence.


How do Identity Columns get Replicated? During Transactional Replication, we have data going from Server A to Server B.

Say we were to reseed an Identity Column on a Table on Server A (for whatever reason), how would this affect the data on Server B?

My intuition would say that the data on Server A will use the newly seeded Identity, and that would be sent over to Server B as-is (and Server B would have content using the new Identity), but I'm not sure if this is correct.

It is always tricky when you have Identity column and that is a Primary Key for the table.

Based on my experience dealing with T-Rep and Merge, I would recommend to use NOT FOR REPLICATION to NO. By default it is set to YES. You can do it on sql 2005 and up dynamically (without affecting replication) using sys.sp_identitycolumnforreplication.

If you are not updating the data on subscriber side, you should be fine.

To answer your question :

  Say we were to reseed an Identity Column on a Table on Server A (for whatever reason), how would this affect the data on Server B?

Considering, you are not updating the data on subscriber, when you want to reseed, you should always take the max value from the identity column and then reseed the value accordingly.

You can use below sql to find that out :

SELECT  QUOTENAME(SCHEMA_NAME(t.schema_id)) + '.' +  QUOTENAME(t.name) AS TableName,
        c.name AS ColumnName,
        IDENT_CURRENT(SCHEMA_NAME(t.schema_id)  + '.' + t.name) AS CurrentIdentityValue,
        IDENT_INCR (SCHEMA_NAME(t.schema_id) + '.' + t.name) as Identity_increment
    FROM    sys.columns AS c
        INNER JOIN  sys.tables AS t ON t.[object_id] = c.[object_id]
        WHERE   c.is_identity = 1
  -- write here the table name if you want to filter for specific table
    and t.name like '%TABLE_NAME%'

for example consider below customer table :

TableName     ColumnName    CurrentIdentityValue    Identity_increment
[dbo].[customer]    id         7                    1

Now if you want to somehow reseed the identity value, you can run

dbcc checkident ('customer',reseed,8 )

This means that it will set the current Identity value to 8, so next time any row is inserted into the table, it will have current identity value as 8 (CurrentIdentityValue) + 1(Identity_increment) = 9.

You have to be careful doing this as this can cause identity gaps.

Other things you can do are :

Partitioning
Manual identity range management solutions using sp_addaritcle with parameters like @identity_range ,@pub_identity_range and @threshold

Read up on :

The identity crisis in replication - by Hilary Cotter
All about “Identity Range Management” - by Chris Skorlinski


MySQL JOIN COUNTS from another table I have this two tables:
Table members
id  full_name   sex
1   John Smith  1
2   Carol Danvers   2
3   Clark Kent  1
4   Lois Lane   2
5   Diana Prince    2

Table member_emails
id  member  email
1   1   jsmith@mail.com
2   1   johnsmith@jsa.org
3   2   danvers@marvels.com
4   4   llane@dailyplanet.com

I would like to know how many distinct member sex count who has emails, like this table:
total   male    female
3       1       2

I've already use
 SELECT
 COUNT(DISTINCT e.member) AS total,
 SUM(CASE
    WHEN m.sex = 1
    THEN 1
    ELSE 0
    END) AS "male",
 SUM(CASE
    WHEN m.sex = 2
    THEN 1
    ELSE 0
    END) AS "female"
FROM member_emails e
  JOIN members m ON m.id = (
      SELECT DISTINCT e.member
      FROM members
      WHERE id = e.member
)

But it resulted
total   male    female
3       2       2

As it can't distinct e.member.
Does anyone have any ideas how to do this?

You can SELECT the DISTINCT member ids before joining

CREATE TABLE members (
  `id` INTEGER,
  `full_name` VARCHAR(15),
  `sex` INTEGER
);

INSERT INTO members
  (`id`, `full_name`, `sex`)
VALUES
  ('1', 'John Smith', '1'),
  ('2', 'Carol Danvers', '2'),
  ('3', 'Clark Kent', '1'),
  ('4', 'Lois Lane', '2'),
  ('5', 'Diana Prince', '2');

CREATE TABLE member_email (
  `id` INTEGER,
  `member` INTEGER,
  `email` VARCHAR(21)
);

INSERT INTO member_email
  (`id`, `member`, `email`)
VALUES
  ('1', '1', 'jsmith@mail.com'),
  ('2', '1', 'johnsmith@jsa.org'),
  ('3', '2', 'danvers@marvels.com'),
  ('4', '4', 'llane@dailyplanet.com');

SELECT
COUNT(*) AS total,
SUM(`sex`= 1) AS male
,SUM(`sex`= 2) AS  female
FROM
(SELECT DISTINCT `member` FROM member_email) m_e
INNER JOIN  members m ON m_e.`member` = m.`id`

total | male | female
----: | ---: | -----:
    3 |    1 |      2

db<>fiddle here


Best way to enter data in DB that is spread out date wise I have an Excel file that, to keep it simple here, has 3 columns: ActivityID, ActivityName and TotalHours.

TotalHours column is basically all the hours allotted to that specific ActivityID and the file can have about ~1500 of these activityID equating to 1500 rows.

Now the thing is that there are additional columns in the file which break down the TotalHours by week dates. These hours are split into different week periods but adding them all up equals TotalHours.

I don't want to end up dynamically creating table columns in my DB as I could end up with 100+ fields. One solution is that I could read the Excel just like it is and do calculations on the fly but this would then require the file to be strictly templatized meaning the data needs to be consistent and be in specified cells for me to read them. This is something that I am trying to avoid.

Is there any better way that you know I can approach this problem?

Thanks

Am I understanding that you pretty much have a work order, and that work order can be worked on for a number of hours on any day, and you want to store how many hours someone worked it each day? So if I work on a project for 2 hours on the 3rd, and 5 hours on the 10th, then it would store 12 hours in the total column?

If that's the case, I would approach it in a more event based method. Have a table for the activity (ActivityID, ActivityName...). Then Have a table for work entries for the activity (ActivityID, PersonWorking, DateWorked, PersonWorked...). The hours will each only be entered into the Work table, and have a view that would sum up each activity and show the days worked along with the total. And as a note, that database systems can usually handle a lot of rows listing 4-5 fields a bit easier than trying to just have 10 years worth of dates in the column headers and just spread everything out.

In my experience it is almost never a good idea to 1)store a computed value based on data in the same record row, and 2)store date data as field names. Unless you have a VERY SPECIFIC set of dates that will apply to all projects/activities, don't use them as field headers.


MySQL - What is the diffrence between ClearText authentication and PAM authentication plugins? PAM Authentication:

PAM enables a system to use a standard interface to access various kinds of authentication methods, such as Unix passwords or an LDAP directory.

Cleartext Client-Side Authentication Plugin

client-side plugin can be used to send the password to the server in clear text. There is no corresponding server-side plugin.

I have read documentation but could not get enough help, PAM authentication is supported only in MySQL Enterprise edition but cleartext authentication is applicable to all MySQL servers as it's a client who invokes this kind of authentication mechanism.

Questions:

Has anybody practically tried using PAM authentication or Cleartext authentication plugin?

If so please explain how to configure and setup with an example?

The Cleartext Authentication Plugin, as you have pointed out, is included in all distributions, but it's not quite correct to that it "is applicable to all MySQL servers."  Yes, it is, but actually, not really... because with Cleartext:

  There is no corresponding server-side plugin. Rather, the client-side plugin can be used by any server-side plugin that needs a clear text password

Okay, Oracle, which server-side plugins would those be?

The answer to that would appear to be "none of them," except, of course, the proprietary PAM plugin.

Of course, if you aren't using a plugin that needs it on the server-side, there's no reason you would use the cleartext plugin on the client side, and the only reason you'd need it on the server side would be because you were going to pass the credentials on to another system... like the PAM plugin does.

If you need the PAM authentication plugin's capabilities, the choices appear to be these:

write your own server-side plugin for PAM authentication or direct authentication against whatever backend system contains your access credentials
use Percona's PAM Authentication Plugin for MySQL which is free and open source and works in MySQL Server as well as in Percona Server
use MariaDB, which supports PAM authentication through its own native client mechanism since 5.2.10 and with some support for the MySQL cleartext client plugin as of MariaDB 5.5.32
pay Oracle's asking price for "Enterprise" MySQL and get the "official" plugin.


Partitioning and freeing disk space I am running MySql innodb with innodb_file_per_table.

As a daily script, I would like to create a new partition for a set of tables, as well as delete yesterday's partitions. What command would need to be run to delete the old partitions to free up the disk space?  I would like to avoid having a system that runs out of disk space because of the database disk usage.  I would also like to avoid having to shut down Mysql.

Edit: I have read that discarding the tablespace should delete the .ibd files that take up most of the space.

ALTER TABLE tbl_name DISCARD TABLESPACE;

However there is a known bug using this command on partitioned tables.

The status of the bug is labeled as Won't fix and I can understand why in this context.

The right way would have been to run ALTER TABLE tbl_name DROP PARTITION; because the MySQL Documentation on ALTER TABLE Partition Operations says:

  DROP PARTITION can be used to drop one or more RANGE or LIST partitions. This statement cannot be used with HASH or KEY partitions; instead, use COALESCE PARTITION (see below). Any data that was stored in the dropped partitions named in the partition_names list is discarded.

  ALTER TABLE t1 DROP PARTITION p0, p1;

Once you ran ALTER TABLE tbl_name DISCARD TABLESPACE;, the data dictionary should no longer be aware of the physical presence of the orphaned .ibd files. However, since the bug does not reclaim the diskspace, perhaps the data dictionary entries for your tables is intact. Let's find out.

WARNING : You should run service mysql stop and do a physical backup of /var/lib/mysql

First do this: SHOW CREATE TABLE tbl_name\G

Then, run SELECT * FROM tbl_name WHERE (stuff from partition #x) LIMIT 10;

to test the table's usability of each partition.

Once you are confortable with the table usability, you can any of the following:

REMOVE ANY PARTITION YOU WANT

ALTER TABLE tbl_name DROP PARTITION partition-name;

If you do not trust ALTER TABLE tbl_name DROP PARTITION partition-name;, do it manually

CREATE TABLE tbl_name2 LIKE tbl_name;
ALTER TABLE tbl_name2 DROP PARTITION partition-name;
INSERT INTO tbl_name2 SELECT * FROM tbl_name WHERE (anything but stuff from partition-name)
ALTER TABLE tbl_name RENAME tbl_name9;
ALTER TABLE tbl_name2 RENAME tbl_name;
DROP TABLE tbl_name9;

DROP ALL DATA FROM PARTITION BUT KEEP PARTITION

ALTER TABLE tbl_name TRUNCATE PARTITION partition-name;

If you do not trust ALTER TABLE tbl_name TRUNCATE PARTITION partition-name;, do it manually

CREATE TABLE tbl_name2 LIKE tbl_name;
INSERT INTO tbl_name2 SELECT * FROM tbl_name WHERE (anything but stuff from partition-name)
ALTER TABLE tbl_name RENAME tbl_name9;
ALTER TABLE tbl_name2 RENAME tbl_name;
DROP TABLE tbl_name9;

CREATE A BLANK TABLE

CREATE TABLE tbl_name2 LIKE tbl_name;
DROP TABLE tbl_name;
ALTER TABLE tbl_name2 RENAME tbl_name;

Give it a Try !!!

UPDATE 2013-05-20 18:41 EDT

I am glad you were able to drop the partition.

I have some distressing news: It is not possible to shrink ibdata1 without dumping all the data, deleting ibdata1, restarting mysql, and reloading.

Here are my past posts on how to do that safely:

May 21, 2012 : How large will a MySQL database be relative to the dump file?
Apr 01, 2012 : Is innodb_file_per_table advisable?
Mar 25, 2012 : Why does InnoDB store all databases in one file?
Feb 04, 2011 : MySQL InnoDB - innodb_file_per_table cons?
Oct 29, 2010 : Howto: Clean a mysql InnoDB storage engine?


Can I replicate to a pre-existing database? I am going to test this out, however I'd like to know what the forum would say.

I have 2 SQL servers with replication setup between them.

I am now adding another new SQL Server and I want this one to be the new publisher.

Do I need to recreate the databases on the old server when I setup replication again?

Can I simply stop subscription and then begin replicating from the new server to the same databases after I setup the machine?

Yes. After dropping the subscription, you will be able to simply configure replication from the new publisher to the existing subscriber. There is no need to drop the subscriber DB. Please bare in mind however that the subscriber will need to be re-initialized from the new publication. (Assuming transactional or merge publication) this can be achieved either through the snapshot agent or a backup/restore.

Also bare in mind that assuming your new publisher will also be your new distributor, you will need to configure this as well.


MariaDB Galera stop working without network connectivity I am running MariaDB Galera cluster whose using mariaDB and 3 nodes(node A, node B, node C). My nodes are placed locally in our office.

My problem is if node A/B/C lost network connection, it also lost it's working ability.

Say a application is connected to node A, then node A lost network connectivity, so when someone tries to read/write data on node A using physical access,say CLI, it doesn't work. It's showing,
 ERROR 1047 WSREP has not yet prepared node for application use

I want to use node A/B/C offline as well. When they get back the network connection, it will automatically replicate offline data to the connected nodes.

Thanks in advance,

Nixon

Galera can't do that, unfortunately, although you can configure it to handle shorter network outages. (See Galera's config tips for WAN replication.) Nodes have to be connected for Galera's global ordering to work so that it can merge writes from multiple nodes. See e.g. here

Maybe what your application could do instead if a node loses network and becomes unavailable (which could be detected by the application or a DB proxy) is to automatically fail over to a different cluster node. However, that still requires network connectivity to this other node.

So if your architecture needs to be tolerant against network losses between nodes, then you may have to look for a different solution.


9 node percona xtradb cluster We are looking to launch a 9 node percona xtradb cluster on Amazon where each ec2 node uses 20k iops disks.

I was afraid if a 9 node cluster will work and mostly what I see is 3 node clusters.

We are write heavy on some tables and also read heavy.

Any suggestions.

PXC and Galera Cluster do not write scale very well.
I mentioned this in my answer to the old post Transaction speed benchmarks for mySQL v5.6 replication - seems very slow where I said the following:
Please keep in mind that Percona XtraDB Cluster does not write scale very well to begin with. Note what the Documentation says under its drawbacks (Second Drawback):

This can’t be used as an effective write scaling solution. There might be some improvements in write throughput when you run write traffic to 2 nodes vs all traffic to 1 node, but you can’t expect a lot. All writes still have to go on all nodes.

What this means for you is this: The more nodes in the Cluster, the more network communication between nodes is required to certify that all 9 nodes can commit together or rollback together. This increased communication, which makes n(n-1) calls between nodes (where n is the number of nodes), will definitely become the bottleneck for a single INSERT, UPDATE, or DELETE. It becomes worse with multi-statement transactions. This is why most examples of PXC/Galera stress using three nodes. You could probably get away with 5 nodes but you should expect worse write performance than 3 nodes.
You could probably setup 3 different 3-node clusters and use one node from each cluster in Circular Replication (See my post MySQL 5.5 Replication to Galera/Percona XtraDB Cluster). This could easily create split brain scenarios if MySQL Replication breaks between clusters.
EPILOGUE
Please stick with 3-node cluster. Should you go with 9 nodes, you must live with the network communication across zones. If so, try to make nodes reside on the East Coast in different zones, or on the West Coast in different zones.


How can I tell if my SQL Server DB performance is hardware-limited? Testing an app currently under single-user load - as the test data has increased to production sizes (400k-2M rows per table), some SELECT sp's are not quite fast enough anymore (with limited test data, used to be <30ms each, now it's 100-200ms, but there are several, so the delay is becoming apparent in the UI).

You can use DBCC SQLPERF("waitstats"). This will return the wait times of what tasks your SQL server was waiting on. Detailed explanations of each counter can be found online. You can use this information to find out your bottlenecks.

Also, turn on the client statistics in query analyzer to see the wait times on the client side.

I am assuming you hardware has not changed since your initial test, so since they are constant, I wouldn't doubt them.


Nullable smallint column usage in a CASE Statement nested in a STUFF function I'm having issues writing a Stored Procedure that uses a CASE statement inside of a STUFF function. I thought my issue may have to do with Restricted being a nullable smallint. I tried converting it (can you even convert a smallint to an string?) and I'm still not getting results as expected. I'd like the WHEN and the ELSE to combine when they meet the specific conditions in my query, but instead it is completely eliminating this row from my results.

In the STUFF, if the row is Restricted, I’d like to tack on “(Restricted)” to the ItemStatus. If not Restricted (0 or NULL), I'd like to just tack on the ItemStatus.

I tried replacing the WHEN in the CASE statement with something like:

WHEN CONVERT(varchar(100),  ISNULL(Statuses2.Restricted, 0) ) = 0 THEN ', ' + dmStatuses2.ItemStatus + ' (Restricted)'

If I change the WHEN to:

WHEN Statuses2.Restricted IS NOT NULL THEN ', ' + dmStatuses2.ItemStatus + ' (Restricted)'

...I get the row to show up (but only with the ItemStatus. This column should have 2 strings concatenated as there is at least one Restricted and non-Restricted value)…but once I add an additional condition to try and check the value of Restricted (Restricted = 0 or Restricted = 1), I start not getting any results at all.

Any ideas of what I'm doing wrong?

SELECT
    StatusList
    --etc...
FROM
(
    SELECT
        StatusList
        --etc...
    FROM
    (
        SELECT
            StatusList,
            [Count] = ISNULL(COUNT(*), 0)
            --etc...
        FROM
            Items
        INNER JOIN
        (
            SELECT ROW_NUMBER() OVER (PARTITION BY /* some stuff */ ) as RowNum, Statuses.*,
                STUFF((
                    SELECT
                        CASE
                            WHEN Statuses2.Restricted = 1 THEN ', ' + dmStatuses2.ItemStatus + ' (Restricted)'
                            ELSE ', ' + dmStatuses2.ItemStatus
                        END
                    FROM Statuses Statuses2
                    WHERE
                        Statuses2.StatusId = Statuses.StatusId
                     FOR XML PATH('')
                ), 1, 2, '')
                     AS StatusList
            FROM Statuses
            INNER JOIN dmStatuses ON
                dmStatuses.StatusId = Statuses.StatusId
            WHERE
                Statuses.TableId = @TableId
        ) AS Statuses2 ON
            Statuses2.TableId = Items.TableId
            AND Statuses2.EndDt IS NULL
            AND RowNum = 1
        INNER JOIN dmStatuses dmStatuses2 ON
            dmStatuses2.StatusId = Statuses2.StatusId
        LEFT JOIN dmItems dmItems2 ON
            dmItems2.ItemId = Items.ItemId
        WHERE
            Items.TableId = @TableId
        GROUP BY
            Items.TableId,
            dmItems2.ItemId,
            Statuses2.StatusList
    ) AS ItemCounts
    LEFT JOIN dmItems ON
        dmItems.ItemId = ItemCounts.ItemId
) AS ItemCountsAndLimits
WHERE
    ItemCountsAndLimits.[Count] > ItemCountsAndLimits.Limit

Another way to create your StatusList is, using STRING_AGG (From SQL Server 2017):

SELECT STRING_AGG(x.status,',') FROM (
   SELECT
      ROW_NUMBER() OVER (PARTITION BY StatusId) as RowNum,
      CASE WHEN Status.Rescriced = 1 THEN ItemStatus + ' (Restricted)'
           ELSE ItemStatus END AS Status
   FROM Statuses) x

Above query is untested, and should be adapted to your situation.


Lead to Order Table design I have a User table having following fields:

id, name, phone, email

and an Order table:

id, order_name, order_desc, user_id (fk to User)

and a Lead table:

id, lead_desc, user_id (fk to User), lead_status

Now, I want to know for each lead (once the lead_status become closed), which order is created from this lead or no order is created from this lead. I am planning to have an order_id (with null=True) foreign key in Lead table to keep this mapping. But since Order table already have a user_id, it doesn't seem to be a good idea.

Is there a better way to do it? I am using Mysql.

One of the of goals of a relational database is to eliminate double storage of data. If you can find the right User for the Lead through the Order then you can leave out the reference in one of the tables (they are always the same as you mentioned in a comment). Keeping it in both tables makes your application more complicated.


Per person weighted answers Hi I'm new to database design but am trying to make sure I get things right first time so as not to pick up bad habits.
My question is I have a set of people for which I want to record needs mapped to importance.
Needs should be taken from a set to avoid duplicates creeping in. Each person will take a subset of these and grade their importance.
i.e.
needs =[tea, biscuits, toilet facilities, internet access]
People = [Phil, Bryan]

Phil needs tea and biscuits, tea with an importance of 10 biscuits with an importance of 3.

Bryan needs internet access and biscuits with importance of 4 and 7 respectively.

How would you suggest organizing the tables to implement this.
Thanks

What you need is a design like this:

Keep you list of people in one table.  Then keep another table with the list of things that various people might need.  Normalizing people and needed things is important for being able to write simple, reliable queries about your data from the perspective of either an individual person or a particular needed thing.

Use a third table to keep track of who needs what, and how badly they need it.


Creating a Maintenance Plan for a SQL Azure Database I need to create a maintenance plan for a number of SQL Azure databases.  Unfortunately, my many years of experience as a SQL Server DBA does not include experience with this task (sarcasm intended).

After a quick search, I found some information in an article here.  Using the information in this article and not knowing whether or not there was a better option, I decided upon using the second option (Database Maintenance Plans).  What follows makes the assumption that I understand the process correctly.

Using a local instance of SQL Server, I created a new maintenance plan.  From there, I added a new connection to one of the SQL Azure databases by using the Manage Connections option.  From there, I added a task to the designer pane.  I used the task for updating statistics but I imagine it could have been any other task.  Editing the properties of the task, I selected a single database.  That is about as far as I made it.  What follows is some of the observations I have made.

By default in the Update Statistics Task property page, the
option Tables and Views is selected for the Object item.  If
I change this option in my environment (for the SQL Azure database),
the dialog is immediately closed (almost as though it has crashed).
If I re-open the Update Statistics Task property page and then
click the View T-SQL button, an error is generated indicated that
login failed for the specified user.  From there, I re-opened the
Manage Connections dialog and checked the properties of the connection to SQL Azure and noticed that the password is blank
(almost as though the password is not being retained anywhere).
If I use a database on the local server this issue does not exist.

Am I proceeding in the correct direction?  If so, is there something I am missing?  Is there a better or more correct way of accomplishing this?

Instead of creating a maintenance plan on a local SQL Server instance to perform maintenance tasks on an Azure SQL database, consider creating a runbook using Azure Automation. The runbook should run  a PowerShell script like the following:

$AzureSQLServerName = "<ServerName>"
$AzureSQLDatabaseName = "<DatabaseName>"

$AzureSQLServerName = $AzureSQLServerName + ".database.windows.net"
$Cred = Get-AutomationPSCredential -Name "SQLLogin"
$SQLOutput = $(Invoke-Sqlcmd -ServerInstance $AzureSQLServerName -Username $Cred.UserName -Password $Cred.GetNetworkCredential().Password -Database $AzureSQLDatabaseName -Query "exec [dbo].[AzureSQLMaintenance] @Operation='all' ,@LogToTable=1" -QueryTimeout 65535 -ConnectionTimeout 60 -Verbose) 4>&1

Write-Output $SQLOutput

Please read detailed information here.


Add a column with Not Null I have a existing table in which i am adding a column as NgoId whose datatype is int. I want to add as NOT NULL to the column .

When I'm adding this I am getting the following error:

  Unable to modify table.    Cannot insert the value NULL into column
  'NgoId', table 'CSR.dbo.Tmp_tbl_post_category'; column does not allow
  nulls. INSERT fails. The statement has been terminated.

Please help how to add the column with NOT NULL.

As you already have data in the table, you cannot add a NOT NULL field, because the existing data will already violate the NOT NULL constraint.

You therefore have 2 choices:

a) Add the column with a DEFAULT value...

ALTER TABLE dbo.Tmp_tbl_post_category ADD NgoId INT DEFAULT 1;

...then run an update statement to correct the values to what they should actually be

b) Truncate the data from the table, add the column and then re-populate the table...

TRUNCATE TABLE dbo.Tmp_tbl_post_category;

ALTER TABLE dbo.Tmp_tbl_post_category ADD NgoId INT;

INSERT INTO dbo.Tmp_tbl_post_category...


Aggregate and GROUP BY conflict with HAVING? I don't know if my logic in my query is incorrect, or if I simply have a syntax error, or both.

For each customer that had more than two orders, I want to list the customerID and the total number of orders placed.  However, this query is producing the following error, (in MS Access 2016)

  The level clause includes a reserved word that is misspelled or missing. or the punctuation is incorrect.

SELECT CustomerID, COUNT(OrderID) AS OrderCount
FROM Order_T;
GROUP BY CustomerID
HAVING(COUNT(OrderID) > 2)

It produces no errors if I remove the HAVING clause, but then doesn't show only customers with more than 2 orders.

You have a semicolon after FROM Order_T. A semicolon is a statement delimiter, it should be at the end of the entire statement:

SELECT CustomerID, COUNT(OrderID) AS OrderCount
FROM Order_T
GROUP BY CustomerID
HAVING(COUNT(OrderID) > 2);  <-- there

I am puzzled about how your query can run without errors if you just remove HAVING, but the semicolon is definitely out of place in the posted code.


Database Design Advice for Expert Systems - Rule Based Systems Expert Systems are a branch of Artificial Intelligence. These systems try to capture knowledge of Experts via set of rules. Most of the rules are stated in natural language. But they need to be interpreted by rule engine therefore they are statements in the form of classic if-then-else.

IF

THEN

ELSE

This specific expert system will hold substances and patients. Every rule give an advice  according to patient's attributes , which is stored in database.

For example:

 IF (PatientWeight < 50 AND PatientSex = Male AND DaysInTreatment = 0)
 THEN SUBSTANCE_X = 10
 IF (PatientWeight < 50 AND PatientSex = Male AND DaysInTreatment > 0)
 THEN SUBSTANCE_X = 20 +  DaysInTreatment *5

I have never designed such a system. Can you give me some starting pointers to read? Or Any advice?

In university, I took courses where we worked with, and then built our own (very simple) expert systems in Prolog. I can't think of an RDBMS implementation that would be easy, or elegant. Most of them would probably end up as an EAV schema (which would work - I've seen and used such systems, but they're not pretty). One of the first hits on google for "prolog expert system" is this: http://www.amzi.com/ExpertSystemsInProlog/ It looks long, but might be a good resource.


Want to remove global_name from DB links (Oracle 11g) I am trying to call remote function via DB LINKS.

However, My global name contains the string ON and I believe it causes an error.

The Error I have is ...

SQL> var tmp varchar2(2);
SQL> execute :tmp := IF_TRX_SKY.CREATEWITH@SKYLBS_LINK2.I-ON.NET('a', 1, 2, 't', 't', 'k', 'm', 'r', 'u', 's', 's', 't', 'k', '00', sysdate, 1);
BEGIN :tmp := IF_TRX_SKY.CREATEWITH@SKYLBS_LINK2.I-ON.NET('a', 1, 2, 't', 't', 'k', 'm', 'r', 'u', 's', 's', 't', 'k', '00', sysdate, 1); END;

                                                   *
ERROR at line 1:
ORA-06550: line 1, column 52:
PLS-00103: Encountered the symbol "ON" when expecting one of the following:
( - + case mod new null <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
continue avg count current max min prior sql stddev sum
variance execute forall merge time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string> pipe
<an alternatively-quoted string literal with character set specification>
<an alternatively-quoted SQ

I assume that if I removed .I-ON.NET part, it would work.

How can I remove/escape this? Oracle documentation states that GLOBAL_NAME is required for replication. Then how can I call this remote function without removing the GLOBAL_NAME?

Here is my create statement to create DB LINKS

SQL> CREATE PUBLIC DATABASE LINK SKYLBS_LINK2
  2  CONNECT TO ICE
  3  IDENTIFIED BY ICE1234
  4  USING 'ORCL';

Database link created.

And this is setting what I have

NAME                             TYPE        VALUE
---------------------------- ----------- ----------------
global_names                    boolean      FALSE

FYI, I can not change config files or reboot database, since I don't have permissions.

Summary:

How to remove GLOBAL_NAME from DB LINKS which I never write in my create statement.
How to execute remote function which contains a forbidden string

To remove DB_DOMAIN from DB_LINKS.

ALTER SYSTEM SET global_names = false SCOPE = SPFILE;
ALTER SYSTEM SET db_domain = '' SCOPE = SPFILE;
UPDATE GLOBAL_NAME SET GLOBAL_NAME = [Name without db_domain];

then restart, DB and Listener.

However, I still do not know how to use this with domain.


Check Index Fragmentation on SQL Server Audit Queues I've read this excellent article, http://rusanu.com/2010/03/09/dealing-with-large-queues/, and seem to be having the same issue, due to a low space issue, the service broker was turned off on a database, and the sys.transmission_queue table grew to around 40 million records.  Turning the broker back on significantly increased the size of tempdb, and now I'm wondering whether my service queues are suffering index fragmentation.

I would normally use dm_db_index_physical_stats to check for index stats, but this doesn't work on queues, so how do I found out about index stats on internal tables, or can I even do so in SQL Server 2005?

I dug into this and it looks like the SQL Server team went out of their way to prevent internal table stats from being exposed through DMFs and DMVs.

I can't say I blame them, as the implementation of things like queues is always subject to change, but it doesn't help you out with your problem. I can see why in this case it's really difficult to balance between exposing metadata and exposing implementation details.

Anyway, let's play around with a few things and see what happens.

If I grab an object_id from sys.internal_tables and plug it into sys.dm_db_index_physical_stats (the obvious thing to do), something interesting happens -- on 2005, you get an empty result set and a level 16 error (msg 2561, state 10); on 2008 R2, you get just an empty result set with no error. In contrast, if you put in some random number on 2005 you get the same error, while on 2008 R2 you now get a different level 16 error (msg 2573, state 40).

This tells me there is some internal checking going on specifically to handle the case of calling this function (and sys.dm_db_index_operational_stats -- probably all of them) with a valid, yet non-user-table object_id. (Note: using the OBJECT_ID function on an internal table name prefixed with sys. does return a result.)

Since DBCC DBREINDEX "solves" the fragmentation problem, I figured I'd try out something else from the same era -- DBCC SHOWCONTIG, the precursor to sys.dm_db_index_physical_stats.

If I feed it a table name from sys.internal_tables (again, prefixed with sys.), it returns nothing -- no error, just a dissatisfying DBCC execution completed. If DBCC printed error messages, contact your system administrator. -- while giving it a random object_id results in a level 16 error (msg 2501, state 45). This is a pretty clear indication there's an explicit check for this case in the code.

I tried all this using a normal connection and the DAC with the same result. For the sake of curiosity, I even tried started the server in single-user mode and used the DAC, but that didn't change anything.

Hacking into the resource database to dump the definitions of the DMFs yielded nothing except internal-only OPENROWSET syntax, which isn't particularly helpful (kind of interesting, though).

While I was hacking into the resource database, I also dumped out the internal assemblies and used Reflector to look at what's inside, but that didn't yield anything (but was likewise interesting).

In any event, I think the only reasonable recourse is doing as the article suggests -- running the deprecated DBCC DBREINDEX from a job as frequently as you need. There just isn't any visibility into these kinds of stats, at least not that I can find, and to me at least, it appears that these stats have been intentionally hidden.

Sadly, even if you were okay writing your own algorithm, the undocumented DBCC IND fails the same way as DBCC SHOWCONTIG, which is really too bad, because this probably would have given you a decent shot. As an academic exercise you could do it with output from DBCC PAGE, but obviously that's nowhere close to a practical (or efficient) solution.


Is there any difference between 'LIMIT 0, 1' and 'LIMIT 1'? I recently stumbled upon example codes, which differed by these notations.

SELECT * FROM table LIMIT 0, 1
SELECT * FROM table LIMIT 1

The first argument should be considered as the offset if I'm not wrong, the purpose of those two queries is to limit the selection to up the first row of table

Is there any negative effect when leaving out the offset/how is it possible to leave it out in the first place? Or did I misunderstood the queries?

As the documentation says:

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements).
With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1).

The LIMIT clause is used in the SELECT statement to constrain the number of rows in a result set. The LIMIT clause accepts one or two arguments. The values of both arguments must be zero or positive integer constants.
The following illustrates the LIMIT clause syntax with 2 arguments:
SELECT * FROM tbl
LIMIT offset, count;

Let’s see what the offset and count mean in the LIMIT clause:

The offset specifies the offset of the first row to return. The offset of the first row is 0, not 1.
The count specifies maximum number of rows to return.

When you use LIMIT with one argument, this argument will be used to specifies the maximum number of rows to return from the beginning of the result set.
SELECT * FROM tbl
LIMIT count;
The query above is equivalent to the following query with the LIMIT clause that accepts two arguments:
SELECT * FROM tbl
LIMIT 0, count;
The LIMIT clause often used with ORDER BY clause. First, you use the ORDER BY clause to sort the result set based on a certain criteria, and then you use LIMIT clause to find lowest or highest values.


how to import mysql system "mysql" database This question might be already answered but it's almost impossible to google it.

When I perform full backup of mysql server using

mysqldump --all-databases

then reinstall it from scratch I want to import FULL dump of all databases, including "mysql".

I successfully done that by executing

mysql -u -p < dumpfile

but now even if all user records are in mysql.user these accounts are not working.

I can't even change their password, I always get

  ERROR 1133 (42000): Can't find any matching row in the user table

Restarting mysqld is of no help

answer:

It was fixed by executing

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.26 sec)

now all users work as before

mysqldump actually has an option to run FLUSH PRIVILEGES; after the dump is completed
[root@**** ~]# mysqldump --help | grep privileges
  --flush-privileges  Emit a FLUSH PRIVILEGES statement after dumping the mysql
flush-privileges                  FALSE

In light of this you would run
mysqldump --all-databases --flush-privileges > dumpfile

Running mysqldump against a MySQL Instance and reloading back into the same MySQL Instance should not annihilate user privileges requiring you to run FLUSH PRIVILEGES; by hand. This may be a problem with MariaDB. You should test this on a Dev Server. You should also see the open bug list of MariaDB to see if this is already a known issue.
I have recommended using --flush-privileges in my past posts

Feb 03, 2012 : How to resolve the master server shut down/unavailability in mysql with master - slave replication
Jan 19, 2012 : How do I replicate Mysql DB server to new server?
Dec 05, 2011 : Set up a MySQL slave using mysqldump to get the initial data
Jun 26, 2011 : How to enable all the possible backup options of MySQL

CAVEAT
Please keep in mind that grants are stored in RAM. Running FLUSH PRIVILEGES; simply reads
mysql.user and reloads the grants into RAM. The error message you mentioned earlier

ERROR 1133 (42000): Can't find any matching row in the user table

may be not referring to the physical mysql.user table, but the in-memory copy of it. Again, I emphasize that MariaDB may be mishandling user grants upon reload.
What worries me further is the fact that you restarted mysql (MariaDB) and the grants still did not work? At the risk of sounding redundant, this may be a problem with MariaDB. You should test this on a Dev Server. You should also see the open bug list of MariaDB to see if this is already a known issue.


How to change the default output format for boolean values In a Postgres database, I know how to use case statements to get only TRUE or FALSE for a boolean field but was wondering whether there is a cleaner way to set this up like changing any configuration in the db.

Update: in Postgres, a the boolean representation for true is not only TRUE but also: yes, y, on, "t". So, I am inserting values with value TRUE. However, the SELECT command returns those values as "t".

There is probably something that I don't understand about your question but given a table with a boolean attribute like:

create table T ( b boolean not null );

You can only insert True, False. Under what circumstances would you need a CASE expression?

Edit:

reading your comments above, I guess you are interested in a textual representation of t/f. Not sure if it fits your needs but you can cast it to text:

select b::text from T

If you want it to be TRUE/FALSE I guess you will have to:

select upper(b::text) from T

Perhaps a function would be cleaner?

CREATE FUNCTION showbool(boolean) RETURNS text
    AS $$ SELECT UPPER(CAST($1 AS text)) $$
    LANGUAGE SQL;

select showbool(b) from T


What if I want to keep using MyISAM In my current use of MyISAM the databases are extremely small and kept for a few weeks after which time they are deleted and completely rebuilt with a clean slate.  The script uses MYISAM tables and other than lack of support in the latest MYSQL versions, I have never had a problem with the way they work.
Changing to INNODB is something that would be time consuming and provide a zero benefit.
However, I am concerned that eventually there may come a time when either the Older MySQL version is not available or some other process forces the move.

My question is: Does anyone know of a specific reason to change from MyISAM to MyinnoDB just to keep up with MySQL.

If I can keep using what works for my own specific use, I see no reason to worry with testing and possible errors when there are more pressing issues that do need attention that have problems now.  MyISAM does not have a problem at this time.

Plenty of good reasons to move to InnoDB.

MyISAM has no crash recovery mechanism. A power break may render your tables useless. They can be corrupted (I've seen this happen plenty of times) and you may be able to salvage data -- or you may not be able to salvage data (and I've witnessed this as well multiple times). With InnoDB you have crash recovery mechanism.
MyISAM has no "hot backup", with the possible exception of file system/lvm/disk snapshots, which do require you to flush all tables to disk. You say your databases are extremely small, so this may not appeal to you too much; but the time it takes to flush your entire database to disk can be quite long for larger datasets. You have multiple ways of hot-backing up an InnoDB database.
MyISAM has poor index-write performance. Because of its lack of crash recovery mechanism, any write to an idnex page is immediately flushed to disk. I once consulted to a company where they had this MyISAM table being written to intensively -- it was so hard on the disk they had to put it on its very own server. Changed to InnoDB and Voila! All was relaxed: the transaction log takes care of all that for you.
MyISAM is very poor on highly concurrent apps. You can't write to the same table from two connections at the same time. This quickly leads to a bottleneck on heavy traffic sites, for example.
Likewise, once you do get a write lock on a table, no one else is then able to read from it (with a minor exception for specific INSERT types). With InnoDB you can have concurrent writes and non blocking reads.
MyISAM is hardly under any type of development. I don't recall any blog post by Oracle or anyone else saying "we've just improved foobar on MyISAM". InnoDB is under intensive development. You're likely to get new features and bugs fixes in InnoDB; not in MyISAM.

The above off the top of my head. I'm sure plenty more reasons will pop up once I submit; but these provide the general feeling for the disadvantages of MyISAM.

But - you said your databases are extremely small. Why then would it be so time consuming to migrate to InnoDB?


PgAdmin 4 installation on Debian/Ubuntu PgAdmin 4 1.0 has been just released, but on the download page for Linux there is only a link to a yum installer.

Anybody knows if there are plans to create a deb installer? Or if there is already one?

There are instructions on the pgAdmin site how to install with pip:

https://www.pgadmin.org/download/pip4.php

But there is no official Debian Package (.deb), yet. A request for packaging is pending:

https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=834129

For now, consider the Postgres Apt repository:

https://wiki.postgresql.org/wiki/Apt/FAQ


How can i replicate many database into one, by avoid primary key collision? I have n number of databases and I have to collect data from those into one database. The databases has some specific value (e.g. currencies) which I want to update from the collector database(of course I want this to update for all databases).
So in the first case I need to avoid primary key collision because i don't want to loose data. I think that setting up auto_increment.increment and auto_increment.offset, can do the trick, but in the second case i have to identify specific rows in all databases.
What kind of technology and topology can I use?

I have a data warehouse that multiple sources of data feed into. I generate a new PK, but bring in the original PK from the source along with it. With a lot of the feeds I also bring in a 'source key' that's typically an environment variable or something to identify the actual source, and then create a composite key to differentiate between original PKs that may be the same and from different sources. In my aggregations this data is stored, and update a dimension to keep a record of these sources, so I can query based on that table.

I read a Brent Ozar post a while back of a technique to alter the seed for one lot to subtract from 0 so you end up with negative integers. For more than 2 you could expand it with number patterns (odd/even, multiples, ending-in). It wasn't so much for this scenario, as you need a lot of foresight to choose which pattern to implement based on the number of sources you think you may have.


How to select all rows from column including two specific numbers within a set of digits, but not in beginning or end? I'm attempting to retrieve all rows that include two specific characters within a set of characters corresponding to the attributable column of data. However, the specified characters can't be included within the beginning or the end of that series of characters.

My attempt currently displays the specified characters but includes beginning and ending characters. However I only want to display those characters contained in between the sequence.

I tried this query:

SELECT table_id,
       table_number
FROM TABLE
WHERE table_number LIKE '%99%';

You could just use %_99_% instead of %99%.

% means 0 ore more characters
_ means exactly 1 character

So %_99_% means at least 1 character before and after 99, and anything before and after.

SQL> select * from t1;

C1
--------------------
1991
1199
9911

SQL> select * from t1 where c1 like '%_99_%';

C1
--------------------
1991


In which normal form is the relation R? Hello Community,
To prepare for my exam I have completed many exercises. Most of them were solvable without any problems. But the following task I could not solve until now. It is:

You are given the relation scheme R (A,B,C,D) with the three functional dependencies:

AB → C
C → D
D → A

In which normal form is R (1st, 2nd or 3rd)? Justify your statement.

Could anyone of you please help me? A good explanation and substantiation would be very, very good!
Thank you sooo much!
P.S.: I already have one solution from a friend. But I do not know if it is correct…
He said that it is the second normal form because the non-key attributes C and D are fully functional depending on A, B. Besides he mentioned that it is not the third normal form because there is a transitive dependence between the non-key attributes (A, B → C and C → D).

Because we have the candidate keys AB, BC and BD, we have no non-key attributes. This means that the second normal form is approved, because no non-key attribute partially depends on a key. It follows that the relation scheme R must be in the third normal form!


Does my database design work? Newbie! I've uploaded an image of my database design below. This is the first database I have designed and I was hoping that I could get some input into whether or not it is set up correctly or if I just have no idea what I'm doing! I am using MySQL. It is for an rpg game. Thanks so much for your time.

Overview of what it should do:

Each user should be able to own multiple characters,
But each character should be owned by only one user
Each Character should have only one location
But many Characters can exist in one location
Each character should own one container (inventory)
But each container should only belong to one character/NPC (a container cannot be owned by both a character and an NPC)
Each container may or may not have only one location
But one location can have many containers inside of it
Each item/weapon/armor should either exist inside of one container or have one location
But one container or location can have many item/weapon/armor
Each NPC should have one location
But one location can have many NPC

Each user should be able to own multiple characters

  But each character should be owned by only one user

Then there is no need for user_character. A foreign key column in character would suffice.

I'd suggest you roll character and npc into the one table, perhaps with a flag to distinguish.  This will save a lot of duplication and UNION statements later.

Combine all the item/ weapon/ armour into a single item table, perhaps with a foreign key from an item_type table. This will remove a lot of complication, too.


Can I do a standard restore/recover using image file backups? We have a database backed up with incrementally updated image file backups.  I understand the typical use case would be to switch to the copies, but would a point in time recovery using restore/recover also work?  I'm considering this route because the storage for space for the copies is slower.

I believe the answer is Yes.  Here is the supporting evidence:

oracle-base.com - Incrementally Updated Backups: Rolling Forward Image Copies Using RMAN


Optimize a query with two range conditions I have a structure similar to this one:

CREATE TABLE `author` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

CREATE TABLE `book` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `author_id` int(11) unsigned NOT NULL,
  `org` int(11) unsigned NOT NULL,
  `country` char(3) NOT NULL,
  `publish_date` date NOT NULL,
  `price` decimal(6,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `author_id` (`author_id`),
  KEY `publish_date` (`publish_date`),
  KEY `i0` (`country`, `org`, `author_id`, `price`, `publish_date`),
  KEY `i1` (`country`, `org`, `author_id`, `publish_date`, `price`)
) ENGINE=InnoDB CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

Here is a SQL Fiddle

So I'm trying to execute a query with two range conditions

SELECT
  id as author_id,
  (SELECT COUNT(DISTINCT `book`.`id`)+1
   FROM `book`
   WHERE
      `book`.`org` = 1
      AND `book`.`country` = 'USA'
      AND `book`.`publish_date` BETWEEN '2010-04-30' AND '2011-04-30'
      AND `book`.`author_id` = `author`.`id`
      AND `book`.`price` < 50
  ) AS `books_under_fifty`
FROM `author`
ORDER BY books_under_fifty desc;

but the optimizer uses only part of my index: const,const,db_9_6349e2.author.id from i0

is there a way to optimize it?

Well, let's think out of the box.  It seems that there are two sets of results -- the authors with some such books, and those without.  The first case is more efficiently done via:

SELECT  author_id,
        COUNT(*)+1 AS books_under_fifty
    FROM  `book`
    WHERE  `org` = 1
      AND  `country` = 'USA'
      AND  publish_date >= '2010-05-01'
      AND  publish_date <  '2010-05-01' + INTERVAL 1 YEAR
      AND  `price` < 50

That would probably be best served by

INDEX(country, org, publish_date, price, author_id)

If you are happy with the performance of that, then you can think out of the box in finding "the rest of the authors".

Back to the 2-ranges problem.  That can sometimes be solved by Partitioning.  Not knowing the distribution of the data (is one year a small subset of the total? or what about price?), I can't say which would be better:

PARTITION BY RANGE(TO_DAYS(publish_date))

Partitioning by price is out, since DECIMAL can't be used.  Storing price as number of cents and using PARTITION BY RANGE(cents) would work, but clumsily.

Then make 20-50 partitions of the partition key.  This would give you "partition pruning" for one 'range', but then the index needs to be tweaked for the other.  Assuming you partition by publish_date:

INDEX(country, org, price)
PRIMARY KEY(id, publish_date)

Meanwhile, ...

Don't use utf8 if you are using standard country_codes; use ascii, at least for that column.

What is org does it distinguish the book?  Or are there some missing columns?  Is there no other "unique" column(s)?

Another variant that might be better:

 PRIMARY KEY(country, org, price,  -- for clustering
             publish_date,   -- because partitioning requires it
             id)           -- to assure uniqueness
 INDEX(id)

This variant assumes you have no 'natural' PK.  And it will be more efficient because it clusters the desired rows together.  I did not include author_id in the PK since it is readily available in the row.

add index

ADD INDEX ac(author_id, country) tricked it into using i0.  Go figure.  Note:  I left (author_id) intact and first in the list of indexes; so it is not just the order of the indexes.

I'm using Percona's 5.6.22-71.0-log .

Note that there are only 4 different country values, evenly distributed.  But there are many different author_id, making it by itself more "selective".


Connecting to mySQL from 2 different machines ALL,

Is it possible to modify the permissions of the MySQL DB or I will need to revoke it and grant it again?

My problem is as follows:

I'm running MySQL server on Gentoo Linux and I'm trying to connect remotely from 2 different Windows machine (all computers are on the same network in my home).

When I had trouble connecting I asked a question on Gentoo forum and got a suggestion to run:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION ;

My guess is that I used a static IP address of one of the machines because I can connect from one Windows machine but not from the other.

So now, my question would be - is it possible to issue something like:

ALTER PRIVILEGES ON.....;

Or maybe I can do some query to fix this?

Thank you.

BTW, a little off-topic: I couldn't find a tag for "remote-access". Could someone please create it? Or its not present for a reason?

EDIT:

I actually want the permissions to be like this:

GRANT ALL ON foo.* TO bar@'192.168.1.x' IDENTIFIED BY 'PASSWORD';

so that I can connect only from 192.168.1.1 - 192.168.1.254. I don't need this server to be publicly available.

EDIT2:

GRANT USAGE ON *.* TO 'root'@'192.168.1.4' IDENTIFIED BY PASSWORD '*DC365D603F605E79DE2F2B8DA969DBB8497BEC89'
GRANT ALL PRIVILEGES ON `draft`.* TO 'root'@'192.168.1.4' WITH GRANT OPTION

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*DC365D603F605E79DE2F2B8DA969DBB8497BEC89' WITH GRANT OPTION
GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION

EDIT3:

I just tried to execute following command:

GRANT ALL PRIVILEGES ON *.* TO 'root'@192.168.1.% IDENTIFIED BY '<my_password>' WITH GRANT OPTION;

and I got following response:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '% IDENTIFIED BY '<my_password>'' at line 1 0.00041 sec

So what you can do :

If you want to change IP from 192.168.1.4 to 192.168.1.3 then you can update mysql.user table

Update mysql.user  set host =' 192.168.1.3 where host='192.168.1.3' ;

Flush privileges:

You cannot define a range of hostname in single query of creating user. Either you need to create same user for different IP or you can give % as hostname but i will not recommend this

If you want different user , create same user with required IP.

Let me know if need more clarity

EDIT

You cannot define a range for hostname in mysql.user table.
To have this you can create same user with same role but on different IP. You have to do this for every IP individually.

UPDATE

Please try creating user :

grant select to 'root'@'192.168.1.%' on db.* Identified by 'pass' ;

UPDATE 2

This is because IP should be placed between '' as a string.

So here you go:

GRANT ALL PRIVILEGES ON db_name.table_name TO 'root'@'192.168.1.%' IDENTIFIED BY '' WITH GRANT OPTION;


SQL Server Audit not tracking changes at the database level I'm currently trying to create a test implementation of SQL Server Audit in SQL Server Management Studio to see how it works. I created an test audit at the server level, and another one at the database level.  In SQL Server, audits are disabled by default when they are first created, so I enabled both of them and tested to see if it worked.  I updated several rows and deleted a role in a table that was in the tracked database.  When I went to check the log, it didn't show any changes at the database level.  I made a few changes (described below), and it did reflect those changes in the SQL Server Audit log.

First, I thought the log might need to be refreshed, so I refreshed the logs at both the database and server level. I restarted SQL Server Management Studio.  Still wasn't working.  I tried to specify which database to track at the server level by using the filter tool in the Properties menu by adding "([database_name]='test_database_name')".

Then, I thought maybe the problem was occurring at the database level, so I rechecked the configuration settings in the database audit properties menu. When selecting the Audit Action Types you want to track, you also have to set the Object Class, the Object Name, and the Principle.  At first, I had set all of the Audit Action Types to track at the database level because I want to track all of the tables in the database, but I changed the Database to Object and selected all of the table names in the Object Name field. I'm not exactly sure what the Principal field does, but you have to select a particular user for the Principal.  I started by selecting the Administrator role because I'm working on a test database, but I changed it to [sys]. None of these changes have had an effect.

Can anyone think of any reasons why the Server Audit log wouldn't be registering changes?

I think the main problem here - and please don't take offense - is that you're pointing and clicking in a GUI but not really sure what you're pointing and clicking at.

Here is an example that creates a server-level audit, then adds a database-level audit specification to track multiple operations on any object in the dbo schema.

USE master;
GO

-- create aserver audit
CREATE SERVER AUDIT Test_Server_Audit
  TO FILE ( FILEPATH = 'C:\temp\' ); -- you may need to change that'
GO

-- turn it on
ALTER SERVER AUDIT Test_Server_Audit WITH (STATE = ON);
GO

-- create a demo database
CREATE DATABASE floob;
GO
USE floob;
GO
CREATE TABLE dbo.blat(x INT);
GO

-- create a database audit specification that monitors for activity
-- against any dbo object:
CREATE DATABASE AUDIT SPECIFICATION Test_Database_Audit
    FOR SERVER AUDIT Test_Server_Audit
    ADD (SELECT, UPDATE, DELETE, INSERT, EXECUTE ON SCHEMA::dbo BY PUBLIC)
    WITH (STATE = ON);
GO

-- do a couple of things:
SELECT * FROM dbo.blat;
DELETE dbo.blat;
GO

-- you should see those couple of things in the audit file:
SELECT * FROM sys.fn_get_audit_file('C:\temp\*.sqlaudit', NULL, NULL);
GO

Now, clean up:

ALTER DATABASE AUDIT SPECIFICATION Test_Database_Audit
    WITH (STATE = OFF);
GO
DROP DATABASE AUDIT SPECIFICATION Test_Database_Audit;
GO
USE master;
GO
ALTER DATABASE floob SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE floob;
GO

ALTER SERVER AUDIT Test_Server_Audit
    WITH (STATE = OFF);
GO
DROP SERVER AUDIT Test_Server_Audit;
GO

I'd start at perhaps a higher conceptual level before pressing a bunch of buttons and hoping that they work.


How to rollback database change in production after few hours or days We are using Oracle 12c in production. Lets say there was release that went to production on Sunday and then some hours or some days later(e.g. Tuesday) we realized that we need to rollback the changes we did, assume there were DDL schema changes, along with DML changes which could be inserts, updates, deletes.
What is the best practice to rollback the changes? we can not restore database from backup because backup was from Sunday and there is data from Sunday to lets say Tuesday.
Just want to know what is the best practice for rolling back database changes in Oracle 12c.

In your scenario, the best OPTION is to use FLASHBACK DATABASE. It is as simple as it gets:
1.Create a restore point flashback database guarantee
create restore point my_save_point guarantee flashback database;

2.You apply your changes in Production, DDLs and DMLs. After some QA tests you realise something went wrong and you want to go to the save point
flashback datatabase to my_save_point ;

That last command will rewind the database to that moment in time, making a rollback of all the changes done from the moment you create the restore point.
I used a lot restore points for roll-outs to Production when they contain substantial changes that are very difficult to rollback using scripts.


Which terms are used to refer a row in database? I am creating a Java application, in which I want to create a class that will relate to a row in database.

I decided to use Entity while naming the class with a prefix/suffix. But,
This page says:

  An entity is a lightweight persistence domain object. Typically, an entity represents a table in a relational database, and each entity instance corresponds to a row in that table.

I am too conscious & put much time while naming classes, packages etc.

So, what are the other terms, a row in a database can be referred with?

It's right to use Entity name for prefix/suffix to represent a table schema as your class Entity is a mapping to table schema not the data of the table.

And in terms of data mapping a single instance of the Entity will always represent the row as the row will have all those columns which are attributes in entity and a List of the Entity will represent a table.


Duplicate Ips for cluster nodes causing backup issues We have a four node cluster (SQL Server 2014 on Windows 12) and we recently setup heart beat network on the nodes following a best practices source after experiencing some cluster instability issues. Ever since a couple of secondary nodes are returning multiple IPs in the DNS. We have removed the entries from the DNS server for both the nodes multiple times but the IPs of the heartbeat network (the IPs are supposed to be non-routable) keep popping ultimately returning two IPs for the same node and this is causing issues with our backups. I have checked the settings on all four nodes and they are the same in the network adapter properties. What am I doing wrong?

PS: In addition, I have also changed some settings in the TCP/IPv4 properties such as removing the DNS server addresses and unchecking the 'register this connection's address in DNS'

We have a four node cluster (SQL Server 2014 on Windows 12) and we recently setup heart beat network on the nodes following a best practices source after experiencing some cluster instability issues.

Health checking goes across any available interface, some interfaces have metrics lower or higher than others but the loss of a single interface should be handled assuming there are other interfaces available. Just for future reference there is no heartbeat network, specifically I believe since 2008 came out.

  Ever since a couple of secondary nodes are returning multiple IPs in the DNS.

You've found the issue, duplicate IPs on the network. Look at all of the adaptors and figure out which are duplicated... or better yet, run the cluster validation wizard and have it do the heavy lifting for you!

  We have removed the entries from the DNS server for both the nodes multiple times but the IPs of the heartbeat network (the IPs are supposed to be non-routable) keep popping ultimately returning two IPs for the same node and this is causing issues with our backups.

This doesn't make much sense to me... if the underlying issue is that there are duplicated IP addresses (assuming IPv4) then the fix shouldn't be deleting records from DNS, that'll just delay the inevitable which would be that they register back with DNS at some point in the future. The fix would be to identify which adaptors and interfaces are improperly configured.

Non-routable doesn't mean it can't register or otherwise talk with anything else. It obviously is getting registered in DNS and can talk to other servers.

So, yes, this seems like it's working as it's been configured though configured incorrectly.

  I have checked the settings on all four nodes and they are the same in the network adapter properties. What am I doing wrong?

My guess is there is a duplicate IP address (tongue-in-cheek). Give the cluster validation wizard a run and choose the networking tests, that should give some actionable output.

  PS: In addition, I have also changed some settings in the TCP/IPv4 properties such as removing the DNS server addresses and unchecking the 'register this connection's address in DNS'.

That's probably not a good thing to do and could have undesirable side effects... like the cluster not working at all. Again, you know the root issue so instead of managing the symptoms fix the root cause.


Oracle 11g DBMS_CRYPTO invalid identifier I'm trying to encrypt a column and I found many tutorials that use DBMS_CRYPTO.
However, when I test this, it says DBMS_CRYPTO is an invalid identifier.
I was using SYSDBA account.

I've searched the Internet but, to my surprise, I couldn't find an answer for this one.

How come DBMS_CRYPTO is an invalid identifier ?

DBMS_CRYPTO is a package which is owned by SYS schema. You can ask the DBA to give the execute privilege on that package to the schema which you are using.

Here DBA has to login as SYS. Even SYSTEM or any other schema for that mater, which has SYSDBA role won't do.