MySQL Cluster Only Sync Partially I am new to MySQL, but I have to solve this problem quickly.

I installed FileRun (which is a file share service) on two nodes, those two nodes have MySQL Cluster backend.

In Filerun GUI, I created a new user on the primary node, but the secondary one couldn't get the data. However, if I manually create a table and insert a data in the filerun database, the synchronise can proceed.

Here is what I did:

On both nodeA and nodeB:

mysql> use filerun;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select id,name from df_users;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | Superuser |
+----+-----------+
1 row in set (0.00 sec)

On nodeA, I created a new user via Filerun GUI, I query again and can see the new user has been added:

mysql> select id,name from df_users;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | Superuser |
|  9 | test      |
+----+-----------+
2 rows in set (0.00 sec)

However, on nodeB, the table was still not updated.

mysql> select id,name from df_users;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | Superuser |
+----+-----------+
1 row in set (0.00 sec)

For the debug purpose, I created a new table on nodeA in the same database:

mysql> create table students (name VARCHAR(10));
Query OK, 0 rows affected (2 min 0.82 sec)

mysql> insert students set name="john";
Query OK, 1 row affected (0.08 sec)

mysql> select * from students;
+------+
| name |
+------+
| john |
+------+
1 row in set (0.01 sec)

Back to nodeB, I can see the data has been transferred:

mysql> show tables;
+-----------------------+
| Tables_in_filerun     |
+-----------------------+
….omitted…..
| students              |
+-----------------------+
1 row in set (0.51 sec)

mysql> select * from students;
+------+
| name |
+------+
| john |
+------+
1 row in set (0.01 sec)

At the same time on nodeB, this event was popped up by MySQL log(only shows the table replication, I don't know why):

2017-09-09T00:40:58.202248Z 1 [Note] NDB Binlog: DISCOVER TABLE Event: REPL$filerun/students
2017-09-09T00:40:58.211230Z 1 [Note] NDB Binlog: logging ./filerun/students (UPDATED,USE_WRITE)

Again, I tried to manually insert a data into the "df_users: table on nodeA:

mysql> insert into df_users (id,name) values("2","Jack");
Query OK, 1 row affected (0.01 sec)

mysql> select id,name from df_users;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | Superuser |
|  2 | Jack      |
|  9 | test      |
+----+-----------+
3 rows in set (0.01 sec)

Unfortunately, the nodeB still receiving nothing!

mysql> select id,name from df_users;
+----+-----------+
| id | name      |
+----+-----------+
|  1 | Superuser |
+----+-----------+
1 row in set (0.00 sec)

Any idea about this? How to check the transaction between two nodes?

Other information:
Both two nodes has Ubuntu 14.04 installed.
MySQL is installed locally,version: 5.7.18-ndb-7.6.3-log

root@ndb-mgmd:~# ndb_mgm -e show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)] 2 node(s)
id=2    @192.168.0.20  (mysql-5.7.18 ndb-7.6.3, Nodegroup: 0, *)
id=3    @192.168.0.21  (mysql-5.7.18 ndb-7.6.3, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @192.168.0.30  (mysql-5.7.18 ndb-7.6.3)

[mysqld(API)]   2 node(s)
id=4    @192.168.0.20  (mysql-5.7.18 ndb-7.6.3)
id=5    @192.168.0.21  (mysql-5.7.18 ndb-7.6.3)

That is because the storage engine is not using ndbcluster. To address this, we should convert the storage engine. See my another post: Unable to Convert table Engine from MyISAM to NDBCLUSTER


How do I connect to mysql without having to type an empty password? I just installed MySQL, and by default the root user has no password. If I connect with this command:

mysql --port=3307 --host=127.0.0.1 -uroot -p

It asks me to enter a password, I just hit return, and it connects me. However, I want to make this part of a script, by piping a statement to it non-interactively, like this:

echo "$statement" | mysql --port=3307 --host=127.0.0.1 -uroot -p

But, when I actually do that, it still asks me for the password. How do I make it NOT ask for the password, and just not use one?

I tried removing the -p, and I get this message:

$ mysql --port=3307 --host=127.0.0.1 -uroot
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I would expect there to be some sort of --nopassword argument, but I can't find one.

Turns out the answer is actually to use the -p -- the long version allows you to set it to empty, like so:

mysql --port=3307 --host=127.0.0.1 -uroot --password=


MySQL Config for server with 8gb RAM and 4 cpus I have a server with 8gb ram and 4 cpus. I don't have enough time for testing and bench marking so I would like to get the advise of experts like you guys, let me know if something is wrong with my mysql config.

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
#connection setting
max_connect_errors      = 10
max_connections         = 400
wait_timeout            = 60
connect_timeout         = 10
interactive_timeout     = 60
#cache setting
query_cache_limit       = 2M
query_cache_size        = 50M
query_cache_type        = 1
table_open_cache        = 5000
thread_cache_size       = 100

#buffer sizes
key_buffer_size         = 20M
sort_buffer_size        = 2M
read_buffer_size        = 2M
join_buffer_size        = 2M

#tmpdir / temp table sizes
tmp_table_size          = 256M
max_heap_table_size     = 256M

#misc. settings
default-storage-engine  = innoDB
datadir                 = /var/lib/mysql
skip-external-locking
server-id               = 1
open-files-limit        = 65535
max_allowed_packet      = 64M

#innodb settings
innodb_data_file_path     = ibdata1:10M:autoextend
innodb_thread_concurrency = 0
innodb_buffer_pool_size   = 4G
innodb_log_buffer_size    = 8M
innodb_file_per_table     = 1
log_error               = /var/log/mysql/error.log
log_slow_queries        = /var/log/mysql/mysql-slow.log
long_query_time         = 2

#log-queries-not-using-indexes
[mysqldump]
quick
[mysql]
[isamchk]
key_buffer              = 16M
!includedir /etc/mysql/conf.d/

I'd agree with comment that asks for "purpose" of the mysql instance though these days you get decent configuration in-built provided with my.cnf.

You should look to use them, my-large/huge/medium.cnf etc...
I see you have query cache and this link will help you understand best value for it.

Checkout this blog post for configuring few important parameters.
Finally I'd also like you to review this config wizard.

Again... none of this will give you exact and perfect config... Be ready to monitor and make according changes.


Match two distinct values from a single column in a joined table I have three tables:
class:
class_id
class_name

student:
student_id
student_name

class_schedule:
class_id
student_id

I want to select all the classes where studentA and studentB are in the same class using the student names. I can use a subquery to pull all the classes studentA is in, and then from that subset the classes that studentB is in and that works. That being said, it is terribly inefficient. I have tried a number of solutions including joining the same table twice, once for each value I want to find, but always get an empty result set.
For testing and prototyping purposes I am using sqlite, but will reside on DB2 long term.

Select c.class_id, c.class_name
  From class c, class_schedule cs
  Where c.class_id = cs.class_id
    And cs.student_id in (
          select student_id from student
            where student_name in ('studentA','studentB'))
  Group by c.class_id, c.class_name
  Having count(*) = 2

Check it out: DB Fiddle


Getting error while importing large data through csv I am new Postgres. I am getting constraint violation errors while importing a
large CSV file.

I want to disable all of the database constraints temporarily - I do not know how to do this.

It really depends which constraints are violated. You neglected to disclose that in your question.
To disable triggers (including foreign key constraints):
ALTER TABLE tbl DISABLE TRIGGER ALL;

Be sure to revert it afterwards:
ALTER TABLE tbl ENABLE TRIGGER ALL;

More in the manual.
However, this does not disable Check, Not-Null, Unique, Primary Key or Exclusion constraints, which are implemented independently.
You could drop and recreate those. But this is rarely useful, constraints are in place for a reason.
Alternative
An alternative would be to import your CSV file into a temporary copy of the target table and repair any data that would violate constraints before inserting from there. Depends on the size of the file and available RAM. You could also make this a regular table for huge files or longer processing.
CREATE TEMP TABLE tmp AS SELECT * FROM target_table LIMIT 0;
COPY tmp FROM '/path/tp/file.csv';

For instance, to remove duplicates from imported data as well as between import and existing data:
INSERT INTO target_table
SELECT DISTINCT *
FROM tmp
LEFT JOIN target_table t0 ON ...
WHERE t0.target_table_id IS NULL;


Optimization around static cursor use I'm currently in-house support for a vendor-supplied application which uses static cursors to crawl through a list of data. I've leaned on the vendor as much as I can to fix their cursor use with no affect, and this software is critical to our operations.

My only recourse in the meantime is to find a way to configure SQL Server that may relieve some of the pressure.

Static cursors that I cannot control are opened with queries that look like:

SELECT {manyColumns} from {table} with (nolock)
WHERE {codeFieldName} > @param
ORDER BY {codeFieldName}
option (fast 1)

The indices are set up well and the result set that ends up being built in tempdb is generated just about as quickly as it can it seems. However, this is where it gets ugly.

Most of the resulting table isn't used. In fact, usually only the first item is used, but these tables are typically thousands of entries (if not tends of thousands) long. What's worse, typically there's no actual need for a static cursor as the underlying data most of the time won't change while the cursor is being accessed. And to make matters even worse, this is the technique that's used to scan an entire subset of the table since these queries are being used only to fetch information about the next item -- create and use cursor, read the top entry, discard the cursor, then create and execute the cursor again... (Yes, this leads to O(n^2) scans, amazingly.)

Is there some way I can force SQL to handle these cursors as dynamic cursors? Or is there a way I can have static cursors not generate a full table in tempdb until the data is about to change? Any other solutions?

Edit: SQL Server 2005 presently, but moving to 2008/2008R2 isn't entirely out of the question. (I have a test environment with 2008.)

Tried so far:

Threw tempdb on a RAM disk. No performance increase. Looks like SQL Server isn't doing much in the way of I/O during these queries and post-2000 versions of SQL have better memory vs. tempdb management. (Note the RAM disk option was taken out after 2000, likely for this reason.)

There's really only a few things that I can think of that might help here, without actually changing the cursor code:

Make sure that your tempdb resides on different physical drives from your database files.  This won't make the temp/static rowset any smaller, but it will make it faster.
Adjust the CURSOR_THRESHOLD configuration setting:  This is a tricky thing and not normally recommended, however, it can have a big impact on this very situation (just not always a positive impact).  My guess would be that you'd want to adjust it from the default of -1 to something like 1000.  But play around with it, and be careful, as it's server instance-wide and can affect other things as well.
Plan Guides:  I think that you need 2008+ for this, and I am not sure if it works on cursor SELECT's.  They are also complicated to use, but if they do work, they can really do amazing things to a repeatable query that is using sub-optimal query plans.

That's all that I can think of ...


PostgreSQL COPY: is it always faster to delete and recreate indexes? In this answer, Erwin Brandstetter recommends

  It is also substantially faster to delete indexes before a huge bulk INSERT / COPY and recreate them afterwards as it is much more costly to incrementally adjust indexes for every inserted row than to create them at once.

1) Is this true even when the table is already large?  If not, is there an easy way to estimate the point of diminishing return?  For example, I'm doing COPY of 30,000 records at a time into a table which already has say 100 million rows.  Each batch is relatively small, but on the other hand the total batches on hand to import at the moment will more than double the size of the table.

2) If I were to delete the index before COPY, would recreating it be faster if the COPY is done with records already in index order?

