Sql Server Database 2008 R2 is in recovery mode still after 16 hours Sql Server 2008 R2 Database  is in recovery mode still after 16 hours for one of the database which is is Simple Recovery model.

The data file is 200 GB and Log file 90 GB.

We have to do Crash Recovery(Restarting the sql server engine) and the particular Database of size as mentioned above gets in recovery mode for past 16 hours.

There are other 5 database of equal or more size, but these other 5 database recovers faster. the reason why we do stopping the service is due to one of the Session is not getting killed in the aforesaid database, even we tried to kill it explicitly.

This session is blocking and causes long operation for backups for the above mentioned database. we checked the VLF's it has as many as 719 records(vlf file) with lot more marked as inactive and everytime the log usage is above 99% near to 100% (DBCC SQLPERF(logspace)).

we tried to increase the autogrowth to 30% for the log, but to no avail, we are unable to the kill the Session, unable to the backup due to extended longer duration and unable to the put the database online and ready(after restarting the 2008 R2 sql service).

What needs to be done for :

to Kill that particular Session which is creating the block ( Header
Block set to 1 in activity monitor)?
how do i resize the log file and see to it that it has good enough space?
is it possible to delete the inactive VLF?
how do i make the database to become online faster ( after restart of 2008 R2 service)?

It sounds like the session you tried to kill was possibly a runaway and all the work it was doing now has to be rolled back as part of crash recovery. A large log file like that will always take time to finish recovery, especially if it is trying to rollback a large transaction.

This query may give you an estimated time for recovery, but it's not always accurate so use as a guide only.

SELECT command AS [Command],
    DB_NAME(database_id) AS [DB],
    CAST(percent_complete AS DECIMAL(5,2)) AS [PercentComplete],
    DATEADD(SECOND, estimated_completion_time, GETDATE()) AS [ETA],
    wait_resource,
    wait_time,
    wait_type,
    blocking_session_id
FROM sys.dm_exec_requests
WHERE command = 'DB STARTUP'

Once the database is recovered, your temptation may be to shrink that log to remove all the VLFs, however, remember that too few VLFs can cause problems as well. Take a look at this article by Kimberly Tripp where she outlines the algorithm SQL uses to determine the number of VLFs to create during a growth, and some guidelines on sizing your log files correctly.

  What needs to be done for :

  to Kill that particular Session which is creating the block ( Header Block set to 1 in activity monitor)?
  how do i resize the log file and see to it that it has good enough space?
  is it possible to delete the inactive VLF?
  how do i make the database to become online faster ( after restart of 2008 R2 service)?

You probably can't. If it is trying to rollback work it has previously undertaken, you simply have to wait for it to finish. You can try KILL spid WITH STATUSONLY to get the status of the roll back, but the majority of time this doesn't return much useful information.
You need to back it up (to mark log portions inactive), shrink it and then regrow it to your optimal size (see Kimberly's article).
Yes, shrinking will do that, but remember to adjust your growth settings and manually grow the file to a suitable size to control VLF numbers in future. Do not use percentage growth factors and do not use too large an MB growth factor. Remember that log files cannot be instantly initialised like data files can be, so every growth has to be zeroed before it can be used.
You really can't. Recovery takes as long as it takes, and you have to wait it out. You don't want inconsistent data in your database, and part of consistency is the rolling back of failed transactions such as this. You can monitor the DB STARTUP session and check for waits or blockers and see what you can do to remove those obstacles, but you won't likely be able to do much.

You can protect against slow recovery in the future by fixing your log growth settings to prevent excess VLFs and implementing monitoring for runaway sessions. Capping your log files can prevent them from growing excessively, but there is always a risk that legitimate transactions get rolled back due to insufficient log space in that scenario.

Take a look at this article regarding the database recovery process to get a full understanding of what is happening.


Copy large PostgreSQL table to multiple csv files with limited row number Is there a way, using a sql function or an already existing psql function, to copy a very large table into multiple csv files and specify the maximum number of rows allowed in the output files ?

For example, a 35 million rows table would output 35 csv files of 1 million rows.

If the records don't have a embedded newlines in text fields, so that there is a strict [one line = one record] mapping, you may pass the output of \copy csv in psql to the Unix command split. For instance:

\copy (your query here) TO program 'split --lines 1000000' (format csv)

See the options of split to change the format of the names of output files or the destination directory.
It can also be used server-side with COPY instead of \copy if you're superuser.

If the records may have embedded newlines, it's more complicated because with the above method, a record might span two consecutive files, making each file an invalid CSV file in isolation. For instance:

\copy (select E'1st line\n2nd line') TO program 'split --lines 1' (format csv)

would produce two files

xaa: "1st line

and

xab: 2nd line"

If the goal is to concatenate the files back into a single file to process it, then it doesn't matter, but if they must be processed individually, a different method should be considered.

It's possible in psql, but a bit involved (as opposed to writing it in a programming language). As of PostgreSQL 12, csv is a native output format in psql so a cursor on the query might be used with FETCH 1000000 statements doing the actual cut-and-retrieve. The skeleton for a piece of script that should work would look like this:

\pset format csv
\pset tuples_only on
begin;
declare c cursor for your-query-here;
fetch 1000000  from c \g file1.csv
fetch 1000000  from c \g file2.csv
... as many times as necessary...
close c;
end;

Because there is no looping construct in psql and assuming you don't know in advance how many fetch steps are needed, you have to generate that piece of script in a previous step that computes the count(*) of the resultset and emits (count(*)+NR-1)/NR times fetch NR from... commands to numbered files like above, where NR is your number of records per file.


mysql error 1265 data truncated for column I created a table in mysql like this :

create table if not exists  data
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
execute_time DATETIME NOT NULL default current_timestamp,
db VARCHAR(25) NOT NULL,
book varchar(6),
`open` float(20,3) not null,
`close` float(20,3) not null)

and when I want to insert value into this table, it fails and show the exception as DataError: (1265, "Data truncated for column 'open' at row 1")

and the values I want to insert into open is  3535929.559. I code the insert data in python  like this:

insertData = """INSERT INTO {table_name} (execute_time,db,book,`open`,`close`) values
                        (%(execute_time)s,%(db)s,%(book)s,%(open)s,%(close)s)""" .format(table_name = settings["dumpTable"]["data"])

    conn.cursor().executemany(insertData,data_in_dict)
    conn.commit()

The most strange thing is when I insert the same value in MySQL workbench ,it went smoothly, the query is like this :

insert into data (db,book,`open`,`close`,execute_time) values ('au','book','3535929.559','1079339.851','2016-7-22');

But the data shown in the table is:

 '5', '2016-07-05 00:00:00', 'au', 'book', '3535929.500', '1079339.875'

I think the problem maybe insertion from python is more restricted? Then how to set it to be less restricted?  Any help would be appreciated!

Not addressing the question, but addressing 2 bugs in your schema.

FLOAT(m,n) takes the decimal input, rounds to n decimal places in decimal, then converts to binary with another rounding error.
FLOAT (with or without (m,n)) can hold only about 7 digits of precision.  Hence, both numbers in your example lose precision at the low end.  As you found out, storing 3535929.559 into a FLOAT(20,3), then retrieving it will yield only 3535929.500.  This is because a FLOAT has only 24 "significant bits of precision".

There are two solutions for both bugs:

DECIMAL(m,n) -- (20,3) will still round to 3 decimal places, but that might be OK for your application.  But it does not have the extra rounding to binary nor the loss of precision.
DOUBLE (without (m,n)) -- This has about 16 digits of precision, more than enough for most applications.  And, even though the decimal input will be rounded to binary, it will be so far out that it is unlikely to cause errors.

Sizes...

FLOAT (with or without (m,n)) takes 4 bytes.
DOUBLE (with or without (m,n)) takes 8 bytes.
DECIMAL(20,3) takes 10 bytes.  Shrinking either number may save space.


getting error incorrect syntax near @certificate I have created a certificate and now I want to remove certificate signature and drop the certificate. When I supply the certificate name directly it is working but when I'm making use of local variables to supply the name its giving me syntax error.

I have tried with different datatypes as well

text, varbinary(max), char(200), varchar(200), nvarchar(200)

None of these datatypes are working.

declare @certificate nvarchar(200)
set @certificate = 'ITManagerCertificate'

begin transaction

use DBwork
-- remove signature from stored procedure
drop signature from Report_Manager
by certificate @certificate

-- drop certificate
--DROP CERTIFICATE N@certificate

if @@ERROR > 0
begin
   rollback transaction
end
else begin
   commit transaction
end

I don't know if you can pass a paramater in this situation. Please try to create your query in a variable, like:

declare @query as nvarchar(255);
set @query = 'drop signature from Report_Manager by certificate ' + @certificate;

and then execute the query like:

EXECUTE sp_executesql @query


Postgres jsonpath ANY Equivalent Consider the following json:

{
  "floor": [
    {
      "level": 1,
      "apt": [
        {
          "no": 1,
          "area": 40,
          "rooms": 1
        },
        {
          "no": 2,
          "area": 80,
          "rooms": 3
        },
        {
          "no": 3,
          "area": null,
          "rooms": 2
        }
      ]
    },
    {
      "level": 2,
      "apt": [
        {
          "no": 4,
          "area": 100,
          "rooms": 3
        },
        {
          "no": 5,
          "area": 60,
          "rooms": 2
        }
      ]
    }
  ]
}

Using Postgres jsonpath, if I wanted to get the apartments with 3 rooms, the following would suffice:

'$.floor[*].apt[*] ? (@.rooms == 3)'

My question is how would I do the same if I wanted to compared against an ARRAY such that I could provide the number of rooms in an array similar to ANY in SQL

'$.floor[*].apt[*] ? (@.rooms = ANY{3,5,6,10,11} )'

Using Postgres 12.2 on Linux Ubuntu 18.04, 64Bit

I don't know of any way to do that with jsonpath.

I'd suggest pulling apartments with any number of rooms and filtering that list, but I suspect that would get quite large as the dataset grows ...

... but then, if you're talking about large data volumes here, I would have to ask why you're not doing this is a proper, Relational way?  Yes, you can get postgres to do this with JSON data but should you?  This is a very simple data structure that you could represent in a single table which, properly indexed, will cope with huge data volumes.

select level, no, size, rooms
from apts
order by 1, 2 ;

+-------+----+------+-------+
| level | no | size | rooms |
+-------+----+------+-------+
|     1 |  1 |   40 |     1 |
|     1 |  2 |   80 |     3 |
|     1 |  3 | null |     2 |
|     2 |  4 |  100 |     3 |
|     2 |  4 |   60 |     2 |
+-------+----+------+-------+

select *
from apts
where rooms in ( 3, 5, 6, 10, 11 ) ;

+-------+----+------+-------+
| level | no | size | rooms |
+-------+----+------+-------+
|     1 |  2 |   80 |     3 |
|     2 |  4 |  100 |     3 |
+-------+----+------+-------+

