mysqldump with LIMIT and --order-by-primary I want to use mysqldump to retrieve X first rows from a database (where "first" are ordered by primary key).

I'm trying to use mysqldump the following way:

mysqldump [connection-info] -where"1=1 LIMIT 40" --order-by-primary database table >> file.sql

(this is inside a script who runs different configurations for different tables)

Now, the problem, that command creates the following query:

SELECT /*!40001 SQL_NO_CACHE */ * FROMtableWHERE 1=1 LIMIT 40 ORDER BY primary_key

Is an invalid query, I need the LIMIT 40 to be placed after the ORDER BY statement.

My guess is that you cant use limit in combination with --order-by-primary

One way around this is to define the order by within the -where argument. If you know the primary key for the table:

mysqldump -u lelle -p -w"1 = 1 order by <primary key> LIMIT 40" <database> <table>

Note that <primary key> is a place holder for the columns in the primary key.

if you don't know the primary key in advance you can use the INFORMATION_SCHEMA to determine that:

select GROUP_CONCAT(column_name SEPARATOR ',')
from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
where CONSTRAINT_NAME = 'PRIMARY'
  and CONSTRAINT_SCHEMA = ... and table_name = ...;

and then use the information in mysqldump.


Storing data files in separate physical drives Does storing sql server data files for 1 database in different drives gain performance? Is it recommended to do so? Any benefits gained from splitting the data files to multiple physical drives? There is no partition tables .The db is oltp which is read and write intensive.

Example database
AdventureWorks

D:\Data\AdventureWorks_1.mdf
D:\Data\AdventureWorks_1.ndf
E:\Data\AdventureWorks_2.ndf
F:\Data\AdventureWorks_3.ndf
F:\Data\AdventureWorks_4.ndf

To answer the question, generally the best option is the S.A.M.E. for all databases!

Stripe And Mirror Everything - also known as RAID 10 (or RAID 1+0).

  According to manufacturer specifications and official independent
  benchmarks, in most cases RAID 10[8] provides better throughput and
  latency than all other RAID levels[9] except RAID 0 (which wins in
  throughput).[10] Thus, it is the preferable RAID level for
  I/O-intensive applications such as database, email, and web servers,
  as well as for any other use requiring high disk performance.[11]

As you can see from the oracle-base link (an absolutely super site for all things Oracle BTW), you can see that this is the preferred general RAID level for Oracle - with the proviso that one can delve a bit deeper and use other RAID levels for different file types (data, logs, control files...). See the table in the link below,

but this is the take-home message (IMHO) - in the RAID Levels section:

  1+0 or 10     As the name suggests this is a combination of RAID 1 and
  RAID 0. This sounds like it should be the same as 0+1, but it is
  subtly different. Each disk is mirrored individually and striping
  occurs across all the mirrored pairs. This is the best RAID for
  Oracle.

So, 1+0 is definitely the preferred option.

Microsoft's page RAID Levels and SQL Server also says:

  RAID 10 provides the performance benefits of disk striping with the
  disk redundancy of mirroring. RAID 10 provides the highest
  read-and-write performance of any one of the other RAID levels, but at
  the expense of using two times as many disks.

Note that last bit but at the expense of using two times as many disks - one gets nothing for nothing! "Yae cannae beet the laws o' physics, Jim..." (with apologies to Gene Roddenberry).

For completeness, Severalnines (a top PostgreSQL consulting company) says:

  Setting up RAID for the database drives not only protects from data
  loss, it can also improve performance if using the right RAID
  configuration. RAID 1 or 10 are generally thought to be the best, and
  10 offers parity and overall speed.

and for MySQL, consider Percona's advice (Percona are a very highly regarded MySQL consulting group with their own fork of the server):

  The best performance without making compromises to redundancy is
  achieved by the use of an advanced controller with a battery-backed
  cache unit and preferably RAID-10 volumes spanned across multiple
  disks.

So, again generally, RAID 1+0 is considered the optimal solution. However, if you read around this topic, you will see that there are issues such as expense - no organisation has infinite resources, so decisions are sometimes made on budgetary grounds not to go with RAID 1+0 but rather RAID 5.

This, IMHO, is a huge mistake - it's a false economy. You might be saving some money on disks, but your employees' and customers' time (and sanity) are valuable resources in their own right (painful past experience, but I've had therapy and I'm OK now...).

So, ideally, it is better to spread data across disks. To answer the part of your question about if one is running more than one database on the same server, then questions of multi-tenancy arise.

For example, you have to ask yourself "If I take down the system, that's two clients gone or would I rather be able to take them down separately if required?". That's a question which only the stakeholders in your own organisation can answer - Management, Customers, DBA's...

Personlly, I would keep separate clients' data as separate as possible, but again you have budget considerations - a separate disk subsystem for each client, software licencing issues and the like. My advice is to read deeply around the area in order to be able to provide a sound strategy for your own organisation given the resources to hand.

p.s. +1 for an interesting question and welcome to the forum!


Designing An ACL Based Permission System I am trying to create a permissions system where everything is going to be stored in MySQL (or some database) and pulled using PHP for a project management system I am building.  I am right now trying to do it is an ACL kind of way.  There are a number key features I want to be able to support:

1.  Being able to assign permissions without being tied to a specific object.

The reason for this is that I want to be able to selectively show/hide elements of the UI based on permissions at a point where I am not directly looking at a domain object instance.  For instance, a button to create a new project should only be shown to users that have the pm.project.create permission but obviously you can assign a create permission to an domain object instance (as it is already created).

2.  Not have to assign permissions for every single object.

Obviously creating permissions entries for every single object (projects, tickets, comments, etc…) would become a nightmare to maintain so I want to have some level of permission inheritance.

3.  Be able to filter queries based on permissions.

This would be a really nice to have but I am not sure if it is possible.  Here's an example: I have a page that lists all projects.  I want the query that pulls all projects to incorporate the ACL so that it would not show projects that the current user does not have pm.project.read access to.  This would have to be incorporated into the main query as if it is a process that is done after that main query (which I know I could do) certain features like pagination become much more difficult.

Right now this is my basic design for the tables:

AclEntities

id - the primary key
key - the unique identifier for the domain object (usually the primary key of that object)
parentId - the parent of the domain object (like the project object if this was a ticket object)
aclDomainObjectId - metadata about the domain object

AclDomainObjects

id - primary key
title - simple string to unique identify the domain object(ie. project, ticket, comment, etc…)
fullyQualifiedClassName - the fully qualified class name for use in code (I am using namespaces)

There would also be tables mapping AclEntities to Users and UserGroups.
I also have this interface that all acl entity based object have to implement:

IAclEntity

getAclKey() - to the the unique key for this specific instance of the acl domain object (generally return the primary key or a concatenated string of a composite primary key)
getAclTitle() - to get the unique title for the domain object (generally just returning a static string)
getAclDisplayString() - get the string that represents this entity (generally one or more field on the object)
getAclParentEntity() - get the parent acl entity object (or null if no parent)
getAclEntity() - get the acl enitty object for this instance of the domain object (or null if one has not been created yet)
hasPermission($permissionString, $user = null) - whether or not the user has the permission for this instance of the domain object
static getFromAclEntityId($aclEntityId) - get a specific instance of the domain object from an acl entity id.

Do any of these features I am looking for seems hard to support or are just way off base?
Am I missing or not taking in account anything in my implementation?
Is performance something I should keep in mind?

I did something like this a long time ago.  Yes it is possible.  No it isn't too hard on the technical side.  I don't think you will have problems in the areas you are asking about.  Really this is pretty basic stuff once you figure out how acls are supposed to behave.

The issue you will run into has to do with consistently enforcing the acls.  It is very easy to forget it once in a call somewhere and you suddenly have the acls not doing what they are supposed to. Oops, security issue.

If you are on PostgreSQL, you could probably use SQL roles and views to enforce this properly on select statements, and so forth.  On MySQL I think this is likely to be more difficult.  You will probably want to have some consistent way of adding clauses to your queries so that you are able to ensure they are enforced all the time.  Do this programmatically. Do not try to do it manually unless you want security holes.


How to export table definitions and data in mysql workbench I am using mysql workbench 6.3 and I can´t find a way to generate a sql file with one table definition and data.

I could find where to export data or table definition but not both.

I think you are looking for the following window on workbench.


Using SQL Server 2017, can log backups be safely taken on primary when using Log Shipping or Availability Groups? We have a CommVault server taking log 15-minutely log backups of our primary (on-premise) DBs (combined with full nightlies), for point-in-time restores.

We want to enable high availability for DR fail-over to a cloud-based SQL Instance and we will be using either Log Shipping or Availability Groups.

This secondary instance will be truly passive so as not to incur additional licensing costs (i.e. we will not be able to take backups from it)

We are using SQL Server 2017 Standard Edition and, again for licensing cost reasons, won't be upgrading to Enterprise.

My question is - can CommVault continue to take its log backups for reliable point-in-time recovery and at the same time that either of the high availability options is enabled, or is there a risk of interference?

can CommVault continue to take its log backups for reliable point-in-time recovery and at the same time that either of the high availability options is enabled, or is there a risk of interference?

You can only have one log chain, so Log Shipping and your CommVault log backups will conflict.  Log Shipping can co-exist with full/differential backups, and with VSS backups, but not Log Backups.

An Availability Group replica is maintained by replicating the log records over the network, not through Log Backups.  So that can co-exist with your existing backup strategy.


Why is the lease locked for a some full sql backups to Azure Blob using Ola Hallengren backup scripts? I'm using SQL Server 2012 and working on backing up to Azure Blob using Ola Hallengren backup scripts.

Some backups are lease locked (ex. model) while others are not (ex. msdb). It's inconsistent, it can be vice-versa, completely locked or unlocked.

Why are the backups being lease locked? How do I have them all unlocked?

Do you know what version of each you are using? How close are you watching this in real time? I'm hoping the below quotes and links with the questions above will help spur you towards the right direction here.

When backing up to or restoring from Windows Azure storage, SQL Server acquires an infinite lease in order to lock exclusive access to the blob. When the backup or restore process is successfully completed, the lease is released. If a backup or restore fails, the backup process attempts to clean up any invalid blob.

Reference

The Lease Blob operation establishes and manages a lock on a blob for write and delete operations. The lock duration can be 15 to 60 seconds, or can be infinite. In versions prior to 2012-02-12, the lock duration is 60 seconds.
Starting in version 2012-02-12, some behaviors of the Lease Blob operation differ from previous versions. For example, in previous versions of the Lease Blob operation you could renew a lease after releasing it. Starting in version 2012-02-12, this lease request will fail, while calls using older versions of Lease Blob still succeed. See the Changes to Lease Blob introduced in version 2012-02-12 section under Remarks for a list of changes to the behavior of this operation.

Reference