There is only one index on the table, which is a 5-column primary key.

I'm doing this on a non-production machine, and I've already disabled fsync, syncronous_commit, autovacuum, etc.  I'm executing the COPY operation from four separate processes concurrently on an 8-core machine.  I've got 12GB of memory, so I could set maintenance_work_mem to 1GB or more if it would help.

My guess would be going with some benchmarking, measuring the time it's taking you to recreate the full index, the time it takes to insert 10k rows without index, with index. then I'd try with 1000 rows, 5000 rows, 10000 rows, to try to figure out a trend.

Because even if you try to reduce the amount of factors that could affect the operation time (as Auto-Vacuum) it still depends on your hardware and your whole postgres.conf

BONUS: if you share with us some benchmarks, we could gladly help you to figure out the trend.


Convert Login from Windows Authentication to SQL Server authentication? I have an account that is setup for Windows authentication. The account is used by SSRS for running reports and has various permissions in multiple databases. I need to convert this account to use SQL Server authentication.

I don't see a way to change it in SSMS. Is there a way to "flip a switch" and make it use SQL Server authentication?

For details as to why I want do this, see this question.

I think your best bet would be to script out the server level permissions granted to the Windows Login and save the SID, delete the Windows Login and recreate it as a SQL Login with the same SID, and finally grant the permissions back on the Login.

This way the login will have it's server level permissions recreated and retains it's database level permissions since the login will match the database user via SID.

    CREATE LOGIN [LoginName] WITH PASSWORD=N'PASSWORD'
            ,DEFAULT_DATABASE=[master]
            ,CHECK_EXPIRATION=OFF
            ,CHECK_POLICY=OFF
            ,SID=[SID]


Why does my query run fast in Environment A, but slow in Environment B? I have a piece of SQL that seems to run really fast in Environment A, but the exact same query runs really slow in Environment B!

Environments are supposed to be the same, so what should I do and/or where should I look in order to see why the query doesn't perform the same?

There are many factors both internal and external to SQL Server that can cause the same query to perform differently across different environments, even when they are configured close to exactly the same, any one of which can result in very different query plans and performance.

Server

Is the hardware across environments the same (disks, memory, CPU, etc.) ?

If VMs are being used, could noisy neighbors affect overall VM performance?
If in the cloud, do autoscaling and other configurations have parity?

Are environments mixed between physical/virtual/cloud?
Do the OS versions match?
Are environments in different datacenters?

Instance

Are the SQL Server versions the same?

A CU or SP can make a world of a difference even if the major versions are the same.

Is the active workload during the query execution(s) comparable?

Are the same volume of queries present in all environments?
Is the nature of the workload the same across all environments?

Do all environments participate in the same HA/DR setup?

Many times lower environments will not have Availability Groups, Log Shipping, or Replication setup while production / DR may be using these technologies.

Do the same maintenance jobs get run on the same schedules in all environments?
Are trace flags equivalent across all environments?
Are the same backup jobs running in all environments?

Impact from backups should be minimal, but often they are not run at all on lower environments.

Are the sys configurations the same?

Database

Are the schemas / indexes / statistics / objects all equal across environments?
Does the exact same data exist across environments?

Amount of data
Distribution of data
Size of data (think dummy data in variable length data types that may not reflect the size of actual values in other environments)

Are database level configs the same?
Are compatibility levels the same?

With all of this in mind, it is not surprising that in many cases it is simply not possible to perfectly copy every aspect of a database across different environments. While testing can give a good degree of certainty as to how queries will perform in each environment, it should be no surprise if there are discrepancies between environments. When developing a new query, additional tuning is often needed as it moves towards production.

Usually, tuning a query that is slower in one environment should not cause regressions in the execution plans generated, so it is an opportunity to adjust indexes, statistics, or the query itself for an overall improvement.

Final note: lower environments are undersized and often should not be expected to give the same performance as a production or pre-production environment.

More Resources:

Different Plans for "Identical" Servers by Aaron Bertrand


Azure sql database grant select on certain tables I have created a user in a Azure sql database in SSMS. Now trying to give select permission to certain tables in a db to this user. The security control options seems quite different than on premise sql server. How can I do these on a Azure db?

I have tried this on a on premise sql server and it works fine. But got error when run these on the azure sql db.

    USE [master]
    GO
    CREATE LOGIN [jnj_user] WITH PASSWORD=N'P2ssw0rd123', DEFAULT_DATABASE=[CRM], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    GO
    USE [CRM]
    GO
    CREATE USER [jnj_user] FOR LOGIN [jnj_user]
    GO

error:

    Msg 40517, Level 16, State 1, Line 3
    Keyword or statement option 'default_database' is not supported in this version of SQL Server.
    Msg 40508, Level 16, State 1, Line 5
    USE statement is not supported to switch between databases. Use a new connection to connect to a different database.

also this does not work on Azure db either:

    GRANT SELECT ON [dbo].[JJVCEtrialOB] TO  jnj_user

error:

    Cannot find the user 'jnj_user', because it does not exist or you do not have permission.

You need to ensure the login is added on all the levels

---- ON SERVER LEVEL
CREATE LOGIN TestUser
    WITH PASSWORD = 'ThisIsAStrongPassword!'
GO

---- ON Master Level
CREATE USER TestUser
    FOR LOGIN TestUser
    WITH DEFAULT_SCHEMA = dbo

---- ON SQL DB LEVEL
CREATE USER TestUser
    FOR LOGIN TestUser
    WITH DEFAULT_SCHEMA = dbo
GO

GRANT SELECT ON OBJECT::dbo.Sales TO TestUser;
GO

You need to add the login on server level under the master context

then add a user to the master db under master context

and then add the user to the sqldb and grant its rights

EDIT You cannot use the use statement, so you will have to manually switch context in SSMS


Prevent the collision data because duplicate id from imported database MariaDB I am currently having a trouble with my Web Application. I made a Web application for a certain company. I made the app using CodeIgniter 3.

I built the database using Maria DB. For the id in each table, i am using Incremental Id for my application database for each table. I usually deploy the web app to the cloud server (sometimes the company have their own dedicated server, but sometimes haven't ). One day, there is a company that  they don't want to deploy the app that i have made before to the cloud ( for the security purposes they said ).

This company wanted to deploy the app to the employee's PC personally in the office, while the pc for each employee not connected to each other ( i.e stand alone pc/personal computer/employee's Laptop ). They said, for every 5 months, they would collect all of the data from employee's personal computer to company's data center, and of course the data center are no connected to the internet. I told them that's not the good way to store their data. ( because the data will be duplicate when i am trying to merge all of the data into one, since my database id are in Incremental id ). The id would be collapse while my database are incremental ID. But, The company still kept to make the app that way, and i don't know how to solved this. Does anyone know how to solved this problem ?

They have at least 10 employees that would used this web app. According that, I have to deploy the app to the 10 PC personally.

Additional info : Each employee have their own unique id which they got from the company

The study cases is what i describe below:

id   |  employee_id  | employee_name   |
1    | 156901010     |  emp1
2    | 156901039     |  emp2
3    | 156901019     |  emp3
4    | 156901015     |  emp4
5    | 156901009     |  emp5
6    | 156901038     |  emp6

the problem is whenever they fill the form from that application, some of the table are not stored the employee's id but the new id that come from increment id.

For example electronic_parts table. They have the attribute like below:

| id  |  electronic_part_name  |  kind_of electronic_part  |

if the employee 1 fill the form from the web app , the table's content would like below.

| id  |  electronic_part_name  |  kind_of electronic_part  |
|  1  |   switch               |           1               |

and if the employee 2 fill the form from the web app , the table's content would like below.

| id  |  electronic_part_name  |  kind_of electronic_part  |
|  1  |      duct tape         |           10              |

When i tried to merge the contents of the table into the data center it would falling apart because the duplicate id.

A few options: Allocate different ID ranges for each user, use composite primary keys, use UUIDs or use some of the mechanism that enables the auto_increment feature to be used in master-master replication:

You can specify a different AUTO_INCREMENT position when you create the tables for each individual employee's database so that there is a range of IDs allocated for each employee. E.g.

CREATE TABLE electronic_parts (
  id INT USIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  electronic_part_name VARCHAR(40),
  kind_of_electronic_part INT UNSIGNED
) AUTO_INCREMENT=1000;

This is probably not a good idea as there is nothing to prevent over-zealous employees from creating so many records that they end up in someone else's range. (Though I suppose you could build a constraint into your application to avoid such collisions.)

Re-design the primary keys so that they become composite keys with one auto-incrementing key as you have already and one key that is unique for each employee. E.g..:

CREATE TABLE electronic_parts (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  employee_id INT UNSIGNED NOT NULL,
  electronic_part_name VARCHAR(40),
  kind_of_electronic_part INT UNSIGNED,
  PRIMARY KEY (id, employee_id)
);

You application then has to populate the employee_id with a unique value for each employee. E.g.:

INSERT INTO electronic_parts (
  employee_id, electronic_part_name, kind_of_electronic_part
)
VALUES (156901010, 'switch', 1);

Thanks to the auto-incrementing id component of the primary key, you don't have to worry about populating that.

Once it's time to merge all the databases, all will be fine because the primary keys are composites with one component unique for each employee, and one component unique within each individual database, so together this makes each primary key value unique across all databases.

I've tested this successfully in MariaDB 10.2.