Relational databases are really good at finding small bits of [related] data and bolting them back together again.  They're generally pretty rubbish at taking large blocks of data, like chunks of JSON, and pulling them apart again.  Yes, RDBMS providers are trying to improve this, but I would suggest it'll still be way slower than doing it the "old-fashioned" way.


How much will computer hardware affect the speed of a "load data local infile" query? I have a VBA program that separates and saves an excel file into 48 different .csv files, then loads those files into 48 separate tables. I use "load data local infile" to accomplish this, and it has worked very well, and very speedily, up until about 2 weeks ago.

Suddenly, every "rs.open..." line, where I actually send the query to MySQL, took about 4-5 seconds, as opposed to the ~1 second it used to take, which drastically increased the time it took to go through each full file.

I still haven't pinpointed the real problem, but it DID get me thinking: Would better computer hardware appreciably speed up the process? I've got 4gigs of ram and a 3.2 Ghz processor, so my computer isn't terrible, but it COULD be better.

Questions

Can the speed of MySQL queries be appreciably increased with better hardware, or is it somehow limited by MySQL itself?

I would like to recommend something with regard to LOAD DATA INFILE:

You need to increase bulk_insert_buffer_size (such as 256M) because a special tree structure is made to accommodate the loading of the data. Even with LOAD DATA INFILE, the tree structure is used when loading data into non-empty tables.

According to the MySQL Docs on bulk_insert_buffer_size:

  MyISAM uses a special tree-like cache to make bulk inserts faster for
  INSERT ... SELECT, INSERT ... VALUES (...), (...), ..., and LOAD DATA
  INFILE when adding data to nonempty tables. This variable limits the
  size of the cache tree in bytes per thread. Setting it to 0 disables
  this optimization. The default value is 8MB.


Appropriate situation to data warehouse and how to best manage the warehouse? I have a query that is on the opening page of our application that takes about 8-10 seconds to load.  I've tried all I can to optimize it and I simply cannot make it go any faster (indexed appropriately, rewriting redundant code, ridding it of view-ception hell it lived in).  It's a query on our largest table with a bunch of joins to other tables that have a many to one relationship to the main (many in the smaller relate to one id in the largest) and requires an unavoidable group by largeTable.id that makes it so slow.

Now I do have to make this work faster one way or another.  I think this may be an appropriate situation to have a data warehouse (I hope that's the right term) type table.  I would insert into this warehouse table the results of the slow query and then query from the warehouse table directly.  Then, anytime something in my application that affects a table that affects the data from the slow query/data warehouse table, I would drop the rows in the table and recreate it which would then take slightly longer than the initial query, but only happen a few times a day, whereas the rest of the day I am querying directly from my data warehouse table much faster.

Is this a reasonable solution?  Are there any common pitfalls or things I need to watch out for with this methodology, or a better way to do it?  After a few days of trying to just optimize the query, I need some work around.  I am still relatively new to this and am open to suggestions.

Calculating and storing a value is a cache; that's a very common strategy.  A data warehouse is a very different beast (usually more than one table!).

The main thing to be concerned about is timeliness.  If customers expect the results to be closer to real-time, that's OK for now, as ten seconds once an hour should be fine.  Even if it bloats to ten times that length, it should be OK.

If you absolutely must have current data, rather than "up to one hour old," you'll need to know when your source tables have changed.  If you already have a DateLastModified column in each of the tables, then you can check to see if there's a more recent values than the DateRecorded you'll be capturing when you save this query.  A stored procedure can query these values and refresh the cache if necessary.  The problem is that the run time will be slow when any source data has changed, which I suspect will be most of the time.  Also, you'll probably need indices on your DateLastModified fields, to quickly identify when the source data hasn't changed.

Alternatively, you could scatter triggers hither and yon, so any time a key table is updated, you mark the cache as dirty so it can be updated.  This would be high maintenance and fragile, but if you don't already have DateLastModified fields it might be easier.

If your customers are OK with this report being out of date by up to a few minutes, just run it on a schedule.


pg_commit_ts growing in postgres instance I have a customer who is trying to set up bidirectional replication with logical replication publication/subcription model across two instances with the help of last_update_wins and reported that with little to no activity he is running out of space. He only have a 5 GB disk as of now with his POC out of which ~2 GB is occupied by wal files (no big fluctuation with the usage) however from the Host console I see that there pg_commit_ts folder keeps growing and is currently @1.7 GB

2.4G    pg_wal
1.7G    pg_commit_ts
96M     db/base
44M     /pg_xact

From postgres documentation I see that this is Subdirectory containing transaction commit timestamp data. I then noticed that he also have enabled "track_commit_timestamp" which collects commit time of transactions.

I'm trying to figure out 1. Is there any way to control retention on pg_commit_ts   2. Is this normal to have huge "pg_commit_ts" with the parameter enabled or does this have to do with their use case.

Please advise.

came to find out, that unless the XIDs are cleared by vacuum / autovacuum pg_commit_ts would not be cleared. So basically this instance needs attention on tuning autovacuum parameters to run autovacuum efficiently


How to create user that can access to "master" database Recently I have created a TABLE that is under Database > System Databases > master > Tables > my_table.
(refer to the image below)

Now I would like to create a user that can access this Table, but don't know how it works. (I wanted to use C# to connect to this Table)

Recommendations

User objects shouldn't be stored in the master database as explained in the following articles.

Tables in the Master Database (BrentOzar.com)

  The master database is a system database – it’s not really for you to poke around in and store stuff.  We usually design different backup & recovery processes for the master database as opposed to user databases.

The master database is run in Simple Recovery model and hence cannot be restored to a certain point-in-time if the database becomes corrupt.

Tables in the SQL Server “master” database, will they cause problems? (ServerFault.com)

  I would recommend doing the move to a dedicated database. Here are a couple of reasons why:

  If you ever want to migrate this database to a different instance of SQL Server you'll need to do this anyway. You can't migrate master.
  The master database is not meant for user data. It's meant to be the central point for data and code needed to keep the instance functional. Adding things in master muddies the waters there.
  Adding things to master requires changing security in master unnecessarily.

master Database (Microsoft TechNet)

  When you work with the master database, consider the following recommendations:

  Always have a current backup of the master database available.
  Back up the master database as soon as possible after the following operations:

  Creating, modifying, or dropping any database
  Changing server or database configuration values
  Modifying or adding logon accounts

  Do not create user objects in master. Otherwise, master must be backed up more frequently.
  Do not set the TRUSTWORTHY option to ON for the master database.

Instead

Consider creating a user database (e.g My_App) for your application.

CREATE DATABASE [My_App]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'My_App', FILENAME = N'E:\DATA\My_App.mdf' , SIZE = 131072KB , FILEGROWTH = 16384KB )
 LOG ON
( NAME = N'My_App_log', FILENAME = N'F:\LOGS\My_App_log.ldf' , SIZE = 65536KB , FILEGROWTH = 16384KB )
GO

Then create an administrative user (e.g. My_App_DBO) for your database:

USE [master]
GO
CREATE LOGIN [My_App_DBO] WITH PASSWORD=N'P@ssw0rd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
USE [My_App]
GO
CREATE USER [My_App_DBO] FOR LOGIN [My_App_DBO]
GO
USE [My_App]
GO
ALTER USER [My_App_DBO] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [My_App]
GO
ALTER ROLE [db_owner] ADD MEMBER [My_App_DBO]
GO

This user will have the db_owner role on your new database and can perform DDL and DML statements against your new database.

  Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database.

Now you can create your table in your new My_App database with the SQL Server Login My_App_DBO.

When you have finished configuring your database consider adding a new user that doesn't have the db_owner role, to reduce the liability of unwanted DDL statements.

Create a new user (e.g. My_App_USER) with only db_datareader and db_datawriter permissions.

USE [master]
GO
CREATE LOGIN [My_App_USER] WITH PASSWORD=N'P@ssw0rd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO
USE [My_App]
GO
CREATE USER [My_App_USER] FOR LOGIN [My_App_USER]
GO
USE [My_App]
GO
ALTER USER [My_App_USER] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [My_App]
GO
ALTER ROLE [db_datareader] ADD MEMBER [My_App_USER]
GO
USE [My_App]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [My_App_USER]
GO

A SQL Server login with the db_datawriter role can:

  Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.

A SQL Server login with the db_datareader role can:

  Members of the db_datareader fixed database role can run a SELECT statement against any table or view in the database.

And then go from there.

Instead of using SQL Server logins you can assign Windows Domain users as SQL Logins.
You could also vary the access levels on each user object you create for each individual login.

This is just a small starting point and should give you a general idea on how to keep things out of the master database.

And always ensure you are on your database when running scripts to create objects:

USE My_App

Good luck on your journey.


Pulling SQL Server data into Excel I'm trying to pull data from an SQL Server database into Excel 2016 via ODBC / Microsoft Query (built in to Excel).

I'd like to have a dropdown of companies, where after a company is selected from the dropdown, all contacts for that company (from a separate contacts table) are displayed.

From what I've learned so far I need to use :field or @field to indicate that the user chooses - something like:

SELECT Company.CompanyName,
       CompanyContacts.Forename,
       CompanyContacts.Surname,
       CompanyContacts.Email
FROM Commercial.CompanyContacts CompanyContacts
     INNER JOIN Commercial.Company Company
        ON (CompanyContacts.CompanyID = Company.CompanyID)
WHERE (UPPER (Company.CompanyName) = UPPER (@CompanyName))

But Excel / MS Query returns:

  "Must declare the scalar variable "@CompanyName". Statement(s) could not be prepared."

Can anyone advise on how I declare the scalar variable? Assuming that's even what I'm actually needing to do here. Many thanks.

Have your declaration at the top.. Variables are declared in the body of a batch or procedure with the DECLARE statement and are assigned values by using either a SET or SELECT statement.

DECLARE @CompanyName VARCHAR(200);
SET @CompanyName = 'CompanyName';

SELECT Company.CompanyName,
       CompanyContacts.Forename,
       CompanyContacts.Surname,
       CompanyContacts.Email
FROM Commercial.CompanyContacts CompanyContacts
     INNER JOIN Commercial.Company Company
        ON (CompanyContacts.CompanyID = Company.CompanyID)
WHERE (UPPER (Company.CompanyName) = UPPER (@CompanyName))


Tables with no rows not empty I've got a couple of tables that get cleared out regularly by a cron job on a web server. However when these tables are completely empty it looks like there is still data in them.

e.g. This database is showing 2 tables that have 0 rows, yet their size is not 0. If i truncate the tables they do go down to 0.

The only additional data I can think of that might be contained in these tables are the auto increment number of the id fields, but considering the number in the first table is only at 29 as of the screenshot 117Kb seems quite a lot for such a small bit of data.

Sorry if this is an incredibly obvious answer, I don't know a huge amount about how databases actually work.

TRUNCATE effectively drops and re-creates the table, so the allocated space is gone. A DELETE... command will only remove the rows but does not cause the table size on disk to automatically shrink. This would cause excessive I/O for every DELETE... command to shrink files if it were true.

Truncate Reference for MySQL 5.7


Sending one email when all jobs completes successfully I need to create backup jobs over several instances, I wonder if there is any way to send a single notification via mail only when jobs from all instances completed successfully.