SQL Server 2016 Multiple Availability Groups and instances So we are getting ready to make a major architecture shift and I get to be on the hook for it, yay!

Currently we have 22 separate availability groups each with their own windows cluster across 44 servers.

We want to move to 6 servers to hold the 22 AGs, yes I know it doesn't divide evenly.

So for example a given server (VM) would be 4 SQL Server instances, each instance would be an availability group and have its own listener.

So something like:

NODE1: SQLINST1, SQLINST2, SQLINST3, SQLINST4
NODE2: SQLINST1, SQLINST2, SQLINST3, SQLINST4
AGs: SQLINST1, SQLINST2, SQLINST3, SQLINST4
Listeners: SQLINST1Lis, SQLINST2Lis, SQLINST3Lis, SQLINST4Lis

My questions are:

does each SQL Server instance also need to be its own windows cluster or could one cluster suffice for all the named instances?
Will this work at all?

A) does each sql instance also need to be its own windows cluster or could one cluster suffice for all the named instances?

SQL Server doesn't have cluster membership, servers do. If the server is part of a windows cluster then all instances of SQL Server could be part of the same cluster. It is not possible to have a server be part of more than one cluster.

  B) Will this work at all?

It may... only if you have an unordinary small workload. Just the worker threads alone would make me assume that you're not going to have a good time. This coupled with having 4 instances per server, that don't communicate with each other, that are all trying to step on each others' toes (so to speak) leaves me thinking this, "I am really glad I won't have to admin this!".

My personal take

I would step away from thinking of AGs such as this. If you want to consolidate, that's great and I'm all for it! However, it needs to be done in a way that doesn't end up hurting you or your customers.

If it were me, and I was tasked with the same thing, I'd immediately push back on "6" servers. I don't yet know how many I'll need... unless of course we're not going to do any scientific research or testing - in which case I'd escalate my concerns.

We don't know things such as the number of total databases, how much log generation each database creates, etc., which we'd need to start gaining an understanding of what will be needed.

Undoubtedly you can consolidate these. Is it even the right thing to put them all in a single cluster? I wouldn't.

Each cluster is a fault domain, and while there are some really awesome features such as distributed availability groups that still ends up being more than a single cluster. Think of it this way, all of your AGs are in a single cluster - what happens if you have an issue with said cluster? Are all of the AGs now down? Maybe. Probably. Either way it's not something I'd want to be dealing with at 5 am.

Some of the main things you'll see when you have too many AGs or databases in AGs:

Worker thread exhaustion
Slow DMV queries
Slow Cluster API responses
Slow or Unresponsive Instances of SQL Server

My advice would be to gather metrics:

Number of database
Number of needed replicas for HA and DR
Log generation rate
CPU/Disk/Memory usage

That'll give you a start on how the pieces will need to fit together.
There needs to be much more diving into how this will turn out, but hopefully this will point you in the right direction.


SQL Server Management Studio Change User Server Roles to sysadmin? On my local machine I have SSMS (SQL Server 2008 R2).

For user xxxx Server Roles are only public.

When I try to change it to sysadmin it throws the error:

  User does not have permission to perform this action. (.Net SqlClient Data Provider)
  Error Code 15247

Server type (local)/SQLExpress
Authentication: Windows Authentication
User: ComputerName\username (disabled)
Password: none (disabled)

How can I do this?

The user I am connecting with server role is: public, but Windows Admin user.

The instance is configured for mixed mode authentication, but I don't remember the password for sa. I can't change the password because I am not sysadmin.

Is there any way I can create a sysadmin user account or make my account sysadmin and get full control of my SSMS?

You've explained that you don't have the SA password and you're not an administrator. In that case, you're going to have to hack your way around it.

See Think Your Windows Administrators Don’t Have Access to SQL Server 2008 by Default? Think Again. by Argenis Fernandez.

That post explains how to impersonate the NT AUTHORITY\SYSTEM login using PsExec (or creating a Windows Scheduled Task running under the SYSTEM account) to create a new login for yourself. Then, you can log in via that new login, and grant your Windows account SA permissions.


How to manage millions of users? I am about to launch something really big. I need to prepare my server and database.

I would like to group each set of 100,000 users in separate user tables but I do not know how to associate one user trying to log in to the appropriate user table.

For instance, how would I know that user jay@mail.com is related to the user table #36?

Would it be the same to have 10 millions users in one user table or 100 of 100,000?

How does Facebook? I can't believe they would have one global user table with 950 million entries.

You're not going to have a billion users tomorrow and MySQL can handle several million rows without any problem. I have 5 million users in my user table and trust me, it isn't even on my radar of things to worry about.

Don't worry about sharding until you need to do it. You are attempting to optimize prematurely for a problem that may or may not ever exist and in the process, you will severely cripple the rate at which you can innovate. Be fast to launch and find the problems as they come. You cannot predict in advance what your scaling challenges will be.

When and if you ever reach this scale, you will then have quite a bit of money and resources to throw at this kind of problem.


Assign values with the := operator When I assign a variable with

result := title || '', by '' || author;

it is taking more time (approx. 15 sec) to run the function.
However, when I assign the variable with

result = title || '', by '' || author;

it takes only 133ms.

Why is it taking more time for the first scenario? And what is the reason behind this?

Full function is given below.

CREATE OR REPLACE FUNCTION myschema.fn_get_res_no(reservation_no character varying)
  RETURNS character varying AS
$BODY$

DECLARE
    emd_status_firstcall varchar(2);
    emd_status_secondcall varchar(2);
    emd_status      varchar(6);
BEGIN
    SELECT firstwscomplete, secondwscomplete
    INTO emd_status_firstcall, emd_status_secondcall
    FROM myschema.mytable
    WHERE respkgconfirmid = reservation_no;

    emd_status = emd_status_firstcall || ', ' || emd_status_secondcall;
    RETURN emd_status ;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE;

This question concerns PL/pgSQL exclusively, where := is the assignment operator. It has no place in pure SQL.

Using = instead of := in PL/pgSQL is an undocumented legacy feature, which should not be used.
:= is the documented assignment operator in PL/pgSQL..
More details about that in this related question on SO.

Update: Since Postgres 9.4 = is also documented as plpgsql assignement operator.

I have never seen = performing faster than :=. They should be identical. I fact, I tested your exact code in Postgres 9.1 and 9.2 and found no difference in performance whatsoever.
You may be mis-interpreting caching effects. Always run EXPLAIN ANALYZE a couple of times to populate the cache and get comparable results.

Finally, you could replace your function with this much simpler and faster one:

CREATE OR REPLACE FUNCTION myschema.fn_get_res_no(reservation_no varchar)
  RETURNS varchar AS
$BODY$
BEGIN
   RETURN (
      SELECT firstwscomplete || ', ' || secondwscomplete
      FROM   myschema.mytable
      WHERE  respkgconfirmid = reservation_no
      );
END
$BODY$ LANGUAGE plpgsql;

Or even use a plain SQL function:

CREATE OR REPLACE FUNCTION myschema.fn_get_res_no(reservation_no varchar)
  RETURNS varchar AS
$BODY$
   SELECT firstwscomplete || ', ' || secondwscomplete
   FROM   myschema.mytable
   WHERE  respkgconfirmid = reservation_no);
$BODY$
  LANGUAGE sql;

Assignments in PL/pgSQL

It is comparatively expensive to make assignments in PL/pgSQL, which is a very simple Ada-like procedural language. Every assignment is executed with a (very simple) SELECT command, which is more expensive than in other fully fledged procedural languages.

PL/pgSQL excels as glue for SQL commands, when you need some procedural elements.


How can I output more than 256 characters to a file? I am using the following command to write the output from a stored procedure to a file for further processing:

DECLARE @shellCommand VARCHAR(2000)
SET @shellCommand = 'SQLCMD -S ' + @@SERVERNAME + ' -d ' + @dbName + ' -U ' + @UserName + ' -P ' + @Password + ' -Q "' + @sqlCommand + '" -s "," -h -1 -k 1 -W -o "' + @outputfileName + '"'

@sqlCommand is set correctly, to "EXEC StoredProcedureName parameterValue" and when run independently apparently produces the correct data.

The problem I'm having is that one of the fields contains text greater than 256 characters long and is getting truncated when fed through the SQLCMD command.

On reading the documentation I see that the default column width is 256 characters. So I'm looking at using the -y or -Y option to allow more than 256 characters to be output. However, these options aren't compatible with the -W option which I was using to remove trailing spaces from the output. I started by using -y 0 (despite the performance warning - once I get the output working I can look at the performance). However, that produces some strange results.

The file consists of a header row followed by the data rows and should look something like this:

First,ABC,Number,String,ReallyLongString,...
A,0123,14.99,"Short string","Longish string",...
B,0456,23.99,"Normal string","Really, really long string that's causing problems",...

With the -W option the file format is correct but the really long string is truncated. The reason we found it was because the trailing " was missing and the file wasn't being read properly.

With -y 0 the really long string is getting output but large numbers of spaces are being added to apparently random columns. We're getting something like this:

First,ABC ,Number    [...]      ,String,ReallyLongString,...
A    ,0123,14.99     [...]      ,"Short string","Longish string",...
B    ,0456,23.99     [...]      ,"Normal string","Really, really long string that's causing problems",...