Use the UUID() or UUID_SHORT() functions to generate globally unique primary keys instead of auto incrementing primary keys. Your application then needs to either explicitly call one of these functions when creating the records, or you could use a trigger. (I personally think it's bad design to use triggers this way, but it does work.)
Assuming you know a fixed upper limit to how many users there will be (n): You can use auto incrementing primary keys and set auto_increment_increment to n, and then set auto_increment_offset to 0 for the first user, 1 for the second user, 2 for the third user, and so on. This way you will avoid collisions in the same way that auto_increment is used in Galera clusters and other master-master replication configurations.


Selecting values from one table which don't exist in the other I have two tables, one containing a list of all orders and another containing a list of orders that have been invoiced.

I have the following query which I'm testing in SQL Server Management Studio, but it's not returning the correct data. I just want to see all orders that haven't been invoiced. E.g. Any order that's not in the invoicing table. Both the Job Number and Sub Job Number are crucial. These are known as VixJobSite and VixJobSub in the invoicing table.

SELECT
VixJobSite,
VixJobSub,
dbo.JOBCOSTING_JOBS.JOB_NUMBER,
dbo.JOBCOSTING_JOBS.SUB_JOB_NUMBER,
SummaryRef,
dbo.SERVICERECORDS_CALL_OUTS.DATE_REQUIRED

FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    '\\nemesis\O\X\Invoicing Database\BackEnd\TestInvoicing.mdb';
    'admin';'',tblSummaryInfo),dbo.SERVICERECORDS_CALL_OUTS,dbo.JOBCOSTING_JOBS,dbo.SALES_ACCOUNTS
WHERE dbo.JOBCOSTING_JOBS.INVOICE_TO_ACCOUNT = dbo.SALES_ACCOUNTS.INV_TO_ACCOUNT
AND SERVICERECORDS_CALL_OUTS.JOB_PRIORITY <> 'Q'
AND SERVICERECORDS_CALL_OUTS.JOB_PRIORITY <> 'P'
AND SERVICERECORDS_CALL_OUTS.JOB_PRIORITY <> 'X'
AND SERVICERECORDS_CALL_OUTS.ENGINEER<> '*QR'
AND SERVICERECORDS_CALL_OUTS.DATE_LOGGED like '%2018%'
AND dbo.SERVICERECORDS_CALL_OUTS.STATUS = 'C'
AND dbo.SALES_ACCOUNTS.ACCOUNT_NUMBER = dbo.JOBCOSTING_JOBS.JOB_NUMBER
AND dbo.SERVICERECORDS_CALL_OUTS.INVOICE_TO =       dbo.SALES_ACCOUNTS.INV_TO_ACCOUNT
AND dbo.JOBCOSTING_JOBS.SITE_ACCOUNT_NUMBER = dbo.SERVICERECORDS_CALL_OUTS.JOB_NUMBER
AND dbo.SERVICERECORDS_CALL_OUTS.JOB_NUMBER = dbo.JOBCOSTING_JOBS.JOB_NUMBER
AND dbo.SERVICERECORDS_CALL_OUTS.SUB_JOB_NUMBER = dbo.JOBCOSTING_JOBS.SUB_JOB_NUMBER
AND dbo.SERVICERECORDS_CALL_OUTS.INVOICE_TO = dbo.JOBCOSTING_JOBS.INVOICE_TO_ACCOUNT
AND dbo.SERVICERECORDS_CALL_OUTS.JOB_NUMBER = VixJobSite COLLATE DATABASE_DEFAULT
AND dbo.SERVICERECORDS_CALL_OUTS.SUB_JOB_NUMBER = VixJobSub COLLATE DATABASE_DEFAULT
AND dbo.JOBCOSTING_JOBS.JOB_NUMBER = VixJobSite COLLATE DATABASE_DEFAULT
AND dbo.JOBCOSTING_JOBS.SUB_JOB_NUMBER = VixJobSub COLLATE DATABASE_DEFAULT
and not exists
( SELECT *
  FROM JOBCOSTING_JOBS,SERVICERECORDS_CALL_OUTS,SALES_ACCOUNTS
  WHERE dbo.JOBCOSTING_JOBS.INVOICE_TO_ACCOUNT = dbo.SALES_ACCOUNTS.INV_TO_ACCOUNT
AND SERVICERECORDS_CALL_OUTS.JOB_PRIORITY <> 'Q'
AND SERVICERECORDS_CALL_OUTS.JOB_PRIORITY <> 'P'
AND SERVICERECORDS_CALL_OUTS.JOB_PRIORITY <> 'X'
AND SERVICERECORDS_CALL_OUTS.ENGINEER<> '*QR'
AND SERVICERECORDS_CALL_OUTS.DATE_LOGGED like '%2018%'
AND dbo.SERVICERECORDS_CALL_OUTS.STATUS = 'C'
AND dbo.SALES_ACCOUNTS.ACCOUNT_NUMBER = dbo.JOBCOSTING_JOBS.JOB_NUMBER
AND dbo.SERVICERECORDS_CALL_OUTS.INVOICE_TO = dbo.SALES_ACCOUNTS.INV_TO_ACCOUNT
AND dbo.JOBCOSTING_JOBS.SITE_ACCOUNT_NUMBER = dbo.SERVICERECORDS_CALL_OUTS.JOB_NUMBER
AND dbo.SERVICERECORDS_CALL_OUTS.JOB_NUMBER = dbo.JOBCOSTING_JOBS.JOB_NUMBER
AND dbo.SERVICERECORDS_CALL_OUTS.SUB_JOB_NUMBER = dbo.JOBCOSTING_JOBS.SUB_JOB_NUMBER
AND dbo.SERVICERECORDS_CALL_OUTS.INVOICE_TO = dbo.JOBCOSTING_JOBS.INVOICE_TO_ACCOUNT
)

I think it's a matter of using EXCEPT ?
Instead of NOT EXISTS()

Simple example:

IF EXISTS ( SELECT 1 FROM sys.tables WHERE name = 'OrderLines')
    DROP TABLE [dbo].[OrderLines];

IF EXISTS ( SELECT 1 FROM sys.tables WHERE name = 'InvoiceLines')
    DROP TABLE [dbo].[InvoiceLines];

-- create tables
CREATE TABLE [dbo].[OrderLines](
    [Id] [INT] IDENTITY(1,1) NOT NULL,
    [JobNumber] [INT] NULL,
    [SubJobNumber] [INT] NULL,
 CONSTRAINT [PK_OrderLines] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[InvoiceLines](
    [Id] [INT] IDENTITY(1,1) NOT NULL,
    [JobNumber] [INT] NULL,
    [SubJobNumber] [INT] NULL,
 CONSTRAINT [PK_InvoiceLines] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)
) ON [PRIMARY]
GO

-- insert sample data
INSERT INTO dbo.OrderLines (Jobnumber, SubJobNumber) values (1,1),(1,2),(1,3),(2,1),(2,2)
INSERT INTO dbo.InvoiceLines (Jobnumber, SubJobNumber) values (1,1),(1,2),(2,1)

-- query result missing jobnumber/subjobnumber records in second table
SELECT JobNumber, SubJobNumber
FROM dbo.OrderLines
EXCEPT
SELECT JobNumber, SubJobNumber
FROM dbo.InvoiceLines

Results:

JobNumber   SubJobNumber
----------- ------------
1           3
2           2

(2 rows affected)

I would work with the key values that make the result set (Jobnumber, Subjobnumber), and then get the other data for those rows.


ORACLE Shifting from from one version to another I have RHEL5 server with Oracle Home actual location is SAN, which is mounted on this server.

Now for few Reasons I have one more Server with RHEL 6 64Bit.

I will map those storage LUNS there.

What are the various ways to migrate and what are the advantages/disadvantages ?

Point 1: Take backup from old system and go for new install no new server ?

Point 2: Directly Map LUN and with few changes I can achieve this ?

Can I go for creating control files and copy and backup on new server and start making changes in spfile and pfile and go for changes ??

Since you upgrade your OS, at some point you need to relink your Oracle software. In that case, easiest is to make a new software installation on the new server. Unmount the luns where the database is located from the old server and mount that on the new server where the database files should be mounted on the same locations as on the old server.

I hope that the ORACLE_HOME is not on the same lun[s] as where the database is located. If ORACLE_HOME does exist on the same LUN[s], make sure to create the new ORACLE_HOME on a different PATH and make sure that config files like /etc/oratab and listener.ora reflect this change.

This should give the quickest migration.
If there is to be done more than just re-mount the LUN[s], make sure to have a capable DBA involved. That will save you a lot of downtime and problems.


Not exists in sql query I'm using oracle database

I need to display customer id and customer name of those customers who have not purchased at all from any retail outlet using not exists

My query:

select customerid,customername
from customer c
where  not exists (
select p.customerid
from purchasebill p,retailoutlet r,customer c
where p.customerid=c.customerid and p.retailoutletid=r.retailoutletid group by p.customerid);

I supposed to get 3 records but i'm getting output as no rows selected, instead of not exists if i use not in, the output is correct.What's wrong in the above code please let me know.

First of all, NOT IN is not the same as NOT EXISTS.

create table customer (customerid number);
create table purchasebill (customerid number, retailoutletid number);
create table retailoutlet (retailoutletid number);

insert into customer values (1);
insert into customer values (2);

insert into purchasebill values (1, 1);
insert into purchasebill values (null, 3);

insert into retailoutlet values (1);
insert into retailoutlet values (3);

commit;

Let's find customers without purchasebill (customerid = 2). FIrst with a naive NOT IN:

SQL> select * from customer c where c.customerid not in (select p.customerid from purchasebill p);

no rows selected

Now with NOT EXISTS:

SQL> select * from customer c where not exists (select 1 from purchasebill p where p.customerid = c.customerid);

CUSTOMERID
----------
         2

NOT IN did not provide the result we expected. It's because the way NOT IN works. If any row of the subquery returns NULL, NOT IN will return FALSE, so no result in this case.

Now your queries:

SQL> select * from customer c where not exists (select p.customerid from customer c, purchasebill p, retailoutlet r where p.customerid = c.customerid and r.retailoutletid = p.retailoutletid);

no rows selected

SQL> select * from customer c where customerid not in (select p.customerid from customer c, purchasebill p, retailoutlet r where p.customerid = c.customerid and r.retailoutletid = p.retailoutletid);

CUSTOMERID
----------
         2

The problem with the NOT EXISTS query is that you have customer c both in the outer query and the subquery. The subquery uses customer c from its inside, not the customer c from the outer query. Because of this, the subquery always returns data, so NOT EXISTS will always evaluate to FALSE, hence no result.

The NOT IN version works, because the inner query is not a correlated subquery, it does not use customer c from the outside, but the inside. The inner query returns customers who made purchases, without NULLs, because the join to purchasebill p eliminates them. With no NULLs to worry anymore, NOT IN works as naively expected.

If you want to make NOT EXISTS work, remove customer c from the subquery, so it becomes a correlated subquery:

SQL> select * from customer c where not exists (select 1 from purchasebill p, retailoutlet r where p.customerid = c.customerid and r.retailoutletid = p.retailoutletid);

CUSTOMERID
----------
         2


Getting Information on active Restore in TSQL / SQL Server Management Studio On my server a database is currently restoring according to the SQL Server Management Studio

In the history

SELECT *
FROM MSDB..RestoreHistory WITH (nolock)
WHERE destination_database_name = 'myDB'
ORDER BY restore_date DESC

I can't see any entry of that restore currently running.

Is there any other system-table where I can find out who started the restore and when?

To see the restore progress, use below t-sql

SELECT command,
            s.text,
            start_time,
            percent_complete,
            CAST(((DATEDIFF(s,start_time,GetDate()))/3600) as varchar) + ' hour(s), '
                  + CAST((DATEDIFF(s,start_time,GetDate())%3600)/60 as varchar) + 'min, '
                  + CAST((DATEDIFF(s,start_time,GetDate())%60) as varchar) + ' sec' as running_time,
            CAST((estimated_completion_time/3600000) as varchar) + ' hour(s), '
                  + CAST((estimated_completion_time %3600000)/60000 as varchar) + 'min, '
                  + CAST((estimated_completion_time %60000)/1000 as varchar) + ' sec' as est_time_to_go,
            dateadd(second,estimated_completion_time/1000, getdate()) as est_completion_time
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE r.command in ('RESTORE DATABASE', 'BACKUP DATABASE', 'RESTORE LOG', 'BACKUP LOG')

  Is there any other system-table where I can find out who started the restore and when?

You can find out the restore history - especially the user_name will give you who restored the database.

SELECT  [rs].[destination_database_name],
[rs].[restore_date],
[bs].[backup_start_date],
[bs].[user_name],
[bs].[backup_finish_date],
[bs].[database_name] as [source_database_name],
[bmf].[physical_device_name] as [backup_file_used_for_restore]
FROM msdb..restorehistory rs
INNER JOIN msdb..backupset bs
ON [rs].[backup_set_id] = [bs].[backup_set_id]
INNER JOIN msdb..backupmediafamily bmf
ON [bs].[media_set_id] = [bmf].[media_set_id]
--where [rs].[destination_database_name] like '%database_name%' -- OPTIONAL FILTER
ORDER BY [rs].[restore_date] DESC


Getting ORA-01017: invalid username/password in non-CDB database I have installed Oracle 12c (software only in Linux) and created a non-CDB database (single tenant) database. Following is my listener.ora.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ora12cl)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
SID_LIST_LISTENER=
(SID_LIST=
  (SID_DESC=
    (SID_NAME=orcl)
    (GLOBAL_DBNAME=orcl)
  )
)

This is my tnsnames.ora

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora12cl)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = orcl)
    )
  )

Here is my initorcl.ora

db_name=orcl
db_block_size=8192
memory_target=300M
memory_max_target=300M
processes=200
job_queue_processes=10
open_cursors=500
fast_start_mttr_target=500
undo_management=AUTO
undo_tablespace=UNDOTBS1