The way I've achieved this is to add two steps to each backup job.  The first writes to a results table; it doesn't matter where this lives so long as all jobs can reach it.  The second step runs sp_start_job, targetting a common, new job.

This new job has three steps.  One, check the results table.  If any backup job has not yet reported then stop.  If all backup jobs have reported success then continue.  Step 2 - reset the results table.  Step 3 - send the email.  Two and three can be reversed depending on your tolerence for failure and your recovery practices.

The results table can be managed by insert/truncate or update/update.  You can get inventive with run numbers and history logging, too, I've that's needed.

You have to think how you want to recover from failed jobs?  Purge the results and start all again?  Re-start failed jobs only?  Have a precursor step which launches your backups, which then must be idempotent etc. etc.


How can I define the datatype of a view in MySQL? I've written a simple aggregation view over a table, and the view returns different data types that the table. This causes some issues on the application side.

Here's a simplified query for the sake of the example:

SELECT id, FLOOR(date_id / 100) month_id, SUM(value) FROM some_table WHERE....;

The problem is that data_id is an INTEGER(11) column, represented in my app as an int32, and the result of FLOOR(date_id / 100) is a BIGINT, represented in my app as an int64.

The type of id also changes, for reasons I can't explain, from int64 to int32.

Is there any way to cast the column to an int32? CAST seemed not to work.
I haven't found any documentation for a way to see what the expected data type of a view result set is.

You can hunt down the column definitions in the INFORMATION_SCHEMA. For example, for the table mydb.mytb and the column name needed is called mycol, do this:

SELECT column_type FROM information_schema.columns
WHERE table_schema='mydb'
AND table_name='mytb'
AND column_name='mycol';

For the query with formulas that you have in your question:

SELECT id, FLOOR(date_id / 100) month_id, SUM(value) FROM some_table WHERE....;

Try creating your own empty temp table using your query with a FALSE-evaluated WHERE clause:

CREATE TABLE IF NOT EXISTS test.mycolumnmap
SELECT id, FLOOR(date_id / 100) month_id, SUM(value) value FROM some_table WHERE 1=2;

That table, though empty, has a definite table structure and some columns types defined. Now, just run the same query to get the column types from the temp table:

SELECT column_name,column_type FROM information_schema.columns
WHERE table_schema='test'
AND table_name='mycolumnmap'
AND column_name IN ('id','month_id','value');

Another alternative is to just ask the INFORMATION_SCHEMA about the columns of the view directly:

SELECT column_name,column_type FROM information_schema.columns
WHERE table_schema='test'
AND table_name='view_name';

Give it a Try !!!


database DDLs and implicit commit I've been taught that in RDBMS any DDL always causes an implicit commit, ending any active transaction at the time the DDL is issued.

Now I found this page on the PostgreSQL wiki that states that PostgreSQL is able to perform DDL statements in a transaction, making them atomic and rollback-able.

But does that change anything with respect to the implicit commit about the DDL?
Is it still executed when the DDL is issued?

Your question is tagged database-agnostic, so I'll provide an answer that applies to SQL Server (though I suspect that it goes for most modern platforms).

For SQL Server, the answer is no; DDL statements do not automatically COMMIT or ROLLBACK an open transaction. It normally places a schema lock on the object(s) you're touching with the DDL statement, which is held until the end of the transaction.

In fact, transactions are very useful whenever you want to combine DDL and DML statements, in order to maintain atomic business rules in your database.

Don't know about other database platforms, though.


microsoft sql express 2012 authentication connection issues - can't login I am a novice when it comes to Windows networking authentication, as well as just getting my feet wet with SQL Express, so please bear with me, hopefully this is a simple fix but so far the obvious fixes I have found in forums have not solved it.

I am trying to set up a geodatabase to be accessible by remote PC's running ESRI's ArcMap with standard license.

I have installed MS SQL Express 2012 as well as necessary components from the 2008 release to be able to access geodatabases.

I can successfully connect to the database from within ArcMap on the machine that is serving it, so I know the server is working.

However if I try and set up a connection on a remote machine I get authentication errors.  I can confirm the server machine is seeing the connection attempt and the firewall is letting in the TCP/IP, as I can see in SQL Management Studio Express's log on the server machine logging the failed attempts.

I get two errors depending on how i try and connect, if I use windows authentication I get a message that the domain is untrusted so i can't use windows authentication.  If I use database authentication I get a message that login failed and to check login/pwd/server.  As mentioned before I know the server part seems to be correct.

In SQL Management configuration I have added a user to the DB and given it a password, I have also switched authentication over to both windows authentication and db authentication.  However if I select db authentication at the connect dialog and put in the user/pwd I still get an error.

I have tried to add my remote machine's IP to the trusted domains on the server PC, but not sure I did that right.  Still get errors that say the domain is untrusted if i try windows authentication.

So not sure where to turn next, or what info you folks might require to sort this out.  Any leads are appreciated, thanks.

More information would help here and I've asked those questions in the comments. That said - a few thoughts:

Basically to access a SQL Server database you need a few things:

A login - this gets you into the server itself. This is not a database user, but a login at the instance level (In SSMS this is the top most security, not the security under a database.)
That login being mapped to a user - now there are exceptions but for the purposes here - you need that login to then be granted access to the database. This would be security found at the database level.
Permissions - you'll need permissions of some sort to do something in SQL.
SQL Server needs to have the protocol enabled that you are trying to connect with. Also if there is a firewall, the port (default is 1433 for TCP port) needs to be open.

When there are connection issues like you describe it is normally around something like not actually being on a domain but trying to use windows auth, a password mismatch, not having access to a default database selected for a login or an actual network issue like a firewall.

These are two older posts but they are fantastic resources for troubleshooting connectivity to SQL Server. This first link is from the SQL Server protocols team and describes the steps to troubleshooting connectivity issues. This second link is from the SQL Server connectivity team blog describing the various login failure messages and their states and how to troubleshoot each state.


MySQL logging max_questions resource How would I know in MySQL if the user has exceeded his max_queries per hour?
I know that I can see this error on the client side, but I just want to know that before client is reporting to me, somewhere on the server side, would be nice to have it in error_log at least.

There is no specific status variable for queries per user in MariaDB. You can only monitor Queries and Questions status variables on your monitoring system.


Table Joined To Itself Appears Inefficient My ultimate goal is to compare two rows (only two will exist) and for a specific type and determine if its rows values are greater than the other rows type values. The result I came up with seems to be unwieldy which leaves me to wonder if it can be done in a faster or more elegant way.

I have created sql fiddle here which demonstrates the issue.

Say we are shipping cargo and our main route of type 1 needs to be compared to the other route type 2. Each route has a Wave value and a Roll value which need to be compared independently. Note that other route types (except main, it is always uses) can be compared to Main of type value 1, but only two at a time.

CREATE TABLE Shipping
(
[RouteID]         [INT]  NOT NULL,
[WaveValue]       [INT] NOT NULL,
[RollValue]       [INT] NOT NULL

);

INSERT Shipping(RouteID, WaveValue, RollValue)
VALUES (1, 20, 2),  -- This is the main route
       (2, 10, 30); -- The other secondary route, (only 2 items ever in this table)

With the values currently in the table we expect that the MainsWave will be greater than the other's wave and the reverse for the Roll.

Here is the working sql, note that the true end result is only concerned with IsMainWaveGreater and IsMainRollGreater but all columns are shown for debug:

   SELECT Max1.RouteID
        , Max1.WaveValue
        , Max1.RollValue
        , MaxOther.RouteID
        , MaxOther.WaveValue
        , MaxOther.RollValue
  , IIF(Max1.RouteID = 1,
        IIF(Max1.WaveValue > MaxOther.WaveValue, 1, 0),
        IIF(Max1.WaveValue > MaxOther.WaveValue, 0, 1)) AS IsMainWaveGreater
  , IIF(Max1.RouteID = 1,
        IIF(Max1.RollValue > MaxOther.RollValue, 1, 0),
        IIF(Max1.RollValue > MaxOther.RollValue, 0, 1)) AS IsMainRollGreater
   FROM
    Shipping AS Max1
   INNER JOIN Shipping AS MaxOther ON Max1.RouteID > MaxOther.RouteID;

Since the row that could be read first and joined to the next may not be the Main route (remember always value 1 in RouteID for main), the sql has to have an IIF which checks on what type Max1 is and then swap the check/results accordingly.

Can this be optimized or even rewritten?

This is just one operation in a CTE process so other operations can be entertained.

With two rows per table any method will be good.

Since you are OK with hard-coding the value 1 for the main RouteID I'd do it like this:

SELECT
    CASE WHEN Main.WaveValue > Other.WaveValue THEN 1 ELSE 0 END AS IsMainWaveGreater
    ,CASE WHEN Main.RollValue > Other.RollValue THEN 1 ELSE 0 END AS IsMainRollGreater
FROM
    Shipping AS Main
    CROSS APPLY
    (
        SELECT
            Other.WaveValue
            ,Other.RollValue
        FROM Shipping AS Other
        WHERE Other.RouteID <> 1
    ) AS Other
WHERE Main.RouteID = 1
;

It assumes that there are only two rows in the table, if there are more "other" routes, then specify their specific RouteID in the CROSS APPLY instead of <> 1 to get only one row that's needed.

Here is SQL Fiddle.


MySQL does not generating PID file I upgraded my OS from redhat 2.6.3 to 2.6.5 after that
when I restarted my MySQL server but it not started,

error message is "150320 11:53:17 [ERROR] Error message file '/usr/share/mysql/english/errmsg.sys' had only 641 error messages,
but it should contain at least 728 error messages." and when I fired the command service mysql start it giving error "Starting MySQL......The server quit without updating PID file (/var/mysql-data/hostname.pid).                                            [FAILED]"

Please help..

I got the sollution,

1:extract your new mysql file :-
tar -zxf mysql-5.1.58.tar.gz

2:Rename the old errmsg.sys file which is in error file showing
mv /usr/share/mysql/english/errmsg.sys /usr/share/mysql/english/errmsg.sys.old

3:copy the new errmsg.sys file to exsting place
cp mysql-5.1.58/sql/share/english/errmsg.sys /usr/share/mysql/english/errmsg.sys

4:start your server.
 service mysql start

for Detail explanation kindly refer this link

http://toastergremlin.com/?p=447


Have x number of rows returned for one record I have a Dates table with every single date and a column called weekly, biweekly or monthly . I want to show my data as it would appear if it were scheduled

ex

id    date   weekly   biweekly    monthly
1   01/01/2020  1       0            0
2   01/02/2020  0       1            0
3   01/03/2020  0       0            1

So what I want to do essentially is if:

weekly=1 then show me the current date and the next 6 rows (So all the other information will be the same for this 6 rows but the date will change only)

BIweekly=1 then show me the current date and the next 13 rows

MONTHLY=1 then show me the current date and the next 29 rows