(There are many, many more spaces between "Number" and the next column as represented by the "[...]", I'm just showing a few).

There are more numeric values that have to be formatted in a similar way and it does appear that it's these that are causing the extra spaces after the value and before the next comma. We can live with a few extra spaces, but not as many as this as the resultant file is too large to be read by the target program.

The data is generated by a stored procedure that looks like this:

SELECT
    'First' AS First,
    'ABC' AS ABC,
    'Number' AS Number,
    'String' AS String,
    'ReallyLongString' AS ReallyLongString,
    ...

UNION ALL

SELECT
    'A' as First,
    Column1 as ABC,
    REPLACE(FORMAT(Column2, 'N2', 'en-GB'), ',', '') as Number, -- to get the format correct
    '"' + Column3 + '"' as String,
    '"' + Column4 + '"' as ReallyLongString,
    ....
FROM Table
WHERE <condition>

I'm thinking that the problem is the output from the stored procedure. I only added the -W option to the SQLCMD because of the extra trailing spaces being output, but looking at the stored procedure I can't work out where they are coming from. I changed the number formatting to include RTRIM:

RTRIM(REPLACE(FORMAT(Column2, 'N2', 'en-GB'), ',', ''))

but that appeared to make no difference.

Is there something I can do to the stored procedure or is there a combination of options to SQLCMD that will produce the desired output? Or am I going to have to find some other way of producing this file?

While trying out the various suggestions in the comments I have stumbled across a solution.

I replaced:

'"' + Column4 + '"' as ReallyLongString,

with:

'"' + REPLICATE('O', 4000) + '"' AS ReallyLongString,

and all of the text was output.

However, using a variable:

DECLARE @longText NVARCHAR(MAX)
SET @longText = REPLICATE('O', 4000);

'"' + @longText + '"' AS ReallyLongString,

didn't.

What did work was declaring the variable with a specific length:

DECLARE @longText NVARCHAR(4000)

So, as the text in this case is never going to be longer than 2000 characters (the device reading the file can't cope with text that long) I convert all the text to 2000 character long strings:

SELECT
    'A' as First,
    Column1 as ABC,
    REPLACE(FORMAT(Column2, 'N2', 'en-GB'), ',', '') as Number, -- to get the format correct
    '"' + CAST(Column3 as NVARCHAR(2000)) + '"' as String,
    '"' + CAST(Column4 as NVARCHAR(2000)) + '"' as ReallyLongString,
    ....
FROM Table
WHERE <condition>

This is the how. I still don't really know the why.


Create a default value for session valid_until MySQL Server version:        5.6.22 Homebrew

The table:

CREATE TABLE `sessions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `date_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `date_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `archived` tinyint(3) unsigned NOT NULL DEFAULT '0',
  `valid_until` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  KEY `fk_sessions_users1_idx` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

By default, I want the valid_until column to be the current time + 1 day (or whatever). I've tried settings the 'default' to DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 1 DAY) but this failed. I also tried changing the type to date, timestamp, time and year but those give the same error:

ALTER TABLE `sessions` CHANGE `valid_until` `valid_until` DATETIME
 NOT NULL
 DEFAULT 'DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 1 DAY)'

MySQL said: Invalid default value for 'valid_until'

From the manual:

  With one exception, the default value must be a constant; it cannot be
  a function or an expression. This means, for example, that you cannot
  set the default for a date column to be the value of a function such
  as NOW() or CURRENT_DATE. The exception is that you can specify
  CURRENT_TIMESTAMP as the default for TIMESTAMP and DATETIME columns.

The way to workaround this is to use a trigger.

CREATE TRIGGER before_insert_on_sessions BEFORE INSERT ON `sessions`
FOR EACH ROW SET new.valid_until = IFNULL(new.valid_until,DATE_ADD(NOW(), INTERVAL 1 DAY));


Error in login in with root user in new mysql server I've installed a MySQL server in CentOS 6.2 machine

+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  |
| version                 | 5.1.61-log          |
| version_comment         | Source distribution |
| version_compile_machine | i386                |
| version_compile_os      | redhat-linux-gnu    |
+-------------------------+---------------------+

After the installation I executed this command:

/usr/bin/mysqladmin -u root password Pa$$w0rd

Now I can only access the server using the command:

mysql -uroot -pPa$$w0rd

But I'd like to do it using:

mysql -uroot -p

and after that, inform the password.

Everytime I try that I get an error:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Please run the following:
mysql -uroot -pPa$$w0rd -ANe"SELECT USER() ReqUserLogin,CURRENT_USER() AllowedUserLogin;"
mysql -uroot -pPa$$w0rd -ANe"SELECT user,host,password FROM mysql.user WHERE user='root'"

The first query will show you two things:

USER() reports how you attempted to authenticate in MySQL
CURRENT_USER() reports how you were allowed to authenticate in MySQL

The second query will show you all root users. It is possible only one root user got the password embedded and left the other without one.
Run this query to assign the same password to all root users:
UPDATE mysql.user A INNER JOIN mysql.user B USING (user)
SET A.password=B.password WHERE A.password='' AND B.password<>'';
FLUSH PRIVILEGES;

While you are at this point, you may as well get rid of anonymous users:
DELETE FROM mysql.user WHERE user=''; FLUSH PRIVILEGES;

and disable public access to test databases
DELETE FROM mysql.db WHERE SUBSTR(db,4) = 'test';
FLUSH PRIVILEGES;

I wrote about this before:

Cannot drop anonymous user from mysql.user
Is this a normal set of MySQL privileges?
Limiting database security

UPDATE 2012-06-13 15:58 EDT
I think I understand the problem. Look back at your original command to create the password:
/usr/bin/mysqladmin -u root password Pa$$w0rd

In Linux, $$ stands for something. Just run echo $$ and see:
[root@... ~]# echo $$
30837
[root@... ~]# ps -ef | grep 30837
root      2354 30837  0 15:50 pts/0    00:00:00 ps -ef
root      2355 30837  0 15:50 pts/0    00:00:00 grep 30837
root     30837 30835  0 14:49 pts/0    00:00:00 -bash
[root@... ~]#

You should have done this:
/usr/bin/mysqladmin -u root password 'Pa$$w0rd'

Here is a working theory. Run that command echo $$ (let's pretend $$ is 30837)
Try to login like this:
mysql -u root password -p"Pa$$w0rd"

If this does not work, try this:
mysql -u root password -pPa30387w0rd

If this last one works, you have the password with the number embedded.
Here is how to change it to Pa$$w0rd:
mysql -u root password -pPa$$w0rd -e"SET PASSWORD = PASSWORD('Pa$$w0rd');"

Give it a Try !!!


Slow MySQL searching from millions record My table looks like this.

id | hash | date

I've an index on hash which is in binary mode.

I have about 3 million rows right now.

But if I query with the following query, it takes about 4 seconds.

select HEX(hash) as hash
from animal
where hash in (UNHEX("19788EAB833C289CD7AB"),
    UNHEX("74FA22042B131511B6AB"), UNHEX("3F716CE5C8E383D2B657"),
    UNHEX("B2AE511D489990C13AD0"), UNHEX("FF6FD22DF8498BA74572"),
    UNHEX("3C4E02A76F0EEA51CB9D"), UNHEX("2A40966445E46A9B47BE"), ...)
    ... = around 100 length

What should I do to make it faster?

EDIT:

The result from using EXPLAIN

1 | SIMPLE | animal | range | hash | hash | 10 | NULL | 7 | Using index condition; Using MRR; Using filesort

EDIT 2:

I've mysql version 5.6.26.

CREATE TABLE `animal` (
 `id` mediumint(9) NOT NULL,
 `hash` binary(10) NOT NULL,
 `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 KEY `hash` (`hash`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

hash is unique?  If so, change it from KEY to PRIMARY KEY.  That will save some space, but not change the performance much.

It sounds like your innodb_buffer_pool_size is small.  It should be set to about 70% of available RAM.

Let's analyze what is happening.  You have about 100 hashes and a KEY (of any type) on hash.  It will reach into the key about 100 times in 100 different locations.  ("MRR" is involved here, but those details are not very relevant.)  Each lookup of a hash value in the BTree-organized index probably does not find the desired block in the buffer_pool, so it has to hit the disk to get it.  This means about 100 disk hits.  Assuming you have a cheap disk, these hits will take a total of about 1 second.  (Sorry, I can't explain 4 seconds, unless the cache was "cold" and index blocks needed to be fetched, too.)

So, what will a bigger buffer_pool do?  Eventually (as your queries cache more and more of the table), those 100 will be "cache hits" and not have to go to disk.  And the speed will be sub-second.

Your table is probably under 400MB (regardless of whether you take someone's advice on PRIMARY KEY).  You should be able to afford a buffer_pool bigger than that.


Slow-running query on clustered MySQL environment I've got a very simple query, directly equivalent to

SELECT * FROM sometable t WHERE somecol = 'somevalue' ORDER BY createdon DESC LIMIT 0,20000;

When I run this query on my non-clustered development machine, it runs in 0.07 of a second.

When I run the same query on one of our clustered load-balanced servers, it takes upwards of half an hour to complete.  The data on both servers is exactly the same.

Why is this occurring?

Here is some more info on the setup:

The total number of rows in the table is ~14k, so it doesn't get anywhere near the LIMIT.
MySQL version is MySQL Server: 5.1.56-ndb-7.1.15a-cluster-gpl
Ubuntu 11.04 (GNU/Linux 2.6.38-8-server x86_64).
Storage Engine NDBCLUSTER
InnoDB on my local server

The table schema is as follows, where {X} is InnoDB on my local machine or NDBCluster on the production servers.

CREATE TABLE `sometable` (
    `Id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `UserId` bigint(20) unsigned NOT NULL,
    `Cookie` varchar(255) DEFAULT NULL,
    `somecol` varchar(30) DEFAULT NULL,
    `IpAddress` varchar(255) DEFAULT NULL,
    `SomeCollection` text,
    `someothercolumn` decimal(6,2) NOT NULL,
    `someothercolumn2` decimal(6,2) DEFAULT NULL,
    `Result` tinyint(4) NOT NULL,
    `Version` tinyint(4) NOT NULL,
    `Source` varchar(255) DEFAULT NULL,
    `CreatedOn` datetime NOT NULL,
    PRIMARY KEY (`Id`),
    KEY `CreatedOnIndex` (`CreatedOn`),
    KEY `SomeColIndex` (`somecol`),
    KEY `ResultIndex` (`Result`),
    KEY `SomeCol2Index` (`someothercolumn2`)
) ENGINE={X} AUTO_INCREMENT=97043 DEFAULT CHARSET=latin1;

The query returns 11,154 records, which is the correct amount for the query.

Regarding latency between the data nodes, this is what a typical ping request returns:

64 bytes from 192.168.3.45: icmp_req=1 ttl=64 time=0.479 ms

You may want to consider if you really need the MySQL Cluster, it may be overkill for your purposes and may not justify the complexity. A pair of MySQL Master-Master or Master-Slave instances with replication are good enough for many shops and will deliver better performance in this scenario.

If you have to stay with your current architecture, you can add Index on column "somecol", first try HASH, then B-TREE index. These should help, still performance would not be as good as a simple host instance.


DBCC CHECKDB firing every 20 - 60 seconds I have a dev environment that is rapidly approaching deployment into production and have noticed in the logs that roughly every 20 seconds I see the message:

Starting up database 'dbname'
CHECKDB for database 'dbname' finished without errors

I noticed this because the end users who are testing this had a problem which I isolated to an error that I noticed in the Windows Event Log (Application Log):

The log for database 'dbname' is not available. Check event log for related messages.

Incidentally, I found no related messages but resolved with offline/online cycle.

The only thing I can think of is that this application collects data every 1ms, but all of this data does not get inserted into the database. Being a dev server, it was config'd so that logs and data are on the same drive. With these two pieces of information in mind, I'm leaning toward this being an IO issue and that the server is attempting to recover from a loss of connection to its storage (SAN). Yet even this doesn't make sense to me because it is not losing all dbs, just this one. What else could be causing this?

Platform: SQL Server 2008 R2 (Ent.) on Windows Server 2008 R2 (Std.)

The reason you are seeing this:

Starting up database 'dbname'
CHECKDB for database 'dbname' finished without errors

Is because you have your database option set for AutoClose.

To turn off AutoClose, do this:

alter database YourDatabase
set auto_close off
go

What AutoClose does is shut down the database after the last user process disconnects.  And then database automatically "reopens" when the next user connection attempts to connect.

Typically it is best practice to keep AutoClose OFF, because of the obvious latency to spin up the database again.  I understand from your question that this is a development environment, but you shouldn't need it there either (and you definitely don't want that in your production environment).

Furthermore DBCC CHECKDB is not actually "firing every 20 - 60 seconds" per the question title, it just appears that way!

Since SQL Server 2005 the dbi_dbccLastKnownGood value (if present) is reported in the error log every time the database is started. So you are just seeing the same historic information presented repeatedly.

This topic is covered in the article Why is SQL Server running DBCC CHECKDB against my databases at Server Startup?


On reboot listeners don't start but restarting the service after will start them when I reboot ubuntu the listener service comes up but it doesn't seem to have any listeners

lsnrctl status

LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 24-JUL-2014 15:34:43

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dex-ci.escalation-point.com)(PORT=1522)))
 STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date                24-JUL-2014 15:33:58
Uptime                    0 days 0 hr. 0 min. 44 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File   /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ip-10-113-43-249/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1522)))
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
The listener supports no services
The command completed successfully

after a /etc/init.d/oracle-xe restart

 lsnrctl status | sed -e 's/^/     /'

 LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 24-JUL-2014 15:42:15

 Copyright (c) 1991, 2011, Oracle.  All rights reserved.

 Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dex-ci.escalation-point.com)(PORT=1522)))
 STATUS of the LISTENER
 ------------------------
 Alias                     LISTENER
 Version                   TNSLSNR for Linux: Version 11.2.0.2.0 - Production
 Start Date                24-JUL-2014 15:41:24
 Uptime                    0 days 0 hr. 0 min. 50 sec
 Trace Level               off
 Security                  ON: Local OS Authentication
 SNMP                      OFF
 Default Service           XE
 Listener Parameter File   /u01/app/oracle/product/11.2.0/xe/network/admin/listener.ora
 Listener Log File         /u01/app/oracle/diag/tnslsnr/dex-ci/listener/alert/log.xml
 Listening Endpoints Summary...
   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522)))
   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
   (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=8888))(Presentation=HTTP)(Session=RAW))
 Services Summary...
 Service "XE" has 1 instance(s).
   Instance "XE", status READY, has 1 handler(s) for this service...
 Service "XEXDB" has 1 instance(s).
   Instance "XE", status READY, has 1 handler(s) for this service...
 The command completed successfully

What this tells me is that my configuration is fine, so it's something going on a boot, maybe a race condition... I have looked at the logs and was unable to find anything interesting. What kinds of things could cause these services to not come up on a restart?

This was a problem with the hostname not getting set consistently on boot, and was not correctly available at that time but was later.


Can mariaDB contain indices only with data coming from remote tables? I'm trying to think through a setup with a central mariaDB instance (A) accessing data on physically remote servers (B).

All queries to the database (A) can be answered from indices only. Indices are read-only that is re-indexing is triggered from the remote-server (B) at certain intervalls (once per day).

I'm far from a specialist in database things, so when searching around and finding mariadb CONNECT I was wondering:

Question:
Is it at all possible to have a mariaDB database (A) containing "indices only" and the underlying tables being kept on remote locations (B)? If it was, is it feasable?

Thanks for some ideas!

No: local instance can't know when remote tables are updated.

Also, I suggest you use SPIDER instead of CONNECT.


MongoDB working set size on capped collection larger than capped collection size? On my database server right now I have 16GB of ram and an 8GB capped collection with about 4GB of indexes. So I may understand this wrong but shouldn't this only ever use around 12GB of my RAM or maybe a bit more? After about 2 weeks of running the database it starts to act like it no longer fits into memory with insert speeds slowing down significantly and I have to repair the database. Am I just misunderstanding the way this should work?

Check out https://jira.mongodb.org/browse/SERVER-5615

You might be running into a similar issue. Do you have several indexes on your data? Can you do a db.coll.stats()? And maybe show some output of mongostat while it's running slower?

I've heard similar complaints in the past, but haven't actually used capped collections myself.


Structuring SQLite database with 2 relationships? I'm current working on a SQLite-database in a android project which will store 3 types of objects; feed, users and posts.

There will be several feeds containing several varying users(a user can be part of serveral feeds) and of my understanding i should implement this by having a Many-to-Many relationship:
feedTable - bridge_feeduserTable - userTable

But there will also be posts made by users and a post should be linked to a the user which created the post. How should i link this up?

Should i have a feedTable - bridge_feeduserTable - userTable - bridge_userpostTable - postTable?  or should the posts be stored in a separate database with all the posts? What is the best practice regarding this issue?

Some basic questions that will asked to the database is:
What users are part of feed x?
What posts have been made by the users in feed x?

If I get you right then one post is linked to one user but one user can have many posts.
I think this is a one to many relationship. So, put a field user_id in your table post then you know which post is linked to which user.

It's a german page but the first image would help you.


MySQL replication error 1146 Any idea why there is '1146 - table doesn't exists' on 'CREATE TABLE IF NOT EXISTS...' statement. It actually happened only on one of the slaves (there are two in total):

Last_SQL_Error: Error 'Table 'db_name.table_name' doesn't exist' on query. Default database: 'db_name'. Query: 'CREATE TABLE IF NOT EXISTS `table_name` (
          `step_id` int(11) NOT NULL,
          `item_id` int(11) NOT NULL,
          `item_type` varchar(128) COLLATE utf8_unicode_ci NOT NULL,
          `date_attached` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
          PRIMARY KEY (`step_id`,`item_id`,`item_type`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci'

EDIT:

I've re-synced this table but I'm not sure why this happened.

130802  9:32:03 [ERROR] Cannot find or open table db_name/table_name from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See http://dev.mysql.com/doc/refman/5.5/en/innodb-troubleshooting.html
how you can resolve the problem.

The error itself explains what the problem is. There is a .frm file on the filesystem for the table. Go to your MySQL data directory (it's usually /var/lib/mysql on a linux box) and take a backup of the .frm file and remove it from that location. You should then be able to create the table on your slave.


Guide to protect SQL Server against speculative execution side-channel vulnerabilities I am going through this link.  My question is regarding Scenario 1 under recommendation as most of our SQL Servers run on physical machine

  SQL Server runs on "bare metal" (no virtual machines)

  AND no other untrusted application logic (application tier) is run on
  the same machine

  AND no untrusted SQL Server extensibility interfaces are being used
  (see below for list).

What does it mean by untrusted application logic?

Does a webserver running on the same server with internally developed site constitute untrusted application logic?

What about a third party services that monitors the server?

Untrusted application logic means any application or service running any logic that you(r organization) does not trust.

For your purpose, if you want to be lenient (potentially less secure) you can substitute "unauthorized" for "untrusted" - if the server is running any unauthorized code, it falls under that category.

If you want to be conservative (potentially more secure), assume all your servers run untrusted application logic and move on with that as a founding security assumption, applying the more secure recommendations to them.


Table Creation Order I have designed my database model with entities and they have relations along with normalization. Now I want to implement my design in my database engine. Before implementing I am puzzled with the order of table creation.

Which pattern should I follow?

the tables with no foreign keys first
the tables with foreign keys

Or would it be better to follow this order:

tables without any foreign keys
tables which are connected with that table

I am new to database design. So forgive me if i am asking this awkward question.

I am implementing my design via mysqlworkbench 8.x

I use to follow this path:

create all table, only structure (fields), without any Primary Keys nor Foreign Key, without any Index, the order doesn't matter

1.1 inject predefined (aka static) datas

create Primary Keys & Indexes
create Constraints if any
finally, create inter-table relations aka Foreign keys

Gains expected:

no time spent to manage table order (your primary goal)

=> in the worst case, you can order your tables by an alphabetic order
declarations are grouped by meaning (table then PK & Indexes then Constraints then FK), a gain of visibility and accessibility
easiest to debug
you can store all these items all in one file (for a simple project), or one file by step (big or complex project)


Reusing views to do aggregate queries I have created some views which give me some values.

So some thing like the following

select from a table of all ids gives me some thing like

id1
id2
id3
id4

view1 gives me

id1 2
id1 1
id1 3
id2 1

view2 gives me

id3 London
id3 Paris
id1 London

What I need is some thing like

id1  6  1
id2  1  0
id3  0  2
id4  0  0

As you can see that I need aggregation of numbers from first view and aggregation of number os cities from second view. What would be a way to do this?

Following sample may be useful to build query which you want to write.

USE tempdb
GO

CREATE TABLE t_Ids (id VARCHAR(10) PRIMARY KEY )
GO

INSERT INTO t_Ids VALUES('id1'),('id2'),('id3'),('id4')
GO

CREATE VIEW v_V1
AS
SELECT 'id1' id, 2 value UNION
SELECT 'id1' id, 1 UNION
SELECT 'id1' id, 3 UNION
SELECT 'id2' id, 1
GO

CREATE VIEW v_V2
AS
SELECT 'id3' id, 'London' city UNION
SELECT 'id3' id, 'Paris' UNION
SELECT 'id1' id, 'London'
GO

SELECT t.id, ISNULL(SUM(v1.value), 0) AS agg_value,  COUNT(DISTINCT v2.city) cnt_city
FROM t_Ids AS t
LEFT OUTER JOIN v_v1 AS v1 ON t.id = v1.id
LEFT OUTER JOIN v_v2 AS v2 ON t.id = v2.id
GROUP BY t.id
GO

DROP TABLE t_Ids
GO

DROP VIEW v_v1, v_v2
GO


SQL Server 2008 transaction log full We have a large database, with a size of approx 400GB. We are using bulk insert (from upstream system) with the following parameters:

`Driver=/IIS/Appl/Server/branded_odbc/lib/VMsqls24.so`
`Description=DataDirect 6.0 SQL Server Native Wire Protocol`
`Address=SQLLPB2GBRxxx.xxxxx.COM,14331`
`AnsiNPW=Yes`
`AuthenticationMethod=4`
`Database=DB_PROD`
`QuotedId=No`
`EnableBulkLoad=1`
`BulkBinaryThreshold=32`
`BulkCharacterThreshold=-1`
`BulkLoadBatchSize=1024`
`BulkLoadOptions=0`

The delta changes in the DB represent approx 70GB at month end. The transaction log file is falling over at 800GB (twice the size of the actual DB, where 800GB is the current size of the allocated transaction log drive). Autogrow is enabled on the transaction log, but we are unsure WHY it is becoming so large. Simple recovery is set.

Are there any parameters (either upstream on the bulk insert, or on the transaction log db ) we can fine tune?

While it's running try running the CHECKPOINT command separately. It could be that your system is not getting to perform checkpoints and may need some extra prodding to do so.


Postgresql cannot drop trigger I'm running the following with alembic:

def downgrade():
    conn = op.get_bind()
    conn.execute(sa.sql.text('''
DROP TRIGGER update_rated_trigger ON rating;
DROP FUNCTION update_rated;
    '''))

It gives the error:

  sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) syntax error at or near ";"
  LINE 2: DROP TRIGGER "update_rated_trigger";

Not sure what the problem is with postgres?

The issue is that Postgres requires you to include the braces at the end of the function name when calling DROP FUNCTION.

If you change your code to the following, it'll work:

def downgrade():
    conn = op.get_bind()
    conn.execute(sa.sql.text('''
DROP TRIGGER update_rated_trigger ON rating;
DROP FUNCTION update_rated();
    '''))


Incremental Backups In SQL server I am very much aware that native SQL server backups has full/differential/log backup strategies which are not really an incremental backups and would like to know if there are any 3rd party tools to implement an incremental backup strategy ?

This is not true.

Transaction log backups are incremental, that is, they only store incremental changes since the previous transaction log backup.

You are confusing the differential log backup with "backups are not incremental", because differential backups only keep a record of the changes since the last full backup.

(Differential backups work in all recovery models, namely Simple, Bulk-Logged, and Full.)

If you want incremental backups, take transaction log backups regularly.


SQL Server Date Conversion On applying the where condition :

WHERE DateFilled BETWEEN '02/01/2014' AND '02/28/2014'

My syntax is not detecting the values which are filled on 02/28/2014.

If I write the syntax as

WHERE DateFilled >= '02/01/2014'

I am getting the output.

In the SQL Server table, DateFilled column has a value as follows.

2014-02-28 09:24:00.000

What could be the reason? Any date conversion is required. How to get StartDate and EndDate using BETWEEN condition?

I am assuming that DateFilled has a data type of DateTime or other date type which includes a time portion.  When using DateTime values in T-SQL be aware that a time is always included and if not specified midnight is used by default.  See: http://technet.microsoft.com/en-us/library/ms180878(v=sql.105).aspx#ConversionBetweenStringLiteralsandDateandTimeDataTypes
When the input string is "DATE only" then "the The TIME part defaults to 00:00:00."  In your case the string "02/28/2014" is converted to a datetime value of 2014-02-28 00:00:00.000.

Your query is interpreted as DateFilled between '02/01/2014 00:00:00 .000' and '02/28/2014 00:00:00.000' thus the record with a datetime of 2014-02-28 09:24:00.000 (which occurs after midnight on, is not found.

Your query may be re-written as

SELECT Column1, Column2
FROM MyTable
WHERE DateFilled >= '02/01/2014'
   AND DateFilled < '03/01/2014';


How does MySQL or PostgreSQL deal with multi-column indexes in ActiveRecord? I'm creating indexes for my models right now and I want to know how MySQL and PostgreSQL deal with an index with more than 1 column like:

add_index :users, [:username, :created_at]

That should utilize the index when I do a query like (I think):

User.where("username = ? and created_at > ?", 'jim', DateTime.now.yesterday)

But will it also utilize the index if I only use the username in a query?

User.where("username = ?", 'jim')

In PostgreSQL at least, multi-column indexes are usable for single-column searches on the left column, but not for single-column searches on the right column.

So: an index on (a,b,c) can be used for searches on a, a,b and a,b,c but not for searches on b,c or c.

If search patterns make individually usable indexes on all columns necessary and if the column values are small, people often create indexes a,b,c, b,c and c.

An alternative can be individual indexes on a, b, and c. PostgreSQL can use a bitmap index scan in many circumstances to combine the indexes, so a search with conditions on b and c will use both the indexes on b and c.

Rather than guess, the best thing to do is use EXPLAIN ANALYZE or the auto_explain module to examine the query plans and run-time stats from your queries, so you can see what's actually happening.


Create Index with a WHERE clause I've read the documentation on the MSDN, but it doesn't answer what I'm after.  If I create an index on specified columns using a WHERE clause for certain IDs, this will limit the index to those IDs and new IDs will insert into this index.  That is all fine and dandy.  But, in my WHERE clause, I'd like to specify a list of previous IDs and also include anything that is greater than the highest ID in the table e.g.

WHERE CampaignID IN (1,2,10,14,16,20,26)
  AND CampaignID > 26

Is this advisable?  The goal I'm trying to achieve is to index all of the data that is presently used, and ignore all other data.  The table is over 10Gb and it's in constant use so I can't remove these old records as it locks the table and stops data being inserted.

New IDs will always INSERT into the table, not the index. Your proposed INDEX would merely provide a more efficient lookup for some ID values.

Some thoughts though

The index may not be used too: it would have to cover all columns in the table. Or searches would have to return just a few rows so the key lookup needed (for a non-covering index). For more, see "Using Covering Indexes to Improve Query Performance".

As it stands, it may help, it may not.

Deleting or archiving old records can be made less painful too, which is probably the better solution. Example (SO): https://stackoverflow.com/a/2126482/27535


FILEPROPERTY not working over linked server to Azure SQL Database FILEPROPERTY (Transact-SQL) is not working with a linked server pointing from a on-Premise SQL 2017 to a Azure SQL Database. I see the following comment in books online:

  Returns NULL for files that are not in the current database.

But my linked server has @catalog pointing to a specific database.

Here is my code and I get NULL for spaceUsedMB , freeSpaceMB, and spaceUsedMB columns.

SELECT
 collectedAt = GetDate(),
 serverName =(SELECT * FROM OPENQUERY (AzureDB_adventureworks, 'SELECT @@SERVERNAME')),
 databaseName=(SELECT * FROM OPENQUERY (AzureDB_adventureworks, 'SELECT DB_NAME()')),
 fileName = LEFT(a.NAME, 64) ,
 a.FILE_ID AS fileId,
 fileSizeMB = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2)),
 spaceUsedMB = CONVERT(DECIMAL(12, 2), ROUND(FILEPROPERTY(a.name,'SpaceUsed')/ 128.000, 2)),
 freeSpaceMB = CONVERT(DECIMAL(12, 2), ROUND(( a.size - FILEPROPERTY(a.name,'SpaceUsed'))/ 128.000, 2)),
 percentFree = CONVERT(DECIMAL(12, 2), (CONVERT(DECIMAL(12, 2), ROUND((a.size - FILEPROPERTY(a.name,'SpaceUsed'))/128.000, 2))*100)/ CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2))),
 a.physical_name
FROM [AzureDB_adventureworks].[adventureworks].sys.database_files a

I was able to get by with following code but still curious to know why above code did not work.

SELECT * FROM OPENQUERY (AzureDB_adventureworks,
    'SELECT
        GETDATE() AS collectedAT,
        @@SERVERNAME AS serverName,
        DB_NAME() AS databaseName,
        LEFT(a.name, 64) AS fileName,
        a.file_id AS fileId,
        a.size AS fileSizeMB,
        CONVERT(DECIMAL(12, 2), ROUND(FILEPROPERTY(a.name,''SpaceUsed'')/ 128.000, 2)) AS spaceUsedMB,
        CONVERT(DECIMAL(12, 2), ROUND(( a.size - FILEPROPERTY(a.name,''SpaceUsed''))/ 128.000, 2)) AS freeSpaceMB,
        CONVERT(DECIMAL(12, 2), (CONVERT(DECIMAL(12, 2), ROUND((a.size - FILEPROPERTY(a.name,''SpaceUsed''))/128.000, 2))*100)/ CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2))) as percentFree,
        a.physical_name AS physicalName
from adventureworks.sys.database_files a'
)

If I run the following code against Azure SQL Database I will get correct result for all columns.

SELECT
 collectedAt = GetDate(),
 serverName = @@SERVERNAME,
 databaseName= DB_NAME(),
 fileName = LEFT(a.NAME, 64) ,
 a.FILE_ID AS fileId,
 fileSizeMB = CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2)),
 spaceUsedMB = CONVERT(DECIMAL(12, 2), ROUND(FILEPROPERTY(a.name,'SpaceUsed')/ 128.000, 2)),
 freeSpaceMB = CONVERT(DECIMAL(12, 2), ROUND(( a.size - FILEPROPERTY(a.name,'SpaceUsed'))/ 128.000, 2)),
 percentFree = CONVERT(DECIMAL(12, 2), (CONVERT(DECIMAL(12, 2), ROUND((a.size - FILEPROPERTY(a.name,'SpaceUsed'))/128.000, 2))*100)/ CONVERT(DECIMAL(12, 2), ROUND(a.size / 128.000, 2))),
 a.physical_name
FROM sys.database_files a

Please note that logs are excluded for the purposes of determining database size in Azure SQL Database, that is the reason you receive NULL as result. You should add the following WHERE clause to your query.

WHERE type_desc = 'ROWS';

For example, the following query will work and won't return nulls.

SELECT
SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.)
AS DatabaseSizeInBytes,
SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.)/1024 /1024
AS DatabaseSizeInMB,
SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS bigint) * 8192.)/1024/1024/1024
AS DatabaseSizeInGB
FROM sys.database_files
WHERE type_desc = 'ROWS';


Help with query statement EDIT

This query seems to work best so far.

select host,plugin_id,month,year, row_number() over (partition by plugin_id order by plugin_id) as plugin_split
from table
where risk in ('High')
group by plugin_id,host,month,year
order by plugin_id,host,month asc;

This seems to group all the plugin_id,host, and month together to make it easy to parse.

Would it be possible to only select hosts that have the same plugin_id for month 1 and 9? And then only for month 1 but not month 9? And then only for month 9 but not month 1? I was looking into parsing this with ruby if I couldn't do it in SQL. Not really a DBA any help would be appreciated. Thanks!

select host,risk,plugin_id,month,year from table where risk in ('High') group by plugin_id,host,risk,month,year;

     host      | risk | plugin_id | month | year
---------------+------+-----------+-------+------
 172.20.10.122 | High |     10081 |     1 | 2014
 172.20.10.122 | High |     10081 |     9 | 2013
 172.20.10.51  | High |     10081 |     1 | 2014
 172.20.10.52  | High |     10081 |     1 | 2014
 172.20.10.53  | High |     10081 |     1 | 2014
 172.20.10.53  | High |     10081 |     9 | 2013
 172.20.10.54  | High |     10081 |     9 | 2013
 172.20.10.55  | High |     10081 |     9 | 2013
 172.20.10.56  | High |     10081 |     1 | 2014
 172.20.10.56  | High |     10081 |     9 | 2013
 172.20.10.57  | High |     10081 |     1 | 2014
 172.20.10.58  | High |     10081 |     1 | 2014
 172.20.10.58  | High |     10081 |     9 | 2013
 172.20.10.59  | High |     10081 |     1 | 2014
 172.20.10.59  | High |     10081 |     9 | 2013
 172.20.10.60  | High |     10081 |     1 | 2014
 172.20.10.60  | High |     10081 |     9 | 2013
 172.20.10.61  | High |     10081 |     1 | 2014
 172.20.10.61  | High |     10081 |     9 | 2013
 172.20.10.62  | High |     10081 |     1 | 2014

You can self join the table so that on the left there are the hosts for month 1, on the right for month 9:

SELECT host, risk, plugin_id, month, year
  FROM your_table t1
  JOIN your_table t2 ON t1.month = 1
                    AND t2.month = 9
                    AND t1.host = t2.host
                    AND t1.plugin_id = t2.plugin_id
 WHERE risk = 'High';

This will return the hosts where plugin_id is the same for the two months.  It is not exactly clear what the other queries should be - I imagine it is not like the plugin is the same for a given month.  Please edit your question with the clarification.


MySQL slow server in a Billing application i have on server DELL R220 with 8Gb Ram and 60GB HDD CPU 2.8Mhz

anyway, this server is used to host the Radius Manager one of the telecom billing servers build on FreeRadius and with CentOS 6.

we have only 200 users, meantime we have more than 2 million voucher generated in the system and stored in the database.

the issue that when any inquiry is made to the users table the response time is very long and i do not know why.

i'm not expert and would appreciate you help to address the issue.

Add an index to the users table. This should decrease the response time when the users table is queried. If you can share an example SQL statement used to query the users table, I can provide a more specific recommendation.

The columns in parenthesis should be the columns used in the WHERE clause of your SQL query.

CREATE INDEX index_name
ON table_name (column1, column2, ...);


Tables with hierarchy: create a constraint to prevent circularity through foreign keys Suppose we have a table that has a foreign key constraint to itself, like such:

CREATE TABLE Foo
    (FooId BIGINT PRIMARY KEY,
     ParentFooId BIGINT,
     FOREIGN KEY([ParentFooId]) REFERENCES Foo ([FooId]) )

INSERT INTO Foo (FooId, ParentFooId)
VALUES (1, NULL), (2, 1), (3, 2)

UPDATE Foo SET ParentFooId = 3 WHERE FooId = 1

This table will have the following records:

FooId  ParentFooId
-----  -----------
1      3
2      1
3      2

There are cases where this kind of design could make sense (e.g. the typical "employee-and-boss-employee" relationship), and in any case: I'm in a situation where I have this in my schema.

This kind of design unfortunately allows for circularity in data records, as shown in the example above.

My question then is:

Is it possible to write a constraint that checks this? and
Is it feasible to write a constraint that checks this? (if needed only to a certain depth)

For part (2) of this question it may be relevant to mention that I expect only hundreds or perhaps in some cases thousands of records in my table, normally not nested any deeper than about 5 to 10 levels.

PS. MS SQL Server 2008

Update March 14th 2012
There were several good answers. I've now accepted the one that helped me understand mentioned possibility/feasibility. There are several other great answers though, some with implementation suggestions as well, so if you landed here with the same question have a look at all answers ;)

I have seen 2 main ways of enforcing this:
1, the OLD way:
CREATE TABLE Foo
    (FooId BIGINT PRIMARY KEY,
     ParentFooId BIGINT,
     FooHierarchy VARCHAR(256),
     FOREIGN KEY([ParentFooId]) REFERENCES Foo ([FooId]) )

The FooHierarchy column would contain a value like this:
"|1|27|425"

Where the numbers map to the FooId column.  You would then enforce that the Hierarchy column ends with "|id" and the rest of the string matches the FooHieratchy of the PARENT.
2, the NEW way:
SQL Server 2008 has a new datatype called the HierarchyID, that does all of this for you.
It operates on the same principal as the OLD way, but it is handled efficiently by SQL Server, and is suitable for use as a REPLACEMENT for your "ParentID" column.
CREATE TABLE Foo
    (FooId BIGINT PRIMARY KEY,
     FooHierarchy HIERARCHYID )


Use extra column to save an unaccented copy of a string instead of using unaccent module I am heavily using the unaccent module in a table with 500.000 rows where each row contains  proportionally a lot of accented characters.

The issue I am facing is that my queries have become time consuming monsters.

Is it utterly unacceptable -in terms of database design- to save an unaccented copy of each string in another column of the table in question and query that one instead of using the unaccent module?

You could store an unaccented version redundantly. And create index on that column to support your queries.

Or, better yet, to avoid bloating your table with redundant data, create a functional index on the original column.  You need an IMMUTABLE function for that, while unaccent() is only STABLE. Detailed instructions:

Does PostgreSQL support “accent insensitive” collations?

Then, based on the function f_unaccent() from the linked answer:

CREATE INDEX tbl_unaccent_col_idx ON tbl(public.f_unaccent(col));

Or some other index. Maybe use the full text search (FTS) infrastructure with unaccent as dictionary. Example:

Creating a case-insensitive and accent/diacritics insensitive search on a field

Or use it in a trigram index:

Queries using GIN index on f_unaccent() seem slow?
Why does a slight change in the search term slow down the query so much?


Why does SAMPLE BLOCK often return zero? In a comment on another question, someone asked:

  I used SAMPLE BLOCK in many tests but found that it would inexplicably return '0' much of the time. Any idea why?

I speculated that this might be to do with the sample hitting empty blocks but even if you tune the table to make sure all the extents allocated are completely full, the behaviour doesn't change, so it can't be that.

test data:

create table foo(bar integer primary key, baz char(1000));

insert into foo(bar,baz) select level, 'A' from dual connect by level<=1000;

begin
  dbms_stats.gather_schema_stats(null);
end;

select sum(blocks) blocks from user_extents where segment_name = 'FOO';

| BLOCKS |
| -----: |
|    256 |

test SAMPLE BLOCK:

with w(estimated_rows,distinct_blocks) as(
 select count(*)*2, count(distinct substr(rowid,1,15)) from foo sample block (50)
 union all
 select count(*)*2, count(distinct substr(rowid,1,15)) from foo sample block (50)
 union all
 select count(*)*2, count(distinct substr(rowid,1,15)) from foo sample block (50)
 union all
 select count(*)*2, count(distinct substr(rowid,1,15)) from foo sample block (50)
 union all
 select count(*)*2, count(distinct substr(rowid,1,15)) from foo sample block (50))
select * from w;

ESTIMATED_ROWS | DISTINCT_BLOCKS
-------------: | --------------:
             0 |               0
             0 |               0
          2000 |             143
          2000 |             143
          2000 |             143

dbfiddle here

I found a Jonathan Lewis blog on a related issue in 10.1, but this didn't explain why it is happening or how to work around the issue.

There doesn't seem to be anything specific mentioned in the documentation, but this Oracle Data Mining blog is interesting:

  SAMPLE relies on the existence of primary keys

I'm not sure this is true, but the existence of a primary key does play a role. The docs do say:

  Block sampling is possible only during full table scans or index fast full scans. If a more efficient execution path exists, then Oracle Database does not perform block sampling. If you want to guarantee block sampling for a particular table or index, then use the FULL or INDEX_FFS hint.

If you look at the plan for the query you are using, you can see it never touches the table, instead it is doing a fast full scan of the primary key:

select * from table(dbms_xplan.display);

| PLAN_TABLE_OUTPUT                                                                              |
| :--------------------------------------------------------------------------------------------- |
| Plan hash value: 130955250                                                                     |
|                                                                                |
| ---------------------------------------------------------------------------------------------- |
| | Id  | Operation                      | Name        | Rows  | Bytes | Cost (%CPU)| Time     | |
| ---------------------------------------------------------------------------------------------- |
| |   0 | SELECT STATEMENT               |             |     1 |    45 |     3  (34)| 00:00:01 | |
| |   1 |  SORT AGGREGATE                |             |     1 |    45 |            |          | |
| |   2 |   VIEW                         | VW_DAG_0    |   500 | 22500 |     3  (34)| 00:00:01 | |
| |   3 |    HASH GROUP BY               |             |   500 |  6000 |     3  (34)| 00:00:01 | |
| |   4 |     INDEX SAMPLE FAST FULL SCAN| SYS_C007394 |   500 |  6000 |     2   (0)| 00:00:01 | |
| ---------------------------------------------------------------------------------------------- |

The SAMPLE BLOCK is operating on the index — which has far fewer blocks and is distorting the sample:

select sum(blocks) blocks from user_extents where segment_name = 'PK_FOO';

| BLOCKS |
| -----: |
|      8 |

With a primary key with more blocks the variability is much less, and you can alleviate the issue even more by using the second parameter in SAMPLE BLOCK to restrict contiguous blocks sampled to 1:

create table foo(bar integer, baz char(1000), constraint pk_foo primary key (bar,baz));

…

select segment_name, sum(blocks) blocks
from user_extents
where segment_name like '%FOO'
group by segment_name;

SEGMENT_NAME | BLOCKS
:----------- | -----:
FOO          |    256
PK_FOO       |    256

with w(estimated_rows,distinct_blocks) as(
 select count(*)*2, count(distinct substr(rowid,1,15)) from foo sample block (50,1)
 union all
 select count(*)*2, count(distinct substr(rowid,1,15)) from foo sample block (50,1)
 union all
 select count(*)*2, count(distinct substr(rowid,1,15)) from foo sample block (50,1)
 union all
 select count(*)*2, count(distinct substr(rowid,1,15)) from foo sample block (50,1)
 union all
 select count(*)*2, count(distinct substr(rowid,1,15)) from foo sample block (50,1))
select * from w;

ESTIMATED_ROWS | DISTINCT_BLOCKS
-------------: | --------------:
          1076 |              77
           896 |              64
           924 |              66
          1006 |              72
           810 |              58

dbfiddle here

This isn't a full explanation, and it might not be possible to have one without knowing how Oracle does the sample internally. What is clear is that whatever Oracle is sampling from, either table or index, needs to be big to iron out the sampling wrinkles.


Is it possible to define a function within a stored procedure? I have a stored procedure:

create proc sp_MyProc(@calcType tinyint) as
begin
    -- some stuff collating data into #MyTempTable

    if (@calcType = 1) -- sum
        select A, B, C, CalcField = sum(Amount)
        from #MyTempTable t
        join AnotherTable a on t.Field1 = a.Field1;
        group by A, B, C
    else if (@calcType = 2) -- average
        select A, B, C, CalcField = avg(Amount)
        from #MyTempTable t
        join AnotherTable a on t.Field1 = a.Field1;
        group by A, B, C
    else if (@calcType = 3) -- some other fancy formula
        select A, B, C, CalcField = sum(case when t.Type = 1 then 1 else 0 end) * t.Factor
        from #MyTempTable t
        join AnotherTable a on t.Field1 = a.Field1;
        group by A, B, C
    -- plus a whole bunch of other, similar cases
    else
        select A, B, C, CalcField = 0.0
        from #MyTempTable t
        join AnotherTable a on t.Field1 = a.Field1;
        group by A, B, C
end

Now all these different cases for different values of @calcType seem to be wasting a lot of space and causing me to copy and paste all over, which always sends shivers down my spine.

Is there some way of declaring a function for CalcField, similar to lambda notation in C#, to make my code more compact and maintainable?  I would want to do something like this:

declare @func FUNCTION(@t #MyTempTable) as real -- i.e. input is of type #MyTempTable and output is of type real

if (@calcType = 1) -- sum
    set @func = sum(@t.Amount)
else if (@calcType = 2) -- average
    set @func = avg(@t.Amount)
else if (@calcType = 3) -- some other fancy formula
    set @func = sum(case when @t.Type = 1 then 1 else 0 end) * @t.Factor
-- plus a whole bunch of other, similar cases
else
    set @func = 0;

select A, B, C, CalcField = @func(t)
from #MyTempTable t
join AnotherTable a on t.Field1 = a.Field1;
group by A, B, C

Obviously the syntax here doesn't work, but is there something that will achieve what I want?

No this isn't possible.

A permanent TVF or view isn't an option due to the reference to #MyTempTable

I've seen a connect item request for temporary views and agree sometimes they would be useful. This was closed as duplicate of one requesting Module-level table expressions.

You might be able to rewrite as

SELECT A,
       B,
       C,
       CASE @calcType
         WHEN 1
           THEN sum(Amount)
         WHEN 2
           THEN avg(Amount)
       END
FROM   #MyTempTable t
       JOIN AnotherTable a
         ON t.Field1 = a.Field1
GROUP  BY A,
          B,
          C

Or if your needs were more complex (e.g. same shape resultset and using same source but different grouping conditions)

WITH Base
     AS (SELECT A,
                B,
                C,
                Factor,
                Type
         FROM   #MyTempTable t
                JOIN AnotherTable a
                  ON t.Field1 = a.Field1)
SELECT A,
       B,
       C,
       sum(Amount)
FROM   Base
WHERE  @calcType = 1
GROUP  BY A,
          B,
          C
UNION ALL
SELECT A,
       B,
       C,
       CalcField = 0.0 * t.Factor
FROM   Base
WHERE  @calcType NOT IN ( 1, 2, 3 )

With the last one in particular you might consider OPTION (RECOMPILE) to simplify the plan. (Likely it would have a filter with a start up predicate without the hint and not actually execute the redundant branches but you would need to check. Also row estimates could be wrong with this approach if the startup predicate is retained).

If neither of those suited you would need to get into the realms of dynamic SQL.


oracle database upgrade issue Currently i'm using Oracle 10g XE database and want to upgrade to Oracle 11g XE. When  i try to run the installer, it fails because of running instance. I searched the internet and got to know i have to uninstall 10g XE before installing 11g. The problem is i want to retrieve my schemas in 11g. So as stated here, i tried,

exp system/<system_password>@<database> file=<user>.dmp owner=<user>

but i got the following error

SP2-0734: unknown command beginning "exp system..." - rest of line ignored.

i read on the internet i tried using

SQL> expdp system/password full=Y directory=DUMP_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.log

but got

SP2-0734: unknown command beginning "expdp syst..." - rest of line ignored.

is there a way (preferably GUI) to just back up my data before uninstall so that it can be recovered after upgrade? I know complete databses can be put into an sql script for mysql using phpmyadmin. Does anything similar exist for oracle as well??

Is recreating a table using netbeans a good solution?

exp and expdp are OS command-line programs.  That's why SQL*Plus doesn't recognise them.  You need to fire up a console (shell) window.

Note that you can run these wherever makes more sense, your local PC or on the database server itself, provided the Oracle client software is installed.

There is an in-database API for Datapump.   It's the PL/SQL package, DBMS_DATAPUMP.  Find out more.  However, for what your want to do, expdp makes more sense.

  " i'm quite amazazed that nowhere in the command i have specified anything regarding oracle databse" (sic)

Welcome to the magic of paths and scope.  Oracle exp is the first program of that name your computer found in your current direction or by traversing the command path.  So that's what it executed.

exp is an Oracle proprietary utility for exporting data from an Oracle database. It is never going to export anything else.  You didn't have to specify a target database because your environment is XE with only one in scope.  In an enterprise environment you would probably have to specify a database name in the connection string, because there could be several databases on your network.


PostgreSQL not using index scan when a second ORDER BY column is present I need to query the latest entry from a table with >130mn entries using PostgreSQL 9.5. There are two columns of interest, row_id (integer, the primary key), and row_time (timestamp, not null, with btree index).

This query works very fast:

EXPLAIN SELECT * FROM tbl ORDER BY row_time DESC LIMIT 1;
                                                QUERY PLAN
----------------------------------------------------------------------------------------------------------
 Limit  (cost=0.57..0.88 rows=1 width=423)
   ->  Index Scan Backward using tbl_row_time on tbl  (cost=0.57..40503877.59 rows=131390688 width=423)

In case there are several rows with the same row_time I would like to sort by row_id as a tie-breaker. Unfortunately, the query plan then regresses to a sequential scan:

EXPLAIN SELECT * FROM tbl ORDER BY row_time DESC, row_id DESC LIMIT 1;
                                   QUERY PLAN
---------------------------------------------------------------------------------
 Limit  (cost=9602337.32..9602337.32 rows=1 width=423)
   ->  Sort  (cost=9602337.32..9930814.04 rows=131390688 width=423)
         Sort Key: row_time DESC, row_id DESC
         ->  Seq Scan on tbl  (cost=0.00..8945383.88 rows=131390688 width=423)

I don't understand why this is happening. It would be easy to retrieve the rows with the maximum row_time using the index (typically  less than 10 rows) and then sort just these. Instead, the full table is scanned, which takes minutes instead of the sub-millisecond time of the first query.

Creating a multicolumn index on (row_time, row_id) makes PostgreSQL chose an index scan again, but increases the size of the index considerably. A subquery to get MAX(row_time), then filter by it and sort the result by row_id is also fast but seems overly complicated for my simple goal. Am I missing something here?

If you create an index on (row_time DESC, row_id DESC), then the second case will will act just as the first one.

LIMIT will always operate after sorting is taken care of, so when sorting without an index is necessary, it will sort the entire recordset prior to processing it through LIMIT.

I wonder what is it you want to achieve? Applying LIMIT over a primary indexed order, then including all rows matching the sorted field(s) in the limited result, and finally sorting the limited results by any other fields and limit again. This is the way to go in this case:

SELECT *
FROM tbl
WHERE row_time = (
  SELECT row_time
  FROM tbl
  ORDER BY row_time DESC
  LIMIT 1)
ORDER BY row_id DESC
LIMIT 1;

Whereas for LIMIT n where n > 1:

SELECT *
FROM tbl
WHERE row_time IN (
  SELECT row_time
  FROM tbl
  ORDER BY row_time DESC
  LIMIT n)
ORDER BY row_id DESC
LIMIT n;


admin user can not login to other databases after upgrading to mongodb 2.4 I had a mongodb 2.2 database with auth enabled. but after upgrading to 2.4 my admin user can not login to any database other than admin database. I removed it from system.users then created another admin user with:

db.addUser( { user: "admin", pwd: "123456", roles: [ "userAdminAnyDatabase" ] } )

But again I can not login to any database except admin database. Can anyone help?

The short answer is, at least for the user you list: this is how it is supposed to work with the new user privilege model in 2.4+

To explain:

The userAdminAnyDatabase role means that the user you have now created will be able to grant access (for itself, or any other users) to any other database, hence the designation in the docs as a "super user".  However, that does not automatically grant that admin user read/write permission on all those databases (though it can bestow them upon themselves).  Hence that user can't authenticate against anything other than the admin DB, but by being able to do so, it can grant the relevant permissions elsewhere.

I think what you might be looking for is the readWriteAnyDatabase role, though I would recommend using that particular role sparingly (if at all) and instead grant access as needed, for greater security.

I can't really speak to the user permissions prior to removal, because they have not been listed, just the role of the new user.


Reducing execution time for this gnarly SQL I'm creating an order system of some kind.  There is a function that I use to get orders (in PHP) and retrieve the required information about the order.  However, it seems that I am pulling a lot of information, including several left joins that could be the victim of executing for a period of more than 3 seconds.

The amount of rows in each table that is used.

Orders - 2,485
Order items - 27,321
Order payments - 3
Customers - 68
Customers' addresses - 153 (joins twice for invoice and delivery address)
Order statuses - 2,277
Status types - 5
Recurring orders data - 46

I know that you will need to use EXPLAIN <query> to debug the query.  It seems that it does not use any of the indexes that I have placed on the tables (basically there is an index on every column that I am joining, apart from the PRIMARY KEY columns)

This is the debug:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   PRIMARY     <derived2>  ALL     NULL    NULL    NULL    NULL    2302    Using filesort
2   DERIVED     <derived3>  ALL     NULL    NULL    NULL    NULL    2302    Using temporary; Using filesort
3   DERIVED     <derived4>  ALL     NULL    NULL    NULL    NULL    2484
3   DERIVED     o   eq_ref  PRIMARY     PRIMARY     4   oi.order_id     1   Using where
3   DERIVED     <derived6>  ALL     NULL    NULL    NULL    NULL    3
3   DERIVED     c   eq_ref  PRIMARY     PRIMARY     4   orders_system.o.customer_id     1
3   DERIVED     <derived7>  ALL     NULL    NULL    NULL    NULL    153
3   DERIVED     <derived8>  ALL     NULL    NULL    NULL    NULL    153
3   DERIVED     <derived9>  ALL     NULL    NULL    NULL    NULL    13
3   DERIVED     <derived10>     ALL     NULL    NULL    NULL    NULL    2277
10  DERIVED     os  ALL     NULL    NULL    NULL    NULL    2277    Using temporary; Using filesort
10  DERIVED     st  index   PRIMARY     PRIMARY     1   NULL    5   Using where; Using index; Using join buffer
9   DERIVED     recurring_orders    ALL     NULL    NULL    NULL    NULL    46  Using where; Using filesort
8   DERIVED     customer_addresses  ALL     NULL    NULL    NULL    NULL    153
7   DERIVED     customer_addresses  ALL     NULL    NULL    NULL    NULL    153
6   DERIVED     order_payments  ALL     NULL    NULL    NULL    NULL    3   Using where; Using temporary; Using filesort
4   DERIVED     <derived5>  ALL     NULL    NULL    NULL    NULL    2484    Using temporary; Using filesort
5   DERIVED     oi  ALL     order_id    NULL    NULL    NULL    27321   Using where; Using temporary; Using filesort
5   DERIVED     o   eq_ref  PRIMARY     PRIMARY     4   orders_system.oi.order_id   1

Visual:

I am not sure why the query is executing a full table scan, this is problematic if it is taking that long to run, especially if orders are about to increase daily.

This is the query that is used to execute, it's too big to view here.

What can I do to improve this query?

At the moment, I am using an instance of MySQL 5.6.19 which has the performance_schema enabled on a 2GB x64 Linux laptop (nothing stellar by any means!). My normal response time is approx. 0.5 s - not too bad. What's your setup (RAM, disk config, OS &c.)?

I must say that I'm surprised at 3 seconds on a server. Are you sure that it's not some sort of network issue?

I checked out I/O for the query and noticed something strange - every time the query executes, the order_items table is accessed 27,321 times - which is exactly the same as the number of records. This smells of a correlated subquery - but the SQL is horribly complex and I haven't been able to get to the bottom of it (not enough time). Obviously, you should only want to access the table once (or a few times maybe) at most. Order_status is only read once for example.

It's not really a surprise that your query doesn't use indexes - see this post here which gives some explanation as to why - basically if the optimiser thinks that a FTS (full table scan) is cheaper than using the index, then that's what it will do. This is a function of the size of the table(s) (yours are v. small ===> FTS) and/or percentage of records to be retrieved (yours 100%, again ==> FTS).

I notice that you are using MyISAM tables, for full text indexes? InnoDB now has full text indexes - see here and check the docco. Also (and more importantly), InnoDB allows you to use DRI (declarative referential integrity) which is very important - IMHO, there is no good reason to use MyISAM tables anymore.

Could you explain the business logic behind this query? It strikes me (and this is only an opinion) that it is overly complex for a relatively simple system with 8 tables - has it grown over time and been added to by different people? I am respectfully suggesting that the time may have come for a complete rewrite from scratch rather than tinkering at the edges.

Another problem is that for orders, you have ordered_date as an INT(12) - when I first saw the data, I thought that it was a timestamp. You are creating extra work for yourself if you don't use the correct data type for your columns. Error checking is more difficult.

[EDIT] in response to OP's comments (easy to hard)

1)

INT v TIMESTAMP v DATE/DATETIME

Take a look at these (1, 2, 3, 4) posts. I suppose that INT v TIMESTAMP is relatively trivial (however, see point 4 in the answer here), but INT/TIMESTAMP v DATE/DATETIME isn't. Check out the answer to post 4 - you should be using DATEs/DATETIMEs when you are using INT/TIMESTAMP.

2)

  MySQL 5.6.19, but the server is using 5.5.37. Would upgrading improve
  the performance?

Maybe, maybe not - it's probably trivial in respect to the issues that I'll be going through in point 3 - see InnoDB/Full Text Indexing. If I had to guess, upgrading would decrease performance by no more than a couple of percent (some think that the performance schema adds overhead) and quite possibly be a significant improvement. These things can vary according to many parameters (disk, CPU, RAM, network, server load, phase of the moon...). Overall though, performance should improve release by release although YMMV.

3)

General remarks.

I changed the storage engine for your system from MyISAM to InnoDB. This resulted in the query time being consistently reduced from ~ .45s to ~ .37s (~20%) - a noticeable improvement - maybe you should try it? I can only speak for the testing (not v. thorough) I did on my 2GB x64 Linux laptop. You didn't answer my question about your own server specs. If I'm getting a response time of ~ .4s, I can't for the life of me understand why you're getting 3s? Unless it's (as I suggested) a network issue, or is the server under load when you run the query?

InnoDB now has full text indexing in 5.6 (see my point 2 above) - AFAIK, that will not work in 5.5 (i.e. your table declarations will fail for InnoDB) - another reason to upgrade.

I tried to put in FOREIGN KEY relationships in the tables, but there appears to be problems with your data - I'm getting different counts after reloading the schemas (without FK and with FK). This can mean you have orphaned records, which may or may not affect the result. Anomalies occurred with orders/order_items and customers/customer_addresses. e.g.

mysql> select i.item_id, i.order_id from order_items i where i.order_id not in (SELECT ORDER_ID from orders);
+---------+----------+
| item_id | order_id |
+---------+----------+
|   26892 |     2433 |
+---------+----------+

Worse, none of the order payments appear valid

mysql> select p.order_id from order_payments p where p.order_id not in (select order_id from orders);
+----------+
| order_id |
+----------+
|     2143 |
|     2214 |
|     2216 |
+----------+
3 rows in set (0.00 sec)

mysql>
mysql> select * from order_payments;
+------------+----------+--------+------------+------------+
| payment_id | order_id | amount | date       | is_deleted |
+------------+----------+--------+------------+------------+
|          1 |     2143 |  99.34 | 1405900800 |          0 |
|          2 |     2216 |   5.09 | 1406505600 |          0 |
|          3 |     2214 |  55.10 | 1406505600 |          0 |
+------------+----------+--------+------------+------------+

I couldn't figure out the other relationships in the time available - maybe we can go over them. It may well be that a solid DB structure with overtly declared FOREIGN KEYs may perform better. As a general rule, the more information you give the optimiser, the better.

The point about the optimiser not using indexes when tables are too small and/or too high a percentage of records is accessed still stands - however, there is no harm in doing things correctly - and as your data volumes change/grow, the optimiser may choose different plans - and the more info, the better.

The orphaned record issue (forgetting about performance for the moment) is another GOOD reason to switch to InnoDB. With proper DRI (declarative referential integrity), you cannot have orphaned records, which can be the source of tricky to find bugs.

Finally, just a few words on the system generally. It seems to me that this query is like a runaway train - it has built up and built up momentum and is now overly complex for purpose. Maybe a step back is required? I got to thinking about triggers and using them to populate other tables (similar to a materialised view) and/or even the use of views themselves. You need to have a system that you are comfortable with and that you can change to suit your needs. Some behemoth sitting in the corner spewing out answers that nobody understands is not very conducive to flexibility, upgrading or peace of mind :-).

I would like to do three things (using InnoDB)

1) get all the data types in order (DATE/DATETIMEs when appropriate)

2) sort out the foreign key relationships (need input on that - please feel free to contact me off list for this - my email is in my profile).

3) break this juggernaut down into its constituent components and explore methods of making it better, faster, easier to understand and modify.

I am unable to spend 09:00 - 18:00 at this, but would like to have a look in the evening if that suits and you are interested.


Add info to mysql view around an IP address - 10.10.1.100 to https://10.10.1.100 I have built a database in MySQL to replace the use of MANY excel spreadsheets that document our servers, network, IP addresses, etc.

In that database I have created a view called DRAC (dell remote access card) that pulls a server name, asset tag, and IP address.

SELECT Matrix.Name AS Name,
  Matrix.DracIP AS DracIP,
  Matrix.Asset
FROM Matrix
WHERE Matrix.Type = 'Physical-Lou1'

I have been able to pull this information to a webpage (w/ DokuWiki+sqlcomp plugin) and it generates a nice table for me.

I was wondering if there was some kind of foo I could use to translate the ip address from this: "10.10.1.100" into this "https://10.10.1.100" within the view so it will display on the page as links that people can use instead of just a bare IP.

EDIT: I ended up w/ this code for those that are interested.

SELECT Matrix.Name AS Name,
  Concat('', Matrix.DracIP,'') AS DracIP,
  Matrix.Asset AS Asset
FROM Matrix
WHERE Matrix.Type = 'Physical-Lou1'

SELECT Matrix.Name AS Name,
  CONCAT('https://',Matrix.DracIP) AS DracIP,
  Matrix.Asset
FROM Matrix
WHERE Matrix.Type = 'Physical-Lou1'


Using the newly added LAG() and LEAD() functions makes it look easy:

WITH a AS
    ( SELECT PersonID, FloorNum, EnterTime,
             PrevFloorNum = LAG(FloorNum) OVER
                                (PARTITION BY PersonID ORDER BY EnterTime)
      FROM RoomAssignments
    )
SELECT PersonID, FloorNum,
       FromTime = EnterTime,
       ToTime = LEAD(EnterTime) OVER
                    (PARTITION BY PersonID ORDER BY EnterTime)
FROM a
WHERE PrevFloorNum <> FloorNum OR PrevFloorNum IS NULL
ORDER BY PersonID, FromTime ;

Test in SQLfiddle


Problem with (not) Locking Tables during Restore First time here in DBA. I'm working to create a simple and strong database backup plan. My solution that is already working to create a gzipped, encrypted backup is:

$string = "mysqldump -u$db_user -p$db_password $database $table | gzip | openssl enc -aes-256-cbc -salt -pass pass:$ePass > " . $path . $table . '_' . $d . "_en.sql.gz";
$result = shell_exec ( $string );

I am trying to test the dump and my programming (head to toe) by importing it into another another table. The table User_test_import below doesn't exist in the operational and live site.
The dump is creating as an example (after modification to test import):

-- MySQL dump 10.11
--
-- Host: localhost    Database: accoun3_flifo
<omitted>
--
-- Table structure for table `User`
--

DROP TABLE IF EXISTS `User_test_import`;
CREATE TABLE `User_test_import` (
   <omitted>
);
--
-- Dumping data for table `User`
--

<Line in question>
LOCK TABLES `User_test_import` WRITE;               /*  Line 59  */
/*!40000 ALTER TABLE `User` DISABLE KEYS */;
INSERT INTO `User_test_import` VALUES ( ... <omitted> ...
);

The third line up had to be 'commented' in order for the import to work using:

$import = 'User_5.sql.gz';
$string = "gunzip < $path$import 2>&1 | mysql -u$db_user -p$db_password $database 2>&1";

From the command line the above reports ERROR 1100 (HY000) at line 60: Table 'User' was not locked with LOCK TABLES User is an existing table. Line 59 is the

The question then is, how do I use the dump with it's LOCK TABLES statement? Thanks.

Edit Question:
Why is TABLE User was not locked with LOCK TABLES occurring? How can it be prevented?

The reason the server is complaining that 'User' was not locked is because of this:

/*!40000 ALTER TABLE `User` DISABLE KEYS */;

That looks like a comment, but it isn't a comment.  MySQL has a special case for comment parsing for backward-compatibility and cross-compatibility.

Comments in this style are processed as if they weren't comments at all, if the server's version is greater than or equal to the number shown, so in this case, any version of MySQL Server 4.0.0 or greater will execute it and any prior version won't.

So, the restore is asking the server to ALTER a table that isn't locked.  You can only do that when your session has no tables locked at all.

If you change that table name to your test table name, the restore should work.

In similar style, if there's an exclamation point in the first position followed by a space, it will be parsed as if not a comment by all versions of MySQL.