I am able to startup and mount the database successfully. I can connect to it

 [oracle@ora12cl dbs]$ sqlplus sys/***** as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue May 10 23:53:18 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>

But if I try to connect using the instance (as I should from a remote system such as my Windows host):

[oracle@ora12cl dbs]$ sqlplus sys/dolan123 as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue May 10 23:53:18 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@ora12cl dbs]$ sqlplus sys/******@orcl as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue May 10 23:54:47 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

I am getting ORA-01017.

I see no reason why should not able to login using the instance name. Note it's a non-CDB database.

[oracle@ora12cl dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue May 10 23:56:43 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select name, cdb from v$database;

NAME      CDB
--------- ---
ORCL      NO

Can anyone help on this please?

Remote (through listener) SYSDBA authentication requies a passwordfile.

Make sure remote_login_passwordfile is set to EXCLUSIVE or SHARED.
Create the password file at ${ORACLE_HOME}/dbs/orapw${ORACLE_SID} as:

orapwd file=${ORACLE_HOME}/dbs/orapw${ORACLE_SID}

This will prompt for a password, then it will create the password file. After that, you can log in remotely as SYSDBA.

PS:

This version uses OS authentication:

$ sqlplus sys/dolan123 as sysdba

As long as you try connecting locally as SYSDBA with a user that is member of the OSDBA group (defined at install time, typically dba), you can type any password, because you will be authenticated based on your group membership, not the password. Successfully logging in with the above does not necessarily means that the SYS password is dolan123.


Sybase ASE 15.7 sort ordering I have my sort ordering in Sybase as:
45  190 altdict -  Alternate (lower-case first) dictionary ordering
Am I right that alternate means "aAbBcC..zZ" ordering?

What should I do to change ordering with "ORDER BY" as 'ABC..YZabc..yz' ? What sort ordering to choose?

Where may it be changed? In a session? In SQL statement?
as far as I know I need to change the whole server sort ordering and rebuilt indexes. But there are a bunch of databases with different owners and different users and it seems a bit extreme

The ASE dataserver is configured with a single sort order; see selecting default sort order.

For scenarios where the user needs to use a sort order different from the dataserver's default sort order there is:

sortkey() - allows for sorting data by a user-selected sort order


Copy table with separate sequences for serial columns While creating a column management tool I came across the need to quickly replicate tables in PostgreSQL so I would not test new tools with non-test tables. To effectively test the new column tool that I ultimately intend to use on the table parts I created this new tool to duplicate parts so I'd end up with a parts1 table. When I thought I had finally gotten all the issues ironed out I encountered the following error when the column tool deletes the table:

  ERROR: cannot drop table parts because other objects depend on it
  DETAIL: default for table parts1 column id depends on sequence
  parts_id_seq1

I've spent the greater part of my day working towards this solution so in short can I simply use string functions to rename the SEQUENCE_NAME variable to disassociate the parts table from the parts table or is it a more convoluted issue than that? Here is the query:

DO $$
  DECLARE
    SEQUENCE_NAME VARCHAR;
  BEGIN
    SELECT s.relname INTO SEQUENCE_NAME
    FROM pg_class AS s JOIN pg_depend d ON d.objid = s.oid
    INNER JOIN pg_class AS t ON d.objid = s.oid AND d.refobjid = t.oid
    INNER JOIN pg_attribute AS a ON (d.refobjid, d.refobjsubid) = (a.attrelid, a.attnum)
    INNER JOIN pg_namespace AS n ON n.oid = s.relnamespace
    WHERE s.relkind = 'S'
    AND n.nspname = 'public'
    AND t.relname='parts';

    LOCK TABLE parts;
    CREATE TABLE parts1 (LIKE parts INCLUDING ALL);
    INSERT INTO parts1 SELECT * FROM parts;
    PERFORM setval(SEQUENCE_NAME::regclass, (SELECT max(id) FROM parts)+1);
  END;
$$ LANGUAGE plpgsql;

To create a copy as close as possible you must use INCLUDING ALL with CREATE TABLE .. (LIKE ..) since there can be any number of columns with defaults that you obviously want to copy.
You just want serial columns to get their own, independent sequence, which makes a lot of sense and probably should be the default behavior. For now, this should do the job:
Function to copy any given table
Copies any given table (must exist) with new given name and independent serial columns (if any).
Data is not included, it's trivial to copy that, too.
CREATE OR REPLACE FUNCTION f_copy_tbl(_tbl regclass, _newtbl text)
  RETURNS void AS
$func$
DECLARE
   _sql text;
BEGIN

-- Copy table
EXECUTE format('CREATE TABLE %I (LIKE %s INCLUDING ALL);', _newtbl, _tbl);

-- Fix serial columns, if any
SELECT INTO _sql
       string_agg('CREATE SEQUENCE ' || seq, E';\n') || E';\n'
    || string_agg(format('ALTER SEQUENCE %s OWNED BY %I.%I'
                        , seq, _newtbl, a.attname), E';\n') || E';\n'
    || 'ALTER TABLE ' || quote_ident(_newtbl) || E'\n  '
    || string_agg(format($$ALTER %I SET DEFAULT nextval('%s'::regclass)$$
                                 , a.attname, seq), E'\n, ')
FROM   pg_attribute  a
JOIN   pg_attrdef    ad ON ad.adrelid = a.attrelid
                       AND ad.adnum   = a.attnum
     , quote_ident(_newtbl || '_' || a.attname || '_seq') AS seq  -- new seq name
WHERE  a.attrelid = _tbl
AND    a.attnum > 0
AND    NOT a.attisdropped
AND    a.atttypid = ANY ('{int,int8,int2}'::regtype[])
AND    ad.adsrc = 'nextval('''
         || (pg_get_serial_sequence (a.attrelid::regclass::text, a.attname))::regclass
         || '''::regclass)'
;

IF _sql IS NOT NULL THEN
   EXECUTE _sql;
END IF;

END
$func$  LANGUAGE plpgsql VOLATILE;

Call:
SELECT f_copy_tbl('tbl', 'tbl1');

Produces and executes SQL code of the form:
CREATE TABLE tbl1 (LIKE tbl INCLUDING ALL);
-- only if there are serial columns:
CREATE SEQUENCE tbl1_tbl_id_seq;     -- one line per serial type ..
CREATE SEQUENCE "tbl1_Odd_COL_seq";  -- .. two in this example
ALTER SEQUENCE tbl1_tbl_id_seq OWNED BY tbl1.tbl_id;
ALTER SEQUENCE "tbl1_Odd_COL_seq" OWNED BY tbl1."Odd_COL";
ALTER TABLE tbl1
  ALTER tbl_id SET DEFAULT nextval('tbl1_tbl_id_seq'::regclass)
, ALTER "Odd_COL" SET DEFAULT nextval('"tbl1_Odd_COL_seq"'::regclass);

Only serial columns get their own sequence. Other column default are copied unchanged - including nextval() from a sequence that is not owned by the column or differs in any way from a serial.

The function is safe against SQL injection and should work with arbitrary table and column names.

SQL Fiddle.
It would be a useful option for the LIKE feature to create separate sequences for serial columns. You may want to post a friendly feature request to pgsql-general.


Allocation Size for Backup disk on SQL Server What would be the optimal allocation size for a backup disk for SQL Server? We have a 2014 instance which was using Veeam backups till recently, but we decided to switch to local backups again for some reasons. Does the 64k size hold good for backups as well? Please advice

Backups are written sequentially, and as such they are far less sensitive to allocation unit and sector sizes.

64K should be no problem at all.


Caching queries that last longer then then X seconds Let's assume that I want work with query cache enabled. For example let's set it to 128mb value but my working enviorment is very random and dynamic, so cache is full in 1 minute after setting it.

When checking

SHOW STATUS LIKE "qcache%";

Qcache_lowmem_prunes value is growing dangerously high because of high ammount of random queries.

So let's go the point of this question:
How to cache only that queries that are taking longer then ie: 1 sec?

Is it even possible with query cache enabled or is it better to turn it off for good in such dynamic enviorment? Are there any different aproaches to cache those long queries?

Let's assume that 1 second for query is achieved due to low budget hardware, not from bad query construction.

Instead, cache only selected queries.  Go through your code and decide which queries are likely to be exactly repeatedly.  On those do SELECT SQL_CACHE ....  On the others do SELECT SQL_NO_CACHE ....
As for the long queries -- improve them.  See this for getting started.  When you have identified a couple of 'slow' queries, open a new Question to get help on making them faster.  Sometimes we can help you make them run 10 times as fast.


Version lie shim for SQL Server 2014 SQL Server 2014 is sufficiently new that not a lot of the 3rd party tooling has caught up with it yet. I'm looking at Red Gate and TOAD in particular...

Is there any way of putting a version lie shim in place on the server until these tools have released newer versions?

EDIT: 2014-05-29 16:00 IST

In response to the hold I'd like to emphasise that this question is about getting the server to lie about its version so that all tools will which have worked to date should continue to work.

Even though two particular vendors have been mentioned this is just by way of example and it should be stated that I'm not looking for a fix for the particular issue shown in the screenshot - that's just there to illustrate my point.

Nope.  You'll have to hold off on Monitoring with the 3rd party tools until they release new builds which support the new versions.  The good news is that they are all smallish companies so they can release new builds pretty quickly.  I'd recommend contacting their support departments and see if there's a new build available, maybe even a BETA that you can try out.

I feel you're pain, I've got a client with SQL 2014 in production and Spotlight can't monitor it for them.


SSMS (2012) limit # of characters on editor window line Is there a way to enforce a hard limit on the number of characters allowed on a line in the SSMS editor window? I have searched, but am unable to find an option to set this.

I'd like to force the line to wrap at 100 characters if possible.

Alternately, is there a way to set a visible cue at 100 characters (similar to Vim's colorcolumn setting)?

Thanks.

SSMS isn't as full-featured as some other editors out there when it comes to visual formatting. I think the closest you'll get is using word wrap from Tools -> Options  and then manually sizing your SSMS window to  force it to a smaller column width.

The bottom row of SSMS will tell you what column number your cursor is on, which may be helpful to size your window to approximately 100 columns wide.


Database Architecture Identifiers I am kind of new around DBA world, i am currently working with JMeter(front/back end performance testing) but when i have spare time (which is a lot lately..) i try to learn about DBA Development, also my company is looking for a DBA dev so there is that.

Also this question comes as a follow up to  Queries from different tables locking each other (LCK_M_X) .

This DB has a lot of Unique Identifiers linked with FKs, (Tid,Did,Cid,id) and a few more not so big, every time i make a transaction its inserted in "DidRegistry" table.

                  DidRegistry

As you can see from "CreationMoment" column, This table gets lots of inserts.. i wanted to change Datetime to Datetime(2)7, what do you think?

Now this part i am not sure about, when i put load on the DB, sp_whoisactive never shows the locks in Didregistry, but in different tables and in different queries (see link on top), those queries are inside transactions, the transactions come from the Developers code, with SQLProfiler i didn't find a way to see what queries belong to what transactions.

I am at a loss if i am chasing the wrong rabbit hole or if this could be a Design problem for future bigger clients with more load profiles.

To capture client API transactions, add the TM: Begin Tran completed, TM: Commit Tran completed, and TM: Rollback Tran completed events in the trace along with the TransactionID column. Queries with the same TransactionID are part of the same transaction.

Note that Profiler is deprecated so I suggest you move to Extended Event tracing. The events are similarly named.

Use datetime2(7) to avoid loss of fractional second accuracy.


Oracle - Session still holding the locks We are using MULE(Anypoint Studio) as the ESB(Enterprise Service Bus) layer.
We are using bitronix transaction manager.
Database connection to ORACLE are made inside the transaction through EJB running on JBOSS.

We have situation in which the MULE server will be stopped without the transaction being COMMITTED OR ROLLED BACK.

When i restart the MULE server, new ejb connections are being made and the old sessions are not getting timed out.

When i query the db using :

select
   sid,
   serial#
from
   v$session
where
   sid in (
   select
      session_id
   from
      dba_dml_locks
   where
      name = '<table_name>');

I can see that two sessions are still active and holding the locks to the table.
When will the locks on the table be released.
Should i set any property in JBOSS or MULE or ORACLE for this timeout?
I can also see that manually killing those sessions, releases the locks. But this is not possible in the production environment. Wont the sessions be timed out automatically?

I can see that changing IDLE_TIME of the profile in ORACLE releases the locks after the given time.

ALTER PROFILE DEFAULT LIMIT IDLE_TIME <time>;

But the client would not accept this, because it affects the complete profile.
Is there any way to timeout only the idle sessions of a particular table?

My suggestion will be to add timeout if response is taking time, and if any thing fails use until successful of mule.


How to retrieve specific characters within rows of database column? Which Oracle SQL query is preferred or ideal to return the second, third and fourth positioned characters within rows of a particular column? A SQL statement using the SUBSTR method or some type of REGEX?

The following is the query I am trying to use:

SELECT REGEXP_SUBSTR(city)
FROM student.zip;

I would appreciate any recommendations-thanks!

Your question's not brilliantly clear, but if you want the 2nd, 3rd and 4th characters in the column city, in the table zip, then use SUBSTR:

SELECT SUBSTR(city,2,3)
FROM student.zip;

REGEXP_INSTR returns the location of a match, not a string, so you don't want to use that. REGEXP_MATCH would be the one to use, but SUBSTR is simpler.


Alternatives to Network Backup In our environment we have some servers that are in an Always On Availability Group, and some that are standalone.

We normally backup to a network share, but we have recently observed that as the databases are growing bigger the time taken is getting longer, which slows down the whole network.

Ola hallengren's script is being used with compression and also splitting the backup files. I am only performing daily "full" backups. The backups are going to the network share EMC isilon drive.

I am never comfortable with EMC DD Boost. The only alternative is to do a local backup and then copy to the same network share.

Is there an efficient way other than the above?

There are ways to tune backups by messing with different knobs like MAXTRANSFERSIZE or BUFFERCOUNT, or striping the file (which you've noted you're already doing).

The problem is that touching those knobs may still result in hitting the limits of your network and/or storage, and them not having any real impact on backup time.

Your first job should be to benchmark the storage you're backup up to using Crystal Disk Mark or DiskSpd. That'll give you some idea of how fast you can expect writes to be at their best.

The next thing you need to test is reads from the drives you're backing up from. If you run a backup to NUL, you can time how long it takes just the read portion of your backup, without having to write it to disk.

With both those numbers in mind, you can start messing with other knobs to see which ones get you closest to them, regardless of if your backup target is local or networked.


Error 'Unknown table engine 'InnoDB'' on query. after restarting mysql I have mysql DB on server S1 (mysql version 5.1.41-3ubuntu12.7-log).

I have created master-slave for this DB on server S2 (mysql version 5.1.54-1ubuntu4-log).

The DB on S1 was using one data file (ibdata).

After dumping the DB to S2, I set innodb_file_per_table=1. This made every table to have its own ibd file. now everything went fine and smoothly.

After restarting mysql on S2, I faced a problem with getting this error:
Error 'Unknown table engine 'InnoDB'' on query. Default database: MyDB
and when I try to show engines, I get the following:

show engines;
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine     | Support | Comment                                                        | Transactions | XA   | Savepoints |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         | NO           | NO   | NO         |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE  | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV        | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| FEDERATED  | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| ARCHIVE    | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+------------+---------+----------------------------------------------------------------+--------------+------+------------+

InnoDB is not listed.

In error log, I can see this:

InnoDB: Database physically writes the file full: wait...
InnoDB: Cannot initialize created log files because
InnoDB: data files are corrupt, or new data files were
InnoDB: created when the database was started previous
InnoDB: time but the database was not shut down
InnoDB: normally after that.
111016  8:24:11 [ERROR] Plugin 'InnoDB' init function returned error.
111016  8:24:11 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
111016  8:24:11 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=S2-relay-bin' to avoid this problem.

I have tried to delete ib_logfiles but this didn't work as well.
if i delete ib_logfiles and ibdata file, innodb will return back normally, but i can't access my innodb tables, ie. after deleting ibdata1 and restarting mysql

desc article;
ERROR 1146 (42S02): Table 'MyDb.article' doesn't exist

My innodb configuration in my.cnf is as follows:

innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_buffer_pool_size = 384M
innodb_log_file_size=5M
innodb_lock_wait_timeout = 18000

although the table is there!!

Has anybody faced such issue before??

Any idea is highly appreciated

Thanks

I have very bad news for you.
You should not have deleted the ibdata1 file. Here is why:
ibdata1 contains four type of information:

table metadata
MVCC data
data pages (with innodb_file_per_table enabled)
index pages (with innodb_file_per_table enabled)

Each InnoDB table created has a numercial id assigned to it via some auto increment metadata feature to each ibd file. That internal tablespace id (ITSID) is embedded in the .ibd file. That number is checked against the list of ITSIDs maintained, guess where, ... ibdata1.
I also have very good news for you along with some bad news.
It is possible to reconstruct ibdata1 to have the correct ITSIDs but it takes work to do it. While I personally have not done procedure alone, I assisted a client at my employer's web hosting to do this. We figured this out together but since the client hosed ibdata1, I let him do most of the work (30 InnoDB tables).
Anyway, here a past post I made in the DBA StackExchange. I answered another question whose root cause was the mixing up of ITSIDs.
To cut right to the chase, here is the article explaining what to do with reference to ITSID and how to massage ibdata1 into acknowledging the presence of the ITSID contained within the .ibd file.
I am sorry there is no quick-and-dirty method for recovering the .ibd file other than playing games with ITSIDs.
UPDATE 2011-10-17 06:19 EDT
Here is your original innodb configuration from your question:
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_buffer_pool_size = 384M
innodb_log_file_size=5M
innodb_lock_wait_timeout = 18000

Please notice that innodb_log_file_size is there twice. Look carefully...
innodb_file_per_table=1
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G <----
innodb_buffer_pool_size=4G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_buffer_pool_size = 384M
innodb_log_file_size=5M <----
innodb_lock_wait_timeout = 18000

The last setting of innodb_log_file_size takes precedence. MySQL expected to start up with the log files being 5M. Your ib_logfile0 and ib_logfile1 were 1G when you tried to start up mysqld. It saw a size conflict and took the path of least resistance, which was to disable InnoDB. That's why InnoDB was missing from show engines;. Mystery solved !!!
UPDATE 2011-10-17 11:07 EDT
The error message was deceptive because innodb_log_file_size was smaller than the log files (ib_logfile0 and ib_logfile1), which were 1G at the time. What's interesting is this: Corruption was reported because the file was expected to be 5M and the files were bigger. If the situation were reversed and the innodb log files were smaller than the declared size in my.cnf you should get something like this in the error log:
110216 9:48:41 InnoDB: Initializing buffer pool, size = 128.0M
110216 9:48:41 InnoDB: Completed initialization of buffer pool
InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes
InnoDB: than specified in the .cnf file 0 33554432 bytes!
110216 9:48:41 [ERROR] Plugin 'InnoDB' init function returned error.
110216 9:48:41 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

In this example, the log files were already existing as 5M and the setting for innodb_log_file_size was bigger (in this case, 32M).
For this particular question, I blame MySQL (eh Oracle [still hate saying it]) for the inconsistent error message protocol.


Implementing voting system like reddit/StackExchange? In creating an application that would require a voting system similar to that of StackExchange or reddit, I want to know how the database tables should optimally be structured.

So far, I have something planned out like this:

  Vote(UserID[PK], ContentID[PK], IsLike)

I had planned that the UserID and ContentID(the entity being "liked") would be a composite primary key. This would make it so that I can tell which users have already "liked" certain content. The IsLike field would determine whether the like/vote was positive or negative(like or dislike).

Also, could the composite primary keys be foreign keys linking to their respective tables (UserID --> User table, ContentID --> Content table) or is there something else I would have to do to make sure this would work?

Would this be an adequate way to store information about which users have liked certain content? Any suggestions welcome.

What you have seems pretty solid.  How about

create table vote (
   user_id int not null,
   content_id int not null,
   time_recorded datetime not null,
   vote int not null,
   primary key (user_id, content_id),
   foreign key (user_id) references `user`(user_id)
      on delete cascade,
   foreign key (content_id) references content(content_id)
      on delete cascade,
   check (vote = 1 or vote = -1)
);

A composite primary key with those two keys makes perfect sense.  They absolutely should be foreign keys as well.  I threw in to cascade a delete from the parent tables; if a user deletes, the "like" will be removed from the content (which, IIRC, is what happens in StackExchange), and if the content is removed, there is no real point in keeping the votes on it.  I also implemented the vote column as an integer which must be either 1 or -1, rather than a boolean.  That way you can get an overall tally of up and down votes with a simple sum(vote), rather than two select count(*).


SQL Server detected a logical consistency-based I/O error - tempdb We started receiving these error messages suddenly

  Error: 824, Severity: 24, State: 2.

  Message SQL Server detected a logical consistency-based I/O error:
  invalid protection option. It occurred during a read of page (7:8192)
  in database ID 2 at offset 0x00000004000000 in file
  'X:\tempdb\tempdb_mssql_6.ndf'.  Additional messages in the SQL Server
  error log or system event log may provide more detail. This is a
  severe error condition that threatens database integrity and must be
  corrected immediately. Complete a full database consistency check
  (DBCC CHECKDB). This error can be caused by many factors; for more
  information, see SQL Server Books Online.

  SQL Server detected a logical consistency-based I/O error: incorrect
  checksum (expected: 0x31333637; actual: 0x9c88f38a). It occurred
  during a read of page (7:8216) in database ID 2 at offset
  0x00000004030000 in file 'X:tempdb\tempdb_mssql_6.ndf'.  Additional
  messages in the SQL Server error log or system event log may provide
  more detail. This is a severe error condition that threatens database
  integrity and must be corrected immediately. Complete a full database
  consistency check (DBCC CHECKDB). This error can be caused by many
  factors; for more information, see SQL Server Books Online.

  An error occurred while processing the log for database 'tempdb'.  If
  possible, restore from backup. If a backup is not available, it might
  be necessary to rebuild the log. During undoing of a logged operation
  in database 'tempdb', an error occurred at log record ID
  (239:1702646:104). Typically, the specific failure is logged
  previously as an error in the Windows Event Log service. Restore the
  database or file from a backup, or repair the database.

  SQL Server must shut down in order to recover a database (database ID
  2). The database is either a user database that could not be shut down
  or a system database. Restart SQL Server. If the database fails to
  recover after another startup, repair or restore the database.

After these messages, I had to start SQL services manually, however the errors continued

  Error while allocating extent for a worktable.  Extent (6:16248) in
  TEMPDB may have been lost.

I ran DBCC CHECKDB(tempdb)  --- no results.....

Checked with storage team for health checks, they say no issue.

However, I see many suspect_pages recorded for this DB and it even got recorded before the server crash too.

then I had to manually delete the tempdb files and restart SQL services. Errors stopped and DB health is OK.

We are on latest version - Microsoft SQL Server 2016 (SP2-CU8) (KB4505830) - 13.0.5426.0 (X64)   Standard Edition (64-bit) ...and I did this upgrade a week ago...it was running fine and suddenly it showed this behavior..and also Rebuild-Reorg maintenance job is running at that time...Storage admins denied any issues from their side...

Trying to investigate the root cause, any pointers/advise is really appreciable. Thanks in advance.

normally,824 means that there should be some hardware related issues ...Since tempdb is geting corrupt again and again , i would suggest you to involve hardware team\vendor and get your disks analyzed ...also check the application\system logs and see if there are disk related errors there...

Insufficient storage space on disk can cause error code 824
Hardware device or driver software which acts as a medium may cause errors
File system inconsistency can also be reason for SQL server logical consistency error
Damage caused to the database files may be a prime cause for the problem as well
Corruption of the file system can also give rise to SQL fatal error 824

Use the SQLIOSim utility to find out if these 824 errors can be reproduced outside of regular SQL Server I/O requests.


Is there anyway to search PL/SQL reserved words with contains function? Is there anyway to search PL/SQL reserved words with the contains function in the Oracle?

We would like to search keywords such as "test for" or "test do" with the contains or catsearch functions from the user table in Oracle but the results only show for "test" and ignore those reserved words such as "do" or "for" automatically.

We assume we have a table called content in the users schema.

The queries are listed below:

create table users.content (content_id int not null, content varchar(20) not null);

insert into users.content values (1, 'do something');

insert into users.content values (2, 'test it');

insert into users.content values (3, 'it has been tested');

insert into users.content values (4, 'will test it');

create index users.idx_content on users.content (content) indextype is ctxsys.context;

--drop index users.idx_content;

--drop table users.content;

select content_id, content from users.content where contains(content, 'do or test') > 0;

You can't use contains because there is no Oracle Text index on the database source.  You could, I suppose, write a query that copied the data from dba_source to a custom table, create an Oracle Text index on that table, and search that table using the contains function.

It would generally make more sense, though, to just query dba_source with a like query

SELECT *
  FROM dba_source
 WHERE lower( text ) like '%for %';


EXECUTE within function not using index? I have an accounts table with ~200k rows and an index for these columns:

  account_type_id BIGINT
, member_id BIGINT
, external_id VARCHAR(64)

CREATE INDEX account_full_qualifiers_idx
ON normal_object.account (account_type_id, member_id, external_id) TABLESPACE index_tbsp;

And I have a function that is performing some ETL work with the following query:

EXECUTE '
   SELECT * FROM normal_object.account
   WHERE account_type_id = $1
   AND member_id = $2
   AND external_id = $3'
INTO e_row
USING r_row.account_type_id, r_row.member_id, r_row.external_id;

Yet, the EXECUTE command is NOT using the index and I'm not sure why. My only guess is that the data types are not lining up. Yet r_row.account_type_id is a BIGINT, r_row.member_id is a BIGINT, and r_row.external_id is a VARCHAR(64).

Any suggestions as to why it is not using the index?
How can I get it to use the index? (I've already tried setting enable_seqscan off.)

Queries executed with EXECUTE are re-planned with the actual parameter values passed to it every time. Since you are using Postgres 9.2, you may not even need EXECUTE:

PostgreSQL Stored Procedure Performance

Either way, upgrading to Postgres 9.3 (or the upcoming 9.4) might help some more.

So, unless we have a type mismatch (like you already suspected) or a collation mismatch, your index should be used, if the parameter values are selective enough (retrieving less than ~ 5% of the table), which most probably is the case.

You write:

  r_row.account_type_id is a BIGINT, r_row.member_id is a BIGINT, and
  r_row.external_id is a VARCHAR(64).

But how do you know that? If r_row is declared as record, data types of columns are defined in the assignment of an actual row ...
We need to see the complete plpgsql function with header and footer. And the exact table definition.

COLLATE "C" for your char-type index

An index on an integer type column is far more efficient than one on varchar(64) in any case. If you have to use the character type but you don't need to sort the column according to your collation setting (as I presume) it would be more efficient to use COLLATE "C" for index and query.

CREATE INDEX account_full_qualifiers_idx
ON normal_object.account (account_type_id, member_id, external_id COLLATE "C")
TABLESPACE index_tbsp;

Your query would then be:

EXECUTE '
   SELECT *
   FROM   normal_object.account
   WHERE  account_type_id = $1
   AND    member_id = $2
   AND    external_id COLLATE "C" = $3
   LIMIT  1'
INTO  e_row
USING r_row.account_type_id, r_row.member_id, r_row.external_id;

LIMIT 1 may or may not be needed. Your index is not UNIQUE, though ...

Or you define the table column with COLLATE "C" to begin with. Index and query will default to the collation of the column.

This may or may not solve the question at hand - it's a good idea for your situation in any case.

Details in this related answer (chapters "Strings and collation" and "Index":

How do I efficiently get "the most recent corresponding row"?
The manual on "Collation Support".


Install MariaDB with custom my.ini file I have an installation script for Windows to install mariaDB as service:

   mysql_install_db --datadir=c:/mariadb/data --service=MariaDB

It works, but I want to change the default my.ini file adding innodb_file_per_table=0 setting before default table creation.
How can I change the my.ini before default db creation?

There is no urgency of getting that setting made.  After the install is finished,

Locate my.cnf
my.cnf may have some "includes" at the end.  One of those places is the 'proper' place to do the next step.
Add any extra settings at the end of the [mysqld] section of the file.
Restart mysqld by restarting the MariaDB 'service'.

You can also set that without restarting, but the setting would be lost at the next restart:

Connect to MariaDB as 'root'.  (I like to use the mysql commandline tool, but there are other ways.)
SET GLOBAL innodb_file_per_table=0;

Now any CREATE TABLE will put the data and indexes in ibdata1.

Note:  To move a table in ibdata1 from or to its own .ibd file (tablespace), do the SET and do ALTER TABLE name ENGINE=InnoDB;


Hash keys probe and residual Say, we have a query like this:

select a.*,b.*
from
a join b
on a.col1=b.col1
and len(a.col1)=10

Assuming the above query uses a Hash Join and has a residual, the probe key will be col1 and the residual will be len(a.col1)=10.

But while going through another example, I could see both the probe and the residual to be the same column. Below is an elaboration on what I am trying to say:

Query:

select *
from T1 join T2 on T1.a = T2.a

Execution plan, with probe and residual highlighted:

Test data:

create table T1 (a int, b int, x char(200))
create table T2 (a int, b int, x char(200))

set nocount on
declare @i int
set @i = 0
while @i < 1000
  begin
      insert T1 values (@i * 2, @i * 5, @i)
    set @i = @i + 1
  end

declare @i int
set @i = 0
while @i < 10000
  begin
    insert T2 values (@i * 3, @i * 7, @i)
    set @i = @i + 1
  end

Question:

How can a probe and a residual be the same column? Why can't SQL Server use the probe column only? Why does it have to use the same column as a residual to filter rows again?

References for test data:

Hash Join (Craig Freedman's SQL Server Blog)

If the join is on a single column typed as tinyint, smallint, or integer* and if both columns are constrained to be NOT NULL, the hash function is 'perfect' – meaning there is no chance of a hash collision, and the query processor does not have to check the values again to ensure they really match.

Otherwise, you will see a residual as items in the hash bucket are tested for a match, not just a hash function match.

Your test does not specify NULL or NOT NULL for the columns (a bad practice, by the way), so it appears you are using a database where NULL is the default.

More information in my post Join Performance, Implicit Conversions, and Residuals and Hash Join Execution Internals by Dmitry Pilugin.

* Other qualifying types are bit, smalldatetime, smallmoney, and (var)char(n) for n = 1 and binary collation


Understanding datetime formats in SQL Server I've dealt with MS SQL Server datetime types for a long time but never thought why the following is happening:

I query a table that contains a smalldatetime column. This smalldatetime is always returned in the format yyyy-MM-dd hh:mm:ss
Now I write a different query on which I want to apply a smalldatetime filter in the WHERE clause, something like WHERE TimeStamp >= 'yyyy-MM-dd hh:mm:ss'
SQL Server retrieves an error and tells me that was not possible to convert that nvarchar to a valid smalldatetime

It appears that it only works if I change the specified format and I write it using the european format, like WHERE TimeStamp >= 'dd-MM-yyyy hh:mm:ss'.
Why is SQL Server showing me the dates in a format that is not covertable or valid when applied back to itself?
I don't have any problem in changing the date format when writing queries, but I want to play with these dates at an application level (Java-JDBC app) and I don't want to be applying date format changes all the time...
Could anyone explain me why this is happening and if there is any way to solve it at a DB level?
Thanks!!

Edit: Please see the screenshot of the error in Management Studio below.

The only truly safe formats for DATETIME/SMALLDATETIME in SQL Server are:

yyyyMMdd
yyyyMMdd hh:nn:ss[.mmmmmmm]
yyyy-MM-ddThh:nn:ss[.mmmmmmm]
----------^ yes, that T is important!

Anything else is subject to incorrect interpretation by SQL Server, Windows, the provider, the application code, end users, etc. For example, the following always breaks:*

SET LANGUAGE FRENCH;
SELECT CONVERT(DATETIME, '2013-11-13');

Result:

  Le paramètre de langue est passé à Français.
  Msg 242, Level 16, State 3, Line 2
  La conversion d'un type de données varchar en type de données datetime a créé une valeur hors limites.

Just changing the language (which any of your user sessions can do) forced SQL Server to interpret that as YYYY-DD-MM instead of YYYY-MM-DD. Similar things can happen with setting like DATEFORMAT. But these settings are literally ignored when using the above two formats.

Always, always, always use one of the above two formats. If you are passing a variable as a string, stop doing that. If you can't, check to make sure it passes ISDATE() first. If you are letting people type any date string into a form field, stop doing that, too. Use a date-picker or calendar control and dictate the format of the string before you pass it to SQL Server. Well, depending on the language, just keep it as a datetime value and don't convert it to a string at all.

I can't reproduce your problem at all, btw...

Please read this post:

Bad Habits to Kick : Mis-handling date/range queries

There is an exception: SELECT CONVERT(DATE, 'yyyy-mm-dd'); will not break. But I err on the side of consistency rather than using a format only in the one place where I know it doesn't break, and having to use a safer format everywhere else.


Querying a many to many relationship in access I have just created a many to many relationship in MS Access with three tables as shown below

AE Career Fair - Struc contains a list of companies where the company name is distinct.

Office Locations - Int is a junction table containing in one column all the companies occuring in AE Career Fair - Struc, but duplicated based on the countries in which they occur.

Countries contains a list of all the countries in the world.

I was able to run a query for companies occuring in one particular country of interest. I would like to take this a step further and query this relationship and determine which companies are in more than one country. I cant really think of a way to do this based on how I currently have it set up. Could anyone provide indications as to how this would be done? Thanks.

I didn't really do a lot with MS Access, but you can try something like this, assuming that company.id is primary key and country.id is primary key and the corresponding tables are related to locations through company_id and country_id, respectively:

SELECT company.name, country.name
FROM locations
LEFT JOIN company
ON company.id = locations.company_id
LEFT JOIN country
ON country.id = locations.country_id
GROUP BY company.name
HAVING count(locations.country_id) > 1


MySQL join for two queries I have the following two queries...

mysql> select id, title from events;
+----+-----------------------------------------------------------------------------------------------+
| id | title                                                                                |
+----+-----------------------------------------------------------------------------------------------+
|  2 | Second planning meeting for Musta                                           |
|  3 | Writing for Online Audiences                                                                  |
|  4 | Beginning with the Wintas Header - Aug 2010 |
|  5 | Sausage Monthly Meetup                                                              |
|  6 | Third planning meeting for Musta                                            |
+----+-----------------------------------------------------------------------------------------------+

mysql> select event_id, count(*) as num_bookmarks from event_connections where connectiontype = 3 group by event_id;
+----------+---------------+
| event_id | num_bookmarks |
+----------+---------------+
|        2 |             1 |
|        3 |             2 |
|        4 |             4 |
|        5 |             8 |
|        6 |             1 |
+----------+---------------+
5 rows in set (0.01 sec)

The 2nd query shows me a total of bookmarks (connectiontype 3) for each event_id. I would like a single query that will show me an event in addition to the total number of bookmarks.

My best effort was...

mysql> select event_id, count(*) as num_bookmarks from event_connections where connectiontype = 3 join event_id on events.id=event_connections.event_id group by event_id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'join event_id on events.id=event_connections.event_id group by event_id' at line 1

The end result would look like be the output from the first query; select id, title from events in addition to a a num_bookmarks column that has a count of bookmarks for that event.

Thanks in advance!

YOUR ORIGINAL QUERY

select event_id, count(*) as num_bookmarks from event_connections
where connectiontype = 3 join event_id on events.id=event_connections.event_id group by event_id;

PROPER SYNTAX

select event_id, count(*) as num_bookmarks
from event_connections join events on events.id=event_connections.event_id
where event_connections.connectiontype = 3
group by event_id;

You just had the WHERE in the wrong place

SUGGESTIONS

If all you care about is the event_id and the counts, do this

select event_id, count(*) as num_bookmarks
from event_connections
where connectiontype = 3
group by event_id;

No need to join.

If you want the event title, then you must join

select events.title, count(*) as num_bookmarks
from event_connections join events on events.id=event_connections.event_id
where event_connections.connectiontype = 3
group by events.title;

GIVE IT A TRY !!!


Task Management Tracker I have this as my proposed structure for task management module I had been working on. I think I can pull off viewing projects I create with the above schema, however I do want to add the ability to assign tasks to other user accounts[table has user_id as PK]. I am seeking some advice as to how to move forward from here to add that ability to assign tasks to users.

There will be a many-to-many relationship between tasks and users, so you will need a table, perhaps ASSIGNED_TASK, that will have FK to the user and task tables. You will likely have some attributes, e.g. user role (task lead, minion, etc), due date, etc.
I recommend that you make your status columns be a string. Using opaque integers is nice from a computer science perspective, but humans find them annoying.
The updated column can be problematic in its meaning. Often the updated date is actually a property of the event (task completion, change in requirements, etc). I recommend that updated be removed from project and task and you track the date of the action in a child table.
Finally, I recommend that your table names be singular not plural. The name of the table should reflect what is being stored in a row and it is independent of whether the table has 0, 1, or a million rows.


Rounding Down with ROUND() function I am wanting to round down these numbers, and I have tried several different ROUND() functions but can not get it exactly right.  Take this sample DDL

Create Table #Fun
(
  value1 float
)

Insert Into #Fun Values
(1002313), (1888222), (1666222), (1222333)

Select
ROUND([value1], -5) As Rounded
FROM #Fun

Drop Table #Fun

In the below the left column is what SQL produces and the right is what I want it to be.  What must be altered in SQL in order to provide the output I am requesting?

1000000 = is fine
1900000 = 2000000
1700000 = 1500000
1200000 = 1000000

It actually looks like you're trying to get the nearest 500000.

Look here: https://stackoverflow.com/questions/1702080/round-to-nearest-5-in-sql-server

In your case, use ROUND(value1/500000.0,0)*500000


Maintenance plan for creating transaction log backups I have a database that is set to the 'Full' recovery model, and I'm creating a maintenance plan to backup the Transaction Log every 15 minutes. A full backup is then taken once every 24 hours.

I was wondering: will the maintenance plan for the transaction logs create a lot of different files every 15 minutes, or will it by default append to the same file for 24 hours?

Also: what is preferred? I couldn't find anything on this subject (or maybe I was Googling the wrong words).

Thanks for any pointers anyone can give me.

EDIT: I also see that the checkmark "Verify backup integrity" is OFF by default. Is there a risk with turning it on? It seems to be that it should be on by default, so is there a reason for not using this?

You might want to read up on the concept of Transaction Log Backups in the following Microsoft Technet Article: SQL Server - Understanding SQL Server Backups

If you set up a maintenance plan that creates a transaction log backup of all your user databases, then the backup job will create a *.TRN (default extension) file for each database and for each scheduled run.

Depending on the retention period you set in your maintenance plan, you will/can have literally thousands of transaction log backup files (*.trn) in your backup location.

E.g. Backing up a 10 user databases every 15 minutes and leaving the retention period for clean-up to 30 days, will leave you with 28'800 transaction log backup files lying around.

10 db * 1 trn/db/15min * 1440 min/d * 30d = 28'800 trn files

db = database
trn = trn backup file
min = minutes
d = day

So depending on the size of the databases and the amount of changing data, you could end up needing quite a lot of disk space for your transcation log backups. Then again, having a short retention period in your maintenance plan could result in lower disk space requirements.

Pssst, after reading the Microsoft article, have a look at some alternative backup solutions, like Ola's Maintenance script or other alternative solutions.


Building distinct Column from a common column from two tables Good afternoon team,

hope all is well

I am hoping you can help me.

I have following two tables "GODS.DQ_TOBE" & "GODS.DQ_CURRENT". Within each table there is a common column called "Account_code__C". What i would like to do is build a  column called Account_code__C which houses the distinct account codes from both of these tables. i would then like to produce two other columns to tell me what table the Account_code__C was found in.

Desired outcome would look something like this

SELECT Account_code__C FROM GODS.DQ_TOBE
Select Account_code__C from GODS.DQ_CURRENT

The following code should get you the result for MS SQL or MySQL:

select distinct f.Account_code__C,
case when a.Account_code__C<>'' then 'Y' else 'N' end AS DQ_TOBE,
case when b.Account_code__C<>'' then 'Y' else 'N' end AS DQ_CURRENT
from (
    SELECT Account_code__C FROM GODS.DQ_TOBE
    union
    SELECT Account_code__C FROM GODS.DQ_CURRENT
) f
left join GODS.DQ_TOBE a on a.Account_code__C=f.Account_code__C
left join GODS.DQ_CURRENT b on b.Account_code__C=f.Account_code__C


Setting up indexes to support a flexible API search endpoint I have a MySQL 5.7 table that holds activities that users can search through. It will end up holding less than 100,000 rows and inserts will be less than 50/day. This activities table has a structure of:

CREATE TABLE activities (
    activityID int AUTO_INCREMENT,
    createdBy char, -- a unique AWS ID. eg: "us-east-1:128d0a74-c82f-4553-916d-90053e4a8b0f"
    active bool,
    city varchar,
    length int,
    price  decimal,
    latitude decimal,
    longitude decimal,
    CONSTRAINT Activities_PK PRIMARY KEY (activityID)
);

The queries I will be making are constructed depending on the query string sent to the API endpoint. There are 3 'paths' the API can be searched by to return activities.

If createdBY is included the query string, Select ... where createdBy = ~some id~
If city is included, Select by city, and length if included, and mininmum price if included, and maximum price if included
If latitude and longitude are included, search between the 2 points

Indexes I have come up with so far:

createdBy, as a B-Tree. Should this be a HASH index because it's a unique identifier?
(city, length, price). Since this is a covering index, if the constructed Select query is only searching by city, would it still use the index even though it contains length and price?

Also, since covering indexes start on the left, should I also include a (city, price, length) index?
(latitude, longitude)

Are these acceptable indexes? Should I include an index for the active column? All queries should only return active activities. My guess will be ~90% of rows would be active, while ~10% would be inactive. This answer suggests no.

Thanks so much! Any other suggestions are welcome!

InnoDB does not support HASH indexes; only B-TREE. You could certainly implement your own with another column called createdByHash. Use something like CRC32 as your hashing function to make it integer-based and save a few bytes per row. Index this column instead and make your queries SELECT .. FROM .. WHERE createdByHash = CRC32(<input>) AND createdBy = <input> (you have to have both parts because there could be collisions with CRC32)

Yes, MySQL uses multi-column indexes in left-to-right order. SELECT city, length, price FROM .. WHERE city = x will still use the index. If you have any range-based queries, the index should put those range'd columns to the right. An index on (A, B, C) and WHERE A=4 AND B>5 AND C=3 will only use the first two columns of that index.

If you are doing a simple greater-than/less-than on the lat/long, what you have will probably be OK. This one is tough since they are both ranged predicates. You may have to experiment on (lat, long) vs (long, lat) in your application.


On an idle instance, why does setting max memory to 256mb cause %CPU to jump Five 2012 instances on a 2-node cluster using AVGs. Two of them doing nothing yet, no user databases or users.  Customer insists on specific max mem settings while waiting for additional memory approval.  We lowered max mem from 1gb to 256mb on both and each instances CPU usage jumped to 20% and stayed there. Raising it to 512mb brought it back down to basically nothing. Regardless of mem setting each instances memory usage stayed right around 200mb.

Any ideas?  I'm still doing research for the technical answer they are requesting.

If the Page Life Expectancy performance counter is at a decent value, this can be due to constant (re) compilation : because of the low amount of memory available, plans get evicted quite quickly from the cache, and need to be compiled every time thier corresponding query is submitted to SQL Server.


Permissions Error on Member that doesn't exist My team built an SSAS cube a year or two ago; it was the second cube we've built. We rolled it out to a few teams who have been using it without any issues. Today, I sat down with a new user to set up the data connection in Excel and do a little training, and the user received a strange error:

  DimensionPermission (1, 2) The '[J]' member was not found in the cube when the string, [Measures].[J], was parsed.

What's strange is that the member in the error message does not exist in this particular cube. It exists in the first cube our team built, but none of the calculations or queries in the second cube use members from the first cube. We used the first cube as a template for the second cube, but all the original measures/dimensions were overwritten or deleted.

As far as I know, no other users have seen this error including users in the same AD group. The security is pretty basic on the cube, AD groups are used in the database roles, nothing is dynamic and we aren't using MDX to specify security.

I've combed through the SSAS project in data tools, and I can't find any reference to this member. Does anyone have an idea of what might be going on here? This and this were the two common themes I found in my searches, but as I said before, everything from the first cube was overwritten or deleted, so I can't find anything in the cube that references the missing member.

In SSMS if you open each role and find the Measures Dimension Data security section then go to the Advanced tab (if you have trouble finding it see this blog for screenshots from the same screens in SSDT-BI) do you see any role mentioning that problem measure? If that measure doesn't exist in the cube then remove it from the security role definition.

If that doesn't help you find it then right click on the database on SSMS and script the database as an Alter then search the XMLA for that measure and post back the section of XMLA where you find it.


Error after upgrading datastax to version 5 After upgrading to DataStax Enterprise version 5, the node does not come up, and I get this error in the log:

  Insufficient permissions on directory /hints

Where is this directory located?

Insufficient permissions on directory /hints

I'm guessing that your "cassandra" user doesn't have permissions to add a new directory on its own.  Similar to the tarball installation process, you'll need to create the directory yourself.

  where the location for this directory?

By default, the location for /hints is:

$CASSANDRA_HOME/data/hints

You can also override this in your cassandra.yaml, with the hints_directory property:

hints_directory: /var/lib/cassandra/hints


AG Group failover and quorum We have a 4 node AG set up across two data centers using 4 replicas with a file share witness. Nodes 1 and 2 are in data center 1 and Nodes 3 and 4 in data center 2. Node 1 is primary and is set to synchronous commit with automatic failover to node 2 in the same datacenter. Node 1 is set to asynchronous commit to nodes 3 and 4 in data center 2. Node weighting is set to one for nodes 1 and 3 (main node in each data center) and zero for nodes 2 and 4.

Please, can someone tell me what the behavior would be if we had an unexpected SQL Server shut down at node 1?

Would the AG attempt to failover to node 3 in data center 2 due to the node weighting, fail and not attempt to fail over to any other replicas. Or would the AG group ignore node 3 due to it being async and try to fail over to the first synchronous replica with automatic failover based on the preferred owner's list?

The configuration described in the original topic is a bit strange. There are exists two resource groups SYSTEM (CNO, CNO_VIP1, CNO_VIP2, FSW) and AG (LISTENER, LISTENER_VIP1, LISTENER_VIP2, AG).

These two resource groups can be owned by different WSFC nodes. For example SYSTEM is owned by node from DC2 and AG is owned by node from DC1. In this case, if the link between DC1 and DC2 is broken:

Then DC1 will have only 1 vote (NODE1)
Then DC2 will have only 2 votes (NODE3 + FSW)

Accordingly to the quorum configuration DC2 part will alive (due to majority of votes) and DC1 will die (due to minority of votes). But as soon as synchronization between DC1 and DC2 is asynchronous there won't be automatic failover to DC2.

So you will end up with the situation when DC2 is alive but can not handle DB workload, because AG will wait manual intervention (allow_data_loss).

To avoid such situation, you need to assign votes like that

DC1:

NODE1: 1
NODE2: 1

ASYNC SYNCHRONIZATION

DC2

NODE3: 0
NODE4: 0

In that configuration AG will fail over to the NODE2 (DC1).

You'd better to explain the purpose of DC2. Is it disaster recovery DC or you need it for read scale or both?


pt-query-digest slowlog output format looses query cache hit information? I'm using pt-query-digest (v2.2.20) slowlog output format to filter out queries based on their timestamp, to run further digests on the filtered slowlog :

ptqd 11-21.slow.log --output slowlog --no-report --filter '($event->{timestamp} > 1483957800) && ($event->{timestamp} < 1483959600)' > 11-21.halfhour.log

The point is my source log 11-21.slow.log has query cache hit info, for instance :

# Time: 170109 12:08:43
# User@Host: pidf_user[pidf_user] @ [10.226.13.132]
# Thread_id: 32829731 Schema: webmbpidf QC_hit: No
# Query_time: 0.015387 Lock_time: 0.000038 Rows_sent: 0 Rows_examined: 0

Whereas the filtered output 11-21.halfhour.log doesn't :

# Time: 170109 11:59:59
# User@Host: pest_user[pest_user] @ 10.226.13.13 []
# Thread_id: 32805954
# Query_time: 0.000221 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0

l lose schema information as well but it's less a problem in my situation.

Is there a way to keep the query cache info in the slowlog output ?

Any help would me much appreciated.

It appears to be a known bug that's already been reported since 2013.

So the actual answer to my question is no: by now it's impossible not to loose this information when outputting a slowlog with pt-query-digest.