id  date    weekly  biweekly    monthly
1   01/01/2020  1   0   0
1   01/02/2020  1   0   0
1   01/03/2020  1   0   0
1   01/04/2020  1   0   0
1   01/05/2020  1   0   0
1   01/06/2020  1   0   0
2   01/02/2020  0   1   0
2   01/03/2020  0   1   0
2   01/04/2020  0   1   0
2   01/05/2020  0   1   0
2   01/06/2020  0   1   0
2   01/07/2020  0   1   0
2   01/08/2020  0   1   0
2   01/09/2020  0   1   0
2   01/10/2020  0   1   0
2   01/11/2020  0   1   0
2   01/12/2020  0   1   0
2   01/13/2020  0   1   0
2   01/14/2020  0   1   0

You can use CROSS APPLY and a CASE statement in your WHERE clause to produce the results you're after.

Setup:

CREATE TABLE DateTable
(
 ID INT,
 DateValue DATETIME,
 Weekly INT,
 Biweekly INT,
 Monthly INT
)

;WITH CTE AS
(
  SELECT 1 AS ID,
    CAST('2020-01-01' AS DATETIME) AS DateValue,
    1 AS Weekly,
    0 AS Biweekly,
    0 AS Monthly
  UNION ALL
  SELECT ID + 1 AS ID,
    DATEADD(DAY, 1, DateValue) AS DateValue,
    CASE WHEN (ID + 1)%2 <> 0 AND (ID + 1)%3 <> 0 THEN 1 ELSE 0 END AS Weekly,
    CASE WHEN (ID + 1)%2 = 0 THEN 1 ELSE 0 END AS Biweekly,
    CASE WHEN (ID + 1)%3 = 0 AND (ID + 1)%2 <> 0 THEN 1 ELSE 0 END AS Monthly
  FROM CTE
  WHERE DATEADD(DAY, 1, DateValue) < DATEADD(YEAR, 1, '2020-01-01')
)

INSERT INTO DateTable
SELECT ID, DateValue, Weekly, Biweekly, Monthly
FROM CTE
OPTION (MAXRECURSION 400)

Query:

SELECT d1.ID, d2.DateValue, d1.Weekly, d1.Biweekly, d1.Monthly
FROM DateTable d1
CROSS APPLY
(
  SELECT *
  FROM DateTable
) d2
WHERE d1.ID IN (1, 2, 3)
  AND d2.DateValue >= d1.DateValue
  AND d2.DateValue <=
    CASE
      WHEN d1.Weekly = 1 THEN DATEADD(DAY, 6, d1.DateValue)
      WHEN d1.Biweekly = 1 THEN DATEADD(DAY, 13, d1.DateValue)
      WHEN d1.Monthly = 1 THEN DATEADD(MONTH, 1, d1.DateValue)
     END

You can see it working in this db<>fiddle example.


New to CTE - How to do a count after making table unique I'm new to CTE's and usually sub query my SQL. I'm unsure how to get the results I want as I keep finding solutions that would require sub queries.

I have the below table in SQL. I'm trying to write a CTE to give me one line of data per day for each person. Where a person has more than one location ('where' field) for a day - I need to exclude all lines for that day. If a person has multiple lines for one day but the location is the same place - I want to only keep one line of this data.

This is an example of the original table:

This is an example of the result that I'm looking for (note - I don't need the ID field in the result).

Below is the definition and insert statement to the original dataset:

CREATE TABLE Tbl1
(`ID` int, `Person` varchar(5), `Date` varchar(10), `Where` varchar(6));

INSERT INTO Tbl1
(`ID`, `Person`, `Date`, `Where`)
VALUES
(1, 'Laura', '17/10/2019', 'School'),
(2, 'Laura', '18/10/2019', 'School'),
(3, 'Laura', '19/10/2019', 'School'),
(4, 'Laura', '19/10/2019', 'Park'),
(5, 'Laura', '20/10/2019', 'Home'),
(6, 'Laura', '21/10/2019', 'School'),
(7, 'Laura', '21/10/2019', 'School'),
(8, 'Laura', '21/10/2019', 'Home'),
(9, 'Tom', '17/10/2019', 'Home'),
(10, 'Tom', '18/10/2019', 'Home'),
(11, 'Tom', '18/10/2019', 'Home'),
(12, 'Tom', '19/10/2019', 'Home'),
(13, 'Tom', '19/10/2019', 'School'),
(14, 'Tom', '20/10/2019', 'Home');

Don't know why you need a CTE (or a subselect), wouldn't this simply be

select person, date_, max(where_) from tbl1
group by person, date_
having count(distinct where_) < 2

(fiddle)

PS. Note I changed the table definition to 1) remove backticks that are only used by MySQL to quote identifiers (Redshift is based on Postgres) and 2) rename columns with names that conflict with SQL keywords.


Execution plan flips Filter and Execute Scalar when using the PK, causes cast to fail I have some data tables that where imported in from a flat file source so everything is varchar(max) except FileRecordID which is a int that hold the row number the data came from in the flat file source (and is the primary key of the imported table.)

The following query works perfectly

SELECT *
  FROM [CLIENT]
  left outer join
  (
    select cast(ENTRY_CODE as int) as ENTRY_CODE, ENTRY_DESCRIPTION
    from [ENTRY]
    inner join [TABLE] on [ENTRY].ENTRY_TABLE = [Table].FileRecordID
    where ENTRY_RECD = 'A' and TABLE_RECD = 'A' and TABLE_CODE = 'CLTDISC' and ISNUMERIC(ENTRY_CODE) = 1
  ) as discounts on CLIENT_CLASS = ENTRY_CODE
  where Client_recd = 'A'
  --and client.[FileRecordID] = 10607

I get the exact number of rows I was expecting and everything is fine. However if I uncomment that last line so I only show the record from the client data table who has  client.[FileRecordID] = 10607 it reports to me

  Msg 245, Level 16, State 1, Line 1
  Conversion failed when converting the varchar value 'BK/TN' to data type int.

The problem is from cast(ENTRY_CODE as int) as ENTRY_CODE in the first outer join. ENTRY can contain text in the ENTRY_CODE column, however all records that have ENTRY_TABLE set to the line number from TABLE where TABLE_CODE = 'CLTDISC' will always be numbers only. I perform the cast as ENTRY_CODE may or may not have leading 0's and spaces so I am trying to get them to be well formed.

What appears to me to be happening is adding client.[FileRecordID] = 10607 to the outer query causes the where clause not to be evaluated before the cast to int in the inner query.

I have tried things like adding and ISNUMERIC(ENTRY_CODE) = 1 to both the inner and outer queries but it has no affect.

How do I fix this?
Can anyone explain what is happening so I can understand how to avoid this in the future?

Non filtered query planed execution

Filtered (non working) query planned execution

Notice the reversed order of the Compute Scalar and the Filter

Other things I have tried that do not work:

Move TABLE_CODE = 'CLTDISC' in to the on clause in the inner join.
Added OPTION (FORCE ORDER) query hint to the query.

Update:
I found a solution but it is very "hackish" IMHO, I really would still like a explanation of how to avoid things like this in the future and how to do this correctly.

SELECT *
  FROM [CLIENT]
  left outer join
  (
    select ENTRY_CODE, ENTRY_DESCRIPTION
    from [ENTRY]
    inner join [TABLE] on [ENTRY].ENTRY_TABLE = [Table].FileRecordID
    where ENTRY_RECD = 'A' and TABLE_RECD = 'A' and TABLE_CODE = 'CLTDISC' and ISNUMERIC(ENTRY_CODE) = 1
  ) as discounts on CLIENT_CLASS = REPLACE(LTRIM(REPLACE(ENTRY_CODE, '0', ' ')), ' ', '0')
  where Client_recd = 'A'
  and client.[FileRecordID] = 10607

I'm sure there are dba.se and StackOverflow duplicates, but it was far faster for me to post this link:

https://feedback.azure.com/forums/908035-sql-server/suggestions/32912431-sql-server-should-not-raise-illogical-errors

In short, the Query Optimizer is really free to rewrite the plan as it sees best.  Sometimes, it means a column is transformed (CAST -> int) close to retrieval so that it doesn't have to be carried many steps forward before performing the CAST.

The only sure-fire way of preventing the error is to look at the CAST expression itself, e.g.:

cast(CASE WHEN ISNUMERIC(ENTRY_CODE)=1 THEN ENTRY_CODE END as int)


How to select TOP 1 value from each column where value is not null I am trying to select many columns from a table where there can be multiple rows with the same ID.  Each row contains data from columns that I want to return in one row.  Any time a column has a value other than NULL, I want to show that.  If all values in that column are NULL, it is OK to show NULL.

Example)

SELECT * FROM Table1 WHERE UnitID='73355'

This returns multiple rows.  Each row contains data in columns that I want to merge together.  I don't want to keep my WHERE clause.  In this example I am just using it to narrow down my results for troubleshooting purposes.  I want every row to return in the table but merge the data where the UnitID's match.  There could be 1, 2, 3, or more rows returned with matching UnitID's.  Each Row will have data in it I will want merged into 1 row.

I tried TOP 1 but it only returns the first row.  I want TOP 1 but each column I want the value that is NOT NULL.

I have this now...If i put in my WHERE clause a specific UnitID, it works.  However, I want to put other columns in my WHERE clause and remove UnitID.  I want to filter based off LRUnitStatus and Model.  As soon as I do that, it does not work.

    SELECT UnitID,
           MAX(LRUnitStatus) as 'Status',
           MAX([UnitNumber]) as 'Unit #',
           MAX(Make) as Make, MAX(Model) as Model,
           MAX([ProductionYear]) as 'Year',
           MAX(Length) as 'Length',
           MIN([Rear Door Type]) as 'Rear Door',
           MAX([Suspension Type]) as 'Suspension',
           MAX([LocationBranchCode]) as 'Location',
           MAX(HUBO) as 'HUBO',
           MIN([Wheel Type]) as 'Wheels',
           MAX([Tire Size]) as 'Tires',
           MAX([Floor Type]) as 'Floor',
           MAX([Tire Inflation]) as 'Tire Inflation',
           MAX([Side Skirts]) as 'Skirts',
           MAX([Side Panels]) as 'Panels',
           MAX([Roof Type]) as 'Roof',
           MAX([Logistic Posts]) as 'Log. Posts',
           MAX([E-Track]) as 'ETrack',
           MAX([Axle Spread]) as 'Axle'
    FROM   [ProfitMaster].[dbo].[vwUKTS_LR_jtg] LR
    WHERE  UnitID='73316'
    GROUP BY UnitID

What about:

SELECT UnitID, MAX(Column1) as Column1, MAX(Column2) as Column2, ...
  FROM Table1
 GROUP BY UnitID
;

This should return a value if there is a non-NULL value, and should only return NULL if that's the only value found.

Note that MAX isn't the only possible aggregate function you could use. MIN should work as well. Depending on you DBMS, there may be other possibilities as well.

Here's a SQLFiddle (against SQL Server - the basic command should work in just about any SQL language) showing it in action. You can see that it picks values from different rows if needed. Even if only one out of five rows has a non-NULL value, both MAX and MIN will return that one non-NULL value. You only get NULL if there are no non-NULL values at all for a given UnitID.


Find all records with the same first 3 characters Does anyone know how to return all records with the same first three characters in MS Access (I'm guessing this has to be done in the SQL view)?

For example

andria -doesn't match

Angie -match

Angela -match

Angel -match

Ann -doesn't match

You can use the LIKE operator to perform a wildcard search and return words that contain/start with specific patterns. Given your example above, you can issue the following query to return only those values that begin with "Ang":

SELECT * FROM people WHERE first_name LIKE 'Ang*';

You could CREATE a VIEW that is pre-filtered to only include the data that you want to be displayed:

CREATE VIEW filtered_people (first_name)
AS
SELECT first_name
  FROM people
 WHERE first_name LIKE 'ang*';

Alternatively, you could just use the query in your code to obtain the data you need:

Sub LikeX()

Dim dbs As Database, rst As Recordset

' Modify this line to include the path to your database
' on your computer.
Set dbs = OpenDatabase("my.mdb")

' Return a list of people whose name begins with ANG
Set rst = dbs.OpenRecordset("SELECT first_name" _
    & " FROM people" _
    & " WHERE first_name Like 'ang*';")

' Populate the Recordset.
rst.MoveLast

' Call EnumFields to print the contents of the
' Recordset. Pass the Recordset object and desired
' field width.
EnumFields rst, 15

dbs.Close

End Sub

You can find more information in the official documentation for MS Access SQL.


MariaDB 10.1.22 to use more RAM rather than CPU Im using:

Server version: 10.1.22-MariaDB - Source distribution

server spec:

128GB RAM
CPU: E5-2600

we have around 80 tables with moderate data below 10k record per table, all of them are InnoDB. I would like to utilize it to use more RAM rather than CPU of the server is it possible?

my.cnf :

[client]
port        = 3306
socket      = /tmp/mysql.sock

[mysqld]
port        = 3306
socket      = /tmp/mysql.sock
user    = mysql
datadir = /www/server/data
basedir = /www/server/mysql
log_error = /www/server/data/mariadb.err
#pid-file = /www/server/data/mariadb.pid
default_storage_engine = InnoDB
#skip-external-locking
#loose-skip-innodb
skip-name-resolve

performance_schema = OFF

key_buffer_size = 1024M
max_allowed_packet = 100G
table_open_cache = 4096
sort_buffer_size = 16M
net_buffer_length = 8K
read_buffer_size = 16M
read_rnd_buffer_size = 1M
myisam_sort_buffer_size = 256M
thread_cache_size = 512
query_cache_size = 512M
tmp_table_size = 512M
max_heap_table_size = 512M
table_definition_cache = 8192
table_cache = 4096
join_buffer_size = 10M
sort_buffer_size = 10M
sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

#skip-networking
#skip-name-resolve
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535

log-bin=mysql-bin
binlog_format=mixed
server-id   = 1
expire_logs_days = 10

default_storage_engine = InnoDB
innodb_data_home_dir = /www/server/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /www/server/data
innodb_buffer_pool_size = 4096M
innodb_additional_mem_pool_size = 2M
innodb_log_file_size = 1024M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 30
innodb_log_files_in_group = 2
innodb_thread_concurrency = 20
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_io_capacity = 2000
innodb_file_format = Barracuda
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
innodb_log_compressed_pages = 0
innodb_flush_neighbors = 0
innodb_adaptive_hash_index_partitions = 16
innodb_buffer_pool_instances = 4

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 1024M
sort_buffer_size = 16M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

UPDATE: we have alot of this kind of SQL hitting on the site (pagination), but its already indexed

SELECT VID, title, duration, addtime, thumb, thumbs, viewnumber, rate, likes, dislikes, type, hd FROM video WHERE active = '1' AND type = 'public' ORDER BY com_num DESC LIMIT 2952, 36

its show something like this on EXPLAIN:

/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 2 queries: 0.562 sec. */
Using index condition; Using where; Using filesort

do you think that the filesort caused it to do more CPU usage?

the cpu usage on photo above (the red box) will spike to 160% on peak hour, how can we reduce it?

I see you see your innodb_thread_concurrency is 20. That's not good. Why ??? Over the years, I have learned that infinite concurrency is best (See my old post MySQL using too much CPU). Usually, I would recommend setting this to 0.
With recent improvements to InnoDB, I learned that 0 may not be the best value in some cases. A comparison of two values (0 and 64) was done the article Percona Server 5.7 performance improvements. The article states:

For ages (Vadim discussed this ten years ago!) InnoDB has had a universal workaround for most scalability issues: the innodb_thread_concurrency system variable. It allows you to limit the number of active threads within InnoDB and reduce shared resource contention. However, it comes with a trade-off in that the maximum possible performance is also limited.

From this article, I started to realize that 0 may not improve CPU a Percona Server instance, but it can slightly improve MySQL. Setting this to 64 may or may not help. Why may or may not ??? The article compares MySQL with Percona. You are using MariaDB. Therefore, you would have to experiment with this. Since innodb_thread_concurrency is dynamic, you could run
SET GLOBAL innodb_thread_concurrency = 0;

or
SET GLOBAL innodb_thread_concurrency = 64;

an see if the CPU will level off.
CPU is not the only issue. I also noticed your innodb_buffer_pool_size is only 4G (4096M) and you have 128G of RAM. From the looks of the processlist, you seem to have a full stack running on the machine. I would suspect that the innodb_buffer_pool_size is too small and MySQL (MariaDB) is spinning its wheels removing old pages and importing new pages in the InnoDB Buffer Pool. You have the room to increase it, because you have 109G (144515380k) of RAM free.
RECOMMENDATIONS

Change innodb_thread_concurrency to 0 or 64
Change innodb_buffer_pool_size to 8G or 16G


STDisjoint() = not STIntersects()? In short, are STDisjoint() and STIntersects() antipodes of each other?
According to the documentation, it seems that they are. But there is no explicit note about it.

Are there any tricks? Which one is faster, for example?

Mathematically, disjoint = not intersects.

As to which is faster, according to the documentation on spatial indexes at http://msdn.microsoft.com/en-us/library/bb895265.aspx#methods , a spatial index can be used for a query containing geometry1.STIntersects(geometry2) = 1.

Strangely enough, geometry1.STDisjoint(geometry2) = 0 is not listed.


If you replace the inner joins with left joins you will get the data that you want. The left join shows the results even with there is not a match on the other side and in those cases shows a null.

SELECT `d0`.`name` AS `name0`, `d1`.`name` AS `name1`, `d2`.`name` AS
       `name2`, `d3`.`name` AS `name3` FROM `my_table_1` AS `d0`
 LEFT JOIN `my_table_1` AS `d1` ON d1.parent_id = d0.value_id
 LEFT JOIN `my_table_1` AS `d2` ON d2.parent_id = d1.value_id
 LEFT JOIN `my_table_1` AS `d3` ON d3.parent_id = d2.value_id
 WHERE (d0.dropdown_id=56)


A "Steps" table with parent_id I'm making a table which has "steps", The thing is

  Some steps may have multiple steps involved. So while looping over the steps I should be able to show this.

What I'm thinking of

+----+----------+-------------+-------+----------------+
| id | guide_id | description | order | parent_step_id |
+----+----------+-------------+-------+----------------+
|  1 |        1 | Step 1      |     1 | null           |
|  2 |        1 | Step 2      |     2 | null           |
|  3 |        1 | Step 2.1    |     3 | 2              |
|  4 |        1 | Step 2.2    |     4 | 2              |
+----+----------+-------------+-------+----------------+

This seems like the only logical way of doing this? But i'm hoping i'm wrong.

Should I create another table?

Looking forward to your responses.
Thank you!

Mysql is not really my usual dbms, but looking at your sample I would suggest you another schema by adding [chapter], and use it like a hierarchyid field of MS Sql.

+----+----------+-------------+-----------+
| id | guide_id | description | chapter   |
+----+----------+-------------+-----------+
|  1 |        1 | Step 1      | /1        |
|  2 |        1 | Step 2      | /1/2      |
|  3 |        1 | Step 2.1    | /1/2/1    |
|  4 |        1 | Step 2.2    | /1/2/2    |
+----+----------+-------------+-----------+

Then indexing by guide_id + chapter you can fetch the whole tree, or you can get one chapter using:

WHERE chapter LIKE '/1/2%' ORDER BY chapter


How to prevent insert row duplicate value in a column and equal the any value I have table

TABLE1(
 id number (primary key),
 IDAccount number,
 status char(10))

Table can have 2 row duplicate (IDAccount, status) with status=disable as well as

id     IDAccount     status
1       100          DISABLE
2       100          DISABLE

But, mustn't have 2 row duplicate (IDAccount, status) with status=enable as well as:

id     IDAccount     status
3       200          ENABLE
4       200          ENABLE // DON'T ALLOW

How I can do it ?
Thanks.

This can be done with a partial index. A bit complicated in Oracle because it doesn't allow a where clause in the create index statement (as e.g. Postgres does).

But you can still trick Oracle in only indexing a sub-set of the rows by exploiting the fact that entries where all index columns are null are not put into the index. So you create a a unique index on an expression that is only not-null if the status is 'ENABLE':

create unique index ix_only_one_enabled
  on table1 ( case when status = 'ENABLE' then idaccount else null end);

The expression will yield null for all rows where the status is something else than enable and those rows won't be put into the index. Which in turn means than only the IDACCOUNT values for rows are put into the index where the status is ENABLE

Note that 'enable' and 'ENABLE' are two different values for Oracle. You probably also want a check constraint on your column to make sure you only put uppercase values in there (or use a trigger),

Unrelated but: you most probably do not want to use CHAR(10) but VARCHAR(10) due to the padding that happens when using the dreaded char data type.


Applying Index happens in memory or disc? Say I have 10k records in Customer table (MySQL or oracle) which has 10 columns. One is ID(primary key) and another is name(created index on it)

Now I search for word customer whose name is Alex. My understanding is first DBMS will determine the row location for record name Alex and then fetch those
rows in memory or it is other way round(where rows are fetched in memory then indexes are applied ?)

Study the concepts of "cache" and "block".

In many situations, especially in databases, the data on disk is organized into "blocks".  When a block is needed (to satisfy some SELECT or UPDATE or whatever), it is requested.

Meanwhile, there is a "cache" in RAM that contains some of the blocks.

To satisfy the request for a block, the system first checks to see if the block is already in cache.  If it is not, then I/O is performed to bring a copy of the block from disk into RAM.  (For "persistence", the copy on disk must be considered the "source of truth", but let's not get into "ACID" in this discussion.)

Once the block is in the cache in RAM, the processing proceeds.

But, the cache is of a fixed size.  When there is not room in cache for a block, some block currently in cache must be pushed out of the way (and written back to disk if it is "dirty").

So...

Case 1:  Everything needed for the query is already in cache:  No I/O needed.

Case 2:  Some blocks are in cache:  Some I/O.

So...  Even if you have 10 billion records, and not nearly enough RAM to cache them, Case 2 says that the processing will (eventually, maybe in hours) finish with your query.

But... You mentioned indexing.  We just talked about how an index can get you to the desired row (think "block" in this view of the processing) by touching very few blocks.  With a suitable index (BTree or otherwise), the desired row can possibly be found by touching very few blocks.  And those blocks might already all be in cache.  Hence, very fast, perhaps less than a second, perhaps with no I/O, even with billions of rows.


Converting from InnoDB tables to innodb_file_per_table I run MySQL and I have 7 schemas, 6 of the 7 schemas are MyISAM.

In one of my schemas I have 20 tables that are InnoDB, the rest are MyISAM.

I really want to convert those 20 tables over to file_per_table.

I read some old documentation on how to do this, but I'm wondering if this approach will work.

Export the foreign keys off the 20 tables.
Drop the foreign keys.
Convert the tables to MyISAM.
Stop MySQL.
Change the my.cnf/ini to put MySQL in innodb_file_per_table mode.
Delete the idbdata files.
Start MySQL.
Switch those tables back to InnoDB (which should be in file mode now).
Re-apply the foreign keys.

Am I missing any steps? Or am I making a mistake somewhere?

The process you mentioned works fine but it takes much amount of time if the tables are bigger  in size
Here the way you can do it quickly .but it requires some down time depends on the table size .

Take the full backup of 20 tables that are InnoDB.
Drop those 20  tables that are InnoDB
Stop mysql
Delete the ibdata files
Start the mysql services with  innodb_file_per_table=1 in my.cnf
Restore those 20  tables that are InnoDB with taken backup.


Log Shipping back up location to a network share instead of clustered file share Below is my current cluster set up

Node 1 - Active
Node 2 - Passive

While doing Log SHipping can I save the back ups to a network share instead of a clustered file share?

The service account will have read/write permission on the network share.

Log shipping backups aren't anything special under the hood, they run an executable (sqllogship.exe) that fires off a standard BACKUP LOG. They can be configured to point anywhere the service account has write access to (and since this is a FCI, anywhere that is reachable from both nodes).

What's the point of writing these to a network share? Reduce the shared storage requirements (presuming there's no direct attached storage you can easily use)?


how to add key and UNIQUE KEY to existing table without check to speed up performance? i have very big InnoDB tables in MYSQL 5.6 version , i want to add KEY an UNIQUE KEY as fast as i can , the data is from restored database so all keys are checked , i am looking something like SET FOREIGN_KEY_CHECKS=0; in FOREIGN KEY . is there something like this for keys ?

If I understand correctly, you want to achieve improved performance for the restore process of an SQL dump file. In that case, you can use do SET UNIQUE_CHECKS=0; before inserting the data and then SET UNIQUE_CHECKS=1; afterwards.

(mysqldump already does this automatically, at least for MariaDB.)

See also the documentation for bulk loading data.


Can I build relationships in existing system without breaking it? This is my first question here, so I would love help to make it work for SO:DBA.

I inherited an older relational system that is sitting on a SQL Server 2005 box and the database is at SQL2000 compatibility.  In my initial inspection, this system is loaded with cursor functions and very cumbersome code (including *= joins).  I have a feeling that this is because every table has one primary key column that is non-identity, and there are no PK-FK relationships built into any but two tables.  This column happens to be the same value, DocID.  There are few if any indexes outside the clustered index on the "PK" that all tables have.  I am finding 2nd and even 1st normalized tables in several locations.  There is no data dictionary that anyone knows of and the original builders of the system are now long gone, the company that built this system has been turned over 3-4 times and does not support this system anymore.   There is only the one box, and a "test" environment is another database structure on the same server, and I do not have the ability to create a secondary server to test/build a solution.  The last little wrinkle is that my company has decided to replace the system, and if nothing else, I will need to extract a great deal of baseline data from this system for import into the new system within the next 6-8 months, if nothing else, to leave the old system.

Currently, there are issues with severe slowdowns and from I/O requests and long lock conditions due to connection conditions and several massive stored procedures that touch most if not all the tables in rapid succession.  That is what I am tasked to eliminate.  I have already begun a manual mapping of the system's architecture from within SQL Server.

I have not experienced a database with this degree of brokenness.  I come from the developer's side of the room, so I know how to write/refactor/update queries in set notation, but I am struggling with why the system was built without using any kind of relationship outside of query WHERE logic and such a huge number of cursor based update and inserts.  (I suppose the why is moot at this point, but it would be nice to attribute the pain of this process to some “good” reason.)

What can I do to establish the relationships so that the DBMS can actually use its power to handle the requests that currently bring the system to a crawl 3-4 times a day?  Would using the SQL Server 2005 Relational Diagram tool be useful for building the relationships and examining the index potential for the columns in each table?  If I use the tool, will the relationship changes be done without "breaking" the legacy processes?  For example, will the stored procedures with cursors still function correctly, if I re-key and index the table they are working on without adapting the Stored Procedure?

I would not make modifications like adding primary/unique or foreign key constraints to a legacy database that you haven't built yourself. Chances are that the original developer, given the problems and design issues you mention, may have built logic that breaks with such constraints.

For instance, if the app uses a 0 in a key column instead of NULL to represent "nothing", a foreign key constraint would fail that UPDATE/INSERT, even if the value is just used temporarily in a procedure.

The best place to start is probably to identify specific queries that show performance issues or cause locking, and deal with those. Just adding proper indexes on strategic tables may prove a huge improvement for you.

As for cursors.. some devs just don't know better. :)


Do I still need OLAP cubes after a SQL Server Upgrade After our DBA got sacked for poor performance, I am now our accidental DBA. I have been pushing to upgrade our SQL Server instance from 2012 to 2017 so I can use the clustered column store indices in our warehouse and downgrade from enterprise to standard at the same time.

We also had been paying a lot of money for enterprise analysis services. We have about SSAS databases that are used in a handful of PowerBI reports and remain mainly unused. I wanted to ask the community if people still find a need for OLAP cubes with the ever-increasing performance improvements in SQL Server and other databases in general.
Im sure there are many variables involved including volume data of data and the number of queries but does anyone have any general insights?
Thanks

on this link: A quick overview of SSAS for DBAs you can find relevant information about SSAS that can help you to see why there are so many advantages using SSAS.

You also need to manage permissions, as you mentioned Power BI, SSAS interacts very well with Power BI, in many environments I restrict the Power BI views by managing the permissions using AD and SSAS.

SSAS also needs maintenance like backups and integrity checks, so yes, there is an overhead if you want to maintain it properly, and include a recover strategy.

You mentioned columnstore indexes and SSAS cubes and you can find a similar question here.

this was a general insight. If you put more focus on your question, you will get more objective and detailed answers.


How to configure TCPS in JDBC thin client for oracle An Java Application running with JDBC thin wtih TCP connection, Now require is to convert in TCPS for encryption transmission from application to database.
String url = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=servername)(PORT=XXXX))(CONNECT_DATA=(SERVICE_NAME=XXXX)))");

with using JKS truststore for certificate;
Here we havn't using sqlnet.ora file for setup TCPS encryption. Can anyone help for same that what are changes require in JDBC,Ciper, certificate etc ?

To setup TCPS with JDBC, we have a few tasks.
First, create the wallet in both client and server:
orapki wallet create -wallet "wallet" -pwd XXXXXXXX -auto_login

Then, create self-signed certificates (or signed by a trusted CA)
orapki wallet add -wallet "wallet" -pwd XXXXXXXX -dn "CN=%COMPUTERNAME%" -keysize 1024 -self_signed -sign_alg sha256 -validity 3650

Exchange via SFTP/SCP and import each other certificate in the wallet
orapki wallet export -wallet "wallet" -pwd XXXXXXXX -dn "CN=%COMPUTERNAME%" -cert ./%COMPUTERNAME%.crt

orapki wallet add -wallet "wallet" -pwd XXXXXXXX -trusted_cert -cert %REMOTECOMPUTER%.crt

Now indicate in SQLNET.ORA the location of the wallet (in TNS_ADMIN folder, in both client and server).
WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = D:\oracle\wallet)
     )
   )

SQLNET.AUTHENTICATION_SERVICES = (TCPS,NTS,BEQ)
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA, SSL_RSA_WITH_3DES_EDE_CBC_SHA)

Now edit listener.ora in the server to add the secure endpoint (PROTOCOL = TCPS):
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = mydbservername)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCPS)(HOST = mydbservername)(PORT = 2484))
    )
  )

Restart the listener with lsnrctl stop, start and status to check that your line is showing up.
Update the TNSNAMES.ORA accordingly in both client and server.
MYDB_SSL=
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCPS)
      (HOST=MYDBSERVERNAME)
      (PORT=2484)
    )
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SERVICE_NAME=REP3)
    )
  )

The TNS_ADMIN folder in the client has to be the same used for the wallet, so place your sqlnet.ora and tnsnames.ora in that folder only.
If everything is good so far, tnsping MYDB_SSL will return OK.
D:\>tnsping MYDB_SSL

TNS Ping Utility for 64-bit Windows: Version 12.2.0.1.0 - Production on 05-FEB-2021 13:10:59

Copyright (c) 1997, 2016, Oracle.  All rights reserved.

Used parameter files:
D:\oracle\product\12.2.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCPS) (HOST=MYDBSERVERNAME) (PORT=2484)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = MYDB)))
OK (1100 msec)

Otherwise, check lsnrctl on the server side, find the log and troubleshoot.
On the java side, set the JDBC URL as per the example below (for Spring/JPA):
spring:
  jpa:
    database: oracle
    properties:
      hibernate:
        dialect: org.hibernate.dialect.Oracle12cDialect
  datasource:
    url: jdbc:oracle:thin:@MYDB_SSL?TNS_ADMIN=\\oracledb\\wallet

Load the certificates in the cacerts with the keytool command:
keytool -importcert -file %ORACLE_BASE%\%CN%.crt -keystore %CACERTS% -storepass changeit -alias %CN%

Run the app, you may specify the TNS_ADMIN in the command line too:
%JAVA% -Doracle.net.tns_admin=%ORACLE_BASE%\wallet -jar myapp.jar...

Hope this helps...


Deploying a Replica Set I am new to MongoDB. I am trying to create a Replica Set for testing and development. It shows me the message in the picture!

I followed the steps shown in the official documentation:

Deploy a Replica Set for Testing and Development

What is the problem please ?

You are using 32-bit OS and 32-bit builds do not support the WiredTiger storage engine.

You need to use either 64-bit or change your storage engine to mmapv1 as suggested in screenshot by passing parameter explicitly. --storageEngine=mmapv1

check here https://docs.mongodb.org/manual/administration/production-notes/


how to run SQL query without permission there is a sql query that joins both example1.dbo.table1 and example2.dbo.table2
User1 has no select permission on example1 DB.
User1 has select permission on example2 DB.
User2 can run that query
how can User1 run that query ? I dont want to give select permission to User1.
Is there any possibility of that ?
I created this query as procedure and added "execute as user2" inside of it but still couldn't run for User1.

You will need cross-database permissions.
The most secure solution is to create a dedicated user from Certificate in the "example1" DB and grant this user read privileges. Then in the "example2" DB you will sign this stored procedure with the same certificate, so the procedure will be able to use permissions of the previously created user.
You can find detailed tutorial here. And for a longer elaboration on the topic, check out Erland's classic article.
Another option would be to enable 'Cross-database ownership chaining', but it is not recommended because of security implications.


select query on Temp table is not returning data in correct order even after specifying index hint In my code, I am creating a temp table using select * into #tempTable.
Temp table contains the huge number of records and I need to retrieve data from it many times in my code so I created a clustered index on it.
But somehow I am not getting data in correct order as per created index.

I know I can do it simply by putting the order by clause, but my question is why I am not getting data in the correct order even if I am forcing the optimizer to use the particular index?

Below is my sample code:

----------- Sample code ----------------------------

;with CTE1
as (
    select col1
        ,col2
        ,-- - -- - - from table1

    union

    select col1
        ,col2
        ,-- - -- - - from table2
        -- few more tables in union --
    )
    , CTE2
as (
    select *
        ,RowNumber = RowNumber()
            over (order by case
                    when @sortOrder = 0
                    then case
                            when @sortColumn = 'Date'
                            then [tdate]......some more dynamic order by conditions..........
                         end
                    end asc
            )
    from CTE1
    inner join
    from TABLE3 on -- some conditions
    )
select *
from CTE2
into #TempTable;

Then add the clustered index:

create clustered index IX_TempTable_RowNumber on #TempTable (RowNumber asc);

select *
from #TempTable with (index (IX_TempTable_RowNumber));

----------------- End of sample code ---------------------

Even I checked the table scheme and index details using

exec tempdb.dbo.sp_help N'#TempTable';

select *
from tempdb.sys.indexes
where name = N'IX_TempTable_RowNumber'

And it seems correct to me. What am I missing here?

You have to use an ORDER BY.  Adding a hint to use a specific index does not guarantee any ordering of the resultset:

https://stackoverflow.com/questions/10064532/the-order-of-a-sql-select-statement-without-order-by-clause


MySQL Error: impossible to write to binary log I have a system (inherited from another development company) and I don't know all its features yet.
Construction:
- web client/application (symfony 1.4)
- DB - MySQL.

Project is deployed on 2 servers:
1-st: nginx server + PHP + DB Master;
2-st: DB Slave.

When data are modificated by web-client at DB Master, DB Slave works good and data have normal replication process.
When data are modificated by MySQL Management Client, I have an notification:

  Cannot execute statement: impossible to write to binary log since
  BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine
  limited to row-based logging. InnoDB is limited to row-logging when
  transaction isolation level is READ COMMITTED or READ UNCOMMITTED

And then, DB Slave falls down and replication process breaks (error insert... duplicate entry...).

The similar notification happens (at this situation - error) with the same text, when I try to insert data with java application/module (it runs inside transaction) and after exception, transaction rolls back and no modification happens.

How can I solve this problem and make system work and make it possible modificate data on DB Master and DB Slaves work in normal mode?

There are 3 methods that MySQL can use to write to the binary logs:

STATEMENT
This means that every SQL statement on the master is recorded in the log, and executed on the slave.  This can cause problems if the SQL statement contains statements such as "NOW()", "RAND()" and anything non-deterministic.  This also requires support from the storage engine in use.

ROW
This means that every row that is changed by a statement is individually recorded in the binary log.  This gives larger binary logs (generally) than statement based logging, but is almost always guaranteed to give the exact replication needed.

MIXED
This allows MySQL to choose between binary and row based logging as required.

If you're getting this error, one suggestion is to change the BINLOG_FORMAT variable to MIXED.  This allows MySQL to automatically switch between ROW and STATEMENT based logging as required.


Online text based game design - Am I linking the databases correctly? I'm trying to recreate an old game that I used to play that went offline quite some time ago. I'm doing this as a hobby project for myself with the hope that maybe a few hundred people might enjoy it once it's finished. It's obviously a long way off as I'm teaching myself as I go but figured I'd start with the database design.

It's a simple (to play) game. You create an empire with a particular race, you create buildings which influence your population, resources, etc and military. The game updates once an hour and depending on what you've built, you start to progress or gain networth. - I hope this is a brief overview to understand the type of game I'm attempting to make? There's no graphical interface, everythings done in text using tables, etc..

Anyways, so I started to use MySQL Workbench to try and map out the databases and now I've realised how much it's going to require in the background. I've got a screenshot of what I've laid out (which I'll post below) and I'd like to know if, in it's current and basic form, it will work. Of course, there's ALOT more to go into this but I want to know that I'm on the right track before progressing.

Thank you for the help.

Here's the screenshot!

& This is what I'm working with: Web Archive of "races" guide for reference.

Yes, it's correct, although, as noticed by yourself , it's pretty crude.

This has been asked months ago. Why didn't anyone answer it yet?

Are you still working on this project?

I wonder if you really have the right skills to complete this... If it's indeed interesting, I could help you with the database architecture.

One word of advice: MySQL does not work well with huge amount of relational data.

Regards, Marcus Vinicius.


insert into select with union needs derived table Why do I need to do another select when I am trying to insert distinct values into a table? Also: is there a better way to do this?

WORKS:

INSERT INTO phppos_categories (name)
SELECT category FROM
    (SELECT DISTINCT category FROM phppos_items as items
    UNION
    SELECT DISTINCT category FROM phppos_item_kits as item_kits)
as category_result;

DOESN'T WORK:

INSERT INTO phppos_categories (name)
    (SELECT DISTINCT category FROM phppos_items as items
    UNION
    SELECT DISTINCT category FROM phppos_item_kits as item_kits)

Try this it will work

INSERT INTO phppos_categories (name)
SELECT DISTINCT category FROM phppos_items
UNION
SELECT DISTINCT category FROM phppos_item_kits


best way to migrate a database? I currently have a on-premise SQL Server 2014 High-Availability cluster.
I will setup a active geo-replication/auto failover group in Azure.
I need to do a one-time data migration---Should I use Azure Data factory?
Also, how do i ensure compatibility with the legacy Sql Server 2014 ?

Thanks, Peter

My suggestion is to use Microsoft Data Migration Assistant. It will perform an assessment of your on-premises databases first, then it allows you to migrate the database to Azure SQL Database. This tool is very user friendly. It's very easy to migrate a database to Azure with this tool.


How to change data location on Ubuntu I already saw this question here:
https://stackoverflow.com/questions/1795176/change-mysql-data-directory
but it's not very helpful.

What I want: to move my sql data folder to my different place (/media/data)
What I did:

a) sudo service mysql stop

b) mv /var/lib/mysql/ /media/data/

c) sudo vim /etc/mysql/my/cnf:

section [mysqld]:
   datadir = /var/lib/mysql/ - > datadir = /media/data/lib/
section [mysqld_safe]:
  added:  datadir = /media/data/lib/

d) sudo vim /etc/apparmor.d/usr.sbin.mysqld:

added:
  /media/data/mysql/ r,
  /media/data/myql/** rwk,
commented out:
  #/var/lib/mysql/ r,
  #/var/lib/mysql/** rwk

e) sudo /etc/init.d/apparmor reload

f) sudo service mysql start
What I got:

a) tail -f /var/log/mysql/error.log:

120215  1:30:01 [Note] Plugin 'FEDERATED' is disabled.
/usr/sbin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13)
120215  1:30:01 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
120215  1:30:01  InnoDB: Operating system error number 13 in a file operation.
InnoDB: The error means mysqld does not have the access rights to
InnoDB: the directory.
InnoDB: File name ./ibdata1
InnoDB: File operation call: 'create'.
InnoDB: Cannot continue operation.

b) Even when sometimes log is clean I cannot connect to server:
   mysql -u root -p
   Enter password:

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

Please help..

I've done that on ubuntu, I don't use apparmor but these are the settings I've changed, all under [mysqld] (I think mysql_safe also uses these settings)

datadir = /mysql_data
innodb_data_home_dir = /mysql_data
innodb_log_group_home_dir = /mysql_data

I've also put my log files there but that is not needed.


MariaDB thread_pool_size tuning I think the setting of thread_pool_size = 500  has been installed at my site with the wrong understanding of its application.

Our host server has only 40 CPUs.
Isn't this setting going to be counter-productive?

However, it looks to me like we might benefit from setting it slightly higher than our CPU count.

Am I correct in thinking that we should not set it (much) higher than our CPU count, and never higher than 128, the limit posed in the manual?

Yes, it is going to be counterproductive. You can set it slightly higher than CPU count, maybe 2*CPU count, but not 12.5*CPU count. You do not want 500 connection to execute at the same time, CPU-bound. you only want to use as many  CPUs as you got.


select two record containing dates in it with two input dates Simply I have Two Input box

 Start_date
 End_date

This is my database table

| id | category | start_date | end_date   |
| 1  | gold     | 2016-05-24 | 2016-05-31 |
| 2  | silver   | 2016-05-16 | 2016-05-23 |

Now When I insert date like below:

start_date = 2016-05-25
end_date = 2016-05-27

I want to find out category between imputed date in input box.

I use this select query:

SELECT category
FROM tablename
WHERE '$start_date','$end_date' BETWEEN start_date AND end_date;

But I don't get any result. I know that if you use single date after where I get result but I want to search with two dates

Simple answer:

If you want both date columns to be between the input dates:

SELECT category
FROM tablename
WHERE 'start_date' BETWEEN start_date AND end_date
  AND 'end_date' BETWEEN start_date AND end_date ;

Or if you want any of the date columns to be between the input dates:

SELECT category
FROM tablename
WHERE 'start_date' BETWEEN start_date AND end_date
   OR 'end_date' BETWEEN start_date AND end_date ;

Or if you want any of the ranges that are "overlapping with the range of the input dates
(seems the most likely, based on the wanted result):

SELECT category
FROM tablename
WHERE 'start_date' <= end_date
  AND start_date <= 'end_date' ;


create a visible uniquifier column for a nonunique clustered index I have a table of events, clustered index is the creation date/time.

Normally there should never be two events happening in the same millisecond, but freak occurences happen, so the column is not neccessarily unique.

I want to add an uniquifier column, that is 0 for the first inserted row with a given time, 1 for the second, and so on:

TIME                    UNIQUIFIER,     TEXT
2018-01-01 01:23:45.678,   0,           "aaa"
2018-01-01 02:00:00.000,   0,           "bbb"
2018-01-01 02:00:00.000,   1,           "ccc"
2018-01-01 02:00:00.000,   2,           "ddd"
2018-01-01 03:45:67.890,   0,           "eee"

I know MS Sql Server creates such a column automatically, when I create the clustered index. However the column is hidden.

I would like a visible uniquifier, that behaves like this hidden one, so I can use it in foreign key relationships.

I.e. so that when ...

another table needs to reference an existing event,
and I only know the time when that event happened,
and I only care about the first event that happened at the same time,

... I can use Time:2018-01-01 02:00:00 / Uniquifier:0 as the foreign key, knowing this row exists, without me looking up the event in the first table.

I could use an autoincremented identity column instead to make the key unique, but this means I cannot reference an event without knowing its identity column.

For example if I want to create the event AND a bunch of additional event info in a separate 1-n table, I would have to first create the event, then look up its identity column, and only then create the related information, using that identity column. I am trying to avoid that roundtrip.

Is it possible to have such a visible column in SQL Server filled with automatic values, same as the hidden uniquifier column?

Or do I have to manually calculate such a column when I insert rows?

Is it possible to have such a visible column in SQL Server filled with automatic values, same as the hidden uniquifier column?

No there is no built-in feature to do this.

  Or do I have to manually calculate such a column when I insert rows?

You could consider using INSTEAD OF or AFTER triggers to maintain the correct uniqueifier-like behaviour for the same-time group when rows are inserted/updated/deleted.

This may require some careful thought to operate correctly under high concurrency, but it is probably best done as close to the data as possible (i.e. server-side, in the database, rather than client-side). A well-written trigger (with supportive indexing) will introduce minimal overhead.

Alternatively, make the index unique and implement retry logic. Since the expectations seems to be that collisions will be rare, this might well be acceptable.


What should I monitor on the SQL Server machine? I need to create some alerts, logging jobs and monitors on a machine that is going to run SQL Server 2012 Standard for an ERP system.

The OS will be Windows Server 2008 R2 Standard/Datacenter (if it matters).

I am planning to implement: server performance monitors (processor time, load, memory used, reads/writes to disk, disk space).

I don't know what should I be logging in terms of the database/SQL Server instance. I thought about autogrowth operations on the database and transaction log files.
I guess I should be monitoring locks, transactions, connections and a few more things. But what exactly? Which activities should be logged only and which should be emailed to me ASAP?

I will greatly appreciate your advice as I'm a beginner in database administration.

Some of the options you mention (file auto grow for example) are already logged in the SQL Server default trace http://msdn.microsoft.com/en-us/library/ms175513.aspx

Other options for your own monitoring can be done via windows perfmon to monitor all kind of windows or SQL Server variables. You could also save output from any of the SQL Server DMV to store various options over time (query plan statistics, wait types, memory, locks etc).

You could investigate using the new Management Data Warehouse feature to collate lots of information http://msdn.microsoft.com/en-us/library/bb677306.aspx.

Having said all of that, that is going to be a steep learning curve and it is difficult to give a definitive answer. As a beginner is buying a third party tool that will do all this monitoring for you an option? Some packages are: -

http://www.sqlsentry.com/products/performance-advisor/sql-server-performance
http://www.red-gate.com/products/dba/sql-monitor/


compare dates in results I'm looking to compare the dates in the result set

Example:

SELECT * FROM tbl WHERE foo=bar

Output

id, datetime, name
1, 2014-01-01 18:13:45, test 1
2, 2014-01-01 19:01:32, test 2
3, 2014-01-01 20:34:44, test 3
4, 2014-01-03 04:45:22, test 4

I would like to be able to tell between each record if the difference in time is less or more than 24 hours of the previous record.

Example:

1, 2014-01-01 18:13:45, test 1 <-- first record since the previous record
2, 2014-01-01 19:01:32, test 2 <-- second record was less than 24 hours since the previous record
3, 2014-01-01 20:34:44, test 3 <-- third record was less than 24 hours since the previous record
4, 2014-01-03 04:45:22, test 4 <-- fourth record was more than 24 hours since the previous record

thoughts?

As said by @ypercube, lag() will help.

select id,
       (d - lag) as lag,
       case when (d - lag) < interval '24 hours' then false else true end as p
from (
      select id,
             (datetime) as d,
             lag(datetime) over (order by id) as lag
      from lagtest
     ) as t;

For the first row no lag will be displayed.


using tmpfs for mariadb tmp tables My server is writing about 50% of temp tables to disk, even when max_heap_table_size and tmp_table_size are ridiculously high (500M). Yesterday I ran a script all day that monitored the tmp folder and the total space never exceeded 100M.

My theory is big columns such as long text are forcing tmp tables to disk. I have no control over the queries or this part of the database design.

If I pointed tmpdir to a 500M tmpfs file system I could speed up these queries. I can only think of 2 reason why not to do this.

It takes away from memory that could be allocated to innodb buffer pool
Infrequent operation such as table copy, optimise, alter etc can use huge amounts of tmp space

Regarding reason 1, I'm thinking the performance gains would trump and gains by allocating more memory to innodb buffer pool, but I need to test.

Regarding reason 2, if I pointed tmpdir to a tmpfs file system and innodb_tmpdir to a large disk based system, will that avoid filling up tmpfs when doing table copy, optimise, alter etc?

500M for tmp_table_size is really dangerous, but not for the reason you might think.

tmp_table_size and max_heap_table_size work together.  When a SELECT needs to store intermediate results for, say, a sort (ORDER BY), it first tries to do it in RAM.  There are several cases where this won't work:

A TEXT or BLOB column is being fetched.  It will bypass RAM and go straight to disk.  (tmp_table_size is irrelevant in this case.)
It starts to use RAM but the temp table grows bigger than either of those two settings.  At this point, it copies what it has so far to disk.  (Having tmp_table_size big will actually hurt for large temp tables.)

If you have several SELECTs running at the same time, and doing the second bullet item above, that is potentially several allocations of 500M.  If you run out of RAM, the system will start swapping.  Swapping is much worse than spilling tmp tables to disk sooner.

Note further, that a single SELECT can cause more than one temp table to be used!  One example is GROUP BY something ORDER BY some_other_thing

Bottom Line: Set tmp_table_size and max_heap_table_size to no more than 1% of RAM.

Now, back to 50% of tmp tables being on disk.  Sometimes that is necessary.  But there are some common cases where disk can be avoided.

Don't ever use TINYTEXT.
Don't use TEXT if a modestly sized VARCHAR will suffice.
Don't blindly say VARCHAR(255); make them reasonably sized.  (Before spilling to disk, they are stored in a MEMORY table, which turns VARCHAR into CHAR.  Note: 8.0 fixes that.)
Poor indexes.
Poor formulation of queries.

For the last two, let's see a couple of the queries that are likely to be spilling to disk.  I can often spot what to do.

Filesystems

I dislike chopping disk up into filesystems.  The inevitable result is running out of disk on one FS while there is lots of room on another FS.
I dislike using ramdisk for anything; MySQL does a lot of intelligent work to make use of the RAM it is given; don't rob it for transient use.
I strongly agree with your Reason 1 (give RAM to the buffer_pool).
As for reason 2...  Note that any DDL (Alter/Optimize/...) that needs to copy a table over would prefer to do it in the same FS so that the final step is mv, not cp + rm.
As for OPTIMIZE TABLE, I frequently rant about how that is virtually useless for InnoDB; don't use it.


Design best practices for last tracking with indexed view My tables structure is below :

  TbDoc (ID int , ...)
  TbDocActions( ID Int, DocID Int, Date DateTime, col1 int, col2 int, ...)

I want to have indexed view to get last TbDocActions columns for each TbDoc record.
result of this view must be such as below :

  DocID , col1, col2, ...

For get this result with view I can use below query:

Select Z.DocID, X.*
From (Select DocID, Max(ID) as MaxActionID
      From TbDocActions
      Group By DocID
     )Z
inner join TbDocActions X ON X.ID = Z.MaxActionID

but I want indexed view to have better performance. and in indexed view I can't use Max() aggregate function.

As you already know, you can't use a MAX() aggregate in an indexed view. In any case don't fool yourself into believing that creating an indexed view is a magical performance power button. Depending on your workload, maintaining the indexed view may cost you more performance than you're gaining from the queries against it.

A couple of alternatives might be:

(1) create a non-clustered index on dbo.TbDocActions(DocID, ID DESC) or drop the existing clustered index and change it to DocID, ID DESC. (If ID is an identity column, you've probably also set it up to be the primary key and it will be clustered by default, but that doesn't have to be the case.)

(2) maintain a separate table with the DocID as the primary key, and keep the highest ID up to date using triggers (or a stored procedure if you are constraining writes to stored procedures only). You will have to have logic for all three DML types (insert, update and delete) because, in theory, any of these could affect the MAX calculation. (Less risk, obviously, if both ID columns are IDENTITY.) You could also add a column to the main table and keep that up to date the same way.

Note that the same caveats apply - adding indexes or maintaining values in other tables might benefit this specific query, but they are not magic - they might affect other parts of your workload. You should always take your entire business cycle into consideration when thinking about "fixing" any single query...


Get the value of one attribute from the reference key of another table? I have two tables named as ps_order_detail and ps_product_lang in MySQL. I want to get the product name from one table whose reference key is in another table. I have two tables. First the ps_order_detail database table is like this

CREATE TABLE IF NOT EXISTS `ps_order_detail` (
  `id_order_detail` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `product_id` int(10) unsigned NOT NULL,
  `product_attribute_id` int(10) unsigned DEFAULT NULL,
  `product_name` varchar(255) NOT NULL,
  PRIMARY KEY (`id_order_detail`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `ps_order_detail`
--

INSERT INTO `ps_order_detail` (`id_order_detail`, `product_id`, `product_attribute_id`, `product_name`) VALUES
(1, 5, 11, 'iPod touch - Capacité: 32Go'),
(2, 2, 2, 'iPod shuffle - Color : Green'),
(3, 5, 9, 'iPod touch - Disk space : 8GB');

and another table for ps_product_lang is like this

CREATE TABLE IF NOT EXISTS `ps_product_lang` (
  `id_product` int(10) unsigned NOT NULL,
  `id_shop` int(11) unsigned NOT NULL DEFAULT '1',
  `id_lang` int(10) unsigned NOT NULL,
  `name` varchar(128) NOT NULL,
  PRIMARY KEY (`id_product`,`id_shop`,`id_lang`),
  KEY `id_lang` (`id_lang`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `ps_product_lang`
--

INSERT INTO `ps_product_lang` (`id_product`, `id_shop`, `id_lang`,  `name`) VALUES
(1, 1, 1, 'iPod Nano', 'In stock'),
(2, 1, 1,  'iPod shuffle'),
(3, 1, 1,  'MacBook Air'),
(4, 1, 1, 'MacBook'),
(5, 1, 1, 'iPod touch'),
(6, 1, 1, 'Belkin Leather Folio for iPod nano - Black / Chocolate'),
(7, 1, 1, 'Shure SE210 Sound-Isolating Earphones for iPod and iPhone'),
(8, 1, 1, 'Lappy');

Now you can see that in the table  ps_order_detailthere is one column named as product_id and in the table ps_product_lang there is one column for name . so my question is how can I get the name of product by combining both ps_product_lang and ps_order_detail table.
I want to get the product name from ps_order_detail product_ida attribute? So any help and suggestions will be really appreciable. Thanks.

Try this:

SELECT DISTINCT a.product_name
FROM `ps_order_detail` a
JOIN ps_product_lang b
    ON a.product_id=b.id_product